How To Query a Database using JDBC


This article will provide an introduction on how to query a database using JDBC, Java and SQL.

Java Database Connectivity (JDBC) is a standard Application Programming Interface (API) that uses the Java programming language to interact with Database Management Systems (DBMS).


JDBC Step-by-Step

  1. Establish a Connection
  2. Create a Statement
  3. Execute Queries
  4. Manipulate Data
  5. Process Results
  6. Close the Connection

For more information, check out our Common Connection Errors section along with our Helpful Links and References.


Establish a Connection

To connect to a database you will need a vendor specific JDBC driver.

List of Common JDBC drivers

Available JDBC Drivers

Driver class Connection String Download
Derby org.apache.derby.jdbc.EmbeddedDriver jdbc:derby:[subsubprotocol:][databaseName][;attribute=value] Derby Download Includes Driver
Firebird (JCA-JDBCDriver) org.firebirdsql.jdbc.FBDriver jdbc:firebirdsql://<server> Download
FrontBase com.frontbase.jdbc.FBJDriver jdbc:FrontBase://<server>[:<port>]/<database> Download
HyperSQL (HSQLDB) org.hsqldb.jdbc.JDBCDriver jdbc:hsqldb:hsql://<server>/<database> HSQLDB Download Includes Driver
IBM DB2 COM.ibm.db2.jdbc.app.DB2Driver jdbc:db2://<server> DB2 Download Includes Driver
JDBC-ODBC Bridge sun.jdbc.odbc.JdbcOdbcDriver jdbc:odbc:<odbcName> Built into JVM
MySQL com.mysql.jdbc.Driver jdbc:mysql://<server>/<database> Download
MSSQL (MicrosoftDriver) com.microsoft.jdbc.sqlserver.SQLServerDriver jdbc:microsoft:sqlserver://<server>;DatabaseName=<database> Download
Oracle oracle.jdbc.driver.OracleDriver jdbc:oracle:thin:<user>/<password>@<server>/<database> Download
PostgreSQL org.postgresql.Driver jdbc:postgresql://<server>:<port> Download
Sybase com.sybase.jdbc2.jdbc.SybDriver jdbc:sybase:Tds:<server>:<port>/<database> Download
Teradata com.teradata.jdbc.TeraDriver jdbc:teradata://<DatabaseServerName> Download

The three key pieces of information required for a connection are:

JDBC Java Class Name - The Java class for the JDBC driver for your source. For MySQL it is com.mysql.jdbc.Driver.

Connection URL for Database - The URL provides the location of the database. The first part is jdbc (the protocol), followed by a database specific identifier (mysql), then the host URL or IP address. As part of the URL you can also specify connection parameters. e.g jdbc:mysql://localhost/mydb?user=testuser&password=testpw

Authentication Information - Most systems will require a user id and password which may be supplied as part of the URL or when making the connection.

Connecting to a data source involves two steps:

  1. Load your JDBC driver (optional since JDBC 4.0/Java 6)
  2. Load the Java class for the driver.

    Class.forName("com.mysql.jdbc.Driver")
  3. Make a Connection
  4. A JDBC application connects to a database using either the DriverManager class or using the DataSource interface.

    Connecting with no user id/password:

    Connection con = DriverManager.getConnection("jdbc:mysql://localhost/mydb");

    Connecting with a user id and password:

    Connection con = DriverManager.getConnection("jdbc:mysql://localhost/mydb",
                                                         "testuser", "testpw");

    Connecting with a user id and password specified as properties:

    Properties prop = new Properties();
    prop.put("user", "testuser");
    prop.put("password", "testpw");
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost/mydb", prop);
    
Back to top

Create a Statement

Once a connection is established, the Connection object is used to send SQL statements to the database.

There are three types of statements: Statement, PreparedStatement, and CallableStatement.

Statement: Executes a SQL statement and generates a result. The example below creates a Statement object called s for the database connection con:

Statement s = con.createStatement();

