Connect Java applications to PostgreSQL databases using the Java Database Connectivity (JDBC) API
Connect Java applications to PostgreSQL databases using the Java Database Connectivity (JDBC) API
Java's platform independence and PostgreSQL's robust feature set make a powerful combination for building enterprise-grade applications. In this lesson, you'll learn how to connect Java applications to PostgreSQL databases using JDBC (Java Database Connectivity), the standard database API for Java.
JDBC is a Java API that provides a standard interface for connecting Java applications to relational databases. It offers:
JDBC operates through a set of interfaces and classes that act as a bridge between your Java application and the database:
By the end of this lesson, you'll be able to build robust Java applications integrated with PostgreSQL databases using industry best practices.
Let's get started with setting up the environment.
Before we start writing code, let's set up our development environment with Java and the PostgreSQL JDBC driver.
The PostgreSQL JDBC driver allows Java applications to connect to PostgreSQL databases. There are several ways to include it in your project:
If you're using Maven, add this dependency to your pom.xml file:
1<dependency>
2 <groupId>org.postgresql</groupId>
3 <artifactId>postgresql</artifactId>
4 <version>42.6.0</version> <!-- Use the latest version -->
5</dependency>If you're using Gradle, add this to your build.gradle file:
1implementation 'org.postgresql:postgresql:42.6.0' // Use the latest versionYou can also manually download the JDBC driver JAR from the [PostgreSQL JDBC Driver website](https://jdbc.postgresql.org/download/) and add it to your project's classpath.
Let's create a simple Java class to test our JDBC connection to PostgreSQL:
1import java.sql.Connection;
2import java.sql.DriverManager;
3import java.sql.ResultSet;
4import java.sql.SQLException;
5import java.sql.Statement;
6
7public class JdbcConnectionTest {
8 public static void main(String[] args) {
9 // JDBC URL, username, and password of PostgreSQL server
10 String url = "jdbc:postgresql://localhost:5432/postgres";
11 String user = "postgres";
12 String password = "your_password"; // Use environment variables in production
13
14 try {
15 // Load PostgreSQL JDBC Driver
16 Class.forName("org.postgresql.Driver");
17
18 // Connect to the database
19 Connection conn = DriverManager.getConnection(url, user, password);
20
21 if (conn != null) {
22 System.out.println("Connected to PostgreSQL server successfully!");
23
24 // Run a simple test query
25 Statement stmt = conn.createStatement();
26 ResultSet rs = stmt.executeQuery("SELECT version()");
27
28 if (rs.next()) {
29 System.out.println("PostgreSQL Server version: " + rs.getString(1));
30 }
31
32 // Close resources
33 rs.close();
34 stmt.close();
35 conn.close();
36 System.out.println("Connection closed.");
37 } else {
38 System.out.println("Failed to connect to PostgreSQL server.");
39 }
40 } catch (ClassNotFoundException e) {
41 System.out.println("PostgreSQL JDBC Driver not found.");
42 e.printStackTrace();
43 } catch (SQLException e) {
44 System.out.println("Connection failed.");
45 e.printStackTrace();
46 }
47 }
48}For this lesson, we'll create a simple "bookstore" database similar to our Python examples:
1-- Connect to PostgreSQL and run these commands
2CREATE DATABASE bookstore;
3
4\c bookstore
5
6CREATE TABLE books (
7 id SERIAL PRIMARY KEY,
8 title VARCHAR(100) NOT NULL,
9 author VARCHAR(100) NOT NULL,
10 price DECIMAL(10,2) NOT NULL,
11 stock INTEGER NOT NULL DEFAULT 0
12);
13
14INSERT INTO books (title, author, price, stock) VALUES
15 ('The Great Gatsby', 'F. Scott Fitzgerald', 12.99, 25),
16 ('To Kill a Mockingbird', 'Harper Lee', 14.99, 30),
17 ('1984', 'George Orwell', 11.99, 20),
18 ('Pride and Prejudice', 'Jane Austen', 9.99, 15),
19 ('The Catcher in the Rye', 'J.D. Salinger', 13.99, 22);The JDBC URL for PostgreSQL follows this format:
1jdbc:postgresql://host:port/database?param1=value1¶m2=value2Where:
Example URLs:
jdbc:postgresql://localhost:5432/bookstorejdbc:postgresql://localhost:5432/bookstore?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactoryWith our environment set up and test database ready, we can now start exploring how to connect Java to PostgreSQL using JDBC.
Now that we have our environment set up, let's explore the fundamental database operations using JDBC.
The first step in any JDBC application is establishing a connection to the database:
1import java.sql.Connection;
2import java.sql.DriverManager;
3import java.sql.SQLException;
4
5public class DbConnection {
6 private static final String URL = "jdbc:postgresql://localhost:5432/bookstore";
7 private static final String USER = "postgres";
8 private static final String PASSWORD = "your_password"; // Use environment variables in production
9
10 public static Connection getConnection() throws SQLException {
11 return DriverManager.getConnection(URL, USER, PASSWORD);
12 }
13
14 // Better approach: Using environment variables
15 public static Connection getConnectionSecure() throws SQLException {
16 String url = System.getenv("DB_URL");
17 String user = System.getenv("DB_USER");
18 String password = System.getenv("DB_PASSWORD");
19
20 if (url == null) url = "jdbc:postgresql://localhost:5432/bookstore";
21 if (user == null) user = "postgres";
22 // Password should be provided as environment variable
23
24 return DriverManager.getConnection(url, user, password);
25 }
26}> Security Best Practice: Never hardcode database credentials in your code. Use environment variables, a configuration file (outside source control), or a secure vault service.
After establishing a connection, you can execute SQL queries using Statement objects:
1import java.sql.Connection;
2import java.sql.ResultSet;
3import java.sql.SQLException;
4import java.sql.Statement;
5import java.util.ArrayList;
6import java.util.List;
7
8public class BookDao {
9 public List<Book> findAllBooks() {
10 List<Book> books = new ArrayList<>();
11
12 // Using try-with-resources to automatically close resources
13 try (Connection conn = DbConnection.getConnection();
14 Statement stmt = conn.createStatement();
15 ResultSet rs = stmt.executeQuery("SELECT id, title, author, price, stock FROM books")) {
16
17 while (rs.next()) {
18 Book book = new Book();
19 book.setId(rs.getLong("id"));
20 book.setTitle(rs.getString("title"));
21 book.setAuthor(rs.getString("author"));
22 book.setPrice(rs.getBigDecimal("price"));
23 book.setStock(rs.getInt("stock"));
24
25 books.add(book);
26 }
27 } catch (SQLException e) {
28 e.printStackTrace();
29 }
30
31 return books;
32 }
33}
34
35// Book model class
36class Book {
37 private Long id;
38 private String title;
39 private String author;
40 private java.math.BigDecimal price;
41 private int stock;
42
43 // Getters and setters
44 public Long getId() { return id; }
45 public void setId(Long id) { this.id = id; }
46
47 public String getTitle() { return title; }
48 public void setTitle(String title) { this.title = title; }
49
50 public String getAuthor() { return author; }
51 public void setAuthor(String author) { this.author = author; }
52
53 public java.math.BigDecimal getPrice() { return price; }
54 public void setPrice(java.math.BigDecimal price) { this.price = price; }
55
56 public int getStock() { return stock; }
57 public void setStock(int stock) { this.stock = stock; }
58
59 @Override
60 public String toString() {
61 return "Book{id=" + id + ", title='" + title + "', author='" + author +
62 "', price=" + price + ", stock=" + stock + '}';
63 }
64}NEVER use string concatenation to build SQL queries. Use PreparedStatement instead to prevent SQL injection:
1public Book findBookById(long id) {
2 String sql = "SELECT id, title, author, price, stock FROM books WHERE id = ?";
3
4 try (Connection conn = DbConnection.getConnection();
5 PreparedStatement pstmt = conn.prepareStatement(sql)) {
6
7 // Set the parameter value (prevents SQL injection)
8 pstmt.setLong(1, id);
9
10 try (ResultSet rs = pstmt.executeQuery()) {
11 if (rs.next()) {
12 Book book = new Book();
13 book.setId(rs.getLong("id"));
14 book.setTitle(rs.getString("title"));
15 book.setAuthor(rs.getString("author"));
16 book.setPrice(rs.getBigDecimal("price"));
17 book.setStock(rs.getInt("stock"));
18 return book;
19 }
20 }
21 } catch (SQLException e) {
22 e.printStackTrace();
23 }
24
25 return null;
26}
27
28public List<Book> findBooksByAuthor(String author) {
29 List<Book> books = new ArrayList<>();
30 String sql = "SELECT id, title, author, price, stock FROM books WHERE author LIKE ?";
31
32 try (Connection conn = DbConnection.getConnection();
33 PreparedStatement pstmt = conn.prepareStatement(sql)) {
34
35 // Set the parameter value (prevents SQL injection)
36 pstmt.setString(1, "%" + author + "%");
37
38 try (ResultSet rs = pstmt.executeQuery()) {
39 while (rs.next()) {
40 Book book = new Book();
41 book.setId(rs.getLong("id"));
42 book.setTitle(rs.getString("title"));
43 book.setAuthor(rs.getString("author"));
44 book.setPrice(rs.getBigDecimal("price"));
45 book.setStock(rs.getInt("stock"));
46 books.add(book);
47 }
48 }
49 } catch (SQLException e) {
50 e.printStackTrace();
51 }
52
53 return books;
54}1public boolean addBook(Book book) {
2 String sql = "INSERT INTO books (title, author, price, stock) VALUES (?, ?, ?, ?)";
3
4 try (Connection conn = DbConnection.getConnection();
5 PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
6
7 pstmt.setString(1, book.getTitle());
8 pstmt.setString(2, book.getAuthor());
9 pstmt.setBigDecimal(3, book.getPrice());
10 pstmt.setInt(4, book.getStock());
11
12 int affectedRows = pstmt.executeUpdate();
13
14 if (affectedRows > 0) {
15 // Get the generated ID
16 try (ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
17 if (generatedKeys.next()) {
18 book.setId(generatedKeys.getLong(1));
19 }
20 }
21 return true;
22 }
23 } catch (SQLException e) {
24 e.printStackTrace();
25 }
26
27 return false;
28}1public boolean updateBook(Book book) {
2 String sql = "UPDATE books SET title = ?, author = ?, price = ?, stock = ? WHERE id = ?";
3
4 try (Connection conn = DbConnection.getConnection();
5 PreparedStatement pstmt = conn.prepareStatement(sql)) {
6
7 pstmt.setString(1, book.getTitle());
8 pstmt.setString(2, book.getAuthor());
9 pstmt.setBigDecimal(3, book.getPrice());
10 pstmt.setInt(4, book.getStock());
11 pstmt.setLong(5, book.getId());
12
13 int affectedRows = pstmt.executeUpdate();
14 return affectedRows > 0;
15 } catch (SQLException e) {
16 e.printStackTrace();
17 }
18
19 return false;
20}1public boolean deleteBook(long id) {
2 String sql = "DELETE FROM books WHERE id = ?";
3
4 try (Connection conn = DbConnection.getConnection();
5 PreparedStatement pstmt = conn.prepareStatement(sql)) {
6
7 pstmt.setLong(1, id);
8
9 int affectedRows = pstmt.executeUpdate();
10 return affectedRows > 0;
11 } catch (SQLException e) {
12 e.printStackTrace();
13 }
14
15 return false;
16}JDBC allows you to retrieve data in different ways from a ResultSet:
1// Accessing columns by index (1-based)
2String title = rs.getString(2); // Assuming 'title' is the second column
3
4// Accessing columns by name (preferred for clarity)
5String title = rs.getString("title");
6
7// Getting different data types
8long id = rs.getLong("id");
9String title = rs.getString("title");
10java.math.BigDecimal price = rs.getBigDecimal("price");
11int stock = rs.getInt("stock");
12java.sql.Date publishDate = rs.getDate("publish_date");
13boolean isAvailable = rs.getBoolean("is_available");
14
15// Checking for null values
16rs.getObject("optional_column");
17if (rs.wasNull()) {
18 // Handle null value
19}These basic operations form the foundation of working with PostgreSQL in Java. In the next section, we'll explore transaction management for maintaining data integrity.
Proper transaction management and error handling are essential for building robust Java database applications. Let's explore how to implement these with JDBC.
In JDBC, each connection starts in auto-commit mode by default (each statement is immediately committed). To control transactions manually, you need to disable auto-commit and explicitly commit or roll back transactions:
1public boolean transferBookStock(long fromBookId, long toBookId, int quantity) {
2 Connection conn = null;
3 PreparedStatement pstmt1 = null;
4 PreparedStatement pstmt2 = null;
5
6 try {
7 // Get connection
8 conn = DbConnection.getConnection();
9
10 // Disable auto-commit to begin a transaction
11 conn.setAutoCommit(false);
12
13 // Check if 'fromBook' has enough stock
14 pstmt1 = conn.prepareStatement(
15 "SELECT stock FROM books WHERE id = ?"
16 );
17 pstmt1.setLong(1, fromBookId);
18 ResultSet rs = pstmt1.executeQuery();
19
20 if (!rs.next() || rs.getInt("stock") < quantity) {
21 // Not enough stock, roll back and return
22 conn.rollback();
23 return false;
24 }
25
26 // Decrement stock from source book
27 pstmt1 = conn.prepareStatement(
28 "UPDATE books SET stock = stock - ? WHERE id = ?"
29 );
30 pstmt1.setInt(1, quantity);
31 pstmt1.setLong(2, fromBookId);
32 pstmt1.executeUpdate();
33
34 // Increment stock for destination book
35 pstmt2 = conn.prepareStatement(
36 "UPDATE books SET stock = stock + ? WHERE id = ?"
37 );
38 pstmt2.setInt(1, quantity);
39 pstmt2.setLong(2, toBookId);
40 pstmt2.executeUpdate();
41
42 // Log the transfer
43 PreparedStatement pstmt3 = conn.prepareStatement(
44 "INSERT INTO stock_transfers (from_book_id, to_book_id, quantity, transfer_date) VALUES (?, ?, ?, NOW())"
45 );
46 pstmt3.setLong(1, fromBookId);
47 pstmt3.setLong(2, toBookId);
48 pstmt3.setInt(3, quantity);
49 pstmt3.executeUpdate();
50
51 // Commit the transaction
52 conn.commit();
53 return true;
54 } catch (SQLException e) {
55 // Roll back on error
56 try {
57 if (conn != null) conn.rollback();
58 } catch (SQLException ex) {
59 ex.printStackTrace();
60 }
61 e.printStackTrace();
62 return false;
63 } finally {
64 // Reset auto-commit mode and close resources
65 try {
66 if (conn != null) {
67 conn.setAutoCommit(true);
68 conn.close();
69 }
70 if (pstmt1 != null) pstmt1.close();
71 if (pstmt2 != null) pstmt2.close();
72 } catch (SQLException e) {
73 e.printStackTrace();
74 }
75 }
76}The above code can be improved using try-with-resources for better resource management:
1public boolean transferBookStock(long fromBookId, long toBookId, int quantity) {
2 String checkStockSql = "SELECT stock FROM books WHERE id = ?";
3 String decrementSql = "UPDATE books SET stock = stock - ? WHERE id = ?";
4 String incrementSql = "UPDATE books SET stock = stock + ? WHERE id = ?";
5 String logTransferSql =
6 "INSERT INTO stock_transfers (from_book_id, to_book_id, quantity, transfer_date) " +
7 "VALUES (?, ?, ?, NOW())";
8
9 try (Connection conn = DbConnection.getConnection()) {
10 // Disable auto-commit
11 conn.setAutoCommit(false);
12
13 try {
14 // Check if 'fromBook' has enough stock
15 try (PreparedStatement pstmt = conn.prepareStatement(checkStockSql)) {
16 pstmt.setLong(1, fromBookId);
17 ResultSet rs = pstmt.executeQuery();
18
19 if (!rs.next() || rs.getInt("stock") < quantity) {
20 conn.rollback();
21 return false;
22 }
23 }
24
25 // Decrement stock from source book
26 try (PreparedStatement pstmt = conn.prepareStatement(decrementSql)) {
27 pstmt.setInt(1, quantity);
28 pstmt.setLong(2, fromBookId);
29 pstmt.executeUpdate();
30 }
31
32 // Increment stock for destination book
33 try (PreparedStatement pstmt = conn.prepareStatement(incrementSql)) {
34 pstmt.setInt(1, quantity);
35 pstmt.setLong(2, toBookId);
36 pstmt.executeUpdate();
37 }
38
39 // Log the transfer
40 try (PreparedStatement pstmt = conn.prepareStatement(logTransferSql)) {
41 pstmt.setLong(1, fromBookId);
42 pstmt.setLong(2, toBookId);
43 pstmt.setInt(3, quantity);
44 pstmt.executeUpdate();
45 }
46
47 // Commit the transaction
48 conn.commit();
49 return true;
50 } catch (SQLException e) {
51 conn.rollback();
52 e.printStackTrace();
53 return false;
54 } finally {
55 // Reset auto-commit mode
56 conn.setAutoCommit(true);
57 }
58 } catch (SQLException e) {
59 e.printStackTrace();
60 return false;
61 }
62}The SQLException class provides detailed information about database errors:
1try {
2 // Database operations
3} catch (SQLException e) {
4 System.err.println("Database error occurred:");
5 System.err.println("SQL State: " + e.getSQLState());
6 System.err.println("Error Code: " + e.getErrorCode());
7 System.err.println("Message: " + e.getMessage());
8
9 // Handle specific error codes
10 if (e.getSQLState().equals("23505")) { // Unique violation
11 System.err.println("A duplicate key was found!");
12 } else if (e.getSQLState().equals("23503")) { // Foreign key violation
13 System.err.println("A foreign key constraint was violated!");
14 }
15
16 e.printStackTrace();
17}JDBC allows you to set the transaction isolation level to control transaction behavior:
1try (Connection conn = DbConnection.getConnection()) {
2 // Set isolation level
3 conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
4
5 // Disable auto-commit for transaction
6 conn.setAutoCommit(false);
7
8 // Perform database operations...
9
10 // Commit transaction
11 conn.commit();
12}JDBC supports these isolation levels:
For operations that process multiple rows, batch processing can significantly improve performance:
1public void addBooksBatch(List<Book> books) {
2 String sql = "INSERT INTO books (title, author, price, stock) VALUES (?, ?, ?, ?)";
3
4 try (Connection conn = DbConnection.getConnection();
5 PreparedStatement pstmt = conn.prepareStatement(sql)) {
6
7 // Disable auto-commit for batch
8 conn.setAutoCommit(false);
9
10 for (Book book : books) {
11 pstmt.setString(1, book.getTitle());
12 pstmt.setString(2, book.getAuthor());
13 pstmt.setBigDecimal(3, book.getPrice());
14 pstmt.setInt(4, book.getStock());
15
16 // Add to batch instead of executing immediately
17 pstmt.addBatch();
18 }
19
20 // Execute the entire batch
21 int[] results = pstmt.executeBatch();
22
23 // Commit transaction
24 conn.commit();
25
26 System.out.println("Added " + results.length + " books in batch");
27 } catch (SQLException e) {
28 e.printStackTrace();
29 }
30}For complex operations, you might need savepoints to create "checkpoints" within a transaction:
1public void complexInventoryOperation(long bookId) {
2 try (Connection conn = DbConnection.getConnection()) {
3 try {
4 // Start transaction
5 conn.setAutoCommit(false);
6
7 // First operation: Update price
8 try (PreparedStatement pstmt = conn.prepareStatement(
9 "UPDATE books SET price = price * 1.1 WHERE id = ?")) {
10 pstmt.setLong(1, bookId);
11 pstmt.executeUpdate();
12 }
13
14 // Create a savepoint after updating the price
15 Savepoint priceUpdateSavepoint = conn.setSavepoint("PriceUpdate");
16
17 try {
18 // Second operation: Update stock
19 try (PreparedStatement pstmt = conn.prepareStatement(
20 "UPDATE books SET stock = stock - 5 WHERE id = ?")) {
21 pstmt.setLong(1, bookId);
22 pstmt.executeUpdate();
23 }
24
25 // Check if stock is now negative
26 try (PreparedStatement pstmt = conn.prepareStatement(
27 "SELECT stock FROM books WHERE id = ?")) {
28 pstmt.setLong(1, bookId);
29 ResultSet rs = pstmt.executeQuery();
30
31 if (rs.next() && rs.getInt("stock") < 0) {
32 // Roll back to savepoint
33 conn.rollback(priceUpdateSavepoint);
34 System.out.println("Stock update rolled back, but price update kept");
35 } else {
36 System.out.println("Both price and stock updated successfully");
37 }
38 }
39 } catch (SQLException e) {
40 // Roll back to savepoint on error
41 conn.rollback(priceUpdateSavepoint);
42 System.out.println("Stock update failed, rolled back to savepoint: " + e.getMessage());
43 }
44
45 // Commit what's left
46 conn.commit();
47 } catch (SQLException e) {
48 conn.rollback();
49 System.out.println("Entire transaction failed: " + e.getMessage());
50 e.printStackTrace();
51 } finally {
52 conn.setAutoCommit(true);
53 }
54 } catch (SQLException e) {
55 e.printStackTrace();
56 }
57}Proper transaction and error handling are critical for data integrity in database applications. In the next section, we'll look at connection pooling and design patterns for production Java applications.
For production Java applications, we need advanced techniques for efficient database access. Let's explore connection pooling and design patterns for database operations.
Creating new database connections is an expensive operation. Connection pooling solves this by maintaining a pool of reusable connections, significantly improving performance.
HikariCP is one of the fastest and most reliable JDBC connection pools for Java applications:
1<dependency>
2 <groupId>com.zaxxer</groupId>
3 <artifactId>HikariCP</artifactId>
4 <version>5.0.1</version> <!-- Use the latest version -->
5</dependency>1import com.zaxxer.hikari.HikariConfig;
2import com.zaxxer.hikari.HikariDataSource;
3
4import javax.sql.DataSource;
5import java.sql.Connection;
6import java.sql.SQLException;
7
8public class ConnectionPool {
9 private static HikariDataSource dataSource;
10
11 static {
12 // Initialize the connection pool once
13 HikariConfig config = new HikariConfig();
14 config.setJdbcUrl("jdbc:postgresql://localhost:5432/bookstore");
15 config.setUsername("postgres");
16 config.setPassword("your_password");
17
18 // Pool configuration
19 config.setMaximumPoolSize(10);
20 config.setMinimumIdle(5);
21 config.setIdleTimeout(300000);
22 config.setMaxLifetime(1800000);
23
24 // Connection testing and validation
25 config.setConnectionTestQuery("SELECT 1");
26 config.setValidationTimeout(5000);
27
28 // Other useful settings
29 config.setPoolName("BookstoreConnectionPool");
30 config.addDataSourceProperty("cachePrepStmts", "true");
31 config.addDataSourceProperty("prepStmtCacheSize", "250");
32 config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
33
34 dataSource = new HikariDataSource(config);
35 }
36
37 public static Connection getConnection() throws SQLException {
38 return dataSource.getConnection();
39 }
40
41 public static DataSource getDataSource() {
42 return dataSource;
43 }
44
45 public static void close() {
46 if (dataSource != null && !dataSource.isClosed()) {
47 dataSource.close();
48 }
49 }
50}1public List<Book> findAllBooks() {
2 List<Book> books = new ArrayList<>();
3
4 try (Connection conn = ConnectionPool.getConnection();
5 Statement stmt = conn.createStatement();
6 ResultSet rs = stmt.executeQuery("SELECT id, title, author, price, stock FROM books")) {
7
8 // Process results
9 while (rs.next()) {
10 Book book = new Book();
11 book.setId(rs.getLong("id"));
12 book.setTitle(rs.getString("title"));
13 book.setAuthor(rs.getString("author"));
14 book.setPrice(rs.getBigDecimal("price"));
15 book.setStock(rs.getInt("stock"));
16
17 books.add(book);
18 }
19 } catch (SQLException e) {
20 e.printStackTrace();
21 }
22
23 return books;
24}The DAO pattern provides a clean separation between the business logic and the persistence layer:
1public interface BookDao {
2 List<Book> findAll() throws SQLException;
3 Book findById(long id) throws SQLException;
4 Book create(Book book) throws SQLException;
5 boolean update(Book book) throws SQLException;
6 boolean delete(long id) throws SQLException;
7 List<Book> findByAuthor(String author) throws SQLException;
8}1public class BookDaoImpl implements BookDao {
2 private DataSource dataSource;
3
4 public BookDaoImpl(DataSource dataSource) {
5 this.dataSource = dataSource;
6 }
7
8 @Override
9 public List<Book> findAll() throws SQLException {
10 List<Book> books = new ArrayList<>();
11 String sql = "SELECT id, title, author, price, stock FROM books";
12
13 try (Connection conn = dataSource.getConnection();
14 PreparedStatement pstmt = conn.prepareStatement(sql);
15 ResultSet rs = pstmt.executeQuery()) {
16
17 while (rs.next()) {
18 books.add(mapResultSetToBook(rs));
19 }
20 }
21
22 return books;
23 }
24
25 @Override
26 public Book findById(long id) throws SQLException {
27 String sql = "SELECT id, title, author, price, stock FROM books WHERE id = ?";
28
29 try (Connection conn = dataSource.getConnection();
30 PreparedStatement pstmt = conn.prepareStatement(sql)) {
31
32 pstmt.setLong(1, id);
33
34 try (ResultSet rs = pstmt.executeQuery()) {
35 if (rs.next()) {
36 return mapResultSetToBook(rs);
37 }
38 }
39 }
40
41 return null;
42 }
43
44 @Override
45 public Book create(Book book) throws SQLException {
46 String sql = "INSERT INTO books (title, author, price, stock) VALUES (?, ?, ?, ?) RETURNING id";
47
48 try (Connection conn = dataSource.getConnection();
49 PreparedStatement pstmt = conn.prepareStatement(sql)) {
50
51 pstmt.setString(1, book.getTitle());
52 pstmt.setString(2, book.getAuthor());
53 pstmt.setBigDecimal(3, book.getPrice());
54 pstmt.setInt(4, book.getStock());
55
56 try (ResultSet rs = pstmt.executeQuery()) {
57 if (rs.next()) {
58 book.setId(rs.getLong(1));
59 return book;
60 }
61 }
62 }
63
64 return null;
65 }
66
67 @Override
68 public boolean update(Book book) throws SQLException {
69 String sql = "UPDATE books SET title = ?, author = ?, price = ?, stock = ? WHERE id = ?";
70
71 try (Connection conn = dataSource.getConnection();
72 PreparedStatement pstmt = conn.prepareStatement(sql)) {
73
74 pstmt.setString(1, book.getTitle());
75 pstmt.setString(2, book.getAuthor());
76 pstmt.setBigDecimal(3, book.getPrice());
77 pstmt.setInt(4, book.getStock());
78 pstmt.setLong(5, book.getId());
79
80 int rowsAffected = pstmt.executeUpdate();
81 return rowsAffected > 0;
82 }
83 }
84
85 @Override
86 public boolean delete(long id) throws SQLException {
87 String sql = "DELETE FROM books WHERE id = ?";
88
89 try (Connection conn = dataSource.getConnection();
90 PreparedStatement pstmt = conn.prepareStatement(sql)) {
91
92 pstmt.setLong(1, id);
93
94 int rowsAffected = pstmt.executeUpdate();
95 return rowsAffected > 0;
96 }
97 }
98
99 @Override
100 public List<Book> findByAuthor(String author) throws SQLException {
101 List<Book> books = new ArrayList<>();
102 String sql = "SELECT id, title, author, price, stock FROM books WHERE author LIKE ?";
103
104 try (Connection conn = dataSource.getConnection();
105 PreparedStatement pstmt = conn.prepareStatement(sql)) {
106
107 pstmt.setString(1, "%" + author + "%");
108
109 try (ResultSet rs = pstmt.executeQuery()) {
110 while (rs.next()) {
111 books.add(mapResultSetToBook(rs));
112 }
113 }
114 }
115
116 return books;
117 }
118
119 private Book mapResultSetToBook(ResultSet rs) throws SQLException {
120 Book book = new Book();
121 book.setId(rs.getLong("id"));
122 book.setTitle(rs.getString("title"));
123 book.setAuthor(rs.getString("author"));
124 book.setPrice(rs.getBigDecimal("price"));
125 book.setStock(rs.getInt("stock"));
126 return book;
127 }
128}The service layer sits between controllers and DAOs, handling business logic:
1public class BookService {
2 private BookDao bookDao;
3
4 public BookService(BookDao bookDao) {
5 this.bookDao = bookDao;
6 }
7
8 public List<Book> getAllBooks() {
9 try {
10 return bookDao.findAll();
11 } catch (SQLException e) {
12 e.printStackTrace();
13 return Collections.emptyList();
14 }
15 }
16
17 public Book getBookById(long id) {
18 try {
19 return bookDao.findById(id);
20 } catch (SQLException e) {
21 e.printStackTrace();
22 return null;
23 }
24 }
25
26 public Book addBook(Book book) {
27 try {
28 return bookDao.create(book);
29 } catch (SQLException e) {
30 e.printStackTrace();
31 return null;
32 }
33 }
34
35 public boolean updateBook(Book book) {
36 try {
37 return bookDao.update(book);
38 } catch (SQLException e) {
39 e.printStackTrace();
40 return false;
41 }
42 }
43
44 public boolean deleteBook(long id) {
45 try {
46 return bookDao.delete(id);
47 } catch (SQLException e) {
48 e.printStackTrace();
49 return false;
50 }
51 }
52
53 public boolean transferStock(long fromBookId, long toBookId, int quantity) {
54 Connection conn = null;
55
56 try {
57 conn = ConnectionPool.getConnection();
58 conn.setAutoCommit(false);
59
60 // Get source book
61 Book fromBook = findBookById(conn, fromBookId);
62 if (fromBook == null || fromBook.getStock() < quantity) {
63 if (conn != null) conn.rollback();
64 return false;
65 }
66
67 // Get destination book
68 Book toBook = findBookById(conn, toBookId);
69 if (toBook == null) {
70 if (conn != null) conn.rollback();
71 return false;
72 }
73
74 // Update stock
75 fromBook.setStock(fromBook.getStock() - quantity);
76 toBook.setStock(toBook.getStock() + quantity);
77
78 // Save changes
79 boolean updateFrom = updateBookInTransaction(conn, fromBook);
80 boolean updateTo = updateBookInTransaction(conn, toBook);
81
82 if (updateFrom && updateTo) {
83 conn.commit();
84 return true;
85 } else {
86 conn.rollback();
87 return false;
88 }
89 } catch (SQLException e) {
90 try {
91 if (conn != null) conn.rollback();
92 } catch (SQLException ex) {
93 ex.printStackTrace();
94 }
95 e.printStackTrace();
96 return false;
97 } finally {
98 try {
99 if (conn != null) {
100 conn.setAutoCommit(true);
101 conn.close();
102 }
103 } catch (SQLException e) {
104 e.printStackTrace();
105 }
106 }
107 }
108
109 private Book findBookById(Connection conn, long id) throws SQLException {
110 String sql = "SELECT id, title, author, price, stock FROM books WHERE id = ?";
111
112 try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
113 pstmt.setLong(1, id);
114
115 try (ResultSet rs = pstmt.executeQuery()) {
116 if (rs.next()) {
117 Book book = new Book();
118 book.setId(rs.getLong("id"));
119 book.setTitle(rs.getString("title"));
120 book.setAuthor(rs.getString("author"));
121 book.setPrice(rs.getBigDecimal("price"));
122 book.setStock(rs.getInt("stock"));
123 return book;
124 }
125 }
126 }
127
128 return null;
129 }
130
131 private boolean updateBookInTransaction(Connection conn, Book book) throws SQLException {
132 String sql = "UPDATE books SET stock = ? WHERE id = ?";
133
134 try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
135 pstmt.setInt(1, book.getStock());
136 pstmt.setLong(2, book.getId());
137
138 int rowsAffected = pstmt.executeUpdate();
139 return rowsAffected > 0;
140 }
141 }
142}Similar to DAO but more domain-focused, the Repository pattern abstracts the persistence layer:
1public interface BookRepository {
2 List<Book> findAll();
3 Book findById(long id);
4 Book save(Book book);
5 void delete(Book book);
6 List<Book> findByAuthor(String author);
7 boolean transferStock(long fromBookId, long toBookId, int quantity);
8}
9
10public class JdbcBookRepository implements BookRepository {
11 private DataSource dataSource;
12
13 public JdbcBookRepository(DataSource dataSource) {
14 this.dataSource = dataSource;
15 }
16
17 // Implementation similar to the DAO example above
18}These design patterns provide a clean, maintainable approach to database access in Java applications. In the next section, we'll put it all together in a practical example.
Let's build a RESTful API for our bookstore using Java, JDBC, and Spring Boot. This example will combine the concepts we've learned into a complete application.
First, set up a Spring Boot project with the necessary dependencies:
1<dependencies>
2 <dependency>
3 <groupId>org.springframework.boot</groupId>
4 <artifactId>spring-boot-starter-web</artifactId>
5 </dependency>
6 <dependency>
7 <groupId>org.springframework.boot</groupId>
8 <artifactId>spring-boot-starter-jdbc</artifactId>
9 </dependency>
10 <dependency>
11 <groupId>org.postgresql</groupId>
12 <artifactId>postgresql</artifactId>
13 </dependency>
14 <dependency>
15 <groupId>com.zaxxer</groupId>
16 <artifactId>HikariCP</artifactId>
17 </dependency>
18</dependencies>1bookstore-api/
2├── pom.xml
3├── src/
4│ └── main/
5│ ├── java/
6│ │ └── com/
7│ │ └── example/
8│ │ └── bookstore/
9│ │ ├── BookstoreApplication.java
10│ │ ├── config/
11│ │ │ └── DatabaseConfig.java
12│ │ ├── controller/
13│ │ │ └── BookController.java
14│ │ ├── model/
15│ │ │ └── Book.java
16│ │ ├── repository/
17│ │ │ ├── BookRepository.java
18│ │ │ └── JdbcBookRepository.java
19│ │ └── service/
20│ │ ├── BookService.java
21│ │ └── BookServiceImpl.java
22│ └── resources/
23│ └── application.properties1# Database Configuration
2spring.datasource.url=jdbc:postgresql://localhost:5432/bookstore
3spring.datasource.username=postgres
4spring.datasource.password=your_password
5spring.datasource.driver-class-name=org.postgresql.Driver
6
7# HikariCP Configuration
8spring.datasource.hikari.maximum-pool-size=10
9spring.datasource.hikari.minimum-idle=5
10spring.datasource.hikari.idle-timeout=300000
11spring.datasource.hikari.pool-name=BookstoreHikariCP
12
13# Server Configuration
14server.port=80801package com.example.bookstore.config;
2
3import javax.sql.DataSource;
4
5import org.springframework.boot.context.properties.ConfigurationProperties;
6import org.springframework.boot.jdbc.DataSourceBuilder;
7import org.springframework.context.annotation.Bean;
8import org.springframework.context.annotation.Configuration;
9import org.springframework.jdbc.core.JdbcTemplate;
10
11@Configuration
12public class DatabaseConfig {
13
14 @Bean
15 @ConfigurationProperties("spring.datasource")
16 public DataSource dataSource() {
17 return DataSourceBuilder.create().build();
18 }
19
20 @Bean
21 public JdbcTemplate jdbcTemplate(DataSource dataSource) {
22 return new JdbcTemplate(dataSource);
23 }
24}1package com.example.bookstore.model;
2
3import java.math.BigDecimal;
4
5public class Book {
6 private Long id;
7 private String title;
8 private String author;
9 private BigDecimal price;
10 private int stock;
11
12 // Constructors
13 public Book() {}
14
15 public Book(Long id, String title, String author, BigDecimal price, int stock) {
16 this.id = id;
17 this.title = title;
18 this.author = author;
19 this.price = price;
20 this.stock = stock;
21 }
22
23 // Getters and setters
24 public Long getId() { return id; }
25 public void setId(Long id) { this.id = id; }
26
27 public String getTitle() { return title; }
28 public void setTitle(String title) { this.title = title; }
29
30 public String getAuthor() { return author; }
31 public void setAuthor(String author) { this.author = author; }
32
33 public BigDecimal getPrice() { return price; }
34 public void setPrice(BigDecimal price) { this.price = price; }
35
36 public int getStock() { return stock; }
37 public void setStock(int stock) { this.stock = stock; }
38
39 @Override
40 public String toString() {
41 return "Book{id=" + id + ", title='" + title + "', author='" + author +
42 "', price=" + price + ", stock=" + stock + '}';
43 }
44}1package com.example.bookstore.repository;
2
3import java.util.List;
4import com.example.bookstore.model.Book;
5
6public interface BookRepository {
7 List<Book> findAll();
8 Book findById(Long id);
9 Book save(Book book);
10 boolean update(Book book);
11 boolean delete(Long id);
12 List<Book> findByAuthor(String author);
13 boolean transferStock(Long fromBookId, Long toBookId, int quantity);
14}1package com.example.bookstore.repository;
2
3import java.sql.Connection;
4import java.sql.PreparedStatement;
5import java.sql.ResultSet;
6import java.sql.SQLException;
7import java.util.ArrayList;
8import java.util.List;
9
10import javax.sql.DataSource;
11
12import org.springframework.beans.factory.annotation.Autowired;
13import org.springframework.dao.DataAccessException;
14import org.springframework.jdbc.core.JdbcTemplate;
15import org.springframework.jdbc.core.PreparedStatementCreator;
16import org.springframework.jdbc.core.RowMapper;
17import org.springframework.jdbc.support.GeneratedKeyHolder;
18import org.springframework.jdbc.support.KeyHolder;
19import org.springframework.stereotype.Repository;
20
21import com.example.bookstore.model.Book;
22
23@Repository
24public class JdbcBookRepository implements BookRepository {
25
26 private final JdbcTemplate jdbcTemplate;
27 private final DataSource dataSource;
28
29 private final RowMapper<Book> bookRowMapper = (rs, rowNum) -> {
30 Book book = new Book();
31 book.setId(rs.getLong("id"));
32 book.setTitle(rs.getString("title"));
33 book.setAuthor(rs.getString("author"));
34 book.setPrice(rs.getBigDecimal("price"));
35 book.setStock(rs.getInt("stock"));
36 return book;
37 };
38
39 @Autowired
40 public JdbcBookRepository(JdbcTemplate jdbcTemplate, DataSource dataSource) {
41 this.jdbcTemplate = jdbcTemplate;
42 this.dataSource = dataSource;
43 }
44
45 @Override
46 public List<Book> findAll() {
47 String sql = "SELECT id, title, author, price, stock FROM books";
48 return jdbcTemplate.query(sql, bookRowMapper);
49 }
50
51 @Override
52 public Book findById(Long id) {
53 String sql = "SELECT id, title, author, price, stock FROM books WHERE id = ?";
54 try {
55 return jdbcTemplate.queryForObject(sql, bookRowMapper, id);
56 } catch (DataAccessException e) {
57 return null;
58 }
59 }
60
61 @Override
62 public Book save(Book book) {
63 String sql = "INSERT INTO books (title, author, price, stock) VALUES (?, ?, ?, ?)";
64 KeyHolder keyHolder = new GeneratedKeyHolder();
65
66 jdbcTemplate.update(new PreparedStatementCreator() {
67 @Override
68 public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
69 PreparedStatement ps = connection.prepareStatement(sql, new String[]{"id"});
70 ps.setString(1, book.getTitle());
71 ps.setString(2, book.getAuthor());
72 ps.setBigDecimal(3, book.getPrice());
73 ps.setInt(4, book.getStock());
74 return ps;
75 }
76 }, keyHolder);
77
78 book.setId(keyHolder.getKey().longValue());
79 return book;
80 }
81
82 @Override
83 public boolean update(Book book) {
84 String sql = "UPDATE books SET title = ?, author = ?, price = ?, stock = ? WHERE id = ?";
85 int rowsAffected = jdbcTemplate.update(sql, book.getTitle(), book.getAuthor(),
86 book.getPrice(), book.getStock(), book.getId());
87 return rowsAffected > 0;
88 }
89
90 @Override
91 public boolean delete(Long id) {
92 String sql = "DELETE FROM books WHERE id = ?";
93 int rowsAffected = jdbcTemplate.update(sql, id);
94 return rowsAffected > 0;
95 }
96
97 @Override
98 public List<Book> findByAuthor(String author) {
99 String sql = "SELECT id, title, author, price, stock FROM books WHERE author LIKE ?";
100 return jdbcTemplate.query(sql, bookRowMapper, "%" + author + "%");
101 }
102
103 @Override
104 public boolean transferStock(Long fromBookId, Long toBookId, int quantity) {
105 Connection conn = null;
106
107 try {
108 conn = dataSource.getConnection();
109 conn.setAutoCommit(false);
110
111 // Check if 'fromBook' has enough stock
112 String checkStockSql = "SELECT stock FROM books WHERE id = ?";
113 PreparedStatement checkStmt = conn.prepareStatement(checkStockSql);
114 checkStmt.setLong(1, fromBookId);
115 ResultSet rs = checkStmt.executeQuery();
116
117 if (!rs.next() || rs.getInt("stock") < quantity) {
118 conn.rollback();
119 return false;
120 }
121
122 // Decrement stock from source book
123 String decrementSql = "UPDATE books SET stock = stock - ? WHERE id = ?";
124 PreparedStatement decrementStmt = conn.prepareStatement(decrementSql);
125 decrementStmt.setInt(1, quantity);
126 decrementStmt.setLong(2, fromBookId);
127 decrementStmt.executeUpdate();
128
129 // Increment stock for destination book
130 String incrementSql = "UPDATE books SET stock = stock + ? WHERE id = ?";
131 PreparedStatement incrementStmt = conn.prepareStatement(incrementSql);
132 incrementStmt.setInt(1, quantity);
133 incrementStmt.setLong(2, toBookId);
134 incrementStmt.executeUpdate();
135
136 // Log the transfer
137 String logTransferSql =
138 "INSERT INTO stock_transfers (from_book_id, to_book_id, quantity, transfer_date) " +
139 "VALUES (?, ?, ?, NOW())";
140 PreparedStatement logStmt = conn.prepareStatement(logTransferSql);
141 logStmt.setLong(1, fromBookId);
142 logStmt.setLong(2, toBookId);
143 logStmt.setInt(3, quantity);
144 logStmt.executeUpdate();
145
146 // Commit the transaction
147 conn.commit();
148 return true;
149 } catch (SQLException e) {
150 try {
151 if (conn != null) conn.rollback();
152 } catch (SQLException ex) {
153 ex.printStackTrace();
154 }
155 e.printStackTrace();
156 return false;
157 } finally {
158 try {
159 if (conn != null) {
160 conn.setAutoCommit(true);
161 conn.close();
162 }
163 } catch (SQLException e) {
164 e.printStackTrace();
165 }
166 }
167 }
168}1package com.example.bookstore.service;
2
3import java.util.List;
4import com.example.bookstore.model.Book;
5
6public interface BookService {
7 List<Book> getAllBooks();
8 Book getBookById(Long id);
9 Book createBook(Book book);
10 boolean updateBook(Book book);
11 boolean deleteBook(Long id);
12 List<Book> getBooksByAuthor(String author);
13 boolean transferStock(Long fromBookId, Long toBookId, int quantity);
14}1package com.example.bookstore.service;
2
3import java.util.List;
4
5import org.springframework.beans.factory.annotation.Autowired;
6import org.springframework.stereotype.Service;
7
8import com.example.bookstore.model.Book;
9import com.example.bookstore.repository.BookRepository;
10
11@Service
12public class BookServiceImpl implements BookService {
13
14 private final BookRepository bookRepository;
15
16 @Autowired
17 public BookServiceImpl(BookRepository bookRepository) {
18 this.bookRepository = bookRepository;
19 }
20
21 @Override
22 public List<Book> getAllBooks() {
23 return bookRepository.findAll();
24 }
25
26 @Override
27 public Book getBookById(Long id) {
28 return bookRepository.findById(id);
29 }
30
31 @Override
32 public Book createBook(Book book) {
33 return bookRepository.save(book);
34 }
35
36 @Override
37 public boolean updateBook(Book book) {
38 return bookRepository.update(book);
39 }
40
41 @Override
42 public boolean deleteBook(Long id) {
43 return bookRepository.delete(id);
44 }
45
46 @Override
47 public List<Book> getBooksByAuthor(String author) {
48 return bookRepository.findByAuthor(author);
49 }
50
51 @Override
52 public boolean transferStock(Long fromBookId, Long toBookId, int quantity) {
53 return bookRepository.transferStock(fromBookId, toBookId, quantity);
54 }
55}1package com.example.bookstore.controller;
2
3import java.util.List;
4
5import org.springframework.beans.factory.annotation.Autowired;
6import org.springframework.http.HttpStatus;
7import org.springframework.http.ResponseEntity;
8import org.springframework.web.bind.annotation.DeleteMapping;
9import org.springframework.web.bind.annotation.GetMapping;
10import org.springframework.web.bind.annotation.PathVariable;
11import org.springframework.web.bind.annotation.PostMapping;
12import org.springframework.web.bind.annotation.PutMapping;
13import org.springframework.web.bind.annotation.RequestBody;
14import org.springframework.web.bind.annotation.RequestMapping;
15import org.springframework.web.bind.annotation.RequestParam;
16import org.springframework.web.bind.annotation.RestController;
17
18import com.example.bookstore.model.Book;
19import com.example.bookstore.service.BookService;
20
21@RestController
22@RequestMapping("/api/books")
23public class BookController {
24
25 private final BookService bookService;
26
27 @Autowired
28 public BookController(BookService bookService) {
29 this.bookService = bookService;
30 }
31
32 @GetMapping
33 public ResponseEntity<List<Book>> getAllBooks(
34 @RequestParam(required = false) String author) {
35
36 if (author != null && !author.isEmpty()) {
37 return ResponseEntity.ok(bookService.getBooksByAuthor(author));
38 }
39
40 return ResponseEntity.ok(bookService.getAllBooks());
41 }
42
43 @GetMapping("/{id}")
44 public ResponseEntity<Book> getBookById(@PathVariable Long id) {
45 Book book = bookService.getBookById(id);
46
47 if (book == null) {
48 return ResponseEntity.notFound().build();
49 }
50
51 return ResponseEntity.ok(book);
52 }
53
54 @PostMapping
55 public ResponseEntity<Book> createBook(@RequestBody Book book) {
56 Book createdBook = bookService.createBook(book);
57 return ResponseEntity.status(HttpStatus.CREATED).body(createdBook);
58 }
59
60 @PutMapping("/{id}")
61 public ResponseEntity<Book> updateBook(@PathVariable Long id, @RequestBody Book book) {
62 Book existingBook = bookService.getBookById(id);
63
64 if (existingBook == null) {
65 return ResponseEntity.notFound().build();
66 }
67
68 book.setId(id);
69 boolean updated = bookService.updateBook(book);
70
71 if (!updated) {
72 return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).build();
73 }
74
75 return ResponseEntity.ok(book);
76 }
77
78 @DeleteMapping("/{id}")
79 public ResponseEntity<Void> deleteBook(@PathVariable Long id) {
80 Book existingBook = bookService.getBookById(id);
81
82 if (existingBook == null) {
83 return ResponseEntity.notFound().build();
84 }
85
86 boolean deleted = bookService.deleteBook(id);
87
88 if (!deleted) {
89 return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).build();
90 }
91
92 return ResponseEntity.noContent().build();
93 }
94
95 @PostMapping("/transfer")
96 public ResponseEntity<String> transferStock(
97 @RequestParam Long fromBookId,
98 @RequestParam Long toBookId,
99 @RequestParam int quantity) {
100
101 if (quantity <= 0) {
102 return ResponseEntity.badRequest().body("Quantity must be greater than 0");
103 }
104
105 boolean transferred = bookService.transferStock(fromBookId, toBookId, quantity);
106
107 if (!transferred) {
108 return ResponseEntity.badRequest()
109 .body("Failed to transfer stock. Check if books exist and have sufficient stock.");
110 }
111
112 return ResponseEntity.ok("Successfully transferred " + quantity +
113 " stock from book " + fromBookId + " to book " + toBookId);
114 }
115}1package com.example.bookstore;
2
3import org.springframework.boot.SpringApplication;
4import org.springframework.boot.autoconfigure.SpringBootApplication;
5
6@SpringBootApplication
7public class BookstoreApplication {
8 public static void main(String[] args) {
9 SpringApplication.run(BookstoreApplication.class, args);
10 }
11}1# Get all books
2curl http://localhost:8080/api/books
3
4# Get a specific book
5curl http://localhost:8080/api/books/1
6
7# Search for books by author
8curl http://localhost:8080/api/books?author=Fitzgerald
9
10# Add a new book
11curl -X POST http://localhost:8080/api/books \
12 -H "Content-Type: application/json" \
13 -d '{"title":"The Alchemist","author":"Paulo Coelho","price":10.99,"stock":25}'
14
15# Update a book
16curl -X PUT http://localhost:8080/api/books/1 \
17 -H "Content-Type: application/json" \
18 -d '{"title":"The Great Gatsby","author":"F. Scott Fitzgerald","price":13.99,"stock":30}'
19
20# Delete a book
21curl -X DELETE http://localhost:8080/api/books/6
22
23# Transfer stock between books
24curl -X POST "http://localhost:8080/api/books/transfer?fromBookId=1&toBookId=2&quantity=5"This Spring Boot application demonstrates:
This example provides a production-ready foundation that could be extended with:
In the next section, we'll compare JDBC with other Java database access technologies.
Let's review best practices for using Java with PostgreSQL, and compare JDBC with other technologies.
1// DO: Use connection pooling
2DataSource dataSource = new HikariDataSource(config);
3try (Connection conn = dataSource.getConnection()) {
4 // Use connection
5}
6
7// DON'T: Create new connections for each operation
8Connection conn = DriverManager.getConnection(url, user, password);1// DO: Use prepared statements
2String sql = "SELECT * FROM books WHERE author = ?";
3PreparedStatement pstmt = conn.prepareStatement(sql);
4pstmt.setString(1, author);
5
6// DON'T: Concatenate user input
7String sql = "SELECT * FROM books WHERE author = '" + author + "'"; // Vulnerable!1// DO: Use batch operations
2String sql = "INSERT INTO books (title, author, price) VALUES (?, ?, ?)";
3try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
4 for (Book book : books) {
5 pstmt.setString(1, book.getTitle());
6 pstmt.setString(2, book.getAuthor());
7 pstmt.setBigDecimal(3, book.getPrice());
8 pstmt.addBatch();
9 }
10 pstmt.executeBatch();
11}
12
13// DO: Set fetch size for large result sets
14Statement stmt = conn.createStatement();
15stmt.setFetchSize(100);
16ResultSet rs = stmt.executeQuery("SELECT * FROM large_table");1// DO: Use transactions properly
2try {
3 conn.setAutoCommit(false);
4
5 // Perform multiple operations...
6
7 conn.commit();
8} catch (SQLException e) {
9 if (conn != null) {
10 try {
11 conn.rollback();
12 } catch (SQLException ex) {
13 // Log exception
14 }
15 }
16 // Handle or rethrow exception
17} finally {
18 if (conn != null) {
19 try {
20 conn.setAutoCommit(true);
21 conn.close();
22 } catch (SQLException e) {
23 // Log exception
24 }
25 }
26}1try {
2 // Database operations
3} catch (SQLTransientConnectionException e) {
4 // Retry logic for transient connection issues
5} catch (SQLIntegrityConstraintViolationException e) {
6 // Handle constraint violations
7} catch (SQLException e) {
8 // Log detailed error information
9 logger.error("Database error: " + e.getSQLState() + " - " + e.getMessage());
10 // Return user-friendly error
11 throw new ServiceException("A database error occurred");
12} finally {
13 // Clean up resources
14}Let's compare JDBC with other popular Java database access technologies:
JPA/Hibernate:
1// Hibernate/JPA example
2@Entity
3@Table(name = "books")
4public class Book {
5 @Id
6 @GeneratedValue(strategy = GenerationType.IDENTITY)
7 private Long id;
8
9 @Column(nullable = false)
10 private String title;
11
12 @Column(nullable = false)
13 private String author;
14
15 @Column(nullable = false, precision = 10, scale = 2)
16 private BigDecimal price;
17
18 @Column(nullable = false)
19 private int stock;
20
21 // Getters and setters
22}
23
24// Using EntityManager (JPA)
25@PersistenceContext
26private EntityManager entityManager;
27
28public List<Book> findAll() {
29 return entityManager.createQuery("SELECT b FROM Book b", Book.class).getResultList();
30}
31
32public Book save(Book book) {
33 entityManager.persist(book);
34 return book;
35}When to choose which:
Spring JDBC:
1// Spring JDBC example
2@Autowired
3private JdbcTemplate jdbcTemplate;
4
5public List<Book> findAll() {
6 return jdbcTemplate.query(
7 "SELECT id, title, author, price, stock FROM books",
8 (rs, rowNum) -> new Book(
9 rs.getLong("id"),
10 rs.getString("title"),
11 rs.getString("author"),
12 rs.getBigDecimal("price"),
13 rs.getInt("stock")
14 )
15 );
16}
17
18public Book findById(Long id) {
19 try {
20 return jdbcTemplate.queryForObject(
21 "SELECT id, title, author, price, stock FROM books WHERE id = ?",
22 new Object[]{id},
23 (rs, rowNum) -> new Book(
24 rs.getLong("id"),
25 rs.getString("title"),
26 rs.getString("author"),
27 rs.getBigDecimal("price"),
28 rs.getInt("stock")
29 )
30 );
31 } catch (EmptyResultDataAccessException e) {
32 return null;
33 }
34}When to choose which:
jOOQ:
1// jOOQ example
2import static org.jooq.impl.DSL.*;
3import org.jooq.DSLContext;
4
5@Autowired
6private DSLContext create;
7
8public List<Book> findAll() {
9 return create.select(BOOKS.ID, BOOKS.TITLE, BOOKS.AUTHOR, BOOKS.PRICE, BOOKS.STOCK)
10 .from(BOOKS)
11 .orderBy(BOOKS.ID)
12 .fetch()
13 .into(Book.class);
14}
15
16public Book findById(Long id) {
17 return create.select(BOOKS.ID, BOOKS.TITLE, BOOKS.AUTHOR, BOOKS.PRICE, BOOKS.STOCK)
18 .from(BOOKS)
19 .where(BOOKS.ID.eq(id))
20 .fetchOneInto(Book.class);
21}
22
23public List<Book> findExpensiveBooksByAuthor(String author, BigDecimal minPrice) {
24 return create.select(BOOKS.ID, BOOKS.TITLE, BOOKS.AUTHOR, BOOKS.PRICE, BOOKS.STOCK)
25 .from(BOOKS)
26 .where(BOOKS.AUTHOR.like("%" + author + "%"))
27 .and(BOOKS.PRICE.greaterThan(minPrice))
28 .orderBy(BOOKS.PRICE.desc())
29 .fetch()
30 .into(Book.class);
31}When to choose which:
MyBatis:
1// MyBatis example
2@Mapper
3public interface BookMapper {
4 @Select("SELECT id, title, author, price, stock FROM books")
5 List<Book> findAll();
6
7 @Select("SELECT id, title, author, price, stock FROM books WHERE id = #{id}")
8 Book findById(Long id);
9
10 @Insert("INSERT INTO books (title, author, price, stock) VALUES (#{title}, #{author}, #{price}, #{stock})")
11 @Options(useGeneratedKeys = true, keyProperty = "id")
12 int insert(Book book);
13
14 @Update("UPDATE books SET title = #{title}, author = #{author}, price = #{price}, stock = #{stock} WHERE id = #{id}")
15 int update(Book book);
16
17 @Delete("DELETE FROM books WHERE id = #{id}")
18 int delete(Long id);
19}
20
21// Using the mapper
22@Autowired
23private BookMapper bookMapper;
24
25public List<Book> getAllBooks() {
26 return bookMapper.findAll();
27}When to choose which:
Java and PostgreSQL make a powerful combination for building enterprise applications. JDBC provides a solid foundation for database connectivity, while higher-level frameworks offer various abstractions to simplify development.
Key takeaways:
By understanding these concepts and choosing the right technology for your needs, you'll be well-equipped to build robust Java applications with PostgreSQL integration.
In the next lesson, we'll explore ORMs with Java, focusing on JPA and Hibernate for PostgreSQL integration.
In this lesson, we've covered a comprehensive overview of connecting Java applications to PostgreSQL databases using JDBC:
To continue your learning journey with Java and databases, consider exploring:
To reinforce what you've learned, try building these projects:
These projects will help you apply what you've learned in real-world scenarios and deepen your understanding of Java and PostgreSQL integration.
In the next lesson, we'll explore NoSQL databases with Java, focusing on MongoDB integration.
Your progress is saved in this browser, on this device only.