JavaWeb——JDBC练习

 

 

 

查询数据

    //查询所有数据
    @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();
    }