JDBC练习——完成商品品牌数据的增删改查操作
一、准备环境
1、数据库表tb_brand
2、实体类Brand
public class Brand {
//要与表中字段的类型和名称对应
private Integer id;
private String brandName;
private String companyName;
private Integer ordered;
private String description;
private Integer status; //0:禁用，1:启动
//提供Setter和Getter方法
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;
}
//提供toString方法
@Override
public String toString() {
return "Brand{" +
"id=" + id +
", brandName='" + brandName + ''' +
", companyName='" + companyName + ''' +
", ordered=" + ordered +
", description='" + description + ''' +
", status=" + status +
'}';
}
}
3、测试用例
期望把代码放到一个包内
package com.itheima.example;
public class BrandTest {
}
二、增删改查功能实现
查询：查询所有数据添加：添加品牌修改：根据id修改删除：根据id删除
（一）查询所有数据
1、步骤
（1）获取Connection
（2）定义SQL：select * from tb_brand
（3）获取PreparedStatement对象
（4）设置参数：（不需要）
（5）执行SQL
（6)处理结果：List< Brand >
（7）释放资源
@Test
public void testSelectAll() throws Exception{
//1、创建驱动
//2、获取连接
String url = "jdbc:mysql://localhost:3306/db1?serverTimezone=GMT";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url,username,password);
//3、定义SQL
String sql = "select * from tb_brand";
//4、获取PreparedStatement对象
PreparedStatement psmt = conn.prepareStatement(sql);
//5、设置参数：因为查询语句中没有？占位符，所以不需要设置参数
//6、执行SQL
ResultSet rs = psmt.executeQuery();
//7、处理结果：List< Brand >,遍历rs
List list = new ArrayList<>();
while (rs.next()){
//创建对象
Brand brand = new Brand();
//获取数据 getXxx()
//先写后面，前面alt+/
Integer id = rs.getInt("id");
String brandName = rs.getString("brand_name");
String companyName = rs.getString("company_name");
Integer ordered = rs.getInt("ordered");
String description = rs.getString("description");
Integer status = rs.getInt("status");
//封装brand对象
brand.setId(id);
brand.setBrandName(brandName);
brand.setCompanyName(companyName);
brand.setOrdered(ordered);
brand.setDescription(description);
brand.setStatus(status);
//封装集合
list.add(brand);
}
System.out.println(list);
//8、释放资源
psmt.close();
rs.close();
conn.close();
}
（二）添加品牌
1、编写SQL语句 insert into tb_brand (brand_name, company_name, ordered, description, status) values (?,?,?,?,?);
2、是否需要参数？需要，除了id之外的所有数据
3、返回结果如何封装？Boolean
@Test
public void testAdd() throws Exception{
//1、创建驱动
//2、获取连接
String url = "jdbc:mysql://localhost:3306/db1?serverTimezone=GMT";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url,username,password);
//3、定义SQL
String sql = "insert into tb_brand (brand_name, company_name, ordered, description, status) values (?,?,?,?,?);";
//4、获取PreparedStatement对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//接受用户输入的品牌信息
String input_brand_name = "龟苓膏";
String input_company_name = "苼禾堂";
int input_ordered = 9;
String input_description = "过瘾不过火";
int input_status = 0;
//5、设置参数：查询语句中？占位符
pstmt.setString(1, input_brand_name);
pstmt.setString(2,input_company_name);
pstmt.setInt(3,input_ordered);
pstmt.setString(4,input_description);
pstmt.setInt(5,input_status);
//6、执行sql
int count = pstmt.executeUpdate(); //因为是添加数据，一定要用executeUpdate()，count是影响的行数
//7、处理结果,判断登录是否成功，只要rs里面有数据，就说明登录成功
if(count>0){
System.out.println("添加成功");
}else{
System.out.println("添加失败");
}
//8、释放资源
pstmt.close();
conn.close();
}
（三）根据id修改
1、编写SQL语句
2、是否需要参数？需要：Brand对象所有数据
3、返回结果如何封装？boolean
@Test
public void testUpdate() throws Exception{
//1、创建驱动
//2、获取连接
String url = "jdbc:mysql://localhost:3306/db1?serverTimezone=GMT";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url,username,password);
//3、定义SQL
String sql = "update tb_brand set brand_name = ? , company_name = ? , " +
"ordered = ? , description = ? , status = ? " +
"where id = ?";
//4、获取PreparedStatement对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//接受用户输入的品牌信息
int input_id = 4;
String input_brand_name = "龟苓爽";
String input_company_name = "苼禾堂";
int input_ordered = 500;
String input_description = "过瘾不过火";
int input_status = 0;
//5、设置参数：查询语句中？占位符
pstmt.setString(1, input_brand_name);
pstmt.setString(2,input_company_name);
pstmt.setInt(3,input_ordered);
pstmt.setString(4,input_description);
pstmt.setInt(5,input_status);
pstmt.setInt(6,input_id);
//6、执行sql
int count = pstmt.executeUpdate(); //因为是添加数据，一定要用executeUpdate()，count是影响的行数
//7、处理结果,判断登录是否成功，只要rs里面有数据，就说明登录成功
if(count>0){
System.out.println("修改成功");
}else{
System.out.println("修改失败");
}
//8、释放资源
pstmt.close();
conn.close();
}
（四）根据id删除
1、编写SQL语句
delete from tb_brand where id = ?
2、是否需要参数？需要id
3、返回结果如何封装？boolean
@Test
public void testDelete() throws Exception{
//1、创建驱动
//2、获取连接
String url = "jdbc:mysql://localhost:3306/db1?serverTimezone=GMT";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url,username,password);
//3、定义SQL
String sql = "delete from tb_brand where id = ?";
//4、获取PreparedStatement对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//接受用户输入的品牌信息
int input_id = 4;
//5、设置参数：？占位符
pstmt.setInt(1,input_id);
//6、执行sql
int count = pstmt.executeUpdate(); //因为是删除数据，一定要用executeUpdate()，count是影响的行数
//7、处理结果,判断登录是否成功，只要rs里面有数据，就说明登录成功
if(count>0){
System.out.println("删除成功");
}else{
System.out.println("删除失败");
}
//8、释放资源
pstmt.close();
conn.close();
}
