以下是一个带Blob类型的存储过程
CREATE OR REPLACE PROCEDURE updatepicture (pi_mallid VARCHAR2,pi_tb_ord_id VARCHAR2,pi_goodsid VARCHAR2,pi_picture BLOB,
po_result OUT NUMBER,
po_message OUT VARCHAR2) IS
v_ord_id netsale.ord_id%TYPE;
BEGIN
SELECT MAX(ord_id) INTO v_ord_id FROM netsale
WHERE mallid = pi_mallid AND tb_ord_id = pi_tb_ord_id ;
UPDATE netsales
SET picture = pi_picture
WHERE ord_id = v_ord_id AND goodsid = pi_goodsid;
IF SQL%ROWCOUNT <> 1 THEN
po_result :=101;
po_message := '插入图片失败';
ELSE
po_result := 0;
po_message :='SUCCESS';
END IF;
EXCEPTION WHEN OTHERS THEN
po_result :=401;
po_message := SQLERRM;
RETURN;
END;
JAVA数据保存
public Map
updatepicture(Map
map) throws Exception {
Map
outMap = new HashMap
();
synchronized (dao) {
DBConnect conn = new DBConnect();
try {
//获取链接部分有封装,但在该代码段中不是重点
conn.init(null, E_DB_Name.e_db_rmsdb.value(), false);
String po_result="";
String po_msg="";
String sql = "{call updatepicture(?,?,?,?,?,?)}";
java.sql.CallableStatement ps = conn.prepareCall(sql);
ps.setString(1, map.get("PI_MALLID")+"");
ps.setString(2, map.get("PI_TB_ORD_ID")+"");
ps.setString(3, map.get("PI_GOODSID")+"");
byte[] b=(byte[])map.get("PI_PICTURE");
InputStream is = new ByteArrayInputStream(b);
//设置BLOB类型的值
ps.setBinaryStream(4, is, b.length);
ps.registerOutParameter(5, java.sql.Types.VARCHAR);
ps.registerOutParameter(6, java.sql.Types.VARCHAR);
boolean flag=ps.execute();
//输出out值
System.out.println(ps.getString(5));
System.out.println(ps.getString(6));
} catch (SQLException e) {
conn.rollback();
e.printStackTrace();
throw new Exception(e.getMessage());
} catch (Exception e) {
conn.rollback();
e.printStackTrace();
} finally {
conn.close();
}
}
return outMap;
}
将Base64格式的图片字符串转成byte[],并执行存储过程保存
import sun.misc.BASE64Decoder;
public class Base64ToByte {
public static void main(String[] args) throws Exception {
String base64ImgStr="";//此为base64格式的字符串
//判断图片的格式
String data = "data:image";
int sIndex = base64ImgStr.indexOf(data);
String chanImgStr = base64ImgStr.substring(sIndex+data.length());
int eIndex = chanImgStr.indexOf(";");
String suffix = chanImgStr.substring(1, eIndex);
String base = "base64,";
int bIndex = base64ImgStr.indexOf(base);
base64ImgStr = base64ImgStr.substring(bIndex + 7);
BASE64Decoder decoder = new BASE64Decoder();
byte[] imgbyte = decoder.decodeBuffer(base64ImgStr);
Map
map =new HashMap
();
map.put("PI_MALLID", "1");
map.put("PI_TB_ORD_ID", "12016091720000000000");
map.put("PI_GOODSID", "74445901");
map.put("PI_PICTURE", imgbyte);
Map
rm=new SqlTest.updatepicture(map);
}
}
另外一个是通过图片url地址获取byte[],之后保存成Blob类型数据
import java.io.IOException;
import java.io.InputStream;
import java.net.HttpURLConnection;
import java.net.URL;
import java.util.HashMap;
import java.util.Map;
import java.io.ByteArrayOutputStream;
import java.io.ByteArrayInputStream;
public class PicUrlSaveBlob {
public Map
updatepicture(Map
map) throws Exception {
Map
outMap = new HashMap
();
HttpURLConnection urlconn =null;
InputStream inStream=null;
InputStream is=null;
synchronized (dao) {
DBConnect conn = new DBConnect();
try {
//获取数据链接部分有封装,但在该代码段中不是重点,请用自己方法获取数据库连接
conn.init(null, E_DB_Name.e_db_rmsdb.value(), false);
String po_result="";
String po_msg="";
String sql = "{call updatepicture(?,?,?,?,?,?)}";
java.sql.CallableStatement ps = conn.prepareCall(sql);
ps.setString(1, map.get("PI_MALLID")+"");
ps.setString(2, map.get("PI_TB_ORD_ID")+"");
ps.setString(3, map.get("PI_GOODSID")+"");
//设置BLOB类型的值
URL url=new URL("http://127.0.0.1/img/1.jpg");
urlconn = (HttpURLConnection)url.openConnection();
urlconn.setRequestMethod("GET");
urlconn.setConnectTimeout(5 * 1000);
inStream = urlconn.getInputStream();//通过输入流获取图片数据
byte[] b=inputStreamToByte(inStream);
is = new ByteArrayInputStream(b);
ps.setBinaryStream(4, is, b.length);
ps.registerOutParameter(5, java.sql.Types.VARCHAR);
ps.registerOutParameter(6, java.sql.Types.VARCHAR);
boolean flag=ps.execute();
//输出out值
System.out.println(ps.getString(5));
System.out.println(ps.getString(6));
} catch (SQLException e) {
conn.rollback();
e.printStackTrace();
throw new Exception(e.getMessage());
} catch (Exception e) {
conn.rollback();
e.printStackTrace();
} finally {
conn.close();
}
}
return outMap;
}
public static byte[] inputStreamToByte(InputStream is) throws IOException {
ByteArrayOutputStream bytestream = new ByteArrayOutputStream();
int ch;
while ((ch = is.read()) != -1) {
bytestream.write(ch);
}
byte imgdata[] = bytestream.toByteArray();
bytestream.close();
return imgdata;
}
}