Java Libraries
Copy JAR files which are listed below:
mysql-connector-java-5.1.36.jar
Create Database
Create a database with the name is advancedjava. This database have 1 tables: Product table.
--
-- Table structure for table `product`
--
CREATE TABLE `product` (
`id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`name` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
`price` decimal(10,1) NOT NULL,
`quantity` int(11) NOT NULL,
`description` text COLLATE utf8_unicode_ci NOT NULL,
`featured` tinyint(1) NOT NULL,
`dateCreated` date NOT NULL,
`manufacturer` varchar(20)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Dumping data for table `product`
--
INSERT INTO `product` (`name`, `price`, `quantity`, `description`, `featured`, `dateCreated`, `manufacturer`) VALUES
('laptop 1', '2.0', 5, 'Description of laptop 1', 1, '2018-03-19', 'Manufacturer 1'),
('laptop 2', '12.0', 7, 'Description of laptop 2', 0, '2018-03-21', 'Manufacturer 1'),
('computer 1', '4.0', 6, 'Description of computer 1', 1, '2018-03-23', 'Manufacturer 2'),
('computer 2', '10.0', 6, 'Description of computer 2', 1, '2018-03-14', 'Manufacturer 2'),
('computer 3', '21.0', 4, 'Description of computer 3', 0, '2018-03-26', 'Manufacturer 2'),
('tivi 1', '17.0', 2, 'Description of tivi 1', 1, '2018-03-23', 'Manufacturer 3'),
('tivi 2', '32.0', 8, 'Description of tivi 2', 0, '2018-03-29', 'Manufacturer 3');
Structure of Product Table
Data of Product Table
Entities Class
Create new package named entities. In this package, create new java class named Group.java as below:
Group Entity
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;
}
}
Create ConnectDatabase Class
Create new package named models. In this package, create new java class named ConnectDatabase.java as below:
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.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/advancedjava", "root", "123456");
} catch (Exception e) {
connection = null;
}
return connection;
}
}
Create ProductModel Class
In models package, create new java class named ProductModel.java contains methods to interact with the database.
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;
}
}
Structure of Project
Run Application
Create new package named demo. In this package, create new java class named Main.java as below:
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("==============================");
}
}
}
Output
Manufacturer 1
Min Price: 2.0
Max Price: 12.0
Avg Price: 7.0
Count Product: 2
Sum Quantities: 12
==============================
Manufacturer 2
Min Price: 4.0
Max Price: 21.0
Avg Price: 11.66667
Count Product: 3
Sum Quantities: 16
==============================
Manufacturer 3
Min Price: 17.0
Max Price: 32.0
Avg Price: 24.5
Count Product: 2
Sum Quantities: 10
==============================