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.