Program 1: Establishing a Connection with PostgreSQL
This program establishes a connection between a Java application and PostgreSQL using JDBC.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class PostgreSQLConnectionExample {
public static void main(String[] args) {
String url =
"jdbc:postgresql://localhost:5432/jdbc_demo";
String username = "postgres";
String password = "password";
try {
Connection con =
DriverManager.getConnection(
url, username, password);
System.out.println(
"Connected to PostgreSQL successfully.");
con.close();
} catch (SQLException e) {
System.out.println(
"Connection failed: " +
e.getMessage());
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\postgresql.jar" PostgreSQLConnectionExample.java
PS D:\test> java -classpath ".;D:\test\postgresql.jar" PostgreSQLConnectionExample
Connected to PostgreSQL successfully.Code language: CSS (css)Program 2: Creating an Employee Table
This program creates an employee table in PostgreSQL using the JDBC Statement interface.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class CreateEmployeeTable {
public static void main(String[] args) {
String url =
"jdbc:postgresql://localhost:5432/jdbc_demo";
String username = "postgres";
String password = "password";
String sql =
"CREATE TABLE IF NOT EXISTS employee (" +
"employee_id INT PRIMARY KEY, " +
"employee_name VARCHAR(100), " +
"department VARCHAR(50), " +
"salary NUMERIC(10,2))";
try {
Connection con =
DriverManager.getConnection(
url, username, password);
Statement stmt =
con.createStatement();
stmt.executeUpdate(sql);
System.out.println(
"Employee table created successfully.");
stmt.close();
con.close();
} catch (SQLException e) {
System.out.println(
"Error: " + e.getMessage());
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\postgresql.jar" CreateEmployeeTable.java
PS D:\test> java -classpath ".;D:\test\postgresql.jar" CreateEmployeeTable
Employee table created successfully.Code language: CSS (css)Program 3: Inserting Records Using PreparedStatement
This program inserts an employee record into PostgreSQL using a parameterized SQL statement with PreparedStatement.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class PostgreSQLPreparedStatementExample {
public static void main(String[] args) {
String url =
"jdbc:postgresql://localhost:5432/jdbc_demo";
String username = "postgres";
String password = "password";
String sql =
"INSERT INTO employee " +
"(employee_id, employee_name, department, salary) " +
"VALUES (?, ?, ?, ?)";
try {
Connection con =
DriverManager.getConnection(
url, username, password);
PreparedStatement ps =
con.prepareStatement(sql);
ps.setInt(1, 101);
ps.setString(2, "Ravi");
ps.setString(3, "IT");
ps.setDouble(4, 50000);
ps.executeUpdate();
System.out.println(
"Employee inserted successfully.");
ps.close();
con.close();
} catch (SQLException e) {
System.out.println(
"Error: " + e.getMessage());
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\postgresql.jar" PostgreSQLPreparedStatementExample.java
PS D:\test> java -classpath ".;D:\test\postgresql.jar" PostgreSQLPreparedStatementExample
Employee inserted successfully.Code language: CSS (css)Program 4: Retrieving Records Using ResultSet
This program retrieves employee records from PostgreSQL using Statement and ResultSet.
import java.sql.*;
public class PostgreSQLResultSetExample {
public static void main(String[] args) {
String url =
"jdbc:postgresql://localhost:5432/jdbc_demo";
try {
Connection con =
DriverManager.getConnection(
url, "postgres", "password");
Statement stmt =
con.createStatement();
ResultSet rs =
stmt.executeQuery(
"SELECT * FROM employee");
System.out.println(
"Employee Details");
System.out.println(
"-------------------------");
while (rs.next()) {
System.out.println(
rs.getInt("employee_id") + " " +
rs.getString("employee_name") + " " +
rs.getString("department") + " " +
rs.getDouble("salary"));
}
rs.close();
stmt.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\postgresql.jar" PostgreSQLResultSetExample.java
PS D:\test> java -classpath ".;D:\test\postgresql.jar" PostgreSQLResultSetExample
Employee Details
-------------------------
101 Ravi IT 50000.0Code language: CSS (css)Program 5: Updating Records Using PreparedStatement
This program updates the salary of an employee in PostgreSQL using PreparedStatement.
import java.sql.*;
public class PostgreSQLUpdateExample {
public static void main(String[] args) {
String url =
"jdbc:postgresql://localhost:5432/jdbc_demo";
String sql =
"UPDATE employee " +
"SET salary = ? " +
"WHERE employee_id = ?";
try {
Connection con =
DriverManager.getConnection(
url, "postgres", "password");
PreparedStatement ps =
con.prepareStatement(sql);
ps.setDouble(1, 60000);
ps.setInt(2, 101);
int rows =
ps.executeUpdate();
System.out.println(
rows + " record updated.");
ps.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\postgresql.jar" PostgreSQLUpdateExample.java
PS D:\test> java -classpath ".;D:\test\postgresql.jar" PostgreSQLUpdateExample
1 record updated.Code language: CSS (css)Program 6: Deleting Records Using PreparedSystem
This program deletes an employee record from PostgreSQL using a parameterized SQL statement.
import java.sql.*;
public class PostgreSQLDeleteExample {
public static void main(String[] args) {
String url =
"jdbc:postgresql://localhost:5432/jdbc_demo";
String sql =
"DELETE FROM employee " +
"WHERE employee_id = ?";
try {
Connection con =
DriverManager.getConnection(
url, "postgres", "password");
PreparedStatement ps =
con.prepareStatement(sql);
ps.setInt(1, 101);
int rows =
ps.executeUpdate();
System.out.println(
rows + " record deleted.");
ps.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\postgresql.jar" PostgreSQLDeleteExample.java
PS D:\test> java -classpath ".;D:\test\postgresql.jar" PostgreSQLDeleteExample
1 record deleted.Code language: CSS (css)Program 7: Performing Batch Insert Using PreparedSystem
This program inserts multiple employee records into PostgreSQL using JDBC batch processing.
import java.sql.*;
public class PostgreSQLBatchExample {
public static void main(String[] args) {
String url =
"jdbc:postgresql://localhost:5432/jdbc_demo";
String sql =
"INSERT INTO employee " +
"(employee_id, employee_name, department, salary) " +
"VALUES (?, ?, ?, ?)";
try {
Connection con =
DriverManager.getConnection(
url, "postgres", "password");
PreparedStatement ps =
con.prepareStatement(sql);
ps.setInt(1, 201);
ps.setString(2, "Arun");
ps.setString(3, "IT");
ps.setDouble(4, 45000);
ps.addBatch();
ps.setInt(1, 202);
ps.setString(2, "Priya");
ps.setString(3, "HR");
ps.setDouble(4, 42000);
ps.addBatch();
ps.setInt(1, 203);
ps.setString(2, "Kiran");
ps.setString(3, "Finance");
ps.setDouble(4, 48000);
ps.addBatch();
int[] result =
ps.executeBatch();
System.out.println(
result.length +
" records processed in batch.");
ps.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\postgresql.jar" PostgreSQLBatchExample.java
PS D:\test> java -classpath ".;D:\test\postgresql.jar" PostgreSQLBatchExample
3 records processed in batch.Code language: CSS (css)Program 8: Using Transactions with commit() and rollback()
This program performs multiple salary updates inside a PostgreSQL transaction. If the operations are successful, commit() saves the changes. If an error occurs, rollback() can undo the transaction.
import java.sql.*;
public class PostgreSQLTransactionExample {
public static void main(String[] args) {
String url =
"jdbc:postgresql://localhost:5432/jdbc_demo";
Connection con = null;
try {
con = DriverManager.getConnection(
url, "postgres", "password");
con.setAutoCommit(false);
PreparedStatement ps =
con.prepareStatement(
"UPDATE employee " +
"SET salary = salary + ? " +
"WHERE employee_id = ?");
ps.setDouble(1, 5000);
ps.setInt(2, 201);
ps.executeUpdate();
ps.setDouble(1, 3000);
ps.setInt(2, 202);
ps.executeUpdate();
con.commit();
System.out.println(
"Transaction committed successfully.");
ps.close();
con.close();
} catch (SQLException e) {
System.out.println(
"Transaction failed.");
if (con != null) {
try {
con.rollback();
System.out.println(
"Transaction rolled back.");
} catch (SQLException ex) {
ex.printStackTrace();
}
}
e.printStackTrace();
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\postgresql.jar" PostgreSQLTransactionExample.java
PS D:\test> java -classpath ".;D:\test\postgresql.jar" PostgreSQLTransactionExample
Transaction committed successfully.Code language: CSS (css)Program 9: Using Savepoint in PostgreSQL Transaction
This program creates a savepoint after the first update and then rolls back the second update to that savepoint. The first update remains part of the transaction.
import java.sql.*;
public class PostgreSQLSavepointExample {
public static void main(String[] args) {
String url =
"jdbc:postgresql://localhost:5432/jdbc_demo";
try {
Connection con =
DriverManager.getConnection(
url, "postgres", "password");
con.setAutoCommit(false);
Statement stmt =
con.createStatement();
// First update
stmt.executeUpdate(
"UPDATE employee " +
"SET salary = salary + 2000 " +
"WHERE employee_id = 201");
System.out.println(
"First update completed.");
// Create savepoint
Savepoint savepoint =
con.setSavepoint("AfterFirstUpdate");
System.out.println(
"Savepoint created.");
// Second update
stmt.executeUpdate(
"UPDATE employee " +
"SET salary = salary + 5000 " +
"WHERE employee_id = 202");
System.out.println(
"Second update completed.");
// Roll back to savepoint
con.rollback(savepoint);
System.out.println(
"Rolled back to savepoint.");
// Commit remaining changes
con.commit();
System.out.println(
"Transaction committed.");
stmt.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\postgresql.jar" PostgreSQLSavepointExample.java
PS D:\test> java -classpath ".;D:\test\postgresql.jar" PostgreSQLSavepointExample
First update completed.
Savepoint created.
Second update completed.
Rolled back to savepoint.
Transaction committed.Code language: CSS (css)Program 10: Calling a PostgreSQL Function Using CallableStatement
There is an important difference here compared with MySQL: PostgreSQL commonly uses functions for routines that return values, and JDBC can call such a function using CallableStatement.
Create the PostgreSQL Function
Run this in PostgreSQL:
CREATE OR REPLACE FUNCTION get_employee_name(
p_employee_id INTEGER
)
RETURNS VARCHAR
LANGUAGE plpgsql
AS $$
DECLARE
employee_name_result VARCHAR;
BEGIN
SELECT employee_name
INTO employee_name_result
FROM employee
WHERE employee_id = p_employee_id;
RETURN employee_name_result;
END;
$$;Code language: JavaScript (javascript)import java.sql.*;
public class PostgreSQLCallableStatementExample {
public static void main(String[] args) {
String url =
"jdbc:postgresql://localhost:5432/jdbc_demo";
String username = "postgres";
String password = "password";
try {
Connection con =
DriverManager.getConnection(
url, username, password);
CallableStatement cs =
con.prepareCall(
"{ ? = call get_employee_name(?) }");
// Register function return value
cs.registerOutParameter(
1, Types.VARCHAR);
// Set function input parameter
cs.setInt(2, 201);
// Execute function
cs.execute();
// Get returned value
String employeeName =
cs.getString(1);
System.out.println(
"Employee ID: 201");
System.out.println(
"Employee Name: " +
employeeName);
cs.close();
con.close();
} catch (SQLException e) {
System.out.println(
"Database Error: " +
e.getMessage());
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\postgresql.jar" PostgreSQLCallableStatementExample.java
PS D:\test> java -classpath ".;D:\test\postgresql.jar" PostgreSQLCallableStatementExample
Employee ID: 201
Employee Name: ArunCode language: CSS (css)