Java Database Connectivity (JDBC) [22]

$\bullet$ Most applications need access to a database

$\bullet$ Most databases are relational (SQL) tables of rows and columns

$\bullet$ Java has a built-in technique (JDBC) to access SQL databases

$\bullet$ Applications need 7 key lines of JDBC:

import java.sql.*;
public class jdbc {
  public static void main(String args[]) {
    try {
      // 1: load specific code to talk to mysql
      Class.forName("org.gjt.mm.mysql.Driver");

      // 2: establish network connection to server
      Connection con = DriverManager.getConnection(
        "jdbc:mysql://gold.mcs.csueastbay.edu:3306/SuppDB",// server name, port, database name
        "4311",                                            // user id
        "4311");                                           // password

      // 3: environment for queries
      Statement stmt = con.createStatement();

      // 4: SQL query of a supplier table
      stmt.execute("select * from S where STATUS > 10");

      // 5: rs encapsulates rows which match the query
      ResultSet rs = stmt.getResultSet();

      // 6: iterate through the rows with a cursor
      //    next() returns false when cursor goes beyond results
      while (rs.next()) {

         // 7: extract columns from the row pointed to by the cursor
         System.out.println(rs.getString("S_NO") + " " + rs.getInt("STATUS"));
      }
    } catch (Exception e) {System.out.println(e.getMessage());}
  }
}

Java Database Connectivity (JDBC) [23]

Uniform Resource Locator (URL):

SYNTAX: protocol//host:port/database

   String url = "jdbc:mysql://gold.mcs.csueastbay.edu:3306/SuppDB"

CLIENT driver must match specific SERVER:

SYNTAX: Class.forName(driverName)

   Class.forName("org.gjt.mm.mysql.Driver");
   Class.forName("ORACLE DRIVER");

Make a CONNECTION:

SYNTAX: DriverManager.getConnection(url,user,pass)

   Connection con = DriverManager.getConnection(url,"4311","4311");

Create a STATEMENT:

   Statement stmt = con.createStatement();

Execute a QUERY:

   ResultSet rs = stmt.executeQuery("select * from SP where STATUS >=20");

   int i = stmt.executeUpdate("update SP set QTY=400 where S_NO = 'S1'");

   if (stmt.execute("select * from SP where STATUS >=20")) 
     \\ true means SQL was a SELECT
     \\ false means SQL was a UPDATE/INSERT/DELETE

Get the RESULTS:

   ResultSet rs = stmt.getResultSet();

Get the METADATA (or DATA DICTIONARY):

   ResultSetMetaData md = rs.getMetaData();

Java Database Connectivity (JDBC) [24]

Get the NEXT (FIRST) record:

   while(rs.next())

Get the COLUMN COUNT

   int n = md.getColumnCount();

Get the COLUMN NAME or INDEX:

   String name = md.getColumnName(i);
   int i = rs.findColumn("QTY");

Get the COLUMN TYPE or WIDTH:

   String type = md.getColumnTypeName(i);
   int size = md.getColumnDisplaySize(i);

Get the DATA by INDEX or NAME:

   String s = rs.getString(i);
   String s = rs.getString("QTY");
   int j = rs.getInt(i);
   double d = rs.getDouble(i);
   Timestamp t = rs. getTimestamp(i);

CLOSE:

   rs.close();
   stmt.close();
   con.close();

TRANSACTION PROCESSING:

   try {
     con.setAutoCommit(false);       // not supported by mysql
     stmt = con.createStatement();
     stmt.executeUpdate("update SP set QTY=400 where S_NO = 'S1'");
     stmt.close();
     stmt = con.createStatement();
     stmt.executeUpdate("update S set CITY='London' where S_NO = 'S1'");
     stmt.close();
     con.commit();
     con.close();
   } catch (Exception e) {
       try { 
         con.rollback();
       } catch (Exception e1) {}
     }