相关文章
PreparedStatement
PreparedStatement是Statement的子接口,Statement是试行静态sql对象,PreparedStatement是执行预编译sql对象,用占位符?动态传参,解决sql注入问题。
下面就通过一个例子演示,例子是通过jdbc连接查account表中的数据,然后用实体类Account封装起来,返回这个类的集合。
jdbc工具类代码
package com.lingaolu.Utils;
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;
/**
* @author 林高禄
* @create 2020-06-23-11:12
*/
public class JdbcUtils {
private static String driver;
private static String url;
private static String userName;
private static String pw;
static{
try {
Properties p = new Properties();
ClassLoader classLoader = JdbcUtils.class.getClassLoader();
// 这个路径相对于src的路径来说
URL resource = classLoader.getResource("com/lingaolu/file/jdbc.properties");
String path = resource.getPath();
p.load(new FileReader(path));
driver = p.getProperty("driver");
url = p.getProperty("url");
userName = p.getProperty("user");
pw = p.getProperty("password");
Class.forName(driver);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection createConnection() throws SQLException {
return DriverManager.getConnection(url, userName, pw);
}
public static void close(Statement stmt,Connection con){
if(null != stmt){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(null != con){
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(ResultSet set,Statement s,Connection con){
if(null != set){
try {
set.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
close(s,con);
}
}
Account实体类代码
package com.lingaolu.jdbcConnector;
/**
* @author 林高禄
* @create 2020-06-24-8:28
*/
public class Account {
private int id;
private String name;
private double balance;
private int myAge;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public double getBalance() {
return balance;
}
public void setBalance(double balance) {
this.balance = balance;
}
public int getMyAge() {
return myAge;
}
public void setMyAge(int myAge) {
this.myAge = myAge;
}
@Override
public String toString() {
return "Account{" +
"id=" + id +
", name='" + name + '\'' +
", balance=" + balance +
", myAge=" + myAge +
'}';
}
}
测试Demo5的代码
package com.lingaolu.jdbcConnector;
import com.lingaolu.Utils.JdbcUtils;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* @author 林高禄
* @create 2020-06-24-09:04
*/
public class Demo5 {
public static void main(String[] args) {
List<Account> accounts = fineAccount("李四");
accounts.forEach(System.out::println);
System.out.println("----------------------------------");
accounts = fineAccount("王五");
accounts.forEach(System.out::println);
System.out.println("----------------------------------");
accounts = fineAccount("王五' or '1'='1");
accounts.forEach(System.out::println);
}
public static List<Account> fineAccount(String accoutName){
Connection con = null;
PreparedStatement pstmt = null;
ResultSet resultSet = null;
List<Account> rerurnList = new ArrayList<>();
try {
con = JdbcUtils.createConnection();
// 定义sql
String sql = "select * from account where name=?";
// 获取执行的sql
pstmt = con.prepareStatement(sql);
System.out.println(sql);
// 给?赋值,setString表示类型,第一个参数表示在sql参数中的位置,第二个参数表示值
pstmt.setString(1,accoutName);
// 执行查询,不需调传递sql
resultSet = pstmt.executeQuery();
Account acc = null;
while(resultSet.next()){
// 引号里的字段要与表里的一样
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
double balance = resultSet.getDouble("balance");
int age = resultSet.getInt("age");
acc = new Account();
acc.setId(id);
acc.setName(name);
acc.setBalance(balance);
acc.setMyAge(age);
rerurnList.add(acc);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.close(resultSet,pstmt,con);
}
return rerurnList;
}
}
数据库数据:
运行输出
select * from account where name=?
Account{id=2, name='李四', balance=1000.0, myAge=16}
----------------------------------
select * from account where name=?
----------------------------------
select * from account where name=?
输出结果与我们对照数据库的数据预期结果一样,解决了sql注入问题,这个问题再这里使用Statement演示了一边jdbc中sql注入