Mybatis基础入门

SqlMapConfig.xml

<?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>

    <!-- 和spring整合后 environments配置将废除 -->
    <environments default="development">
        <environment id="development">
            <!-- 使用jdbc事务管理 -->
            <transactionManager type="JDBC" />
            <!-- 数据库连接池 -->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver" />
                <property name="url"
                    value="jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8" />
                <property name="username" value="root" />
                <property name="password" value="mysql" />
            </dataSource>
        </environment>
    </environments>

    <!-- 加载mapper.xml -->
    <!-- 扫描加载mapper.xml -->
    <mappers>
        <package name="com.kaisen.jdbc.mapper" />
    </mappers>

</configuration>

mapper.xml映射文件

早期ibatis的映射文件以表名命名，后期mybatis 映射文件命名方式为：表名Mapper.xml。。
命名方式是不固定的。

映射文件使用mybatis-3-mapper.dtd

UserMapper.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映射文件是以sql语句为单位进行配置，最终将sql语句封装到MappedStatement对象中 namespace命名空间作用是更好对sql语句进行隔离，方便管理sql 
    注意：后期讲mybatis的mapper代理开发方式时namespace有特殊的作用？？？ -->
<mapper namespace="com.kaisen.jdbc.mapper.UserMapper">
    <!-- 打开二级缓存 -->
    <cache type="org.mybatis.caches.ehcache.EhcacheCache" > 
        <property name="timeToIdleSeconds" value="3600"/>
        <property name="timeToLiveSeconds" value="3600"/>
        <!-- 同ehcache参数maxElementsInMemory -->
        <property name="maxEntriesLocalHeap" value="1000"/>
        <!-- 同ehcache参数maxElementsOnDisk -->
        <property name="maxEntriesLocalDisk" value="10000000"/>
        <property name="memoryStoreEvictionPolicy" value="LRU"/>
    </cache>


    <!-- 根据用户id查询一个用户信息 select：用于查询，主要配置sql语句、输入参数类型、
    输出结果类型 最终该 select 标签 中所配置的内容会封装到MappedStatement对象，可以将该 
        select称为是一个Statement id：唯 一标识 namespace下的一个sql语句，
        将id称为Statement的id parameterType：指定输入参数的类型（简单类型、自定义pojo） 
        #{}：表示一个占位符号，占位符号可以防止sql注入 #{value}：value表示接收输入参数的值，
        如果接收的输入参数是简单类型，#{}里边可以写value或其它的名称 
        resultType：将sql查询结果集映射成java对象 将多个列的值映射到一个对象中，需要定义的pojo，
        resultType映射规则是sql查询列名和pojo的属性名必须一致方可完成映射 
        resultType 指定单条记录所映射的java对象 -->

        <!-- 在statement中设置useCache=false可以禁用当前select语句的二级缓存，
        即每次查询都会发出sql去查询，默认情况是true，即该sql使用二级缓存 -->

        <!--增删改操作 flushCache="true" 属性，默认情况下为true即刷新缓存，
        如果改成false则不会刷新。使用缓存时如果手动修改数据库表中的查询数据会出现脏读。 -->

    <!-- 根据id查询用户 -->
    <select id="findUserById" parameterType="int" resultType="user" useCache="false">
        SELECT
        id,username,birthday,sex,address FROM USER WHERE id = #{id}
    </select>

    <update id="updateUser" parameterType="user">
        update user set username=#{username},address=#{address}
    </update>
    <!-- 根据用户名模糊查询用户 -->
    <select id="findUserByName" parameterType="java.lang.String"
        resultType="user">
        SELECT id,username,birthday,sex,address FROM USER WHERE username like
        #{name}
        <!-- SELECT id,username,birthday,sex,address FROM USER WHERE username like 
            '%${value}%' -->
    </select>

    <!-- 输入参数类 -->
    <!-- 根据包装类中的pojo属性查询数据 -->
    <select id="findUserByQuerUserVoUser" parameterType="querUserVo"
        resultType="user">
        SELECT id,username,birthday,sex,address FROM USER WHERE id
        = #{user.id}
    </select>

    <!-- 输入参数为hashmap 根据map的key取值 -->
    <select id="findUserByHashMap" parameterType="hashmap"
        resultType="user">
        SELECT id,username,birthday,sex,address FROM USER WHERE sex
        = '${sex}' and username like '%${username}%'
    </select>


    <!-- 输出参数映射 -->
    <!-- 输出参数为简单类型 -->
    <select id="findUserCount" parameterType="querUserVo"
        resultType="int">
        SELECT count(id) FROM USER WHERE username like
        '%${user.username}%'
    </select>

    <!-- 输出参数为map 字段名与属性名不一致 -->
    <!-- 定义resultMap type映射的pojo类 id 唯一标示 -->
    <resultMap type="user" id="querUserResultMap">
        <!--结果集中的唯一标示 column结果集中的列名 property 映射的pojo类的属性名 -->
        <id column="id_" property="id" />

        <!-- 普通列 -->
        <result column="username_" property="username" />
        <result column="birthday_" property="birthday" />
        <result column="sex_" property="sex" />
        <result column="address_" property="address" />

    </resultMap>
    <select id="findUserResultMap" parameterType="string" resultMap="querUserResultMap">
        SELECT id id_,username username_,birthday birthday_,sex sex_,address
        address_ FROM USER WHERE sex = #{sex}
    </select>


    <!-- 动态sql 语法 -->
    <!-- 定义sql片段 -->
    <sql id="findByUsernameAndSexSqlWhere">
        <!-- 判断用户是否有值 -->
        <if test="user!=null">
            <if test="user.username!=null and user.username!=''">
                and username like '%${user.username}%'
            </if>
            <if test="user.sex!=null and user.sex!=''">
                and sex = #{user.sex}
            </if>

        </if>

    </sql>

    <!-- 综合条件查询   引用动态sql片段 -->
    <select id="findUserList" parameterType="querUserVo" resultType="user">
        SELECT id,username,birthday,sex,address FROM USER
        <!-- 使用动态sql片段  where包裹 -->
        <where>
            <!-- 引入sql片段 -->
            <include refid="findByUsernameAndSexSqlWhere"/>
        </where>
    </select>


    <!-- foreach 循环遍历输入参数 -->

    <!-- sql片段 -->
    <sql id="foreachUserIds">
        <!-- collection为要遍历的集合 item为遍历出来的每一个元素  open为开始循环时拼接的sql close为结束时拼接的sql separator每次循环中间拼接的sql -->
        <foreach collection="ids" item="id" open="and id in(" close=")" separator=",">
            #{id}
        </foreach>
    </sql>
    <select id="findUsersByIds" parameterType="querUserVo" resultType="user">
        SELECT id,username,birthday,sex,address FROM USER
        <where>
            <include refid="foreachUserIds"/>
        </where>
    </select>






    <!-- 添加用户 -->
    <!-- <insert id=""></insert> -->

    <!-- 删除用户 -->
    <!-- <delete id=""></delete> -->

