/**
* 新增广告
*/
public boolean saveAdvertise(Advertisement advertisement,String webUrl,String url) throws Exception {
// TODO Auto-generated method stub
boolean b = false;
PreparedStatement pstmt = null;
Connection conn = null;
ResultSet rset = null;
try {
conn = this.getJdbcTemplate().getDataSource().getConnection();
conn.setAutoCommit(false);
BLOB blob = null;
String sql = "insert into HOLIDAY_ADVERTISEMENT(ADVERTISEID,ADVERTISENAME,PIC,LINKURL,HOTELADDNUM,HOLIDAYADDNUM,HOLIDAYTYPEADDNUM,PUBLISHSTATE,CHOREN) values (SEQ_ADVERTISE.nextval,?,empty_blob(),?,?,?,?,?,?)";
/** 新增广告策略
* 如果新增的广告频道位置在对应频道已经存在,需先将存在的频道位置设为null,然后新增就ok啦。
* 开始-----
**/
String sql2 = "";
if(advertisement.getHoteladdnum()!=null){
sql2 = "update HOLIDAY_ADVERTISEMENT set HOTELADDNUM = null where HOTELADDNUM = "+advertisement.getHoteladdnum()+" and CHOREN = '"+advertisement.getChoren()+"'";
pstmt = conn.prepareStatement(sql2);
pstmt.executeUpdate();
if(pstmt!=null){
pstmt.close();
}
}
if(advertisement.getHolidayaddnum()!=null){
sql2 = "update HOLIDAY_ADVERTISEMENT set HOLIDAYADDNUM = null where HOLIDAYADDNUM = "+advertisement.getHolidayaddnum()+" and CHOREN = '"+advertisement.getChoren()+"'";
pstmt = conn.prepareStatement(sql2);
pstmt.executeUpdate();
if(pstmt!=null){
pstmt.close();
}
}
if(advertisement.getHolidaytypeaddnum()!=null){
sql2 = "update HOLIDAY_ADVERTISEMENT set HOLIDAYTYPEADDNUM = null where HOLIDAYTYPEADDNUM = "+advertisement.getHolidaytypeaddnum()+" and CHOREN = '"+advertisement.getChoren()+"'";
pstmt = conn.prepareStatement(sql2);
pstmt.executeUpdate();
if(pstmt!=null){
pstmt.close();
}
}
/** 结束 **/
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, advertisement.getAdvertisename());
pstmt.setString(2, advertisement.getLinkurl());
if(advertisement.getHoteladdnum()!=null){
pstmt.setLong(3, advertisement.getHoteladdnum());
}else {
// pstmt.setNull(3, Types.NULL);
pstmt.setNull(3, java.sql.Types.INTEGER);
}
if(advertisement.getHolidayaddnum()!=null){
pstmt.setLong(4, advertisement.getHolidayaddnum());
}else {
// pstmt.setNull(4, Types.NULL);
pstmt.setNull(4, java.sql.Types.INTEGER);
}
if(advertisement.getHolidaytypeaddnum()!=null){
pstmt.setLong(5, advertisement.getHolidaytypeaddnum());
}else {
// pstmt.setNull(5, Types.NULL);
pstmt.setNull(5, java.sql.Types.INTEGER);
}
pstmt.setLong(6, advertisement.getPublishstate());
pstmt.setString(7, advertisement.getChoren());
pstmt.executeUpdate();
if(pstmt!=null){
pstmt.close();
}
pstmt = conn
.prepareStatement("select PIC from HOLIDAY_ADVERTISEMENT where ADVERTISENAME= ? for update");
pstmt.setString(1, advertisement.getAdvertisename());
rset = pstmt.executeQuery();
if (rset.next())
blob = (BLOB) rset.getBlob(1);
System.out.println(url);
String fileName = url;
File f = new File(webUrl+fileName);
FileInputStream fin = new FileInputStream(f);
System.out.println("file size = " + fin.available());
pstmt = conn
.prepareStatement("update HOLIDAY_ADVERTISEMENT set PIC=? where ADVERTISENAME=?");
OutputStream out = blob.getBinaryOutputStream();
byte[] data = new byte[(int) fin.available()];
fin.read(data);
out.write(data);
fin.close();
out.close();
pstmt.setBlob(1, blob);
pstmt.setString(2, advertisement.getAdvertisename());
pstmt.executeUpdate();
conn.commit();
/** 删除上传的文件 **/
File file = new File(webUrl+url);
if(file.exists()){
file.delete();
}
b = true;
} catch (Exception e) {
e.printStackTrace();
} finally{
if(rset!=null){
rset.close();
}
if(pstmt!=null){
pstmt.close();
}
if(conn!=null){
conn.close();
}
}
return b;
}
上面的代码是实际工作中的逻辑,总结一下保存图片到数据库的步骤:
一、insert into 保存数据到数据库,对应的blob地方使用empty_blob();
二、select PIC from HOLIDAY_ADVERTISEMENT where ADVERTISENAME= ? for update 查询出blob的字段,用blob保存起来。
三、update HOLIDAY_ADVERTISEMENT set PIC=? where ADVERTISENAME=? 将要保存的图片的文件流读取到blob中,替换原来的blob字段内容。具体代码在上面的程序中寻找。