Chapter 21 JDBC Notes

Question 1

JDBC five interfaces

Answer 1

• Driver: Establishes a connection to the database
• Connection: Sends commands to a database
• PreparedStatement: Executes a SQL query
• CallableStatement: Executes commands stored in the database
• ResultSet: Reads results of a query

Question 2

Java Persistence API (JPA):
Accesses data through Java objects using a concept called object‐relational mapping (ORM). The idea is that you don’t have to write as much code, and you get your data in Java objects. JPA is not on the exam, and therefore it is not covered in this
chapter.

Question 3

JDBC example:

1. DriverManager
2. Connection
3. PreparedStatement
4. ResultSet

Answer 3

public class MyFirstDatabaseConnection {
  public static void main(String[] args) throws SQLException {
    String url = "jdbc:derby:zoo";
    try (Connection conn = DriverManager.getConnection(url);
    PreparedStatement ps = conn.prepareStatement("SELECT name FROM animal");
    ResultSet rs = ps.executeQuery()) {
      while (rs.next())
        System.out.println(rs.getString(1));
    }
  }
}

1. Get Connection :
   Connection conn = DriverManager.getConnection(url);
2. Prepare statement : (sql statement is mandatory)
   PreparedStatement ps = conn.prepareStatement("SELECT name FROM animal");
3. Get ResultSet :
   ResultSet rs = ps.executeQuery())

Question 4

COMPILING WITH MODULES

Answer 4

The JDBC classes are all in the module java.sql.

When working with SQL, you need the java.sql module and
import java.sql.*.

That said, if you do want to use JDBC code with modules, remember to update your module‐info file to include the following:
requires java.sql;

Question 5

The JDBC URL format

Answer 5

JDBC URL has a variety of formats. They have three parts in common.

1. Protocal : jdbc
2. Subprotocal : Product/Vendor name, ex: derby, mysql, or postgres
3. Subname : Database specific connection details
4. Colon separators (:)

ex :

jdbc:postgresql://localhost:5432/zoo

Question 6

> [!Note]
The subname typically contains information about the database such as the location and/or name of the database.
The syntax varies.
You need to know about the three main parts. You don’t need to memorize the subname formats.

Question 7

JDBC URL example:

jdbc:derby:zoo
jdbc:postgresql://localhost/zoo
jdbc:oracle:thin:@123.123.123.123:1521:zoo
jdbc:mysql://localhost:3306
jdbc:mysql://localhost:3306/zoo?profileSQL=true

Answer 7

• First line It starts with jdbc and then comes the subprotocol derby, and it ends with the subname, which is the database name. The location is not required, because Derby is an in‐memory database.
• the port is optional when using the default location.

Question 8

Do you see what is wrong with each of the following?

jdbc:postgresql://local/zoo
jdbc:mysql://123456/zoo
jdbc;oracle;thin;/localhost/zoo

Answer 8

• The first one uses local instead of localhost. The literal localhost is a specially defined name. You can’t just make up a name. Granted, it is possible for our database server to be named local, but the exam won’t have you assume names. If the database server has a special name, the question will let you know it.
• The second one says that the location of the database is 123456. This doesn’t make sense. A location can be localhost or an IP address or a domain name. It can’t be any random number.
• The third one is no good because it uses semicolons ( ;) instead of colons ( :).

Question 9

GETTING A DATABASE CONNECTION

Answer 9

There are two main ways to get a Connection: DriverManager or DataSource.

DriverManager is the one covered on the exam.

The DriverManager class is in the JDK, as it is an API that comes with Java. It uses the factory pattern, which means that you call a static method to get a Connection, rather than calling a constructor. The factory pattern means that you can get any implementation of the interface when calling the method. The good news is that the method has an easy‐to‐remember name— getConnection().

Question 10

To get a Connection from the Derby database, you write the following:

import java.sql.*;
public class TestConnect {
    public static void main(String[] args) throws SQLException {
        Connection conn = DriverManager.getConnection("jdbc:derby:zoo");
        System.out.println(conn);
    }
}

Answer 10