PreparedStatement: Two common uses for a PreparedStatement:

  1. For dynamic SQL statements - Instead of building a SQL statement using string concatenation which is subject to SQL injection attacks and mistakes, a PreparedStatement provides a SQL statement outline with the ability to add parameters at run-time, usually based on user input. This is more secure as parameters are validated and escaped when inserted into the statement.
  2. For improved performance of similar queries - If the same query will be executed multiple times with different parameters by the database, using a PreparedStatement will often allow the database to avoid parsing and validating the query multiple times, which will result in faster execution.
PreparedStatement ps = con.prepareStatement("SELECT name FROM table WHERE id = ?");
ps.setInt(1);
ps.executeQuery();

CallableStatement: Executes a call to a database stored procedure.

CallableStatement cs = con.prepareCall("CALL myStoredProcedure");
Back to top

Execute Queries

A query to retrieve data uses the SQL SELECT statement. The syntax for the SELECT statement depends on the query you want to perform and the specific syntax used by your database (SQL syntax reference). Users often encounter challenges when trying to execute queries on different databases because each database has its own SQL variant (dialect) and functions and features supported. UnityJDBC allows users to query many databases in the same SQL query and will translate a SQL to the dialect used by the database.

Example

Statement s = con.createStatement();
ResultSet rs = s.executeQuery("SELECT name, address FROM Customer");

Modifying Data

To modify data, use an INSERT, UPDATE, CREATE or DELETE statement. Depending on your intentions, there are two variations that you need to know:

  1. The statement executed is an INSERT, UPDATE, CREATE or DELETE and no results are expected to be returned. The executeUpdate method should be used and will return a count of the number of rows affected:
  2. int rowcount = s.executeUpdate("UPDATE Emp SET salary=0");
    
  3. The statement executed is an INSERT which is creating a new record in a table whose primary key field is an autonumber field:
  4. rowcount = s.executeUpdate ("INSERT Product VALUES ('Prod. Name')",
                           Statement.RETURN_GENERATED_KEYS );
    ResultSet autoKeys = s.getGeneratedKeys();
    int keyValue = -1;
    if (autoKeys.next())
    	keyValue = autoKeys.getInt(1);
    
Back to top

Process Results

A ResultSet is an interface for navigating through a relation consisting of rows and columns. A cursor indicates the current row in the table, and the user can request column values for the current row. By default a ResultSet is not updateable and the cursor can only be moved forward using the next() method. If you need the ResultSet to be scrollable, a request should be made when executing the createStatement.

Example: next()

while ( rst.next() )
{	System.out.println(rst.getString("ename") + "," + rst.getDouble(2));
}

Note: The first call to next() will place the cursor on the first row.

Example: Scrollable ResultSet

// ResultSet will be scrollable and read-only
Statement s = con.createStatement (ResultSet.TYPE_SCROLL_INSENSITIVE,
                                    ResultSet.CONCUR_READ_ONLY);
ResultSet rs = s.executeQuery("SELECT eno, ename FROM Emp");

Example: Retrieving data from a particular type using getType()

getArray()
getBoolean()
getDate()
getFloat()
getObject()
getString()
//etc...

Note that all methods take as a parameter the column index in the ResultSet (indexed from 1) or the column name and return the requested type. If the type you requested is not the type returned by the database, Java will attempt to perform casting.


Close Connection

When you are finished using a Connection, call the close method to immediately release its resources. Closing a connection will automatically close its associated Statement and ResultSet.

Example

finally
{
    if (con != null)
    {
    	try
    	{
    		con.close();
    	}
    	catch (SQLException e)
    	{	System.out.println("Error during close: "+e);
    	}
    }
}
Back to top

