Implementing PostgreSQL aggregate queries using Java
To implement various aggregation queries in PostgreSQL using Java, it is first necessary to introduce appropriate dependencies in the project. The following are the dependency coordinates for using PostgreSQL JDBC driver in the Maven project:
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>VERSION</version>
</dependency>
Please replace 'VERSION' with the version of PostgreSQL you want to use.
Here are some common examples of data aggregation queries and their Java code implementation:
##1 Calculate the total number
To calculate the number of rows in a table, you can use the 'COUNT' aggregation function.
import java.sql.*;
public class CountExample {
public static void main(String[] args) {
String url = "jdbc:postgresql://localhost:5432/mydatabase";
String user = "username";
String password = "password";
try (Connection conn = DriverManager.getConnection(url, user, password);
Statement stmt = conn.createStatement()) {
String sql = "SELECT COUNT(*) FROM mytable";
ResultSet rs = stmt.executeQuery(sql);
rs.next();
int count = rs.getInt(1);
System. out. println ("total:"+count);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
##2 Calculate Average
To calculate the average value of a column, you can use the 'AVG' aggregation function.
import java.sql.*;
public class AvgExample {
public static void main(String[] args) {
String url = "jdbc:postgresql://localhost:5432/mydatabase";
String user = "username";
String password = "password";
try (Connection conn = DriverManager.getConnection(url, user, password);
Statement stmt = conn.createStatement()) {
String sql = "SELECT AVG(salary) FROM employees";
ResultSet rs = stmt.executeQuery(sql);
rs.next();
double average = rs.getDouble(1);
System. out. println ("average salary:"+average);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
##3 Calculate total
To calculate the sum of a column, you can use the 'SUM' aggregation function.
import java.sql.*;
public class SumExample {
public static void main(String[] args) {
String url = "jdbc:postgresql://localhost:5432/mydatabase";
String user = "username";
String password = "password";
try (Connection conn = DriverManager.getConnection(url, user, password);
Statement stmt = conn.createStatement()) {
String sql = "SELECT SUM(sales) FROM orders";
ResultSet rs = stmt.executeQuery(sql);
rs.next();
double totalSales = rs.getDouble(1);
System. out. println ("Total Sales:"+totalSales);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
##4 Find maximum value
To request the maximum value of a column, the 'MAX' aggregation function can be used.
import java.sql.*;
public class MaxExample {
public static void main(String[] args) {
String url = "jdbc:postgresql://localhost:5432/mydatabase";
String user = "username";
String password = "password";
try (Connection conn = DriverManager.getConnection(url, user, password);
Statement stmt = conn.createStatement()) {
String sql = "SELECT MAX(price) FROM products";
ResultSet rs = stmt.executeQuery(sql);
rs.next();
double maxPrice = rs.getDouble(1);
System. out. println ("Maximum Price:"+maxPrice);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
##5 Find the minimum value
To require the minimum value of a certain column, the 'MIN' aggregation function can be used.
import java.sql.*;
public class MinExample {
public static void main(String[] args) {
String url = "jdbc:postgresql://localhost:5432/mydatabase";
String user = "username";
String password = "password";
try (Connection conn = DriverManager.getConnection(url, user, password);
Statement stmt = conn.createStatement()) {
String sql = "SELECT MIN(quantity) FROM inventory";
ResultSet rs = stmt.executeQuery(sql);
rs.next();
int minQuantity = rs.getInt(1);
System. out. println ("minimum quantity:"+minQuantity);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
##6 Group statistics
To group a column and perform statistics, you can use the 'GROUP BY' clause and the corresponding aggregation function.
import java.sql.*;
public class GroupByExample {
public static void main(String[] args) {
String url = "jdbc:postgresql://localhost:5432/mydatabase";
String user = "username";
String password = "password";
try (Connection conn = DriverManager.getConnection(url, user, password);
Statement stmt = conn.createStatement()) {
String sql = "SELECT category, AVG(price) FROM products GROUP BY category";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
String category = rs.getString(1);
double averagePrice = rs.getDouble(2);
System.out.println(category + ": " + averagePrice);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
These examples can serve as references and be modified according to your actual needs. Ensure that the correct database connection parameters are provided before running the code.
