java操作oracle大字段CLOB

一、通过mybatis框架，不需要做特殊操作

查询

 1 <resultMap id="BaseResultMap" type="com.wondersgroup.view360.ui.imagelogo.model.SecurityImageLogo">
 2         <id column="ID" jdbcType="VARCHAR" property="id"/>
 3         <result column="IMAGECODE" jdbcType="VARCHAR" property="imagecode"/>
 4         <result column="IAMGENAME" jdbcType="VARCHAR" property="iamgename"/>
 5         <result column="CREATEUSER" jdbcType="VARCHAR" property="createuser"/>
 6         <result column="CREATETIME" jdbcType="TIMESTAMP" property="createtime"/>
 7         <result column="UPDATEUSER" jdbcType="VARCHAR" property="updateuser"/>
 8         <result column="UPDATETIME" jdbcType="TIMESTAMP" property="updatetime"/>
 9         <result column="IMAGEVALUE" jdbcType="CLOB" property="imagevalue"/>
10     </resultMap>
11 
12 <sql id="Base_Column_List">
13     ID, IMAGECODE, IAMGENAME, CREATEUSER, CREATETIME, UPDATEUSER, UPDATETIME, IMAGEVALUE
14   </sql>
15 
16 <select id="selectByExample" parameterType="com.wondersgroup.view360.ui.imagelogo.model.SecurityImageLogoExample"
17             resultMap="BaseResultMap">
18         select
19         <if test="distinct">
20             distinct
21         </if>
22         <include refid="Base_Column_List"/>
23         from SECURITY_IMAGE_LOGO
24         <if test="_parameter != null">
25             <include refid="Example_Where_Clause"/>
26         </if>
27         <if test="orderByClause != null">
28             order by ${orderByClause}
29         </if>
30     </select>

插入

1 <insert id="insert" parameterType="com.wondersgroup.view360.ui.imagelogo.model.SecurityImageLogo">
2     insert into SECURITY_IMAGE_LOGO (ID, IMAGECODE, IAMGENAME, 
3       CREATEUSER, CREATETIME, UPDATEUSER, 
4       UPDATETIME, IMAGEVALUE)
5     values (#{id,jdbcType=VARCHAR}, #{imagecode,jdbcType=VARCHAR}, #{iamgename,jdbcType=VARCHAR}, 
6       #{createuser,jdbcType=VARCHAR}, #{createtime,jdbcType=TIMESTAMP}, #{updateuser,jdbcType=VARCHAR}, 
7       #{updatetime,jdbcType=TIMESTAMP}, #{imagevalue,jdbcType=CLOB})
8   </insert>

mybatis源码已经自动实现了对CLOB字段的查询和插入操作

二、通过Java代码来查询和插入CLOB字段

查询

对字段类型进行判断

1 if(obj instanceof String){
2                                     xmlStringEMR = (String)obj;
3                                 } else if(obj instanceof NClob){
4                                     xmlStringEMR = modelMaintainConfigService.clob2Str((NClob)obj);
5                                 }
6                                 else if(obj instanceof Clob){
7                                     xmlStringEMR =oracleClob2Str((Clob) obj);
8                                 }

CLOB转成字符串

 1 /*
 2      * 将CLOB类型转成String进行解析
 3      * */
 4     public String oracleClob2Str(Clob clob)  {
 5         try {
 6             return (clob != null ? clob.getSubString(1, (int) clob.length()) : null);
 7         } catch (SQLException e) {
 8             // TODO Auto-generated catch block
 9             e.printStackTrace();
10         }
11         return "";
12     }

将NCLOB转成字符串

 1 /**
 2      * 将NCLOB转成字符串
 3      * @param nclob
 4      * @return
 5      * @throws Exception
 6      */
 7     @Override
 8     public String clob2Str(NClob nclob) throws Exception {
 9         String content = "";
10         try {
11             Reader is = nclob.getCharacterStream();
12             BufferedReader buff = new BufferedReader(is);// 得到流
13             String line = buff.readLine();
14             StringBuffer sb = new StringBuffer();
15             while (line != null) {// 执行循环将字符串全部取出付值给StringBuffer由StringBuffer转成STRING
16                 sb.append(line);
17                 line = buff.readLine();
18             }
19             content = sb.toString();
20         } catch (Exception e) {
21             log.error("java.sql.NClob类型转java.lang.String类型出错..."+e.getCause());
22             e.printStackTrace();
23         }
24         return content;
25     }

插入

 1 Class.forName("com.mysql.jdbc.Driver");
 2 //new oracle.jdbc.driver.OracleDriver();
 3 //建立连接
 4 conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myuser", "root", "root");
 5 //使用PreparedStatement对象里来构建并执行SQL语句，7个问号代表7个字段预先要保留的值
 6 pstmt = conn.prepareStatement("INSERT INTO staff(name, age, sex,address, depart, worklen,wage) VALUES (?, ?, ?, ?, ?, ?, ?)");
 7 //通过PreparedStatement对象里的set方法去设置插入的具体数值
 8 pstmt.setString(1, name);
 9 pstmt.setInt(2, age);
10 pstmt.setString(3, sex);
11 pstmt.setString(4,address );
12 pstmt.setString(5, depart);
13 pstmt.setInt(6, worklen);
14 StringReader c = new StringReader(s);
15 //这里插入大字段
16 pstmt.setCharacterStream(7, c,s.length());
17 pstmt.executeUpdate();