jdbc day02

DML語法

• 比起插敘語句,沒有ResultSet結果集,stmt.executeUpdate(sql)返回的是改變幾行的int類型.

前幾步和day01一致
//3.寫sql語句
        String sql = "insert into copy_emp(last_name) values("+"'洛哥哥')";
        //4.創建發送對象
        Statement stmt = conn.createStatement();
        stmt.executeUpdate(sql);
        //7.關閉資源

SQL註入

sql註入就是用戶輸入的值,當做了sql語言的一部分,然後攻破密碼等重要信息.
為了防止sql註入,就要將含有單引號的採用預編譯處理.

比如 :

String USERNAME = "admin";
String PASSWORD = "123 OR '1' = '1"; (sql註入,使破解後的密碼恆成立)

列印輸入密碼之後的sql語言

select count(*) from login where USERNAME= 'admin' and PASSWORD= '123' OR '1'='1';

預編譯

• 採用?占位符, 將需要輸入單引號的sal語法用?表示.
• 書寫簡單,而且防止sql註入問題.
• PreparedStatement繼承Statement.
• 預編譯中的問號,pre.setString(1,"哈哈");
  1代表第一個問號,不代表第一個參數.

預編譯中和普通編譯區別在於:

普通編譯:

    //4.創建發送對象
    Statement stmt = conn.createStatement();
    //5.發送sql語句
    ResultSet rs = stmt.executeQuery(sql);

預編譯

    //4.創建預編譯對象
    PreparedStatement pstmt = conn.prepareStatement(sql);
    //5.填入數據
    pstmt.setString(1, myUsername);
    pstmt.setString(2, myPassword);
    //6.創建結果集
    ResultSet rs = pstmt.executeQuery();

        前兩步與day01一致,查看用戶輸入的用戶名和密碼是否是已經存在資料庫的
        String myUsername = "admin";
        String myPassword = "admin";
        //3.寫sql語句(判斷輸入的用戶名密碼在資料庫中有幾條記錄)
        String sql = "select count(*) count from login1 where username = ? and password = ?";
        //4.創建預編譯對象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        //5.填入數據
        pstmt.setString(1, myUsername);
        pstmt.setString(2, myPassword);
        //6.創建結果集
        ResultSet rs = pstmt.executeQuery();
        //7.查詢select結果
        if(rs.next()) {
            String flag = rs.getString("count");
            if("1".equals(flag)) {
                System.out.println("登錄成功");
            } else {
                System.out.println("登錄失敗");
            }
        }
        //8.關閉資源
        

DBUtil

• 將屬性和方法設置為static靜態,方便以後直接用類名調用.

public class DBUtil {
    private final static String DriverName = "oracle.jdbc.OracleDriver";
    private final static String URL = "jdbc:oracle:thin:@localhost:1521:orcl";
    private final static String USERNAME = "scott";
    private final static String PASSWORD = "luogg";
    private static Connection conn ;
    private static Statement stmt ;
    //private static PreparedStatement pstmt;
    
    /**
     * 連接資料庫方法
     * @return Connection
     * @param conn 資料庫連接對象
     */
    
    public static Connection getConnection() {
        try {
            Class.forName(DriverName);
            conn = DriverManager.getConnection(URL,USERNAME,PASSWORD);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return conn;
    }
    
    /**
     * 查詢語句關閉
     * @param rs 結果集
     * @param stmt 發送對象
     * @param conn 連接對象
     */
    public static void close(ResultSet rs,Statement stmt,Connection conn) {
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if(stmt != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if(conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
    
    /**
     * 非查詢語句關閉資源
     * @param stmt 發送對象
     * @param conn 連接對象
     */
    public static void close(Statement stmt,Connection conn) {
        close(null,stmt,conn);
    }
    
    /**
     * 查詢語句
     * @param sql
     * @return ResultSet
     */
    public static ResultSet selectSql(String sql) {
        ResultSet rs = null;
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return rs;
    }
    
    /**
     * 增刪改的SQL語句
     * @param sql
     * @return
     */
    public static int runSql(String sql) {
        int result = 0;
        try {
            Statement stmt = conn.createStatement();
            result = stmt.executeUpdate(sql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return result;
    }