Java Fundamentals Tutorial: java.sql

17. java.sql

Java Database Connectivity API (JDBC)

17.1. JDBC Overview

  • Industry standard for database-independent connectivity between Java applications and wide range of relational databases (RDBMS)
  • Technology split into:

    • API: Set of interfaces independent of the RDBMS
    • Driver: RDBMS-specific implementation of API interfaces (e.g. Oracle, DB2, MySQL, etc.)
  • Even though JDBC is RDBMS independent, SQL dialects (syntax) are generally not

Just like Java aims for "write once, run anywhere", JDBC strives for "write once, run with any database".

17.2. JDBC Drivers

Figure 9. JDBC Drivers

images/JDBC-Drivers.png


  • Type One

    • JDBC-ODBC Bridge plus ODBC driver
    • Depends on support for ODBC
    • Not portable
  • Type Two

    • JNI/Native Java driver
    • Requires DB native library
    • Not portable
  • Type Three

    • Pure Java to Middleware driver
    • Depends on Middleware server
    • Driver is portable, but the middleware server might not be
  • Type Four

    • Pure Java Driver
    • Talks directly to the RDBMS
    • Portable

17.3. Getting a JDBC Connection

  • Make sure JDBC driver is in CLASSPATH
  • Register JDBC driver

    • Class.forName("my.sql.Driver");
    • Run with -Djdbc.drivers=my.sql.Driver
    • System.setProperty("jdbc.drivers", "my.sql.Driver");
  • Construct Driver URL

    • jdbc:sub-protocol://database-locator
  • Connection connection = DriverManager.getConnection(url);

For example, to run your application against a MySQL RDBMS, you would run JVM with (all on one line):

+

java -classpath .:mysql-connector-java-5.1.5-bin.jar -Djdbc.drivers=com.mysql.jdbc.Driver my.MainClass args

The driver URL would be set to something like:

String url = "jdbc:mysql://server:3306/db?user=me&password=mysecret";

Finally, to get a JDBC Connection, do:

Connection con = DriverManager.getConnection(url);

Note that you can also pass the username and password at run time:

Connection con = DriverManager.getConnection("jdbc:mysql://server:3306/db", "me", "mysecret");

In a J2EE application server, JDBC connections can also be obtained using a javax.sql.DataSource obtained from a JNDI javax.naming.Context:

Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup(”java:comp/env/jdbc/MyDS");
Connection con = ds.getConnection();

17.4. Preparing a Statement

  • java.sql.Statement

    • Used for executing static SQL statement and returning the results it produces
    • Only one result per statement at any given time
  • java.sql.PreparedStatement

    • Precompiled SQL statement
    • Efficiently executed multiple times
    • Supports insertion + conversion + escaping of Java parameters into the statement
  • Release associated resources with close()

Connection con = DriverManager.getConnection(url);
try {
  Statement stmt = con.createStatement();
  try {
    ResultSet result = stmt.executeQuery("SELECT * FROM Customers");
    try {
      // process result set
    } finally {
      result.close();
    }
  } finally {
    stmt.close();
  }
} finally {
  con.close();
}
Connection con = DriverManager.getConnection(url);
try {
  PreparedStatement stmt = con.prepareStatement(
      "UPDATE Employees SET ExtraVactaion = ? WHERE SSN = ?");
  try {
    stmt.setInt(1, 10); // updates the first ?
    stmt.setString(2, "555-12-1234"); // updates the second ?
    stmt.executeUpdate();
  } finally {
    stmt.close();
  }
} finally {
  con.close();
}

17.5. Processing ResultSet

  • A table of data representing database result in response to a executeQuery()
  • Maintains a cursor pointing to the current row of data

    • Call boolean next() advance the cursor
  • Can be made scrollable and/or updatable
  • Provides getter methods for reading the current row (by column name or index)

    • Type sensitive: getString, getInt, getDate
    • Index starts at 1; names are case insensitive

Connection con = DriverManager.getConnection(url);
try {
  Statement stmt = con.createStatement();
  try {
    ResultSet resultSet = stmt.executeQuery(
      "SELECT CustomerID, CompanyName, ContactName FROM Customers");
    try {
      // for all rows
      while (resultSet.next()) {
        // print the columns of the current row (indexed by name)
        System.out.print(resultSet.getString("CustomerID"));
        System.out.print(", ");
        System.out.print(resultSet.getString("CompanyName"));
        System.out.print(", ");
        System.out.print(resultSet.getString("ContactName"));
        System.out.println();
      }
    } finally {
      resultSet.close();
    }
  } finally {
    stmt.close();
  }
} finally {
  con.close();
}

From JavaDoc: In general, using the column index is more efficient. For maximum portability, result set columns within each row should be read in left-to-right order, and each column should be read only once.

17.6. Using ResultSetMetaData

  • Used to get information about the types and properties of the columns in a ResultSet object:

    • getColumnCount
    • getColumnDisplaySize
    • getColumnLabel, getColumnName
    • getColumnType, getColumnTypeName
    • isAutoIncrement, isCaseSensitive, isNullable, isReadOnly, isSearchable, isSigned, isWritable

String customerID = "someid";
Connection con = DriverManager.getConnection(url);
try {
  // prepare the statement
  PreparedStatement stmt = con.prepareStatement(
    "SELECT * FROM Customers WHERE CustomerID=?");
  try {
    stmt.setString(1, customerID);
    ResultSet resultSet = stmt.executeQuery();
    try {
      if (resultSet.next()) {
        ResultSetMetaData metaData = resultSet.getMetaData();
        for (int c = 1; c <= metaData.getColumnCount(); c++) {
          System.out.print(metaData.getColumnName(c));
          System.out.print(": ");
          System.out.println(resultSet.getObject(c));
        }
      } else { // customer not found
        System.err.println(customerID + " not found");
      }
    } finally {
      resultSet.close(); // release resultSet
    }
  } finally {
    stmt.close(); // release the statement
  }
} finally {
  con.close(); // release the connection
}

17.7. Updates

  • Update actions (INSERT, UPDATE, DELETE) are triggered through

    int result = stmt.executeUpdate();
  • The integer result is set to:

    • the row count returned by INSERT, UPDATE, DELETE statements
    • 0 for SQL statements that return nothing

Connection con = DriverManager.getConnection(url);
try {
  PreparedStatement stmt = con.prepareStatement(
    "INSERT INTO Employee VALUES (?, ?, ?, ?, ?)");
  try {
    stmt.setString(1, "John");
    stmt.setString(2, "555-12-345");
    stmt.setString(3, "john@company.com");
    stmt.setInt(4, 1974);
    stmt.setInt(5, 7);
    int result = stmt.executeUpdate();
    if (result == 0) {
      System.out.println("Employee not added.");
    } else {
      System.out.println("Employee added.");
    }
  } finally {
    stmt.close(); // release the statement
  }
} finally {
  con.close(); // release the connection
}

Copyright © 2019 ProTech. All Rights Reserved.

Sign In Create Account

Navigation

Social Media