</mapper>

OrderCustomerMapper.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映射文件是以sql语句为单位进行配置，最终将sql语句封装到MappedStatement对象中 namespace命名空间作用是更好对sql语句进行隔离，方便管理sql 
    注意：后期讲mybatis的mapper代理开发方式时namespace有特殊的作用？？？ -->
<mapper namespace="com.kaisen.jdbc.mapper.OrderCustomerMapper">


    <!-- 使用resultType 完成 -->
    <select id="findOrdersCustomer" resultType="com.kaisen.po.OrdersCustomer">
        SELECT
        orders.*,USER.username,USER.address FROM orders,USER WHERE
        orders.user_id=USER.id

    </select>

    <!-- 使用resultMap完成 -->
    <!-- 定义resultMap -->
    <resultMap type="orders" id="ordersMap">
        <!-- 唯一标是 -->
        <id column="id" property="id" />

        <!-- 属性 -->
        <result column="user_id" property="userId" />
        <result column="number" property="number" />
        <result column="createtime" property="createtime" />
        <result column="note" property="note" />

        <!-- 关联映射单个对象  标签注意使用javaType指定关联映射的对象类型 -->
        <association property="user" javaType="com.kaisen.po.User" >
            <id column="user_id" property="id" />

            <result column="username" property="username" />
            <result column="address" property="address" />

        </association>
    </resultMap>

    <!-- 查询订单  延迟加载用户信息 -->

        <resultMap type="orders" id="ordersLazyingUserMap">
        <!-- 唯一标是 -->
        <id column="id" property="id" />

        <!-- 属性 -->
        <result column="user_id" property="userId" />
        <result column="number" property="number" />
        <result column="createtime" property="createtime" />
        <result column="note" property="note" />

        <!-- 关联映射单个对象  标签注意使用javaType指定关联映射的对象类型 -->
        <association property="user" javaType="com.kaisen.po.User" select="com.kaisen.jdbc.mapper.UserMapper.findUserById" column="user_id">
        </association>
    </resultMap>
    <select id="findOrdersLazingUser" resultMap="ordersLazyingUserMap">
        select * from orders
    </select>



    <select id="findOrdersCustomerMap" resultMap="ordersMap">
        SELECT
        orders.*,user.username,user.address FROM orders,USER WHERE
        orders.user_id=user.id

    </select>

    <!-- resultMap 多表关联多对多查询 -->
    <!-- 继承resultMap -->
    <resultMap type="orders" id="orderDetialMap" extends="ordersMap">

        <!-- 关联映射集合对象   标签注意使用ofType指定关联映射的List中pojo的类型。-->
        <collection property="orderdetails" ofType="com.kaisen.po.Orderdetail">
            <id column="orderdetail_id" property="id"/>
            <result column="id" property="ordersId"/>
            <result column="items_id" property="itemsId"/>
            <result column="items_num" property="itemsNum"/>

        </collection>

    </resultMap>
    <!-- 查询订单关联的用户与明细 -->
    <select id="findOrdersCustomerOrderDetilsMap" resultMap="orderDetialMap">
        SELECT
        orders.*,
        user.username,
        user.address,
        orderdetail.id orderdetail_id,
        orderdetail.items_id,
        orderdetail.items_num

         FROM orders,USER,orderdetail WHERE
        orders.user_id=user.id and orderdetail.orders_id = orders.id

    </select>


    <!-- 关联查询用户对应的订单  订单对应的明细  明细对应的商品信息 -->

    <!-- 定义resultMap -->
    <resultMap type="user" id="userOrdersDetailsItemsMap">
        <id column="user_id" property="id"/>
        <result column="username" property="username"/>
        <result column="address" property="address"/>
        <!-- 用户关联的订单表 一对多 -->
        <collection property="orderslist" ofType="com.kaisen.po.Orders">
            <id column="id" property="id"/>
            <result column="user_id" property="userId"/>
            <result column="number" property="number"/>
            <result column="createtime" property="createtime"/>
            <result column="note" property="note"/>
            <!-- 订单关联的订单明细表 一对多-->
            <collection property="orderdetails" ofType="com.kaisen.po.Orderdetail">
                <id column="orderdetail_id" property="id"/>
                <result column="id" property="ordersId"/>
                <result column="items_id" property="itemsId"/>
                <result column="items_num" property="itemsNum"/>
                <!-- 订单明细表关联的商品信息表  一对一-->
                <association property="items" javaType="com.kaisen.po.Items">
                    <id column="items_id" property="id"/>

                    <result column="name" property="name"/>
                    <result column="price" property="price"/>
                    <result column="detail" property="detail"/>
                    <result column="pic" property="pic"/>
                    <result column="items_createtime" property="createtime"/>
                </association>
            </collection>
        </collection>
    </resultMap>

    <select id="findUserOrdersDetailsItemsMap" resultMap="userOrdersDetailsItemsMap">
        SELECT
        orders.*,
        user.username,
        user.address,
        orderdetail.id orderdetail_id,
        orderdetail.items_id,
        orderdetail.items_num,
        items.name,
        items.price,
        items.detail,
        items.pic,
        items.createtime items_createtime
         FROM orders,USER,orderdetail,items WHERE
        orders.user_id=user.id 
        and orderdetail.orders_id = orders.id
        and orderdetail.items_id = items.id
    </select>


