JAVA-JDBC之prepareStatement和statement区别

prepareStatement 是预编译方式的创建对象时就会传递sql，可以很好的防止sql注入，可以理解为在sql内部进行替换

statement是字符串替换方式的，他会产生sql注入，比如输入query（”‘skd“，”+wwe“）这样的形式就会匹配到’‘两个’会造成sql语句错误

     

  public static void query(String username,String password) {

             Statement statement=null;

             Connection  connection=null;

             

             /*

              * 1、加载驱动

              * */

             String urlString="jdbc:mysql://localhost:3306/emp?serverTimezone=Asia/Shanghai";

             /*

              * 用statement 字符串拼接的的方式容易造成sql注入，比如输入query（”‘skd“，”+wwe“）这样的形式就会匹配到’‘两个’会造成sql语句错误

              * */

             String sqlString = "select id,username,password,status from emp where username='"+username+"'and password='"+password+"' ";

             System.out.println(sqlString);

             ResultSet rs=null;

             /*

              * 反射方式加载驱动

              * */

             

             try {

                    Class.forName("com.mysql.cj.jdbc.Driver");

                    try {

                          /*

                           * 创建连接

                           * */

                          connection = DriverManager.getConnection(urlString,"root","root");

                          statement = connection.createStatement();

                          /*

                           * 返回对象

                           * */

                          

                          rs=statement.executeQuery(sqlString);

                          

//                        while(rs.next()) {//移动到下一行

//                               int id= rs.getInt(1);//取出当前行的第一列

//                               String username1=rs.getString(2);//取出当前行的第二列，这里因为第一列是int型第二列是String型

//                               String password1=rs.getString(3);

//                               System.out.println(id);

//                               System.out.println(username1);

//                               System.out.println(password1);

//                        }

                          if(rs.next()) {//移动到下一行

                                 int id= rs.getInt(1);//取出当前行的第一列

                                 String username1=rs.getString(2);//取出当前行的第二列，这里因为第一列是int型第二列是String型

                                 String password1=rs.getString(3);

                                 System.out.println(id);

//                               System.out.println(username1);

//                               System.out.println(password1);

                                 System.out.println("登陆成功！");

                          }else {

                                 System.out.println("用户不存在或用户名密码错误！");

                          }

                          

                          

                          

                    } catch (SQLException e) {

                          // TODO Auto-generated catch block

                          e.printStackTrace();

                    }

             } catch (ClassNotFoundException e) {

                    // TODO Auto-generated catch block

                    e.printStackTrace();

             }finally {

//                  先创建后关闭

                    if(rs!=null) {

                          try {

                                 rs.close();

                          } catch (SQLException e) {

                                 // TODO Auto-generated catch block

                                 e.printStackTrace();

                          }

                    }

                    if(statement!=null) {

                          try {

                                 statement.close();

                          } catch (SQLException e) {

                                 // TODO Auto-generated catch block

                                 e.printStackTrace();

                          }

                    }

                    if(connection!=null) {

                          try {

                                 connection.close();

                          } catch (SQLException e) {

                                 // TODO Auto-generated catch block

                                 e.printStackTrace();

                          }

                    }

                    

             }

             

       }

       public static void query1(String username,String password) {

             PreparedStatement  pStatement=null;

             Connection  connection=null;

             

             /*

              * 1、加载驱动

              * */

             String urlString="jdbc:mysql://localhost:3306/emp?serverTimezone=Asia/Shanghai";

             /*

              * 用statement 字符串拼接的的方式容易造成sql注入，比如输入query（”‘skd“，”+wwe“）这样的形式就会匹配到’‘两个’会造成sql语句错误

              * */

             String sqlString = "select id,username,password,status from emp where username= ? and password= ? ";

             System.out.println(sqlString);

             ResultSet rs=null;

       

             try {

                    Class.forName("com.mysql.cj.jdbc.Driver");

                    try {

                          /*

                           * 创建连接

                           * */

                          connection = DriverManager.getConnection(urlString,"root","root");

                          /*

                           * 预编译sql，创建对象时就传递sql,拼接字符串方式不需要

                           * */

                          pStatement = connection.prepareStatement(sqlString);

//                        pStatement.setString(1, "lili");//在jdbc中所有的索引都是从1开始

//                        pStatement.setString(2, "123456");

                          pStatement.setString(1, username);//在jdbc中所有的索引都是从1开始,--1表示第一个问好？

                          pStatement.setString(2, password);//可以理解为在mysql内部进行替换，可以很好的防止sql注入

                          /*

                           * 返回对象

                           * */

                          rs=pStatement.executeQuery();

                          if(rs.next()) {//移动到下一行

                                 int id= rs.getInt(1);//取出当前行的第一列

                                 String username1=rs.getString(2);//取出当前行的第二列，这里因为第一列是int型第二列是String型

                                 String password1=rs.getString(3);

                                 System.out.println(id);

//                               System.out.println(username1);

//                               System.out.println(password1);

                                 System.out.println("登陆成功！");

                          }else {

                                 System.out.println("用户不存在或用户名密码错误！");

                          }

                          

                          

                          

                    } catch (SQLException e) {

                          // TODO Auto-generated catch block

                          e.printStackTrace();

                    }

             } catch (ClassNotFoundException e) {

                    // TODO Auto-generated catch block

                    e.printStackTrace();

             }finally {

//                  先创建后关闭

                    if(rs!=null) {

                          try {

                                 rs.close();

                          } catch (SQLException e) {

                                 // TODO Auto-generated catch block

                                 e.printStackTrace();

                          }

                    }

                    if(pStatement!=null) {

                          try {

                                 pStatement.close();

                          } catch (SQLException e) {

                                 // TODO Auto-generated catch block

                                 e.printStackTrace();

                          }

                    }

                    if(connection!=null) {

                          try {

                                 connection.close();

                          } catch (SQLException e) {

                                 // TODO Auto-generated catch block

                                 e.printStackTrace();

                          }

                    }

                    

             }

             

       }