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

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

oracle表结构

create table T_USERS
(
  ID      NUMBER not null,
  NAME    VARCHAR2(30),
  SEX     VARCHAR2(3),
  BIRS    DATE,
  MESSAGE CLOB
)
create sequence SEQ_T_USERS_ID
minvalue 1
maxvalue 99999999
start with 1
increment by 1
cache 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);
			
	}

}