Chapter 15 JDBC

Question 1

1. Which interfaces or classes are in a database-specific JAR file? (Choose all that apply.)
A. Driver
B. Driver’s implementation
C. Manager
D. DriverManager’s implementation
E. PreparedStatement
F. PreparedStatement implementation

Question 2

2. Which of the following is a valid JDBC URL?
A. jdbc:sybase:localhost:1234/db
B. jdbc::sybase::localhost::/db
C. jdbc::sybase:localhost::1234/db
D. sybase:localhost:1234/db
E. sybase::localhost::/db
F. sybase::localhost::1234/db

Question 3

3. Which of the options can fill in the blank to make the code compile and run without error? (Choose all that apply.)

var sql = """
UPDATE habitat SET environment = null
WHERE environment = ? """;
try (var ps = conn.prepareStatement(sql)) {
\_\_\_\_\_\_\_\_\_\_\_\_\_\_
ps.executeUpdate();
}

A. ps.setString(0, “snow”);
B. ps.setString(1, “snow”);
C. ps.setString(“environment”, “snow”);
D. ps.setString(1, “snow”); ps.setString(1, “snow”);
E. ps.setString(1, “snow”); ps.setString(2, “snow”);
F. ps.setString(“environment”, “snow”);
ps.setString(“environment”, “snow”);

Question 4

4. Suppose that you have a table named animal with two rows. What is the result of the following code?

6: var conn = new Connection(url, userName, password);
7: var ps = conn.prepareStatement(
8: "SELECT count(*) FROM animal");
9: var rs = ps.executeQuery();
10: if (rs.next()) System.out.println(rs.getInt(1));

A. 0
B. 2
C. There is a compiler error on line 6.
D. There is a compiler error on line 10.
E. There is a compiler error on another line.
F. A runtime exception is thrown.

Question 5

5. Which option can fill in the blanks to make the code compile?

boolean bool = ps.\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_();
int num = ps.\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_();
ResultSet rs = ps.\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_();

A. execute, executeQuery, executeUpdate
B. execute, executeUpdate, executeQuery
C. executeQuery, execute, executeUpdate
D. executeQuery, executeUpdate, execute
E. executeUpdate, execute, executeQuery
F. executeUpdate, executeQuery, execute

Question 5

6. Suppose there are two rows in the table before this code is run, and executeUpdate() runs without error. How many rows are in the table after the code completes?

conn.setAutoCommit(true);
String sql = "INSERT INTO games VALUES(3, Jenga);";
try (PreparedStatement ps = conn.prepareStatement(sql,
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)) {
ps.executeUpdate();
}
conn.rollback();

A. Two
B. Three
C. The code does not compile.
D. The code throws an exception.

Question 6

7. Suppose that the table names has five rows and the following SQL statement updates all of them. What is the result of this code?

public static void main(String[] args) throws SQLException {
var sql = "UPDATE names SET name = 'Animal'";
try (var conn =
DriverManager.getConnection("jdbc:hsqldb:file:zoo");
var ps = conn.prepareStatement(sql)) {
var result = ps.executeUpdate();
System.out.println(result);
}
}

A. 0
B. 1
C. 5
D. The code does not compile.
E. A SQLException is thrown.
F. A different exception is thrown.

Question 7

8. Suppose learn() is a stored procedure that takes one IN parameter. What is wrong with the following code? (Choose all that apply.)

18: var sql = "call learn()";
19: try (var cs = conn.prepareCall(sql)) {
20: cs.setString(1, "java");
21: try (var rs = cs.executeQuery()) {
22: while (rs.next())
23: System.out.println(rs.getString(3));
24: }
25: }

A. Line 18 is missing braces.
B. Line 18 is missing a ?.
C. Line 19 is not allowed to use var.
D. Line 20 does not compile.
E. Line 22 does not compile.
F. Something else is wrong with the code.
G. None of the above. This code is correct.

Question 8

9. Suppose that the table enrichment has three rows with the animals bat, rat, and snake. How many lines does this code print?

var sql = "SELECT toy FROM enrichment WHERE animal = ?";
try (var ps = conn.prepareStatement(sql)) {
try (var rs = ps.executeQuery()) {
while (rs.next())
System.out.println(rs.getString(1));
}
}

A. 0
B. 1
C. 3
D. The code does not compile.
E. A SQLException is thrown.
F. A different exception is thrown.

Question 9

10. Suppose that the table food has five rows, and this SQL statement updates all of them. What is the result of this code?

public static void main(String[] args) {
var sql = "UPDATE food SET amount = amount + 1";
try (var conn =
DriverManager.getConnection("jdbc:hsqldb:file:zoo");
var ps = conn.prepareStatement(sql)) {
var result = ps.executeUpdate();
System.out.println(result);
}
}

A. 0
B. 1
C. 5
D. The code does not compile.
E. A SQLException is thrown.
F. A different exception is thrown.

Question 10

11. Suppose we have a JDBC program that calls a stored procedure, which returns a set of results. Which is the correct order in which to close database resources for this call?
A. Connection, ResultSet, CallableStatement
B. Connection, CallableStatement, ResultSet
C. ResultSet, Connection, CallableStatement
D. ResultSet, CallableStatement, Connection
E. CallableStatement, Connection, ResultSet
F. CallableStatement, ResultSet, Connection

Question 11

12. Suppose that the table counts has five rows with the numbers 1 to 5. How many lines does this code print?

