使用statement语句实现多条件查询_[22]-JDBC PreparedStatement的使用

最新推荐文章于 2022-09-01 16:25:36 发布

weixin_39996134

最新推荐文章于 2022-09-01 16:25:36 发布

阅读量903

点赞数

文章标签：使用statement语句实现多条件查询

本文链接：https://blog.csdn.net/weixin_39996134/article/details/111384507

版权

讲PreparedStatement的时候就要讲一下sql注入

所谓 SQL 注入，就是通过把含有 SQL 语句片段的参数插入到需要执行的 SQL 语句中，最终达到欺骗数据库服务器执行恶意操作的 SQL 命令。

案例

//sql注入
	public void sqlInject(String departmentName,int locationId){
		Connection conn = null;
		Statement state = null;
		ResultSet rs = null;
		try{
			conn = JdbcUtil.getConnection();
	state = conn.createStatement();
	String sql = "select * from departments where department_name ='"+departmentName+"' and location_id = "+locationId;
	System.out.println(sql);
	rs = state.executeQuery(sql);
	while(rs.next()){
	System.out.println(rs.getInt("department_id")+" "+rs.getString("department_name")+" "+rs.getInt("location_id"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JdbcUtil.closeResource(state, conn, rs);
		}
	}
	public static void main(String[] args) {

		test.sqlInject("研发部' or 1=1 -- ", 8);
	}

因为Statement不具备预编译的能力,所以发送的sql语句就成了下面这一句

select * from departments where department_ name ='研发部' or 1=1 -- ' and location_ _id = 8

可以看到通过注释 -- 后面的内容被注释掉了,正真发送的内容就只有下面这一段生效

select * from departments where department_ name ='研发部' or 1=1

所以所有的数据都被查了出来,由此看出预编译的使用就特别重要

如果使用JDBC做登录操作,那么用下面的代码就会百分百被SQL注入

package com.lin.liang;

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

/**
 * 模拟登陆
 */
public class JDBClogin {
    public static void main(String[] args) {
//  从键盘获取输入信息
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名");
        String name1  = sc.next();
        System.out.println("请输入用户密码");
        String pwd1 = sc.next();

//  声明连接
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet =null;
        try {
//  加载驱动
            Class.forName("com.mysql.jdbc.Driver");
//  连接数据库
            String url ="jdbc:mysql://127.0.0.1:3306/slayer";
            String user="root";
            String pwd ="root";
            connection = DriverManager.getConnection(url,user,pwd);
//  获取Statement 发送器
            statement = connection.createStatement();
//  发送SQL 处理结果集
            String sql = "select * from  test1 where name = '"+name1+"' and pwd ='"+pwd1+"'";
             resultSet = statement.executeQuery(sql);
//  处理结果集
            if (resultSet.next()){
                System.out.println("登陆成功");
            }else{
                System.out.println("登陆失败,请检查帐号密码");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement !=null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection !=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }
}

PreparedStatement 对象的使用(重点)

PreparedStatement 特点：

• PreparedStatement 接口继承 Statement 接口

• PreparedStatement 效率高于 Statement

• PreparedStatement 支持动态绑定参数

• PreparedStatement 具备 SQL 语句预编译能力

• 使用 PreparedStatement 可防止出现 SQL 注入问题

Statement 和 PreparedStatement之间的区别区别:

Statement:

缺点: 存在sql注入风险 , 拼接麻烦, 可读性差

PreparedStatement:

优点:相对安全，效率相对较高 : 提高某些SQL代码复用性

缺点:创建麻烦一点点

重点代码:

    String sql ="select * from test1 where name=? and pwd=?";
            ps =connection.prepareStatement(sql);
            ps.setString(1,name);
            ps.setString(2,pwd);
//查询
            resultSet = ps.executeQuery();

具体实现代码

package com.lin.liang;

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

public class JDBCpreparedStatement {
    public static void main(String[] args) {
//  实现键盘录入
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名");
        String name  = sc.next();
        System.out.println("请输入用户密码");
        String pwd = sc.next();


//  声明连接
        Connection connection = null;
        PreparedStatement ps =null;
        ResultSet resultSet =null;
        try {
//  加载驱动
            Class.forName("com.mysql.jdbc.Driver");
//  连接数据
            String  rul = "jdbc:mysql://127.0.0.1:3306/slayer";
            String  user = "root";
            String  password  = "root";
            connection = DriverManager.getConnection(rul,user,password);
//  获取PrepareStatement 发送器
            String sql ="select * from test1 where name=? and pwd=?";
            ps =connection.prepareStatement(sql);
            ps.setString(1,name);
            ps.setString(2,pwd);
//查询
            resultSet = ps.executeQuery();
//处理结果集
            if (resultSet.next()){//next方法本身就是布尔返回形
                System.out.println("登陆成功");
            }else {
                System.out.println("登陆失败,请核对账号密码");
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps !=null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection !=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }


    }
}

通过PreparedStatement对象完成数据的新增

/**
 * PreparedStatement 对象的使用
 */
public class PreparedStatementDemo {
    //新增数据
    public void insertDepartments(String departmentsName,int locationID){
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = jdbcUtil.getConnection();
            preparedStatement=connection.prepareStatement("insert into departments values(default,?,?)");

            preparedStatement.setString(1,departmentsName);
            preparedStatement.setInt(2,locationID);
            boolean flag = preparedStatement.execute();

            if (flag != true){
                System.out.println("添加成功");
            }else {
                System.out.println("添加失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.closeResource(preparedStatement, connection, null);
        }
    }

    public static void main(String[] args) {
        PreparedStatementDemo psDemo = new PreparedStatementDemo();
     psDemo.insertDepartments("公关部",12);
    
    }

}

PreparedStatement对象的批处理操作

批处理:在与数据库的一次连接中，批量的执行条SQL语句。

package com.lin;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * PreparedStatement 对象的使用
 */
public class PreparedStatementDemo {

    //批量添加数据
    public void addBath(List<Departments> list){
        Connection connection = null;
        PreparedStatement ps = null;

        try {
            connection = jdbcUtil.getConnection();
            ps = connection.prepareStatement("insert into departments values (default ,?,?)");
            for (int i = 0; i <list.size() ; i++) {
                ps.setString(1,list.get(i).getDepartment_name());
                ps.setInt(2,list.get(i).getLocation_id());
                //添加批处理
                ps.addBatch();
            }
            int[] ints = ps.executeBatch();
            System.out.println(ints);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.closeResource(ps, connection,null);
        }
    }

   
    public static void main(String[] args) {
        PreparedStatementDemo psDemo = new PreparedStatementDemo();
  
        ArrayList<Departments> list = new ArrayList<>();
        for (int i = 0; i <=10 ; i++) {
            Departments dept= new Departments();
            dept.setDepartment_name("测试部"+i);
            dept.setLocation_id(10+i);
            list.add(dept);
        }
        psDemo.addBath(list);

   

    }

}

通过PreparedStatement对象完成数据的查询

这里我们不选择在控制台打印,而是做一个模型对象

package com.lin;

/**
 * 数据模型
 * 用来存放数据库中Departments表中的数据
 */
public class Departments {
    private Integer department_id;
    private String department_name;
    private int location_id;

    public Integer getDepartment_id() {
        return department_id;
    }

    public void setDepartment_id(Integer department_id) {
        this.department_id = department_id;
    }

    public String getDepartment_name() {
        return department_name;
    }

    public void setDepartment_name(String department_name) {
        this.department_name = department_name;
    }

    public int getLocation_id() {
        return location_id;
    }

    public void setLocation_id(int location_id) {
        this.location_id = location_id;
    }

    @Override
    public String toString() {
        return "Departments{" +
                "department_id=" + department_id +
                ", department_name='" + department_name + ''' +
                ", location_id=" + location_id +
                '}';
    }
}

查询一挑数据

/** 
 * PreparedStatement 对象的使用
 */
public class PreparedStatementDemo {
   

    //查询数据
    public Departments selectDepartmentsById(int departmentId){
        Connection connection =null;
        PreparedStatement ps = null;
        ResultSet resultSet=null;
        Departments dept = null;

        try {
            connection = jdbcUtil.getConnection();
            ps = connection.prepareStatement("select * from departments where department_id =?");
            ps.setInt(1,departmentId);
            resultSet = ps.executeQuery();
            while (resultSet.next()){
                 dept = new Departments();
                 dept.setDepartment_id(resultSet.getInt("department_id"));
                 dept.setDepartment_name(resultSet.getString("department_name"));
                 dept.setLocation_id(resultSet.getInt("location_id"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.closeResource(ps, connection, resultSet);
        }
        return dept;
    }

    public static void main(String[] args) {
        PreparedStatementDemo psDemo = new PreparedStatementDemo();
        Departments dept = psDemo.selectDepartmentsById(1);
        if (dept != null){
            System.out.println(dept.toString());
        }
    }

}

查询多条数据

package com.lin;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * PreparedStatement 对象的使用
 */
public class PreparedStatementDemo {
  
    //查询数据 查询多条数据
    public List<Departments> selectDepartmentByLikeName(String departmentName){
       Connection connection = null;
       PreparedStatement ps = null;
       ResultSet rs = null;
       List<Departments> list = new ArrayList<>();

        try {
            connection = jdbcUtil.getConnection();
            ps = connection.prepareStatement(
                         "select * from departments where department_name like ?");
            ps.setString(1,"%"+departmentName+"%");
            rs=ps.executeQuery();
            while (rs.next()){
                Departments departments = new Departments();
                departments.setDepartment_id(rs.getInt("department_id"));
                departments.setDepartment_name(rs.getString("department_name"));
                departments.setLocation_id(rs.getInt("location_id"));
                list.add(departments);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.closeResource(ps, connection,rs);
        }

        return list;
    }

  
    public static void main(String[] args) {
        PreparedStatementDemo psDemo = new PreparedStatementDemo();
 
        List<Departments> listDept = psDemo.selectDepartmentByLikeName("培训");
        for (Departments departments : listDept) {
            System.out.println(departments.toString());
        }

    }

}

通过PreparedStatement对象完成数据的更新

/**
 * PreparedStatement 对象的使用
 */
public class PreparedStatementDemo {
   

    //更新数据
    public void updateDepartment(int department_id,String departmentName,int locationID){
        Connection connection = null;
        PreparedStatement ps = null;

        try {
            connection = jdbcUtil.getConnection();
            ps = connection.prepareStatement("update departments set department_name =?,location_id=? where department_id=?");
            ps.setString(1,departmentName);
            ps.setInt(2,locationID);
            ps.setInt(3,department_id);
            boolean execute = ps.execute();
            if (execute !=true){
                System.out.println("更新成功");
            }else{
                System.out.println("更新失败");
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.closeResource(ps,connection,null);
        }


    }
    public static void main(String[] args) {

     psDemo.updateDepartment(24,"培训部",8);
      
    }

}

PreparedStatement对象的删除操作

package com.lin;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * PreparedStatement 对象的使用
 */
public class PreparedStatementDemo {
   
    //事务处理 - 删除
    public void deleteDepartments(String departmentName){
        Connection connection = null;
        PreparedStatement ps = null;

        try {
            connection  =  jdbcUtil.getConnection();
            //关闭事务的自动提交 ,默认是true 自动提交
            connection.setAutoCommit(false);
            ps =connection.prepareStatement("delete from departments where department_name like ?");
            ps.setString(1,"%"+departmentName+"%");
            ps.execute();
            //提交事务
            connection.commit();
            //事务回滚 -适用发生异常的时候
         //   connection.rollback();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.closeResource(ps, connection,null);
        }


    }

    public static void main(String[] args) {
        PreparedStatementDemo psDemo = new PreparedStatementDemo();

       
        psDemo.deleteDepartments("测试");
    }

}

完整代码

package com.lin;

/**
 * 数据模型
 * 用来存放数据库中Departments表中的数据
 */
public class Departments {
    private Integer department_id;
    private String department_name;
    private int location_id;

    public Integer getDepartment_id() {
        return department_id;
    }

    public void setDepartment_id(Integer department_id) {
        this.department_id = department_id;
    }

    public String getDepartment_name() {
        return department_name;
    }

    public void setDepartment_name(String department_name) {
        this.department_name = department_name;
    }

    public int getLocation_id() {
        return location_id;
    }

    public void setLocation_id(int location_id) {
        this.location_id = location_id;
    }

    @Override
    public String toString() {
        return "Departments{" +
                "department_id=" + department_id +
                ", department_name='" + department_name + ''' +
                ", location_id=" + location_id +
                '}';
    }
}

package com.lin;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * PreparedStatement 对象的使用
 */
public class PreparedStatementDemo {
    //新增数据
    public void insertDepartments(String departmentsName,int locationID){
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = jdbcUtil.getConnection();
            preparedStatement=connection.prepareStatement("insert into departments values(default,?,?)");

            preparedStatement.setString(1,departmentsName);
            preparedStatement.setInt(2,locationID);
            boolean flag = preparedStatement.execute();

            if (flag != true){
                System.out.println("添加成功");
            }else {
                System.out.println("添加失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.closeResource(preparedStatement, connection, null);
        }
    }

    //批量添加数据
    public void addBath(List<Departments> list){
        Connection connection = null;
        PreparedStatement ps = null;

        try {
            connection = jdbcUtil.getConnection();
            ps = connection.prepareStatement("insert into departments values (default ,?,?)");
            for (int i = 0; i <list.size() ; i++) {
                ps.setString(1,list.get(i).getDepartment_name());
                ps.setInt(2,list.get(i).getLocation_id());
                //添加批处理
                ps.addBatch();
            }
            int[] ints = ps.executeBatch();
            System.out.println(ints);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.closeResource(ps, connection,null);
        }
    }

    //查询数据  查询单挑数据
    public Departments selectDepartmentsById(int departmentId){
        Connection connection =null;
        PreparedStatement ps = null;
        ResultSet resultSet=null;
        Departments dept = null;

        try {
            connection = jdbcUtil.getConnection();
            ps = connection.prepareStatement("select * from departments where department_id =?");
            ps.setInt(1,departmentId);
            resultSet = ps.executeQuery();
            while (resultSet.next()){
                 dept = new Departments();
                 dept.setDepartment_id(resultSet.getInt("department_id"));
                 dept.setDepartment_name(resultSet.getString("department_name"));
                 dept.setLocation_id(resultSet.getInt("location_id"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.closeResource(ps, connection, resultSet);
        }
        return dept;
    }

    //查询数据 查询多条数据
    public List<Departments> selectDepartmentByLikeName(String departmentName){
       Connection connection = null;
       PreparedStatement ps = null;
       ResultSet rs = null;
       List<Departments> list = new ArrayList<>();

        try {
            connection = jdbcUtil.getConnection();
            ps = connection.prepareStatement("select * from departments where department_name like ?");
            ps.setString(1,"%"+departmentName+"%");
            rs=ps.executeQuery();
            while (rs.next()){
                Departments departments = new Departments();
                departments.setDepartment_id(rs.getInt("department_id"));
                departments.setDepartment_name(rs.getString("department_name"));
                departments.setLocation_id(rs.getInt("location_id"));
                list.add(departments);
            }
            int[] arr = ps.executeBatch();
            System.out.println(arr);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.closeResource(ps, connection,rs);
        }

        return list;
    }

    //更新数据
    public void updateDepartment(int department_id,String departmentName,int locationID){
        Connection connection = null;
        PreparedStatement ps = null;

        try {
            connection = jdbcUtil.getConnection();
            ps = connection.prepareStatement("update departments set department_name =?,location_id=? where department_id=?");
            ps.setString(1,departmentName);
            ps.setInt(2,locationID);
            ps.setInt(3,department_id);
            boolean execute = ps.execute();
            if (execute !=true){
                System.out.println("更新成功");
            }else{
                System.out.println("更新失败");
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.closeResource(ps,connection,null);
        }


    }
    public static void main(String[] args) {
        PreparedStatementDemo psDemo = new PreparedStatementDemo();
      //  psDemo.insertDepartments("公关部",12);

        ArrayList<Departments> list = new ArrayList<>();
        for (int i = 0; i <=10 ; i++) {
            Departments dept= new Departments();
            dept.setDepartment_name("测试部"+i);
            dept.setLocation_id(10+i);
            list.add(dept);
        }
        psDemo.addBath(list);

        //   psDemo.updateDepartment(24,"培训部",8);
       /* Departments dept = psDemo.selectDepartmentsById(1);
        if (dept != null){
            System.out.println(dept.toString());
        }*/
       /* List<Departments> listDept = psDemo.selectDepartmentByLikeName("培训");
        for (Departments departments : listDept) {
            System.out.println(departments.toString());
        }*/


    }

}

weixin_39996134

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
使用statement语句实现多条件查询_[22]-JDBC PreparedStatement的使用

讲PreparedStatement的时候就要讲一下sql注入所谓 SQL 注入，就是通过把含有 SQL 语句片段的参数插入到需要执行的 SQL 语句中，最终达到欺骗数据库服务器执行恶意操作的 SQL 命令。案例//sql注入 public void sqlInject(String departmentName,int locationId){ Connection conn = nul...
复制链接

扫一扫