</mapper>

pojo类

User类

public class User implements Serializable{
    private int id;
    private String username;// 用户姓名
    private String sex;// 性别
    private Date birthday;// 生日
    private String address;// 地址

    private List<Orders> orderslist;
    实现getter和setter方法
{

Orders类与OrdersCustomer类

public class Orders implements Serializable{
    private Integer id;

    private Integer userId;

    private String number;

    private Date createtime;

    private String note;

    private User user;

    private List<Orderdetail> orderdetails;
    }

public class OrdersCustomer extends Orders {
    //继承了orders类
    private String username;
    private String address;
    {

定义接口管理方法(接口名与配置mapper名一致且同路径)

public interface OrderCustomerMapper {

    List<OrdersCustomer> findOrdersCustomer() throws Exception;

    List<Orders> findOrdersCustomerMap() throws Exception;

    List<Orders> findOrdersCustomerOrderDetilsMap() throws Exception;

    List<User> findUserOrdersDetailsItemsMap() throws Exception;

    List<Orders> findOrdersLazingUser() throws Exception;


}

public interface UserMapper {
    public User findUserById(Integer id) throws Exception;

    public List<User> findUserByName(String name) throws Exception;

    public User findUserByQuerUserVoUser(QuerUserVo quervo) throws Exception;

    public List<User> findUserByHashMap(Map< String, Object> map) throws Exception;

    public int findUserCount(QuerUserVo quervo) throws Exception;

    public List<User> findUserResultMap(String sex) throws Exception;

    public List<User> findUserList(QuerUserVo quervo) throws Exception;

    public List<User> findUsersByIds(QuerUserVo quer) throws Exception;

    public void updateUser(User user) throws Exception;
}

测试

public class TestForDao {

    private SqlSessionFactory sqlSessionFactory;

    @Before
    public void setUp() throws IOException {
        // mybatis全局配置文件
        String resource = "SqlMapConfig.xml";

        // 根据mybatis的全局配置文件构造 一个流
        InputStream inputStream = Resources.getResourceAsStream(resource);

        // 创建SqlSessionFactory
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    }

    // 使用dao查询
    @Test
    public void testForFind() {
        UserDao userDao = new UserDaoImpl(sqlSessionFactory);
        try {
            User user = userDao.findByUserId(1);
            System.out.println(user);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    // 使用代理先查询
    @Test
    public void testForFind1() {
        SqlSession sqlSession = sqlSessionFactory.openSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        try {
            User user = mapper.findUserById(1);

            System.out.println(user);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    // 使用代理查询
    @Test
    public void testForFind2() {
        SqlSession sqlSession = sqlSessionFactory.openSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        // assert mapper==null;

        try {
            // 条件查询
            // List<User> list = mapper.findUserByName("张三");
            // 模糊查询
            List<User> list = mapper.findUserByName("%张%");
            System.out.println(list);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    // 根据包装类中的pojo属性查询
    @Test
    public void testForFindUserByVo() throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        // assert mapper==null;

        QuerUserVo quer = new QuerUserVo();
        //设置user的id 
        User use = new User();
        use.setId(1);
        quer.setUser(use);
        //查询
        User user = mapper.findUserByQuerUserVoUser(quer);
        System.out.println(user);

    }

    //输入参数为map 是查询用户
    @Test
    public void testForFindUserByHashMap() throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        // assert mapper==null;
        //设置map
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("sex", "男");
        map.put("username", "张");

        List<User> list = mapper.findUserByHashMap(map);
        System.out.println(list);

    }

    //输出参数为简单类型 是查询用户findUserCount
    @Test
    public void testForFindUserCount() throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        //设置queruservo
        QuerUserVo quer = new QuerUserVo();
        User user = new User();
        user.setUsername("张");
        quer.setUser(user);

        int count = mapper.findUserCount(quer);
        System.out.println(count);

    }
    //输出参数为resultmap映射的pojo类  是查询用户findUserResultMap
    @Test
    public void testForFindUserResultMap() throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        //设置queruservo

        List<User> list = mapper.findUserResultMap("男");
        System.out.println(list);

    }
    //综合条件查询findUserList
    @Test
    public void testForFindUserList() throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        //设置queruservo
        QuerUserVo quer = new QuerUserVo();
        User user = new User();
        user.setUsername("张");
        user.setSex("男");
        quer.setUser(user);

        List<User> list = mapper.findUserList(quer);

        System.out.println(list);

    }
    //综合条件查询findUsersByIds  遍历id集合查询
    @Test
    public void testForFindUsersByIds() throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        //设置queruservo
        QuerUserVo quer = new QuerUserVo();
        quer.getIds().add("1");
        quer.getIds().add("16");
        quer.getIds().add("22");

        List<User> list = mapper.findUsersByIds(quer);

        System.out.println(list);

    }
    //多表联合查询  一对一
    @Test
    public void testForFindOrdersCustomer() throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();

        OrderCustomerMapper mapper = sqlSession.getMapper(OrderCustomerMapper.class);
        //设置queruservo
        List<OrdersCustomer> list = mapper.findOrdersCustomer();

        System.out.println(list);

    }

    //多表联合查询 数据通过resultMap封装到ordersCustomer中
    @Test
    public void testForFindOrdersCustomerResultMap() throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();

        OrderCustomerMapper mapper = sqlSession.getMapper(OrderCustomerMapper.class);
        //设置queruservo
        List<Orders> list = mapper.findOrdersCustomerMap();

        System.out.println(list);

    }
    //多表联合查询 数据通过resultMap封装到orders中
    @Test
    public void testForFindOrdersCustomerOrderdetails() throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();

        OrderCustomerMapper mapper = sqlSession.getMapper(OrderCustomerMapper.class);
        //设置queruservo
        List<Orders> list = mapper.findOrdersCustomerOrderDetilsMap();

        System.out.println(list);

    }
    //多表联合查询 数据通过resultMap封装到user中
    @Test
    public void testForFindUserOrdersDetailsItems() throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();

        OrderCustomerMapper mapper = sqlSession.getMapper(OrderCustomerMapper.class);
        //设置queruservo
        List<User> list = mapper.findUserOrdersDetailsItemsMap();

        System.out.println(list);

    }
    //延迟加载
    @Test
    public void testForFindOrdersLazingUser() throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();

