JDBC练习-增删改查
package com.czie.example; import com.alibaba.druid.pool.DruidDataSourceFactory; import javax.sql.DataSource; import java.io.FileInputStream; import java.sql.Connection; import java.util.Properties; import com.czie.pojo.Brand; import org.junit.Test; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; import java.util.zip.CheckedOutputStream; /** * 品牌数据的增删改查操作 */ public class BrandTest { /** * 查询所有 * 1. SQL：select * from tb_brand; * 2. 参数：不需要 * 3. 结果：List<Brand> */ @Test public void testSelectAll() throws Exception { // String url="jdbc:mysql://localhost:3306/lps?useSSL=false&useServerPrepStmts=true"; // String username="root"; // String password="1234"; // Connection conn = DriverManager.getConnection(url,username,password); //3. 加载配置文件 //1.导入jar包 //2.定义配置文件 //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(); //定义sql语句 String sql = "select * from tb_brand"; //获取pstmt对象 PreparedStatement pstmt = conn.prepareStatement(sql); //设置参数 //执行sql ResultSet rs = pstmt.executeQuery(); //处理结果 list<brand> 封装Brand对象 装载到list集合之中 List<Brand> brands = new ArrayList<>(); Brand brand = null; 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); //释放资源 rs.close(); conn.close(); pstmt.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 { // String url="jdbc:mysql://localhost:3306/lps?useSSL=false&useServerPrepStmts=true"; // String username="root"; // String password="1234"; // Connection conn = DriverManager.getConnection(url,username,password); //3. 加载配置文件 //1.导入jar包 //2.定义配置文件 // 接收页面提交的参数 String brandName = "香飘飘"; String companyName = "香飘飘"; int ordered = 1; String description = "绕地球一圈"; int status = 1; //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(); //定义sql语句 String sql = "insert into tb_brand (brand_name, company_name, ordered, description, status)\n" + "values (?,?,?,?,?);"; //获取pstmt对象 PreparedStatement pstmt = conn.prepareStatement(sql); //设置参数 pstmt.setString(1,brandName); pstmt.setString(2,companyName); pstmt.setInt(3,ordered); pstmt.setString(4,description); pstmt.setInt(5,status); //执行sql int count = pstmt.executeUpdate(); System.out.println("受影响的行数"+count); System.out.println(count>0); //释放资源 conn.close(); pstmt.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 url="jdbc:mysql://localhost:3306/lps?useSSL=false&useServerPrepStmts=true"; // String username="root"; // String password="1234"; // Connection conn = DriverManager.getConnection(url,username,password); //3. 加载配置文件 //1.导入jar包 //2.定义配置文件 // 接收页面提交的参数 String brandName = "香飘飘"; String companyName = "香飘飘"; int ordered = 1000; String description = "绕地球三圈"; int status = 1; int id = 4; //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(); //定义sql语句 String sql = "update tb_brand set " + "brand_name=?,\n" + "company_name=?,\n" + "ordered=?,\n" + "description=?,\n" + "status=?\n" + "WHERE id =?"; //获取pstmt对象 PreparedStatement pstmt = conn.prepareStatement(sql); //设置参数 pstmt.setString(1,brandName); pstmt.setString(2,companyName); pstmt.setInt(3,ordered); pstmt.setString(4,description); pstmt.setInt(5,status); pstmt.setInt(6,id); //执行sql int count = pstmt.executeUpdate(); System.out.println("受影响的行数"+count); System.out.println(count>0); //释放资源 conn.close(); pstmt.close(); } /** * 删除 * 1. SQL： delete from tb_brand where id = ? * 2. 参数：需要，id * 3. 结果：boolean */ @Test public void testDeleteByID() throws Exception { // String url="jdbc:mysql://localhost:3306/lps?useSSL=false&useServerPrepStmts=true"; // String username="root"; // String password="1234"; // Connection conn = DriverManager.getConnection(url,username,password); //3. 加载配置文件 //1.导入jar包 //2.定义配置文件 // 接收页面提交的参数 int id = 4; //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(); //定义sql语句 String sql ="delete from tb_brand where id=?"; //获取pstmt对象 PreparedStatement pstmt = conn.prepareStatement(sql); //设置参数 pstmt.setInt(1,id); //执行sql int count = pstmt.executeUpdate(); System.out.println("受影响的行数"+count); System.out.println(count>0); //释放资源 conn.close(); pstmt.close(); } }
driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql:///lps?useSSL=false&useServerPrepStmts=true username=root password=1234 # 初始化连接数量 initialSize=5 # 最大连接数 maxActive=10 # 最大等待时间 maxWait=3000
-- 删除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);
SELECT * FROM tb_brand;
