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

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

Connecting to a data source involves two steps:

1. Load your JDBC driver (optional since JDBC 4.0/Java 6)

Load the Java class for the driver.

Class.forName("com.mysql.jdbc.Driver")

2. Make a Connection

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);

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 s = con.createStatement();

PreparedStatement ps = con.prepareStatement("SELECT name FROM table WHERE id = ?");
ps.setInt(1);
ps.executeQuery();

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

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:

int rowcount = s.executeUpdate("UPDATE Emp SET salary=0");

2. The statement executed is an INSERT which is creating a new record in a table whose primary key field is an autonumber field:

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);

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()
getInt()
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. The easiest technique is to open the connection in the try declaration so that it will be closed automatically at the end of the block. You can also close the connection directly in the finally clause of the try block.

Examples

try (Connection con = DriverManager.getConnection(url, uid, pw);)
{
	// Database access code
    
	// Connection will be automatically closed at the end of the try block
}

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

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.
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.

Helpful Links

References

1. Oracle. "Establishing a Connection". JDBC Basics.
2. Oracle. "Processing SQL Statemtents with JDBC". JDBC Basics

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