Accessing Databases
[ Home ] [ Up ] [ Relational Databases ] [ Structured Query Language (SQL) ] [ What is JDBC? ] [ Connecting to Databases ] [ SQL Errors and Warnings ] [ Accessing Databases ] [ Creating and Populating a Database ] [ Transactions ] [ Prepared and Callable Statements ] [ Using Metadata ]

 

 

Once you've established a connection with a database, you can now start to use SQL to access the database. To do this, you use the Statement interface. Each driver has an associated class which implements the Statement interface (It also has an associated class which implements the Connection interface.)

The Statement Interface

Given a Connection, you obtain an instance of Statement as follows:

Connection conn = DriverManager.getConnection(...);
Statement stmt = conn.createStatement();

Now, you can use the Statement to execute a SQL statement:

stmt.executeUpdate("CREATE TABLE ...");

Here's an example:

package jdbc;

import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class ConnectToDatabase
{
    public static void main(String[] args)
    {
        Connection conn = null;
        Statement stmt = null;
        try
        {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            conn = DriverManager.getConnection("jdbc:odbc:Employees");
            stmt = conn.createStatement();
            stmt.executeUpdate("CREATE TABLE address " +
                               "(" +
                               "Street     char(50)," +
                               "Town       char(40)," +
                               "State      char(2)," +
                               "PostalCode char(9)" +
                               ")" );
        }
        catch(ClassNotFoundException ex)
        {
            ex.printStackTrace();
        }
        catch(SQLException ex)
        {
            ex.printStackTrace();
        }
        finally
        {
            if (conn != null)
            {
                try
                {
                    if (stmt != null)
                        stmt.close();
                    conn.close();
                }
                catch(SQLException ex)
                { /* Do nothing */ }
            }
        }
    }
}

Note:  You should be cleaning up your statements explicitly, as in the above example.

The executeUpdate() Method

You use Statement's executeUpdate() method to execute SQL statements such as CREATE TABLE, DROP TABLE, INSERT, UPDATE and DELETE. It returns the count of rows that were affected by the statement.

For example, here are some INSERTs:

package jdbc;

import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class ConnectToDatabase
{
    public static void main(String[] args)
    {
        Connection conn = null;
        Statement stmt = null;
        try
        {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            conn = DriverManager.getConnection("jdbc:odbc:Employees");
            stmt = conn.createStatement();
            
            int totalCount = 0;
            int count = 0;
            count = addAddress(stmt, 
                            "27 Bellvue Ave", "Carson City", "NV", "94567");
            totalCount += count;
            count = addAddress(stmt, 
                            "9000 Spontadema St", "Jersey City", "NJ", "34267");
            totalCount += count;
            count = addAddress(stmt, 
                            "756 Fickle Circle", "Sun City", "IA", "87345");
            totalCount += count;
            System.out.println(totalCount + " addresses added.");
        }
        catch(ClassNotFoundException ex)
        {
            ex.printStackTrace();
        }
        catch(SQLException ex)
        {
            ex.printStackTrace();
        }
        finally
        {
            if (conn != null)
            {
                try
                {
                    if (stmt != null)
                        stmt.close();
                    conn.close();
                }
                catch(SQLException ex)
                { /* Do nothing */ }
            }
        }
    }
    
    private static int addAddress(Statement stmt, 
                                  String street, String town, 
                                  String state, String postalCode)
        throws SQLException
    {
        String st = (street != null) ? ("'" + street + "'") : ("NULL");
        String tw = (town != null) ? ("'" + town + "'") : ("NULL");
        String sta = (state != null) ? ("'" + state + "'") : ("NULL");
        String code = (postalCode != null) ? ("'" + postalCode + "'") : ("NULL");
        
        String sql =
            "INSERT INTO address " +
            "VALUES(" + st + ", " + tw + ", " + sta + ", " + code + ")";
        System.out.println("Executing statement:\n" + sql);
        
        int count = stmt.executeUpdate(sql);
        return count;
    }
}

