mybatis-ck

入门

1. 编写mybatis的配置文件mybatis-config

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!--可以有多个environment default使用哪一个-->
    <environments default="mysql">
        <environment id="mysql">
            <!--事务管理器 JDBC Manager 只有两个-->
            <transactionManager type="JDBC"></transactionManager>
            <!--数据源 POOLED JNDI UNPOOLED 三种-->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/guli?serverTimezone=GMT%2B8"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <!--自己编写mapper地址-->
        <mapper resource="mapper/EduTeacherMapper.xml"></mapper>
    </mappers>
</configuration>

2. 自己实体类不说了
3. 编写实体类对应的mapper.xml文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--namespace命名空间唯一-->
<mapper namespace="test">
    <!--id唯一 resultType 返回值类型-->
    <select id="findAllTeacher" resultType="com.csc.bean.EduTeacher">
        select * from edu_teacher
    </select>
</mapper>

4. 测试

 @Test
    public void findAllTeacher() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = build.openSession();
        List<EduTeacher> eduTeacherList = sqlSession.selectList("test.findAllTeacher");
        for (EduTeacher eduTeacher:eduTeacherList)
        {
            System.out.println(eduTeacher);
        }
    }

以上为入门简单示例。

查询单个对象

 <select id="findTeacherById" parameterType="long" resultType="com.csc.bean.EduTeacher">
        select * from edu_teacher where id=#{id}
    </select>

 @Test
    public void findTeacherById()
    {
        EduTeacher o = sqlSession.selectOne("test.findTeacherById", 1189389726308478977L);
        System.out.println(o);

    }

注意selectOne参数类型要与parameterType类型一致。
聚合函数查询

<select id="findCountTeacher" resultType="int">
        select count(1) from edu_teacher
    </select>

 @Test
    public void findCountTeacher()
    {
        int o = sqlSession.selectOne("test.findCountTeacher");
        System.out.println(o);
    }

注意count（1）之间不能有空格
模糊查询

 <select id="findTeacherByName" parameterType="string" resultType="com.csc.bean.EduTeacher">
        select * from edu_teacher where name like '%${name}%'
    </select>

 public void findTeacherByName()
    {
        List<EduTeacher> eduTeacherList = sqlSession.selectList("test.findTeacherByName","王");
        for (EduTeacher edu:eduTeacherList )
        {
            System.out.println(edu);
        }
    }

${}会导致sql注入 应该使用#{} 括号里边的值为string或者基本类型时可以随便写。

