Learn how to connect to PostgreSQL databases in Sealos Devbox using Java
This guide will walk you through the process of connecting to a PostgreSQL database using Java within your Sealos Devbox project, including basic CRUD (Create, Read, Update, Delete) operations.
To connect to the PostgreSQL server from a Java program, you need a PostgreSQL JDBC driver.
You can download the latest version of the driver on the jdbc.postgresql.org download page. The downloaded file is a jar file e.g., postgresql-42.7.1.jar.
Create a new file named DatabaseConfig.java with the following content:
DatabaseConfig.java
import java.io.IOException;import java.io.InputStream;import java.util.Properties;public class DatabaseConfig { private static final Properties properties = new Properties(); static { try (InputStream input = DatabaseConfig.class.getClassLoader().getResourceAsStream("db.properties")) { if (input == null) { System.out.println("Sorry, unable to find db.properties"); System.exit(1); } properties.load(input); } catch (IOException e) { e.printStackTrace(); } } public static String getDbUrl() { return properties.getProperty("db.url"); } public static String getDbUsername() { return properties.getProperty("db.username"); } public static String getDbPassword() { return properties.getProperty("db.password"); }}
The DatabaseConfig class is responsible for loading database configuration from the db.properties file. It has three static methods that expose the database configuration:
getDbUrl() – Returns the database URL.
getDbUsername() – Returns the username.
getDbPassword() – Returns the password.
This class ensures that sensitive database credentials are not hardcoded in the application.
Create a new file named Employee.java with the following content:
Employee.java
public class Employee { private int id; private String name; private String position; public Employee(int id, String name, String position) { this.id = id; this.name = name; this.position = position; } // Getters and setters public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getPosition() { return position; } public void setPosition(String position) { this.position = position; } @Override public String toString() { return "Employee{" + "id=" + id + ", name='" + name + '\'' + ", position='" + position + '\'' + '}'; }}
The Employee class represents the data model for an employee. It includes fields for id, name, and position, along with a constructor, getters, setters, and a toString method for easy printing of employee information.
Create a new file named DB.java with the following content:
DB.java
import java.sql.*;import java.util.ArrayList;import java.util.List;public class DB { public static Connection getConnection() throws SQLException { String jdbcUrl = DatabaseConfig.getDbUrl(); String user = DatabaseConfig.getDbUsername(); String password = DatabaseConfig.getDbPassword(); return DriverManager.getConnection(jdbcUrl, user, password); } public static void createTable() throws SQLException { String sql = "CREATE TABLE IF NOT EXISTS employees (" + "id SERIAL PRIMARY KEY," + "name VARCHAR(100) NOT NULL," + "position VARCHAR(100) NOT NULL)"; try (Connection conn = getConnection(); Statement stmt = conn.createStatement()) { stmt.execute(sql); } } public static void insertEmployee(String name, String position) throws SQLException { String sql = "INSERT INTO employees (name, position) VALUES (?, ?)"; try (Connection conn = getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql)) { pstmt.setString(1, name); pstmt.setString(2, position); pstmt.executeUpdate(); } } public static List<Employee> getEmployees() throws SQLException { List<Employee> employees = new ArrayList<>(); String sql = "SELECT id, name, position FROM employees"; try (Connection conn = getConnection(); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql)) { while (rs.next()) { employees.add(new Employee( rs.getInt("id"), rs.getString("name"), rs.getString("position") )); } } return employees; } public static void updateEmployee(int id, String name, String position) throws SQLException { String sql = "UPDATE employees SET name = ?, position = ? WHERE id = ?"; try (Connection conn = getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql)) { pstmt.setString(1, name); pstmt.setString(2, position); pstmt.setInt(3, id); pstmt.executeUpdate(); } } public static void deleteEmployee(int id) throws SQLException { String sql = "DELETE FROM employees WHERE id = ?"; try (Connection conn = getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql)) { pstmt.setInt(1, id); pstmt.executeUpdate(); } }}
The DB class is responsible for database operations:
The getConnection() method connects to the PostgreSQL database using the connection parameters from DatabaseConfig.
It returns a Connection object if successful, or throws a SQLException if there's an error.
Other methods (createTable, insertEmployee, etc.) use this connection to perform CRUD operations.
Each method opens a new connection, performs its operation, and then closes the connection using try-with-resources, ensuring proper resource management.
Create a new file named Main.java with the following content:
Main.java
import java.sql.SQLException;import java.util.List;public class Main { public static void main(String[] args) { try { System.out.println("Connecting to the PostgreSQL database..."); // Create the employees table DB.createTable(); System.out.println("Employees table created (if not exists)."); // Insert sample employees DB.insertEmployee("John Doe", "Developer"); DB.insertEmployee("Jane Smith", "Designer"); System.out.println("Sample employees inserted."); // Retrieve and display all employees List<Employee> employees = DB.getEmployees(); System.out.println("Employees:"); for (Employee emp : employees) { System.out.println(emp); } // Update an employee DB.updateEmployee(1, "John Doe", "Senior Developer"); System.out.println("Employee updated."); // Delete an employee DB.deleteEmployee(2); System.out.println("Employee deleted."); // Display updated employee list employees = DB.getEmployees(); System.out.println("\nUpdated Employees:"); for (Employee emp : employees) { System.out.println(emp); } } catch (SQLException e) { System.err.println("Database operation error: " + e.getMessage()); } }}
The Main class demonstrates the usage of the DB class to perform various database operations:
It creates a table, inserts sample data, retrieves and displays employees, updates an employee, deletes an employee, and displays the updated list.
Each operation is wrapped in a try-catch block to handle potential SQLExceptions.
The program uses the methods from the DB class, which manage their own connections, ensuring that connections are properly opened and closed for each operation.