Chapter 21 JDBC Notes Flashcards

Question 1

Q

JDBC five interfaces

Answer

A

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

Q

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

Q

JDBC example:

DriverManager
Connection
PreparedStatement
ResultSet

Answer

A

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

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

Question 4

Q

COMPILING WITH MODULES

Answer

A

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

Q

The JDBC URL format

Answer

A

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

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

ex :

jdbc:postgresql://localhost:5432/zoo

Question 6

Q

> [!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

Q

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

A

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

Q

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

A

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

Q

GETTING A DATABASE CONNECTION

Answer

A

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

Q

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

A

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

Q

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

A

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

Q

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

Q

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

Answer

A

Statement
PreparedStatement
CallableStatement

Question 14

Q

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

Answer

A

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

Q

OBTAINING A PREPAREDSTATEMENT

Answer

A

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

Q

EXECUTING A PREPAREDSTATEMENT

Answer

A

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

Q

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

A

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

Q

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

A

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

Q

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

A

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

Q

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

A

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

Q

Reviewing PreparedStatement Methods

Answer

A

ps.execute(), CRUD
ps.executeQuery(), R
ps.executeUpdate(), CUD

Question 22

Q

Return types of execute methods

Answer

A

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

Question 23

Q

WORKING WITH PARAMETERS

Answer

A

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

Q

> [!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

Q

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

A

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

Q

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

A

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

Q

PreparedStatement variable binding methods

Answer

A

void setBoolean(int parameterIndex, boolean x), ex db type BOOLEAN
void setDouble(int parameterIndex, double x), ex db type DOUBLE
void setInt(int parameterIndex, int x), ex db type INTEGER
void setLong(int parameterIndex, long x), ex db type BIGINT
void setObject(int parameterIndex, Object x), ex db type ANY TYPE
void setString(int parameterIndex, String x), ex db type CHAR, VARCHAR

Question 28

Q

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

A

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

Q

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

A

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

Q

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

A

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

Q

BATCHING STATEMENTS

You don’t need to know the addBatch() and executeBatch() methods for the exam, but they are useful in practice.

Answer

A

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

Q

READING A RESULTSET

20: String sql = "SELECT id, name FROM exhibits";
21: Map<Integer, String> 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

A

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

Q

> [!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

Q

Just like with a PreparedStatement, JDBC starts counting at 1 in a ResultSet.

20: String sql = "SELECT id, name FROM exhibits";
21: Map<Integer, String> 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

Q

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

A

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

Q

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

A

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

Question 37

Q

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

A

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

Q

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

A

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

Q

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

A

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

Question 40

Q

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

A

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

Q

Getting Data from a ResultSet

Answer

A

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

Q

GETTING DATA FOR A COLUMN

Answer

A

boolean getBoolean(int columnIndex)
boolean getBoolean(String columnLabel)
double getDouble(int columnIndex)
double getDouble(String columnLabel)
int getInt(int columnIndex)
int getInt(String columnLabel)
long getLong(int columnIndex)
long getLong(String columnLabel)
Object getObject(int columnIndex)
Object getObject(String columnLabel)
String getString(int columnIndex)
String getString(String columnLabel)

Question 43

Q

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

A

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

Q

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

A

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

Q

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

Q

> [!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

Q

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

A

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

Q

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

A

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

Q

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

A

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

Q

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

Q

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

A

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

Q

COMPARING CALLABLE STATEMENT PARAMETERS

Answer

A

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

Q

Closing Database Resources

Answer

A

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

Q

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

A

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

Q

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

A

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

Q

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

A

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.

Brainscape's Knowledge GenomeTM

Chapter 21 JDBC Notes Flashcards

Brainscape's Knowledge Genome^TM