Mybatis之Oracle增删查改示例（含Date、Clob数据类型操作）

在Oracle中，LOB（Large Object，大型对象）类型的字段现在用得越来越多了。因为这种类型的字段，容量大（最多能容纳4GB的数据），且一个表中可以有多个这种类型的字段，很灵活，适用于数据量非常大的业务领域（如图象、档案等）。而LONG、LONG RAW等类型的字段，虽然存储容量也不小（可达2GB），但由于一个表中只能有一个这样类型的字段的限制，现在已很少使用了。

 

 

LOB类型分为BLOB和CLOB两种：BLOB即二进制大型对象（Binary Large Object），适用于存贮非文本的字节流数据（如程序、图象、影音等）。而CLOB，即字符型大型对象（Character Large Object），则与字符集相关，适于存贮文本型的数据（如历史档案、大部头著作等）。

oracle表结构

create table T_USERS(  ID      NUMBER not null,  NAME    VARCHAR2(30),  SEX     VARCHAR2(3),  BIRS    DATE,  MESSAGE CLOB)create sequence SEQ_T_USERS_IDminvalue 1maxvalue 99999999start with 1increment by 1cache 20;

配置mybatis配置文件UsersMapper.xml

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE mapper  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"  "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="examples.mapper.UsersMapper" >  <!-- Result Map-->  <resultMap type="examples.bean.Users" id="BaseResultMap">    <result property="id" column="id" />    <result property="name" column="name" />    <result property="sex" column="sex" />    <result property="birs" column="birs" jdbcType="TIMESTAMP"/>    <result property="message" column="message" jdbcType="CLOB"       javaType = "java.lang.String"  typeHandler ="examples.service.OracleClobTypeHandler"/>  </resultMap>    <sql id="Tabel_Name">    t_users  </sql>    <!-- 表中所有列 -->  <sql id="Base_Column_List" >    id,name,sex,birs,message  </sql>  <!-- 查询条件 -->  <sql id="Example_Where_Clause">    where 1=1    <trim suffixOverrides=",">      <if test="id != null">        and id = #{id}      </if>      <if test="name != null and name != ''">        and name like concat(concat('%', '${name}'), '%')      </if>      <if test="sex != null and sex != ''">        and sex like concat(concat('%', '${sex}'), '%')      </if>      <if test="birs != null">        and birs = #{birs}      </if>      <if test="message != null">        and message = #{message}      </if>    </trim>  </sql>    <!-- 1.新增记录 -->  <insert id="add" parameterType="Object" >     <selectKey resultType="int" order="BEFORE" keyProperty="id">      select seq_t_users_id.nextval as id from dual    </selectKey>    insert into t_users(id,name,sex,birs,message) values(#{id},#{name},#{sex},#{birs},#{message,jdbcType=CLOB})  </insert>  <!-- 2.根据id修改记录-->    <update id="update" parameterType="Object" >    update t_users set name=#{name},sex=#{sex},birs=#{birs},message=#{message} where id=#{id}  </update>  <!-- 3.只修改不为空的字段 -->  <update id="updateBySelective" parameterType="Object" >    update t_users set     <trim  suffixOverrides="," >      <if test="name != null  and name != '' ">        name=#{name},      </if>      <if test="sex != null  and sex != '' ">        sex=#{sex},      </if>      <if test="birs != null  ">        birs=#{birs},      </if>      <if test="message != null  and message != '' ">        message=#{message},      </if>    </trim> where id=#{id}  </update>  <!-- 4.根据id进行删除 -->  <delete id="delete" parameterType="Object">    delete from t_users where id = #{id}  </delete>    <!-- 5.根据id查询 -->  <select id="queryById" resultMap="BaseResultMap" parameterType="Object">    select    <include refid="Base_Column_List" />    from t_users where id = #{id}  </select>  <!-- 6.查询列表，只查询不为空的字段 -->  <select id="queryBySelective" resultMap="BaseResultMap" parameterType="Object">    select    <include refid="Base_Column_List" />    from t_users    <include refid="Example_Where_Clause" />  </select>    <!-- 7.列表总数 -->  <select id="queryByCount" resultType="java.lang.Integer" parameterType="Object">    select count(1) from t_users    <include refid="Example_Where_Clause" />  </select>    <!-- 8.查询列表 -->  <select id="queryByList" resultMap="BaseResultMap" parameterType="Object">    select    <include refid="Base_Column_List" />    from t_users     <include refid="Example_Where_Clause"/>  </select></mapper>

Mapper类接口

package examples.mapper;import java.util.List;public interface UsersMapper<T> {  public void add(T t);  public void update(T t);  public void updateBySelective(T t);  public void delete(Object id);  public T queryById(Object id);    public List<T> queryBySelective(T t);    public int queryByCount(T t);  public List<T> queryByList(T t);  }

