Mybatis十（ mybatis其他使用）

1.批量执行

public void addUser(User user);

<insert id="addUser" parameterType="model.User">
        INSERT INTO user (id,last_name,email) VALUES
        (#{id},#{lastName},#{email})
    </insert>

 

@Test
    public void testBatch() {
        String resource = "mybatis-config.xml";
        InputStream inputStream;
        try {
            inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            long currentTimeMillis = System.currentTimeMillis();
            User user = new User();
            user.setId(2);
            user.setGender("1");
//            user.setEmail("1232341");
            for (int i = 0; i < 1000; i++) {
                //mapper.addUser(new User(Integer.parseInt(UUID.randomUUID().toString().substring(0,5)),"aa","11"));    
                mapper.addUser(new User(i+100,"aa","11"));    
            }
            long currentTimeMillis2 = System.currentTimeMillis();
            System.out.println("执行时长："+(currentTimeMillis-currentTimeMillis2));
            sqlSession.commit();
            // 释放资源
            sqlSession.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

整合spring配置拿到批量执行的链接，直接注入使用即可

 

2.存储过程的使用

　　oracle

　　

package page;

import java.util.List;

import model.User;
/**
 * 分装分页查询数据
 * @author admin
 *
 */
public class Page {
    
    private int start;
    private int end;
    private int count;
    private List<User> users;
    public int getStart() {
        return start;
    }
    public void setStart(int start) {
        this.start = start;
    }
    public int getEnd() {
        return end;
    }
    public void setEnd(int end) {
        this.end = end;
    }
    public int getCount() {
        return count;
    }
    public void setCount(int count) {
        this.count = count;
    }
    public List<User> getUsers() {
        return users;
    }
    public void setUsers(List<User> users) {
        this.users = users;
    }
    
}

 

--创建存储过程 

CREATE OR REPLACE PROCEDURE page(
       p_start in int,p_end in int,p_count out int,p_users out sys_refcursor       
) as
begin 
    select count(*)  into p_count from user;
    open p_users for
            select * from (select rownum rn,u.* from user u where rownum<=P_end) where rn>=p_start;

end page;

 mybatis 调用存储过程

　public void selectPageByProcedure(Page page);

　　

<resultMap type="user" id="pageMap">
        <id column="id" property="id" />
        <result column="last_name" property="lastName" />
        <result column="email" property="email" />
        <result column="gender" property="gender" />
    </resultMap>
    <select id="selectPageByProcedure" statementType="CALLABLE" >
        {call page(
            #{start,mode=IN,jdbcType=INTEGER},
            #{end,mode=IN,jdbcType=INTEGER},
            #{count,mode=OUT,jdbcType=INTEGER},
            #{users,mode=OUT,jdbcType=CURSOR,javaType=ResultSet,resultMap=pageMap},
        )}
    </select>

单元测试

　　

/**
     * oracle分页：
     *     借助rownum:行号；子查询；
     *     存储过程包装分页逻辑
     */
    @Test
    public void testProcedure() {
        String resource = "mybatis-config.xml";
        InputStream inputStream;
        try {
            inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession sqlSession = sqlSessionFactory.openSession();
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            Page page =new Page();
            page.setStart(1);
            page.setEnd(5);
            page.setStart(1);
            mapper.selectPageByProcedure(page);
            System.out.println("总记录数："+page.getCount());
            System.out.println("查出的数据数："+page.getUsers().size());
            System.out.println("查出的数据："+page.getUsers());
            sqlSession.commit();
            // 释放资源
            sqlSession.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

3.自定义TypeHandler类型处理枚举类型

　　通用枚举类处理

　　

package model;

public enum UserStates {
    
    LOGIN,LOGINOUT,LOCK
}

　添加枚举属性

　　

@Alias("user")
public class User {

    private int id;
    private String lastName;
    private String email;
    private String gender;
    private Department dept;
    private UserStates state;

<!--public void addUser(User user)-->
<insert id="addUser" parameterType="model.User">
        INSERT INTO user (id,last_name,email,user_states) VALUES
        (#{id},#{lastName},#{email},#{state})
    </insert>

 

/**
     * mybaits 处理枚举对象的时候取名字,EnumTypeHandler(默认执行)，存入库中名字
     * 改变使用：EnumOrdinalTypeHandler(配置指定)
     */
    @Test
    public void testEnum() {
        String resource = "mybatis-config.xml";
        InputStream inputStream;
        try {
            inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession sqlSession = sqlSessionFactory.openSession();
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            User user =new User(21,"aa","11");
            user.setState(UserStates.LOGIN);
            mapper.addUser(user);    
            sqlSession.commit();
            // 释放资源
            sqlSession.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

//指定handler后，插入枚举值

<typeHandlers>
        <typeHandler handler="org.apache.ibatis.type.EnumOrdinalTypeHandler" javaType="model.UserStates"/>
    </typeHandlers> 

枚举获取其属性demo

@Test
    public void testEnumUse() {
        UserStates login = UserStates.LOGIN;
        System.out.println(login.ordinal());
        System.out.println(login.name());
    }

 自定义：

　　存入指定code,输出指定msg

package model;

/**
 * 保存状态码
 * @author admin
 *
 */
public enum UserStates {
    
    LOGIN(100,"用户登录"),LOGINOUT(200,"用户登出"),LOCK(000,"用户被锁");
    
    private Integer code;
    private String msg;
    private UserStates(Integer code,String msg){
        this.code=code;
        this.msg=msg;
    }
    public Integer getCode() {
        return code;
    }
    public void setCode(Integer code) {
        this.code = code;
    }
    public String getMsg() {
        return msg;
    }
    public void setMsg(String msg) {
        this.msg = msg;
    }
    //按照状态码返回枚举对象
    public static UserStates getUserStatesByCode(Integer code){
        switch(code){
        case 100:
            return LOGIN;
        case 200:
            return LOGINOUT;    
        case 000:
            return LOCK;
        default:
            return LOGINOUT;
        }
    }
}

 

　　测试获取属性

　　

@Test
    public void testEnumUse() {
        UserStates login = UserStates.LOGIN;
        System.out.println(login.ordinal());
        System.out.println(login.name());
        System.out.println(login.getCode());
        System.out.println(login.getMsg());
    }

 自定义类型处理器

package handler;

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 model.UserStates;

/**
 * 实现TypeHandler接口或者继承BaseTypeHandler
 * @author admin
 *
 */
public class EnumUserStatesHandler implements TypeHandler<UserStates>{
    /**
     * 定义参数如何保存
     */
    @Override
    public void setParameter(PreparedStatement ps, int i, UserStates parameter, JdbcType jdbcType) throws SQLException {
        // TODO Auto-generated method stub
        ps.setString(i, parameter.getCode().toString());
    }

    @Override
    public UserStates getResult(ResultSet rs, String columnName) throws SQLException {
        // TODO Auto-generated method stub
        int code = rs.getInt(columnName);
        System.out.println("从数据库中获取到状态码："+code);
        UserStates states = UserStates.getUserStatesByCode(code);
        return states;
    }

    @Override
    public UserStates getResult(ResultSet rs, int columnIndex) throws SQLException {
        int code = rs.getInt(columnIndex);
        System.out.println("从数据库中获取到状态码："+code);
        UserStates states = UserStates.getUserStatesByCode(code);
        return states;
    }

    @Override
    public UserStates getResult(CallableStatement cs, int columnIndex) throws SQLException {
        int code = cs.getInt(columnIndex);
        System.out.println("从数据库中获取到状态码："+code);
        UserStates states = UserStates.getUserStatesByCode(code);
        return states;
    }
    
}

　public void addUser(User user);

　public User selectUserStates(Integer id);

　　

<insert id="addUser" parameterType="model.User">
        INSERT INTO user (id,last_name,email,user_states) VALUES
        (#{id},#{lastName},#{email},#{state})
    </insert>

<select id="selectUserStates" resultType="user">
        select user_states state from user where id=#{id}
    </select>

mybatis-cofig配置
<typeHandlers>    
           <!--配置自定义类型处理器  -->
        <typeHandler handler="handler.EnumUserStatesHandler" javaType="model.UserStates"/>
    </typeHandlers> 

　　

@Test
    public void testEnum() {
        String resource = "mybatis-config.xml";
        InputStream inputStream;
        try {
            inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession sqlSession = sqlSessionFactory.openSession();
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            User user =new User(22,"aa","11");
            //插入数据
            user.setState(UserStates.LOGIN);
            //mapper.addUser(user);    
            User selectUserStates = mapper.selectUserStates(22);
            System.out.println(selectUserStates.getState());
            sqlSession.commit();
            // 释放资源
            sqlSession.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

其他地方配置类型处理器，也可以实现上述效果