Exception in thread "main" java.sql.SQLException:
No suitable driver found for jdbc:derby:zoo
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:702)
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:251)
at connection.TestConnect.main(TestConnect.java:9)

java -cp "<path_to_derby>/derby.jar" TestConnect.java
This time the program runs successfully and prints something like the following:

org.apache.derby.impl.jdbc.EmbedConnection40@1372082959
(XID = 156), (SESSIONID = 1), (DATABASE = zoo), (DRDAID = null)

Question 11

There is also a signature that takes a username and password.

import java.sql.*;
public class TestExternal {
    public static void main(String[] args) throws SQLException {
        Connection conn = DriverManager.getConnection( "jdbc:postgresql://localhost:5432/ocp-book", "username", "Password20182");
        System.out.println(conn);
    }
}

Answer 11

• Notice the three parameters that are passed to getConnection().
• The first is the JDBC URL that you learned about in the previous section.
• The second is the username for accessing the database,
• and the third is the password for accessing the database. It should go without saying that our password is not Password20182. Also, don’t put your password in real code. It’s a horrible practice. Always load it from some kind of configuration, ideally one that keeps the stored value encrypted.

Question 12

You might see Class.forName() in code. It was required with older drivers (that were designed for older versions of JDBC) before getting a Connection. It looks like this:

public static void main(String[] args) throws SQLException, ClassNotFoundException {
    Class.forName("org.postgresql.Driver");
    Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/ocp-book", "username", "password");
}

Class.forName() loads a class before it is used. With newer drivers, Class.forName() is no longer required.

Question 13

In Java, you have a choice of working with a Statement, PreparedStatement, or CallableStatement.
The latter two are subinterfaces of Statement,

Answer 13

• Statement
• PreparedStatement
• CallableStatement

Question 14

While it is possible to run SQL directly with Statement, you shouldn’t. PreparedStatement is far superior for the following reasons:

Answer 14

• Performance: In most programs, you run similar queries multiple times. A PreparedStatement figures out a plan to run the SQL well and remembers it.
• Security: As you will see in Chapter 22, “Security,” you are protected against an attack called SQL injection when using a PreparedStatement correctly.
• Readability: It’s nice not to have to deal with string concatenation in building a query string with lots of parameters.
• Future use: Even if your query is being run only once or doesn’t have any parameters, you should still use a PreparedStatement. That way future editors of the code won’t add a variable and have to remember to change to PreparedStatement then.

Using the Statement interface is also no longer in scope for the JDBC exam, so we do not cover it in this book. In the following sections, we will cover obtaining a PreparedStatement, executing one, working with parameters, and running multiple updates.

Question 15

OBTAINING A PREPAREDSTATEMENT

Answer 15

Example :

try (PreparedStatement ps = conn.prepareStatement("SELECT * FROM exhibits")) {
// work with ps
}

Passing a SQL statement when creating the object is mandatory.

try (var ps = conn.prepareStatement()) { // DOES NOT COMPILE
}

There are overloaded signatures that allow you to specify a ResultSet type and concurrency mode. On the exam, you only need to know how to use the default options, which processes the results in order.

Question 16

EXECUTING A PREPAREDSTATEMENT

Answer 16

• Modifying Data with int executeUpdate()
• Reading Data with ResultSet executeQuery()
• Processing Data with boolean execute()
  
  • if SELECT sql statement return true, get ResultSet via ps.getResultSet()
  • else return false. get int via ps.getUpdateCount()

Question 17

Modifying Data with executeUpdate()
Here’s an example of all three update types:

10: var insertSql = "INSERT INTO exhibits VALUES(10, 'Deer', 3)";
11: var updateSql = "UPDATE exhibits SET name = '' " +
12: "WHERE name = 'None'";
13: var deleteSql = "DELETE FROM exhibits WHERE id = 10";
14:
15: try (var ps = conn.prepareStatement(insertSql)) {
16: int result = ps.executeUpdate();
17: System.out.println(result); // 1
18: }
19:
20: try (var ps = conn.prepareStatement(updateSql)) {
21: int result = ps.executeUpdate();
22: System.out.println(result); // 0
23: }
24:
25: try (var ps = conn.prepareStatement(deleteSql)) {
26: int result = ps.executeUpdate();
27: System.out.println(result); // 1
28: }

