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