Implementing MySQL aggregation queries using Java
To implement MySQL aggregation queries in Java, you can use JDBC to connect to a database and execute SQL statements. The following is an example of using Java to implement various data aggregation queries:
1. Introducing Maven dependencies:
<!-- MySQL JDBC driver -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.23</version>
</dependency>
2. Write Java code:
import java.sql.*;
public class AggregationQuery {
public static void main(String[] args) {
//Database Connection Information
String url = "jdbc:mysql://localhost:3306/db_name";
String username = "username";
String password = "password";
try {
//Establishing a database connection
Connection connection = DriverManager.getConnection(url, username, password);
//Create a Statement object
Statement statement = connection.createStatement();
//Execute Aggregate Query
//Count Rows
String countQuery = "SELECT COUNT(*) FROM table_name";
ResultSet countResult = statement.executeQuery(countQuery);
if (countResult.next()) {
int count = countResult.getInt(1);
System.out.println("Total count: " + count);
}
//Summation
String sumQuery = "SELECT SUM(column_name) FROM table_name";
ResultSet sumResult = statement.executeQuery(sumQuery);
if (sumResult.next()) {
double sum = sumResult.getDouble(1);
System.out.println("Sum: " + sum);
}
//Average
String avgQuery = "SELECT AVG(column_name) FROM table_name";
ResultSet avgResult = statement.executeQuery(avgQuery);
if (avgResult.next()) {
double average = avgResult.getDouble(1);
System.out.println("Average: " + average);
}
//Maximum value
String maxQuery = "SELECT MAX(column_name) FROM table_name";
ResultSet maxResult = statement.executeQuery(maxQuery);
if (maxResult.next()) {
double max = maxResult.getDouble(1);
System.out.println("Max: " + max);
}
//Minimum value
String minQuery = "SELECT MIN(column_name) FROM table_name";
ResultSet minResult = statement.executeQuery(minQuery);
if (minResult.next()) {
double min = minResult.getDouble(1);
System.out.println("Min: " + min);
}
//Close Connection
statement.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Please note the replacement of 'db' in the code_ Name, username, password, table_ Name and column_ The name 'refers to the actual database, username, password, table name, and column name.
The above code will execute various aggregation queries for MySQL and print the results. You can modify the query statements and the way results are processed according to specific needs.
