APPENDIX B: JAVA DATABASE CONNECTION [48]

Uniform Resource Locator (URL):

SYNTAX: protocol//host:port/database

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

CLIENT driver must match specific SERVER:

SYNTAX: Class.forName(driverName)

   Class.forName("org.git.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();

JDBC [49]

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) {}
     }