Mybatis基础入门

SqlMapConfig.xml

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE configurationPUBLIC "-//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 mapperPUBLIC "-//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 mapperPUBLIC "-//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循环的名字;                }            }        }    }}

个人笔记整理