Chapter 21 JDBC Notes Flashcards
JDBC five interfaces
-
Driver: Establishes aconnectionto the database -
Connection: Sendscommandsto a database -
PreparedStatement: Executes aSQLquery -
CallableStatement: Executescommandsstored in the database -
ResultSet: Readsresultsof a query
Java Persistence API (JPA):
Accesses data through Java objects using a concept called object‐relational mapping (ORM). The idea is that you don’t have to write as much code, and you get your data in Java objects. JPA is not on the exam, and therefore it is not covered in this
chapter.
JDBC example:
DriverManagerConnectionPreparedStatementResultSet
public class MyFirstDatabaseConnection {
public static void main(String[] args) throws SQLException {
String url = "jdbc:derby:zoo";
try (Connection conn = DriverManager.getConnection(url);
PreparedStatement ps = conn.prepareStatement("SELECT name FROM animal");
ResultSet rs = ps.executeQuery()) {
while (rs.next())
System.out.println(rs.getString(1));
}
}
}
-
Get
Connection:Connection conn = DriverManager.getConnection(url); -
Prepare statement: (sql statement is mandatory)PreparedStatement ps = conn.prepareStatement("SELECT name FROM animal"); -
Get
ResultSet:ResultSet rs = ps.executeQuery())
COMPILING WITH MODULES
The JDBC classes are all in the module java.sql.
When working with SQL, you need the java.sql module andimport java.sql.*.
That said, if you do want to use JDBC code with modules, remember to update your module‐info file to include the following:requires java.sql;
The JDBC URL format
JDBC URL has a variety of formats. They have three parts in common.
- Protocal : jdbc
- Subprotocal : Product/Vendor name, ex: derby, mysql, or postgres
- Subname : Database specific connection details
- Colon separators (
:)
ex :
jdbc:postgresql://localhost:5432/zoo
> [!Note]
The subname typically contains information about the database such as the location and/or name of the database.
The syntax varies.
You need to know about the three main parts. You don’t need to memorize the subname formats.
JDBC URL example:
jdbc:derby:zoo jdbc:postgresql://localhost/zoo jdbc:oracle:thin:@123.123.123.123:1521:zoo jdbc:mysql://localhost:3306 jdbc:mysql://localhost:3306/zoo?profileSQL=true
- First line It starts with jdbc and then comes the subprotocol derby, and it ends with the subname, which is the database name. The location is not required, because Derby is an in‐memory database.
- the port is optional when using the default location.
Do you see what is wrong with each of the following?
jdbc:postgresql://local/zoo jdbc:mysql://123456/zoo jdbc;oracle;thin;/localhost/zoo
- The first one uses local instead of localhost. The literal localhost is a specially defined name. You can’t just make up a name. Granted, it is possible for our database server to be named local, but the exam won’t have you assume names. If the database server has a special name, the question will let you know it.
- The second one says that the location of the database is 123456. This doesn’t make sense. A location can be localhost or an IP address or a domain name. It can’t be any random number.
- The third one is no good because it uses semicolons ( ;) instead of colons ( :).
GETTING A DATABASE CONNECTION
There are two main ways to get a Connection: DriverManager or DataSource.
DriverManager is the one covered on the exam.
The DriverManager class is in the JDK, as it is an API that comes with Java. It uses the factory pattern, which means that you call a static method to get a Connection, rather than calling a constructor. The factory pattern means that you can get any implementation of the interface when calling the method. The good news is that the method has an easy‐to‐remember name— getConnection().
To get a Connection from the Derby database, you write the following:
import java.sql.*;
public class TestConnect {
public static void main(String[] args) throws SQLException {
Connection conn = DriverManager.getConnection("jdbc:derby:zoo");
System.out.println(conn);
}
}
Exception in thread "main" java.sql.SQLException: No suitable driver found for jdbc:derby:zoo at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:702) at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:251) at connection.TestConnect.main(TestConnect.java:9)
java -cp "<path_to_derby>/derby.jar" TestConnect.java
This time the program runs successfully and prints something like the following:
org.apache.derby.impl.jdbc.EmbedConnection40@1372082959 (XID = 156), (SESSIONID = 1), (DATABASE = zoo), (DRDAID = null)
There is also a signature that takes a username and password.
import java.sql.*;
public class TestExternal {
public static void main(String[] args) throws SQLException {
Connection conn = DriverManager.getConnection( "jdbc:postgresql://localhost:5432/ocp-book", "username", "Password20182");
System.out.println(conn);
}
}
- Notice the
threeparameters that are passed togetConnection(). - The first is the JDBC
URLthat you learned about in the previous section. - The second is the username for accessing the database,
- and the third is the password for accessing the database. It should go without saying that our password is not Password20182. Also, don’t put your password in real code. It’s a horrible practice. Always load it from some kind of configuration, ideally one that keeps the stored value encrypted.
You might see Class.forName() in code. It was required with older drivers (that were designed for older versions of JDBC) before getting a Connection. It looks like this:
public static void main(String[] args) throws SQLException, ClassNotFoundException {
Class.forName("org.postgresql.Driver");
Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/ocp-book", "username", "password");
}
Class.forName() loads a class before it is used. With newer drivers, Class.forName() is no longer required.
In Java, you have a choice of working with a Statement, PreparedStatement, or CallableStatement.
The latter two are subinterfaces of Statement,
StatementPreparedStatementCallableStatement
While it is possible to run SQL directly with Statement, you shouldn’t. PreparedStatement is far superior for the following reasons:
- Performance: In most programs, you run similar queries multiple times. A PreparedStatement figures out a plan to run the SQL well and remembers it.
- Security: As you will see in Chapter 22, “Security,” you are protected against an attack called SQL injection when using a PreparedStatement correctly.
- Readability: It’s nice not to have to deal with string concatenation in building a query string with lots of parameters.
- Future use: Even if your query is being run only once or doesn’t have any parameters, you should still use a PreparedStatement. That way future editors of the code won’t add a variable and have to remember to change to PreparedStatement then.
Using the Statement interface is also no longer in scope for the JDBC exam, so we do not cover it in this book. In the following sections, we will cover obtaining a PreparedStatement, executing one, working with parameters, and running multiple updates.
OBTAINING A PREPAREDSTATEMENT
Example :
try (PreparedStatement ps = conn.prepareStatement("SELECT * FROM exhibits")) {
// work with ps
}
Passing a SQL statement when creating the object is mandatory.
try (var ps = conn.prepareStatement()) { // DOES NOT COMPILE
}
There are overloaded signatures that allow you to specify a ResultSet type and concurrency mode. On the exam, you only need to know how to use the default options, which processes the results in order.
EXECUTING A PREPAREDSTATEMENT
-
Modifying Data with
int executeUpdate() -
Reading Data with
ResultSet executeQuery() -
Processing Data with
boolean execute()- if
SELECTsql statement returntrue, getResultSetviaps.getResultSet() - else return
false. get int viaps.getUpdateCount()
- if
Modifying Data with executeUpdate()
Here’s an example of all three update types:
10: var insertSql = "INSERT INTO exhibits VALUES(10, 'Deer', 3)";
11: var updateSql = "UPDATE exhibits SET name = '' " +
12: "WHERE name = 'None'";
13: var deleteSql = "DELETE FROM exhibits WHERE id = 10";
14:
15: try (var ps = conn.prepareStatement(insertSql)) {
16: int result = ps.executeUpdate();
17: System.out.println(result); // 1
18: }
19:
20: try (var ps = conn.prepareStatement(updateSql)) {
21: int result = ps.executeUpdate();
22: System.out.println(result); // 0
23: }
24:
25: try (var ps = conn.prepareStatement(deleteSql)) {
26: int result = ps.executeUpdate();
27: System.out.println(result); // 1
28: }
int executeUpdate()
- Line 15 creates the insert statement,
- and line 16 runs that statement to insert one row.
ps.executeUpdate() - The result is 1 because one row was affected.
- Line 20 creates the update statement,
- and line 21 checks the whole table for matching records to update.
ps.executeUpdate() - Since no records match, the result is 0.
- Line 25 creates the delete statement,
- and line 26 deletes the row created on line 16.
ps.executeUpdate() - Again, one row is affected, so the result is 1.
Reading Data with executeQuery()
30: var sql = "SELECT * FROM exhibits";
31: try (var ps = conn.prepareStatement(sql);
32: ResultSet rs = ps.executeQuery() ) {
33:
34: // work with rs
35: }
ResultSet executeQuery()
- On line 31, we create a
PreparedStatementfor ourSELECTquery. - On line 32, we actually run it. Since we are running a query to get a result, the return type is
ResultSet.
Processing Data with execute()
boolean isResultSet = ps.execute();
if (isResultSet) {
try (ResultSet rs = ps.getResultSet()) {
System.out.println("ran a query");
}
} else {
int result = ps.getUpdateCount();
System.out.println("ran an update");
}
If the PreparedStatement refers to sql that is a SELECT, the boolean is true and we can get the ResultSet. If it is not a SELECT, we can get the number of rows updated.
What do you think happens if we use the wrong method for a SQL statement? Let’s take a look.
var sql = "SELECT * FROM names";
try (var conn = DriverManager.getConnection("jdbc:derby:zoo");
var ps = conn.prepareStatement(sql)) {
var result = ps.executeUpdate();
}
This throws a SQLException similar to the following:Statement.executeUpdate() cannot be called with a statement that returns a ResultSet.
We can’t get a compiler error since the SQL is a String. We can get an exception, though, and we do. We also get a SQLException when using executeQuery() with SQL that changes the database.
Statement.executeQuery() cannot be called with a statement that returns a row count.
Again, we get an exception because the driver can’t translate the query into the expected return type.
Reviewing PreparedStatement Methods
-
ps.execute(), CRUD -
ps.executeQuery(), R -
ps.executeUpdate(), CUD
Return types of execute methods
-
ps.execute(),boolean, R=true, CUD=false -
ps.executeQuery(),ResultSet, R=The rows and columns returned, CUD=N/A -
ps.executeUpdate(), int, R=N/A, CUD=Number of rows added/changed/removed