Answer 17

int executeUpdate()

• Line 15 creates the insert statement,
• and line 16 runs that statement to insert one row. ps.executeUpdate()
• The result is 1 because one row was affected.
• Line 20 creates the update statement,
• and line 21 checks the whole table for matching records to update. ps.executeUpdate()
• Since no records match, the result is 0.
• Line 25 creates the delete statement,
• and line 26 deletes the row created on line 16. ps.executeUpdate()
• Again, one row is affected, so the result is 1.

Question 18

Reading Data with executeQuery()

30: var sql = "SELECT * FROM exhibits";
31: try (var ps = conn.prepareStatement(sql);
32: ResultSet rs = ps.executeQuery() ) {
33:
34: // work with rs
35: }

Answer 18

ResultSet executeQuery()

• On line 31, we create a PreparedStatement for our SELECT query.
• On line 32, we actually run it. Since we are running a query to get a result, the return type is ResultSet.

Question 19

Processing Data with execute()

boolean isResultSet = ps.execute();
if (isResultSet) {
try (ResultSet rs = ps.getResultSet()) {
System.out.println("ran a query");
}
} else {
int result = ps.getUpdateCount();
System.out.println("ran an update");
}

Answer 19

If the PreparedStatement refers to sql that is a SELECT, the boolean is true and we can get the ResultSet. If it is not a SELECT, we can get the number of rows updated.

Question 20

What do you think happens if we use the wrong method for a SQL statement? Let’s take a look.

var sql = "SELECT * FROM names";
try (var conn = DriverManager.getConnection("jdbc:derby:zoo");
var ps = conn.prepareStatement(sql)) {
var result = ps.executeUpdate();
}

Answer 20

This throws a SQLException similar to the following:
Statement.executeUpdate() cannot be called with a statement that returns a ResultSet.

We can’t get a compiler error since the SQL is a String. We can get an exception, though, and we do. We also get a SQLException when using executeQuery() with SQL that changes the database.

Statement.executeQuery() cannot be called with a statement that returns a row count.

Again, we get an exception because the driver can’t translate the query into the expected return type.

Question 21

Reviewing PreparedStatement Methods

Answer 21

1. ps.execute(), CRUD
2. ps.executeQuery(), R
3. ps.executeUpdate(), CUD

Question 22

Return types of execute methods

Answer 22

1. ps.execute(), boolean, R=true, CUD=false
2. ps.executeQuery(), ResultSet, R=The rows and columns returned, CUD=N/A
3. ps.executeUpdate(), int, R=N/A, CUD=Number of rows added/changed/removed

Question 23

WORKING WITH PARAMETERS

Answer 23

PreparedStatement allows us to set parameters. Instead of specifying the three values in the SQL, we can use a question mark (?) instead. A bind variable is a placeholder that lets you specify the actual values at runtime.

14: public static void register(Connection conn, int key,
15: int type, String name) throws SQLException {
16:
17: String sql = "INSERT INTO names VALUES(?, ?, ?)";
18:
19: try (PreparedStatement ps = conn.prepareStatement(sql)) {
20: ps.setInt(1, key);
21: ps.setString(3, name);
22: ps.setInt(2, type);
23: ps.executeUpdate();
24: }
25: }

Question 24

> [!NOTE]
Remember that JDBC starts counting columns with 1 rather than 0. A common exam (and interview) question tests that you know this!

Question 25

**Let's see what happens if you don't set all the bind variables.** ``` var sql = "INSERT INTO names VALUES(?, ?, ?)"; try (var ps = conn.prepareStatement(sql)) { ps.setInt(1, key); ps.setInt(2, type); // missing the set for parameter number 3 ps.executeUpdate(); } ```

Answer 25

The code compiles, and you get a **SQLException**. The message may vary based on your database driver.
**At least one parameter to the current statement is uninitialized.**

Question 26

**What about if you try to set more values than you have as bind variables?** ``` var sql = "INSERT INTO names VALUES(?, ?)"; try (var ps = conn.prepareStatement(sql)) { ps.setInt(1, key); ps.setInt(2, type); ps.setString(3, name); ps.executeUpdate(); } ```

