- java操作clob字段
- 在oracle数据库中如何插入CLOB值 oracle中最常用的varcher2类型最多只能存储4000个字节的内容,一般情况下是能够满足用户的需求的。但是在一些特殊情况下(如要存储图片或者要存储的内容超过了4000个字节),varcher2就满足不了这个需求了。这个时候我们可以借助于oracle里面的大字段CLOB,后者 BLOB。举例如下: 首先,在数据库中建一张表news,为了简单起见,只有一个字段content(CLOB)。做好准备工作后就可以开始我们的CLOB之旅了。 以下是插入CLOB的代码:
- import java.sql.*;
- import java.io.*;
- public class TestClob{
- public void TestClob(){}
- public static void main(String args[]){
- try{
- Class.forName("oracle.jdbc.driver.OracleDriver");
- Connectionconn=DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1521:ora32","tjpt","tjpt"); conn.setAutoCommit(false); //第一步:插入一个空的CLOB
- String sql1="insert into news(content,id) values (EMPTY_CLOB(),'1')";
- PreparedStatement ps1=conn.prepareStatement(sql1);
- ps1.executeUpdate(); ps1.close(); //第二步:取出该CLOB
- String sql2="select content from news for update";
- PreparedStatement ps2=conn.prepareStatement(sql2);
- ResultSet rs2=ps2.executeQuery();
- while (rs2.next()){
- oracle.sql.CLOB clob=(oracle.sql.CLOB)rs2.getClob(1);
- BufferedWriter out=new BufferedWriter(clob.getCharacterOutputStream());
- String content="1234";//假定这是新闻的内容,当然可以也可以是其他的内容
- out.write(content,0,content.length());
- out.close();
- }
- conn.commit();
- } catch(Exception e){e.printStackTrace();}
- }
- }
- 既然插入进去了,那我们还得要检验一下:插进去的是不是你想插进去的内容?以下就是读取CLOB的代码: import java.sql.*;
- import java.io.*;
- public class ReadClob{
- public void ReadClob(){}
- public static void main(String args[]){
- try{
- Class.forName("oracle.jdbc.driver.OracleDriver");
- Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ora32","tjpt","tjpt"); String sql1="select content from news";
- PreparedStatement ps1=conn.prepareStatement(sql1);
- ResultSet rs1=ps1.executeQuery();
- while (rs1.next()){
- oracle.sql.CLOB clob=(oracle.sql.CLOB)rs1.getClob(1);
- BufferedReader in=new BufferedReader(clob.getCharacterStream());
- StringWriter out=new StringWriter();
- int c;
- while((c=in.read())!=-1){
- out.write(c);
- }
- String content=out.toString();
- System.out.println (content);//输出CLOB内容 }
- } catch(Exception e){e.printStackTrace();
- }
- }
- }
- 例题2:插入
- //ReportEntity 存放数据的bean
- public boolean AddMonth_Report(ReportEntity re) throws Exception {
- Connection conn = null;
- Statement st = null;
- ResultSet rs = null;
- ////这个sql是先插入空值,占个位子
- String sql =
- "insert into staff_monthform(id,memb_id,m_plan,m_summery,m_date) values"
- + " (SEQ_MID.nextval,'" + re.getMEMB_ID()+
- "',empty_clob(),empty_clob(),sysdate)";
- //clob型的字段要先以empty_clob()空值插入;然后用update把数据修改进去
- try {
- conn = ConnectionPool.getConnection();//连接池
- conn.setAutoCommit(false);
- st = conn.createStatement();
- st.executeUpdate(sql);
- //这个sql执行的是update,先将该条记录锁定,再修改数据
- String sql2 = "select m_plan from staff_monthform where memb_id='" +
- re.getMEMB_ID() + "' "
- +
- " and to_char(M_DATE,'mm')=to_char(sysdate,'mm') for update";
- rs = st.executeQuery(sql2);
- oracle.sql.CLOB clobtt = null;
- if (rs.next()) {
- try {
- oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getObject(1); //clob
- Writer outstream = clob.getCharacterOutputStream(); //字符 输出流
- outstream.write(new String(re.getM_PLAN().getBytes(
- "ISO8859_1"))); //写
- outstream.close();
- } catch (Exception ex) {
- ex.printStackTrace();
- }
- return true;
- }
- conn.commit();
- } catch (Exception ex) {
- ex.printStackTrace();
- } finally {
- try {
- if (rs != null) {
- rs.close();
- }
- if (st != null) {
- st.close();
- }
- if (conn != null) {
- ConnectionPool.close(conn);
- }
- } catch (Exception sqlex) {
- Logger.error(sqlex.getMessage());
- }
- }
- return false;
- }
- 在s2sh操作clob字段时,更新的时候容易出现这种错误:
- 不允许的操作: streams type cannot be used in batching
- 解决方法如下:
- 近日程序的数据库转为oracle 在操作blob类型时报以下错误:不允许的操作: streams type cannot be used in batching。经过查找多方面的资料查证Oracle JDBC不允许流操作以批量方式执行(Oracle CLOB采用流机制作为数据读写方式)。 只需在spring配置文件中更改hibernate jdbc.batch_size为0即可。
- <bean id="oracleSessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
- <property name="dataSource">
- <ref bean="oracleDataSource"/>
- </property>
- <property name="hibernateProperties">
- <props>
- <prop key="hibernate.dialect">org.hibernate.dialect.Oracle9Dialect</prop>
- <prop key="connection.provider_class">
- org.hibernate.connection.C3P0ConnectionProvider
- </prop>
- <prop key="hibernate.show_sql">true</prop>
- <prop key="hibernate.cache.use_query_cache">true</prop>
- <prop key="hibernate.cache.provider_class">
- org.hibernate.cache.EhCacheProvider
- </prop>
- <prop key="hibernate.jdbc.batch_size">0</prop>
- </props>
- </property>
- <bean id="oracleSessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean"> <property name="dataSource"> <ref bean="oracleDataSource"/> </property> <property name="hibernateProperties"> <props> <prop key="hibernate.dialect">org.hibernate.dialect.Oracle9Dialect</prop> <prop key="connection.provider_class"> org.hibernate.connection.C3P0ConnectionProvider </prop> <prop key="hibernate.show_sql">true</prop> <prop key="hibernate.cache.use_query_cache">true</prop> <prop key="hibernate.cache.provider_class"> org.hibernate.cache.EhCacheProvider </prop> <prop key="hibernate.jdbc.batch_size">0</prop> </props> </property>