Java Fundamentals Tutorial: java.sql

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

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();

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();
}

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.

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
}

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, "[email protected]");
    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
}