1.需求
完成商品品牌数据的增删改查操作。
• 查询：查询所有数据
• 添加：添加品牌
• 修改：根据 id修改
• 删除：根据 id删除
2.环境搭建
•准备环境：1.创建项目 2.导入druid 和mysql驱动包 3.在src下面创建连接数据库的配置文件
Ø数据库表 tb_brand
Ø实体类 Brand
Ø测试用例
3.查询所有数据
1.获取Connection
2.定义SQL：select *** from tb_brand;
3.获取 PreparedStatement对象
4.设置参数：不需要
5.执行SQL
6.处理结果：List
7.释放资源
@Test public void query() throws Exception{ Properties p = new Properties(); p.load(new FileInputStream("src\\druid.properties")); DataSource ds = DruidDataSourceFactory.createDataSource(p); //获取Connection Connection conn = ds.getConnection(); //定义SQL：select * from tb_brand; String sql = "select * from tb_brand"; //获取 PreparedStatement对象 PreparedStatement pst = conn.prepareStatement(sql); //设置参数：不需要 //执行SQL ResultSet rs = pst.executeQuery(); //处理结果：List<Brand> ArrayList<Brand> list = 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); list.add(brand); } System.out.println("list = " + list); //释放资源 rs.close(); pst.close(); conn.close(); }
4.添加
@Test public void add() throws Exception{ Properties p = new Properties(); p.load(new FileInputStream("src\\druid.properties")); DataSource ds = DruidDataSourceFactory.createDataSource(p); //获取Connection Connection conn = ds.getConnection(); String sql = "insert into tb_brand values(null,?,?,?,?,?)"; //获取 PreparedStatement对象 PreparedStatement pst = conn.prepareStatement(sql); pst.setString(1,"苹果"); pst.setString(2,"apple"); pst.setInt(3,1); pst.setString(4,"贵"); pst.setInt(5,0); int count = pst.executeUpdate(); System.out.println("count = " + count); //释放资源 pst.close(); conn.close(); }
5.修改
@Test public void update() throws Exception{ Properties p = new Properties(); p.load(new FileInputStream("src\\druid.properties")); DataSource ds = DruidDataSourceFactory.createDataSource(p); //获取Connection Connection conn = ds.getConnection(); String sql = "update tb_brand set brand_name = ?,company_name= ?,ordered= ?,description = ?,status= ? where id = ?"; //获取 PreparedStatement对象 PreparedStatement pst = conn.prepareStatement(sql); pst.setString(1,"OPPO"); pst.setString(2,"OPPO"); pst.setInt(3,1); pst.setString(4,"贵"); pst.setInt(5,0); pst.setInt(6,3); int count = pst.executeUpdate(); System.out.println("count = " + count); //释放资源 pst.close(); conn.close(); }
6.删除
@Test public void delete() throws Exception{ Properties p = new Properties(); p.load(new FileInputStream("src\\druid.properties")); DataSource ds = DruidDataSourceFactory.createDataSource(p); //获取Connection Connection conn = ds.getConnection(); String sql = "delete from tb_brand where id = ?"; //获取 PreparedStatement对象 PreparedStatement pst = conn.prepareStatement(sql); pst.setInt(1,4); int count = pst.executeUpdate(); System.out.println("count = " + count); //释放资源 pst.close(); conn.close(); }
