一、通过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();