Mybatis连接Oracle实现增删改查实践

1. 首先要在项目中增加Mybatis和Oracle的Jar文件

这里我使用的版本为ojdbc7

Mybatis版本为：3.2.4

2. 在Oracle中创建User表

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; 

　　

3.创建User类

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() {  
	    }

		@Override
		public String toString() {
			return "User [name=" + name + ", sex=" + sex + ", id=" + id + ", birs=" + birs + ", message=" + message
					+ "]";
		}  
	    
	    
}

　　

4. 创建UsersMapper接口

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

　　

5. 创建OracleClobTypeHandler.java

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandler;

import oracle.sql.CLOB;

public class OracleClobTypeHandler  implements TypeHandler<Object> {

	 public Object valueOf(String param) {  
	        return null;  
	    }  
	   
	    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());  
	    }  
	   
	    public Object getResult(ResultSet arg0, int arg1) throws SQLException {  
	        return null;  
	    }  
	  

	    public Object getResult(CallableStatement arg0, int arg1) throws SQLException {  
	        return null;  
	    }  
	  
	    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);  
	    }  

}

　　

6. 创建配置文件

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.example.oracle.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:@127.0.0.1:1521:orcl" />  
                <property name="username" value="system" />  
                <property name="password" value="123456" />  
            </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.example.mapper.UsersMapper" >  
  
    <!-- Result Map-->  
    <resultMap type="com.example.oracle.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.example.oracle.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>     

　　两个xml文件路径为：

 

 

7 .在main方法中测试

public class OracleMain {

	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);
	    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("张三");
		bean.setSex("男");
		bean.setBirs(date);
		bean.setMessage("您好，我是张三");
		dao.add(bean);
		
		bean = new User();
		bean.setName("李四");
		bean.setSex("男");
		bean.setBirs(date);
		bean.setMessage("您好，我是李四");
		dao.add(bean);
		
		printUserInfo(dao);
		
		//查詢并更新
		bean = new User();
		bean.setName("李四");
		List<User> list = dao.queryByList(bean);
		for(User user : list){
			user.setName("王五");
			user.setSex("女");
			dao.update(user);
		}
		System.out.println("---------更新--------------");
		printUserInfo(dao);
		
		int num = dao.queryByCount(nullBean);
		System.out.println("num="+ num);
		
		sqlSession.commit();  
	
	}

	private static void printUserInfo(UsersMapper dao) {
		User nullBean = new User();
		List<User> list = dao.queryByList(nullBean);
		for(User user : list){
			System.out.println(user.getName() + " " + user.getMessage());
		}
	}
}