JDBC with MySQL

• JDBC is a set of classes and interfaces that support database access
• JDBC interfaces are found in java.sql package
• Individual database vendors provide implementations of these interfaces in the form of a JDBC driver
• JDBC drivers are classified into four types:


• Type 1 - uses JDBC-ODBC bridge which provides a gateway to the ODBC API
• Type 2 - the driver contains Java code that calls the native C or C++ methods provided by the database driver to talk to the database
• Type 3 - provides a client with a pure Java implementation of the JDBC API in which the driver uses a network protocol to talk to middleware on the server. The middleware, in turn, performs the actual database access
• Type 4 - uses network protocols built into the database engine to talk directly to the database using Java sockets


• Performance wise Type 2 and Type 4 are the only viable choices for a production application
• Though Type 2 drivers give a better performance, they lose out on portability
• JDBC is divided into two Java packages - java.sql and javax.sql
• JDBC supports two ways of getting access to a database connection


• through a JDBC data source
• using the JDBC driver manager


• Data source method is preferred, but it comes from the optional javax.sql package and hence support is not guaranteed, whereas driver manager method can be relied upon

Driver manager connectivity

• DriverManager class is available in java.sql package and maintains a list of implementations of the JDBC java.sql.Driver class and provides us with database connections based on the JDBC URLs provided
• A JDBC URL is in the form of jdbc:protocol:subprotocol
• The first task is to register the driver implementation with the JDBC DriverManager. There are two ways:
• specify the names of the drivers on the command line of the application using the jdbc.drivers property
  

  java -Djdbc.drivers=com.mysql.jdbc.Driver ApplicationClass

• explicitly load the class in the program by executing a new statement or a Class.forName()

  Class.forName("com.mysql.jdbc.Driver")

• After registration, you can ask the DriverManager for a Connection by calling the getConnection() method in the driver with the information identifying the required connection. The information includes a JDBC URL, user ID and password etc.

  Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "admin");

• Simplest forms of database access are SELECT, INSERT, UPDATE and DELETE
• A Statement represents any kind of SQL statement, a Statement instance can be created using a Connection instance

  Statement stmt = con.createStatement();

• The executeUpdate() method of the Statement instance passes the specified SQL on to the database for execution. It returns an integer

  stmt.executeQuery ("INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL)");

• The above method can be used to insert/delete/update rows, create new tables etc.
• Queries return information from the databse in the form of a ResultSet
• A ResultSet is an interface that represents zero or more rows matching a database query
• executeQuery() method of the Statement instance returns a ResultSet from the database (only one result set for MySQL, but JDBC also supports multiple result sets if the database supports it)

  ResutlSet rs = stmt.executeQuery("select * from pet");
  while(rs.next()) {
   for(int i=1; i<=numCols; i++) {
       System.out.println(rs.getName(i) + ": " + rs.getString(i));
   }
  }
  

• Until the first call to next(), the ResultSet does not point to any row. Each call to next() points the ResultSet to the subsequent row
• A scrollable ResultSet lets us move forward, backward, or to an arbitrary row. This can be specified while creating the statement

  Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

---

Following is a complete sample code that lets us access a MySQL database:

import java.sql.*;

public class Jdbc11 {
 public static void main(String[] args) {
     Connection con = null;
     Statement stmt = null;
     ResultSet rs = null;
     try {
         //Statement stmt;
         Class.forName("com.mysql.jdbc.Driver");
         String url = "jdbc:mysql://localhost:3306/mydb";
         con = DriverManager.getConnection(url, "root", "admin");
         System.out.println ("Database connection established");
         String query = "INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL)";
         String query2 = "select * from pet";
         stmt = con.createStatement ();
         stmt.executeUpdate(query);
         rs = stmt.executeQuery (query2);
         int numCols = rs.getMetaData().getColumnCount();
         while(rs.next()) {
             for(int i=1; i<=numCols; i++) {
                 System.out.println(rs.getName(i) + ": " + rs.getString(i));
             }
         }
     }
     catch(Exception e) {
         e.printStackTrace();
     }
     finally {
         if(con != null) {
             try {
                 rs.close();
                 stmt.close();
                 con.close ();
                 System.out.println ("Database connection terminated");
             }
             catch (Exception e) { /* ignore close errors */ }
         }
     }
 }
}

(Ref: http://www.oreilly.com/catalog/msql2/chapter/ch13.html)