JDBC

The Java Database Connectivity (JDBC)

• The Java Database Connectivity (JDBC) API is the industry standard for database-independent connectivity between the Java programming language and a wide range of databases – SQL databases and other tabular data sources, such as spreadsheets or flat files. The JDBC API provides a call-level API for SQL-based database access.
• JDBC technology allows you to use the Java programming language to exploit "Write Once, Run Anywhere" capabilities for applications that require access to enterprise data. With a JDBC technology-enabled driver, you can connect all corporate data even in a heterogeneous environment.

Java Data Objects (JDO)

• The Java Data Objects (JDO) API is a standard interface-based Java model abstraction of persistence. Application programmers can use JDO technology to directly store Java domain model instances into the persistent store (database).
• Benefits include ease of programming, application portability, database independence, high performance, and optional integration with Enterprise JavaBeans (EJB).

Processing SQL Statements with JDBC

In general, to process any SQL statement with JDBC, you follow these steps:

• Establishing a connection
  • establish a connection with the data source you want to use.
  • A data source can be a DBMS, a legacy file system, or some other source of data with a corresponding JDBC driver.
  • This connection is represented by a Connection object.
• Create a statement
  • A Statement is an interface that represents a SQL statement.
  • You execute Statement objects, and they generate ResultSet objects, which is a table of data representing a database result set.
  • You need a Connection object to create a Statement object.
    • stmt = con.createStatement();
  • There are three different kinds of statements:
    • Statement: Used to implement simple SQL statements with no parameters.
    • PreparedStatement: (Extends Statement.) Used for precompiling SQL statements that might contain input parameters.
    • CallableStatement: (Extends PreparedStatement.) Used to execute stored procedures that may contain both input and output parameters.
• Execute the query
  • To execute a query, call an execute method from Statement such as the following:
    • execute: Returns true if the first object that the query returns is a ResultSet object.
    • Use this method if the query could return one or more ResultSet objects.
    • Retrieve the ResultSet objects returned from the query by repeatedly calling Statement.getResultSet.
    • executeQuery: Returns one ResultSet object.
    • executeUpdate: Returns an integer representing the number of rows affected by the SQL statement. Use this method if you are using INSERT, DELETE, or UPDATE SQL statements.
    • ResultSet rs = stmt.executeQuery(query);
• Process the ResultSet object
  • You access the data in a ResultSet object through a cursor.
  • Note that this cursor is not a database cursor. This cursor is a pointer that points to one row of data in the ResultSet object.
  • Initially, the cursor is positioned before the first row. You call various methods defined in the ResultSet object to move the cursor.

    try {
      stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery(query);
      while (rs.next()) {
          String coffeeName = rs.getString("COF_NAME");
          int supplierID = rs.getInt("SUP_ID");
          float price = rs.getFloat("PRICE");
          int sales = rs.getInt("SALES");
          int total = rs.getInt("TOTAL");
          System.out.println(coffeeName + "\t" + supplierID +
                             "\t" + price + "\t" + sales +
                             "\t" + total);
      }
    }
    

• Close the connection
  • When you are finished using a Statement, call the method Statement.close to immediately release the resources it is using. When you call this method, its ResultSet objects are closed.

    } finally {
      if (stmt != null) { stmt.close(); }
    }
    

  • regardless of any SQLException objects thrown, by wrapping it in a finally block
  • JDBC throws an SQLException when it encounters an error during an interaction with a data source. See Handling SQL Exceptions for more information.
  • In JDBC 4.1, which is available in Java SE release 7 and later, you can use a try-with-resources statement to automatically close Connection, Statement, and ResultSet objects, regardless of whether an SQLException has been thrown.

    try (Statement stmt = con.createStatement()) {
    ...
    }