JDBC 101-ish

JDBC is an API for connecting and executing queries on a database.
Relatively low-level, it’s the foundation of most ORMs and other high-level data access libraries on the JVM.

What JDBC can do

• Query Database
• Query Database Metadata
• Update Database
• Perform Database Transactions

Connecting to a Database

1. Registering the Driver import mysql-connector-java dependency

    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>6.0.6</version>
    </dependency>
   

   let’s register the driver using the Class.forName() method, which dynamically loads the driver class:

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

2. Creating the Connection
   General format of the JDBC URL: jdbc:<subprotocol>:<subname>, see some examples:
   • jdbc:postgresql://localhost/test
   • jdbc:oracle://127.0.0.1:44000/test
   • jdbc:microsoft:sqlserver://himalaya:1433

    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/myDb", "user1", "pass");
   

Excuting SQL statements

1. Statement
   Statement is an interface

    Statement stmt = con.createStatement();
   

   • executeQuery() for SELECT instructions
   • executeUpdate() for updating the data or the database structure
   • execute() can be used for both cases above when the result is unknown

2. PreparedStatement

   PreparedStatement objects contain precompiled SQL sequences. They can have one or more parameters denoted by a question mark

    String updatePositionSql = "UPDATE employees SET position=? WHERE emp_id=?";
    PreparedStatement pstmt = con.prepareStatement(updatePositionSql);
       
    pstmt.setString(1, "lead developer");
    pstmt.setInt(2, 1);
       
    int rowsAffected = pstmt.executeUpdate();
   

3. CallableStatement
   The CallableStatement interface allows calling stored procedures.

    String preparedSql = "{call insertEmployee(?,?,?,?)}";
    CallableStatement cstmt = con.prepareCall(preparedSql);
       
    cstmt.setString(2, "ana");
    cstmt.setString(3, "tester");
    cstmt.setDouble(4, 2000);
       
    cstmt.registerOutParameter(1, Types.INTEGER);
       
    cstmt.execute();
    int new_id = cstmt.getInt(1);
   

ResultSet

Navigate this type of ResultSet, we can use one of the methods:

• first(), last(), beforeFirst(), beforeLast() – to move to the first or last line of a ResultSet or to the line before these
• next(), previous() – to navigate forward and backward in the ResultSet
• getRow() – to obtain the current row number
• moveToInsertRow(), moveToCurrentRow() – to move to a new empty row to insert and back to the current one if on a new row
• absolute(int row) – to move to the specified row
• relative(int nrRows) – to move the cursor the given number of rows

CRUD

• updateRow() – to persist the changes to the current row to the database
• insertRow(), deleteRow() – to add a new row or delete the current one from the database
• refreshRow() – to refresh the ResultSet with any changes in the database
• cancelRowUpdates() – to cancel changes made to the current row

   Statement updatableStmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
   ResultSet updatableResultSet = updatableStmt.executeQuery(selectSql);
    
   updatableResultSet.moveToInsertRow();
   updatableResultSet.updateString("name", "mark");
   updatableResultSet.updateString("position", "analyst");
   updatableResultSet.updateDouble("salary", 2000);
   updatableResultSet.insertRow();  

Get Metadata

metadata is the data about database itself.

DatabaseMetaData dbmd = con.getMetaData();
ResultSet tablesResultSet = dbmd.getTables(null, null, "%", null);
while (tablesResultSet.next()) {
    LOG.info(tablesResultSet.getString("TABLE_NAME"));
}

A method to find information about a certain resultSet

ResultSetMetaData rsmd = rs.getMetaData();
int nrColumns = rsmd.getColumnCount();
 
IntStream.range(1, nrColumns).forEach(i -> {
    try {
        LOG.info(rsmd.getColumnName(i));
    } catch (SQLException e) {
        e.printStackTrace();
    }
});

References

• Introduction to JDBC
• JDBC with Groovy
• A Comprehensive OCPJP 8 Certification Guide