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

Tags: ,

Updated: