4天贯通JDBC技术四、PreparedStatement
PreparedStatement是Statement的子接口
①需要预编译SQL语句：PreparedStatement ps = conn.preparedStatement(sql);
②填充占位符：setObject(int index);//index从1开始
③execute() / executeUpdate() ; executeQuery(); 返回一个ResultSet
1.替换原来的Statement，实现增删改和查的操作
-->Statement的问题：①拼串 不方便，容易出错 ②存在sql注入的问题，可以对数据库进行恶意攻击。
// 实现一个通用的UPDATE INSERT DELETE的操作的方法(version 2.0)
public void update(String sql, Object... args) {
Connection conn = null;
PreparedStatement ps = null;
try {
// 1.获取连接
conn = JDBCUtils.getConnection();
// 2.返回PreparedSt对象，预编译sql语句
ps = conn.prepareStatement(sql);
// 3.填充占位符
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.close(null, ps, conn);
}
}
// 实现一个通用的查询操作,返回一个对象(version 2.0)
public <T> T getInstance(String sql, Class<T> clazz, Object... args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
// 1.获取连接
conn = JDBCUtils.getConnection();
// 2.预编译sql语句，返回PreparedStatement对象
ps = conn.prepareStatement(sql);
// 3.填充占位符
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
// 4.执行并返回ResultSet的对象
rs = ps.executeQuery();
if (rs.next()) {
// 5.创建T的对象
T t = clazz.newInstance();
// 6.将结果集中的列值作为T的对象的属性，给予赋值
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
for (int i = 0; i < columnCount; i++) {
Object columnVal = rs.getObject(i + 1);
String columnLabel = rsmd.getColumnLabel(i + 1);
PropertyUtils.setProperty(t, columnLabel, columnVal);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 7.关闭相应的操作
JDBCUtils.close(rs, ps, conn);
}
return null;
}
// 实现一个通用的查询操作,返回一个对象的集合(version 2.0)
public <T> List<T> getForList(String sql,Class<T> clazz,Object ... args){
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
List<T> list = new ArrayList<T>();
try{
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
for(int i = 0;i < args.length;i++){
ps.setObject(i + 1, args[i]);
}
rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
while(rs.next()){
T t = clazz.newInstance();
for(int i = 0;i < columnCount;i++){
Object columnVal = rs.getObject(i + 1);
String columnLabel = rsmd.getColumnLabel(i + 1);
PropertyUtils.setProperty(t, columnLabel, columnVal);
}
list.add(t);
}
}catch(Exception e){
e.printStackTrace();
}finally{
JDBCUtils.close(rs, ps, conn);
}
return list;
}
//2.使用PreparedStatement的其他优点
1.实现大数据类型的数据的插入、修改、查询的操作.
setBlob() getBlob();
// 从数据表中将大数据类型的数据取出
@Test
public void testBlob3(){
Connection conn = null;
PreparedStatement ps = null;
String sql = "select id,name,email,birth,photo from customers where id = ?";
ResultSet rs = null;
InputStream is = null;
FileOutputStream fos = null;
try{
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
fos = new FileOutputStream("ym1.jpg");
ps.setInt(1, 21);
rs = ps.executeQuery();
if(rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
Date birth = rs.getDate("birth");
String email = rs.getString("email");
Customer cust = new Customer(id,name,email,birth);
System.out.println(cust);
}
Blob photo = rs.getBlob(5);
is = photo.getBinaryStream();
byte[] b = new byte[1024];
int len;
while((len = is.read(b)) != -1){
fos.write(b, 0, len);
}
}catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.close(rs, ps, conn);
if(fos != null){
try {
fos.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(is != null){
try {
is.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
// 向数据表中修改现有的大数据类型的数据
@Test
public void testBlob2() {
Connection conn = null;
PreparedStatement ps = null;
String sql = "update customers set photo = ? where id = ?";
try {
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
ps.setBlob(1, new FileInputStream("ym.jpg"));
ps.setInt(2, 21);
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.close(null, ps, conn);
}
}
// 向数据库的表中写入大数据类型的数据
@Test
public void testBlob1() {
Connection conn = null;
PreparedStatement ps = null;
String sql = "insert into customers(name,email,birth,photo)values(?,?,?,?)";
try {
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
ps.setString(1, "杨幂1");
ps.setString(2, "yang@126.com");
ps.setDate(3, new Date(new java.util.Date().getTime()));
ps.setBlob(4, new FileInputStream("1.jpg"));
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.close(null, ps, conn);
}
}
2.使用PreparedStatement进行批量操作时，效率优于Statement.
//批量操作，主要指的是批量插入。
//oracle是支持批量插入的。
//如何实现最优？ ①使用PreparedStatement ②addBatch() executeBatch() clearBatch()
public void test4() {
Connection conn = null;
PreparedStatement ps = null;
long start = System.currentTimeMillis();
String sql = "insert into dept values(?,?)";
try {
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
for (int i = 0; i < 100000; i++) {
ps.setInt(1, i + 1);
ps.setString(2, "dept_" + (i + 1) + "_name");
//1.“攒”SQL
ps.addBatch();
if( (i + 1) % 250 == 0){
//2.执行sql
ps.executeBatch();
//3.清空sql
ps.clearBatch();
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.close(null, ps, conn);
}
long end = System.currentTimeMillis();
System.out.println("花费时间：" + (end - start));//2427
}
