JDBC操作MySQL数据库案例

JDBC操作MySQL数据库案例

 import java.sql.Connection;

 import java.sql.DriverManager;

 import java.sql.PreparedStatement;

 import java.sql.ResultSet;

 import java.sql.Statement;

 import org.junit.Test;

 public class JDBCTest {

     @Test

     public void test() throws Exception {

         Connection con = null;//定义引用

         Statement stmt = null;

         ResultSet rs = null;

         //规范的代码格式，try catch finally

         try {

             String driverClassName = "com.mysql.jdbc.Driver";

             String url = "jdbc:mysql://localhost:3306/exam";

             String username = "root";

             String password = "123";

             Class.forName(driverClassName);                                //加载

             con = DriverManager.getConnection(url, username, password);    //连接

             stmt = con.createStatement();                                //可以理解为创建SQL语句发射器

             //executeUpdate方法，可以执行增删改语句（INSERT, UPDATE, DELETE），返回被改变的记录条数

             String sql="DELETE FROM stu";

             int r = stmt.executeUpdate(sql);

             System.out.println("共删除了"+r+"条记录！");

             //executeQuery方法，用于执行查询操作（SELECT），返回结果集

             String sql2="select * from emp";

             rs = stmt.executeQuery(sql2);

             while(rs.next()) {                            //把光标向下移动一行，并判断下一行是否存在！

                 int empno = rs.getInt(1);                //通过列编号来获取该列的值！

                 String ename = rs.getString("ename");    //通过列名称来获取该列的值

                 double sal = rs.getDouble("sal");

                 System.out.println(empno +  ", " + ename + ", " + sal);

             }

         } catch(Exception e) {

             throw new RuntimeException(e);

         } finally {

             // 一定要关闭！！！！！！

             if(rs != null) rs.close();

             if(stmt != null) stmt.close();

             if(con != null) con.close();

         }

     }

     @Test

     /**

      * 预处理方式

      * 优点：灵活，效率高，防SQL攻击

      * SQL攻击例子：

      *   若：sql = "select * from t_user where username='" + username + "' and password='" + password + "'";

      *   username = "a' or 'a'='a";

      *     password = "a' or 'a'='a";

      *     最后拼成的语句为：

      *  select * from t_user where username='a' or 'a'='a" and password='a' or 'a'='a'

      *  永远为true。

      */

     public void test2() throws Exception {

         Connection con = null;//定义引用

         ResultSet rs = null;

         PreparedStatement pstmt=null;

         try {

             String driverClassName = "com.mysql.jdbc.Driver";

             //mysql默认预处理是关闭的，加上这两个参数之后可以开启预处理

             String url = "jdbc:mysql://localhost:3306/exam?useServerPrepStmts=true&cachePrepStmts=true";

             String username = "root";

             String password = "123";

             Class.forName(driverClassName);

             con = DriverManager.getConnection(url, username, password);

             String sql="select * from emp where empno=? and job=?";

             pstmt = con.prepareStatement(sql);

             pstmt.setInt(1, 1001);

             pstmt.setString(2, "文员");

             rs =pstmt.executeQuery();

             if(rs.next())

             {

                 System.out.println(rs.getString("ename"));

             }

         } catch(Exception e) {

             throw new RuntimeException(e);

         } finally {

             if(rs != null) rs.close();

             if(pstmt != null) pstmt.close();

             if(con != null) con.close();

         }

     }

 }