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
}
