Chapter 21 JDBC Review Questions

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. DriverManager
D. DriverManager’s implementation
E. PreparedStatement
F. PreparedStatement’s implementation

Answer 1

Wrong: B, D, F

B, F.
* The Driver and PreparedStatement interfaces are part of the JDK, making options A and E incorrect.
* The concrete DriverManager class is also part of the JDK, making options C and D incorrect.
* Options B and F are correct since the implementation of these interfaces is part of the database-specific driver JAR file.

Question 2

2. Which are required parts of a JDBC URL? (Choose all that apply.)
A. Connection parameters
B. IP address of database
C. jdbc
D. Password
E. Port
F. Vendor‐specific string

Answer 2

wrong: B, C, D, E, F

C, F.
* A JDBC URL has three parts.
* The first part is the string jdbc, making option C correct.
* The second part is the subprotocol. This is the vendor/product name, which isn’t an answer choice.
* The subname is vendor-specific, making option F correct as well.

Question 3

3. 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

Answer 3

A.
* A JDBC URL has three main parts separated by single colons, making options B, C, E, and F incorrect.
* The first part is always jdbc, making option D incorrect.
* Therefore, the correct answer is option A. Notice that you can get this right even if you’ve never heard of the Sybase database before.

Question 4

4. 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.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”);

Answer 4

B, D.
* When setting parameters on a PreparedStatement, there are only options that take an index, making options C and F incorrect.
* The indexing starts with 1, making option A incorrect.
* This query has only one parameter, so option E is also incorrect.
* Option B is correct because it simply sets the parameter.
* Option D is also correct because it sets the parameter and then immediately overwrites it with the same value.

Question 5

5. 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.

Answer 5

C.

• A Connection is created using a static method on DriverManager. It does not use a constructor. Therefore, option C is correct. If the Connection was created properly, the answer would be option B.

Question 6

6. Which of the options can fill in the blanks in order 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

Answer 6

B.
* The first line has a return type of boolean, making it an execute() call.
* The second line returns the number of modified rows, making it an executeUpdate() call.
* The third line returns the results of a query, making it an executeQuery() call.

Question 7

7. Which of the following are words in the CRUD acronym? (Choose all that apply.)
A. Create
B. Delete
C. Disable
D. Relate
E. Read
F. Upgrade

Answer 7

A, B, E.
CRUD stands for Create, Read, Update, Delete, making options A, B, and E correct.

Question 8

8. Suppose that the table animal 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:derby: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.

Answer 8

C.
This code works as expected. It updates each of the five rows in the table and returns the number of rows updated. Therefore, option C is correct.

Question 9

9. 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:     }
26: }

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.

Answer 9

A, B.
* Option A is one of the answers because you are supposed to use braces ({}) for all SQL in a CallableStatement.
* Option B is the other answer because each parameter should be passed with a question mark (?).
* The rest of the code is correct. Note that your database might not behave the way that’s described here, but you still need to know this syntax for the exam.

Question 10

10. 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)) {
    ps.setString(1, "bat");
    try (var rs = ps.executeQuery(sql)) {
        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.

Answer 10

Wrong: B

E.
The code compiles because PreparedStatement extends Statement and Statement allows passing a String in the executeQuery() call. While PreparedStatement can have bind variables, Statement cannot. Since this code uses executeQuery(sql) in Statement, it fails at runtime. A SQLException is thrown, making option E correct.

Question 11

11. 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:derby: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.

Answer 11

Wrong : C

D.
* JDBC code throws a SQLException, which is a checked exception. The code does not handle or declare this exception, and therefore it doesn’t compile. Since the code doesn’t compile, option D is correct.
* If the exception were handled or declared, the answer would be option C.

Question 12

12.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

Answer 12

D.
JDBC resources should be closed in the reverse order from that in which they were opened. The order for opening is Connection, CallableStatement, and ResultSet. The order for closing is ResultSet, CallableStatement, and Connection.

Question 13

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

Answer 13

C.

• This code calls the PreparedStatement twice.
• The first time, it gets the numbers greater than 3. Since there are two such numbers, it prints two lines.
• The second time, it gets the numbers greater than 100. There are no such numbers, so the ResultSet is empty.
• A total of two lines is printed, making option C correct.

Question 14

14. 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)

Answer 14

Wrong : B, D, F

B, F.

• In a ResultSet, columns are indexed starting with 1, not 0. Therefore, options A, C, and E are incorrect.
• There are methods to get the column as a String or Object. However, option D is incorrect because an Object cannot be assigned to a String without a cast.

Question 15

15. 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.

Answer 15

C.

Since an OUT parameter is used, the code should call registerOutParameter(). Since this is missing, option C is correct.C

Question 16

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 (?, ?, ?, ?)

Answer 16

E.

• First, notice that this code uses a PreparedStatement. Options A, B, and C are incorrect because they are for a CallableStatement.
• Next, remember that the number of parameters must be an exact match, making option E correct.
• Remember that you will not be tested on SQL syntax. When you see a question that appears to be about SQL, think about what it might be trying to test you on.

Question 17

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.

Answer 17

D.

• This code calls the PreparedStatement twice.
• The first time, it gets the numbers greater than 3. Since there are two such numbers, it prints two lines.
• Since the parameter is not set between the first and second calls, the second attempt also prints two rows.
• A total of four lines are printed, making option D correct.D

Question 18

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.

Answer 18

D.

Before accessing data from a ResultSet, the cursor needs to be positioned. The call to rs.next() is missing from this code.

Question 19

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.

Answer 19

E.

• This code should call prepareStatement() instead of prepareCall() since it not executing a stored procedure.
• Since we are using var, it does compile. Java will happily create a CallableStatement for you.
• Since this compile safety is lost, the code will not cause issues until runtime. At that point, Java will complain that you are trying to execute SQL as if it were a stored procedure, making option E correct.

Question 20

20. Which of the following could be true of the following code? (Choose all that apply.)

var sql = "{call transform(?)}";
try (var cs = conn.prepareCall(sql)) {
    cs.registerOutParameter(1, Types.INTEGER);
    cs.execute();
    System.out.println(cs.getInt(1));
}

A. The stored procedure can declare an IN or INOUT parameter.
B. The stored procedure can declare an INOUT or OUT parameter.
C. The stored procedure must declare an IN parameter.
D. The stored procedure must declare an INOUT parameter.
E. The stored procedure must declare an OUT parameter.

Answer 20

Wrong : D

E.

Since the code calls registerOutParameter(), we know the stored procedure cannot use an IN parameter. Further, there is no setInt(), so it cannot be an INOUT parameter either. Therefore, the stored procedure must use an OUT parameter, making option E the answer.

Question 21

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

25: String url = "jdbc:derby: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

Answer 21

B.

• The prepareStatement() method requires SQL be passed in. Since this parameter is omitted,
• line 27 does not compile, and option B is correct.
• Line 30 also does not compile as the method should be getInt(). However, the question asked about the first compiler error.