Implementing MariaDB aggregation queries using Java
To implement various aggregation queries in MariaDB using Java, it is necessary to connect to the MariaDB database using JDBC and execute SQL statements. Here is a simple step-by-step guide:
1. Introduce Dependency: Add MariaDB's JDBC dependency to the pom.xml file of the project.
<dependency>
<groupId>org.mariadb.jdbc</groupId>
<artifactId>mariadb-java-client</artifactId>
<version>2.7.3</version>
</dependency>
2. Create a database connection: Use JDBC to connect to the database.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class Main {
public static void main(String[] args) {
try {
Connection conn = DriverManager.getConnection("jdbc:mariadb://localhost:3306/mydb", "username", "password");
//Execute Aggregate Query
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
3. Execute aggregation queries: After successful connection, various aggregation queries can be executed.
-Total number of query records:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Main {
public static void main(String[] args) {
try {
Connection conn = DriverManager.getConnection("jdbc:mariadb://localhost:3306/mydb", "username", "password");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM mytable");
if (rs.next()) {
int count = rs.getInt(1);
System. out. println ("Total number of records:"+count);
}
rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
-Calculate the average value:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Main {
public static void main(String[] args) {
try {
Connection conn = DriverManager.getConnection("jdbc:mariadb://localhost:3306/mydb", "username", "password");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT AVG(column_name) FROM mytable");
if (rs.next()) {
double average = rs.getDouble(1);
System. out. println ("average:"+average);
}
rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
-Sum operation:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Main {
public static void main(String[] args) {
try {
Connection conn = DriverManager.getConnection("jdbc:mariadb://localhost:3306/mydb", "username", "password");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT SUM(column_name) FROM mytable");
if (rs.next()) {
double sum = rs.getDouble(1);
System. out. println ("total:"+sum);
}
rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
In this way, you can use Java to implement various aggregation queries for MariaDB. Be sure to replace the database connection information in the code and the table and column names in the SQL statement to suit your specific scenario.
