JDBC

Here a list of points regarding JDBC, a topic on the exam. I copied some stuff from the book chapter 21.

• There are 5 interfaces in the JDK: Driver, Connection, CallableStatement, PreparedStatement and ResultSet.
• A database specific JAR-file contains the Driver’s implementation and the PreparedStatement’s implementation.
• CRUD stand for Create, Read, Update, Delete.
• JDBC URL has the following form: jdbc:<vendor>:<database specific conection details>
• Examples of url’s:

jdbc:mysql://localhost:3306
jdbc:oracle:thin:@123.123.123.123:1521:zoo
jdbc:postgresql://localhost/zoo
jdbc:derby:zoo

• To establish connection:

String url = "jdbc:mysql://localhost:3306/200?profileSQL=true";
Connection conn = DriverManager.getConnection(url);

• getConnection has a form with three params, you can supply username and password then. Not recommended.
• Better is to store username and password in configuration file, encrypted.
• If compiler asks for driver, you can give classpath via terminal:

java -cp "pathToDriver/derby.jar" Test.java

• PreparedStatement is superior to Statement, its parent, for the following reasons:
  • performance (faster on multiple queries)
  • security (SQL injection)
  • readability (because of ?)
  • future use - easier to adjust queries
• The query string needs to be passed to the prepareStatement method, not to execute(), executeQuery() or executeUpdate().
• If you do not pass it to the prepareStatement method, compile-time SQLException.
• If you pass query string to execute(), executeUpdate() or executeQuery(), an SQLException is thrown during runtime and not during compilation. Reason is that Statement, superinterface of preparedStatement, has method signatures for these methods that allow for the passing of a string.
• executeQuery() returns a ResultSet and works only for SELECT.
• executeUpdate() returns int, the number of affected rows. Works for INSERT, UPDATE, DELETE.
• execute() returns true if a ResultSet has been produced (SELECT) or false if not (INSERT, UPDATE, DELETE). Requires ps.getResultSet() to retrieve ResultSet, or ps.getUpdateCount to retrieve number of affected rows.
• Important: use try with resources to open Connection, PreparedStatement or CallableStatement and ResultSet.
• Do not open Connection etc before the try statement (and then naming them within the try statement). This might cause problems if the try statement is never reached because of some error.
• If you open a ResultSet, you will get a nested try-with-resources because of the bind variables that need to be set. This is the right way to do it.
• Closing a connection automatically closes Prepared/CallableStatement and ResultSet.
• Closing Prepared/CallableStatement automatically closes ResultSet.
• Retrieving a new ResultSet closes the previous one automatically.
• SQLExceptions can be thrown so best to put them in method signature.
• SQLExceptions will be runtime exceptions because the query string is not evaluated during compilation.
• The bind variables in querystrings can be set like this:

String sql = "INSERT INTO names VALUES(?,?,?)";

try(PreparedStatement ps = conn.prepareStatement(sql)){
  ps.setInt(1, key);
  ps.setString(3, name);
  ps.setInt(2,type);
  ps.executeUpate();  -- returns 1 as 1 row has been created
  ...
}

• There should be “throws SQLException” in the method signature. Watch for it in exam questions.
• If the number of bind variables does not match the number of variables set, RunTime SQLException.
• There are 6 set.. types:
  • setBoolean
  • setDouble
  • setInt – note the name, not ‘Integer’
  • setLong
  • setObject
  • setString
• You can use setObject for any value, those values will be autoboxed. You can cast them back but why should you use Object in the first place.
• ps.addBatch() and ps.executeBatch() help with mutiple inserts/updates. You can make a loop in which you adjust the bind variables, use ps.addBatch() within the loop and ps.executeBatch() after the loop.
• When working with a ResultSet, use if(rs.hasNext()) or while (rs.hasNext()). The initial cursor position is before, and not on, the first row.
• Trying to access a column name or index that doesn’t exist throws SQLException.
• Catching exceptions: e.getSQLState() returns information/error code provided by database vendor.
• e.getErrorCode() is a Java thing, might not be helpful.

CallableStatement

CallableStatement gets its own header because I’m less familiar with it.

• There are 4 types of stored procedures, IN, OUT, INOUT and no params. The OUT is somewhat typical, it is a return value but not in the form of a ResultSet.
• Query string is formulated differently:

String sql = "{call name_stored_procedure(?,?,..)}"
CallableStatement cs = conn.prepareCall(sql);
cs.setInt(1,27);
cs.setString(2, "SomeName");
cs.registerOutParameter(3, Type.BOOLEAN);

• Order in which you supply parameters not important, here 2 and 3 are swapped. You can supply them by index but also by name (this is not possible with PreparedStatement as far as I know).
• If a ResultSet is to be returned you use cs.executeQuery() and proceed normally.
• Use executeUpdate() for inserts, updates and deletes.
• The number of ?’s is the sum of the in- and out parameters.
• OUT parameters need to be registered with registerOutParameter(index, Type.WHATEVER).
• If you want to make clear there is an output value, add “?=” before “call” in the query string. It has no effect whatsoever but enhances readability.
• For a stored procedure with no IN- and only OUT, no ResultSet, use execute(). This example from book shows how to retrieve the OUT value:

String sql = "{?= call magic_number(?)}";  -- ?= is optional
try(var cs = conn.prepareCall(sql)){
  cs.registerOutParameter(1, Types.INTEGER);
  cs.execute();
  Integer outVal = cs.getInt("num");  -- I think using index 1 would also work
} 

• It is possible to give the same variable to an IN- and OUT value:

var sql = "{call double_number(?)};  -- could have included optional ?=
try(var cs = conn.prepareCall(sql)){
  cs.setInt(1,8);
  cs.registerOutParameter(1, Types.DOUBLE);
  cs.execute(); 
  Double outVal = cs.getInt("num"); -- or by index
}

End.

---

<

Previous Post

DIRRT and DOFSS annotations

>

Next Post

Collection, List, Set, Queue, Map