Answer 26

Again, you get a **SQLException**, this time with a different message. On Derby, that message was as follows:
**The number of values assigned is not the same as the number of specified or implied columns.**

Question 27

***`PreparedStatement`* variable binding methods**

Answer 27

1. ***`void setBoolean(int parameterIndex, boolean x)`***, ex db type BOOLEAN 2. ***`void setDouble(int parameterIndex, double x)`***, ex db type DOUBLE 3. ***`void setInt(int parameterIndex, int x)`***, ex db type INTEGER 4. ***`void setLong(int parameterIndex, long x)`***, ex db type BIGINT 5. ***`void setObject(int parameterIndex, Object x)`***, ex db type ANY TYPE 6. ***`void setString(int parameterIndex, String x)`***, ex db type CHAR, VARCHAR

Question 28

Notice the ***`setObject()`*** method works with any Java type. If you pass a primitive, it will be autoboxed into a wrapper type. That means we can rewrite our example as follows: ``` String sql = "INSERT INTO names VALUES(?, ?, ?)"; try (PreparedStatement ps = conn.prepareStatement(sql)) { ps.setObject(1, key); ps.setObject(2, type); ps.setObject(3, name); ps.executeUpdate(); } ```

Answer 28

Java will handle the type conversion for you. **It is still better to call the more specific setter methods since that will give you a compile‐time error if you pass the wrong type instead of a runtime error.**

Question 29

**COMPILE VS. RUNTIME ERROR WHEN EXECUTING** **The following code is incorrect. Do you see why?** ``` ps.setObject(1, key); ps.setObject(2, type); ps.setObject(3, name); ps.executeUpdate(sql); // INCORRECT ```

Answer 29

**The problem is that the last line passes a SQL statement. With a *`PreparedStatement`*, we pass the SQL in when creating the object.** More interesting is that this **does not result in a compiler error**. **Remember that *`PreparedStatement`* extends *`Statement`*. The *`Statement`* interface does accept a SQL statement at the time of execution, so the code compiles.** Running this code gives **SQLException**. The text varies by database.

Question 30

**UPDATING MULTIPLE TIMES** ``` var sql = "INSERT INTO names VALUES(?, ?, ?)"; try (var ps = conn.prepareStatement(sql)) { ps.setInt(1, 20); ps.setInt(2, 1); ps.setString(3, "Ester"); ps.executeUpdate(); ps.setInt(1, 21); ps.setString(3, "Elias"); ps.executeUpdate(); } ```

Answer 30

Note that we set all three parameters when adding Ester, but only two for Elias. The ***`PreparedStatement`*** is smart enough to **remember the parameters that were already set and retain them.** **You only have to set the ones that are different.**

Question 31

**BATCHING STATEMENTS** **You don't need to know the *`addBatch()`* and *`executeBatch()`* methods for the exam, but they are useful in practice.**

Answer 31

``` public static void register(Connection conn, int firstKey, int type, String… names) throws SQLException { var sql = "INSERT INTO names VALUES(?, ?, ?)"; var nextIndex = firstKey; try (var ps = conn.prepareStatement(sql)) { ps.setInt(2, type); for(var name: names) { ps.setInt(1, nextIndex); ps.setString(3, name); ps.addBatch(); nextIndex++; } int[] result = ps.executeBatch(); System.out.println(Arrays.toString(result)); } } ``` Now we call this method with two names:
`register(conn, 100, 1, "Elias", "Ester");`
The output shows the array has two elements since there are two different items in the batch. Each one added one row in the database.
`[1, 1]`
When using batching, you should call executeBatch() at a set interval, such as every 10,000 records (rather than after ten million). Waiting too long to send the batch to the database could produce operations that are so large that they freeze the client (or even worse the database!).

Question 32

**READING A RESULTSET** ``` 20: String sql = "SELECT id, name FROM exhibits"; 21: Map idToNameMap = new HashMap<>(); 22: 23: try (var ps = conn.prepareStatement(sql); 24: ResultSet rs = ps.executeQuery()) { 25: 26: while (rs.next()) { 27: int id = rs.getInt("id"); 28: String name = rs.getString("name"); 29: idToNameMap.put(id, name); 30: } 31: System.out.println(idToNameMap); 32: } ```

