第三十八篇：JAVA访问数据库之增删改查(CRUD)

上一篇博客介绍了如何使用Java连接数据库，那么这一篇将继续为大家介绍如何使用JDBC对数据库的增删改查(CRUD)操作。 
这一篇博客中的示例将使用上一篇中生成的H2数据库文件。

查询

查询在数据库的操作中是很重要的，我们把数据保存在数据库中，就是为了我们在需要的时候能够快速、高效的查询出来。

/**
    * 查询
    * 
    * @throws Exception
    */
   public void query() throws Exception
   {
      Connection conn = getConnection();
      Statement statement = conn.createStatement();
      ResultSet resultSet = statement
            .executeQuery("SELECT USER_ID, USER_NAME FROM USER_INFO");
      System.out.printf("%20s %50s\n", "USER_ID", "USER_NAME");
      // 遍历结果集
      while (resultSet.next())
      {
         System.out.printf("%20s %50s\n", resultSet.getString("USER_ID"),
               resultSet.getString("USER_NAME"));
      }
      resultSet.close();
      statement.close();
      conn.close();
   }

   /**
    * 通过用户编号查询
    * 
    * @param userId
    * @throws Exception
    */
   public void queryById(String userId) throws Exception
   {
      Connection conn = getConnection();
      // 动态参数使用PreparedStatement，使用Statement存在SQL注入风险
      PreparedStatement statement = conn
            .prepareStatement("SELECT USER_ID, USER_NAME FROM USER_INFO WHERE USER_ID = ?");
      statement.setString(1, userId);
      ResultSet resultSet = statement.executeQuery();
      System.out.printf("%20s %50s\n", "USER_ID", "USER_NAME");
      // 遍历结果集
      while (resultSet.next())
      {
         System.out.printf("%20s %50s\n", resultSet.getString("USER_ID"),
               resultSet.getString("USER_NAME"));
      }
      resultSet.close();
      statement.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
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

修改

在这里，我讲增删改作为一类，同属于对数据库的修改操作，在例子中，也会发现，我们使用的方法完全相同，仅仅是SQL的区别。

/**
    * 修改
    * 
    * @param userId
    * @param userName
    * @throws Exception
    */
   public void update(String userId, String userName) throws Exception
   {
      Connection conn = getConnection();
      // 动态参数使用PreparedStatement，使用Statement存在SQL注入风险
      PreparedStatement statement = conn
            .prepareStatement("UPDATE USER_INFO SET USER_NAME = ? WHERE USER_ID = ?");
      statement.setString(1, userName);
      statement.setString(2, userId);
      System.out.println("受影响的记录条数为：" + statement.executeUpdate());
      statement.close();
      conn.close();
   }

   /**
    * 清空
    */
   public void clear() throws Exception
   {
      Connection conn = getConnection();
      Statement statement = conn.createStatement();
      System.out.println("受影响的记录条数为："
            + statement.executeUpdate("DELETE USER_INFO"));
      statement.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
24
25
26
27
28
29
30
31
32
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

完整示例

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class JDBCCRUDemo
{

   /**
    * 获得数据库连接
    * 
    * @return
    * @throws Exception
    */
   public Connection getConnection() throws Exception
   {
      Class.forName("org.h2.Driver");
      return DriverManager.getConnection("jdbc:h2:h2.db", "test", "123");
   }

   /**
    * 查询
    * 
    * @throws Exception
    */
   public void query() throws Exception
   {
      Connection conn = getConnection();
      Statement statement = conn.createStatement();
      ResultSet resultSet = statement
            .executeQuery("SELECT USER_ID, USER_NAME FROM USER_INFO");
      System.out.printf("%20s %50s\n", "USER_ID", "USER_NAME");
      // 遍历结果集
      while (resultSet.next())
      {
         System.out.printf("%20s %50s\n", resultSet.getString("USER_ID"),
               resultSet.getString("USER_NAME"));
      }
      resultSet.close();
      statement.close();
      conn.close();
   }

   /**
    * 通过用户编号查询
    * 
    * @param userId
    * @throws Exception
    */
   public void queryById(String userId) throws Exception
   {
      Connection conn = getConnection();
      // 动态参数使用PreparedStatement，使用Statement存在SQL注入风险
      PreparedStatement statement = conn
            .prepareStatement("SELECT USER_ID, USER_NAME FROM USER_INFO WHERE USER_ID = ?");
      statement.setString(1, userId);
      ResultSet resultSet = statement.executeQuery();
      System.out.printf("%20s %50s\n", "USER_ID", "USER_NAME");
      // 遍历结果集
      while (resultSet.next())
      {
         System.out.printf("%20s %50s\n", resultSet.getString("USER_ID"),
               resultSet.getString("USER_NAME"));
      }
      resultSet.close();
      statement.close();
      conn.close();
   }

   /**
    * 添加
    * 
    * @param userId
    * @param userName
    * @throws Exception
    */
   public void insert(String userId, String userName) throws Exception
   {
      Connection conn = getConnection();
      // 动态参数使用PreparedStatement，使用Statement存在SQL注入风险
      PreparedStatement statement = conn
            .prepareStatement("INSERT INTO USER_INFO(USER_ID, USER_NAME) VALUES(?, ?)");
      statement.setString(1, userId);
      statement.setString(2, userName);
      System.out.println("受影响的记录条数为：" + statement.executeUpdate());
      statement.close();
      conn.close();
   }

   /**
    * 修改
    * 
    * @param userId
    * @param userName
    * @throws Exception
    */
   public void update(String userId, String userName) throws Exception
   {
      Connection conn = getConnection();
      // 动态参数使用PreparedStatement，使用Statement存在SQL注入风险
      PreparedStatement statement = conn
            .prepareStatement("UPDATE USER_INFO SET USER_NAME = ? WHERE USER_ID = ?");
      statement.setString(1, userName);
      statement.setString(2, userId);
      System.out.println("受影响的记录条数为：" + statement.executeUpdate());
      statement.close();
      conn.close();
   }

   /**
    * 清空
    */
   public void clear() throws Exception
   {
      Connection conn = getConnection();
      Statement statement = conn.createStatement();
      System.out.println("受影响的记录条数为："
            + statement.executeUpdate("DELETE USER_INFO"));
      statement.close();
      conn.close();
   }

   public static void main(String[] args) throws Exception
   {
      JDBCCRUDemo demo = new JDBCCRUDemo();
      demo.insert("id111", "name111");
      demo.insert("id222", "name222");
      demo.insert("id333", "name333");
      demo.insert("id444", "name444");
      demo.insert("id555", "name555");
      demo.query();
      demo.queryById("id444");
      demo.update("id222", "name1234");
      demo.query();
      demo.clear();
   }
}
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138

运行结果： 
受影响的记录条数为：1 
受影响的记录条数为：1 
受影响的记录条数为：1 
受影响的记录条数为：1 
受影响的记录条数为：1 
             USER_ID                                          USER_NAME 
               id111                                            name111 
               id222                                            name222 
               id333                                            name333 
               id444                                            name444 
               id555                                            name555 
             USER_ID                                          USER_NAME 
               id444                                            name444 
受影响的记录条数为：1 
             USER_ID                                          USER_NAME 
               id111                                            name111 
               id333                                            name333 
               id444                                            name444 
               id555                                            name555 
               id222                                           name1234 
受影响的记录条数为：5