MyBatis框架之注解开发

MyBatis注解开发之查询对象:

新建接口, 使用注解:

package com.rl.dao;

import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import com.rl.model1.Person;

public interface PersonDao {

    @Select("select * from person p where p.person_id = #{personId}")
    @Results(value={
            @Result(column="person_id", property="personId", id=true),
            @Result(column="name", property="name"),
            @Result(column="gender", property="gender"),
            @Result(column="person_addr", property="personAddr"),
            @Result(column="birthday", property="birthday")
    })
    public Person selectPersonById(Integer personId);
}

其中"@select"注解相当于配置文件中的select标签, "@result"注解相当于resultMap标签中的映射

项目结构图:

测试代码:

package com.rl.test;

import java.io.InputStream;

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 org.junit.Before;
import org.junit.Test;

import com.rl.dao.PersonDao;
import com.rl.model1.Person;

public class MyBatisTest12 {

    SqlSessionFactory sqlSessionFactory;
    
    @Before
    public void setUp() throws Exception {
        InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
        //注册接口类
        sqlSessionFactory.getConfiguration().addMapper(PersonDao.class);
    }

    @Test
    public void test() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //需要实现类, 但不是手写, 而是直接获取
        //获取注册的接口的实现类
        PersonDao personDao = sqlSession.getMapper(PersonDao.class);
        
        try {
            Person person = personDao.selectPersonById(4);
            System.out.println(person);
        } finally{
            sqlSession.close();
        }
    }
}

输出结果:

Person [personId=4, name=黄忠, gender=1, personAddr=上海, birthday=Sun Sep 09 00:00:00 CST 2018, orderList=null]

MyBatis注解开发之查询集合:

接口注解代码:

@Select("select * from person p where p.gender = #{gender} and p.birthday < #{birthday}")
    @Results(value={
            @Result(column="person_id", property="personId", id=true),
            @Result(column="name", property="name"),
            @Result(column="gender", property="gender"),
            @Result(column="birthday", property="birthday"),
            @Result(column="person_addr", property="personAddr")
    })
    public List<Person> selectPersonByQC(QueryCondition qc);

测试代码:

@Test
    public void test1() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        PersonDao personDao = sqlSession.getMapper(PersonDao.class);
        
        try {
            QueryCondition qc = new QueryCondition();
            qc.setBirthday(new Date());
            qc.setGender(1);
            List<Person> pList = personDao.selectPersonByQC(qc);
            for(Person p: pList){
                System.out.println(p);
            }
        } finally{
            sqlSession.close();
        }
    }

MyBatis注解开发之模糊查询:

接口注解代码(模糊查询时不能使用String作为参数, 必须使用Map或者对象作为参数):

@Select("select * from person p where p.name like '%${name}%'")
    @Results(value={
            @Result(column="person_id", property="personId", id=true),
            @Result(column="name", property="name"),
            @Result(column="gender", property="gender"),
            @Result(column="birthday", property="birthday"),
            @Result(column="person_addr", property="personAddr")
    })
    public List<Person> selectPersonByLike(QueryCondition qc);

测试代码:

@Test
    public void test2() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        PersonDao personDao = sqlSession.getMapper(PersonDao.class);
        
        try {
            QueryCondition qc = new QueryCondition();
            qc.setName("忠");
            List<Person> pList = personDao.selectPersonByLike(qc);
            for(Person p: pList){
                System.out.println(p);
            }
        } finally{
            sqlSession.close();
        }
    }

输出结果:

Person [personId=4, name=黄忠, gender=1, personAddr=上海, birthday=Sun Sep 09 00:00:00 CST 2018, orderList=null]

MyBatis注解开发之添加(带主键返回):

接口注解代码:

/**
     * @SelectKey: 主键返回主键:
     *  before: 在mysql数据库中, 主键的生成返回是在插入语句之后, 所以在配置文件中是配置成after, 对应此处的before属性则是false
     *  keyProperty: 主键名称, 此时为实体类中的"personId"
     *  resultType: 返回值类型, 主键的数据类型自然是Integer
     *  statement: 插入主键的sql语句, mysql中的为"select LAST_INSERT_ID()"
     * 
     * @param p
     */
    @Insert("insert into person (person_id, name, gender, birthday, person_addr) "
            + "values(#{personId}, #{name}, #{gender}, #{birthday}, #{personAddr})")
    @SelectKey(before = false, keyProperty = "personId", resultType = Integer.class, statement = { ""
            + "select LAST_INSERT_ID()" })
    public void insertPerson(Person p);

测试代码:

@Test
    public void test3() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        PersonDao personDao = sqlSession.getMapper(PersonDao.class);
        try {
            Person p = new Person();
            p.setName("郑十");
            p.setGender(1);
            p.setBirthday(new Date());
            p.setPersonAddr("木星");
            personDao.insertPerson(p);
            sqlSession.commit();
        } catch (Exception e) {
            e.printStackTrace();
            sqlSession.rollback();
        }finally{
            sqlSession.close();
        }
    }

