Create Database
Create a database named mydb. This database have product table as below:
--
-- Table structure for table `product`
--
CREATE TABLE `product` (
`id` int(11) IDENTITY(1,1) NOT NULL PRIMARY KEY,
`name` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
`price` decimal(10,1) DEFAULT NULL,
`quantity` int(11) DEFAULT NULL,
`description` text COLLATE utf8_unicode_ci NOT NULL,
`status` tinyint(1) DEFAULT NULL,
`dateCreated` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Dumping data for table `product`
--
INSERT INTO `product` (`name`, `price`, `quantity`, `description`, `status`, `dateCreated`) VALUES
('Mobile 1', '2.0', 2, 'Description of Mobile 1', 0, '2017-09-12 00:00:00'),
('Mobile 2', '1.0', 5, 'Description of Mobile 2', 0, '2018-11-21 00:00:00'),
('Mobile 3', '3.4', 2, 'Description of Mobile 3', 0, '2018-09-24 00:00:00'),
('Laptop 1', '5.5', 17, 'Description of Laptop 1', 1, '2018-11-09 00:00:00'),
('Laptop 2', '7.0', 8, 'Description of Laptop 2', 0, '2017-10-26 00:00:00'),
('Laptop 4', '3.0', 11, 'Description of Laptop 4', 0, '2018-10-11 00:00:00'),
('Computer 1', '41.5', 6, 'Description of Computer 1', 1, '2017-11-27 00:00:00'),
('Computer 2', '7.0', 2, 'Description of Computer 2', 1, '2018-09-21 00:00:00');
Structure of Product Table
Data of Product Table
Database Configuration
Create new json file named appsettings.json in the classpath of your project
{
"connectionString": {
"url": "jdbc:mysql://localhost:3306/mydb",
"username": "root",
"password": "123456"
}
}
Create Entity
Create new package named entities. In this package, create new java class named Product.java as below:
package entitites;
import java.io.Serializable;
import java.math.BigDecimal;
import java.time.LocalDate;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;
@Table(name = "product")
public class Product implements Serializable {
@Id
@GeneratedValue
private int id;
private String name;
private BigDecimal price;
private int quantity;
private String description;
private boolean status;
private LocalDate dateCreated;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public BigDecimal getPrice() {
return price;
}
public void setPrice(BigDecimal price) {
this.price = price;
}
public int getQuantity() {
return quantity;
}
public void setQuantity(int quantity) {
this.quantity = quantity;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
public boolean isStatus() {
return status;
}
public void setStatus(boolean status) {
this.status = status;
}
public LocalDate getDateCreated() {
return dateCreated;
}
public void setDateCreated(LocalDate dateCreated) {
this.dateCreated = dateCreated;
}
}
Create Database Context
Create new package named models. In this package, create new java class named MyDemoContext.java as below:
package models;
import entitites.Product;
import jentityframework.DbSet;
public class MyDemoContext {
public DbSet Products;
public MyDemoContext() {
Products = new DbSet(Product.class);
}
}
Run Application
package demo;
import models.MyDBContext;
public class Demo {
public static void main(String[] args) {
MyDBContext db = new MyDBContext();
double sum1 = db.Products.sum("quantity");
System.out.println("Sum 1: " + sum1);
double sum2 = db.Products.sum("price * quantity");
System.out.println("Sum 2: " + sum2);
double sum3 = db.Products.Where(p -> p.isStatus()).sum("price * quantity");
System.out.println("Sum 3: " + sum3);
}
}
Output
Sum 1: 53.0
Sum 2: 461.0
Sum 3: 356.0