var sql = "SELECT num FROM counts WHERE num> ?";
try (var ps = conn.prepareStatement(sql,
ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE))
{
ps.setInt(1, 3);
try (var rs = ps.executeQuery()) {
while (rs.next())
System.out.println(rs.getObject(1));
}
ps.setInt(1, 100);
try (var rs = ps.executeQuery()) {
while (rs.next())
System.out.println(rs.getObject(1));
}
}

A. 0
B. 1
C. 2
D. 4
E. The code does not compile.
F. The code throws an exception.

Question 12

13. Which of the following can fill in the blank correctly? (Choose all that apply.)

var rs = ps.executeQuery();
if (rs.next())
\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_;

A. String s = rs.getString(0)
B. String s = rs.getString(1)
C. String s = rs.getObject(0)
D. String s = rs.getObject(1)
E. Object s = rs.getObject(0)
F. Object s = rs.getObject(1)

Question 13

14. Suppose learn() is a stored procedure that takes one IN parameter and one OUT parameter. What is wrong with the following code? (Choose all that apply.)

18: var sql = "{?= call learn(?)}";
19: try (var cs = conn.prepareCall(sql)) {
20: cs.setInt(1, 8);
21: cs.execute();
22: System.out.println(cs.getInt(1));
23: }

A. Line 18 does not call the stored procedure properly.
B. The parameter value is not set for input.
C. The parameter is not registered for output.
D. The code does not compile.
E. Something else is wrong with the code.
F. None of the above. This code is correct.

Question 14

16. Which of the following can fill in the blank? (Choose all that apply.)

var sql = "\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_";
try (var ps = conn.prepareStatement(sql)) {
ps.setObject(3, "red");
ps.setInt(2, 8);
ps.setString(1, "ball");
ps.executeUpdate();
}

A. { call insert_toys(?, ?) }
B. { call insert_toys(?, ?, ?) }
C. { call insert_toys(?, ?, ?, ?) }
D. INSERT INTO toys VALUES (?, ?)
E. INSERT INTO toys VALUES (?, ?, ?)
F. INSERT INTO toys VALUES (?, ?, ?, ?)

Question 14

15. Which can fill in the blank and have the code run without error? (Choose all that apply.)

17: conn.setAutoCommit(false);
18:
19: var larry = conn.setSavepoint();
20: var curly = conn.setSavepoint();
21: var moe = conn.setSavepoint();
22: var shemp = conn.setSavepoint();
23:
24: \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_;
25:
26: conn.rollback(curly);

A. conn.rollback(larry)
B. conn.rollback(curly)
C. conn.rollback(moe)
D. conn.rollback(shemp)
E. conn.rollback()
F. The code does not compile.

Question 15

17. Suppose that the table counts has five rows with the numbers 1 to 5. How many lines does this code print?

var sql = "SELECT num FROM counts WHERE num> ?";
try (var ps = conn.prepareStatement(sql)) {
ps.setInt(1, 3);
try (var rs = ps.executeQuery()) {
while (rs.next())
System.out.println(rs.getObject(1));
}
try (var rs = ps.executeQuery()) {
while (rs.next())
System.out.println(rs.getObject(1));
}
}

A. 0
B. 1
C. 2
D. 4
E. The code does not compile.
F. The code throws an exception.

Question 16

18. There are currently 100 rows in the table species before inserting a new row. What is the output of the following code?

String insert = "INSERT INTO species VALUES (3, 'Ant', .05)";
String select = "SELECT count(*) FROM species";
try (var ps = conn.prepareStatement(insert)) {
ps.executeUpdate();
}
try (var ps = conn.prepareStatement(select)) {
var rs = ps.executeQuery();
System.out.println(rs.getInt(1));
}

A. 100
B. 101
C. The code does not compile.
D. A SQLException is thrown.
E. A different exception is thrown.

Question 17

19. Which of the options can fill in the blank to make the code compile and run without error? (Choose all that apply.)

var sql = "UPDATE habitat WHERE environment = ?";
try (var ps = conn.prepareCall(sql)) {
\_\_\_\_\_\_\_\_\_\_\_
ps.executeUpdate();
}

A. ps.setString(0, “snow”);
B. ps.setString(1, “snow”);
C. ps.setString(“environment”, “snow”);
D. The code does not compile.
E. The code throws an exception at runtime.

Question 18

20. Which is the first line containing a compiler error?

25: String url = "jdbc:hsqldb:file:zoo";
26: try (var conn = DriverManager.getConnection(url);
27: var ps = conn.prepareStatement();
28: var rs = ps.executeQuery("SELECT * FROM swings")) {
29: while (rs.next()) {
30: System.out.println(rs.getInteger(1));
31: }
32: }

A. Line 26
B. Line 27
C. Line 28
D. Line 29
E. Line 30
F. None of the above

Question 19

21. Suppose conn is a valid connection object and the exhibits table is empty. Which are true? (Choose two.)

try (conn) {
conn.setAutoCommit(false);
String sql = "INSERT INTO exhibits VALUES(3, 'Test', 2)";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.executeUpdate();
}
conn.setAutoCommit(true); // line W
}

A. As written, the table will remain empty after this code.
B. As written, the table will contain one row after this code.
C. As written, the code will throw an exception.
D. When line W is commented out, the table will remain empty after this code.
E. When line W is commented out, the table will contain one row after this code.
F. When line W is commented out, the code will throw an exception.