JDBC basic actions.

This is a basic set of database actions when using jdbc.

Issuing queries with prepared statements

/**
 * Will fetch only one result
 */
public String justQuery() throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = dataSource.getConnection();
            preparedStatement = connection.prepareStatement("SELECT a FROM abc");
            ResultSet resultSet = preparedStatement.executeQuery();
            String a = null;
            if(resultSet.next()) {
                a = resultSet.getString("a");
            }
            resultSet.close();
            return a;
        } finally {
            //Close the statements
            if(preparedStatement!=null) preparedStatement.close();
            //Relase the connection resource back to the pool
            if(connection!=null) connection.close();
        }
        
    }

Fetching more the one results.

/**
 * Fetching many results
 */
public List<Long> justQuery() throws SQLException {
        
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = dataSource.getConnection();
            preparedStatement = connection.prepareStatement("SELECT id FROM abc");
            ResultSet resultSet = preparedStatement.executeQuery();
            List<Long> ids = new ArrayList<>();
            if(resultSet.next()) {
                Long id = resultSet.getLong("id");
                ids.add(id);
            }
            resultSet.close();
            return ids;
        } finally {
            //Close the statements
            if(preparedStatement!=null) preparedStatement.close();
            //Relase the connection resource back to the pool
            if(connection!=null) connection.close();
        }
    }

Calling stored procedures with callable statements

public long callProcedure(String something) throws SQLException {
        
        Connection connection = null;
        CallableStatement callableStatement = null;
        try {
            connection = dataSource.getConnection();
            callableStatement = connection.prepareCall("CALL doSomething(?,?)");
            callableStatement.setString(1, something);
            callableStatement.registerOutParameter(2, Types.NUMERIC);
            callableStatement.execute();
            Long lastInsertId = callableStatement.getLong("resultvar");
            return lastInsertId;
        } finally {
            if(callableStatement!=null) callableStatement.close();
            //Release back to the connection pool
            if(connection!=null) connection.close();
        }   
    }

Batch processing

public void doBatchOpeation(List<Long> userIds) throws SQLException {
        Connection connection = null;
        Statement batchStmt = null;
        try {
            connection = DatasourceProvider.getConnection();
            batchStmt = connection.createStatement();
            for(Long userId:userIds) {
                batchStmt.addBatch("UPDATE user SET active=true WHERE userid="+userId);
            }
            batchStmt.executeBatch();
        } finally {
            if(batchStmt!=null) batchStmt.close();
            if(connection!=null) connection.close();
        }
    }

And last but not least transactions!!!

public void doTransaction(Long userId,BigDecimal ammount) throws SQLException{

        Connection connection = null;
        PreparedStatement insertStatemt = null;
        PreparedStatement updateStatement = null;
        
        try {
            connection = dataSource.getConnection();
            connection.setAutoCommit(false);

            insertStatemt = connection.prepareStatement("INSERT INTO transfers (USERID,AMMOUNT) VALUES (?,?)");
            insertStatemt.setLong(1, userId);
            insertStatemt.setBigDecimal(2, ammount);
            insertStatemt.executeUpdate();

            updateStatement = connection.prepareStatement("UPDATE user SET tnum=tnum+1 WHERE userid=?");
            updateStatement.setLong(1, userId);
            updateStatement.executeUpdate();
            
            //Don't forget to commit
            connection.commit();
            
        } catch (SQLException e) {
            if(connection!=null) {
                connection.rollback();
            } else throw e;
        } finally {
            if(insertStatemt!=null) insertStatemt.close();
            if(updateStatement!=null) updateStatement.close();
            if(connection!=null) {
                //Setting autocommit true before sending connection back to the pool
                connection.setAutoCommit(true);
                //And release the connection back to the pool
                connection.close();
            }
        }
    }

All in all using jdbc has a lot of boilerplate and also you have to be carefull. While many other frameworks like spring provide tools such as a transaction manager or jdbc template, when using jdbc you have to do everything on your own. You are responsible for everything, for example releasing the connections back to the pool or setting the connection back to autocommit mode.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s