Use Group By in Java JDBC

This project will demonstrate how to Use Group By in Java JDBC

  • LearnJavaJDBC
    • src
      • demo
        • Main.java
      • entities
        • Group.java
      • models
        • ProductModel.java
        • ConnectDatabase.java
    • pom.xml
package entities;

import java.math.BigDecimal;

public class Group implements java.io.Serializable {

    private String manufacturer;
    private BigDecimal minPrice;
    private BigDecimal maxPrice;
    private int sumQuantities;
    private int countProduct;
    private double avgPrice;

    public String getManufacturer() {
        return manufacturer;
    }

    public void setManufacturer(String manufacturer) {
        this.manufacturer = manufacturer;
    }

    public BigDecimal getMinPrice() {
        return minPrice;
    }

    public void setMinPrice(BigDecimal minPrice) {
        this.minPrice = minPrice;
    }

    public BigDecimal getMaxPrice() {
        return maxPrice;
    }

    public void setMaxPrice(BigDecimal maxPrice) {
        this.maxPrice = maxPrice;
    }

    public int getSumQuantities() {
        return sumQuantities;
    }

    public void setSumQuantities(int sumQuantities) {
        this.sumQuantities = sumQuantities;
    }

    public int getCountProduct() {
        return countProduct;
    }

    public void setCountProduct(int countProduct) {
        this.countProduct = countProduct;
    }

    public double getAvgPrice() {
        return avgPrice;
    }

    public void setAvgPrice(double avgPrice) {
        this.avgPrice = avgPrice;
    }

}
package models;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import entities.Group;

public class ProductModel {

    public List<Group> groupBy() {
        List<Group> groups = new ArrayList<Group>();
        try {
            PreparedStatement preparedStatement = ConnectDatabase.getConnection().prepareStatement("select manufacturer, min(price) as minPrice, max(price) as maxPrice, sum(quantity) as sumQuantities, count(id) as countProduct, avg(price) as avgPrice from product group by manufacturer");
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Group group = new Group();
                group.setManufacturer(resultSet.getString("manufacturer"));
                group.setMinPrice(resultSet.getBigDecimal("minPrice"));
                group.setMaxPrice(resultSet.getBigDecimal("maxPrice"));
                group.setSumQuantities(resultSet.getInt("sumQuantities"));
                group.setCountProduct(resultSet.getInt("countProduct"));
                group.setAvgPrice(resultSet.getDouble("avgPrice"));
                groups.add(group);
            }
        } catch (Exception e) {
            groups = null;
        }
        return groups;
    }

}
package models;

import java.sql.Connection;
import java.sql.DriverManager;

public class ConnectDatabase {

    public static Connection getConnection() {
        Connection connection = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/learn_java_jdbc", "root", "123456");
        } catch (Exception e) {
            connection = null;
        }
        return connection;
    }

}
package demo;

import entities.Group;
import models.ProductModel;

public class Main {

    public static void main(String[] args) {

        ProductModel productModel = new ProductModel();
        for(Group group : productModel.groupBy()) {
            System.out.println(group.getManufacturer());
            System.out.println("Min Price: " + group.getMinPrice());
            System.out.println("Max Price: " + group.getMaxPrice());
            System.out.println("Avg Price: " + group.getAvgPrice());
            System.out.println("Count Product: " + group.getCountProduct());
            System.out.println("Sum Quantities: " + group.getSumQuantities());
            System.out.println("==============================");
        }

    }

}
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>LearnJavaJDBC</groupId>
    <artifactId>LearnJavaJDBC</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <description>Learn Java JDBC with Real Apps</description>
    <dependencies>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.19</version>
        </dependency>

    </dependencies>
</project>

Screenshots