一、任务简要描述
数据库编程：练习数据库编程。用MySQL数据库，建立一个数据库表，通过JDBC连接MySQL数据库，并对数据库进行增删改查等操作。
我做了数据库连接，插入数据、删除数据、修改数据、数据查询功能，通过输入密码查询，并解决了sql的注入问题。
Java数据库连接体系结构是用于Java应用程序连接数据库的标准方法。JDBC对Java程序员而言是API，对实现与数据库连接的服务提供商而言是接口模型。作为API，JDBC为程序开发提供标准的接口，并为数据库厂商及第三方中间件厂商实现与数据库的连接提供了标准方法。JDBC使用已有的SQL标准并支持与其它数据库连接标准，如ODBC之间的桥接。JDBC实现了所有这些面向标准的目标并且具有简单、严格类型定义且高性能实现的接口。
Java 具有坚固、安全、易于使用、易于理解和可从网络上自动下载等特性，是编写数据库应用程序的杰出语言。所需要的只是 Java应用程序与各种不同数据库之间进行对话的方法。而 JDBC 正是作为此种用途的机制。
二、问题及解决
1 import java.sql.Connection;
2 import java.sql.DriverManager;
3 import java.sql.PreparedStatement;
4 import java.sql.ResultSet;
5 import java.sql.SQLException;
6 import java.sql.Statement;
7
8
9
10 public class JDBCDemo01 {
11 public static void main(String[] args) {
12 selectAll();
13 //登录校验
14 selectByUsernamePassword("Micheal", "123");
15 selectByUP2("Micheal", "123");
16 //分页查询
17 selectUserByPage(3, 4);
18 insert("ky", "123456");
19 delete(3);
20 update(2, "123456");
21
22 }
23
24 public static void selectAll() {
25
26 Connection con = null;
27 Statement stmt = null;
28 ResultSet rs = null;
29
30 try {
31 con = JDBCUtils.getConnection();
32
33 stmt = con.createStatement();
34 rs = stmt.executeQuery("select * from user");
35
36 while(rs.next()) {
37 System.out.println(rs.getInt("id") + "," + rs.getString(rs.getString("username")) + "," + rs.getString("password"));
38
39 }
40
41 } catch (Exception e) {
42 // TODO Auto-generated catch block
43 e.printStackTrace();
44 } finally {
45 JDBCUtils.close(rs, stmt, con);
46 }
47 }
48
49 public static boolean selectByUsernamePassword(String username, String password) {
50 Statement stmt = null;
51 Connection con = null;
52 ResultSet rs = null;
53 try {
54 Class.forName("com.mysql.cj.jdbc.Driver");
55
56 String url = "jdbc:mysql://localhost:3306/web01?useUnicode = true & characterEncoding = UTF8 & useSSL = false";
57 con = DriverManager.getConnection(url, "root", "root");
58
59 stmt = con.createStatement();
60
61 String sql = "select * from user where username = '"+username+"' and password = '"+password+"'";
62 rs = stmt.executeQuery(sql);
63
64 if(rs.next()) {
65 return true;
66 } else {
67 return false;
68 }
69 } catch (Exception e) {
70 // TODO Auto-generated catch block
71 e.printStackTrace();
72 } finally {
73 try {
74 if(rs != null) rs.close();
75 } catch (SQLException e) {
76 // TODO Auto-generated catch block
77 e.printStackTrace();
78 }
79
80 try {
81 if(stmt != null) stmt.close();
82 } catch (SQLException e) {
83 // TODO Auto-generated catch block
84 e.printStackTrace();
85 }
86
87 try {
88 if(con != null) con.close();
89 } catch (SQLException e) {
90 // TODO Auto-generated catch block
91 e.printStackTrace();
92 }
93 }
94 return false;
95 }
96 //解决sql注入问题
97 public static boolean selectByUP2(String username, String password) {
98 Statement stmt = null;
99 Connection con = null;
100 ResultSet rs = null;
101 try {
102 Class.forName("com.mysql.cj.jdbc.Driver");
103
104 String url = "jdbc:mysql://localhost:3306/web01?useUnicode = true & characterEncoding = UTF8 & useSSL = false";
105 con = DriverManager.getConnection(url, "root", "root");
106
107 String sql = "select * from user where username = ? and password = ?";
108 PreparedStatement pstmt = con.prepareStatement(sql);
109
110 pstmt.setString(1, username);
111 pstmt.setString(2, password);
112
113 rs = pstmt.executeQuery();
114 if(rs.next()) return true;
115 else return false;
116
117 } catch (Exception e) {
118 // TODO Auto-generated catch block
119 e.printStackTrace();
120 } finally {
121 try {
122 if(rs != null) rs.close();
123 } catch (SQLException e) {
124 // TODO Auto-generated catch block
125 e.printStackTrace();
126 }
127
128 try {
129 if(stmt != null) stmt.close();
130 } catch (SQLException e) {
131 // TODO Auto-generated catch block
132 e.printStackTrace();
133 }
134
135 try {
136 if(con != null) con.close();
137 } catch (SQLException e) {
138 // TODO Auto-generated catch block
139 e.printStackTrace();
140 }
141 }
142 return false;
143 }
144 //pageNumber是页数，第几页pageCount是每页显示多少条数据
145 public static void selectUserByPage(int pageNumber, int pageCount) {
146 Connection con = null;
147 PreparedStatement stmt = null;
148 ResultSet rs = null;
149
150 try {
151 Class.forName("com.mysql.cj.jdbc.Driver");
152
153 //String url = "jdbc:mysql://localhost:3306/web01";
154 String url = "jdbc:mysql://localhost:3306/web01?useUnicode=true&characterEncoding=UTF8&useSSL=false&allowPublicKeyRetrieval=true";
155 String user = "root";
156 String password = "root";
157
158 con = DriverManager.getConnection(url, user, password);
159
160 stmt = con.prepareStatement("select * from user limit ?, ?");
161 stmt.setInt(1, (pageNumber - 1) * pageCount);
162 stmt.setInt(2, pageCount);
163
164 rs = stmt.executeQuery();
165
166 while(rs.next()) {
167 System.out.println(rs.getInt("id") + "," + rs.getString(rs.getString("username")) + "," + rs.getString("password"));
168
169 }
170
171
172
173 } catch (ClassNotFoundException e) {
174 // TODO Auto-generated catch block
175 e.printStackTrace();
176 }//使用什么驱动连接数据库
177 catch (SQLException e) {
178 // TODO Auto-generated catch block
179 e.printStackTrace();
180 } finally {
181 try {
182 if(rs != null) rs.close();
183 } catch (SQLException e) {
184 // TODO Auto-generated catch block
185 e.printStackTrace();
186 }
187
188 try {
189 if(stmt != null) stmt.close();
190 } catch (SQLException e) {
191 // TODO Auto-generated catch block
192 e.printStackTrace();
193 }
194
195 try {
196 if(con != null) con.close();
197 } catch (SQLException e) {
198 // TODO Auto-generated catch block
199 e.printStackTrace();
200 }
201 }
202 }
203
204 public static void insert(String username, String password) {
205 Connection con = null;
206 PreparedStatement stmt = null;
207 ResultSet rs = null;
208 try {
209 con = JDBCUtils.getConnection();
210
211 String sql = "insert into user(username, password) valuse(?, ?)";
212 stmt = con.prepareStatement(sql);
213 stmt.setString(1, username);
214 stmt.setString(2, password);
215
216 int result = stmt.executeUpdate(); //返回值代表受到影响的行数
217
218 } catch (Exception e) {
219 // TODO Auto-generated catch block
220 e.printStackTrace();
221 } finally {
222 JDBCUtils.close(rs, stmt, con);
223 }
224
225 }
226
227 public static void delete(int id) {
228 Connection con = null;
229 PreparedStatement stmt = null;
230 ResultSet rs = null;
231 try {
232 con = JDBCUtils.getConnection();
233
234 String sql = "delete from user where id = ?";
235 stmt = con.prepareStatement(sql);
236 stmt.setInt(1, id);
237
238 int result = stmt.executeUpdate(); //返回值代表受到影响的行数
239 if(result > 0) {
240 System.out.println("删除成功");
241 }else {
242 System.out.println("删除失败");
243 }
244
245 } catch (Exception e) {
246 // TODO Auto-generated catch block
247 e.printStackTrace();
248 } finally {
249 JDBCUtils.close(rs, stmt, con);
250 }
251
252 }
253
254
255 public static void update(int id, String newPassword) {
256 Connection con = null;
257 PreparedStatement stmt = null;
258 ResultSet rs = null;
259 try {
260 con = JDBCUtils.getConnection();
261
262 String sql = "update user set password = ? where id = ?";
263 stmt = con.prepareStatement(sql);
264 stmt.setString(1, newPassword);
265 stmt.setInt(2, id);
266
267 int result = stmt.executeUpdate(); //返回值代表受到影响的行数
268 if(result > 0) {
269 System.out.println("修改成功");
270 }else {
271 System.out.println("修改失败");
272 }
273
274 } catch (Exception e) {
275 // TODO Auto-generated catch block
276 e.printStackTrace();
277 } finally {
278 JDBCUtils.close(rs, stmt, con);
279 }
280
281 }
282 }
工具类用做数据库连接和关闭
1 package com.kuyan.jdbc01;
2
3 import java.sql.Connection;
4 import java.sql.DriverManager;
5 import java.sql.ResultSet;
6 import java.sql.SQLException;
7 import java.sql.Statement;
8
9 public class JDBCUtils {
10 private static final String connectionURL = "jdbc:mysql://localhost:3306/web01?useUnicode = true & characterEncoding = UTF8 & useSSL = false";
11 private static final String username = "root";
12 private static final String password = "root";
13
14 public static Connection getConnection() {
15 try {
16 Class.forName("com.mysql.cj.jdbc.Driver");
17
18 return DriverManager.getConnection(connectionURL, username, password);
19
20 } catch (Exception e) {
21 // TODO Auto-generated catch block
22 e.printStackTrace();
23 }
24
25 return null;
26 }
27
28 public static void close(ResultSet rs, Statement stmt, Connection con) {
29 try {
30 if(rs != null) rs.close();
31 } catch (SQLException e) {
32 // TODO Auto-generated catch block
33 e.printStackTrace();
34 }
35
36 try {
37 if(stmt != null) stmt.close();
38 } catch (SQLException e) {
39 // TODO Auto-generated catch block
40 e.printStackTrace();
41 }
42
43 try {
44 if(con != null) con.close();
45 } catch (SQLException e) {
46 // TODO Auto-generated catch block
47 e.printStackTrace();
48 }
49 }
50
51
52 }
总结：在数据库连接时报错了好几次，首先我下载的jdbc驱动jar文件与MySQL数据库版本不适用，我又重新下载。
另外，我使用的mysql版本是8.0.x，mysql-connector版本8.0.x
应该改为Class.forName("com.mysql.cj.jdbc.Driver");新版本，系统可以自动加载，不用书写也可以
老版本
url="jdbc:mysql://localhost:3306/databaseName"
新版本
1 url="jdbc:mysql://localhost:3306/databaseName?serverTimezone=GMT"
这是对时区的设置，不设置就抛错，同时这种写法会有警告，是要求你主动设置是否进行加密校验，即useSSL=false或者useSSL=true //url中的？之后的是属性设置
我先用sql语句在数据库中建数据库web01、建表user
但在用Java操作数据库时，由于权限设置问题一直报错，上网查找相关资料又询问同学，可能由于版本问题一直得不到解决办法。