Answer 32

It outputs this: ***`{1=African Elephant, 2=Zebra}`*** There are a few things to notice here. * First, we use the ***`executeQuery()`*** method on line **24**, since we want to have a ***`ResultSet`*** returned. * On line **26**, we **loop** through the results. **Each time through the loop represents one *`row`* in the *`ResultSet`*.** * Lines **27** and **28** show you the best way to **get** the **columns** for a given **row**. * **At line 24, the cursor starts out pointing to the location before the first row in the ResultSet.** * **On the first loop iteration, *`rs.next()`* returns *`true`*, and the *`cursor moves to point to the first row of data.`*** * On the second loop iteration, rs.next() returns true again, and the cursor moves to point to the second row of data. * The next call to rs.next() returns false. The cursor advances past the end of the data. The false signifies that there is no more data available to get. * We did say the “best way.” There is another way to access the columns. **You can use an index instead of a column name.** The column name is better because it is clearer what is going on when reading the code. It also allows you to change the SQL to reorder the columns.

Question 33

> [!NOTE] > On the exam, either you will be told the names of the columns in a table or you can assume that they are correct. Similarly, you can assume that all SQL is correct.

Question 34

Just like with a PreparedStatement, **JDBC starts counting at *`1`* in a *`ResultSet`*.** ``` 20: String sql = "SELECT id, name FROM exhibits"; 21: Map idToNameMap = new HashMap<>(); 22: 23: try (var ps = conn.prepareStatement(sql); 24: ResultSet rs = ps.executeQuery()) { 25: 26: while (rs.next()) { 27: int id = rs.getInt(1); 28: String name = rs.getString(2); 29: idToNameMap.put(id, name); 30: } 31: System.out.println(idToNameMap); 32: } ```

Question 35

Sometimes you want to **get only one row from the table.** Maybe you need only one piece of data. Or maybe the SQL is just returning the number of rows in the table. When you want only one row, you use an if statement rather than a while loop. ``` var sql = "SELECT count(*) FROM exhibits"; try (var ps = conn.prepareStatement(sql); var rs = ps.executeQuery()) { if (rs.next()) { int count = rs.getInt(1); System.out.println(count); } } ```

Answer 35

**It is important to check that rs.next() returns true before trying to call a getter on the ResultSet. If a query didn't return any rows, it would throw a SQLException, so the if statement checks that it is safe to call.** **Alternatively, you can use the column name.** ``` var sql = "SELECT count(*) AS count FROM exhibits"; try (var ps = conn.prepareStatement(sql); var rs = ps.executeQuery()) { if (rs.next()) { var count = rs.getInt("count"); System.out.println(count); } } ```

Question 36

**What is the output?** ``` var sql = "SELECT count(*) AS count FROM exhibits"; try (var ps = conn.prepareStatement(sql); var rs = ps.executeQuery()) { if (rs.next()) { var count = rs.getInt("total"); System.out.println(count); } } ```

Answer 36

**This throws a SQLException with a message like this:** ***`Column 'total' not found.`***

Question 37

**Do you see what is wrong here when no rows match?** ``` var sql = "SELECT * FROM exhibits where name='Not in table'"; try (var ps = conn.prepareStatement(sql); var rs = ps.executeQuery()) { rs.next(); rs.getInt(1); // SQLException } ```

Answer 37

Calling ***`rs.next()`*** works. It returns ***`false`***. However, calling a getter afterward does throw a ***`SQLException`*** because the result set cursor does not point to a valid position. **If there actually were a match returned, this code would have worked.**

Question 38

**Do you see what is wrong with the following?** ``` var sql = "SELECT count(*) FROM exhibits"; try (var ps = conn.prepareStatement(sql); var rs = ps.executeQuery()) { rs.getInt(1); // SQLException } ```

Answer 38

**Not calling *`rs.next()`* at all is a problem.** **The result set cursor is still pointing to a location before the first row**, so the getter has nothing to point to.

