MyBatis

1.resultMap结果集映射 

什么字段不一样写什么字段

  <!--column 数据库语句中的字段   property 实体类里的字段-->
    <resultMap id="UserMap" type="com.kuang.pojo.User">
        <result column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="password" property="pwd"/>
    </resultMap>
    <select id="selectUserById" resultMap="UserMap" >
        select * from user where id=#{id}
    </select>

2.日志工厂

settings里面设置setting 

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <properties resource="db.properties">
    </properties>
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>
<typeAliases>
    <package name="com.kuang.pojo.User"/>
</typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">

                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
<mappers>

    <mapper resource="com/kuang/dao/UserMapper.xml"/>
</mappers>
</configuration>

3.log4j配置

properties配置文件

log4j.rootLogger=DEBUG,console,file

#控制台输出的相关设置
log4j.appender.console = org.apache.log4j.ConsoleAppender
log4j.appender.console.Target = System.out
log4j.appender.console.Threshold=DEBUG
log4j.appender.console.layout = org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%c]-%m%n

#文件输出的相关设置
log4j.appender.file = org.apache.log4j.RollingFileAppender
log4j.appender.file.File=./log/kuang.log
log4j.appender.file.MaxFileSize=10mb
log4j.appender.file.Threshold=DEBUG
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}][%c]%m%n

#日志输出级别
log4j.logger.org.mybatis=DEBUG
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

配置文件里设置

<settings>
        <setting name="logImpl" value="LOG4J"/>
    </settings>

4.多对一查询

复杂属性 对象用association，集合用collection

JavaType 用来指定实体类中属性的类型

ofType   用来指定映射到List或者是集合中的pojo类型  private List<Student> students

方法一

<select id="selectAll" resultMap="Student">
        select * from student
    </select>
    <resultMap id="Student" type="Student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
<!--            property 对应实体类字段  column 对应数据库语句中的字段  javaType 返回值类型 select 与查询标签的id对应-->
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>
    <select id="getTeacher" resultType="Teacher">
        select * from teacher where id=#{id}
    </select>

方法二

   <select id="selectAll2" resultMap="StudentTeacher2">
        select s.id sid,s.name sname,t.name tname from teacher t,student s where s.tid=t.id
    </select>
    <resultMap id="StudentTeacher2" type="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="Teacher">
            <result property="name" column="tname"/>
        </association>
    </resultMap>

5.多对多查询

   复杂属性 对象用association，集合用collection，集合中的泛型信息用ofType获取

private List<Student> students

 <select id="selectTeacher" resultMap="TeacherStudent">
        select s.name sname,s.id sid,t.name tname,t.id tid
        from student s,teacher t
        where t.id=s.id and t.id=#{tid}
    </select>
    <resultMap id="TeacherStudent" type="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        <!--集合中的泛型信息用ofType获取 private List<Student> students-->
        <collection property="students" ofType="Student">
            <result property="name" column="sname"/>
            <result property="id" column="sid"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>

 <select id="selectTeacher2" resultMap="TeacherStudent2">
        select * from teacher where id=#{tid}
    </select>
    <resultMap id="TeacherStudent2" type="Teacher">
        <collection property="students" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId" column="id"></collection>
    </resultMap>
    <select id="getStudentByTeacherId" resultType="Student">
        select * from student where tid=#{tid}
    </select>

6.动态SQL

使用<where>可以添加and也可以不添加and，where条件都不匹配时则查询全部

1.choose、when、otherwise

choose至少要有一个匹配

按顺序进行匹配，只要满足其中的一个自动结束匹配

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <choose>
    <when test="title != null">
      AND title like #{title}
    </when>
    <when test="author != null and author.name != null">
      AND author_name like #{author.name}
    </when>
    <otherwise>
      AND featured = 1
    </otherwise>
  </choose>
</select>

2.set标签

set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号

<update id="updateAuthorIfNecessary">
  update Author
    <set>
      <if test="username != null">username=#{username},</if>
      <if test="password != null">password=#{password},</if>
      <if test="email != null">email=#{email},</if>
      <if test="bio != null">bio=#{bio}</if>
    </set>
  where id=#{id}
</update>

3.foreach

传入的集合为collection="ids"将其遍历为item="id"  

 这一部分为拼接

open="and (" close=")" separator="or"

<!-- select * from blog where 1=1 and (id=1 or id=2 or id=3)-->
    <select id="selectBlogForeach" parameterType="map" resultType="Blog">
        select * from blog
        <where>
            <foreach collection="ids" item="id" open="and (" close=")" separator="or">
                id=#{id}
            </foreach>
        </where>
    </select>

测试类

 @Test
    public void test2(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Map map = new HashMap();
        List<Integer> list = new ArrayList<>();
        list.add(1);
        list.add(2);
        map.put("ids",list);
        List<Blog> blogs = mapper.selectBlogForeach(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }

别名

别名	映射的类型
_byte	byte
_long	long
_short	short
_int	int
_integer	int
_double	double
_float	float
_boolean	boolean
string	String
byte	Byte
long	Long
short	Short
int	Integer
integer	Integer
double	Double
float	Float
boolean	Boolean
date	Date
decimal	BigDecimal
bigdecimal	BigDecimal
object	Object
map	Map
hashmap	HashMap
list	List
arraylist	ArrayList
collection	Collection
iterator	Iterator