JavaWeb学习--JDBC增删盖查案例

前期准备:在数据库中已经建好表和数据,表名为tb_brand,其中有三条数据

进入练习:

1.查询

 

/**
     * 查询所有
     * 1.SQL:select * from tb_brand
     * 2.参数:不需要
     * 3.结果:返回一个List<Brand>类型
     */

    @Test
    public  void testSelectAll() throws Exception {

//        System.out.println("*******************");
//        System.out.println(System.getProperty("user.dir"));
//        System.out.println("*******************");

        //1.获取Connection连接池
        Properties prop = new Properties();
        prop.load(new FileInputStream("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,查询Query
        ResultSet rs = pstmt.executeQuery();

        //6.处理结果List<Brand>,将Brand对象装载到List集合

        Brand brand = null;//复用
        List<Brand> brands = 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);

            //装载Brand对象

            brands.add(brand);

        }
        System.out.println(brands);

        //7.释放资源
        rs.close();
        pstmt.close();
        conn.close();
    }

 打印brands,数据已经查取到并存入List中

2.添加

 

/**
     * 添加
     * 1.SQL:insert into tb_brand(brand_name,company_name,ordered,description,status) values(?,?,?,?,?);
     * 2.是否需要参数:需要,处理id之外所有,应为id应该自增而不是去设置
     * 3.返回结果? 成功或失败,boolean
     * @throws Exception
     */


    @Test
    public  void testAdd() throws Exception {

//        System.out.println("*******************");
//        System.out.println(System.getProperty("user.dir"));
//        System.out.println("*******************");


        //假设这里是页面提交的参数
        String brandName = "香飘飘";
        String companyName = "香飘飘";
        int ordered = 1;
        String description = "绕地球一圈";
        int status = 1;

        //1.获取Connection连接池
        Properties prop = new Properties();
        prop.load(new FileInputStream("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) 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,增加用Update
        int count = pstmt.executeUpdate(); //返回的是影响的行数

        //6.处理结果
        System.out.println(count > 0);


        //7.释放资源

        pstmt.close();
        conn.close();
    }

数据库中新增一行信息

 

3.修改

 

/**
     * 修改
     * 1.SQL:update tb_brand set brand_name = ?,company_name = ?, ordered = ?,description = ?,status = ? where id = ?
     * 2.是否需要参数:需要,所有数据
     * 3.返回结果? 成功或失败,boolean
     * @throws Exception
     */


    @Test
    public  void testUpdate() throws Exception {

//        System.out.println("*******************");
//        System.out.println(System.getProperty("user.dir"));
//        System.out.println("*******************");


        //假设这里是页面提交的参数
        String brandName = "香飘飘";
        String companyName = "香飘飘";
        int ordered = 10000;
        String description = "绕地球三圈";
        int status = 1;
        int id = 4;

        //1.获取Connection连接池
        Properties prop = new Properties();
        prop.load(new FileInputStream("src/druid.properties"));
        //获取连接池对象
        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,增加用Update
        int count = pstmt.executeUpdate(); //返回的是影响的行数

        //6.处理结果
        System.out.println(count > 0);


        //7.释放资源

        pstmt.close();
        conn.close();
    }

修改了ordered和description中的数据

 

4.删除

 

 /**
     * 删除
     * 1.SQL:delete from tb_brand where id = ?
     * 2.是否需要参数:需要,id
     * 3.返回结果? 成功或失败,boolean
     * @throws Exception
     */


    @Test
    public  void testDeleteById() throws Exception {

//        System.out.println("*******************");
//        System.out.println(System.getProperty("user.dir"));
//        System.out.println("*******************");


        //假设这里是页面提交的参数
        int id = 4;

        //1.获取Connection连接池
        Properties prop = new Properties();
        prop.load(new FileInputStream("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,增加用Update
        int count = pstmt.executeUpdate(); //返回的是影响的行数

        //6.处理结果
        System.out.println(count > 0);


        //7.释放资源

        pstmt.close();
        conn.close();
    }
}

第四条数据被删除