Statement與PreparedStatement的區(qū)別：
1. PreparedStatement可以寫動態(tài)參數(shù)化的查詢
用PreparedStatement你可以寫帶參數(shù)的sql查詢語句租幕，通過使用相同的sql語句和不同的參數(shù)值來做查詢比創(chuàng)建一個(gè)不同的查詢語句要好
2. PreparedStatement比 Statement 更快
PreparedStatement用來執(zhí)行SQL語句查詢的時(shí)候囊蓝。數(shù)據(jù)庫系統(tǒng)會對sql語句進(jìn)行預(yù)編譯處理，預(yù)處理語句將被預(yù)先編譯好令蛉，這條預(yù)編譯的sql查詢語句能在將來的查詢中重用聚霜，這樣一來，它比Statement對象生成的查詢速度更快珠叔。
3. PreparedStatement可以防止SQL注入式攻擊
如果你是做Java web應(yīng)用開發(fā)的蝎宇，那么必須熟悉那聲名狼藉的SQL注入式攻擊。Sony就遭受了SQL注入攻擊祷安，被盜用了一些Sony play station（PS機(jī)）用戶的數(shù)據(jù)姥芥。在SQL注入攻擊里，惡意用戶通過SQL元數(shù)據(jù)綁定輸入汇鞭。
例如:
sql = "SELECT * FROM users WHERE name = '" + userName + "' and pw = '"+ passWord +"';"
惡意填入：
userName = "1' OR '1'='1";
passWord = "1' OR '1'='1";
那么最終SQL語句變成了：
sql = "SELECT * FROM users WHERE name = '1' OR '1'='1' and pw = '1' OR '1'='1';"
PreparedStatement的局限性
盡管PreparedStatement非常實(shí)用凉唐，但是它仍有一定的限制庸追。
為了防止SQL注入攻擊，PreparedStatement不允許一個(gè)占位符（台囱？）有多個(gè)值淡溯，在執(zhí)行有IN子句查詢的時(shí)候這個(gè)問題變得棘手起來。
注意：占位符索引位置從1開始簿训，而不是從0開始咱娶。
接下來給大家展示使用使用·PreparedStatement·執(zhí)行sql增、刪强品、改膘侮、查語句的過程：
實(shí)體類
package zr.com.chiansoft.vo;
import java.util.Date;
public class Emp {
private int empno;
private String ename;
private String job;
private int mgr;
private Date hiredate;
private double sal;
private double comm;
private int deptno;
public Emp() {
super();
// TODO Auto-generated constructor stub
}
public Emp(int empno, String ename, String job, int mgr, Date hiredate, double sal, double comm, int deptno) {
super();
this.empno = empno;
this.ename = ename;
this.job = job;
this.mgr = mgr;
this.hiredate = hiredate;
this.sal = sal;
this.comm = comm;
this.deptno = deptno;
}
public Emp(String ename, String job, int mgr, Date hiredate, double sal, double comm, int deptno) {
super();
this.ename = ename;
this.job = job;
this.mgr = mgr;
this.hiredate = hiredate;
this.sal = sal;
this.comm = comm;
this.deptno = deptno;
}
public int getEmpno() {
return empno;
}
public void setEmpno(int empno) {
this.empno = empno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public int getMgr() {
return mgr;
}
public void setMgr(int mgr) {
this.mgr = mgr;
}
public Date getHiredate() {
return hiredate;
}
public void setHiredate(Date hiredate) {
this.hiredate = hiredate;
}
public double getSal() {
return sal;
}
public void setSal(double sal) {
this.sal = sal;
}
public double getComm() {
return comm;
}
public void setComm(double comm) {
this.comm = comm;
}
public int getDeptno() {
return deptno;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
@Override
public String toString() {
return "Emp [empno=" + empno + ", ename=" + ename + ", job=" + job + ", mgr=" + mgr + ", hiredate=" + hiredate
+ ", sal=" + sal + ", comm=" + comm + ", deptno=" + deptno + "]";
}
}
dao接口
package zr.com.chinasoft.dao;
import java.util.List;
import zr.com.chiansoft.vo.Emp;
public interface EmpDao {
/**
* 作者：_借東西的小人
* 向表中增加員工信息
*/
boolean addEmp(Emp emp);
/**
* 通過empno刪除員工信息
*/
boolean deleteByEmpno(int empno);
/**
* 通過ename刪除員工信息
*/
boolean deleteByEname(String ename);
/**
* 通過empno修改員工信息
*/
boolean update(Emp emp);
/**
* 查詢所有員工信息
*/
List<Emp> QueryEmp();
/**
* 通過ename查詢員工信息
*/
List<Emp> QueryEmpByEname(Emp emp);
}
工具類
package zr.com.chiansoft.dbUtils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import zr.com.chiansoft.vo.Emp;
public class DBUtils {
static String user = "SCOTT";
static String password = "TIGER";
static Connection conn = null;
static PreparedStatement ps = null;
/**
* 獲取連接
*/
public static Connection getConnection(String user,String password){
Connection conn = null;
try {
// 1.加載驅(qū)動
Class.forName("oracle.jdbc.driver.OracleDriver");
// 2.獲取連接對象
String url = "jdbc:oracle:thin:@localhost:1521:xe";
conn = DriverManager.getConnection
(url,"SCOTT","TIGER");
//
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
return conn;
}
/**
* 更新語句
*/
public static boolean update(String sql,Object obj[]){
conn = getConnection(user, password);
int count = 0;
try {
// 預(yù)編譯sql
ps = conn.prepareStatement(sql);
for(int i=0;i<obj.length;i++){
// 給sql語句占位符賦值
ps.setObject(i+1, obj[i]);
}
count = ps.executeUpdate();
System.out.println("數(shù)據(jù)表更新"+count+"條");
} catch (SQLException e) {
e.printStackTrace();
}finally{
close(null, ps, conn);
}
return count==0?false:true;
}
/**
* 查詢?nèi)空Z句
*/
public static List<Emp> QueryAll(String sql,ResultSet rs){
conn = getConnection(user, password);
try {
// 預(yù)編譯sql
ps=conn.prepareStatement(sql);
// 執(zhí)行sql
rs = ps.executeQuery();
} catch (SQLException e1) {
e1.printStackTrace();
}
List<Emp> list = new ArrayList<>();
try {
//遍歷rs
while(rs.next()){
int empno = rs.getInt(1);
String ename1 = rs.getString(2);
String job = rs.getString(3);
int mgr = rs.getInt(4);
Date hiredate = rs.getDate(5);
double sal = rs.getDouble(6);
double comm = rs.getDouble(7);
int deptno = rs.getInt(8);
Emp emp = new Emp(empno,ename1, job, mgr, hiredate, sal, comm, deptno);
list.add(emp);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
/**
*按照條件查詢語句
*/
public static List<Emp> Query(String sql,ResultSet rs,Object obj[]){
conn = getConnection(user, password);
try {
// 預(yù)編譯sql
ps=conn.prepareStatement(sql);
// 給sql語句占位符賦值
ps.setObject(1, obj[0]);
// 執(zhí)行sql
rs = ps.executeQuery();
} catch (SQLException e1) {
e1.printStackTrace();
}
List<Emp> list = new ArrayList<>();
try {
// 遍歷rs
while(rs.next()){
int empno = rs.getInt(1);
String ename1 = rs.getString(2);
String job = rs.getString(3);
int mgr = rs.getInt(4);
Date hiredate = rs.getDate(5);
double sal = rs.getDouble(6);
double comm = rs.getDouble(7);
int deptno = rs.getInt(8);
Emp emp = new Emp(empno,ename1, job, mgr, hiredate, sal, comm, deptno);
list.add(emp);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
/**
* 關(guān)閉連接
*/
public static void close(ResultSet rs,Statement stat,Connection conn){
try {
// 6.關(guān)閉連接
if(rs!=null){
rs.close();
}
if(stat!=null){
stat.close();
}
if(conn!=null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
dao接口的實(shí)現(xiàn)
package zr.com.chiansoft.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import zr.com.chiansoft.dbUtils.DBUtils;
import zr.com.chiansoft.vo.Emp;
import zr.com.chinasoft.dao.EmpDao;
public class EmpDaoImpl implements EmpDao{
// 數(shù)據(jù)庫用戶名
String user = "SCOTT";
// 數(shù)據(jù)庫密碼
String password = "TIGER";
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
boolean re = false;
int count = 0;
List<Emp> list = new ArrayList<Emp>();
/**
* 向表中增加員工信息
*/
@Override
public boolean addEmp(Emp emp) {
// 1.加載驅(qū)動
// 2.獲取連接對象
// 3.編寫sql語句
String sql = "insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) "
+ "values (?,?,?,?,?,?,?,?)";
Object obj[] = {emp.getEmpno(),emp.getEname(),emp.getJob(),emp.getMgr(),
emp.getHiredate(),emp.getSal(),emp.getComm(),emp.getDeptno()};
DBUtils.update(sql, obj);
// 6.關(guān)閉連接
DBUtils.close(null, ps, conn);
return re;
}
/**
* 通過empno刪除員工信息
*/
@Override
public boolean deleteByEmpno(int empno) {
String sql = "delete from emp where empno=?";
Object obj[] = {empno};
re = DBUtils.update(sql, obj);
DBUtils.close(null, ps, conn);
return re;
}
/**
* 通過ename刪除員工信息
*/
@Override
public boolean deleteByEname(String ename) {
String sql = "delete from emp where ename=?";
Object obj[] = {ename};
re = DBUtils.update(sql, obj);
DBUtils.close(null, ps, conn);
return re;
}
/**
* 通過empno修改員工信息
*/
@Override
public boolean update(Emp emp) {
String sql = "update emp set ename=?,job=?,mgr=?,hiredate=?,sal=?,comm=?,deptno=? where empno=?";
Object obj[] = {emp.getEname(),emp.getJob(),emp.getMgr(),
emp.getHiredate(),emp.getSal(),emp.getComm(),emp.getDeptno(),emp.getEmpno()};
re = DBUtils.update(sql, obj);
DBUtils.close(null, ps, conn);
return re;
}
/**
* 查詢所有員工信息
*/
@Override
public List<Emp> QueryEmp() {
String sql = "select * from emp";
list = DBUtils.QueryAll(sql,rs);
DBUtils.close(rs, ps, conn);
return list;
}
/**
* 通過ename查詢員工信息
*/
@Override
public List<Emp> QueryEmpByEname(Emp emp) {
String sql = "select * from emp where ename=?";
Object []obj = {emp.getEname()};
list = DBUtils.Query(sql,rs,obj);
DBUtils.close(rs, ps, conn);
return list;
}
}
測試類
package zr.com.chinasoft.test;
import java.sql.Date;
import java.util.List;
import zr.com.chiansoft.dao.impl.EmpDaoImpl;
import zr.com.chiansoft.vo.Emp;
import zr.com.chinasoft.dao.EmpDao;
public class EmpTest {
public static void main(String[] args) {
EmpDao dao = new EmpDaoImpl();
Emp emp = new Emp();
System.out.println("添加員工測試");
emp.setEmpno(1122);
emp.setEname("lilil");
emp.setJob("Cliker");
emp.setHiredate(Date.valueOf("2017-05-05"));
emp.setSal(1111);
emp.setComm(100);
emp.setDeptno(10);
dao.addEmp(emp);
System.out.println("通過empno刪除員工信息測試");
dao.deleteByEmpno(1122);
System.out.println("通過ename刪除員工信息測試");
dao.deleteByEname("狼狼");
System.out.println("通過empno修改員工信息測試");
emp.setEmpno(1000);
emp.setEname("泡泡");
emp.setJob("Mouse");
emp.setMgr(100);
emp.setHiredate(Date.valueOf("1999-9-9"));
emp.setSal(6666);
emp.setComm(666);
emp.setDeptno(10);
dao.update(emp);
System.out.println("查詢所有員工信息測試");
List<Emp> list = dao.QueryEmp();
for(Emp empInfo:list){
System.out.println(empInfo);
}
System.out.println("通過ename查詢員工信息測試");
emp.setEname("韓跑跑");
List<Emp> list1 = dao.QueryEmpByEname(emp);
for(Emp empInfo:list1){
System.out.println(empInfo);
}
}
}
完整的項(xiàng)目代碼已經(jīng)上傳到github中了，訪問地址:github
QQ截圖20170919143859.png
在學(xué)習(xí)的過程中如果遇到什么問題的榛，歡迎大家提問琼了。
