JDBC操作MySQL数据库案例
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 }
