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; } |
