Java Database Connectivity

I’ll start off with a small example and then give a couple of pointers regarding JDBC.

 

Example

This example connects to a MySQL database. I’m querying the superhero database which originally contains 2 rows:

id, name, first_name, last_name, good
'1','Superman','Clark','Kent','1'
'2','Batman','Bruce','Wayne','1'

The code queries the database for all the names of the superheros. It prints out all of these names.

Then, a new superhero is added. The number of rows affected is printed.

Finally the database is queried again and the names are printed (including the newly inserted record).

import java.sql.*;

public class UseJDBC {

    public static void main(String[] args) throws SQLException {
        String url = "jdbc:mysql://localhost:3306/demo";

        try(Connection conn = DriverManager.getConnection(url, "demouser", "demopass");
            Statement stmt = conn.createStatement())
        {

            ResultSet rs = stmt.executeQuery("select name from superhero");
            showColumnContent(rs, 1);


            int result = stmt.executeUpdate("insert into superhero (name, first_name, good) values ('Goku', 'Kakarot', 1)");
            System.out.println("result: rows affected = " + result);

            ResultSet rs2 = stmt.executeQuery("select name from superhero");
            showColumnContent(rs2, 1);

        }
    }

    private static void showColumnContent(ResultSet rs, int columnIndex) throws SQLException {
        while(rs.next()){
            System.out.println(rs.getString(columnIndex));
        }
    }
}

Output:

Superman
Batman
result: rows affected = 1
Superman
Batman
Goku

 

Extra ++

Now I’ll dive a little deeper in working with JDBC:

• JDBC has four key interfaces: Driver, Connection, Statement, Resultset
• A Driver creates a Connection
• On the Connection you can create a Statement
• When executing a statement it can return a ResultSet
• If you want to automatically close the sql Resources instantiate them in a try block

  try(Connection conn = DriverManager.getConnection(url, "demouser", "demouser");
      Statement stmt = conn.createStatement();
      PreparedStatement pstmt = conn.prepareStatement("select last_name from superhero where first_name = ?");
      ResultSet rs = stmt.executeQuery("select name from superhero"))
      {
           ...
      }

• The classes that implement these interfaces come from an external JDBC driver
• The drivers are specific to the type of database (MySQL, Postgress..)
• You can download a jar that contains these concrete classes
• You have to run your program with that new jar file on the classpath. java -cp /path/to/driver/jar MyProgram
• A JDBC url needs to be in a specific format: jdbc:ProductName://ConnectionDetails. For example:
  • jdbc:mysql://localhost:3306/database
  • when you do not specify localhost, localhost will be the default location
• When you look into a driver JAR file there is a directory META-INF/services. This directory has a file java.sql.Driver containing the path to the concrete class that implements the Driver interface. This way Java knows where to locate the driver implementation.
• You can create a statement like
  • conn.createStatement();
  • conn.createStatement(int ResultSetType, int ResultSetConcurrency);
    • Type: specify how you can navigate trough your ResultSet.
    • Concurrency: specify whether or not you can do updates on the ResultSet
• To execute a statement there are three methods:
  • execute():
    • supports: SELECT, INSERT, DELETE, UPDATE
    • returns: boolean (true for select, false for other)
  • executeQuery():
    • supports: SELECT
    • returns: ResultSet
  • executeUpdate():
    • supports: INSERT, DELETE, UPDATE
    • returns: int (the number of rows returned)
• You can get read results from a ResultSet with methods like getInt(int index), getString(int index) which take the column number as method argument
• The indexes of the columns in a ResultSet start from 1!
• DATE, TIME and TIMESTAMP are retrieved from a ResultSet with getDate(), getTime(), getTimeStamp() and return java.sql.Date, java.sql.Time, java.sql.TimeStamp. These can be converted to LocalDate, LocalTime and LocalDateTime with the methods toLocalDate(), toLocalTime() and toLocalDateTime();
• To navigate forward one row in a ResultSet call next() on the ResultSet object
  • If the ResultSetType allows it (eg. ResultSet.TYPE_SCROLL_INSENSITIVE) you can also use methods like beforeFirst(), afterLast(), previous(), last(), first(), absolute(int row), relative(int row). They all return a boolean except for afterLast() and beforeFirst() which return void.
• if rs is a ResultSet then rs.absolute(0) returns falls because cursor is positioned before the first row.
• You need to close database resources to avoid memory leaks
• When you close a Connection the Statement and ResultSet will be automatically closed.
• When executing on a statement the previous ResultSet of that statement is automatically closed.