对数据库内容进行增删改查
public class BrandTest {
/*
查询所有
*/
@Test
public void testSelectAll() throws Exception {
//1.获取Connection
//3.加载配置文件
Properties prop = new Properties();
prop.load(new FileInputStream("G:\\workspace\\javaweb\\jdbc\\jdbc-demo\\src\\druid.properties"));
//4.获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//获取数据库连接 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> 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(brand);
}
//7.释放资源
rs.close();
pstmt.close();
conn.close();
}
/*
添加操作
*/
@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("G:\\workspace\\javaweb\\jdbc\\jdbc-demo\\src\\druid.properties"));
//4.获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//获取数据库连接 Connection
Connection conn = dataSource.getConnection();
//2.定义SQL
String sql="insert into tb_brand(brand_name,company_name,ordered,descript ion,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);
pstmt.close();
conn.close();
}
/*
修改操作
*/
@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("G:\\workspace\\javaweb\\jdbc\\jdbc-demo\\src\\druid.properties"));
//4.获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//获取数据库连接 Connection
Connection conn = dataSource.getConnection();
//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.处理结果
System.out.println(count>0);
pstmt.close();
conn.close();
}
/*
修改操作
*/
@Test
public void testDeleteById() throws Exception {
//接受页面提交的参数
int id=4;
//1.获取Connection
//3.加载配置文件
Properties prop = new Properties();
prop.load(new FileInputStream("G:\\workspace\\javaweb\\jdbc\\jdbc-demo\\src\\druid.properties"));
//4.获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//获取数据库连接 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);
pstmt.close();
conn.close();
}
}