mybatis连接Oracle做增删改查

入门请看http://blog.youkuaiyun.com/liumengcheng/article/details/34409783

首先创建表

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;

User.java

package com.southgis.scout.demo;

import java.util.Date;
/**
 * 用户类
 * 
 * @author lmc
 * 
 */
public class User {
	private String name;
	private String sex;
	private Integer id;
	private Date birs;
	private String message;

	
	public String getMessage() {
		return message;
	}
	
	public void setMessage(String pMessage) {
		this.message = pMessage;
	}
	
	public Date getBirs() {
		return birs;
	}
	
	public void setBirs(Date pbirs) {
		this.birs = pbirs;
	}
	
	public String getName() {
		return name;
	}
	
	public void setName(String name) {
		this.name = name;
	}
	
	public String getSex() {
		return sex;
	}
	
	public void setSex(String psex) {
		this.sex = psex;
	}
	
	public Integer getId() {
		return id;
	}
	
	public void setID(Integer pid) {
		this.id = pid;
	}
	
	public User() {
	}

}

UsersMapper.java

package com.southgis.scout.demo;

import java.util.List;
import com.southgis.scout.demo.User;  
public interface UsersMapper {

	public void add(User t);

	public void update(User t);

	public void updateBySelective(User t);

	public void delete(Object id);

	public User queryById(Object id);
	
	public List<User> queryBySelective(User t);
	
	public int queryByCount(User t);

	public List<User> queryByList(User t);
	
}

OracleClobTypeHandler.java

package com.southgis.scout.demo;

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);
	}
}

main.java

package com.southgis.scout.demo;

import java.io.IOException;  
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Date;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession; 
import org.apache.ibatis.session.SqlSessionFactory; 
import org.apache.ibatis.session.SqlSessionFactoryBuilder;  

import com.southgis.scout.demo.UsersMapper;  
import com.southgis.scout.demo.User;  
public class main {
	
	/**
	 * 获得MyBatis SqlSessionFactory  
	 * SqlSessionFactory负责创建SqlSession，一旦创建成功，就可以用SqlSession实例来执行映射语句，commit，rollback，close等方法。
	 * @return
	 */
	private static SqlSessionFactory getSessionFactory() {
		SqlSessionFactory sessionFactory = null;
		String resource = "configuration.xml";
		try {
			sessionFactory = new SqlSessionFactoryBuilder().build(Resources
					.getResourceAsReader(resource));
		} catch (IOException e) {
			e.printStackTrace();
		}
		return sessionFactory;
	}

	public static void main(String[] args) {
		SqlSession sqlSession = getSessionFactory().openSession();
		UsersMapper dao = sqlSession.getMapper(UsersMapper.class);
		
		//删除表中所有信息
		User nullBean = new User();
		List<User> delList = dao.queryByList(nullBean);
		if(delList != null) {
			for(User user : delList) {
				dao.delete(user.getId());
			}
		}
		
		DateFormat dd=new SimpleDateFormat("yyyy-MM-dd");
		Date date=null;
		try {
			date = dd.parse("1985-01-01");
		} catch (ParseException e) {
			e.printStackTrace();
		}
		
		//新增
		User bean = new User();
		bean.setName("ding");
		bean.setSex("男");
		bean.setBirs(date);
		bean.setMessage("This is Clob!");
		dao.add(bean);
		
		List<User> list = dao.queryByList(nullBean);
		if(list != null) {
			for(User user : list) {
				System.out.println(user.getName()+user.getMessage());
			}
		}
		
		//查询并更新
		bean = new User();
		bean.setName("ding");
		List<User> queList = dao.queryByList(bean);
		if(queList != null) {
			for(User user : list) {
				user.setSex("女");
				dao.updateBySelective(user);
			}
		}
		
		list = dao.queryByList(nullBean);
		if(list != null) {
			for(User user : list) {
				System.out.println(user.getSex());
			}
		}
		
		//查询并更新
		bean = new User();
		bean.setName("ding");
		List<User> queList2 = dao.queryByList(bean);
		if(queList != null) {
			for(User user : queList2) {
				user.setSex("男");
				user.setMessage("");
				dao.update(user);
			}
		}
		
		list = dao.queryByList(nullBean);
		if(list != null) {
			for(User user : list) {
				System.out.println(user.getSex());
			}
		}
		
		int num = dao.queryByCount(nullBean);
		System.out.println("num=" + num);
		//默认是不提交的，所以这里手工提交
		sqlSession.commit();
		
	}

}

configuration.xml

<?xml version="1.0" encoding="UTF-8" ?>   
<!DOCTYPE configuration   
    PUBLIC "-//mybatis.org//DTD Config 3.0//EN"   
    "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	<typeAliases>
		<!--给实体类起一个别名 user 不过建议不要起别名，容易搞混-->
		<typeAlias type="com.southgis.scout.demo.User" alias="User" />
	</typeAliases>
	<!--数据源配置  这块用 Oracle数据库 -->
	<environments default="development">
		<environment id="development">
			<transactionManager type="jdbc" />
			<dataSource type="POOLED">
				<property name="driver" value="oracle.jdbc.OracleDriver" />
				<property name="url" value="jdbc:oracle:thin:@192.168.56.102:1521:orcl" />
				<property name="username" value="scott" />
				<property name="password" value="tiger" />
			</dataSource>
		</environment>
	</environments>
	<mappers>
		<!--UsersMapper.xml装载进来  同等于把“dao”的实现装载进来 -->
		<mapper resource="UsersMapper.xml" />
	</mappers>
</configuration> 

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">
  <!--这块等于dao接口的实现  namespace必须和接口的类路径一样-->
<mapper namespace="com.southgis.scout.demo.UsersMapper" >

	<!-- Result Map-->
	<resultMap type="com.southgis.scout.demo.User" 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 ="com.southgis.scout.demo.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>
	
	<!-- 下面的id都和接口UsersMapper中的方法名一样-->
	
	<!-- 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>