Chapter 21: JDBC Flashcards
What are the five key interfaces of JDBC in the JDK?
- Driver
- Connection
- PreparedStatement
- CallableStatement
- ResultSet
What does the Driver interface do?
Establishes a connection to the database
What does the Connection interface do?
Sends commands to a database
What does the PreparedStatement interface do?
Executes a SQL query
What does the CallableStatement interface do?
Executes commands stored in the database
What does the ResultSet interface do?
Reads results of a query
In what package are the five key interfaces of JDBC stored?
java.sql
(Assuming no specific server names were given)
Is the following JDBC url valid?
jdbc:postgresql;//localhost/zoo
It has a semicolon (;) instead of colons (:) so it is not valid
(Assuming no specific server names were given)
Is the following JDBC url valid?
jdbc:mysql://local/zoo
It uses local instead of localhost. Since no server name was given, local is invalid.
(Assuming no specific server names were given)
Is the following JDBC url valid?
jdbc:postgresql://localhost/zoo
This URL is valid. The format is split into three parts, seperated by a colon (:) -> jdbc:subprotocol:subname
(Assuming no specific server names were given)
Is the following JDBC url valid?
jdbc:oracle://123456/zoo
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.
(Assuming no specific server names were given)
Is the following JDBC url valid?
jdbc:mysql://localhost:3306/zoo
This URL is valid. The format is split into three parts, seperated by a colon (:) -> jdbc:subprotocol:subname
Write a main method that makes a connection with with ‘jdbc:derby:zoo’ using DriverManager without username and password.
import java.sql.*;
…
public static void main (String[] args) throws SQLException {
Drivermanager.getConnection(“jdbc:derby:zoo”);
}
Write a main method that makes a connection with with ‘jdbc:derby:zoo’ using DriverManager with username: “user” and password: “test”.
import java.sql.*;
…
public static void main (String[] args) throws SQLException {
Drivermanager.getConnection(“jdbc:derby:zoo”, “user, “test);
}
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”);
}
Both getConnection() and prepareStatement() might throw an SQLException, which is checked. This is missing from the code.
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”);
}
This is valid.
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();
}
Passing a SQL Statement when creating the PreparedStatement object is mandatory, so this will not compile.
The prepareStatement() method of a Connection object implements the AutoClosable interface
True
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(); //?
}
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.
Write the missing code to read the data using the query
...
var sql = "SELECT * FROM exhibits";
try (var ps = conn.prepareStatement(sql); ...) {
}
var sql = "SELECT * FROM exhibits";
try (var ps = conn.prepareStatement(sql); ResultSet rs = ps.executeQuery()) {
}What are the three PreparedStatement methods to run a query or perform an update and what do they return?
- 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
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();
}
This throws a SQLException because the method executeUpdate() can’t be used to with a READ query
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();
}
This throws a SQLException because the method executeQuery() can’t be used to with an update query
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();
}
This is valid and it returns a ResultSet
Why is it important to close database resources?
To avoid creating a resource leak that will eventually slow down your program.
Given the following resources, in what order must they be closed?
- PreparedStatement
- Connection
- ResultSet
- ResultSet
- PreparedStatement
- Connection
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));
}
}
}
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.
To execute a stored SQL procedure, which JDBC interface should be used?
A. Statement Interface
B. PreparedStatement Interface
C. PrePreparedStatement Interface
D. CallableStatement Interface
D. CallableStatement Interface
