public class TestZSGC {
/*
查询所有
1.SQL: select * from tb_brand;
2.参数: 不需要
3. 结果: Lise<Brand>
*/
@Test
public void testSelectAll() throws Exception{
//1.获取Connection
Properties prop = new Properties();
prop.load(new FileInputStream("E:\\idea_java_project\\jdbc\\jdbc-demo\\src\\druid.properties"));
//获取连接池对象
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.处理结果
ArrayList<Brand> brands = new ArrayList<>();
Brand brand = null;
while(rs.next()){
// brands.add(new Brand(rs.getInt("id"),rs.getString("brand_name"),
// rs.getString("company_name"),rs.getInt("ordered"),
// rs.getString("description"),rs.getInt("status")));
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);
//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
Properties prop = new Properties();
prop.load(new FileInputStream("E:\\idea_java_project\\jdbc\\jdbc-demo\\src\\druid.properties"));
//获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//获取数据库链接 Connection
Connection conn = dataSource.getConnection();
//2.定义SQL语句
String sql = "insert into tb_brand (brand_name, company_name, ordered, description, status)\n" +
"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);
//7.释放资源
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
Properties prop = new Properties();
prop.load(new FileInputStream("E:\\idea_java_project\\jdbc\\jdbc-demo\\src\\druid.properties"));
//获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//获取数据库链接 Connection
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 = ?";
//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);
//7.释放资源
pstmt.close();
conn.close();
}
//删除
@Test
public void testDelete() throws Exception{
//接受页面提交的参数
int id = 4;
//1.获取Connection
Properties prop = new Properties();
prop.load(new FileInputStream("E:\\idea_java_project\\jdbc\\jdbc-demo\\src\\druid.properties"));
//获取连接池对象
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);
//7.释放资源
pstmt.close();
conn.close();
}
}