4天貫通JDBC技術(shù)四、PreparedStatement
PreparedStatement是Statement的子接口
①需要預(yù)編譯SQL語句：PreparedStatement ps = conn.preparedStatement(sql);
②填充占位符：setObject(int index);//index從1開始
③execute() ?/ ?executeUpdate() ?; ??executeQuery(); 返回一個(gè)ResultSet
1.替換原來的Statement，實(shí)現(xiàn)增刪改和查的操作
?????-->Statement的問題：①拼串 ?不方便，容易出錯(cuò) ②存在sql注入的問題，可以對(duì)數(shù)據(jù)庫進(jìn)行惡意攻擊。
// 實(shí)現(xiàn)一個(gè)通用的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對(duì)象，預(yù)編譯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);
}
}
// 實(shí)現(xiàn)一個(gè)通用的查詢操作,返回一個(gè)對(duì)象(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.預(yù)編譯sql語句，返回PreparedStatement對(duì)象
ps = conn.prepareStatement(sql);
// 3.填充占位符
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
// 4.執(zhí)行并返回ResultSet的對(duì)象
rs = ps.executeQuery();
if (rs.next()) {
// 5.創(chuàng)建T的對(duì)象
T t = clazz.newInstance();
// 6.將結(jié)果集中的列值作為T的對(duì)象的屬性，給予賦值
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.關(guān)閉相應(yīng)的操作
JDBCUtils.close(rs, ps, conn);
}
return null;
}
// 實(shí)現(xiàn)一個(gè)通用的查詢操作,返回一個(gè)對(duì)象的集合(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的其他優(yōu)點(diǎn)
1.實(shí)現(xiàn)大數(shù)據(jù)類型的數(shù)據(jù)的插入、修改、查詢的操作.
setBlob() ??getBlob();
// 從數(shù)據(jù)表中將大數(shù)據(jù)類型的數(shù)據(jù)取出
@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();
}
}
}
}
// 向數(shù)據(jù)表中修改現(xiàn)有的大數(shù)據(jù)類型的數(shù)據(jù)
@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);
}
}
// 向數(shù)據(jù)庫的表中寫入大數(shù)據(jù)類型的數(shù)據(jù)
@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進(jìn)行批量操作時(shí)，效率優(yōu)于Statement.
//批量操作，主要指的是批量插入。
//oracle是支持批量插入的。
//如何實(shí)現(xiàn)最優(yōu)？ ?①使用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.執(zhí)行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("花費(fèi)時(shí)間：" + (end - start));//2427
}
