?
?
?
查询数据
//查询所有数据
@Test
public void selectALL() throws Exception {
//1.获取 Connection 对象
//1.1加载配置文件
Properties prop = new Properties();
prop.load(new FileInputStream("src/druid.properties"));
//1.2获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//1.3获取数据库连接 Connection
Connection conn = dataSource.getConnection();
//2.定义SQL语句
String sql = "select * from tb_brand;";
//3.获取PreparedStatement对象
PreparedStatement ps = conn.prepareStatement(sql);
//4.设置参数
//5.执行sql
ResultSet res = ps.executeQuery();
//6.处理结果
ArrayList<Brand> brands = new ArrayList<>();
while (res.next()) {
//创建对象
Brand brand = new Brand();
//获取数据
int id = res.getInt("id");
String brandName = res.getString("brand_name");
String companyName = res.getString("company_name");
int ordered = res.getInt("ordered");
String description = res.getString("description");
int status = res.getInt("status");
//给对象赋值
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.释放资源
res.close();
ps.close();
conn.close();
}
添加数据
//添加数据
@Test
public void add() throws Exception {
String brandName = "香飘飘";
String companyName = "香飘飘公司";
Integer ordered = 80;
String description = "绕地球一圈";
Integer status = 1;
//1.获取 Connection 对象
//1.1加载配置文件
Properties prop = new Properties();
prop.load(new FileInputStream("src/druid.properties"));
//1.2获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//1.3获取数据库连接 Connection
Connection conn = dataSource.getConnection();
//2.定义SQL语句
String sql = "insert into tb_brand (brand_name, company_name, ordered, description, status)" +
" values (?,?,?,?,?) ";
//3.获取PreparedStatement对象
PreparedStatement ps = conn.prepareStatement(sql);
//4.设置参数
ps.setString(1, brandName);
ps.setString(2, companyName);
ps.setInt(3, ordered);
ps.setString(4, description);
ps.setInt(5, status);
//5.执行sql
int count = ps.executeUpdate();
//6.处理结果
if (count > 0) {
System.out.println("执行成功");
} else {
System.out.println("执行失败");
}
//7.释放资源
ps.close();
conn.close();
}修改数据
@Test
public void update() throws Exception {
String brandName = "香飘飘";
String companyName = "香飘飘公司";
Integer ordered = 200;
String description = "绕地球三圈";
Integer status = 1;
Integer id = 4;
//1.获取 Connection 对象
//1.1加载配置文件
Properties prop = new Properties();
prop.load(new FileInputStream("src/druid.properties"));
//1.2获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//1.3获取数据库连接 Connection
Connection conn = dataSource.getConnection();
//2.定义SQL语句
String sql = "update tb_brand " +
"set brand_name =?,company_name=?,ordered=?,description=?,status=? " +
"where id =?";
//3.获取PreparedStatement对象
PreparedStatement ps = conn.prepareStatement(sql);
//4.设置参数
ps.setString(1, brandName);
ps.setString(2, companyName);
ps.setInt(3, ordered);
ps.setString(4, description);
ps.setInt(5, status);
ps.setInt(6, id);
//5.执行sql
int count = ps.executeUpdate();
//6.处理结果
System.out.println(count > 0);
//7.释放资源
ps.close();
conn.close();
}删除数据
@Test
public void delete() throws Exception {
Integer id = 4;
//1.获取 Connection 对象
//1.1加载配置文件
Properties prop = new Properties();
prop.load(new FileInputStream("src/druid.properties"));
//1.2获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//1.3获取数据库连接 Connection
Connection conn = dataSource.getConnection();
//2.定义SQL语句
String sql = "delete from tb_brand where id = ?";
//3.获取PreparedStatement对象
PreparedStatement ps = conn.prepareStatement(sql);
//4.设置参数
ps.setInt(1, id);
//5.执行sql
int count = ps.executeUpdate();
//6.处理结果
System.out.println(count > 0);
//7.释放资源
ps.close();
conn.close();
}
