先把练习用的表建立出来
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); SELECT * FROM tb_brand;
然后是主类Brand
package pojo; /** * 品牌 */ public class Brand { 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 String getBrandName() { return brandName; } public String getCompanyName() { return companyName; } public Integer getOrdered() { return ordered; } public String getDescription() { return description; } public Integer getStatus() { return status; } public void setId(Integer id) { this.id = id; } public void setBrandName(String brandName) { this.brandName = brandName; } public void setCompanyName(String companyName) { this.companyName = companyName; } public void setOrdered(Integer ordered) { this.ordered = ordered; } public void setDescription(String description) { this.description = description; } 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 + '}'; } }
之后为了方便测试，整了一下junit，然后就可以敲主要内容了
package example.example; import com.alibaba.druid.pool.DruidDataSourceFactory; import org.junit.jupiter.api.Test; import pojo.Brand; import javax.sql.DataSource; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; import java.util.Properties; import java.util.ResourceBundle; /** * 品牌数据增删改查 */ public class Brandtest { /** * 查询所有 * 1.sql:select * from tb_brand * 2.参数：不需要 * 3.结果：list<Brand> */ @Test public void testSelectall() throws Exception { //1.获取connection Properties prop=new Properties(); prop.load(new FileInputStream("D:\\66\\jdbc_demo\\src\\druid.properties")); DataSource dataSource= DruidDataSourceFactory.createDataSource(prop); Connection conn=dataSource.getConnection(); //2.定义sql String sql="select * from tb_brand"; PreparedStatement psmt =conn.prepareStatement(sql); //4.设置参数 //5.执行sql ResultSet rs =psmt.executeQuery(); //6.处理结果 list<Brand> Brand brand=new Brand(); 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.setId(id); brand.setBrandName(brandName); brand.setCompanyName(companyName); brand.setDescription(description); brand.setStatus(status); brand.setOrdered(ordered); //装载集合 brands.add(brand); } //7.释放资源 rs.close(); psmt.close(); conn.close(); System.out.println(brands); } /** * 添加 * 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="theshy"; String companyName="wbg"; int ordered=1; String description="别吃，别吃"; int status=1; //1.获取connection Properties prop=new Properties(); prop.load(new FileInputStream("D:\\66\\jdbc_demo\\src\\druid.properties")); DataSource dataSource= DruidDataSourceFactory.createDataSource(prop); Connection conn=dataSource.getConnection(); //2.定义sql String sql="insert into tb_brand(brand_name, company_name, ordered, description, status) values (?,?,?,?,?);"; PreparedStatement psmt =conn.prepareStatement(sql); //4.设置参数 psmt.setString(1,brandName); psmt.setString(2,companyName); psmt.setInt(3,ordered); psmt.setString(4,description); psmt.setInt(5,status); //5.执行sql int count=psmt.executeUpdate();//影响的行数 //6.处理结果 System.out.println(count>0); //7.释放资源 psmt.close(); conn.close(); } /** * 修改 * 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="theshy"; String companyName="wbg"; int ordered=1000; String description="别吃，别吃"; int status=1; int id=4; //1.获取connection Properties prop=new Properties(); prop.load(new FileInputStream("D:\\66\\jdbc_demo\\src\\druid.properties")); DataSource dataSource= DruidDataSourceFactory.createDataSource(prop); 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=?;"; PreparedStatement psmt =conn.prepareStatement(sql); //4.设置参数 psmt.setString(1,brandName); psmt.setString(2,companyName); psmt.setInt(3,ordered); psmt.setString(4,description); psmt.setInt(5,status); psmt.setInt(6,id); //5.执行sql int count=psmt.executeUpdate();//影响的行数 //6.处理结果 System.out.println(count>0); //7.释放资源 psmt.close(); conn.close(); } /** * 删除 * 1.sql:delete from tb_brand where id=?; * 2.参数：需要id * 3.结果：boolean */ @Test public void testdelete() throws Exception { int id=4; //1.获取connection Properties prop=new Properties(); prop.load(new FileInputStream("D:\\66\\jdbc_demo\\src\\druid.properties")); DataSource dataSource= DruidDataSourceFactory.createDataSource(prop); Connection conn=dataSource.getConnection(); //2.定义sql String sql="delete from tb_brand where id=?;"; PreparedStatement psmt =conn.prepareStatement(sql); //4.设置参数 psmt.setInt(1,id); //5.执行sql int count=psmt.executeUpdate();//影响的行数 //6.处理结果 System.out.println(count>0); //7.释放资源 psmt.close(); conn.close(); } }