查询
查询在数据库的操作中是很重要的，我们把数据保存在数据库中，就是为了我们在需要的时候能够快速、高效的查询出来。
/** * 查询 * * @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 = 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
修改
在这里，我讲增删改作为一类，同属于对数据库的修改操作，在例子中，也会发现，我们使用的方法完全相同，仅仅是SQL的区别。
/** * 修改 * * @param userId * @param userName * @throws Exception */ public void update(String userId, String userName) throws Exception { Connection conn = getConnection(); 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
完整示例
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 = 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 = 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 = 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
受影响的记录条数为：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