        OrderCustomerMapper mapper = sqlSession.getMapper(OrderCustomerMapper.class);
        //设置queruservo
        List<Orders> list = mapper.findOrdersLazingUser();

        User user = list.get(0).getUser();
        System.out.println(user);

        System.out.println(list);

    }
    //测试一级缓存
    @Test
    public void testForFindUserSessionsqlCache() throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);


            User user = mapper.findUserById(1);

            user.setUsername("王十九");

            mapper.updateUser(user);
            sqlSession.commit();

            user = mapper.findUserById(1);

            System.out.println(user);

    }
    //测试二级缓存
    @Test
    public void testForFindUserMapperCache() throws Exception {
        SqlSession sqlSession1 = sqlSessionFactory.openSession();
        SqlSession sqlSession2 = sqlSessionFactory.openSession();
        SqlSession sqlSession3= sqlSessionFactory.openSession();
        SqlSession sqlSession4 = sqlSessionFactory.openSession();

        UserMapper mapper1 = sqlSession1.getMapper(UserMapper.class);
        UserMapper mapper2 = sqlSession2.getMapper(UserMapper.class);
        UserMapper mapper3 = sqlSession3.getMapper(UserMapper.class);
        UserMapper mapper4 = sqlSession4.getMapper(UserMapper.class);

        //使用sqlsession1查询
        User user = mapper1.findUserById(1);
        System.out.println(user);

        //关闭sqlsession1  数据会写入二级缓存
        sqlSession1.close();
        /*user.setUsername("王十九");
        mapper.updateUser(user);
        sqlSession.commit();*/

        //使用sqlsession2查询
        User user2 = mapper2.findUserById(1);

        System.out.println(user2);

    }

    /**
     * 
     * @Title: testForfOR @Description: TODO(跳出双层for循环) @param 参数 @return void
     * 返回类型 @throws
     */
    @Test
    public void testForfOR() {
        for循环的名字: for (int i = 0; i < 10; i++) {

            for (int j = 0; j < 10; j++) {

                System.out.println(i + "---" + j);
                if (i == 4) {
                    System.out.println("=================================");
                    break for循环的名字;
                }
            }
        }
    }

}

个人笔记整理