How do I retrieve values from ResultSet?

By Wayan in JDBC API 0 Comment

Here is another example on how to read data from a ResultSet returned by executing an SQL query in database.

package org.kodejava.example.sql;

import java.sql.*;

public class ResultSetExample {
    public static void main(String[] args) throws Exception {
        Connection connection = getConnection();
        try {
            String query = "SELECT id, title, publisher, year, price " +
                    "FROM books";
            PreparedStatement ps = connection.prepareStatement(query);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                // Read values using column name
                String id = rs.getString("id");
                String title = rs.getString("title");
                String publisher = rs.getString("publisher");

                // Read values using column index
                int year = rs.getInt(4);
                float price = rs.getFloat(5);

                System.out.printf("%s. %s, %s, %d, %f\n", id, title,
                        publisher, year, price);
            }
        } finally {
            closeConnection(connection);
        }
    }

    private static Connection getConnection() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        return DriverManager.getConnection("jdbc:mysql://localhost/kodejava",
                "kodejava", "kodejava123");
    }

    private static void closeConnection(Connection connection) {
        try {
            if (connection != null && !connection.isClosed()) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

We start by creating a connection to the database, see the getConnection() method. Creates a PreparedStatement to execute a query to get some data from the books table.

After executing the PreparedStatement we will have a ResultSet object. To iterate all the data in the ResultSet we call the next() method in a while-loop. When no more record to read the method return false. The ResultSet object also provides some methods to read value of the fields, the name of the method is corresponded to the type of data stored on each field of the table.

To read data using the ResultSet‘s methods (eg. getString(), getInt(), getFloat(), etc) we can either use the column name or the column index of the field read in the SQL statement.

How do I query records from table?

By Wayan in JDBC API 0 Comment 
package org.kodejava.example.sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class JdbcQueryExample {
    public static void main(String[] args) throws Exception {
        Connection connection = null;
        try {
            // Here we load the MySQL database Driver class
            Class.forName("com.mysql.jdbc.Driver");

            // Define properties for connecting to database such as
            // database jdbc url, username and password
            String url = "jdbc:mysql://localhost/kodejava";
            String username = "kodejava";
            String password = "kodejava123";

            // Get a connection to database.
            connection = DriverManager.getConnection(url, username, password);

            // Create a statement object.
            Statement statement = connection.createStatement();

            // Executes a query command to select isbn and the book title
            // from books table. The execute query returns a ResultSet object
            // which is the result of our query execution.
            String query = "SELECT isbn, title, published_date FROM books";
            ResultSet books = statement.executeQuery(query);

            // To get the value returned by the statement.executeQuery we need
            // to iterate the books object until the last items.
            while (books.next()) {
                // To get the value from the ResultSet object we can call
                // a method that correspond to the data type of the column in
                // database table. In the example below we call
                // books.getString("isbn") to get the book's ISBN information.
                System.out.println(books.getString("isbn") + "; "
                        + books.getString("title") + "; "
                        + books.getDate("published_date"));
            }
        } finally {
            if (connection != null && !connection.isClosed()) {
                // We've done the business with the connection object, so
                // let's close it.
                connection.close();
            }
        }
    }
}