mybatis注解开发（增删改查多表查询）

1.创建personDao

public interface PersonDao {

    @Select("select * from person where id = #{id}")
    @Results(value = {
            @Result(column = "id", property = "id", id = true),
            @Result(column = "name", property = "name", id = true),
            @Result(column = "gender", property = "gender", id = true),
            @Result(column = "address", property = "address", id = true),
            @Result(column = "birthday", property = "birthday", id = true)
    })
    Person selectId(Integer id);

    @Select("select * from person where gender = ${gender} and birthday < #{birthday}")
    @Results(value = {
            @Result(column = "id", property = "id", id = true),
            @Result(column = "name", property = "name", id = true),
            @Result(column = "gender", property = "gender", id = true),
            @Result(column = "address", property = "address", id = true),
            @Result(column = "birthday", property = "birthday", id = true)
    })
    List<Person> selectPersonBy(Map map);

    @Select("select * from person where name like '%${name}%'")
    @Results(value = {
            @Result(column = "id", property = "id", id = true),
            @Result(column = "name", property = "name", id = true),
            @Result(column = "gender", property = "gender", id = true),
            @Result(column = "address", property = "address", id = true),
            @Result(column = "birthday", property = "birthday", id = true)
    })
    List<Person> selectPersonByLike(Map map);

    @Insert("insert into person (id, name, gender, address, birthday) values(#{id},#{name},#{gender},#{address},#{birthday})")
    @SelectKey(statement = {"select last_insert_id()"}, keyProperty = "id", before = false, resultType = Integer.class)
    void insertPerson(Person person);

    @Update("update person set name = #{name}, gender = #{gender}, address = #{address}, birthday = #{birthday} where id=#{id}")
    void updatePerson(Person p);

    @Delete("delete from person where id=#{id}")
    void deletePerson(Integer id);

    @SelectProvider(type = SqlHelper.class, method = "getSql")
    @Results(value = {
            @Result(column = "id", property = "id", id = true),
            @Result(column = "name", property = "name", id = true),
            @Result(column = "gender", property = "gender", id = true),
            @Result(column = "address", property = "address", id = true),
            @Result(column = "birthday", property = "birthday", id = true)
    })
    List<Person> selectPersonCondition(Map map);

    /*
     * 注解开发如果是多表关联查询还是要依赖配置文件
     * */
    @Select("select * from person p, orders o where p.id = o.pid and p.id = #{id}")
    @ResultMap("mappings.PersonMapper.selectOrderByPersonIdRM")
    public Person selectOrderByPersonId(Integer id);
}

 2.测试

package zhou;

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 zhou.dao.PersonDao;
import zhou.model.Person;

import java.io.InputStream;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

/**
 * mybatis注解形式的开发
 */
public class AppTest05 {
    SqlSessionFactory sessionFactory;

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


    /*
     * 二级缓存：可以跨SessonFactory范围之内跨Session
     * 可以放在内存跟硬盘上，一般会放在硬盘上，需要在Model类中implements Serializable该类
     * 实现该类之后便可将二级缓存放入硬盘
     * */
    @Test
    public void selectPersonByIdLazy() {
        SqlSession session = sessionFactory.openSession();
        /*获得所注册的接口的实现类*/
        PersonDao mapper = session.getMapper(PersonDao.class);
        try {
            Person person = mapper.selectId(1);
            System.out.println(person);
        } finally {
            session.close();
        }
    }

    @Test
    public void selectPersonBy() {
        SqlSession session = sessionFactory.openSession();
        /*获得所注册的接口的实现类*/
        PersonDao mapper = session.getMapper(PersonDao.class);
        try {
            HashMap<String, Object> map = new HashMap<>();
            map.put("gender", 0);
            map.put("birthday", new Date());
            List<Person> list = mapper.selectPersonBy(map);
            for (Person p : list
            ) {
                System.out.println(p.toString());
            }
        } finally {
            session.close();
        }
    }
    @Test
    public void selectPersonByLike() {
        SqlSession session = sessionFactory.openSession();
        /*获得所注册的接口的实现类*/
        PersonDao mapper = session.getMapper(PersonDao.class);
        try {
            HashMap<String, Object> map = new HashMap<>();
            map.put("name", "1");
            List<Person> list = mapper.selectPersonByLike(map);
            for (Person p : list
            ) {
                System.out.println(p.toString());
            }
        } finally {
            session.close();
        }
    }

