mysql 有empty_blob()_BLOB和CLOB字段的存取

对blob和clob插入

tblappresourcemanager 是我的一个实体类， 你可以自己建一个实体

public String insert(Tblappresourcemanager tblappresourcemanager,HttpServletRequest request) {

Connection conn = SqlUtil.getJdbcConnection();

ResultSet rs=null;

PreparedStatement ps = null;

String resourceid = tblappresourcemanager.getResourceid();

try {

StringBuffer sql = new StringBuffer();

sql.append("insert into TBLAPPRESOURCEMANAGER(APPID,APPKEY,APPTYPE,APPPATH,REMARK,RESOURCEID,BACK2,APPCONTEXT) values(?,?,?,?,?,?,?,empty_blob())");

ps= conn.prepareStatement(sql.toString());

//通过oralce.sql.BLOB/CLOB.empty_lob()构造空Blob/Clob对象

ps.setString(1,tblappresourcemanager.getAppid());

ps.setString(2,tblappresourcemanager.getAppkey());

ps.setString(3,tblappresourcemanager.getApptype());

ps.setString(4,tblappresourcemanager.getApppath());

ps.setString(5,tblappresourcemanager.getRemark());

ps.setString(6,resourceid);

ps.setString(7,tblappresourcemanager.getBack2());

ps.executeUpdate();

ps.close();

//再次对读出Blob/Clob句柄

ps=(PreparedStatement) conn.prepareStatement("select APPCONTEXT from TBLAPPRESOURCEMANAGER where RESOURCEID=? for update");

ps.setString(1,resourceid);

rs=ps.executeQuery();

oracle.sql.BLOB contextBlob = null;

while(rs.next()){

contextBlob=(oracle.sql.BLOB)rs.getBlob("appcontext");

}

MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;

MultipartFile mFile = multipartRequest.getFile("fileId");

if(mFile==null){

return "获取文件失败";

}

InputStream inStream = mFile.getInputStream();

OutputStream outStream=contextBlob.getBinaryOutputStream();

int count = -1;

byte[] data = new byte[1024];

while ((count = inStream.read(data)) != -1) {

outStream.write(data, 0, count);

}

inStream.close();

outStream.close();

//再将Blob/Clob字段更新到数据库

ps=(PreparedStatement)conn.prepareStatement("update TBLAPPRESOURCEMANAGER set APPCONTEXT=? where RESOURCEID=?");

ps.setBlob(1, contextBlob);

ps.setString(2,resourceid);

ps.executeUpdate();

ps.close();

conn.commit();

conn.close();

//调用读取文件

readfile(resourceid);

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

e.getMessage();

return "数据库连接异常";

}catch (FileNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

return "文件流异常";

}catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

return "数据流异常";

}

return null;

}

public String readfile(String resouceid){

Connection conn = SqlUtil.getJdbcConnection();

ResultSet rs=null;

PreparedStatement ps = null;

InputStream in=null;

oracle.sql.BLOB blob = null;

String str = "select APPCONTEXT from TBLAPPRESOURCEMANAGER where RESOURCEID=?";

try {

ps = conn.prepareStatement(str);

ps.setString(1, resouceid);

rs= ps.executeQuery();

while(rs.next()){

blob = (oracle.sql.BLOB)rs.getBlob("appcontext");

}

in = blob.getBinaryStream();

FileOutputStream outStream = new FileOutputStream("D:\\cesi4.xls");

byte[] buf = new byte[1024];

int bytesIn = 0;

while ((bytesIn = in.read(buf, 0, 1024)) != -1) {

outStream.write(buf, 0, bytesIn);

}

in.close();

outStream.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}catch (FileNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} finally {

try {

rs.close();

ps.close();

conn.close();

}

catch(Exception e) {

e.printStackTrace();

}

}

return null;

}