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

Posted on 2018-01-20 16:22 work hard work smart 阅读(16060) 评论(0) 编辑收藏举报

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文件

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

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