oracle 从数据库中取数据，对clob数据进行处理
Oracle中有表结构
create table SC_ANSWER
(
ID VARCHAR2(32 CHAR) not null,
CONTENT CLOB,
POINT NUMBER(10) default 0,
ENDTIME TIMESTAMP(6),
TEACHER_ID VARCHAR2(32 CHAR),
QUESTION_ID VARCHAR2(32 CHAR),
PEOPLENUMBER NUMBER(10) default 0
)
content 字段的类型是clob，当从oracle数据库中取出来的时候，要做一下处理
java中的sql语句
public List getQuestionAnswerContent(String id){
if(id==null||id.equals("")){
return null;
}
List list = null;
String sql="select sq.id,sq.title as questiontile,sq.title,sb.name ,sa.content as content , sq.content as questionContent ,cast(sq.sendtime as date) as sendtime,cast(sa.endtime as date) as endtime ,sm.nickname,st.realname "+
"from sc_question sq ,sc_subject sb ,sc_answer sa ,sc_member sm,sc_teacher st "+
"where sq.subject_id=sb.id and sa.question_id=sq.id and sa.teacher_id=st.id and sm.id=sq.member_id and (sq.state='1' or sq.state='2') and sq.id='"+id+"'";
list = myCmsArticleDAO.getQuestionAnswersInfo(sql);
return list;
}
通过下面的方法，对查询出来的数据进行处理 ,下面的这种处理方式，在回调方法使用了
Map results = new HashMap();这样处理了，返回值是个list
list = jdbcTemplate.query(sql, new RowMapper(){});
public List getQuestionAnswersInfo(String sql){
List list=null;
list = jdbcTemplate.query(sql, new RowMapper(){
public Object mapRow(ResultSet rs, int i) throws SQLException {
Map results = new HashMap();//存放对像的hashmap
CLOB clob = null;
String str = "";
try {
// clob = (oracle.sql.CLOB) rs.getClob("content");//content字段属性为clob,转成clob对象
Object obj=rs.getClob("content");
str = clobToString2(obj);//将clob对象转为String
results.put("content",str);
results.put("questionContent",rs.getString("questionContent"));
results.put("questionTitle",rs.getString("questiontile"));
results.put("realname",rs.getString("realname"));
results.put("sendtime",rs.getString("sendtime"));
results.put("endtime",rs.getString("endtime"));
results.put("nickname",rs.getString("nickname"));
results.put("courseName",rs.getString("name"));
results.put("nickname",rs.getString("nickname"));
results.put("courseName",rs.getString("name"));
DateFormat sdf = new SimpleDateFormat("yyyy.MM.dd HH:mm:ss");
Timestamp ts =null;
ts= Timestamp.valueOf(rs.getString("sendtime"));
results.put("sendtime",sdf.format(ts));
ts= Timestamp.valueOf(rs.getString("endtime"));
results.put("endtime",sdf.format(ts));
} catch (Exception e) {
logger.error(e.getMessage());
results.put("content", "");
}
return results;
}
//转换CLOB为普通CLOB类型进行读取
public String clobToString2(Object clob2)throws SQLException, IOException{
log.info("classname:"+clob2.getClass().getName());
try{
//判断CLOB类型
if ("oracle.sql.CLOB".equals(clob2.getClass().getName())){
String rtn = "";
oracle.sql.CLOB clob = (oracle.sql.CLOB)clob2;
InputStream input = clob.getAsciiStream();
int len = (int)clob.length();
byte[] by = new byte[len];
int i ;
while(-1 != (i = input.read(by, 0, by.length))) {
input.read(by, 0, i);
}
rtn = new String(by);
rtn=clob.getSubString((long)1,(int)clob.length());
return rtn;
}else if("weblogic.jdbc.wrapper.Clob_oracle_sql_CLOB".equals(clob2.getClass().getName())){
//通过weblogic数据源访问时，需要把weblogic的CLOB转换为java.sql.Clob类型
String rtn = "";
Method method = clob2.getClass().getMethod("getVendorObj",new Class[]{});
java.sql.Clob clob = (Clob) method.invoke(clob2);
InputStream input = clob.getAsciiStream();
int len = (int)clob.length();
byte[] by = new byte[len];
int i ;
while(-1 != (i = input.read(by, 0, by.length))) {
input.read(by, 0, i);
}
rtn = new String(by);
rtn=clob.getSubString((long)1,(int)clob.length());
return rtn;
}else{
return null;
}
}catch(Exception e){
log.info("convert to clob error:"+e.getLocalizedMessage());
return null;
}
}
});
return list;
}
Jsp页面的代码
<div class="sx_wrap" id="top">
<%@ include file="/include/head.jsp" %>
<div class="sx_title1"><a href="${pageContext.request.contextPath}/article/index.do" title="网校首页">网校首页</a><span onclick="goQuestionAnswer();" style="cursor: pointer" title="问题答疑">问题答疑</span></div>
<c:if test="${not empty aritcleList}">
<c:forEach items="${aritcleList}" var="smart" varStatus="status">
<div class="sx_title">${smart['courseName']}<!-- <span class="f12 c999 ml">(100人已关注此问题)</span> --></div>
<!--详情内容 开始-->
<div class="sx_cont">
<div class="answer">${smart['questionTitle']}</span> </div>
<div class="questions">
<div class="f12">提问人：${smart['nickname']} <span class="c999 ml mr15">${smart['sendtime']}</span> </div>
${smart['questionContent']}
</div>
<c:choose>
<c:when test="${not empty smart['content']}">
<div class="answer">${smart['content']}
<p class="tright f12">回答者：${smart['realname']} <span class="c999">${smart['endtime']}</span></p>
</div>
</c:when>
<c:otherwise>
<div class="answer">问题还未做解答...</div>
</c:otherwise>
</c:choose>
</div>
</c:forEach>
</c:if>
</div>
浙公网安备 33010602011771号