类型转换工具类

package examples.service;import java.sql.CallableStatement;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import oracle.sql.CLOB;import org.apache.ibatis.type.JdbcType;import org.apache.ibatis.type.TypeHandler;public class OracleClobTypeHandler implements TypeHandler<Object> {    public Object valueOf(String param) {    return null;  }  @Override  public Object getResult(ResultSet arg0, String arg1) throws SQLException {    CLOB clob = (CLOB) arg0.getClob(arg1);    return (clob == null || clob.length() == 0) ? null : clob.getSubString((long) 1, (int) clob.length());  }  @Override  public Object getResult(ResultSet arg0, int arg1) throws SQLException {    return null;  }  @Override  public Object getResult(CallableStatement arg0, int arg1) throws SQLException {    return null;  }  @Override  public void setParameter(PreparedStatement arg0, int arg1, Object arg2, JdbcType arg3) throws SQLException {    CLOB clob = CLOB.empty_lob();    clob.setString(1, (String) arg2);    arg0.setClob(arg1, clob);  }}

Spring配置文件

<?xml version="1.0" encoding="UTF-8"?><beans xmlns="http://www.springframework.org/schema/beans"  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"  xmlns:mvc="http://www.springframework.org/schema/mvc" xmlns:aop="http://www.springframework.org/schema/aop"  xmlns:tx="http://www.springframework.org/schema/tx"  xsi:schemaLocation="http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd    http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd    http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd    http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd    http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd"  default-autowire="byType">  <!-- 配置数据源 -->  <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">         <property name="driverClassName"><value>oracle.jdbc.driver.OracleDriver</value></property>          <property name="url"><value>jdbc:oracle:thin:@127.0.0.1:1521:pms</value></property>          <property name="username"><value>pms</value></property>          <property name="password"><value>pms</value></property>  </bean>  <!-- 配完数据源 和 拥有的 sql映射文件 sqlSessionFactory 也可以访问数据库 和拥有 sql操作能力了 --><!--   <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">    <property name="dataSource" ref="dataSource" />    <property name="configLocation" value="classpath:mybatis-config.xml"/>  </bean> -->  <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">    <property name="dataSource" ref="dataSource" />    <property name="mapperLocations">      <list>        <value>classpath:examples/mybatis/oracle/UsersMapper.xml</value>      </list>    </property>  </bean>  <!-- 通过设置 mapperInterface属性，使接口服务bean 和对应xml文件管理 可以使用其中的sql -->  <bean id="dao" class="org.mybatis.spring.mapper.MapperFactoryBean">    <!-- 此处等同于 Mybatis 中 ServerDao serverDao = sqlSession.getMapper(ServerDao.class); 指明映射关系 -->    <property name="mapperInterface" value="examples.mapper.UsersMapper" />    <property name="sqlSessionFactory" ref="sqlSessionFactory" />  </bean></beans>

测试类

package examples.service;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.List;import org.springframework.context.ApplicationContext;import org.springframework.context.support.ClassPathXmlApplicationContext;import examples.bean.Users;import examples.mapper.UsersMapper;public class TestUsersService {  @SuppressWarnings("unchecked")  public static void main(String[] args) throws ParseException {      ApplicationContext ac =       new ClassPathXmlApplicationContext("classpath:/examples/service/spring.xml");      UsersMapper<Users> dao = (UsersMapper<Users>)ac.getBean("dao");            //删除表中所有信息      Users nullBean = new Users();      List<Users> delList = dao.queryByList(nullBean);      if(delList != null) {        for(Users user : delList) {          dao.delete(user.getId());        }      }            //新增      Users bean = new Users();      bean.setName("ding");      bean.setSex("男");      bean.setBirs(new SimpleDateFormat("yyyy-MM-dd").parse("1985-01-01"));      bean.setMessage("This is Clob!");      dao.add(bean);            List<Users> list = dao.queryByList(nullBean);      if(list != null) {        for(Users user : list) {          System.out.println(user);        }      }            //查询并更新      bean = new Users();      bean.setName("ding");      List<Users> queList = dao.queryByList(bean);      if(queList != null) {        for(Users user : list) {          user.setSex("女");          dao.updateBySelective(user);        }      }            list = dao.queryByList(nullBean);      if(list != null) {        for(Users user : list) {          System.out.println(user);        }      }            //查询并更新      bean = new Users();      bean.setName("ding");      List<Users> queList2 = dao.queryByList(bean);      if(queList != null) {        for(Users user : queList2) {          user.setSex("男");          user.setMessage("");          dao.update(user);        }      }            list = dao.queryByList(nullBean);      if(list != null) {        for(Users user : list) {          System.out.println(user);        }      }                  int num = dao.queryByCount(nullBean);      System.out.println("num=" + num);        }}

转载自：http://www.tuicool.com/articles/YjieM3