JDBC操作MySQL数据库案例

1 import java.sql.Connection;
2 import java.sql.DriverManager;
3 import java.sql.PreparedStatement;
4 import java.sql.ResultSet;
5 import java.sql.Statement;
6
7 import org.junit.Test;
8
9 public class JDBCTest {
10 &＃64;Test
11 public void test() throws Exception {
12 Connection con &＃61; null;//定义引用
13 Statement stmt &＃61; null;
14 ResultSet rs &＃61; null;
15 //规范的代码格式&＃xff0c;try catch finally
16 try {
17 String driverClassName &＃61; "com.mysql.jdbc.Driver";
18 String url &＃61; "jdbc:mysql://localhost:3306/exam";
19 String username &＃61; "root";
20 String password &＃61; "123";
21
22 Class.forName(driverClassName); //加载
23 con &＃61; DriverManager.getConnection(url, username, password); //连接
24 stmt &＃61; con.createStatement(); //可以理解为创建SQL语句发射器
25
26 //executeUpdate方法&＃xff0c;可以执行增删改语句&＃xff08;INSERT, UPDATE, DELETE&＃xff09;&＃xff0c;返回被改变的记录条数
27 String sql&＃61;"DELETE FROM stu";
28 int r &＃61; stmt.executeUpdate(sql);
29 System.out.println("共删除了"&＃43;r&＃43;"条记录&＃xff01;");
30
31 //executeQuery方法&＃xff0c;用于执行查询操作&＃xff08;SELECT&＃xff09;&＃xff0c;返回结果集
32 String sql2&＃61;"select * from emp";
33 rs &＃61; stmt.executeQuery(sql2);
34 while(rs.next()) { //把光标向下移动一行&＃xff0c;并判断下一行是否存在&＃xff01;
35 int empno &＃61; rs.getInt(1); //通过列编号来获取该列的值&＃xff01;
36 String ename &＃61; rs.getString("ename"); //通过列名称来获取该列的值
37 double sal &＃61; rs.getDouble("sal");
38 System.out.println(empno &＃43; ", " &＃43; ename &＃43; ", " &＃43; sal);
39 }
40 } catch(Exception e) {
41 throw new RuntimeException(e);
42 } finally {
43 // 一定要关闭&＃xff01;&＃xff01;&＃xff01;&＃xff01;&＃xff01;&＃xff01;
44 if(rs !&＃61; null) rs.close();
45 if(stmt !&＃61; null) stmt.close();
46 if(con !&＃61; null) con.close();
47 }
48 }
49 &＃64;Test
50 /**
51 * 预处理方式
52 * 优点&＃xff1a;灵活&＃xff0c;效率高&＃xff0c;防SQL攻击
53 * SQL攻击例子&＃xff1a;
54 * 若&＃xff1a;sql &＃61; "select * from t_user where username&＃61;&＃39;" &＃43; username &＃43; "&＃39; and password&＃61;&＃39;" &＃43; password &＃43; "&＃39;";
55 * username &＃61; "a&＃39; or &＃39;a&＃39;&＃61;&＃39;a";
56 * password &＃61; "a&＃39; or &＃39;a&＃39;&＃61;&＃39;a";
57 * 最后拼成的语句为&＃xff1a;
58 * select * from t_user where username&＃61;&＃39;a&＃39; or &＃39;a&＃39;&＃61;&＃39;a" and password&＃61;&＃39;a&＃39; or &＃39;a&＃39;&＃61;&＃39;a&＃39;
59 * 永远为true。
60 */
61 public void test2() throws Exception {
62 Connection con &＃61; null;//定义引用
63 ResultSet rs &＃61; null;
64 PreparedStatement pstmt&＃61;null;
65 try {
66 String driverClassName &＃61; "com.mysql.jdbc.Driver";
67 //mysql默认预处理是关闭的&＃xff0c;加上这两个参数之后可以开启预处理
68 String url &＃61; "jdbc:mysql://localhost:3306/exam?useServerPrepStmts&＃61;true&cachePrepStmts&＃61;true";
69 String username &＃61; "root";
70 String password &＃61; "123";
71 Class.forName(driverClassName);
72 con &＃61; DriverManager.getConnection(url, username, password);
73
74 String sql&＃61;"select * from emp where empno&＃61;? and job&＃61;?";
75 pstmt &＃61; con.prepareStatement(sql);
76 pstmt.setInt(1, 1001);
77 pstmt.setString(2, "文员");
78 rs &＃61;pstmt.executeQuery();
79 if(rs.next())
80 {
81 System.out.println(rs.getString("ename"));
82 }
83 } catch(Exception e) {
84 throw new RuntimeException(e);
85 } finally {
86 if(rs !&＃61; null) rs.close();
87 if(pstmt !&＃61; null) pstmt.close();
88 if(con !&＃61; null) con.close();
89 }
90 }
91 }