JDBC读写oracle大字段BLOB

技术2022-05-20 33

一 JDBC将大字段BLoB内容写入到Oracle数据库(写大字段BLOB)

例子的详细信息参照为技改大修批量生成数据写的小工具

1.1 构建界面MyNotePad.java

package test;

import java.awt.BorderLayout;

import java.awt.Container;

import java.awt.GridLayout;

import java.awt.event.ActionEvent;

import java.awt.event.ActionListener;

import java.io.File;

import javax.swing.ButtonGroup;

import javax.swing.JButton;

import javax.swing.JFileChooser;

import javax.swing.JFrame;

import javax.swing.JLabel;

import javax.swing.JOptionPane;

import javax.swing.JPanel;

import javax.swing.JRadioButton;

import javax.swing.JScrollPane;

import javax.swing.JTextArea;

import javax.swing.JTextField;

public class MyNotePad extends JFrame {

private JTextField filenameTf = new JTextField(),dirTf = new JTextField(),xmmcTf = new JTextField(),userNameTf = new JTextField();

private JLabel fileNameLabel = new JLabel("文件名："),filePathLable= new JLabel("文件路径："),xmmcLable = new JLabel("项目名称："),userNameLabel=new JLabel("账号：");

private JButton openBt = new JButton("选择可研报告");

private JButton saveBt = new JButton("生成项目");

private JPanel optPane = new JPanel();

private JPanel navigatePane = new JPanel();

private JTextArea contentTa = new JTextArea(5,20);

private JRadioButton rb1 = new JRadioButton("技改",true), rb2 = new JRadioButton("大修");

private ButtonGroup group= new ButtonGroup();

private String lx="技改";

public static boolean isReady = false;

public MyNotePad(String title)

{

super(title);

openBt.addActionListener(new OpenHandler());

optPane.add(openBt);

saveBt.addActionListener(new SaveHandler());

optPane.add(saveBt);

dirTf.setEditable(false);

filenameTf.setEditable(false);

group.add(rb1);group.add(rb2);

rb1.addActionListener(listener2);

rb2.addActionListener(listener2);

navigatePane.setLayout(new GridLayout(5,2));

navigatePane.add(fileNameLabel);

navigatePane.add(filenameTf);

navigatePane.add(filePathLable);

navigatePane.add(dirTf);

navigatePane.add(xmmcLable);

navigatePane.add(xmmcTf);

navigatePane.add(rb1);

navigatePane.add(rb2);

navigatePane.add(userNameLabel);

navigatePane.add(userNameTf);

Container contentPane = this.getContentPane();

contentPane.add(navigatePane,BorderLayout.NORTH);

contentPane.add(optPane,BorderLayout.SOUTH);

contentPane.add(new JScrollPane(contentTa),BorderLayout.CENTER);

this.setSize(600,600);

this.setVisible(true);

this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

}

private ActionListener listener2 = new ActionListener(){

public void actionPerformed(ActionEvent actionEvent) {

JRadioButton rb = (JRadioButton)actionEvent.getSource();

lx = rb.getText();

}

};

class OpenHandler implements ActionListener {

public void actionPerformed(ActionEvent actionEvent) {

JFileChooser jc = new JFileChooser();//文件选择

int rVal = jc.showOpenDialog(MyNotePad.this );

if( rVal==JFileChooser.APPROVE_OPTION){

File dir = jc.getCurrentDirectory();//文件路径

File file = jc.getSelectedFile();

filenameTf.setText(file.getName());

dirTf.setText(dir.toString());

isReady = true;

}

class SaveHandler implements ActionListener{

public void actionPerformed(ActionEvent actionEvent) {

if( isReady){

String fileName = getFilenameTf().getText();

String fileDir = getDirTf().getText();

String xmmc = xmmcTf.getText();

System.out.println("xmmc="+xmmc+"xmmc.length="+xmmc.length());

if( xmmc==null||xmmc.trim()==""||xmmc.length()<=0)

{

JOptionPane.showMessageDialog(MyNotePad.this, "请先输入项目名称");

return;

}

String userName = userNameTf.getText();

if( userName==null||userName.trim()==""||userName.length()<=0)

{

JOptionPane.showMessageDialog(MyNotePad.this, "请先输入人员账号");

return;

}

//fileDir+"//"+fileName是文件路径，加上文件的名称

if( CreateXM.createDxxm(lx,fileDir+"//"+fileName,fileName,10,userName,contentTa,xmmc)){

JOptionPane.showMessageDialog(MyNotePad.this, "批量生成项目成功");

}else

{

JOptionPane.showMessageDialog(MyNotePad.this, "批量生成项目失败");

}

}else

{

JOptionPane.showMessageDialog(MyNotePad.this, "请先选择可研报告");

return;

}

public JTextField getFilenameTf() {

return filenameTf;

}

public JTextField getDirTf() {

return dirTf;

}

public static void main(String []args)

{

new MyNotePad("批量生成项目");

//System.out.println("测试Helloword!");

}

1.2 实现生成项目的功能(包括传入大字段数据Blob到数据库的功能)

package test;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

import java.util.Random;

import javax.swing.JTextArea;

/**

* 批量生成技改大修项目

* 主要的功能点：可研报告文件存储在数据库中

* @author fy

public class CreateXM {

public static boolean createDxxm(String jgOrDxString ,String filePath,String fileName,int sl,String userName,JTextArea jTextArea,String xmmcPre){

Connection conn=null;

try {

conn = ConnectionFactory.getConnection();

} catch (Exception e3) {

PrintComm.println("数据库连接失败", jTextArea);

return false;

}

if( conn==null)

{

PrintComm.println("数据库连接失败", jTextArea);

return false;

}

SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm");

Date now = new Date();

String bzsj = df.format(now);

Statement psmt = null;

ResultSet rs = null;

String xmlx = "";

String zylb = "";

String zyxf ="";

String gzyy="";//改造原因

String[] xmlxArr ={"70101","70102","70103"};

List<String> zylbArr =new ArrayList<String>();//{"70201","70202","70203","70299"};

zylbArr.add("70201");

zylbArr.add("70202");

zylbArr.add("70203");

zylbArr.add("70299");

List<String> gzyyList = null;

Random random = null;

String kybgAttr = "5F4EBE72-B09C-4AD6-A623-085B9F2AE982";

String bzr = "";

String bzbm = "";

String userID = "";

String dsgs ="";

String wsgs="";

String qygs="";

String selBzrSql = "select empid,deptid,user_id from mw_app.mwv_om_userinfo userinfo where userinfo.user_name='"+userName+"' ";

try {

psmt = conn.createStatement();

rs = psmt.executeQuery(selBzrSql);

if(rs.next())

{

bzr = rs.getString(1);

bzbm = rs.getString(2);

userID = rs.getString(3);

}

} catch (SQLException e2) {

e2.printStackTrace();

}

PrintComm.println("bzr="+bzr+",bzbm="+bzbm+",userID="+userID, jTextArea);

String dldWsql = "select frdw.obj_id, frdw.mc, frdw.dwjb, dwjb.JBMC from (select obj_id, mc, dwjb from mw_sys.MWT_PD_DEPS ";

dldWsql +="where SFCX='63785359-B0D9-4548-8F18-4F8FD682D684' and dwjb is not null and SFDLDW = 'T' start with obj_id = (select lsbm from mw_sys.MWT_PD_EMPS where ";

dldWsql +="sysuserid ='"+userID+"' and ZZQK='00F94133-B7A4-42E1-8C0B-3639993F5486' ) connect by prior sjbm = obj_id)frdw,mw_app.MWT_UD_GY_DWJB dwjb ";

dldWsql +="where dwjb.JBBH = frdw.dwjb and frdw.dwjb < 8 order by dwjb desc ";

try {

rs = psmt.executeQuery(dldWsql);

while(rs.next())

{

String dwjb=rs.getString(3);

System.out.println("dwjb==="+dwjb);

if( dwjb.equals("4"))//单位级别是地市级电网的

{

dsgs = rs.getString(1);

System.out.println("dwjb.equals(/"4/"),dsgs="+dsgs);

}

if(dwjb.equals("3"))//单位级别是网省公司的

{

wsgs = rs.getString(1);

System.out.println("dwjb.equals(/"3/"),wsgs="+wsgs);

}

if(dwjb.equals("2"))//单位级别是区域公司的

{

qygs = rs.getString(1);

System.out.println("dwjb.equals(/"2/"),qygs="+qygs);

}

} catch (SQLException e2) {

e2.printStackTrace();

}

if( "技改"==jgOrDxString)

{

kybgAttr="594F3551-3F2E-456C-B5BF-AD029BCD2FF4";

zylbArr.add("70204");

String gzyySql = "select bzdm from MW_APP.MWT_UD_GG_DM　WHERE DMLB = '723' ";

try {

psmt = conn.createStatement();

rs = psmt.executeQuery(gzyySql);

gzyyList = new ArrayList<String>();

while(rs.next())

{

gzyyList.add(rs.getString(1));

}

random = new Random();

int gzyyIndex = random.nextInt(gzyyList.size()-1);

System.out.println("gzyyIndex==="+gzyyIndex);

PrintComm.println("gzyyIndex="+gzyyIndex, jTextArea);

gzyy = gzyyList.get(gzyyIndex);

} catch (SQLException e1) {

e1.printStackTrace();

}

String sfcyh="T";//是否差异化补强改造

List<String> zyxfList = null;

try {

for( int i = 1;i<=sl;i++){

String xmmc = bzsj+xmmcPre+"-"+i;

System.out.println("xmmc="+xmmc);

PrintComm.println("xmmc="+xmmc,jTextArea);

random = new Random();

int xmlxIndex = random.nextInt(xmlxArr.length-1);

xmlx = xmlxArr[xmlxIndex];

random = new Random();

int zylbIndex = random.nextInt(zylbArr.size()-1);

zylb=zylbArr.get(zylbIndex);

String sql = "SELECT DM, DMMC FROM MW_APP.MWT_UD_GG_DM　WHERE DMLB = '703' and dm<>'70305' and bzdm like (select bzdm from mw_app.mwt_ud_gg_dm where dm = '"+zylb+"')||'%'";

psmt = conn.createStatement();

rs = psmt.executeQuery(sql);

zyxfList = new ArrayList<String>();

while(rs.next())

{

zyxfList.add(rs.getString(1));

}

random = new Random();

if( zyxfList.size()==1)

{

zyxf = zyxfList.get(0);

}else if( zyxfList.size()>1)

{

int zyxfIndex = random.nextInt(zyxfList.size()-1);

zyxf = zyxfList.get(zyxfIndex);

}

String dydj = "13";

String dxmd="70403";

String zycd = "70602"; String zcxz= "00403";

String xmzt = "71102"; String qtxmsx = ""; String sfnw = "F"; String sfkqdwxm = "F";

String xmid = "";

int ztz = 0;

psmt = conn.createStatement();

rs = psmt.executeQuery("select mw_sys.newguid||'-'||substr('0000'||rownum,-5,5), round(dbms_random.value(400,1)) from dual");

if(rs.next()){

xmid = rs.getString(1);

ztz = rs.getInt(2);

}

PrintComm.println("xmid="+xmid+",ztz="+ztz, jTextArea);

String insertSql = "insert into mw_app.mwt_ud_jgdx_dxxmk (obj_id,obj_dispidx,bzr,bzbm,bzsj,xmmc,kb,znhbdz,xmlx,zylb,zyxf,dydj,dxmd,zycd,xmkssj,xmwcsj,zcxz,ztz,jhnd,xmzt,qtxmsx,sfnw,sfkqdwxm,xmnr,wsgs,dsgs,qygs) ";

insertSql+="select '"+xmid+"', mw_sys.mwq_obj_dispidx.nextval,'"+bzr+"','"+bzbm+"',sysdate,'"+xmmc+"','1' ,0 ";

insertSql+=",'"+xmlx+"','"+zylb+"','"+zyxf+"','"+dydj+"','"+dxmd+"','"+zycd+"',to_date('2012-01','yyyy-MM'),to_date('2012-12','yyyy-MM'),'"+zcxz+"','"+ztz+"','2012','"+xmzt+"','"+qtxmsx+"','"+sfnw+"','"+sfkqdwxm+"','"+xmmc+"','"+wsgs+"','"+dsgs+"','"+qygs+"' ";

insertSql+=" from dual";

if( "技改"==jgOrDxString){

insertSql = "insert into mw_app.mwt_ud_jgdx_jgxmk (TGDWSSNL,obj_id,obj_dispidx,bzr,bzbm,bzsj,xmmc,kb,znhbdz,xmlx,zylb,zyxf,dydj,gzmd,zycd,xmkssj,xmwcsj,zcxz,ztz,bznd,xmzt,qtxmsx,sfnw,sfkqdwxm,xmnr,wsgs,jglx,lxyj,bz,sfknxm,dsgs,qygs) ";

insertSql+="select 1,'"+xmid+"', mw_sys.mwq_obj_dispidx.nextval,'"+bzr+"','"+bzbm+"',sysdate,'"+xmmc+"','1' ,0 ";

insertSql+=" from dual";

String insertNdtzSql = "insert into mw_app.mwt_ud_jgdx_ndtzqk(obj_id,obj_dispidx,xmid,nd,zjjh) ";

insertNdtzSql+=" select mw_sys.newguid||'-'||substr('0000'||rownum,-5,5),mw_sys.mwq_obj_dispidx.nextval , '"+xmid+"','2012','"+ztz+"' from dual ";

psmt.addBatch(insertNdtzSql);

}

PrintComm.println("insertSql="+insertSql, jTextArea);

psmt.addBatch(insertSql);

psmt.executeBatch();

if ("70202"==zylb||"70203"==zylb){//如果是电网一次和二次系统要添加规模

insertSql = "insert into mw_app.mwt_ud_jgdx_gzgm (obj_id,obj_dispidx,xmid,dydj,jldw,sl,zjjh)";

insertSql+="select mw_sys.newguid||'-'||substr('0000'||rownum,-5,5),mw_sys.mwq_obj_dispidx.nextval,'"+xmid+"',2, '台',3,"+ztz+" from dual";

System.out.println("insertSql="+insertSql);

psmt.execute(insertSql);

}

File imagFile = new File(filePath);//根据文件的完整路径名，得到File类型的文件

String vfile_id = "";

psmt = conn.createStatement();

rs = psmt.executeQuery("select mw_sys.newguid from dual");

if(rs.next()){

vfile_id = rs.getString(1);

}

String insertVfile = "insert into mw_sys.mwt_is_vfile(vfile_id, vdir_id, vfile_name, vfile_dispidx, vfile_ctime, vfile_mtime, vfile_sn)";

insertVfile+="select '"+vfile_id+"','fd991f0d-0871-4efe-bb4e-0c7d26a4fac8','"+fileName+"','12345',sysdate,sysdate,20188 from dual";

PrintComm.println("往vfile表中插入数据：insertVfile="+insertVfile,jTextArea);

psmt.executeQuery(insertVfile);

String insertFsdata = "insert into mw_sys.mwt_om_fsdata(obj_id,attr_id,vfile_id,fsdata_data) values(";

insertFsdata+="'"+xmid+"','"+kybgAttr+"','"+vfile_id+"',?)";

PrintComm.println("往fsdata表中插入数据: insertFsdata="+insertFsdata,jTextArea);

PreparedStatement pstmt = null;

pstmt = conn.prepareStatement(insertFsdata);

pstmt.setBinaryStream(1, new FileInputStream(filePath),(int)imagFile.length());//将可研报告的文件存储在数据库中

pstmt.executeUpdate();

String kybgSql = "update mw_app.mwt_ud_jgdx_dxxmk set kybg=? where obj_id ='"+xmid+"'";

if( "技改"==jgOrDxString){

kybgSql = "update mw_app.mwt_ud_jgdx_jgxmk set kybg=? where obj_id ='"+xmid+"'";

}

PrintComm.println(kybgSql,jTextArea);

pstmt = conn.prepareStatement(kybgSql);

pstmt.setString(1, fileName);

pstmt.executeUpdate();

}//end of for

} catch (SQLException e) {

e.printStackTrace();

return false;

} catch (FileNotFoundException e) {

e.printStackTrace();

return false;

}finally{

try {rs.close();} catch (SQLException e) {e.printStackTrace();}

try {psmt.close();} catch (SQLException e) {e.printStackTrace();}

try {conn.close();} catch (SQLException e) {e.printStackTrace();}

}

return true;

}

1.3自定义的输出工具类（用于将后台信息输出到界面中)

package test;

import javax.swing.JTextArea;

public class PrintComm {

public PrintComm(){

}

public static void println(String msg,JTextArea jTextArea)

{

jTextArea.append(msg+"/n");

}

1.4获得数据库连接的工具类ConnectionFactory.java

package test;

import java.io.InputStream;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.util.Properties;

public class ConnectionFactory {

public static Properties config = new Properties();

static{

try {

InputStream in = ConnectionFactory.class.getClassLoader()

.getResourceAsStream("db.txt");//读取配置文件

config.load(in);

in.close();

} catch (Exception e) {

}

public static Connection getConnection() throws ClassNotFoundException, SQLException{

Connection conn = null;

System.out.println("getDriver()="+getDriver());

Class.forName(getDriver());

conn = DriverManager.getConnection(getDbUrl(),getUsername(),getPassword());

return conn;

}

public static String getDriver(){

return config.getProperty("driver");

}

public static String getDbUrl(){

return config.getProperty("dburl");

}

public static String getUsername(){

return config.getProperty("username");

}

public static String getPassword(){

return config.getProperty("password");

}

1.5供ConnectionFactory使用的配置文件db.txt

driver=oracle.jdbc.driver.OracleDriver

dburl=jdbc:oracle:thin:@192.168.43.20:1521:spms

username=mw_sys

password=sys

二将数据库中的大字段BLOB的内容读出到文件中（读取大字段BLOB）

public byte[] getBlob(String memberID) {

String query = "select fsdata_data from mw_sys.mwt_om_fsdata where obj_id ='"+memberID+"'";

Statement stmt =null;

ResultSet rs = null;

Blob blob = null;

byte[] bytes = null;

BufferedOutputStream stream = null; //读取的文件流

try {

stmt = conn.createStatement();

rs = stmt.executeQuery(query);

if( rs.next()){

blob = rs.getBlob(1);

}

bytes = blob.getBytes(1, (int)(blob.length()));

File file = new File("E://test3.jpg");//文件路径

FileOutputStream fstream = new FileOutputStream(file);

stream = new BufferedOutputStream(fstream);

stream.write(bytes);

stream.close();

} catch (SQLException e) {

e.printStackTrace();

} catch (FileNotFoundException e) {

e.printStackTrace();

} catch (IOException e) {

e.printStackTrace();

}finally{

try {

rs.close();

} catch (SQLException e) {

e.printStackTrace();

}

try {

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

return bytes;

}

public static void main(String []args){

LobServlet lob = new LobServlet();

lob.getBlob("EEDF4A05-E408-4804-9BE4-69878AA80D67-00001");

}

三读取数据库中的大字段数据到浏览器端显示(不完善的版本）

3.1 BlobTest.jsp

<%@ page language="java" contentType="text/html; charset=gbk"

pageEncoding="gbk"%>

<html>

<head>

</head>

<body>

</form>

</body>

</html>

3.2 LobServlet.java

package servlests;

import java.io.BufferedOutputStream;

import java.io.File;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.io.IOException;

import java.sql.Blob;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import javax.servlet.ServletException;

import javax.servlet.ServletOutputStream;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import utils.ConnectionFactory;

public class LobServlet extends HttpServlet {

/**

private static final long serialVersionUID = 1L;

private static Connection conn = null;

static {

try {

conn = ConnectionFactory.getConnection();

System.out.println("conn="+conn);

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

@Override

protected void doGet(HttpServletRequest request, HttpServletResponse response)

throws ServletException, IOException {

ServletOutputStream out = response.getOutputStream();

String memberID= "6ED4140F-6D18-496C-B391-1A12953B8910-00001";

out.write(getBlob(memberID));

out.flush();

out.close();

}

public byte[] getBlob(String memberID) {

String query = "select fsdata_data from mw_sys.mwt_om_fsdata where obj_id ='"+memberID+"'";

Statement stmt =null;

ResultSet rs = null;

Blob blob = null;

byte[] bytes = null;

try {

stmt = conn.createStatement();

rs = stmt.executeQuery(query);

if( rs.next()){

blob = rs.getBlob(1);

}

bytes = blob.getBytes(1, (int)(blob.length()));

} catch (SQLException e) {

e.printStackTrace();

} finally{

try {

rs.close();

} catch (SQLException e) {

e.printStackTrace();

}

try {

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

return bytes;

}

3.3 配置文件web.xml

<servlet-name>LobServletTest</servlet-name>

<servlet-class>servlests.LobServlet</servlet-class>

</servlet>

<servlet-mapping>

<servlet-name>LobServletTest</servlet-name>

<url-pattern>/LobServletTest</url-pattern>

</servlet-mapping>

注：以上例子中不完善之处

（1）在点击页面上的按钮后，才会显示大字段文件的内容。改进错误：让一进入页面就自动显示文件的内容

(2)第一次访问的时候能够正常显示，再次访问的时候，就会报nullpointexception，数据库连接已关闭的错误；

分析原因为：在以上的LobServlet.java中，Connection conn是声明为静态变量的，而且在每次访问完成后就会自动关闭该连接，所以造成再次访问的时候，Connection conn已经关闭了。继续晚上见主题四

四

专利

最新回复(0)