Mybatis插件和批量操作

布满荆棘的人生 2022-05-24 11:35 260阅读 0赞

扩展一个简单的实现

@Intercepts({
            @Signature(type = StatementHandler.class,method = "parameterize",args = java.sql.Statement.class)
    })
    public class MyFirstPlugin implements Interceptor {
        /** * intercept:拦截 * 拦截目标对象的方法的执行 * @param invocation * @return * @throws Throwable */ @Override public Object intercept(Invocation invocation) throws Throwable {
            System.out.println("MyFirstPlugin....interceptor:"+invocation.getMethod()); //动态改变sql运行的参数：以前1号员工，现在4号 Object target = invocation.getTarget(); System.out.println("当前拦截对象："+target); //拿到StatementHandler====》ParameterHandler===》ParameterObject //拿到目标对象target的元数据 MetaObject metaObject = SystemMetaObject.forObject(target); Object value = metaObject.getValue("parameterHandler.parameterObject"); System.out.println("SQL语句用的参数是"+value); metaObject.setValue("parameterHandler.parameterObject",4); //执行目标方法 Object proceed = invocation.proceed(); //返回执行后的返回值 return proceed; }
    
        /** * plugin:包装目标对象——包装：为目标对象创建一个代理类 * @param target * @return */ @Override public Object plugin(Object target) {
            System.out.println("MyFirstPlugin....plugin:mybatis将要包装的对象"+target); //我们可以借助Plugin的wrap方法来使用当前的intercept包装我们目标对象 Object wrap = Plugin.wrap(target, this); //返回为当前target创建好的动态代理 return wrap; }
    
        /** * setProperties:将插件注册时的property属性设置进来 * @param properties */ @Override public void setProperties(Properties properties) {
            System.out.println("插件配置的信息"+properties); }
    }

@Test public void testPlugin() throws IOException {
        SqlSession sqlSession = getSqlSession(); EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class); Employee e = mapper.getEmps(1); System.out.println(e); }

![70][]

##### 分页插件pageHelper #####

配置依赖

<dependency>
        <groupId>com.github.pagehelper</groupId>
        <artifactId>pagehelper</artifactId>
        <version>5.1.2</version>
    </dependency>

在全局配置文件中配置拦截器

在代码中使用PageHelper.startPage( )即可实现分页

@Test
    public void test() throws IOException {
        SqlSession sqlSession = getSqlSession();
        EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
    
        PageHelper.startPage(1,5);
    
        List<Employee> employeeList = mapper.getEmps();
        for (Employee e:employeeList) {
            System.out.println(employeeList);
        }
    }

插件会自动查询数量和进行分页的sql

![70 1][]

其他信息

@Test
    public void test() throws IOException {
        SqlSession sqlSession = getSqlSession();
        EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
    
        Page<Object> page = PageHelper.startPage(1, 5);
    
        List<Employee> employeeList = mapper.getEmps();
        for (Employee e:employeeList) {
            System.out.println(employeeList);
        }
    
        System.out.println("当前页码"+page.getPageNum());
        System.out.println("总记录数"+page.getTotal());
        System.out.println("每页记录数"+page.getPageSize());
        System.out.println("总页码"+page.getPages());
    }

还可以使用PageInfo达到更多分页的信息:

@Test
    public void test() throws IOException {
        SqlSession sqlSession = getSqlSession();
        EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
    
        Page<Object> page = PageHelper.startPage(1, 5);
    
        List<Employee> employeeList = mapper.getEmps();
    
        PageInfo info = new PageInfo(employeeList);
    
        for (Employee e:employeeList) {
            System.out.println(employeeList);
        }
    
        System.out.println("当前页码"+info.getPageNum());
        System.out.println("总记录数"+info.getTotal());
        System.out.println("每页记录数"+info.getPageSize());
        System.out.println("总页码"+info.getPages());
        System.out.println("是否第一页"+info.isIsFirstPage());
        System.out.println("是否最后一页"+info.isIsLastPage());

##### 批量操作 #####

可以使用<foreach>标签做批量，但是使用这个标签是对sql执行拼接，但是比如MySQL数据库对于大量数据的这种处理是特别麻烦！

使用批量：

@Test
    public void testBatch() throws IOException {
       String str = "mybatis-config.xml";
       Reader reader = Resources.getResourceAsReader(str);
       SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
        //可以执行批量操作sqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
        try {
            EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
            long start = System.currentTimeMillis();
            for (int i = 0; i < 10000; i++) {
                mapper.addEmployee(new Employee(UUID.randomUUID().toString().substring(1,5)+"lala","1","lala@qq.com"));
            }
            sqlSession.commit();
            long end = System.currentTimeMillis();
            System.out.println("执行的时长"+(end-start));
        } finally {
            sqlSession.close();
        }
    }

![70 2][]

![70 3][]

预编译SQL一次==》设置参数10000次==》数据库执行（批量，1次）

非批量：

public void testBatch() throws IOException {
       String str = "mybatis-config.xml";
       Reader reader = Resources.getResourceAsReader(str);
       SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
        //可以执行批量操作sqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
            long start = System.currentTimeMillis();
            for (int i = 0; i < 10000; i++) {
                mapper.addEmployee(new Employee(UUID.randomUUID().toString().substring(1,5)+"lala","1","lala@qq.com"));
            }
            sqlSession.commit();
            long end = System.currentTimeMillis();
            System.out.println("执行的时长"+(end-start));
        } finally {
            sqlSession.close();
        }
    }

![70 4][]

![70 5][]

预编译SQL==》设置参数==》执行——调用多少次执行多少次

与spring整合的时候，批量：

在spring配置文件中：

<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
        <constructor-arg name="sqlSessionFactory" ref="sqlSessionFactoryBean"/>
        <constructor-arg name="executorType" value="BATCH"/>
    </bean>

使用：在service中

@Autowired
    private SqlSession sqlSession;

这个就是可以使用批量的SqlSession

[70]: /images/20220524/142de75949d149908c14ded67c0f959b.png
[70 1]: /images/20220524/11865c3e5a1748a7af228a23ed0d6388.png
[70 2]: /images/20220524/fab3d08cd3e54f4a93594de4a559ad78.png
[70 3]: /images/20220524/ed985d78bb5c4660af2aa3952bb395d8.png
[70 4]: /images/20220524/d011091a852644ef9d03910c400fcd82.png
[70 5]: /images/20220524/43a1f925d5a2447b922aa44f221312ee.png