Question 39

**How about this one?** ``` var sql = "SELECT count(*) FROM exhibits"; try (var ps = conn.prepareStatement(sql); var rs = ps.executeQuery()) { if (rs.next()) rs.getInt(0); // SQLException } ```

Answer 39

Since column **indexes begin with 1**, there is **no column 0** to point to and a ***`SQLException`*** is thrown.

Question 40

**What is wrong with this one?** ``` var sql = "SELECT name FROM exhibits"; try (var ps = conn.prepareStatement(sql); var rs = ps.executeQuery()) { if (rs.next()) rs.getInt("badColumn"); // SQLException } ```

Answer 40

Trying to get a column that isn't in the ***`ResultSet`*** is just as bad as an invalid column index, and it also throws a ***`SQLException`***.

Question 41

**Getting Data from a *`ResultSet`***

Answer 41

**To sum up this section, it is important to remember the following:** * **Always use an *`if statement`* or *`while loop`* when calling *`rs.next()`*.** * **Column indexes begin with *`1`*.**

Question 42

**GETTING DATA FOR A COLUMN**

Answer 42

1. ***`boolean getBoolean(int columnIndex)`*** ***`boolean getBoolean(String columnLabel)`*** 2. ***`double getDouble(int columnIndex)`*** ***`double getDouble(String columnLabel)`*** 3. ***`int getInt(int columnIndex)`*** ***`int getInt(String columnLabel)`*** 4. ***`long getLong(int columnIndex)`*** ***`long getLong(String columnLabel)`*** 5. ***`Object getObject(int columnIndex)`*** ***`Object getObject(String columnLabel)`*** 6. ***`String getString(int columnIndex)`*** ***`String getString(String columnLabel)`***

Question 43

**The *`getObject()`* method can *`return any type`*.** For a primitive, it uses the wrapper class. Let's look at the following example: ``` 16: var sql = "SELECT id, name FROM exhibits"; 17: try (var ps = conn.prepareStatement(sql); 18: var rs = ps.executeQuery()) { 19: 20: while (rs.next()) { 21: Object idField = rs.getObject("id"); 22: Object nameField = rs.getObject("name"); 23: if (idField instanceof Integer) { 24: int id = (Integer) idField; 25: System.out.println(id); 26: } 27: if (nameField instanceof String) { 28: String name = (String) nameField; 29: System.out.println(name); 30: } 31: } 32: } ```

Answer 43

* Lines **21** and **22** get the column as whatever type of Object is most appropriate. * Lines **23–26** show you how to confirm that the type is Integer before casting and unboxing it into an int. * Lines **27–30** show you how to confirm that the type is String and cast it as well. You probably won't use ***`getObject()`*** when writing code for a job, but it is good to know about it for the exam.

Question 44

We've been **creating the PreparedStatement and ResultSet in the same try‐with‐resources statement. This doesn't work if you have bind variables because they need to be set in between.** Luckily, we can **nest try‐with‐resources** to handle this. This code prints out the ID for any exhibits matching a given name: ``` 30: var sql = "SELECT id FROM exhibits WHERE name = ?"; 31: 32: try (var ps = conn.prepareStatement(sql)) { 33: ps.setString(1, "Zebra"); 34: 35: try (var rs = ps.executeQuery()) { 36: while (rs.next()) { 37: int id = rs.getInt("id"); 38: System.out.println(id); 39: } 40: } 41: } ```

Answer 44

* First, we create the ***`PreparedStatement`*** on line **32**. * Then we set the ***`bind variable`*** on line **33**. * It is only after these are both done that we have a ***`nested try‐with‐resources`*** on line **35** to create the ResultSet.

Question 45

* **Sometimes you want your SQL to be directly in the database instead of packaged with your Java code.** * **This is particularly useful when you have *`many queries and they are complex.`*** * **A *`stored procedure`* is code that is *`compiled in advance and stored in the database.`*** * ***`Stored procedures`* are commonly written in a database‐specific variant of SQL, which varies among database software providers.** * Using a ***`stored procedure`* reduces network round‐trips.** * It also **allows database experts to own that part of the code.** * However, ***`stored procedures`* are database‐specific and introduce complexity of maintaining your application.** * **On the exam, you need to know how to call a stored procedure but not decide when to use one.**

