PreparedStatement
PreparedStatement VS Statement
基本优化
PreparedStatement
--是Statement的子接口
特点:可以设定参数,把带参数的SQL语句先传过去,再设置对应参数的值闯过去执行
第三步,创建PreparedStatement,传入带参数的SQL语句
对于变化的数值,可以参数化,用?做占位
String sql = "INSERT INTO t_user(id,username,password,sex,id_number,tel,addr)"
+ "VALUES(t_user_id_seq.NEXTVAL,?,?,?,?,?,?)";
第四步,执行SQL语句
先设置占位符的值,发过去值执行SQL语句
每一个问号对应一个索引,从1开始,语句按照从左到右的顺序
pstmt.setXXX(索引, 对应的值);
执行,注意不要再传入SQL语句了
int rows = pstmt.exectueUpdate();
对比代码展示:
package com.zhang.jdbc;
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.List;
public class UserJDBC {
public static void main(String[] args) {
// TODO Auto-generated method stub
UserJDBC jdbc = new UserJDBC();
try {
//查询
/*User user = jdbc.queryUser("xiaoming");
System.out.println(user);*/
//添加用户
/*User user = new User(0,"lisi","123",0,"123123123123123123","111","111");
int rows = jdbc.addUser(user);
System.out.println(rows);
System.out.println(jdbc.queryUser("lisi"));*/
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//通过用户名查找对应的用户
/*public User queryUser(String username) throws ClassNotFoundException, SQLException {
User user = null;
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "easybuy", "easybuy");
Statement stmt = conn.createStatement();
String sql = "SELECT id,username,password,sex,"
+ "id_number,tel,addr FROM t_user WHERE username='" +username+ "'";
ResultSet rs = stmt.executeQuery(sql);
if(rs.next()) {
user = new User(rs.getLong(1),rs.getString(2),rs.getString(3),
rs.getInt(4),rs.getString(5),rs.getString(6),rs.getString(7));
}
if(rs!=null) {rs.close();}
if(stmt!=null) {stmt.close();}
if(conn!=null) {conn.close();}
return user;
}*/
//PreparedStatement版通过用户查找对应的用户
public User queryUser(String username) throws ClassNotFoundException, SQLException {
User user = null;
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "easybuy", "easybuy");
String sql = "SELECT id,username,password,sex,"
+ "id_number,tel,addr FROM t_user WHERE username=?";
PreparedStatement pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery(sql);
if(rs.next()) {
user = new User(rs.getLong(1),rs.getString(2),rs.getString(3),
rs.getInt(4),rs.getString(5),rs.getString(6),rs.getString(7));
}
if(rs!=null) {rs.close();}
if(pstmt!=null) {pstmt.close();}
if(conn!=null) {conn.close();}
return user;
}
//添加一个用户到用户表,id可以使用序列t_user_id_seq
/*public int addUser(User user) throws ClassNotFoundException, SQLException {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "easybuy", "easybuy");
Statement stmt = conn.createStatement();
* 首先要创建序列,语法如下
* CREATE SEQUENCE t_user_id_seq
* START WITH 10
* INCREMENT BY 1;
String sql = "INSERT INTO t_user(id,username,password,sex,id_number,tel,addr)"
+ "VALUES(t_user_id_seq.NEXTVAL,'"+user.getUsername()+ "','"+user.getPassword()+ "',"+user.getSex()+ ",'"+user.getIdNumber()+ "','"+user.getTel()+ "','"+user.getAddr()+ "'" +")";
int rows = stmt.executeUpdate(sql);
if(stmt!=null) {
stmt.close();
}
if(conn!=null) {
conn.close();
}
return rows;
}*/
//PreparedStatement版插入一个用户到用户表
public int addUser(User user) throws ClassNotFoundException, SQLException {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "easybuy", "easybuy");
//第三步:创建可以执行SQL语句的PreparedStatement
String sql = "INSERT INTO t_user(id,username,password,sex,id_number,tel,addr)"
+ "VALUES(t_user_id_seq.NEXTVAL,?,?,?,?,?,?)";
PreparedStatement pstmt =conn.prepareStatement(sql);
//第四步:执行SQL语句
//先设置占位符的值
pstmt.setString(1, user.getUsername());
pstmt.setString(2, user.getPassword());
pstmt.setInt(3, user.getSex());
pstmt.setString(4, user.getIdNumber());
pstmt.setString(5, user.getTel());
pstmt.setString(6, user.getAddr());
//执行
int rows = pstmt.executeUpdate();
if(pstmt!=null) {
pstmt.close();
}
if(conn!=null) {
conn.close();
}
return rows;
}
}
PreparedStatement VS Statement
- 直观看
- 利用PreparedStatement不需要拼接,不需要关心单引号的问题
-
先传带参数的SQL语句
-
再传参数执行
-
-
利用Statement利用拼接,注意文本型要加单引号拼接
-
把完整的拼接后语句传过去执行
-
- 利用PreparedStatement不需要拼接,不需要关心单引号的问题
-
效率看
-
如果添加一个学生,Statement相对快一点
-
如果添加多个学生,
-
对于Statement,循环第四步,发送100次语句过去,都不相同
-
对于一个语句,发送过去需要先编译处理,再执行,编译一次
-
-
对于PreparedStatement,循环第四步,发送100次参数值过去,语句是一样的
-
对于带参数的语句,先预编译,只要预编译一次执行100次,相对效率高一点
-
对于服务端有优化处理,如果一个语句前面有执行过,此时如果要执行相同的语句就不需要编译了,直接执行
-
-
-
-
从安全角度看
-
需求:登录时,通过用户名和密码查找一个用户
-
Statement:
-
"SELECT id,username,password,sex FROM t_user WHERE username="+username+"and password=' "+password+" ' " ;
-
-
PreparedStatement:
-
SELECT id,username,password,sex FROM t_user WHERE username=? and password=?
-
-
假如:用户名:aa 密码:111 (结果一致)
-
假如:用户名:aa 密码:"111' OR '1'='1" (Statement中枪,可以查到所有的用户信息;PreparedStatement直接报错,不允许设置)
-
PreparedStatement更安全
-
PreparedStatement可以替代Statement,我们推荐使用PreparedStatement。
-
优化
DBUtils:释放资源
- 关闭:
- 数量不一样
- Statement,PreparedStatement,无影响,PreparedStatement是Statement的子接口
释放资源类DBUtils如下:
package com.zhang.util;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* 资源的释放
* @author zygycp
*
*/
public class DBUtils {
public static void close(ResultSet rs, Statement stmt,Connection conn) {
if(rs!=null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(stmt!=null) {
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
在以后的第六步关闭资源只需调用该类即可:
DBUtils.close(rs,stmt,conn);
如果只关闭两个,可以用空值传入
DBUtils.close(null,stmt,conn);
也可以从新定义一个只有两个关闭的方法:
//方法重载
public static void close(Statement stmt,Connection conn) {
close(null,stmt,conn);
}
//本质上结果是一样的,这样方便客户使用
使用:
DBUtils.close(stmt,conn);
ConnectionFactory:
一般情况:
package com.zhang.util;
/**
* Connection工厂类
* 利用这个类获取连接对象
* @author zygycp
*
*/
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class ConnectionFactory {
//完成1,2两步,返回连接对象
public static Connection getConnection() {
Connection conn = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "easybuy", "easybuy");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
}
通用情况:
- properties:属性文件(键值对存储方式)
- key=value
- key=value
- key=value
- Java中:Properties类,集合类,存储键值对(Map体系)
- 这个类对象可以从properties文件中加载数据,把所有的键值对加载的内存对象中
- 在ConnectionFactory所在的包下创建一个properties文件:jdbcinfo.properties
代码如下:
package com.zhang.util;
/**
* Connection工厂类
* 利用这个类获取连接对象
* @author zygycp
*
*/
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class ConnectionFactory {
private static String DRIVER;
private static String URL;
private static String UNAME;
private static String UPASS;
//静态代码块:在类被加载时执行,只会执行一次
static {
//1.创建一个Properties对象
Properties prop = new Properties();
//加载properties文件的数据
try {
//利用Class对象的getResourceAsStream,可以返回同一个包下资源的输入字节流
prop.load(ConnectionFactory.class.getResourceAsStream("jdbcinfo.properties"));
//获取键对应的值
DRIVER = prop.getProperty("driver");
URL = prop.getProperty("url");
UNAME = prop.getProperty("uname");
UPASS = prop.getProperty("upass");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//完成1,2两步,返回连接对象
public static Connection getConnection() {
Connection conn = null;
try {
Class.forName(DRIVER);
conn = DriverManager.getConnection(URL, UNAME, UPASS);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
public static void main(String[] args) {
System.out.println(getConnection());
}
}
jdbcinfo.properties文件内容如下:
driver = oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@localhost:1521:XE
uname=easybuy
upass=easybuy