JDBC进阶
BsaeDao
在前面jdbc的学习中,进行jdbc连接时,会产生大量冗余的代码,此时我们可以引入一个父类BaseDao(基础数据访问对象),此类用于将连接信息、获取数据库的连接、资源的关闭等等集中在一个类中,下面以前面数据中的Account表作为示例:
package org.example.excise1.dao;
import java.sql.*;
public class BaseDao {
//数据库连接信息
private static final String URL="jdbc:mysql://localhost:3306/spring_db";
private static final String USERNAME="root";
private static final String PASSWORD="123456";
public Connection conn=null;
public PreparedStatement pst=null;
public ResultSet rs = null;
//关闭连接
public void close_db(){
try {
if(rs!=null){
rs.close();
}
if(pst!=null){
pst.close();
}
if(conn!=null){
conn.close();
}
}catch (Exception e){
e.printStackTrace();
}
}
//获取连接
public void open_db(){
try {
conn= DriverManager.getConnection(URL,USERNAME,PASSWORD);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
此时就可以直接使用父类中的方法与属性,有效的减少了代码冗余。
参数化
在前面的jdbc学习中会遇到以下两个问题:
1.sql指令拼接难度高,容易出错,连续拼接会消耗大量的空间
2.存在着SQL注入的问题
SQL注入:指攻击者将恶意的 SQL 代码插入到应用程序的查询语句中,以达到修改、删除或绕过访问控制的目的。攻击者可以利用这个漏洞获取敏感数据、修改数据库内容,甚至完全控制数据库服务器。
为了解决这两个问题,就引入了参数化
什么是参数化
简单来说就是将用户输入作为参数传递给sql语句中,而不是将用户输入直接拼接到sql语句中,从而防止注入攻击。
如何使用参数化
1.在sql指令中,使用 ? 来表示一个参数,例如:
String sql = "insert into account(name,money) values(?,?)";
注意:所有参数按照从左往右的顺序,依次获取参数序号,序号从1开始
2.将Statement接口对象替换为其子接口对象PreparedStatement
示例代码
// ?作为占位符
String sql = "insert into account(name,money) values(?,?)";
//获取连接
open_db();
try {
//得到prepareStatement对象
pst=conn.prepareStatement(sql);
//设置占位符属性
pst.setString(1,account.getName());
pst.setDouble(2,account.getMoney());
//执行sql语句
pst.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
//关闭连接
close_db();
}
}
这里我们使用了prepareStatement对象的setXxx方法用于指定?占位符的值,也可以理解为传参
注意:子接口PreparedStatement对象在进行创建的时候就已经需要sql语句作为参数了,所以在调用executeUpdate或者executeQuery方法时是不需要填写参数的,如果给了参数,执行的其实是父类Statement接口的方法
通用查询
前面考虑到查询会有各种条件,如果将所有条件考虑进去的话,我们将会写非常多查询的重载方法(非常多!强调一下),这里我们就引入了通用查询
通用查询的参数需要什么类型呢?
考虑到需要的条件数量是不确定的,并且数据必须要对应列名,我们应当所有map集合,map的键为列名,应为String类型。map的值类型各不相同,所以我们应该使用object类型。
例如:
public List<Account> query(Map<String, Object> param){}
如何将map集合中的参数组装为可用的SQL指令
分为两种情况:
1.所有的比较为等值比较
public List<Account> query(Map<String, Object> param) {
StringBuilder sql=new StringBuilder("select * from account where 1=1 ");
//存储map集合中的值
List<Object> values=new ArrayList<>();
//存储查询的结果集
List<Account> list=new ArrayList<>();
//拼接
for (String str :param.keySet()){
sql.append(" and ").append(str).append(" = ? ");
//将值存入集合
values.add(param.get(str));
}
//开启连接
open_db();
try {
//获取连接
pst=conn.prepareStatement(sql.toString());
//为sql指令的参数赋值
for (int i = 0; i < values.size(); i++) {
pst.setObject(i+1,values.get(i));
}
//执行sql返回结果集
rs=pst.executeQuery();
//将数据存入list集合
while (rs.next()){
Account account=new Account();
account.setId(rs.getInt("id"));
account.setName(rs.getString("name"));
account.setMoney(rs.getDouble("money"));
list.add(account);
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
//关闭连接
close_db();
}
return list;
}
2.部分列使用范围或模糊查询
大部分代码与第一种情况相同,不过在进行拼接的时候不应该使用循环了,应该一个一个判断
修改前的代码:
//拼接
for (String str :param.keySet()){
sql.append(" and ").append(str).append(" = ? ");
//将值存入集合
values.add(param.get(str));
}
修改后的代码
if (pars.containsKey("Name")){
sql.append(" and empName like ? ");
//将值存入集合
values.add("%"+param.get(Name)+"%");
}
if (pars.containsKey("money")){
sql.append(" and empSalary >= ? ");
//将值存入集合
values.add(param.get(money))
}
完整代码
BaseDao
package org.example.excise1.dao;
import java.sql.*;
public class BaseDao {
//数据库连接信息
private static final String URL="jdbc:mysql://localhost:3306/spring_db";
private static final String USERNAME="root";
private static final String PASSWORD="123456";
public Connection conn=null;
public PreparedStatement pst=null;
public ResultSet rs = null;
//关闭连接
public void close_db(){
try {
if(rs!=null){
rs.close();
}
if(pst!=null){
pst.close();
}
if(conn!=null){
conn.close();
}
}catch (Exception e){
e.printStackTrace();
}
}
//获取连接
public void open_db(){
try {
conn= DriverManager.getConnection(URL,USERNAME,PASSWORD);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
AccountDao
package org.example.excise1.dao;
import org.example.excise1.po.Account;
import java.util.List;
import java.util.Map;
public interface AccountDao {
//新增方法
void add(Account account);
//通用查询
List<Account> query(Map<String, Object> param);
}
AccountDao的实现类
package org.example.excise1.dao.impl;
import org.example.excise1.dao.AccountDao;
import org.example.excise1.dao.BaseDao;
import org.example.excise1.po.Account;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
public class AccountDaoImpl extends BaseDao implements AccountDao {
//新增
@Override
public void add(Account account) {
// ?作为占位符
String sql = "insert into account(name,money) values(?,?)";
//获取连接
open_db();
try {
//得到prepareStatement对象
pst=conn.prepareStatement(sql);
//设置占位符属性
pst.setString(1,account.getName());
pst.setDouble(2,account.getMoney());
//执行sql语句
pst.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
//关闭连接
close_db();
}
}
//通用查询
@Override
public List<Account> query(Map<String, Object> param) {
StringBuilder sql=new StringBuilder("select * from account where 1=1 ");
//存储map集合中的值
List<Object> values=new ArrayList<>();
//存储查询的结果集
List<Account> list=new ArrayList<>();
//拼接
for (String str :param.keySet()){
sql.append(" and ").append(str).append(" = ? ");
//将值存入集合
values.add(param.get(str));
}
//开启连接
open_db();
try {
//获取连接
pst=conn.prepareStatement(sql.toString());
//为sql指令的参数赋值
for (int i = 0; i < values.size(); i++) {
pst.setObject(i+1,values.get(i));
}
//执行sql返回结果集
rs=pst.executeQuery();
//将数据存入list集合
while (rs.next()){
Account account=new Account();
account.setId(rs.getInt("id"));
account.setName(rs.getString("name"));
account.setMoney(rs.getDouble("money"));
list.add(account);
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
//关闭连接
close_db();
}
return list;
}
}
Account实体类
package org.example.excise1.po;
public class Account {
Integer id;
String name;
Double money;
public Account() {
}
public Account(String name, double money) {
this.setName(name);
this.setMoney(money);
}
public Account(int id, String name, double money) {
this.id = id;
this.name = name;
this.money = money;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public double getMoney() {
return money;
}
public void setMoney(double money) {
this.money = money;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
@Override
public String toString() {
return "Account{" +
"id=" + id +
", name='" + name + '\'' +
", money=" + money +
'}';
}
}
测试类
package org.example.excise1;
import org.example.excise1.dao.impl.AccountDaoImpl;
import org.example.excise1.po.Account;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class Test {
public static void main(String[] args) {
AccountDaoImpl adao = new AccountDaoImpl();
Map<String,Object> map=new HashMap<>();
map.put("name","大明");
List<Account> query = adao.query(map);
//输出结果
System.out.println(query);
}
}