02 Statement和PreparedStatement

文章目录

• • Statement
  • PreparedStatement

Statement

(1)相同的SQL语句， 重复执行第n次，编译n次 — 效率低
(2)Statement sql中的参数赋值 直接通过字符串拼接，可能会有非法sql注入，导致数据泄露

import java.sql.*;
import java.util.Scanner;

public class Login {
    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        System.out.print("请输入用户名:   ");
        String userName = sc.nextLine();
        System.out.println();
        System.out.print("请输入密码:   ");
        String userPwd = sc.nextLine();



        //连接
        Connection connection = null;
        //操作对象
        Statement statement = null;
        //结果
        ResultSet resultSet = null;

        try {

            //1注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取连接
            connection = DriverManager.getConnection("jdbc:mysql:///dict?useSSL = false","root","123456");
            //3创建对象
            statement = connection.createStatement();

            //4传入结果

            String sql = " select * from user where username ='"+userName+"' and password = '"+userPwd+"';";
            resultSet = statement.executeQuery(sql);
            if (resultSet.next()){
                System.out.println("登录成功");
            }else {
                System.out.println("登录失败");
            }




        } catch (ClassNotFoundException e)
        {
            e.printStackTrace();
            System.out.println("驱动未能找到");
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("sql出现问题");
        }
        finally {


            //6.关闭所有资源
            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }}
            if (null!=statement){
                try {
                    statement.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }

    }
}

PreparedStatement

(1)相同的SQL语句， 重复执行第n次，不需要重复编译 — 效率高
(2) PreparedStatement 可以有效防止sql注入 ， 通过?占位符给sql中的参数赋值，
数据类型严格匹配，sql语句组成不是字符串直接拼接

import java.sql.*;
import java.util.Scanner;


public class Login2 {


    /*
     * 登录
     * 1.username&password
     * 2.找个对象给他存起来
     * 3.将这个值放到sql中然后给statement执行
     *  select * from user_name = 'name' and user_pwd = 'pwd' or 1=1
     * 4.校验结果(ResultSet):去执行next()  true则成功 不然反之
     *
     *
     *
     *  "select user_name from user where user_name ='   ' and user_pwd = 'pwd' or '1'='1';";
     *
     * */

    public static void main(String[] args) {

        Scanner sc = new Scanner(System.in);
        System.out.print("请输入用户名:   ");
        String userName = sc.nextLine();
        System.out.println();
        System.out.print("请输入密码:   ");
        String userPwd = sc.nextLine();



        //连接
        Connection connection = null;
        //操作对象
        PreparedStatement statement = null;
        //结果
        ResultSet resultSet = null;

        //1.注册驱动
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取连接
            connection = DriverManager.getConnection("jdbc:mysql:///test?useSSL = false","root","root");
            //3.获取操作对象
            //3.1获取预编译对象
            //提前将sql写好并为关键值用?去占位
            statement = connection.prepareStatement("select * from user where username =? and password =?");
            //3.2根据位置放入关键值
            //位置还是从1开始
            statement.setObject(1,userName);
            statement.setObject(2,userPwd);

            //4.sql编写并执行
            //预编译对象直接执行,不需要传入sql,因为已经设置好了!!!
//            String sql = "select user_name from user where user_name ='"+userName+"' and user_pwd = '"+userPwd+"';";
            resultSet = statement.executeQuery();
            //5.解析结果
            /*
             * 判断resultset的next()
             * true 成功
             * false 失败
             * */
            if (resultSet.next()){
                System.out.println("登录成功");
            }else {
                System.out.println("登录失败");
            }

        } catch (ClassNotFoundException e)
        {
            e.printStackTrace();
            System.out.println("驱动未能找到");
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("sql出现问题");
        }
        finally {


            //6.关闭所有资源
            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }}
            if (null!=statement){
                try {
                    statement.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }


    }
}