and here is a DELETE:

package jdbc;

import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class ConnectToDatabase
{
    public static void main(String[] args)
    {
        Connection conn = null;
        Statement stmt = null;
        try
        {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            conn = DriverManager.getConnection("jdbc:odbc:Employees");
            stmt = conn.createStatement();
            
            int totalCount = stmt.executeUpdate("DELETE FROM address");
            System.out.println(totalCount + " addresses deleted.");
        }
        catch(ClassNotFoundException ex)
        {
            ex.printStackTrace();
        }
        catch(SQLException ex)
        {
            ex.printStackTrace();
        }
        finally
        {
            if (conn != null)
            {
                try
                {
                    if (stmt != null)
                        stmt.close();
                    conn.close();
                }
                catch(SQLException ex)
                { /* Do nothing */ }
            }
        }
    }
}

Note: Some ODBC drivers apparently do not support DELETE!

Performing Queries

If we wish to perform a SQL query (i.e. a SELECT statement), then we have to use the executeQuery() method, which returns a ResultSet. ResultSet represents the rows returned by the query, and you can use its methods to move through the rows, obtaining information about each row's column values:

package jdbc;

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

public class ConnectToDatabase
{
    public static void main(String[] args)
    {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try
        {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            conn = DriverManager.getConnection("jdbc:odbc:Employees");
            stmt = conn.createStatement();
            rs = stmt.executeQuery(
                        "SELECT street, town, state, postalCode " +
                        "FROM address " +
                        "ORDER BY state");
            int count = 0;
            while (rs.next())
            {
                String street = rs.getString(1);
                String town   = rs.getString(2);
                String state  = rs.getString(3);
                String postalCode = rs.getString(4);
                System.out.println("[" + ++count + "]");
                System.out.println(" Street: [" + street + "]");
                System.out.println(" Town:   [" + town + "]");
                System.out.println(" State:  [" + state + "]");
                System.out.println(" Postal Code: [" + postalCode + "]");
            }
        }
        catch(ClassNotFoundException ex)
        {
            ex.printStackTrace();
        }
        catch(SQLException ex)
        {
            ex.printStackTrace();
        }
        finally
        {
            if (conn != null)
            {
                try
                {
                    if (stmt != null)
                    {
                        if (rs != null)
                            rs.close();
                        stmt.close();
                    }
                    conn.close();
                }
                catch(SQLException ex)
                { /* Do nothing */ }
            }
        }
    }
}

Here's some output from this program:

[1]
 Street: [756 Fickle Circle                                 ]
 Town:   [Sun City                                ]
 State:  [IA]
 Postal Code: [87345    ]
[2]
 Street: [9000 Spontadema St                                ]
 Town:   [Jersey City                             ]
 State:  [NJ]
 Postal Code: [34267    ]
[3]
 Street: [27 Bellvue Ave                                    ]
 Town:   [Carson City                             ]
 State:  [NV]
 Postal Code: [94567    ]

Note the padding that results from using CHAR(n) data types.

SQL Data Type/Java Data Type Mapping

In the above example, we did a SELECT and retrieved column values for each row using the ResultSet's getString() method. We could do that because we knew that all the columns retrieved were of SQL data type CHAR(n), and we knew that it maps to Java data type String. If we had to deal with other SQL data types, we would need to know how those data types map to Java data types.

Data type mappings from SQL to Java:

SQL Data Type Java Data Type

Comments
BIGINT long  
INTEGER,INT int  
SMALLINT short  
TINYINT byte  
NUMERIC,DECIMAL java.math.BigDecimal Not java.sql.Numeric, as several books maintain. (This appears to be a result of an early change in JDBC that was not picked up by some.)
FLOAT double  
REAL float  
DOUBLE double  
CHAR String Space-padded to the right
VARCHAR,LONGVARCHAR String  
BIT boolean  
DATE java.sql.Date Not java.util.Date.
TIME java.sql.Time  
TIMESTAMP java.sql.Timestamp  
BINARY,VARBINARY,
LONGVARBINARY		 byte[]  
BLOB java.sql.Blob JDBC 2.0 / SQL3
CLOB java.sql.Clob JDBC 2.0 / SQL3

