Codecademy Logo

Java Database Connectivity (JDBC)

Print Cheatsheet

JDBC: Review

The Java Database Connectivity (JDBC) is an Oracle-provided library to connect Java applications to relational databases through standard methods and interfaces. Some frequently used classes include:

  • DriverManager
  • Connection
  • Statement
  • ResultSet
  • SQLException

Exception Handling

A SQLExeception is thrown by objects that interact with the database and is used to notify the user of errors.

Connection

The Connection class creates and manages Statement objects.

Statement statement = Connection.createStatement();

Third Party Drivers

Third-party drivers must be registered with the JVM at run time, JDBC 4.0 now performs this task implicitly as long as driver classes are provided in the classpath of the program.

ResultSet Structure

A ResultSet object mimics a database table and can be referenced by row and column indexes. You must be careful with these references because they are one of the few 1-based indices in the Java language.

- column1 column2 etc…
row1 r1, c1 r1, c2 etc…
row2 r2, c1 r2, c2 etc…

Creating a ResultSet

A ResultSet object is created when an .executeQuery() method is called on the database. This method comes from the Statement class.

ResultSet results = statement.executeQuery(sql); // This returns a ResultSet Object

Iterating over ResultSet Objects

The .next() method of ResultSet allows a user to iterate over each row and process data. Remember, when a ResultSet object is returned, the row-pointer is initially established before the first row.

ResultSet results = statement.executeQuery("SELECT * FROM SOME_TABLE");
while (results.next()) {
// This while loop will iterate over all valid rows of the ResultSet
// Do something here with each row of data
}

Database Connections

Database resources are costly and must be closed to ensure data leaks do not occur. One method to ensure the connections are always closed is to put your code in a try-with-resources block that automatically closes all resources after execution.

try (
Connection connection = DriverManager.getConnection(url);
Statement statement = Connection.createStatement();
ResultSet results = statement.executeQuery(sql);
) {
// Do something with 'results' here
} catch (SQLException e) {
System.out.println("There was an error performing this task.");
}

JVM Registration

The DriverManager class registers drivers with the JVM at run time, this is done by dynamically loading the class with Class.forName().

Class.forName("com.sqlite.JDBC.Driver");

Statements

Statement objects execute SQL statements on the relational database, this is done by passing the SQL as an argument to the methods of Statement.

DriverManager

The DriverManager class establishes the connection to the database in the form of a Connection object.

Connection connection = DriverManager.getConnection(url);