由于最近要用iBATIS操作Oracle的CLOB字段,简单做了一个Demo。此demo依赖的JAR包有ibatis-2.3.0.677.jar、ojdbc6.jar、fastjson-1.1.26.jar。
一.创建表
--创建表
CREATE TABLE BJ.M_MODEL (
MODELID VARCHAR2(128) NOT NULL,
MODEL_HTML CLOB,
CONSTRAINT PK_M_MODEL PRIMARY KEY (MODELID)
);
--创建同义词
create public synonym M_MODEL for BJ.M_MODEL;
--授权
grant select, insert, update, delete on BJ.M_MODEL to bijian;
二.工程代码
sqlMapConfig.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE sqlMapConfig PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-config-2.dtd"> <sqlMapConfig> <transactionManager type="JDBC" commitRequired="false"> <dataSource type="SIMPLE"> <property name="JDBC.Driver" value="oracle.jdbc.driver.OracleDriver"/> <property name="JDBC.ConnectionURL" value="jdbc:oracle:thin:@10.20.0.1:1521:db"/> <property name="JDBC.Username" value="bijian"/> <property name="JDBC.Password" value="123"/> </dataSource> </transactionManager> <sqlMap resource="com/app/model/Model.xml"/> </sqlMapConfig>
Model.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd"> <sqlMap> <!-- Use type aliases to avoid typing the full classname every time. --> <typeAlias alias="model" type="com.app.model.Model" /> <!-- 新增 --> <insert id="insertModelHtml" parameterClass="java.util.Map"> insert into m_model(MODELID, MODEL_HTML) values(#modelId#, #modelHtml#) </insert> <!-- 更新 --> <update id="updateModelHtml" parameterClass="java.util.Map"> update m_model set model_html=#modelHtml# where modelId=#modelId# </update> <!-- 根据ID删除 --> <delete id="deleteModelById" parameterClass="java.lang.String"> delete m_model where modelId=#modelId# </delete> <!-- 删除所有数据 --> <delete id="deleteAllModels"> delete m_model </delete> <!-- 查询 --> <resultMap class="model" id="Model_htmlMap"> <result property="modelId" column="modelid" javaType="String" /> <result property="modelHtml" column="model_html" nullValue="" javaType="java.lang.String" jdbcType="CLOB" /> </resultMap> <select id="getModelById" parameterClass="java.lang.String" resultMap="Model_htmlMap"> SELECT t1.MODELID,t1.model_html FROM M_MODEL t1 WHERE MODELID=#MODELID# ORDER BY t1.MODELID </select> </sqlMap>
Model.java
package com.app.model;
public class Model {
private String modelHtml;
private String modelId;
public String getModelHtml() {
return modelHtml;
}
public void setModelHtml(String modelHtml) {
this.modelHtml = modelHtml;
}
public String getModelId() {
return modelId;
}
public void setModelId(String modelId) {
this.modelId = modelId;
}
}
ModelDAO.java
package com.app.dao;
import java.util.List;
import com.app.model.Model;
public interface ModelDAO {
public void insertModelHtml(String modelId, String modelHtml) throws Exception;
public void updateModelHtml(String modelId, String modelHtml) throws Exception;
public void deleteModelById(String modelId) throws Exception;
public void deleteAllModels() throws Exception;
public List<Model> getModelById(String modelId) throws Exception;
}
ModelDAOImpl.java
package com.app.dao;
import java.io.IOException;
import java.io.Reader;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.app.model.Model;
import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;
public class ModelDAOImpl implements ModelDAO {
public void insertModelHtml(String modelId, String modelHtml) throws Exception {
Map<String, String> map = new HashMap<String, String>(2);
map.put("modelId", modelId);
map.put("modelHtml", modelHtml);
this.getSqlMapClientTemplate().insert("insertModelHtml", map);
}
public void updateModelHtml(String modelId, String modelHtml) throws Exception {
Map<String, String> map = new HashMap<String, String>(2);
map.put("modelId", modelId);
map.put("modelHtml", modelHtml);
int res = this.getSqlMapClientTemplate().update("updateModelHtml", map);
System.out.println(res);
}
public void deleteModelById(String modelId) throws Exception {
this.getSqlMapClientTemplate().delete("deleteModelById", modelId);
}
public void deleteAllModels() throws Exception {
this.getSqlMapClientTemplate().delete("deleteAllModels");
}
public List<Model> getModelById(String modelId) throws Exception {
return this.getSqlMapClientTemplate().queryForList("getModelById", modelId);
}
private static SqlMapClient getSqlMapClientTemplate() throws IOException {
//从配置文件中得到SqlMapClient对象
Reader reader = Resources.getResourceAsReader("sqlMapConfig.xml");
SqlMapClient sqlMapper = SqlMapClientBuilder.buildSqlMapClient(reader);
reader.close();
return sqlMapper;
}
}
三.创建测试程序测试
ModelTest.java
package com.app.test;
import java.util.List;
import com.alibaba.fastjson.JSON;
import com.app.dao.ModelDAO;
import com.app.dao.ModelDAOImpl;
import com.app.model.Model;
public class ModelTest {
/**
* @param args
*/
public static void main(String[] args) throws Exception {
ModelDAO modelDAO = new ModelDAOImpl();
modelDAO.deleteAllModels();
modelDAO.insertModelHtml("1", "<html><head>测试</head><body><p>Hello World!</p></body></html>");
modelDAO.insertModelHtml("2", "<html><head>ibatis</head><body><p>Hello ibatis!</p></body></html>");
modelDAO.insertModelHtml("3", "<html><head>JavaScript</head><body><p>Hello JavaScript!</p></body></html>");
modelDAO.updateModelHtml("1", "<html><head>测试</head><body><p>hello World!</p></body></html>");
modelDAO.deleteModelById("1");
List<Model> modelList = modelDAO.getModelById("2");
System.out.println(JSON.toJSONString(modelList));
}
}
运行结果:
1 [{"modelHtml":"<html><head>ibatis</head><body><p>Hello ibatis!</p></body></html>","modelId":"2"}]