5，JDBC练习
5.1 需求
完成商品品牌数据的增删改查操作
- 查询：查询所有数据
- 添加：添加品牌
- 修改：根据id修改
- 删除：根据id删除
5.2 案例实现
5.2.1 环境准备
- 数据库表
tb_brand
-- 删除tb_brand表 drop table if exists tb_brand; -- 创建tb_brand表 create table tb_brand ( -- id 主键 id int primary key auto_increment, -- 品牌名称 brand_name varchar(20), -- 企业名称 company_name varchar(20), -- 排序字段 ordered int, -- 描述信息 description varchar(100), -- 状态：0：禁用 1：启用 status int ); -- 添加数据 insert into tb_brand (brand_name, company_name, ordered, description, status) values ('三只松鼠', '三只松鼠股份有限公司', 5, '好吃不上火', 0), ('华为', '华为技术有限公司', 100, '华为致力于把数字世界带入每个人、每个家庭、每个组织，构建万物互联的智能世界', 1), ('小米', '小米科技有限公司', 50, 'are you ok', 1);
在pojo包下实体类 Brand
/** * 品牌 * alt + 鼠标左键：整列编辑 * 在实体类中，基本数据类型建议使用其对应的包装类型 */ public class Brand { // id 主键 private Integer id; // 品牌名称 private String brandName; // 企业名称 private String companyName; // 排序字段 private Integer ordered; // 描述信息 private String description; // 状态：0：禁用 1：启用 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 + '}'; } }
5.2.2 查询所有
/** * 查询所有 * 1. SQL：select * from tb_brand; * 2. 参数：不需要 * 3. 结果：List<Brand> */ @Test public void testSelectAll() throws Exception { //1. 获取Connection //3. 加载配置文件 Properties prop = new Properties(); prop.load(new FileInputStream("jdbc-demo/src/druid.properties")); //4. 获取连接池对象 DataSource dataSource = DruidDataSourceFactory.createDataSource(prop); //5. 获取数据库连接 Connection Connection conn = dataSource.getConnection(); //2. 定义SQL String sql = "select * from tb_brand;"; //3. 获取pstmt对象 PreparedStatement pstmt = conn.prepareStatement(sql); //4. 设置参数 //5. 执行SQL ResultSet rs = pstmt.executeQuery(); //6. 处理结果 List<Brand> 封装Brand对象，装载List集合 Brand brand = null; List<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); //7. 释放资源 rs.close(); pstmt.close(); conn.close(); }
5.2.3 添加数据
/** * 添加 * 1. SQL：insert into tb_brand(brand_name, company_name, ordered, description, status) values(?,?,?,?,?); * 2. 参数：需要，除了id之外的所有参数信息 * 3. 结果：boolean */ @Test public void testAdd() throws Exception { // 接收页面提交的参数 String brandName = "香飘飘"; String companyName = "香飘飘"; int ordered = 1; String description = "绕地球一圈"; int status = 1; //1. 获取Connection //3. 加载配置文件 Properties prop = new Properties(); prop.load(new FileInputStream("jdbc-demo/src/druid.properties")); //4. 获取连接池对象 DataSource dataSource = DruidDataSourceFactory.createDataSource(prop); //5. 获取数据库连接 Connection Connection conn = dataSource.getConnection(); //2. 定义SQL String sql = "insert into tb_brand(brand_name, company_name, ordered, description, status) values(?,?,?,?,?);"; //3. 获取pstmt对象 PreparedStatement pstmt = conn.prepareStatement(sql); //4. 设置参数 pstmt.setString(1,brandName); pstmt.setString(2,companyName); pstmt.setInt(3,ordered); pstmt.setString(4,description); pstmt.setInt(5,status); //5. 执行SQL int count = pstmt.executeUpdate(); // 影响的行数 //6. 处理结果 System.out.println(count > 0); //7. 释放资源 pstmt.close(); conn.close(); }
5.2.4 修改数据
/** * 修改 * 1. SQL： update tb_brand set brand_name = ?, company_name= ?, ordered = ?, description = ?, status = ? where id = ? * 2. 参数：需要，所有数据 * 3. 结果：boolean */ @Test public void testUpdate() throws Exception { // 接收页面提交的参数 String brandName = "香飘飘"; String companyName = "香飘飘"; int ordered = 1000; String description = "绕地球三圈"; int status = 1; int id = 4; //1. 获取Connection //3. 加载配置文件 Properties prop = new Properties(); prop.load(new FileInputStream("jdbc-demo/src/druid.properties")); //4. 获取连接池对象 DataSource dataSource = DruidDataSourceFactory.createDataSource(prop); //5. 获取数据库连接 Connection Connection conn = dataSource.getConnection(); //2. 定义SQL String sql = " update tb_brand\n" + " set brand_name = ?,\n" + " company_name= ?,\n" + " ordered = ?,\n" + " description = ?,\n" + " status = ?\n" + " where id = ?"; //3. 获取pstmt对象 PreparedStatement pstmt = conn.prepareStatement(sql); //4. 设置参数 pstmt.setString(1,brandName); pstmt.setString(2,companyName); pstmt.setInt(3,ordered); pstmt.setString(4,description); pstmt.setInt(5,status); pstmt.setInt(6,id); //5. 执行SQL int count = pstmt.executeUpdate(); // 影响的行数 //6. 处理结果 System.out.println(count > 0); //7. 释放资源 pstmt.close(); conn.close(); }
5.2.5 删除数据
/** * 删除 * 1. SQL： delete from tb_brand where id = ? * 2. 参数：需要，id * 3. 结果：boolean */ @Test public void testDeleteById() throws Exception { // 接收页面提交的参数 int id = 4; //1. 获取Connection //3. 加载配置文件 Properties prop = new Properties(); prop.load(new FileInputStream("jdbc-demo/src/druid.properties")); //4. 获取连接池对象 DataSource dataSource = DruidDataSourceFactory.createDataSource(prop); //5. 获取数据库连接 Connection Connection conn = dataSource.getConnection(); //2. 定义SQL String sql = " delete from tb_brand where id = ?"; //3. 获取pstmt对象 PreparedStatement pstmt = conn.prepareStatement(sql); //4. 设置参数 pstmt.setInt(1,id); //5. 执行SQL int count = pstmt.executeUpdate(); // 影响的行数 //6. 处理结果 System.out.println(count > 0); //7. 释放资源 pstmt.close(); conn.close(); }
