Ch21 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

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

Question 9

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

Answer 9

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

Question 10

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

Answer 10

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

Question 11

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 11

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

Question 12

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 12

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

Question 13

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

Answer 13

True

Question 14

Write the missing code to read the data using the query

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

Answer 14

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

Question 15

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

Answer 15

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

Question 16

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 16

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

Question 17

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 17

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.