Data type mappings from Java to SQL:

Java Data Type SQL Data Type

Comments
boolean BIT  
byte TINYINT  
byte[] VARBINARY If value is too large for VARBINARY, mapped to LONGVARBINARY.
double DOUBLE  
float FLOAT  
int INTEGER  
java.sql.Date DATE  
java.math.BigDecimal NUMERIC  
String VARCHAR If value is too large for VARCHAR, mapped to LONGVARCHAR.
java.sql.Time TIME  
java.sql.Timestamp TIMESTAMP  
java.sql.Array ARRAY JDBC 2.0 / SQL3
java.sql.Blob BLOB JDBC 2.0 / SQL3
java.sql.Clob CLOB JDBC 2.0 / SQL3

The java.sql.Types class

The java.sql.Types class that defines constants that are used to identify generic SQL types, called JDBC types. The actual type constant values are equivalent to those in XOPEN.

The getXXX() Methods

Every Java type that corresponds to a SQL type has a getXXX method in the ResultSet interface. However, some SQL type values may be retrieved using more than one of these getXXX methods, since JDBC does conversion in some cases.

Here is a table that shows which SQL types may be retirieved by which methods, and also which method is the recommended method to use:

Use of ResultSet.getXXX Methods to Retrieve JDBC Types

  T
I
N
Y
I
N
T		 S
M
A
L
L
I
N
T		 I
N
T
E
G
E
R		 B
I
G
I
N
T		 R
E
A
L		 F
L
O
A
T		 D
O
U
B
L
E		 D
E
C
I
M
A
L		 N
U
M
E
R
I
C		 B
I
T		 C
H
A
R		 V
A
R
C
H
A
R 		L
O
N
G
V
A
R
C
H
A
R		 B
I
N
A
R
Y		 V
A
R
B
I
N
A
R
Y		 L
O
N
G
V
A
R
B
I
N
A
R
Y		 D
A
T
E		 T
I
M
E		 T
I
M
E
S
T
A
M
P
getByte X x x x x x x x x x x x x            
getShort x X x x x x x x x x x x x            
getInt x x X x x x x x x x x x x            
getLong x x x X x x x x x x x x x            
getFloat x x x x X x x x x x x x x            
getDouble x x x x x X X x x x x x x            
getBigDecimal x x x x x x x X X x x x x            
getBoolean x x x x x x x x x X x x x            
getString x x x x x x x x x x X X x x x x x x x
getBytes                           X X x      
getDate                     x x x       X   x
getTime                     x x x         X x
getTimestamp                     x x x       x x X
getAsciiStream                     x x X x x x      
getUnicodeStream                     x x X x x x      
getBinaryStream                           x x X      
getObject x x x x x x x x x x x x x x x x x x x

An "x" indicates that the getXXX method may legally be used to retrieve the given JDBC type.
An "X" indicates that the getXXX method is recommended for retrieving the given JDBC type.

JDBC 2.0 has also introduced the following getXXX methods:

  • getArray()
  • getBlob()
  • getCLob()
  • getBigDecimal()
  • getRef()

among others.

getXXX(int columnPosition) versus getXXX(String columnName)

The ResultSet interface has two sets of getXXX() methods -- one that expects a column position (1-based), and one that expects a column name. It is recommended to use the column position version for the following reasons:

  • You may not always know the column name ahead of time (for example, when using a SELECT * FROM table statement)
  • If there are more than one columns in the select list with the same name, only the first one is retrieved.
  • There are case-sensitivity issues (should getXXX("name") find a column "NAME" ? What if there is a column "Name"?)
  • It is often considerably less efficient to look up a column by name rather than by position.

 

The page was last updated February 19, 2008