JDBC 实现数据库的增删改查

public class BrandTest {

    /*
    * 查询所有
    * */
    @Test
    public void testSelectAll() throws Exception {
        Properties prop = new Properties();
        System.out.println(System.getProperty("user.dir"));// 显示当前工作目录
        prop.load(new FileInputStream("src/druid.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        Connection conn = dataSource.getConnection();

        String sql = "select * from brand";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        ArrayList<Brand> list = new ArrayList<>();
        Brand brand = null;
        while (rs.next()){
            brand = new Brand();
            brand.setId(rs.getInt("id"));
            brand.setBrandName(rs.getString("brand_name"));
            brand.setCompanyName(rs.getString("company_name"));
            brand.setOrdered(rs.getInt("ordered"));
            brand.setDescription(rs.getString("discription"));
            brand.setStatus(rs.getInt("status"));
            list.add(brand);
        }
        System.out.println(list);
        conn.close();
        rs.close();
        pstmt.close();
    }

    /*
    * 添加
    * */
    @Test
    public void testAdd() throws Exception {

        String brandName = "香飘飘";
        String companyName = "香飘飘公司";
        int ordered = 1;
        String discription = "绕地球一圈！";
        int status = 1;

        Properties prop = new Properties();
        System.out.println(System.getProperty("user.dir"));
        prop.load(new FileInputStream("src/druid.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        Connection conn = dataSource.getConnection();

        String sql = "insert into brand(brand_name, company_name, ordered, discription, status) values(?,?,?,?,?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, brandName);
        pstmt.setString(2, companyName);
        pstmt.setInt(3, ordered);
        pstmt.setString(4, discription);
        pstmt.setInt(5, status);
        int count = pstmt.executeUpdate();
        if (count > 0){
            System.out.println("添加成功！");
        }else {
            System.out.println("添加失败！");
        }
        conn.close();
        pstmt.close();
    }

    /*
    * 修改
    * */
    @Test
    public void testUpdate() throws Exception {

        String brandName = "香飘飘";
        String companyName = "香飘飘公司";
        int ordered = 100;
        String discription = "绕地球一圈！再绕一圈!";
        int status = 1;
        int id = 8;

        Properties prop = new Properties();
        System.out.println(System.getProperty("user.dir"));
        prop.load(new FileInputStream("src/druid.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        Connection conn = dataSource.getConnection();

        String sql = "update brand set brand_name = ?, company_name = ?, ordered = ?, discription = ?, status = ? " +
                "where id = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, brandName);
        pstmt.setString(2, companyName);
        pstmt.setInt(3, ordered);
        pstmt.setString(4, discription);
        pstmt.setInt(5, status);
        pstmt.setInt(6, id);

        int count = pstmt.executeUpdate();
        if (count > 0){
            System.out.println("修改成功！");
        }else {
            System.out.println("修改失败！");
        }
        conn.close();
        pstmt.close();
    }

    /*
     * 删除
     * */
    @Test
    public void testDeleteById() throws Exception {

        int id = 9;

        Properties prop = new Properties();
        System.out.println(System.getProperty("user.dir"));
        prop.load(new FileInputStream("src/druid.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        Connection conn = dataSource.getConnection();

        String sql = "delete from brand where id = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1, id);

        int count = pstmt.executeUpdate();
        if (count > 0){
            System.out.println("删除成功！");
        }else {
            System.out.println("删除失败！");
        }
        conn.close();
        pstmt.close();
    }
}

druid.properties

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///test?useSSL=false&useServerPrepStmts=true
username=
password=
initialSize=5
maxActive=10
maxWait=3000

Brand.java

public class Brand  {
    private Integer id;
    private String brandName;
    private String companyName;
    private Integer ordered;
    private String description;
    private Integer status;

    public Integer getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getBrandName() {
        return brandName;
    }

    public void setBrandName(String brand_name) {
        this.brandName = brand_name;
    }

    public String getCompanyName() {
        return companyName;
    }

    public void setCompanyName(String company_name) {
        this.companyName = company_name;
    }

    public Integer getOrdered() {
        return ordered;
    }

    public void setOrdered(int 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(int status) {
        this.status = status;
    }

    @Override
    public String toString() {
        return "Brand{" +
                "id=" + id +
                ", brand_name='" + brandName + '\'' +
                ", company_name='" + companyName + '\'' +
                ", ordered=" + ordered +
                ", description='" + description + '\'' +
                ", status=" + status +
                '}';
    }
}