Question 46

> [!NOTE] > You do not need to learn anything database specific for the exam. Since studying stored procedures can be quite complicated, we recommend limiting your studying on CallableStatement to what is in this book.

Question 47

**CALLING A PROCEDURE WITHOUT PARAMETERS** ``` 12: String sql = "{call read_e_names()}"; 13: try (CallableStatement cs = conn.prepareCall(sql); 14: ResultSet rs = cs.executeQuery()) { 15: 16: while (rs.next()) { 17: System.out.println(rs.getString(3)); 18: } 19: } ```

Answer 47

* Line **12** introduces a new bit of syntax. A ***`stored procedure`*** is called by putting the word ***`call`*** and the **procedure name in braces ({}).** ***`12: String sql = "{call read_e_names()}";`*** * Line **13** creates a ***`CallableStatement`*** object. When we created a PreparedStatement, we used the prepareStatement() method. Here, we use the ***`prepareCall()`*** method instead. ***`13: try (CallableStatement cs = conn.prepareCall(sql);`*** * Lines **14–18** should look familiar. They are the standard logic we have been using to get a ResultSet and loop through it. This stored procedure returns the underlying table, so the columns are the same.

Question 48

**PASSING AN IN PARAMETER** ``` 25: var sql = "{call read_names_by_letter(?)}"; 26: try (var cs = conn.prepareCall(sql)) { 27: cs.setString("prefix", "Z"); 28: 29: try (var rs = cs.executeQuery()) { 30: while (rs.next()) { 31: System.out.println(rs.getString(3)); 32: } 33: } 34: } ```

Answer 48

* On line **25**, we have to pass a **?** to show we have a parameter. This should be familiar from **bind variables** with a PreparedStatement. ***`25: var sql = "{call read_names_by_letter(?)}";`*** * On line **27**, we set the value of that parameter. Unlike with PreparedStatement, we can use either the **parameter number** (starting with **1**) or the **parameter name**. That means these two statements are equivalent: ``` cs.setString(1, "Z"); cs.setString("prefix", "Z"); ```

Question 49

**RETURNING AN OUT PARAMETER** ``` 40: var sql = "{?= call magic_number(?) }"; 41: try (var cs = conn.prepareCall(sql)) { 42: cs.registerOutParameter(1, Types.INTEGER); 43: cs.execute(); 44: System.out.println(cs.getInt("num")); 45: } ```

Answer 49

* On line **40**, we included two special characters **( ?=)** to **specify that the stored procedure has an output value.** This is optional since we have the **OUT parameter**, but it does aid in readability. ***`40: var sql = "{?= call magic_number(?) }";`*** * On line **42**, we **register the OUT parameter**. This is important. It allows JDBC to retrieve the value on line 44. Remember to always call ***`registerOutParameter()`*** for each OUT or INOUT parameter (which we will cover next). * On line **43**, we call ***`execute()`*** instead of executeQuery() since we are not returning a ResultSet from our stored procedure.

Question 50

**DATABASE‐SPECIFIC BEHAVIOR** Some databases are lenient about certain things this chapter says are required. For example, some databases allow you to omit the following: * **Braces ({})** * **Bind variable (?) if it is an OUT parameter** * **Call to registerOutParameter()** **For the exam, you need to answer according to the full requirements, which are described in this book. For example, you should answer exam questions as if braces are required.**

Question 51

**WORKING WITH AN INOUT PARAMETER** ``` 50: var sql = "{call double_number(?)}"; 51: try (var cs = conn.prepareCall(sql)) { 52: cs.setInt(1, 8); 53: cs.registerOutParameter(1, Types.INTEGER); 54: cs.execute(); 55: System.out.println(cs.getInt("num")); 56: } ```

Answer 51

