package cn.code.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

/**
 * PreparedStatement 
 * 他是statement接口的子接口
 * 强大之处:
 * 1、防sql攻击
 * 2、提高代码的可读性、可维护性
 * 3、提高效率
 * PreparedStatement用法:
 * */

public class NumberFive {
    /**
     * 防sql攻击
     * @throws ClassNotFoundException 
     * */
    //登录校验,查看用户名密码是否正确
    public boolean findByUser(String username,String password) throws ClassNotFoundException{
        String url="jdbc:mysql://localhost:3306/mydb1";
        String driverclassname="com.mysql.jdbc.Driver";
        String mysqlusername = "root";
        String mysqlpassword ="123";
        Connection con =null;
        PreparedStatement ps =null;
        ResultSet rs=null;
        Class.forName(driverclassname);
        try{
            con= DriverManager.getConnection(url, mysqlusername, mysqlpassword);
            //给出sql模板
            String sql="select * from t_user where username=? and password=?";
            //获取preparedStatement,并将sql模板传给它
            ps = con.prepareStatement(sql);
            //为参数赋值
            ps.setString(1, username);
            ps.setString(2, password);
            //执行sql,因为已经把sql语句给了preparedStatement,所以不用再给;
            rs = ps.executeQuery();
            return rs.next();//根据查询结果,返回下一行是否有数据,如果有数据证明用户名密码正确;
        }catch(SQLException e){
            throw new RuntimeException(e);
        }
        finally{
            if(rs!=null)
                try {
                    rs.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            if(ps!=null)
                try {
                    ps.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            if(con!=null)
                try {
                    con.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
        }
    }
    @Test
    public void login() throws ClassNotFoundException{
//        String username="lisi";
//        String password="123";
        String username="a' or 'a'='a";
        String password="a' or 'a'='a";
//        Boolean b = findByUser(username, password);
        Boolean b = findByUser2(username, password);
        System.out.println(b);
    }
    //没有解决sql攻击的方法findByUser2
    public boolean findByUser2(String username,String password) throws ClassNotFoundException{
        String url="jdbc:mysql://localhost:3306/mydb1";
        String driverclassname="com.mysql.jdbc.Driver";
        String mysqlusername = "root";
        String mysqlpassword ="123";
        Connection con =null;
        Statement s =null;
        ResultSet rs=null;
        Class.forName(driverclassname);
        try{
            con= DriverManager.getConnection(url, mysqlusername, mysqlpassword);
            String sql="select * from t_user where username='"+username+"' and password='"+password+"'";
            System.out.println(sql);
            s = con.createStatement();
            rs = s.executeQuery(sql);
            return rs.next();
        }catch(SQLException e){
            throw new RuntimeException(e);
        }
        finally{
            if(rs!=null)
                try {
                    rs.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            if(s!=null)
                try {
                    s.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            if(con!=null)
                try {
                    con.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
        }
    }
}