<select id="findTeacherByName2" parameterType="string" resultType="com.csc.bean.EduTeacher">
         select * from edu_teacher where name like concat('%',#{name},'%')
    </select>

使用concat连接

@Test
    public void findTeacherByName2()
    {
        List<EduTeacher> eduTeacherList = sqlSession.selectList("test.findTeacherByName2","王");
        for (EduTeacher edu:eduTeacherList )
        {
            System.out.println(edu);
        }
    }

使用Mapper接口查询

1. 编写接口

public interface UserMapper {
    public List<User> findUser();
}

2. 编写Mapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.csc.dao.UserMapper">
    <select id="findUser" resultType="com.csc.entity.User">
        select * from user;
    </select>
</mapper>

注意 namespace为接口的全路径，xml名字与接口名一直，resultType也得写全不能省略

3. 测试代码

   @Test
    public void findUserTest()
    {
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> user = mapper.findUser();
        for (User u:user)
        {
            System.out.println(u);
        }
    }

设置别名

数据库表字段可能和实体类字段不同意 使用AS 实体类的属性名进行查询

  <select id="findAllTeacher"  resultType="com.csc.bean.EduTeacher">
        select gmt_create as gmtCreate  from edu_teacher
    </select>

gmtCreate 与EduTeacher属性名称一致才可以进行查询

@Param 多参数查询

使用多个条件的查询语句
java代码 使用@param注解

  public List<User> findUserByNameAndSex(@Param("name") String name , @Param("sex") String sex);

 <select id="findUserByNameAndSex" resultType="com.csc.entity.User">
        select * from user where username like concat('%',#{name},'%') and sex=#{sex}
    </select>

注意java方法中的@Param属性中value值要与sql语句的注入参数一致，区分大小写

类类型查询

java代码
新建查询使用的对象uservo

public class UserVo extends User {
}

参数使用uservo

public List<User> findUserByNameAndSex1(UserVo userVo);

也可以加入@param注解

public List<User> findUserByNameAndSex1(@Param("UserVo") UserVo userVo);

xml

 <select id="findUserByNameAndSex1" resultType="com.csc.entity.User">
        select * from user where username like concat('%',#{username},'%') and sex=#{sex}
    </select>

但是当使用(@Param(“UserVo”) UserVo userVo); 注解时 xml里注入参数要加UserVo.

   <select id="findUserByNameAndSex1" resultType="com.csc.entity.User">
        select * from user where username like concat('%',#{UserVo.username},'%') and sex=#{UserVo.sex}
    </select>

Map类型参数查询

map集合的key要与sql中注入参数名字一致

 public List<User> findUserByNameAndSex2( Map map);

<select id="findUserByNameAndSex2" resultType="com.csc.entity.User">
        select * from user where username like concat('%',#{username},'%') and sex=#{sex}
    </select>

UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        Map map=new HashMap();
        map.put("username","李");
        map.put("sex","1");
        List<User> userList = mapper.findUserByNameAndSex2(map);
        for(User u:userList)
        {
            System.out.println(u);
        }

新增

 public void addUser(User u);

 <insert id="addUser" >
        insert into user(username,sex,address,birthday) values(#{username},#{sex},#{address},#{birthday})
    </insert>

修改

 <update id="updateUser">
        update user set username=#{username} where id=#{id}
    </update>

public void updateUser(User u);

删除

    public void deleteUser(User u);

<delete id="deleteUser">
        delete from user where id=#{id}
    </delete>

ResultMap

当数据库表字段与实体类字段名称不一致时使用
先在xml中配置一个ResultMap

<resultMap id="eduTeacher"  type="com.csc.bean.EduTeacher">
        <id column="id" property="id" javaType="string" jdbcType="CHAR"></id>
        <result column="gmt_create" property="gmtCreate"></result>
        <result column="gmt_modified" property="gmtModified"></result>
    </resultMap>

id随便取值
id标签为主键 区别大小写 jdbctype需要数据库的字段可以参考类：org.apache.ibatis.type.JdbcType中的字段类型填写
javaType与jdbcType可以不写会自动匹配

<select id="queryAllTeacher" resultMap="eduTeacher">
         select * from edu_teacher
    </select>

查询时使用Resultmap

 @Test
   public void test1()
   {
       EduTeacherMapper mapper = sqlSession.getMapper(EduTeacherMapper.class);
       List<EduTeacher> eduTeachers = mapper.queryAllTeacher();
       System.out.println(eduTeachers);
   }

一对一 association

实体类

User类用户类
Orders订单类
一个用户对应一个订单

public class Orders {
    private Integer id;

    private Integer userId;

    private String number;

    private Date createtime;

    private String note;
    
    //用户信息
    private User user;

xml文件

<mapper namespace="com.csc.dao.OrdersMapper">
    <resultMap id="orderMap" type="com.csc.entity.Orders">
        <id property="id" column="id"></id>
        <result property="number" column="number"></result>
        <association property="user" javaType="com.csc.entity.User">
            <id property="id" column="id"></id>
            <result property="username" column="username"></result>
        </association>
    </resultMap>
    <select id="getAllOrders" resultMap="orderMap">
        select o.id,o.number,u.* from user u inner join orders o on u.id=o.user_id
    </select>
</mapper>

查询使用结果集为resultMap
使用association标签来体现Orders类中的user属性
以上查询结果中未写明对应属性的不会被查询出来
查询结果

Orders [id=3, userId=null, number=1000010, createtime=null, note=null, user=User [id=3, username=王五, sex=null, birthday=null, address=null], orderdetails=null]
User [id=3, username=王五, sex=null, birthday=null, address=null]
===========================================================================
Orders [id=4, userId=null, number=1000011, createtime=null, note=null, user=User [id=4, username=王五, sex=null, birthday=null, address=null], orderdetails=null]
User [id=4, username=王五, sex=null, birthday=null, address=null]
===========================================================================
Orders [id=5, userId=null, number=1000012, createtime=null, note=null, user=User [id=5, username=张三, sex=null, birthday=null, address=null], orderdetails=null]
User [id=5, username=张三, sex=null, birthday=null, address=null]
===========================================================================

orders中只有id与number中有值 user中只有id与username有值
不会进行自动匹配
如果需要自动匹配需要在mybatis配置文件中加入setting autoMappingBehavior

先设置成PARTIAL
结果还是一样只匹配orders中属性的值不会匹配association里对象属性的值

设置成FULL

Orders [id=3, userId=1, number=1000010, createtime=Wed Feb 04 13:22:35 GMT+08:00 2015, note=null, user=User [id=3, username=王五, sex=0, birthday=Thu Jul 10 00:00:00 GMT+08:00 2014, address=北京市], orderdetails=null]
User [id=3, username=王五, sex=0, birthday=Thu Jul 10 00:00:00 GMT+08:00 2014, address=北京市]
===========================================================================
Orders [id=4, userId=1, number=1000011, createtime=Tue Feb 03 13:22:41 GMT+08:00 2015, note=null, user=User [id=4, username=王五, sex=0, birthday=Thu Jul 10 00:00:00 GMT+08:00 2014, address=北京市], orderdetails=null]
User [id=4, username=王五, sex=0, birthday=Thu Jul 10 00:00:00 GMT+08:00 2014, address=北京市]
===========================================================================
Orders [id=5, userId=10, number=1000012, createtime=Thu Feb 12 16:13:23 GMT+08:00 2015, note=null, user=User [id=5, username=张三, sex=1, birthday=Thu Jul 10 00:00:00 GMT+08:00 2014, address=北京市], orderdetails=null]
User [id=5, username=张三, sex=1, birthday=Thu Jul 10 00:00:00 GMT+08:00 2014, address=北京市]
===========================================================================

都有值匹配

使用Extends 可以分割resultMap

<mapper namespace="com.csc.dao.OrdersMapper">
    <resultMap id="orderMap" type="com.csc.entity.Orders">
        <id property="id" column="id"></id>
        <result property="userId" column="user_id"></result>
    </resultMap>
    <resultMap id="userMap" type="com.csc.entity.Orders" extends="orderMap">
        <association property="user" javaType="com.csc.entity.User">
            <id property="id" column="id"></id>
            <result property="username" column="username"></result>
        </association>
    </resultMap>
    <select id="getAllOrders" resultMap="userMap">
        select o.*,u.* from user u inner join orders o on u.id=o.user_id
    </select>
</mapper>

使用extends=“orderMap” 可以把id为orderMap内容继承到自己的标签内部。

多对一collection

一个订单对应一个用户 ，但是一个订单内可以有多个商品
在订单类中加List

public class Orders {
    private Integer id;

    private Integer userId;

    private String number;

    private Date createtime;

    private String note;
    
    //用户信息
    private User user;
    
    //订单明细
    private List<Orderdetail> orderdetails;

 <resultMap id="orderdetailsMap" type="com.csc.entity.Orders" extends="userMap">
        <collection property="orderdetails" ofType="com.csc.entity.Orderdetail">
            <id column="id" property="id"></id>
        </collection>
    </resultMap>
    <select id="getAllOrdersAndOrderDetail" resultMap="orderdetailsMap">
        select u.*,o.*,od.* from orders o inner join orderdetail od on o.id=od.orders_id
			inner join user u on o.user_id=u.id
    </select>

在resultMap 加入collection标签 ofType=list集合中的类

动态SQL

where if

 <select id="getUserByWhere" resultType="csc.entity.User">
        select * from user
        <where>
            <if test="id!=null and id!=''">
                and id=#{id}
            </if>
            <if test="username!=null and username!=''">
                and username like concat('%',#{username},'%')
            </if>
        </where>
    </select>

使用where标签来取代sql中的where语句可以实现动态查询