Implementing IBM DB2 aggregated queries using Java
To implement various aggregate queries of IBM DB2 using Java, you need to use DB2's Java Database Connectivity (JDBC) driver. In the code, you can use SQL statements to execute aggregate queries and use the JDBC API to connect to the DB2 database and obtain results.
The following is a complete Java code example that demonstrates how to perform aggregate queries using Java and DB2 JDBC drivers:
import java.sql.*;
public class DB2AggregationQueryExample {
public static void main(String[] args) {
//DB2 database connection information
String url = "jdbc:db2://<hostname>:<port>/<database>";
String username = "<username>";
String password = "<password>";
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
//Loading the DB2 JDBC driver
Class.forName("com.ibm.db2.jcc.DB2Driver");
//Establishing a DB2 database connection
connection = DriverManager.getConnection(url, username, password);
//Create a Statement object to execute SQL queries
statement = connection.createStatement();
//Execute Aggregate Query
String sql = "SELECT COUNT(*) as totalStudents FROM students";
resultSet = statement.executeQuery(sql);
//Process query results
if (resultSet.next()) {
int totalStudents = resultSet.getInt("totalStudents");
System.out.println("Total number of students: " + totalStudents);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
//Release database resources
try {
if (resultSet != null) resultSet.close();
if (statement != null) statement.close();
if (connection != null) connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
In the above example, you need to replace the following information to make it applicable to your DB2 database:
-<hostname>: Your DB2 database host name
-<port>: DB2 database port number
-<database>: DB2 database name
-<username>: DB2 database username
-<password>: DB2 database password
You also need to add dependencies for the DB2 JDBC driver in the Maven configuration file. The following is a typical example of Maven dependencies:
<dependency>
<groupId>com.ibm.db2.jcc</groupId>
<artifactId>db2jcc</artifactId>
<version>11.5.0.0</version>
</dependency>
The above example demonstrates how to execute a COUNT (*) aggregate query and obtain results. You can modify the SQL query as needed to execute other aggregation functions (such as SUM, AVG, MIN, MAX, etc.) and other filtering criteria.
I hope this is helpful to you!
