JDBC实现增删改查综合代码
目标：完成商品品牌的增删改查操作
步骤
-
准备环境
-
Navicat中编写表tb_brand
-
IDEA中编写表中字段名对应的实体类Brand
-
测试用例
-
增删改查练习
- 获取Connection
- 定义SQL
- 获取PreparedStatement对象
- 设置参数：不需要
- 执行SQL
- 处理结果：List<Brand>
- 释放资源
import com.alibaba.druid.pool.DruidDataSourceFactory; import javax.sql.DataSource; import java.io.FileInputStream; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.Arrays; import java.util.List; import java.util.Properties; public class BrandTest { public static void testSelectAll() throws Exception { //1.获取Connection //1.1加载配置文件 Properties prop = new Properties(); prop.load(new FileInputStream("D:/Code/JavaWeb/test/test01/src/druid.properties")); //1.2获取连接池对象 DataSource dataSource = DruidDataSourceFactory.createDataSource(prop); //1.3获取数据库连接 Connection conn = dataSource.getConnection(); //1.4显示连接状态 System.out.println(conn); //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()) { //6.1获取数据 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"); //6.2封装Brand对象 brand = new Brand(); brand.setId(id); brand.setBrandName(brandName); brand.setCompanyName(companyName); brand.setOrdered(ordered); brand.setDescription(description); brand.setStatus(status); //6.3装载集合 brands.add(brand); } System.out.println(brands); //7.释放资源 rs.close(); pstmt.close(); conn.close(); } public static void testAdd() throws Exception { //0.接收页面提交的参数(模拟) String brandName = "巴龙5000"; String companyName = "华为海思"; int ordered = 1000; String description = "澎湃S2，冉冉中国芯"; int status = 1; //1.获取Connection //1.1加载配置文件 Properties prop = new Properties(); prop.load(new FileInputStream("D:/Code/JavaWeb/test/test01/src/druid.properties")); //1.2获取连接池对象 DataSource dataSource = DruidDataSourceFactory.createDataSource(prop); //1.3获取数据库连接 Connection conn = dataSource.getConnection(); //1.4显示连接状态 System.out.println(conn); //2.定义SQL String sql = "insert into tb_brand (brand_name,company_name,ordered,description,status) values(?,?,?,?,?)"; //3.获取pstmt对象 PreparedStatement pstmt = conn.prepareStatement(sql); //4.设置参数(id类信息不应该由客户输入，所以id类信息不用设置参数) 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.处理结果(Boolean) System.out.println(count > 0); //7.释放资源 pstmt.close(); conn.close(); } public static void testUpdate() throws Exception { //0.接收页面提交的参数(模拟)，根据id进行更新 String brandName = "澎湃S2"; String companyName = "小米"; int ordered = 100; String description = "澎湃S2，冉冉中国芯"; int status = 1; int id = 3; //1.获取Connection //1.1加载配置文件 Properties prop = new Properties(); prop.load(new FileInputStream("D:/Code/JavaWeb/test/test01/src/druid.properties")); //1.2获取连接池对象 DataSource dataSource = DruidDataSourceFactory.createDataSource(prop); //1.3获取数据库连接 Connection conn = dataSource.getConnection(); //1.4显示连接状态 System.out.println(conn); //2.定义SQL String sql = "update tb_brand set brand_name = ?,company_name = ?,ordered = ?,description = ?,status = ? 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.处理结果(Boolean) System.out.println(count > 0); //7.释放资源 pstmt.close(); conn.close(); } public static void testDelete() throws Exception { //0.接收页面提交的参数(模拟)，接收id并根据id进行删除 int id = 3; //1.获取Connection //1.1加载配置文件 Properties prop = new Properties(); prop.load(new FileInputStream("D:/Code/JavaWeb/test/test01/src/druid.properties")); //1.2获取连接池对象 DataSource dataSource = DruidDataSourceFactory.createDataSource(prop); //1.3获取数据库连接 Connection conn = dataSource.getConnection(); //1.4显示连接状态 System.out.println(conn); //2.定义SQL String sql = "delete from tb_brand where id = ?;"; //3.获取pstmt对象 PreparedStatement pstmt = conn.prepareStatement(sql); //4.设置参数(需要i) pstmt.setInt(1, id); //5.执行SQL int count = pstmt.executeUpdate();//影响的行数 //6.处理结果(Boolean) System.out.println(count > 0); //7.释放资源 pstmt.close(); conn.close(); } public static void main(String[] args) throws Exception { System.out.println("查询数据任务处理结果："); testSelectAll(); System.out.println("----------------------------------------------------------------------"); System.out.println("添加数据任务处理结果："); testAdd(); System.out.println("----------------------------------------------------------------------"); System.out.println("更新数据任务处理结果："); testUpdate(); System.out.println("----------------------------------------------------------------------"); System.out.println("删除数据任务处理结果："); testDelete(); } } //SQLY /* drop table if exists 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 * from tb_brand; */
网友评论