Common Connection Errors

    "Driver not found" or "Driver class not registered"

    1. Make sure the correct location of the .jar file has been specified and that the JAR is in your CLASSPATH. Note: Some drivers require more than one .jar file, (eg. IBM DB2) so be sure to include all necessary files and locations.
    2. Check that you are spelling the driver's class name correctly.
    3. Always double check for case sensitive errors.

    Error:com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:

    The above error is the result of a communication failure.

    1. Make sure you have started your server.
    2. Check the IP address and port that MySQL is listening on.
    3. Make sure it's not resolving to the IPv6 localhost address instead of IPv4.
    4. Verify the address mysql is bound to; probably being 127.0.0.1 (only) and your settings in my.cnf file.
    5. Make sure you are using the correct JDBC connector.

    Out of memory error

    1. The memory that is available to the application is limited by the Java Virtual Machine (JVM) to ensure that applications don't use all available memory.
    2. If you retrieve large resultsets from the database, you may receive an error message indicating that the application does not have enough memory to store the data.
    3. You can increae the JVM memory using the parameters -mx500M -ms500M where 500M is 500 MB of memory (maximum and at startup).

Errors when connecting JDBC to MySQL server

    ERROR 2003 (HY000): Can't connect to MySQL server on _______ (10061).

    1. Make sure that the MySQL server has been started.
    2. Check "my.ini", make sure that you have a [client] section with port=xxxx.7
    3. Run a client with command "mysql -u root --port=xxxx" to specify the server's port number manually.

    ERROR 2005 (hy000) unknown mysql server host  'localhost'

    1. Try "ping localhost" to check if your localhost exists.
    2. If not, check "C:\Windows\System32\drivers\etc\hosts" file and make sure you have the following entry
      127.0.0.1 localhost
      This ensures that the "localhost" resolved to the IP address 127.0.0.1.

    ERROR 1046 (3D000): No database selected

    1. This error may be caused by an unspecified database. To tell MySQL which database to use, or to set the default databse try:
      USE database_name;

      If the database does not exist, you need to create it as

      CREATE DATABASE database_name;
      USE database_name;
      
    2. If that does not work, try using the fully-qualified name in the form of databaseName.tableName

    "No suitable driver found" (for Windows users) or "NullPointerException" (for Mac/Linux users)

    1. Make sure you copy the driver to the JDK extension directory.
    2. Try running the following command to connect to your JDBC driver
      java -cp .;path-to\mysql-connector-java-5.1.xx-bin.jar JdbcClassName
    3. For Tomcat, you might need to place the driver's .jar file into Tomcat's "Lib" directory.

    java.sql.SQLException: Access denied for user 'username'@'localhost' (using password: YES)

    1. This issue often arries when the incorrect username/password is being used. Double check for spelling/case sensative errors.

    com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'xxxx.xxxx' doesn't exist.

    1. Make sure the database tables you are referencing in your SQL statements actually exist.

    java.sql.SQLException: Column 'xxx' not found.

    1. Make sure you include column 'xxx' in your SELECT statement so it shows in your ResultSet.

    com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException

    1. This is a result of a syntax error. Check the manual that corresponds to your MySQL server to make sure you are using to correct syntax near or at line 'x'.

Errors When Connecting JDBC to Microsoft Access

    java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.

    1. Check your ODBC configuration. This will most likely under control panel -> ODBC.

    java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]. The Microsoft Office Access database engine cannot find the input table or query 'xxx'

    1. Make sure your database exists and that it is spelled correctly.
    2. Check your SQL statement and database tables to make sure you are referring to the correct databade and that it actually exists.

    [Microsoft][ODBC Microsoft Access Driver] SQL General Error.

    1. This error is most likely caused by inserting a record with a duplicated primary key.
Back to top

Helpful Links

  • SQL Syntax


  • References

    1. Oracle. "Establishing a Connection". JDBC Basics.
    2. Xyzws. "How to use JDBC Statement Object?". Java FAQ
    3. Oracle. "Processing SQL Statemtents with JDBC". JDBC Basics
    4. Oracle."DriverManager Class". JavaTM Platform Standard Ed. 7
    5. Oracle."Interface DataSource". JavaTM Platform Standard Ed. 7
    6. ServerFault. "Unable to connect to MySQL through JDBC connector...". Stack Exchange Inc.
    7. Hock-Chuan, Chua. "Common Error Messages JDK, MySQL, Tomcat, JDBC, Servlet...". Programming Notes. 2013.

    Need help?
    Unity Data will turn your data into business value. Contact us for a free consultation.