JDBC完成商品品牌数据的增删改查操作
- 查询:查询所有数据
- 添加:添加品牌
- 修改:根据id修改
- 删除:根据id删除
首先环境准备
数据库表 tb_brand
CREATE TABLE tb_brand (
id INT PRIMARY KEY auto_increment,
brand_name VARCHAR ( 20 ),
company_name VARCHAR ( 20 ),
ordered INT,
description VARCHAR ( 100 ),
`status` INT );
INSERT INTO tb_brand ( brand_name, company_name, ordered, description, `status` )
VALUES
( '三只松鼠', '三只松鼠股份有限公司', 5, '好吃', 0 ),
( '华为', '华为技术有限公司', 100, '华为致力于构建万物互联的智能世界', 1 ),
( '小米', '小米技术有限公司', 50, 'are you ok', 1 );
select id,brand_name, company_name, ordered, description, `status` from tb_brand;
实体类 Brand
package com.itlyc.opjo;
public class Brand {
private Integer id ;
private String brandName;
private String companyName ;
private Integer ordered;
private String description;
private Integer status;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getBrandName() {
return brandName;
}
public void setBrandName(String brandName) {
this.brandName = brandName;
}
public String getCompanyName() {
return companyName;
}
public void setCompanyName(String companyName) {
this.companyName = companyName;
}
public Integer getOrdered() {
return ordered;
}
public void setOrdered(Integer ordered) {
this.ordered = ordered;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
public Integer getStatus() {
return status;
}
public void setStatus(Integer status) {
this.status = status;
}
@Override
public String toString() {
return "Brand{" +
"id=" + id +
", brandName='" + brandName + '\'' +
", companyName='" + companyName + '\'' +
", ordered=" + ordered +
", description='" + description + '\'' +
", status=" + status +
'}';
}
}
测试用例
查询操作
public class BrandTest {
@Test
public void testSelectAll() throws Exception {
Properties prop = new Properties();
prop.load(new FileInputStream("../jdbc-demo/src/druid.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
Connection connection = dataSource.getConnection();
String sql = "select * from tb_brand";
PreparedStatement pstmt = connection.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
ArrayList<Brand> brands = new ArrayList<>();
while(rs.next()){
int id = rs.getInt("id");
String brandName = rs.getString("brand_name");
String companyName = rs.getString("company_name");
int ordered = rs.getInt("ordered");
String description = rs.getString("description");
int status = rs.getInt("status");
Brand brand = new Brand();
brand.setId(id);
brand.setBrandName(brandName);
brand.setCompanyName(companyName);
brand.setOrdered(ordered);
brand.setDescription(description);
brand.setStatus(status);
brands.add(brand);
}
System.out.println(brands);
rs.close();
pstmt.close();
connection.close();
}
}
添加操作
@Test
public void testSelectAdd() throws Exception {
String brandName ="香喷喷";
String companyName = "香喷喷";
int ordered = 1;
String description="绕地球一圈";
int status =1;
Properties prop = new Properties();
prop.load(new FileInputStream("../jdbc-demo/src/druid.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
Connection connection = dataSource.getConnection();
String sql = "insert into tb_brand ( brand_name, company_name, ordered, description, `status` )values(?,?,?,?,?)";
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setString(1,brandName);
pstmt.setString(2,companyName);
pstmt.setInt(3,ordered);
pstmt.setString(4,description);
pstmt.setInt(5,status);
int i = pstmt.executeUpdate();
System.out.println(i>0);
pstmt.close();
connection.close();
}
修改操作:根据id进行修改
@Test
public void testUpdate() throws Exception {
String brandName ="香喷喷";
String companyName = "香喷喷公司";
int ordered = 1000;
String description="绕地球3圈";
int status =1;
int id = 4;
Properties prop = new Properties();
prop.load(new FileInputStream("../jdbc-demo/src/druid.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
Connection connection = dataSource.getConnection();
String sql = "update tb_brand set brand_name=?, company_name=?,ordered = ?,description=?,status=? where id = ?";
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setString(1,brandName);
pstmt.setString(2,companyName);
pstmt.setInt(3,ordered);
pstmt.setString(4,description);
pstmt.setInt(5,status);
pstmt.setInt(6,id);
int i = pstmt.executeUpdate();
System.out.println(i>0);
pstmt.close();
connection.close();
}
删除操作:根据id进行删除操作
@Test
public void testDelete() throws Exception {
int id = 4;
Properties prop = new Properties();
prop.load(new FileInputStream("../jdbc-demo/src/druid.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
Connection connection = dataSource.getConnection();
String sql = "delete from tb_brand where id = ?";
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1,id);
int i = pstmt.executeUpdate();
System.out.println(i>0);
pstmt.close();
connection.close();
}