Chapter 21: JDBC

Question 1

What are the five key interfaces of JDBC in the JDK?

Answer 1

• Driver
• Connection
• PreparedStatement
• CallableStatement
• ResultSet

Question 2

What does the Driver interface do?

Answer 2

Establishes a connection to the database

Question 3

What does the Connection interface do?

Answer 3

Sends commands to a database

Question 4

What does the PreparedStatement interface do?

Answer 4

Executes a SQL query

Question 5

What does the CallableStatement interface do?

Answer 5

Executes commands stored in the database

Question 6

What does the ResultSet interface do?

Answer 6

Reads results of a query

Question 7

In what package are the five key interfaces of JDBC stored?

Answer 7

java.sql

Question 8

(Assuming no specific server names were given)

Is the following JDBC url valid?

jdbc:postgresql;//localhost/zoo

Answer 8

It has a semicolon (;) instead of colons (:) so it is not valid

Question 9

(Assuming no specific server names were given)

Is the following JDBC url valid?

jdbc:mysql://local/zoo

Answer 9

It uses local instead of localhost. Since no server name was given, local is invalid.

Question 10

(Assuming no specific server names were given)

Is the following JDBC url valid?

jdbc:postgresql://localhost/zoo

Answer 10

This URL is valid. The format is split into three parts, seperated by a colon (:) -> jdbc:subprotocol:subname

Question 11

(Assuming no specific server names were given)

Is the following JDBC url valid?

jdbc:oracle://123456/zoo

Answer 11

According to the URL the location of the database is 123456. This is not an ip address/localhost or a domain name so it is not valid.

Question 12

(Assuming no specific server names were given)

Is the following JDBC url valid?

jdbc:mysql://localhost:3306/zoo

Answer 12

This URL is valid. The format is split into three parts, seperated by a colon (:) -> jdbc:subprotocol:subname

Question 13

Write a main method that makes a connection with with ‘jdbc:derby:zoo’ using DriverManager without username and password.

Answer 13

import java.sql.*;
…
public static void main (String[] args) throws SQLException {
Drivermanager.getConnection(“jdbc:derby:zoo”);
}

Question 14

Write a main method that makes a connection with with ‘jdbc:derby:zoo’ using DriverManager with username: “user” and password: “test”.

Answer 14

import java.sql.*;
…
public static void main (String[] args) throws SQLException {
Drivermanager.getConnection(“jdbc:derby:zoo”, “user, “test);
}

Question 15

Does the following code compile?

public static void main(String[] args) {
Connection conn = Drivermanager.getConnection(“jdbc:derby:zoo”, “user, “test);
PreparedStatement ps = conn.prepareStatement(“SELECT * FROM exhibits”);
}

Answer 15

Both getConnection() and prepareStatement() might throw an SQLException, which is checked. This is missing from the code.

Question 16

Does the following code compile?

public static void main(String[] args) throws SQLException {
Connection conn = Drivermanager.getConnection(“jdbc:derby:zoo”, “user”, “test”);
PreparedStatement ps = conn.prepareStatement(“SELECT * FROM exhibits”);
}

Answer 16

This is valid.

Question 17

Does the following code compile?

public static void main(String[] args) throws SQLException {
Connection conn = Drivermanager.getConnection(“jdbc:derby:zoo”, “user”, “test”);
PreparedStatement ps = conn.prepareStatement();
}

Answer 17

Passing a SQL Statement when creating the PreparedStatement object is mandatory, so this will not compile.

Question 18

The prepareStatement() method of a Connection object implements the AutoClosable interface

Answer 18

True

Question 19

What is the result of the executeUpdate() method?

...
var insertSql = "INSERT INTO exhibits VALUES (10, 'Deer', 3)";

try (var ps = conn.prepareStatement(insertSql)) {
    int result = ps.executeUpdate(); //?
}

Answer 19

The result of the method executeUpdate() is the number of rows that were affected. In this case one record was inserted so the result is 1.

Question 20

Write the missing code to read the data using the query

...
var sql = "SELECT * FROM exhibits";
try (var ps = conn.prepareStatement(sql); ...) {
}

Answer 20

var sql = "SELECT * FROM exhibits";
try (var ps = conn.prepareStatement(sql); ResultSet rs = ps.executeQuery()) {
}

Question 21

What are the three PreparedStatement methods to run a query or perform an update and what do they return?

Answer 21

• execute(): returns boolean wether there is a ResultSet (true for read queries and false for write queries)
• executeQuery(): returns ResultSet
• executeUpdate(): returns number of affected rows

Question 22

What is the result of the code?

var sql = “SELECT * FROM exhibits”;

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

Answer 22

This throws a SQLException because the method executeUpdate() can’t be used to with a READ query

Question 23

What is the result of the code?

var sql = “INSERT INTO exhibits VALUES (10, ‘Deer’, 3)”;

try (var conn = DriverManager.getConnection("jdbc:derby:zoo"); var ps = conn.prepareStatement(sql)) {
    var result = ps.executeQuery();
}

Answer 23

This throws a SQLException because the method executeQuery() can’t be used to with an update query

Question 24

What is the result of the code?

var sql = “SELECT * FROM exhibits”;

try (var conn = DriverManager.getConnection("jdbc:derby:zoo"); var ps = conn.prepareStatement(sql)) {
    var result = ps.executeQuery();
}

Answer 24

This is valid and it returns a ResultSet

Question 25

Why is it important to close database resources?

Answer 25

To avoid creating a resource leak that will eventually slow down your program.

Question 26

Given the following resources, in what order must they be closed?

• PreparedStatement
• Connection
• ResultSet

Answer 26

1. ResultSet
2. PreparedStatement
3. Connection

Question 27

What is wrong with the following code?

public void test() throws SQLException {
    var conn = DriverManager.getConnection(url);
    var ps = conn.prepareStatement(sql);
    var rs = ps.executeQuery();

    try (conn; ps; rs) {
        while (rs.next()) {
            System.out.println(rs.getString(1));
        }
    }
}

Answer 27

Suppose an exception is thrown before the try-with-resources block. In that case, we don’t benefit from automatic resource closing which means that the code will has a resource leak if it fails.

Question 28

To execute a stored SQL procedure, which JDBC interface should be used?

A. Statement Interface
B. PreparedStatement Interface
C. PrePreparedStatement Interface
D. CallableStatement Interface

Answer 28

D. CallableStatement Interface