An introduction to using JDBC to connect with MySQL in Java

Before starting with the examples and code snippets, there are some things we have to take into account when creating our connection-related classes:

1. Keep in mind that you’ll need to create a new connection for each one of your query statements. The default connection class is blocking, which means that you cannot make a query statement until the previous one has been finished and closed.
Connections are meant to be closed after a statement has been processed and there are no other statements waiting in line to be executed (connections may be hang or timed out if you want to reuse it later without recreating it).
By the way, if you are only running selection queries and you don’t need writing access, use the setReadOnly method to set the value to true. This way, as the documentation states, a connection in read-only mode serves as a hint to the driver to enable database optimizations.

2. Query timeouts. I can’t emphasize enough how important is to adapt any app by setting up the correct timeouts (not only with Database connections but any other network calls and long tasks). Are you making short queries that rely more on the connection and network rather than the brute force required for the query (i.e simple select or indexed queries in mobile applications)? Or is your app some kind of panel or data displayer/retriever that deals with larger and more complex queries and datasets? By using the setQueryTimeout method in the connection you can override the default timeout so you can handle errors or unexpected behaviour/outputs better. And don’t worry about overwhelming the DB with timed out queries, because the driver will try to cancel them before launching the SQLTimeoutException.

3. Take advantage of the AutoClosable interfaces. If you use the not-so-new-Java-7 try with resources block, it is no longer required to nest a try-catch block inside of your finally or catch block to close the connections and statements. Same applies with the obtained ResultSets. An easy example:

try (PreparedStatement ps = con.prepareStatement(getQueryStatement())) {
    ps.setQueryTimeout(QUERY_TIMEOUT_SECONDS);
    try (ResultSet rs = ps.executeQuery()) {
        handleResult(rs);
    }
} catch (Exception e) {
    log.error("Exception while executing "+ this.getClass().getSimpleName() +":", e);
}

The ResultSet will be closed after calling the handleResult method the PreparedStatement will be also automatically closed after the ResultSet try block (before the Exception catch). Really helpful and clean if you ask me!

Import MySQL JDBC connector

Add the following Maven dependency to import the standard JDBC implementation driver for MySQL databases:

 
    mysql
    mysql-connector-java
    5.1.37

Opening a new Connection

You can open a new connection by using the following method:

public Connection getConnection(boolean isReadOnly) {
    Connection connection;
    try {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        try {
            connection = DriverManager.getConnection("mysql_url", "mysql_username", "mysql_password");
            if (isReadOnly) connection.setReadOnly(true);
        } catch (Exception e) {
            log.error("Exception while opening connection: ", e);
        }
    } catch (Exception e) {
        log.error("Exception while instantiating JDBC driver class: ", e);
    }
    return connection;
}

Remember that is very important to register the driver class manually, especially if you are using more than one sql-related driver (Hadoop Hive or Mongo drivers for example). I realized that when using different jdbc implementations the system might suffer some kind of race condition where the only driver registered is the one that is used in first place, and the second will crash if you don’t register both of them manually.

Close the connection after you’ve finished using it.

Executing SELECT

Running select statements is not as simple as it seems because when you obtain the ResultSet you’ll need to map each row to an object. I’ll soon write a post showing how to use serializers and reflection to map resultsets into objects with a single method instead of writing a mapper for each class, but let’s keep it simple now and see how to make a simple select statement that maps the ResultSet to a list of a simple java object list.

Imagine the following simple class:

public class SQLObject {
    
    public String field1;
    public String field2;    

    public SQLObject(String f1, String f2) {
        field1=f1;
        field2=f2;
    }
}

You can use this method to execute a simple select statement:

public List<SQLObject> select(String statement) {
    List<SQLObject> resultList = null;
    try (Connection connection = getConnection();
         PreparedStatement ps = connection.prepareStatement(statement)) {
        ps.setQueryTimeout(QUERY_TIMEOUT_SECONDS);
        try (ResultSet resultSet = ps.executeQuery()) {
            resultList = mapResultSetToMyObject(resultSet);
        } catch (Exception e) {
            e.printStackTrace();
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return resultList;
}

And the mapResultSetToMyObject method to extract data from the ResultSet:

private List<SQLObject> mapResultSetToMyObject(ResultSet resultSet) throws SQLException {
    List<SQLObject> resultList = new ArrayList<>();
    while (resultSet.next()) {
        try {
            String s1= resultSet.getString("my_string1_label");
            String s2= resultSet.getString("my_string2_label");
            MyObject tmpObj = new MyObject(s1, s2);
            resultList.add(tmpObj);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    return resultList;
}

Executing UPDATE

You can use this method to run an update statement and get the number of modified rows:

public int executeUpdate(String statement) {
    int modifiedRows = -1;
    try (Connection connection = getConnection();
         PreparedStatement ps = connection.prepareStatement(statement)) {
        ps.setQueryTimeout(QUERY_TIMEOUT_SECONDS);
        modifiedRows = ps.executeUpdate();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return modifiedRows;
}

Executing INSERT

This method will execute an insert statement and return the number of inserted rows:

public int executeInsert(String statement) {
    int insertedRows = -1;
    try (Connection connection = getConnection();
         PreparedStatement ps = connection.prepareStatement(statement)) {
        ps.setQueryTimeout(QUERY_TIMEOUT_SECONDS);
        insertedRows = ps.executeUpdate();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return insertedRows;
}

If you are using an indexed table and want to obtain the inserted keys, you can use the following method:

public long executeInsertWithId(String statement) {
    long insertId = -1;
    try (Connection connection = getConnection();
         PreparedStatement ps = connection.prepareStatement(statement, 
Statement.RETURN_GENERATED_KEYS)) {
        ps.setQueryTimeout(QUERY_TIMEOUT_SECONDS);
        int affectedRows = ps.executeUpdate();
        if (affectedRows > 0) {
            try (ResultSet generatedKeys = ps.getGeneratedKeys()) {
                if (generatedKeys.next()) {
                    insertId = generatedKeys.getLong(1);
                }
                else {
                    System.out.println("Failed getting the inserted id");
                }
            }
        } else {
            System.out.println("Insert statement failed, no ID obtained.");
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return insertId;
}

Keep in mind that this method will return only the first id. If you are inserting more than one row, just change the signature long for a Long collection instead and iterate over the ResultSet of generated keys.


That’s it. This is the first post of a series of three. In the next one I will explain how to create connection pools and in the third post I’ll show how to use reflection to use a common mapper for all of your select statements.

If you have any question, correction or anything to add to this post, please feel free to leave a comment or get in touch!

Leave a Reply

Your email address will not be published. Required fields are marked *


*