java jdbc連接和使用
jdbc
導入驅動
//jar是已經打包好的class文件集，可以引用到其他工程中
//Build Path中add external jars導入
連接JDBC
1. 加載驅動
|
1
|
Class.from("com.mysql.jdbc.Driver"); |
創建連接
|
1
2
3
|
//導包使用 java.sql.*；String jdbc="jdbc:mysql://localhost:3306/student?user=root&password=&characterEncoding=utf-8";//student是表名Connection conn = DriverManager.getConnection(jdbc); |
2. 注意 數據庫打開之后一定要記得關。
|
1
|
conn.close(); |
1. 執行SQL語句 （創建表，插入，刪除，更新）
使用Statemant
|
1
2
|
Statemant st = conn.createStatemant(); int row = st.executeUpdate(sql語句);//不能做查詢操作。 |
使用PrepareStatement
可以使用？占位符來代替你需要傳遞的參數
|
1
2
3
4
5
6
7
8
9
|
String sql = "insert into " + TABLENAME+ "(name,subject,score) values(?,?,?)";PrepareStatement pt = conn.prepareStatement(sql); //給每一位占位符設置值,下標從1開始 pt.setString(1,score.getName()); pt.setString(2.score.getSubject());pt.setDouble(3,score.getScore()); //使用無參的方法pt.executeUpdate(); |
1.查詢操作
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
static List<Score> queryScore(Connection pconn, Score pScore) throws SQLException { ArrayList<Score> mlist = new ArrayList<>(); String sql = "select * from " + TABLENAME + " where name = ?"; PreparedStatement ps = pconn.prepareStatement(sql); ps.setString(1, pScore.getName()); ResultSet rs = ps.executeQuery(); while (rs.next()) { // 這里可以通過rs獲取所有結果 String subject = rs.getString("subject"); int id = rs.getInt("id"); double score = rs.getDouble("score"); mlist.add(new Score(id, pScore.getName(), subject, score)); } return mlist; } |
下面是一個小程序
|
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
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
|
//建立數據庫連接類public class DAO { // 放問數據庫的鏈接地址 static String jdbc = "jdbc:mysql://localhost:3306/student?user=root&password=&characterEncoding=utf-8"; // 打開鏈接 public static Connection connection() { // 使用JDBC的步驟 // 1. 加載JDBC驅動 try { // 類的全名 包名+類名 Class.forName("com.mysql.jdbc.Driver"); // 2. 連接數據庫 Connection conn = DriverManager.getConnection(jdbc); return conn; } catch (Exception e) { System.out.println("驅動加載失敗"); return null; } }}//分數類public class Score { String name; String id; String subject; double score; public Score(String name, String subject, double score) { super(); this.name = name; this.subject = subject; this.score = score; } @Override public String toString() { return "Score [name=" + name + ", id=" + id + ", subject=" + subject + ", score=" + score + "]"; } public Score(String name, String id, String subject, double score) { super(); this.name = name; this.id = id; this.subject = subject; this.score = score; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getId() { return id; } public void setId(String id) { this.id = id; } public String getSubject() { return subject; } public void setSubject(String subject) { this.subject = subject; } public double getScore() { return score; } public void setScore(double score) { this.score = score; }}//實現類public class Test { public static String TABLENAME = "score"; public static void main(String[] args) { try { Connection conn = DAO.connection(); if (conn != null) { System.out.println("鏈接上了"); // createTable(conn); // 插入一條記錄 // Score score = new Score("李四 ", "Android", 98); // System.out.println(addScore2(conn, score)); // deleteScore(conn, score); // updateScore(conn, score); List<Score> list = queryScoreByName(conn, "王五"); //queryAllScore(conn); for (Score score : list) { System.out.println(score); } conn.close(); } else { System.out.println("鏈接失敗 "); } } catch (SQLException e) { e.printStackTrace(); } } // 創建一張表 public static boolean createTable(Connection conn) { // 開始執行sql語句 String sql = "create table " + TABLENAME + "(id integer primary key auto_increment,name varchar(3) not null,subject varchar(20) not null,score double)"; // 要執行一條語句，需要一個執行的類 Statement try { Statement st = conn.createStatement(); int result = st.executeUpdate(sql); System.out.println(result); if (result != -1) return true; } catch (SQLException e) { e.printStackTrace(); } return false; } // 添加一條記錄 public static boolean addScore(Connection conn, Score score) throws SQLException { String sql = "insert into " + TABLENAME + "(name,subject,score) values('" + score.getName() + "','" + score.getSubject() + "'," + score.getScore() + ")"; System.out.println(sql); Statement st = conn.createStatement(); int row = st.executeUpdate(sql); if (row > 0) return true; return false; } // 添加一條記錄2 public static boolean addScore2(Connection conn, Score score) throws SQLException { // 占位符?來代替需要設置的參數 String sql = "insert into " + TABLENAME + "(name,subject,score) values(?,?,?)"; PreparedStatement ps = conn.prepareStatement(sql); // 必須給定？所代表的值 ps.setString(1, score.getName()); ps.setString(2, score.getSubject()); ps.setDouble(3, score.getScore()); // 調用無參的方法 int row = ps.executeUpdate(); if (row > 0) return true; return false; } public static boolean deleteScore(Connection conn, Score score) throws SQLException { String sql = "delete from " + TABLENAME + " where name=? and subject=?"; // 創建PrepareStatement PreparedStatement ps = conn.prepareStatement(sql); ps.setString(1, score.getName()); ps.setString(2, score.getSubject()); // ps.setDouble(3, score.getScore()); // 執行 int row = ps.executeUpdate(); System.out.println(row); if (row > 0) return true; return false; } public static boolean updateScore(Connection conn, Score score) throws SQLException { // 修改 score人他的科目的成績 String sql = "update " + TABLENAME + " set score=? where name=? and subject=?"; PreparedStatement ps = conn.prepareStatement(sql); ps.setDouble(1, score.getScore()); ps.setString(2, score.getName()); ps.setString(3, score.getSubject()); int row = ps.executeUpdate(); System.out.println(row); if (row > 0) return true; return false; } public static List<Score> queryAllScore(Connection conn) throws SQLException { String sql = "select * from " + TABLENAME; // 開始查詢 Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(sql); List<Score> list = new ArrayList<Score>(); while (rs.next()) { // 這里可以通過rs獲取所有結果 String id = rs.getString("id"); String name = rs.getString("name"); String subject = rs.getString("subject"); double score = rs.getDouble("score"); list.add(new Score(name, id, subject, score)); } // 結束 return list; } public static List<Score> queryScoreByName(Connection conn, String name) throws SQLException { String sql = "select * from " + TABLENAME + " where name=?"; PreparedStatement pt = conn.prepareStatement(sql); pt.setString(1, name); ResultSet rs = pt.executeQuery(); List<Score> list = new ArrayList<>(); while (rs.next()) { String subject = rs.getString("subject"); String id = rs.getString("id"); double score = rs.getDouble("score"); list.add(new Score(name, id, subject, score)); } return list; } |