* For an **IN parameter**, line **50** is required since it passes the parameter. * Similarly, line **52** is required since it sets the parameter. For an OUT parameter, line **53** is required to register the parameter. * Line **54** uses ***`execute()`*** again because we are not returning a ResultSet. * Remember that an **INOUT** parameter acts as both an **IN** parameter and an **OUT** parameter, so it has all the requirements of both.

Question 52

**COMPARING CALLABLE STATEMENT PARAMETERS**

Answer 52

**Stored procedure parameter types** * **IN**, Used for input, Must set parameter value * **OUT**, Used for output, Must call registerOutParameter(), Can include ?= * **INOUT**, Used for input, Used for output, Must set parameter value, Must call registerOutParameter(), Can include ?=

Question 53

**Closing Database Resources**

Answer 53

The resources need to be closed in a specific order. 1. The **ResultSet** is closed first, 2. followed by the **PreparedStatement** (or **CallableStatement**) 3. and then the **Connection**. While it is a good habit to close all three resources, it isn't strictly necessary. Closing a JDBC resource should close any resources that it created. In particular, the following are true: * Closing a **Connection** also closes **PreparedStatement** (or **CallableStatement**) and **ResultSet**. * Closing a **PreparedStatement** (or **CallableStatement**) also closes the **ResultSet**. It is important to close resources in the right order. This avoids both **resource leaks** and **exceptions**.

Question 54

**WRITING A RESOURCE LEAK** ``` 40: public void bad() throws SQLException { 41: var url = "jdbc:derby:zoo"; 42: var sql = "SELECT not_a_column FROM names"; 43: var conn = DriverManager.getConnection(url); 44: var ps = conn.prepareStatement(sql); 45: var rs = ps.executeQuery(); 46: 47: try (conn; ps; rs) { 48: while (rs.next()) 49: System.out.println(rs.getString(1)); 50: } 51: } ```

Answer 54

* **Suppose an *`exception`* is thrown on line 45.** * The **try‐with‐resources block** is never entered, so we don't benefit from automatic resource closing. * That means this code has a resource leak if it fails. * Do not write code like this.

Question 55

**How many resources are closed in this code?** ``` 14: var url = "jdbc:derby:zoo"; 15: var sql = "SELECT count(*) FROM names where id = ?"; 16: try (var conn = DriverManager.getConnection(url); 17: var ps = conn.prepareStatement(sql)) { 18: 19: ps.setInt(1, 1); 20: 21: var rs1 = ps.executeQuery(); 22: while (rs1.next()) { 23: System.out.println("Count: " + rs1.getInt(1)); 24: } 25: 26: ps.setInt(1, 2); 27: 28: var rs2 = ps.executeQuery(); 29: while (rs2.next()) { 30: System.out.println("Count: " + rs2.getInt(1)); 31: } 32: rs2.close(); 33: } ```

Answer 55

* The correct answer is **four**. * **On line 28, *`rs1`* is closed because the same *`PreparedStatement`* runs another query.** * **On line 32, *`rs2`* is closed in the method call.** * Then the **try‐with‐resources statement** runs and closes the **PreparedSatement** and **Connection** objects.

Question 56

**DEALING WITH EXCEPTIONS** In most of this chapter, we've lived in a perfect world. Sure, we mentioned that a checked SQLException might be thrown by any JDBC method—but we never actually caught it. We just declared it and let the caller deal with it. Now let's catch the exception. ``` var sql = "SELECT not_a_column FROM names"; var url = "jdbc:derby:zoo"; try (var conn = DriverManager.getConnection(url); var ps = conn.prepareStatement(sql); var rs = ps.executeQuery()) { while (rs.next()) System.out.println(rs.getString(1)); } catch (SQLException e) { System.out.println(e.getMessage()); System.out.println(e.getSQLState()); System.out.println(e.getErrorCode()); } ```

Answer 56

The output looks like this: ``` Column 'NOT_A_COLUMN' is either not in any table … 42X04 30000 ``` Each of these methods gives you a different piece of information. The getMessage() method returns a human‐readable message as to what went wrong. We've only included the beginning of it here. The getSQLState() method returns a code as to what went wrong. You can Google the name of your database and the SQL state to get more information about the error. By comparison, getErrorCode() is a database‐specific code. On this database, it doesn't do anything.