Relational Databases Part 3: JDBC
CS 240 – Advanced Programming Concepts
JDBC Overview
Executing SQL from Java
Database Access from Java
Omit transaction steps if you only need to execute a single statement.
JDBC Drivers and Connections
Making the JDBC Driver Available to Your Project
Three Ways:
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<version>8.1.0</version>
</dependency>
implementation group: 'com.mysql', name: 'mysql-connector-j', version: '8.1.0'
Load Database Driver
try {
// Legacy. Modern DB drivers don’t require this
Class.forName("org.sqlite.JDBC");
} catch(ClassNotFoundException e) {
// ERROR! Could not load database driver
}
Open a Database Connection / Start a Transaction
import java.sql.*;
...
String connectionURL = "jdbc:mysql://localhost:3306/BookClub?" +
"user=jerodw&password=mypassword";
Connection connection = null;
try(Connection c = DriverManager.getConnection(connectionURL)) {
connection = c;
// Start a transaction
connection.setAutoCommit(false);
// Use connection ...
} catch(SQLException ex) {
// ERROR
}
Close the connection when you are through with it. Or open it in a try-with-resources statement.
Don’t close before you commit or rollback your transaction.
Executing Queries with JDBC
Execute a Query
List<Book> books = new ArrayList<>();
�String sql =
"select id, title, author, genre, category_id from book";
try(PreparedStatement stmt = connection.prepareStatement(sql);
ResultSet rs = stmt.executeQuery()) {
while(rs.next()) {
int id = rs.getInt(1);
String title = rs.getString(2);
String author = rs.getString(3);
String genre = rs.getString(4);
int categoryId = rs.getInt(5);
books.add(new Book(id, title, author, genre, categoryId));
}
} catch (SQLException ex) {
// ERROR
}
Executing Insert, Update and Delete Statements
Execute an Insert, Update, or Delete
String sql = "update book " +
"set title = ?, author = ?, genre = ?, category_id = ? " +
"where id = ?";
try(PreparedStatement stmt = connection.prepareStatement(sql)) {
stmt.setString(1, book.getTitle());
stmt.setString(2, book.getAuthor());
stmt.setString(3, book.getGenre());
stmt.setInt(4, book.getCategoryId());
stmt.setInt(5, book.getId());
if(stmt.executeUpdate() == 1) {
System.out.println("Updated book " + book.getId());
} else {
System.out.println(
"Failed to update book " + book.getId());
}
} catch(SQLException ex) {
// ERROR
}
Assumes we have a reference named ‘book’ to an object that contains the values we need.
Prevent SQL Injection Attacks with Parameter Replacement in PreparedStatements
1
Citations (Java Database Access with JDBC)
1. Randall Munroe. Exploits of a Mom. xkcd. (CC BY-NC 2.5)
Retrieving Auto-Increment Primary Keys and Ending Transactions
Retrieving Auto-increment PKs (from MySQL)
String sql = "insert into book (title, author, genre, category_id)
values (?, ?, ?, ?)";
try(PreparedStatement stmt = connection.prepareStatement(sql,
Statement.RETURN_GENERATED_KEYS)) {
for(Book book : books) {
stmt.setString(1, book.getTitle());
stmt.setString(2, book.getAuthor());
stmt.setString(3, book.getGenre());
stmt.setInt(4, book.getCategoryId());
if(stmt.executeUpdate() == 1) {
try(ResultSet generatedKeys = stmt.getGeneratedKeys()) {
generatedKeys.next();
int id = generatedKeys.getInt(1); // ID of the inserted book
book.setId(id);
}
System.out.println("Inserted book " + book);
} else {
System.out.println("Failed to insert book");
}
}
}
Commit or Rollback a Transaction
Connection connection = null;
try(Connection c = DriverManager.getConnection(connectionURL)) {
connection = c;
...
connection.commit();
} catch(SQLException ex) {
if(connection != null) {
connection.rollback();
}
throw ex;
}
Close the connection when you are through with it, or open it in a try-with-resources statement.
Usernames, Passwords and User Permissions
Usernames, Passwords and Database Permissions
String connectionURL =
"jdbc:mysql://localhost:3306/BookClub?” +
“user=jerodw&password=mypassword";
Note: In practice, don’t put username and password in source code. Can read from a gitignored config file instead.
Requires a user with permission to access the database:
JDBC: Putting it All Together
Putting It All Together
BLANK SLIDE