- 浏览: 574117 次
- 性别:
- 来自: 上海
文章分类
- 全部博客 (174)
- JBPM (3)
- WWF (0)
- JavaScript (11)
- J2EE (40)
- OperationSystem (11)
- 数据库 (12)
- CSS (1)
- Ajax (2)
- J2SE (30)
- Tools (10)
- 服务器中间件 (3)
- 异常 (0)
- Flex (5)
- jQuery (11)
- html (9)
- Ejb (1)
- HTML5 Shiv–让该死的IE系列支持HTML5吧 (1)
- Spring (9)
- Quartz (3)
- log4j (1)
- maven (1)
- cpdetector (1)
- JSON (1)
- log4jdbc (1)
- asm (8)
- FusionCharts (1)
- jqplot (1)
- highcharts (1)
- excanvas (1)
- html5 (1)
- jpcap介绍 (1)
- weblogic (3)
- URLURLClassLoader (0)
- URLClassLoader (1)
- ant (2)
- ivy (2)
- nexus (1)
- IT (0)
- LoadRunner (1)
- SCSS (1)
- ruby (1)
- webstorm (1)
- typescript (1)
- Jboss7 (1)
- wildfly (1)
- oracle (5)
- esb (0)
- dubbo (2)
- zookeeper (3)
- eclipse (1)
- Android (2)
- Studio (1)
- Google (1)
- 微信 (1)
- 企业号 (1)
- Linux (13)
- Oracle12c (1)
- Hadoop (1)
- InletexEMC (1)
- Windows (1)
- Netty (3)
- Marshalling (2)
- Protobuf (1)
- gcc (1)
- Git (1)
- GitLab (1)
- shell (2)
- java (3)
- Spring4 (1)
- hibernate4 (1)
- postgresql (1)
- ApacheServer (2)
- Tomcat (2)
- ApacheHttpServer (2)
- realvnc (1)
- redhat (7)
- vncviewer (1)
- LVS (4)
- LVS-DR (1)
- RedHat6.5 (5)
- LVS-NAT (1)
- LVS-IPTUNNEL (2)
- LVS-TUN (1)
- keepalived (2)
- yum (1)
- iso (1)
- VMware (1)
- redhat5 (1)
- ha (1)
- nginx (2)
- proguard (1)
- Mat (1)
- DTFJ (1)
- axis2 (1)
- web service (1)
- centos (1)
- random (1)
- urandom (1)
- apache (1)
- IBM (1)
- cve (1)
- 漏洞 (1)
- JDBC (1)
- DataSource (1)
- jdk (1)
- tuxedo (2)
- wtc (1)
最新评论
-
skying007:
好资料,谢谢分享给啊
FusionCharts在服务器端导出图片(J2EE版) -
cgnnzg:
大神好 可以发一份源码给我学习么 多谢了 978241085 ...
springmvc+dubbo+zookeeper -
jifengjianhao:
求源码:854606899@qq.com
springmvc+dubbo+zookeeper -
wdloyeu:
shihuan8@163.com邮箱网盘在哪,没找到。能给份源 ...
Java Socket长连接示例代码 -
huangshangyuanji:
求代码:45613032@qq.com
springmvc+dubbo+zookeeper
一、MySQL与Oracle数据库如何处理Clob,Blob数据类型 (1)不通数据库中对应clob,blob的类型如下: MySQL中:clob对应text,blob对应blob DB2/Oracle中:clob对应clob,blob对应blob (2)domain中对应的类型: clob对应String,blob对应byte[] clob对应java.sql.Clob,blob对应java.sql.Blob (3)hibernate配置文件中对应类型: clob-->clob ,blob-->binary 也可以直接使用数据库提供类型,例如:oracle.sql.Clob,oracle.sql.Blob 二、jdbc操作clob(以oracle为例) 首先操作clob/blob不像操作varchar类型那样简单,插入步骤一般分为两步:第一步插入一个空值,第二步锁住此行,更新clob/blob字段。 //插入空值 conn.setAutoCommit(false); String sql = "INSERT INTO T_FILE(NAME, FILE_CONTENT) VALUES ('Jambhala', EMPTY_CLOB())"; PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.executeUpdate(); //锁住此行 String sql_lockstr = "SELECT FILE_CONTENT FROM T_FILE WHERE NAME='Jambhala' FOR UPDATE"; pstmt = conn.prepareStatement(sql_lockstr); ResultSet rs = pstmt.executeQuery(); oracle.sql.Clob clob = (oracle.sql.Clob)rs.getClob(1); java.io.OutputStream writer = clob.getAsciiOutputStream(); byte[] temp = newFileContent.getBytes(); writer.write(temp); writer.flush(); writer.close(); pstmt.close(); 读取内容: oracle.sql.Clob clob = rs.getClob("FILE_CONTENT"); if(clob != null){ Reader is = clob.getCharacterStream(); BufferedReader br = new BufferedReader(is); String s = br.readLine(); while(s != null){ content += s+"<br>"; s = br.readLine(); } } 三、jdbc操作blob conn.setAutoCommit(false); String sql = "INSERT INTO T_PHOTO(NAME, PHOTO) VALUES ('Jambhala', EMPTY_BLOB())"; pstmt = conn.prepareStatement(sql); pstmt = conn.executeUpdate(); sql = "SELECT PHOTO FROM T_PHOTO WHERE NAME='Jambhala'"; pstmt = conn.prepareStatement(sql); rs = pstmt.executeQuery(sql); if(rs.next()){ oracle.sql.Blob blob = (oracle.sql.Blob)rs.getBlob(1); } //write to a file File file=new File("C:\\test.rar"); FileInputStream fin = new FileInputStream(file); OutputStream out = blob.getBinaryOutputStream(); int count=-1,total=0; byte[] data = new byte[blob.getBufferSize()]; while((count=fin.read(data)) != -1){ total += count; out.write(data, 0, count); } 四、hibernate处理clob MyFile file = new MyFile(); file.setName("Jambhala"); file.setContent(Hibernate.createClob("")); session.save(file); session.flush(); session.refresh(file, LockMode.UPGRADE); oracle.sql.Clob clob = (oracle.sql.Clob)file.getContent(); Writer pw = clob.getCharacterOutputStream(); pw.write(longText); //写入长文本 pw.close(); session.close(); 五、使用hibernate处理blob 原理基本相同: Photo photo = new Photo(); photo.setName("Jambhala"); photo.setPhoto(Hibernate.createBlob("")); session.save(photo); session.flush(); session.refresh(photo, LockMode.UPGRADE); //锁住此对象 oracle.sql.Blob blob = photo.getPhoto(); //取得此blob的指针 OutputStream out = blob.getBinaryOutputStream(); //写入一个文件 File f = new File("C:\\test.rar"); FileInputStream fin = new FileInputStream(f); int count=-1,total=0; byte[] data = new byte[(int)fin.available()]; out.write(data); fin.close(); out.close(); session.flush(); String DRIVER = "oracle.jdbc.driver.OracleDriver"; //Oracle连接用URL private static final String URL = "jdbc:oracle:thin:@testora:1521:orac"; //用户名 private static final String USER = "scott"; //密码 private static final String PASSWORD = "pswd"; //数据库连接 private static Connection conn = null; //SQL语句对象 private static Statement stmt = null; //@roseuid 3EDA089E02BC public LobPros(){} //往数据库中插入一个新的Clob对象 //@param infile 数据文件 //@throws java.lang.Exception //@roseuid 3EDA089E02BC public static void clobInsert(String infile) throws Exception { //设定不自动提交 boolean defaultCommit = conn.getAutoCommit(); conn.setAutoCommit(false); try{ //插入一个空的Clob对象 stmt.executeUpdate("INSERT INTO TEST_CLOB VALUES ('111', EMPTY_CLOB())"); //查询此Clob对象并锁定 ResultSet rs = stmt.executeQuery("SELECT CLOBCOL FROM TEST_CLOB WHERE ID='111' FOR UPDATE"); while(rs.next()){ //取出此Clob对象 oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL"); //向Clob对象中写入数据 BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream()); BufferedReader in = new BufferedReader(new FileReader(infile)); int c; while((c=in.read()) != -1){ out.write(c); } in.close(); out.close(); } //正式提交 conn.commit(); }catch(Exception e){ //出错回滚 conn.rollback(); throw e; } //恢复原提交状态 conn.setAutoCommit(defaultCommit); } //修改Clob对象(是在原Clob对象基础上进行覆盖式的修改) //@param infile 数据文件 //@throws java.lang.Exception //@roseuid 3EDA089E02BC public static void clobModify(String infile) throws Exception { //设定不自动提交 boolean defaultCommit = conn.getAutoCommit(); conn.setAutoCommit(false); try{ //查询Clob对象并锁定 ResultSet rs = stmt.executeQuery("SELECT CLOBCOL FROM TEST_CLOB WHERE ID='111' FOR UPDATE"); while(rs.next()){ //获取此Clob对象 oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL"); //进行覆盖式修改 BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream()); BufferedReader in = new BufferedReader(new FileReader(infile)); int c; while ((c=in.read())!=-1) { out.write(c); } in.close(); out.close(); } //正式提交 conn.commit(); }catch(Exception e){ //出错回滚 conn.rollback(); throw e; } //恢复原提交状态 conn.setAutoCommit(defaultCommit); } //替换CLOB对象(将原CLOB对象清除,换成一个全新的CLOB对象 //@param infile 数据文件 //@throws java.lang.Exception //@roseuid 3EDA04BF01E1 public static void clobReplace(String infile) throws Exception { //设定不自动提交 boolean defaultCommit = conn.getAutoCommit(); conn.setAutoCommit(false); try{ //清空原CLOB对象 stmt.executeUpdate("UPDATE TEST_CLOB SET CLOBCOL=EMPTY_CLOB() WHERE ID='111'"); //查询CLOB对象并锁定 ResultSet rs = stmt.executeQuery("SELECT CLOBCOL FROM TEST_CLOB WHERE ID='111' FOR UPDATE"); while (rs.next()) { //获取此CLOB对象 oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL"); //更新数据 BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream()); BufferedReader in = new BufferedReader(new FileReader(infile)); int c; while ((c=in.read())!=-1) { out.write(c); } in.close(); out.close(); } //正式提交 conn.commit(); }catch(Exception e){ //出错回滚 conn.rollback(); throw e; } //恢复原提交状态 conn.setAutoCommit(defaultCommit); } //CLOB对象读取 //@param outfile 输出文件名 //@throws java.lang.Exception //@roseuid 3EDA04D80116 public static void clobRead(String outfile) throws Exception { //设定不自动提交 boolean defaultCommit = conn.getAutoCommit(); conn.setAutoCommit(false); try{ //查询CLOB对象 ResultSet rs = stmt.executeQuery("SELECT * FROM TEST_CLOB WHERE ID='111'"); while (rs.next()) { //获取CLOB对象 oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL"); //以字符形式输出 BufferedReader in = new BufferedReader(clob.getCharacterStream()); BufferedWriter out = new BufferedWriter(new FileWriter(outfile)); int c; while ((c=in.read())!=-1) { out.write(c); } out.close(); in.close(); } }catch(Exception e){ conn.rollback(); throw e; } //恢复原提交状态 conn.setAutoCommit(defaultCommit); } //向数据库中插入一个新的BLOB对象 //@param infile 数据文件 //@throws java.lang.Exception //@roseuid 3EDA04E300F6 public static void blobInsert(String infile) throws Exception { //设定不自动提交 boolean defaultCommit = conn.getAutoCommit(); conn.setAutoCommit(false); try { //插入一个空的BLOB对象 stmt.executeUpdate("INSERT INTO TEST_BLOB VALUES ('222', EMPTY_BLOB())"); //查询此BLOB对象并锁定 ResultSet rs = stmt.executeQuery("SELECT BLOBCOL FROM TEST_BLOB WHERE ID='222' FOR UPDATE"); while (rs.next()) { //取出此BLOB对象 oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob("BLOBCOL"); //向BLOB对象中写入数据 BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream()); BufferedInputStream in = new BufferedInputStream(new FileInputStream(infile)); int c; while ((c=in.read())!=-1) { out.write(c); } in.close(); out.close(); } //正式提交 conn.commit(); } catch (Exception e) { //出错回滚 conn.rollback(); throw e; } //恢复原提交状态 conn.setAutoCommit(defaultCommit); } //修改BLOB对象(是在原BLOB对象基础上进行覆盖式的修改) //@param infile 数据文件 //@throws java.lang.Exception //@roseuid 3EDA04E90106 public static void blobModify(String infile) throws Exception { //设定不自动提交 boolean defaultCommit = conn.getAutoCommit(); conn.setAutoCommit(false); try { //查询BLOB对象并锁定 ResultSet rs = stmt.executeQuery("SELECT BLOBCOL FROM TEST_BLOB WHERE ID='222' FOR UPDATE"); while (rs.next()) { //取出此BLOB对象 oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob("BLOBCOL"); //向BLOB对象中写入数据 BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream()); BufferedInputStream in = new BufferedInputStream(new FileInputStream(infile)); int c; while ((c=in.read())!=-1) { out.write(c); } in.close(); out.close(); } //正式提交 conn.commit(); } catch (Exception e) { //出错回滚 conn.rollback(); throw e; } //恢复原提交状态 conn.setAutoCommit(defaultCommit); } //替换BLOB对象(将原BLOB对象清除,换成一个全新的BLOB对象) //@param infile 数据文件 //@throws java.lang.Exception //@roseuid 3EDA0505000C public static void blobReplace(String infile) throws Exception { //设定不自动提交 boolean defaultCommit = conn.getAutoCommit(); conn.setAutoCommit(false); try { //清空原BLOB对象 stmt.executeUpdate("UPDATE TEST_BLOB SET BLOBCOL=EMPTY_BLOB() WHERE ID='222'"); //查询此BLOB对象并锁定 ResultSet rs = stmt.executeQuery("SELECT BLOBCOL FROM TEST_BLOB WHERE ID='222' FOR UPDATE"); while (rs.next()) { //取出此BLOB对象 oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob("BLOBCOL"); //向BLOB对象中写入数据 BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream()); BufferedInputStream in = new BufferedInputStream(new FileInputStream(infile)); int c; while ((c=in.read())!=-1) { out.write(c); } in.close(); out.close(); } //正式提交 conn.commit(); } catch (Exception e) { //出错回滚 conn.rollback(); throw e; } //恢复原提交状态 conn.setAutoCommit(defaultCommit); } //BLOB对象读取 //@param outfile 输出文件名 //@throws java.lang.Exception //@roseuid 3EDA050B003B public static void blobRead(String outfile) throws Exception { //设定不自动提交 boolean defaultCommit = conn.getAutoCommit(); conn.setAutoCommit(false); try { //查询BLOB对象 ResultSet rs = stmt.executeQuery("SELECT BLOBCOL FROM TEST_BLOB WHERE ID='222'"); while (rs.next()) { //取出此BLOB对象 oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob("BLOBCOL"); //以二进制形式输出 BufferedOutputStream out = new BufferedOutputStream(new FileOutputStream(outfile)); BufferedInputStream in = new BufferedInputStream(blob.getBinaryStream()); int c; while ((c=in.read())!=-1) { out.write(c); } in.close(); out.close(); } //正式提交 conn.commit(); } catch (Exception e) { //出错回滚 conn.rollback(); throw e; } //恢复原提交状态 conn.setAutoCommit(defaultCommit); } //建立测试用表格 //@throws Exception public static void createTables() throws Exception { try { stmt.executeUpdate("CREATE TABLE TEST_CLOB (ID NUMBER(3), CLOBCOL CLOB)"); stmt.executeUpdate("CREATE TABLE TEST_BLOB (ID NUMBER(3), BLOBCOL BLOB)"); } catch (Exception e) { } } //@param args - 命令行参数 //@throws java.lang.Exception //@roseuid 3EDA052002AC public static void main(String[] args) throws Exception { //装载驱动,建立数据库连接 Class.forName(DRIVER); conn = DriverManager.getConnection(URL,USER,PASSWORD); stmt = conn.createStatement(); //建立测试表格 createTables(); //CLOB对象插入测试 clobInsert("c:/clobInsert.txt"); clobRead("c:/clobInsert.out"); //CLOB对象修改测试 clobModify("c:/clobModify.txt"); clobRead("c:/clobModify.out"); //CLOB对象替换测试 clobReplace("c:/clobReplace.txt"); clobRead("c:/clobReplace.out"); //BLOB对象插入测试 blobInsert("c:/blobInsert.doc"); blobRead("c:/blobInsert.out"); //BLOB对象修改测试 blobModify("c:/blobModify.doc"); blobRead("c:/blobModify.out"); //BLOB对象替换测试 blobReplace("c:/blobReplace.doc"); blobRead("c:/bolbReplace.out"); //关闭资源退出 conn.close(); System.exit(0); }
- mysql和Oracle在对clob和blob字段的处理.zip (3.3 KB)
- 下载次数: 45
发表评论
-
JDK中的随机数机制探究
2017-05-31 21:25 1913今天有同事问起关于JDK1.8中默认是random问题 ... -
Spring4Mvc整合Hibernate4框架示例
2016-02-05 11:52 1360今天用NetBeans8.1搭建了一套Spring4MVC+H ... -
Spring4配置properties文件的一点儿细节
2016-02-04 15:40 2757http://repo.spring.io/release/o ... -
Oracle修改内存使用情况
2015-12-15 10:49 928[oracle@pamiddle ~]$ sqlplus / ... -
Oracle12c的相关命令及操作
2015-12-10 09:27 2864[oracle@pacollector ~]$ lsnrctl ... -
RedHat6.4安装Oracle12c 12.1.0.2.0
2015-12-07 16:32 2038[root@pacollector ~]# vi /etc/h ... -
Netty5 AIO
2015-11-25 21:18 1657工程结构图: TimeServer.java文件内容如下: ... -
JDK1.7 AIO
2015-11-24 22:32 1021工程结构图: Helper.java文件内容如下: p ... -
java JAVA_OPTS javaagent
2015-11-23 15:48 1476附件里java JAVA_OPTS -javaagent的程序 ... -
JavaWeb发送信息到微信公众平台的企业号
2015-11-01 22:42 3760首先到微信公众平台申请微信企业号: https://qy.we ... -
dubbo源代码调试
2015-09-24 17:06 4167今天同事要调试zookeeper+dubbo源代码,所以做了以 ... -
springmvc+dubbo+zookeeper
2015-09-22 11:34 11921笔者这里使用的是apache- ... -
静态方式把oracle实例配置到监听文件里
2015-08-21 21:25 1414本例Oracle的实例名字为parep。 原始的listen ... -
RedHat6.4安装Oracle11g 11.2.0.4
2015-07-25 22:36 2810[root@foglight ~]# vi /etc/host ... -
OracleAS10g安装配置与部署
2015-07-01 20:59 662先到Oracle官方下载OracleAS10的安装包,截图如下 ... -
any+ivy的使用
2014-11-29 16:25 2056javapro-用ivy编译版本.rar文件时示例代码。 i ... -
URLClassLoader初体验
2014-11-26 14:56 2147使用概要: File file = ... -
java和spring使用weblogic数据源配置
2014-10-13 11:29 2947需要weblogic.jar和jrmpclient.jar ... -
一套基本的SpringMvc工程所需要的最基本jar包(pom.xml)
2014-08-19 16:38 1878附件里是pom.xml文件。 -
html的form表单提交得不到参数问题
2014-07-25 16:13 1802今天同事问我他的form表单按submit方式提交,在Java ...
相关推荐
Mybatis 处理 CLOB、BLOB 类型数据
从oracle中的BLOB类型字段中取出照片,转存到mysql数据库中。思路是现将oracle中的照片存储在本地文件夹,在将本地图片上传到mysql中。
kettle通过java代码将数据库blob 字段抽取到本地文件
针对oracle中blob字段的操作,能批量快速的插入大字段,效率非常高
该代码通过配置的方式将mysql中的表数据迁移到oracle表中,代码可以灵活扩展,从而实现将blob、clob、text等字段的数据迁移。
给出在this时区=other时区的日期和时间 SQL> select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,to_char(new_time 2 (sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles from dual; BJ_TIME ...
我们前面已经指出Oracle的Lob字段和一般类型的字段在操作上有一个明显的区别--那就是你必须首先通过Oracle的empty_blob()/empty_clob()初始化Lob字段,然后获取该字段的引用,通过这个引用更改其值。所以要完成对...
Access 微软 Access是一种桌面数据库,只适合数据量少的应用,在处理少量 数据和单机访问的数据库时是很好的,效率也很高 小型企业 三、 Oracle数据库概述 ORACLE数据库系统是美国ORACLE公司(甲骨文)提供的以...
目前工具在Oracle 11g、Microsoft SQLServer 2012、MySQL 5.x、DB2 10.x、Sybase 15.x、PostgreSQL 9.x、Derby 10.x上通过了测试。 2、通过生成器创建BO类及DAO层代码 这是一个配合dbking的代码...