1.导入druid的驱动jar包
2.编写brand实体类
private Integer id;
private String brandName;
private String companyName;
private String ordered;
private String description;
private Integer status;
3.创建brand实体的get和set方法,并生成toString方法
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getBrandName() {
return brandName;
}
public void setBrandName(String brandName) {
this.brandName = brandName;
}
public String getCompanyName() {
return companyName;
}
public void setCompanyName(String companyName) {
this.companyName = companyName;
}
public String getOrdered() {
return ordered;
}
public void setOrdered(String ordered) {
this.ordered = ordered;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
public Integer getStatus() {
return status;
}
public void setStatus(Integer status) {
this.status = status;
}
@Override
public String toString() {
return "Brand{" +
"id=" + id +
", brandName='" + brandName + '\'' +
", companyName='" + companyName + '\'' +
", ordered='" + ordered + '\'' +
", description='" + description + '\'' +
", status=" + status +
'}';
}
4.增删改查测试
4.1查询所有
@Test
//查询所有信息
public void testSelectAll() throws Exception {
Properties properties = new Properties();
properties.load(new FileInputStream("src/druid.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
Connection connection = dataSource.getConnection();
//定义sql语句
String sql = "select * from tb_brand";
//获取statement对象
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//执行sql语句
ResultSet rs = preparedStatement.executeQuery();
//创建brandList集合
ArrayList<Brand> brandList = new ArrayList<>();
Brand brand = null;
while (rs.next()) {
int id = rs.getInt("id");
String brand_name = rs.getString("brand_name");
String company_name = rs.getString("company_name");
String ordered = rs.getString("ordered");
String description = rs.getString("description");
int status = rs.getInt("status");
//封装brand对象
brand = new Brand();
brand.setId(id);
brand.setBrandName(brand_name);
brand.setCompanyName(company_name);
brand.setOrdered(ordered);
brand.setDescription(description);
brand.setStatus(status);
//转载集合
brandList.add(brand);
}
System.out.println(brandList);
//释放资源
rs.close();
connection.close();
preparedStatement.close();
}
4.2新增
@Test
//新增
public void testAdd() throws Exception {
//接收页面提交的参数
String brandName = "香飘飘";
String companyName = "香飘飘";
String ordered = "20";
String description = "真好喝";
int status = 1;
Properties properties = new Properties();
properties.load(new FileInputStream("src/druid.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
Connection connection = dataSource.getConnection();
//定义sql语句
String sql = "insert into tb_brand(brand_name,company_name,ordered,description,status) values (?,?,?,?,?)";
//获取statement对象
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//设置?参数
preparedStatement.setString(1,brandName);
preparedStatement.setString(2,companyName);
preparedStatement.setString(3,ordered);
preparedStatement.setString(4,description);
preparedStatement.setInt(5,status);
//执行sql语句
int count = preparedStatement.executeUpdate();
//处理结果
System.out.println(count>0);
//释放资源
connection.close();
preparedStatement.close();
}
4.3 修改
@Test
//修改所有信息
public void testUpdate() throws Exception {
//接收页面提交的参数
String brandName = "香飘飘";
String companyName = "香飘飘";
String ordered = "20";
String description = "绕地球一圈";
int status = 1;
int id = 63;
Properties properties = new Properties();
properties.load(new FileInputStream("src/druid.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
Connection connection = dataSource.getConnection();
//定义sql语句
String sql = "update tb_brand set brand_name=?,company_name=?,ordered=?,description=?,status=? where id=?";
//获取statement对象
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//设置?参数
preparedStatement.setString(1,brandName);
preparedStatement.setString(2,companyName);
preparedStatement.setString(3,ordered);
preparedStatement.setString(4,description);
preparedStatement.setInt(5,status);
preparedStatement.setInt(6,id);
//执行sql语句
int count = preparedStatement.executeUpdate();
//处理结果
System.out.println(count>0);
//释放资源
connection.close();
preparedStatement.close();
}
4.4 删除
@Test
//根据id删除信息
public void testDeleteById() throws Exception {
//接收页面提交的参数
int id = 63;
Properties properties = new Properties();
properties.load(new FileInputStream("src/druid.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
Connection connection = dataSource.getConnection();
//定义sql语句
String sql = "delete from tb_brand where id=?";
//获取statement对象
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//设置?参数
preparedStatement.setInt(1,id);
//执行sql语句
int count = preparedStatement.executeUpdate();
//处理结果
System.out.println(count>0);
//释放资源
connection.close();
preparedStatement.close();
}