结果截图:

未返回生成主键:

返回生成的主键:

MyBatis注解开发之修改:

接口注解代码:

@Update("update person p set "
            + "p.name = #{name},"
            + "p.gender = #{gender},"
            + "p.person_addr = #{personAddr},"
            + "p.birthday = #{birthday} "
            + "where p.person_id = #{personId}")
    public void updatePerson(Person p);

测试代码:

@Test
    public void test4() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        PersonDao personDao = sqlSession.getMapper(PersonDao.class);
        try {
            Person p = new Person();
            p.setPersonId(4000029);
            p.setName("郑十");
            p.setGender(1);
            p.setBirthday(new Date());
            p.setPersonAddr("土星");
            personDao.updatePerson(p);
            sqlSession.commit();
        } catch (Exception e) {
            e.printStackTrace();
            sqlSession.rollback();
        }finally{
            sqlSession.close();
        }
    }

MyBatis注解开发之删除:

接口注解代码:

@Delete("delete from person where person_id = #{personId}")
    public void deletePersonById(Integer personId);

测试代码:

@Test
    public void test5() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        PersonDao personDao = sqlSession.getMapper(PersonDao.class);
        
        try {
            personDao.deletePersonById(4000025);
            sqlSession.commit();
        } catch (Exception e) {
            e.printStackTrace();
            sqlSession.rollback();
        }finally{
            sqlSession.close();
        }
    }

MyBatis注解开发之动态条件组合查询:

动态sql组合可以解决当某些字段没有设置就进行修改时并不会将null赋值给数据库导致该字段变为null

专门用于组装动态sql的注解: @SelectProvider

"@SelectProvider"注解有两个属性: ①type ②method

"type": 指定工具类的类对象

"method": 指定工具类中的方法名

项目结构图:

工具类:

package com.rl.util;

import java.util.Date;
import java.util.Map;

import org.apache.ibatis.jdbc.SqlBuilder;

public class SqlHelper {

    public String getSql(Map<String, Object> map){
        //先根据key获取value
        String name = (String)map.get("name");
        Integer gender = (Integer)map.get("gender");
        String personAddr = (String)map.get("personAddr");
        Date birthday = (Date)map.get("birthday");
        
        //初始化准备
        SqlBuilder.BEGIN();
        //选择的列, "*"是查询全部列
        SqlBuilder.SELECT("*");
        //具体查询哪张表"person"表
        SqlBuilder.FROM("person");
        //组装sql
        if(name != null){
            SqlBuilder.WHERE("name like '%"+name+"%'");
        }
        if(personAddr != null){
            SqlBuilder.WHERE("person_addr like '%"+personAddr+"%'");
        }
        if(gender != null){
            SqlBuilder.WHERE("gender = #{gender}");
        }
        if(birthday != null){
            SqlBuilder.WHERE("birthday < #{birthday}");
        }
        //返回sql
        String sql = null;
        try {
            sql = SqlBuilder.SQL();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return sql;
    }
}

接口注解代码:

@SelectProvider(type=SqlHelper.class, method="getSql")
    @Results(value={
            @Result(column="person_id", property="personId", id=true),
            @Result(column="name", property="name"),
            @Result(column="gender", property="gender"),
            @Result(column="birthday", property="birthday"),
            @Result(column="person_addr", property="personAddr")
    })
    public List<Person> selectPersonByCondition(Map<String, Object> map);

测试代码:

@Test
    public void test6() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        PersonDao personDao = sqlSession.getMapper(PersonDao.class);
        
        try {
            Map<String, Object> map = new HashMap<String, Object>();
            map.put("name", "十");
            map.put("gender", 1);
            map.put("birthday", new Date());
            map.put("personAddr", "土");
            List<Person> pList = personDao.selectPersonByCondition(map);
            for(Person p: pList){
                System.out.println(p);
            }
        } catch(Exception e){
            e.printStackTrace();
        }finally{
            sqlSession.close();
        }
    }

MyBatis注解开发之关联查询:

"@Results"注解不能用于关联查询, 关联查询必须使用"@ResultMap"注解, 并且需要依赖配置文件

接口注解代码:

@Select("select * from person p, orders o where p.person_id = o.person_id and p.person_id = #{personId}")
    @ResultMap("com.rl.mapper.PersonMapper.selectOrdersByPersonIdRM")
    public Person selectOrdersByPersonId(Integer personId);

测试代码:

@Test
    public void test7() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        PersonDao personDao = sqlSession.getMapper(PersonDao.class);
        
        try {
            Person person = personDao.selectOrdersByPersonId(4);
            System.out.println(person);
        } catch(Exception e){
            e.printStackTrace();
        }finally{
            sqlSession.close();
        }
    }