我们前面已经连接到数据库之后，就要对数据库进行进一步的操作，最简单的便是增删改查操作

1.查

 public void QueeyAll() throws Exception{
        Class.forName(driver);
        conn = DriverManager.getConnection(url,user,password);
        stmt = conn.createStatement();

        String sql = "select * from product";
        result = stmt.executeQuery(sql);

        while(result.next()){
            int proid = result.getInt("proid");
            String proname = result.getString("proname");
            String proflag = result.getString("proflag");
            double procost = result.getDouble("procost");
            System.out.print(proid + "\t");
            System.out.print(proname + "\t");
            System.out.print(proflag + "\t");
            System.out.print(procost + "\n");
        }

        result.close();
        stmt.close();
        conn.close();
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

我们通过定义一个结果集返回数据库中的数据。

2.增

public int InsertPro(int proid,String proname,double procost,String proflag) throws Exception{
        Class.forName(driver);
        conn = DriverManager.getConnection(url,user,password);
        stmt = conn.createStatement();

        String sql = "insert into product (proid,proname,procost,proflag) values(null,?,?,?)";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);

        preparedStatement.setString(1,proname);
        preparedStatement.setDouble(2,procost);
        preparedStatement.setString(3,proflag);

        int i = preparedStatement.executeUpdate();
        System.out.println(i);
        return i;
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

通过定义受影响行数判断是否插入成功

3.删

public int DeleteByID(int proID) throws Exception{

        Class.forName(driver);
        conn = DriverManager.getConnection(url,user,password);

        String sql = "delete from product where proid = ?";

        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setInt(1,proID);

        //输出受影响行数判断删除是否成功
        int i = preparedStatement.executeUpdate();
        System.out.println(i);

        preparedStatement.close();
        conn.close();

        return i;
    }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

删除操作跟增加是一样的，也是通过定义受影响行数判断是否删除成功

4.改

public int UpdatePro(int proid,String proname,double procost,String proflag) throws Exception{
        Class.forName(driver);
        conn = DriverManager.getConnection(url,user,password);
        stmt = conn.createStatement();

        String sql = "update product set proname = ? , procost = ?  ,proflag = ? where proid = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);

        preparedStatement.setString(1, proname);
        preparedStatement.setDouble(2, procost);
        preparedStatement.setString(3, proflag);
        preparedStatement.setInt(4, proid);

        int i = preparedStatement.executeUpdate();
        System.out.println(i);

        return i;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

5.小结

需要注意参数索引的顺序要一一对应