由于最近要用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;
伦理电影 http://www.dotdy.com/
二.工程代码
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"}]