    @Test
    public void insertPerson() {
        SqlSession session = sessionFactory.openSession();
        /*获得所注册的接口的实现类*/
        PersonDao mapper = session.getMapper(PersonDao.class);
        try {
            Person person = new Person();
            person.setName("xxx");
            person.setAddress("how are you");
            person.setBirthday(new Date());
            mapper.insertPerson(person);
            session.commit();
        }catch (Exception e) {
            e.printStackTrace();
            session.rollback();
        }
        finally {
            session.close();
        }
    }

    @Test
    public void updatePerson() {
        SqlSession session = sessionFactory.openSession();
        /*获得所注册的接口的实现类*/
        PersonDao mapper = session.getMapper(PersonDao.class);
        try {
            Person person = new Person();
            person.setId(5);
            person.setName("xxx");
            person.setAddress("how are you");
            person.setBirthday(new Date());
            mapper.updatePerson(person);
            session.commit();
        }catch (Exception e) {
            e.printStackTrace();
            session.rollback();
        }
        finally {
            session.close();
        }
    }

    @Test
    public void deletePerson() {
        SqlSession session = sessionFactory.openSession();
        /*获得所注册的接口的实现类*/
        PersonDao mapper = session.getMapper(PersonDao.class);
        try {
            mapper.deletePerson(5);
            session.commit();
        }catch (Exception e) {
            e.printStackTrace();
            session.rollback();
        }
        finally {
            session.close();
        }
    }

    @Test
    public void selectPersonCondition1() {
        SqlSession session = sessionFactory.openSession();
        /*获得所注册的接口的实现类*/
        PersonDao mapper = session.getMapper(PersonDao.class);
        try {
            HashMap<String, Object> map = new HashMap<>();
            map.put("address", "上海");
            map.put("birthday", new Date());
            List<Person> people = mapper.selectPersonCondition(map);
        } finally {
            session.close();
        }
    }

    @Test
    public void selectOrderByPersonId() {
        SqlSession session = sessionFactory.openSession();
        /*获得所注册的接口的实现类*/
        PersonDao mapper = session.getMapper(PersonDao.class);
        try {
            Person person = mapper.selectOrderByPersonId(1);
            System.out.println(person);
        } finally {
            session.close();
        }
    }

}

 3.1中用所用到SelectProvider的使用

（1）接口

@SelectProvider(type = SqlHelper.class, method = "getSql")
@Results(value = {
        @Result(column = "id", property = "id", id = true),
        @Result(column = "name", property = "name", id = true),
        @Result(column = "gender", property = "gender", id = true),
        @Result(column = "address", property = "address", id = true),
        @Result(column = "birthday", property = "birthday", id = true)
})
List<Person> selectPersonCondition(Map map);

 （2）创建utils.SqlHelper工具类

package zhou.Utils;

import org.apache.ibatis.jdbc.SqlBuilder;
import zhou.model.Person;

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

public class SqlHelper {
    /*获得所有可能传递过来的参数*/
    public String getSql(Map<String, Object> map){
        String name = (String) map.get("name");
        String gender = (String) map.get("gender");
        String address = (String) map.get("address");
        Date birthday = (Date) map.get("birthday");

        SqlBuilder.BEGIN();
        SqlBuilder.SELECT("*");
        SqlBuilder.FROM("person");
        if (name != null) {
            SqlBuilder.WHERE("name like '%" + name + "%'");
        }

        if (gender != null) {
            SqlBuilder.WHERE("gender = #{gender}");
        }

        if (address != null){
            SqlBuilder.WHERE("address like '%"+address +"%'");
        };

        if (birthday != null) {
            SqlBuilder.WHERE("birthday < #{birthday}" );
        }

        return SqlBuilder.SQL();
    }
}

 4.1中多表查询所用到的mapper配置

<resultMap id="selectOrderByPersonIdRM" type="zhou.model.Person" extends="BaseResultMap">
    <!--
        property: 参数是person Model类中定义的list名称
        ofType:要关联的Orders Model 类
    -->
    <collection property="orderList" ofType="zhou.model.Orders">
        <id column="order_id" jdbcType="INTEGER" property="orderId"/>
        <result column="total_prive" jdbcType="REAL" property="totalPrive"/>
        <result column="addr" jdbcType="VARCHAR" property="addr"/>
        <result column="pid" jdbcType="INTEGER" property="pid"/>
    </collection>
</resultMap>