JDBC  

Jussi  Pohjolainen  Tampere  University  of  Applied  Sciences  

JDBC  Intro  

•  Java  Database  Connec@vity  in  Java  since  1.1  •  API  in  Java  for  accessing  Databases  •  PlaDorm  independent,  Database  independent  

Drivers  

•  To  access  a  database,  you  need  a  driver  •  To  code  in  Java  is  always  the  same,  changing  the  driver  changes  the  connec@on  to  different  database.  

•  Lot’s  of  drivers  for  different  databases:  MySQL,  Text,  MS  SQL  Server..  

Driver  Types  •  JDBC  Drivers  are  divided  into  four  categories  

–  Type  1  that  calls  na@ve  code  of  the  locally  available  ODBC  driver.  

–  Type  2  that  calls  database  vendor  na.ve  library  on  a  client  side.    –  Type  3,  the  pure-­‐java  driver  that  talks  with  the  server-­‐side  middleware  that  then  talks  to  database.  

–  Type  4,  the  pure-­‐java  driver  that  uses  database  na@ve  protocol.    •  In  most  cases  you  can  access  the  same  database  with  four  

different  type  of  drivers  •  List  of  drivers:  

–  hQp://developers.sun.com/product/jdbc/drivers  

To  use  JDBC  

1.  Register  driver  2.  Access  database  3.  Do  some  SQL  magic  4.  Handle  result  5.  Close  the  connec@on  

1.  Register  the  Driver  try {

// The driver string here is given you by the

// driver documentation.

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

}

catch(ClassNotFoundException e) {

System.out.println(”Can’t find the driver!");

}

2.  Connect  to  Database  try {

Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");

Connection conn = DriverManager.getConnection(

"jdbc:odbc:mydatabasename", ”userlogin", ”password");

}

catch(SQLException e) {

}

catch(ClassNotFoundException e) {

}

3.  Some  SQL  Magic  try {

Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");

Connection conn = DriverManager.getConnection(

"jdbc:odbc:mydatabasename", ”userlogin", ”password");

Statement statement = conn.createStatement();

statement.executeUpdate("DELETE FROM employees WHERE id<20");

}

catch(SQLException e) { }

catch(ClassNotFoundException e) { }

5.  Close  connec@on  try {

Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");

Connection conn = DriverManager.getConnection(

"jdbc:odbc:mydatabasename", ”userlogin", ”password");

Statement statement = conn.createStatement();

statement.executeUpdate("DELETE FROM employees WHERE id<20");

statement.close();

conn.close();

}

catch(SQLException e) { }

catch(ClassNotFoundException e) { }

Handle  Result?  

•  In  previous  example,  the  result  of  the  SQL  was  boolean  value  (we  did  not  handle  it).  

•  If  SQL  is  select,  then  you  can  retrieve  the  results  

•  Use  ResultSet  object.  ResultSet  holds  the  result  of  your  SQL  query  

•  You  can  navigate  in  result  set  

4.  Handling  Results  try {

Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver"); Connection conn = DriverManager.getConnection( "jdbc:odbc:mydatabasename", ”userlogin", ”password"); Statement statement = conn.createStatement();

ResultSet rs = statement.executeQuery("SELECT * FROM employees"); while(rs.next()) { System.out.println(rs.getString("someColumn")); } statement.close(); conn.close(); } catch(SQLException e) { } catch(ClassNotFoundException e) { }

ResultSet  

rs.next();  rs.next();  rs.next();  //  Prints  “Williams”  System.out.println(rs.getString(“Last  Name”));  

ResultSetMetaData  •  Don’t  know  column  names?  Or  amount?  •  You  can  check  these  using  ResultSetMetaData  •  Example  usage:       ResultSetMetaData rsmd = rs.getMetaData(); int numCols = rsmd.getColumnCount (); while (rs.next()) { for (int i=1; i<=numCols; i++) { System.out.println(rs.getString(i)); } }

Transac@ons  

•  If  you  want  to  commit  several  sql  –  commands  into  one:  – conn.setAutoCommit(false); – // do some sql – conn.commit(); – conn.rollback();

Prepared  Statements  

•  Prepared  statement  or  parameterized  statement  is  a  feature  used  to  execute  the  same  or  similar  database  statements  repeatedly  with  high  efficiency.  

•  Example  Connection con = connect(); String sql = "UPDATE table1 set one = ?, two = ?"; PreparedStatement preStmt = con.prepareStatement(sql); preStmt.setInt(1, 123); preStmt.setString(2, "myNewValue2"); preStmt.executeUpdate();

Scrollable  Resultset  Statement stmt =

con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,

ResultSet.CONCUR_READ_ONLY);

ResultSet srs = stmt.executeQuery(SQLQuery);

Values  

•  TYPE_FORWARD_ONLY  (default)  •  TYPE_SCROLL_INSENSITIVE  •  TYPE_SCROLL_SENSITIVE  –  all  updates  happens  immediately  

•  CONCUR_READONLY  •  CONCUR_UPDATABLE  

Methods  for  Scrollable  ResultSet  

•  next()  •  previous()  •  beforeFirst()  •  aierLast()  •  absolute(int  x)  

Update  Result  

•  It’s  possible  to  update  the  result  using  methods  – updateString()  – updateInt()  – updateFloat()..  

•  Once  updated,  call  updateRow(),  which  will  move  the  result  to  database  

Update   while(rs.next()){

System.out.println(rs.getString("Firstname"));

}

rs.previous();

rs.updateString("Firstname", args[0]);

rs.updateRow();

Adding  a  Row  

rs.moveToInsertRow();

rs.updateString("firstname", "Jack");

rs.updateString("lastname", "Smith");

rs.updateInt("idnumber", 20);

rs.insertRow();

Dele@ng  a  Row  

•  rs.last(); •  rs.deleteRow();