使用Java实现PostgreSQL聚合查询
要使用Java实现PostgreSQL各种聚合查询, 首先需要在项目中引入适当的依赖。以下是在Maven项目中使用PostgreSQL JDBC驱动的依赖坐标：
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>VERSION</version>
</dependency>
请将`VERSION`替换为您要使用的PostgreSQL版本。
下面是一些常见的数据聚合查询示例及其Java代码实现：
## 1. 计算总数
要计算某个表中的行数，可以使用`COUNT`聚合函数。
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("总数: " + count);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
## 2. 计算平均值
要计算某个列的平均值，可以使用`AVG`聚合函数。
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);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
## 3. 计算总和
要计算某个列的总和，可以使用`SUM`聚合函数。
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("总销售额: " + totalSales);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
## 4. 求最大值
要求某个列的最大值，可以使用`MAX`聚合函数。
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("最高价格: " + maxPrice);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
## 5. 求最小值
要求某个列的最小值，可以使用`MIN`聚合函数。
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("最小数量: " + minQuantity);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
## 6. 分组统计
要对某个列进行分组并进行统计，可以使用`GROUP BY`子句和相应的聚合函数。
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();
}
}
}
这些示例可以作为参考，根据您的实际需求进行相应的修改。确保在运行代码之前提供正确的数据库连接参数。
