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

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

  2. Creating the Connection
    General format of the JDBC URL: jdbc:<subprotocol>:<subname>, see some examples:
    • jdbc:postgresql://localhost/test
    • jdbc:oracle://
    • 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);
     int new_id = cstmt.getInt(1);


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


  • 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.updateString("name", "mark");
   updatableResultSet.updateString("position", "analyst");
   updatableResultSet.updateDouble("salary", 2000);

Get Metadata

metadata is the data about database itself.

DatabaseMetaData dbmd = con.getMetaData();
ResultSet tablesResultSet = dbmd.getTables(null, null, "%", null);
while (tablesResultSet.next()) {

A method to find information about a certain resultSet

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


Tags: ,