mybatis学习测试和基础配置_querybyid(8l);-CSDN博客

本文链接：https://blog.csdn.net/xzr882123/article/details/79047225

核心配置文件mybatis-config.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>
    <!--引入外部文件jdbc.properties-->
    <properties resource="jdbc.properties"></properties>
    <!--setting表示行为参数-->
    <settings>
        <!--开启自动驼峰命名规则（camel case）映射，即从经典数据库列名 A_COLUMN 到经典 Java 属性名 aColumn 的类似映射-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <!--关闭二级缓存-->
    <setting name="cacheEnabled" value="false"/>

        <!-- 开启延迟加载 要先导入cglib的jar包,再改造高级查询关系-->
      <!--  <setting name="lazyLoadingEnabled" value="true"/>-->
        <!-- 开启按需加载 -->
      <!--  <setting name="aggressiveLazyLoading" value="false"/>-->
    </settings>
    <!--类型别名：type-pojo类的全路径，alias-别名名称（可随便写，推荐和类名一致-->
    <typeAliases>
        <!--方式一:但是如果类多的话比较麻烦,type:包装类的全路径,alias:别名,建议方式二-->
        <!--<typeAlias type="cn.itcast.pojo.User" alias="User"></typeAlias>-->
        <!--方式二:配置扫描-->
        <package name="cn.itcast.pojo"></package>
    </typeAliases>
    <!-- 环境：后缀s说明可以配置多个，default:指定生效的环境 -->
    <environments default="development">
        <environment id="development">
            <!--事务-->
            <transactionManager type="JDBC"/>
            <!-- 数据源：type-池类型的数据源 -->
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <!--引入映射文件-->
    <mappers>
        <!--resource表示classpath路径下-->
        <mapper resource="UserMapper.xml"/>
        <mapper resource="UserDaoMapper.xml"/>
        <mapper resource="commonSQL.xml"></mapper>
        <mapper resource="OrderMapper.xml"></mapper>

    </mappers>
</configuration>

接口UserMapper

package cn.itcast.mapper;

import cn.itcast.pojo.User;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface UserMapper {
    /*
     * 动态SQL,使用foreach标签
     * 根据多个id查询用户信息
     * */
    public List<User> queryUserInIds(@Param("ids") Long[] ids);
    /*
     * 动态SQL,使用set标签
     * 修改用户信息，如果参数user中的某个属性为null，则不修改。
     * */
    public  void updateUserUseSet(User user);

    /**
     * 动态SQL,使用choose标签
     */
    public List<User> queryByusernameOrAge(@Param("userName") String userName, @Param("age") Integer age);
            /*
             * 动态SQL,使用choose标签
             * */
    public List<User> queryByUsernmmeListAndAge(@Param("userName") String userName, @Param("age") Integer age);

    /*
     * 动态SQL,使用if标签
     * */
    public List<User> queryuserByUsernameLike(@Param("userName") String userName);

    /**
     * 多个参数的传递,#{}和${}使用
     */
    public User login(@Param("userName") String userName, @Param("password") String password);

    /**
     * 预编译表名查询
     */
    public List<User> queryByTableName(@Param("tableName") String tableName);

    /***
     * 根据id查询
     */
    public User queryById(Long id);

    /***
     * 查询所有
     * @return
     */
    public List<User> queryAllUser();

    /***
     * 新增
     * @param user
     */
    public void insert(User user);

    /***
     * 根据id删除
     * @param id
     */
    public void deleteById(Long id);

    /***
     * 修改
     * @param user
     */
    public void update(User user);
}

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">
<!--映射文件的跟标签,namespace 名称空间,全局的唯一标识用动态代理的换必须是接口的全路径类名-->
<mapper namespace="cn.itcast.mapper.UserMapper">
    <!--id用动态代理的话必须与方法名一致  resultType最好使用pojo对象的全路径名(可以通过配置扫描简写)-->
    <!--    这个方式获取不到user_name的值,三种解决方法:1.起别名,2.在核心配置中启用驼峰命名3.自定义resultMap

       方法1: select *,user_name as userName from  tb_user where id = #{id}
       方式2前提开启驼峰命名: select * from tb_user where id = #{id}
       -->

    <!--方法3:
         自定义映射关系
            type:结果集封装类型
            id:唯一标识
            autoMapping：自动映射，如果开启了驼峰匹配，就以驼峰匹配的形式映射,建议一直写着
            子标签：id：主键的映射关系
                    column：主键的列名
                    property：主键的属性名
                result：普通属性的映射关系（非主键，非包装类型）
                    column:变种列
                    property:属性名

        <resultMap id="userMap" type="User" autoMapping="true">
            <id column="id" property="id"></id>
            <result column="user_name" property="userName"></result>
        </resultMap>
        <select id="queryById" resultMap="userMap">
            select * from tb_user where id = #{id}
        </select>
    -->
    <!-- 查询的statement
    id：在同一名称空间下的唯一标识。使用动态代理之后要求和mapper接口方法一致，必须属性
    resultType：结果集封装类型，要求和mapper接口的方法返回类型一致，和resultMap
    parameterType：参数类型，可省略
 -->
    <select id="queryById" resultType="User">
        select * from tb_user where id = #{id}
    </select>

    <!--这边用sql片段代替*-->
    <select id="queryAllUser" resultType="cn.itcast.pojo.User">
        SELECT <include refid="CommonSQL.commonSql"></include> FROM tb_user
    </select>

    <!-- 新增的statement
		id：在同一名称空间下的唯一标识。使用动态代理之后要求和mapper接口方法一致，必须属性
		parameterType：参数类型，可省略
		useGeneratedKeys:开启主键回写，回写到方法参数中
		keyColumn:主键的列名
		keyProperty：主键的属性名
	 -->
    <insert id="insert" parameterType="cn.itcast.pojo.User" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
        INSERT INTO tb_user(
        user_name,
        password,
        name,
        age,
        sex,
        birthday,
        created,
        updated
        )
        VALUES
        (
        #{userName},
        #{password},
        #{name},
        #{age},
        #{sex},
        #{birthday},
        NOW(),
        NOW()
        );
    </insert>

    <!-- 更新的statement
    id：在同一名称空间下的唯一标识。使用动态代理之后要求和mapper接口方法一致，必须属性
    parameterType：参数类型，可省略
 -->
    <update id="update" parameterType="cn.itcast.pojo.User">
        UPDATE tb_user
        SET
        user_name = #{userName},
        password = #{password},
        name = #{name},
        age = #{age},
        sex = #{sex},
        birthday = #{birthday},
        updated = NOW()
        WHERE
        (id = #{id});
    </update>

    <!-- 删除的statement
    id：在同一名称空间下的唯一标识。使用动态代理之后要求和mapper接口方法一致，必须属性
    parameterType：参数类型，可省略
 -->
    <delete id="deleteById" parameterType="cn.itcast.pojo.User">
        DELETE  FROM tb_user where id = #{id}
    </delete>

    <!--预编译表名查询测试${}和#{}-->
    <select id="queryByTableName" resultType="User">
        SELECT * FROM ${tableName}
    </select>

    <select id="login" resultType="User">
      SELECT * FROM tb_user WHERE user_name=#{userName} AND password=#{password}
    </select>

    <!--动态SQL,使用if标签-->
    <select id="queryuserByUsernameLike" resultType="User">
        SELECT * from tb_user where sex=1
        <if test="userName!=null and userName.trim()!=''">
            AND user_name LIKE "%"#{userName}"%"
        </if>
    </select>

    <!--动态SQL,使用choose标签-->
    <select id="queryByUsernmmeListAndAge" resultType="User">

        SELECT * FROM tb_user where 1=1
        <choose>
            <when test="userName!=null and userName!=''">
                and user_name LIKE "%"#{userName}"%"
            </when>
            <when test="age!=null and age!=''">
                AND age < #{age}
            </when>
            <otherwise>
                AND user_name='zhangsan'
            </otherwise>
        </choose>
    </select>
    <!--动态SQL,使用where标签-->
    <select id="queryByusernameOrAge" resultType="User">
        SELECT * FROM tb_user
        <where>
            <if test="userName!=null and userName!=''">
                and user_name LIKE "%"#{userName}"%"
            </if>
            <if test="age!=null and age!=''">
                AND age < #{age}
            </if>
        </where>
    </select>

    <!--set标签
    		set自动添加set关键字
			也有一定的纠错功能：自动去掉sql语句块之后多余的一个逗号
    -->
    <update id="updateUserUseSet" parameterType="User">
         UPDATE tb_user
        <set>

            <if test="name!=null and name.trim()!=''">name = #{name},</if>
            <if test="age!=null">age = #{age},</if>

        </set>
        WHERE
        (id = #{id});
    </update>

<!--foreach标签]
			foreach:遍历集合
			collection：接收的集合参数
			item：遍历的集合中的一个元素
			separator:分隔符
			open:以什么开始
			close：以什么结束
-->
    <select id="queryUserInIds" resultType="User">
      SELECT * FROM tb_user WHERE id in
      <foreach collection="ids" item="id" separator="," open="(" close=")">
        #{id}
      </foreach>
    </select>
</mapper>

log4j.properties日志文件

log4j.rootLogger=DEBUG,A1
log4j.logger.org.mybatis=DEBUG
log4j.appender.A1=org.apache.log4j.ConsoleAppender
log4j.appender.A1.layout=org.apache.log4j.PatternLayout
log4j.appender.A1.layout.ConversionPattern=%-d{yyyy-MM-dd HH:mm:ss,SSS} [%t] [%c]-[%p] %m%n

commonSQL.xml(用于优化sql语句的可以不用)

<?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 名称空间-->
<!--定义SQL片段,在企业中SQL语句不允许出现*,会影响性能-->
<!--别忘了在核心核心配置文件中引入-->
<mapper namespace="CommonSQL">
<sql id="commonSql">
        id,
        user_name,
        password,
        name,
        age,
        sex,
        birthday,
        created,
        updated

</sql>
</mapper>

junit测试代码:

package cn.itcast.test;

import cn.itcast.mapper.UserMapper;
import cn.itcast.pojo.User;
import org.apache.ibatis.annotations.Param;
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 java.io.InputStream;
import java.util.List;

public class UserMapperTest {

    private UserMapper userMapper;
    @Before
    public void setUp() throws Exception {
        //指定mybatis的全局配置文件
        String resource = "mybatis.config.xml";
        //服务配置文件
        InputStream inputStream = Resources.getResourceAsStream(resource);
        //基于配置文件构建sqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //打开sqlSession会话
       // SqlSession sqlSession = sqlSessionFactory.openSession();
        //openSession(true)中true表示自动提交
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        //参数是UserMapper.class接口
        this.userMapper =sqlSession.getMapper(UserMapper.class);

    }

    @Test
    public void queryById() {
        System.out.println(this.userMapper.queryById(1L));
    }

    @Test
    public void queryAllUser() {
        System.out.println(this.userMapper.queryAllUser());
    }

    @Test
    public void insert() {
        User user = new User();
        user.setName("林志4444");
        user.setAge(18);
        this.userMapper.insert(user);
        System.out.println(user.getId());
    }

    @Test
    public void deleteById() {
        this.userMapper.deleteById(5L);
    }

    @Test
    public void update() {
        User user = this.userMapper.queryById(8L);
        user.setName("林志玲");
        user.setAge(20);
        this.userMapper.update(user);
    }


    @Test
    public void queryByTableName(){
        List<User> userList = this.userMapper.queryByTableName("tb_user");
        for (User user : userList) {
            System.out.println(user);
        }
    }
    @Test
    public void login(){
        User user = this.userMapper.login("zhangsan", "123456");
        System.out.println(user);
    }

    @Test
    public void ueryuserByUsernameLike(){
        List<User> userList = this.userMapper.queryuserByUsernameLike("zhang");
        for (User user : userList) {
            System.out.println(user);

        }
    }

    @Test
    public void queryByUsernmmeListAndAge(){
        List<User> userList = this.userMapper.queryByUsernmmeListAndAge("", 30);
        for (User user : userList) {
            System.out.println(user);
        }
    }
    @Test
    public void queryByusernameOrAge(){
        List<User> list = this.userMapper.queryByusernameOrAge("zhang", 30);
        for (User user : list) {
            System.out.println(user);
        }
    }

    @Test
    public  void updateUserUseSet(){
        User user = this.userMapper.queryById(8L);
        user.setName("林999");
        user.setAge(27);
        this.userMapper.updateUserUseSet(user);
    }

    @Test
    public  void queryUserInIds(){
        Long[] ids = {1l,2l,4l,3l};
        List<User> userList = this.userMapper.queryUserInIds(ids);
        for (User user : userList) {
            System.out.println(user);
        }

    }
}

实体类User

package cn.itcast.pojo;

import java.io.Serializable;
import java.util.Date;

public class User implements Serializable{

    private Long id;

    // 用户名
    private String userName;

    // 密码
    private String password;

    // 姓名
    private String name;

    // 年龄
    private Integer age;

    // 性别，1男性，2女性
    private Integer sex;

    // 出生日期
    private Date birthday;

    // 创建时间
    private Date created;

    // 更新时间
    private Date updated;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Integer getSex() {
        return sex;
    }

    public void setSex(Integer sex) {
        this.sex = sex;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public Date getCreated() {
        return created;
    }

    public void setCreated(Date created) {
        this.created = created;
    }

    public Date getUpdated() {
        return updated;
    }

    public void setUpdated(Date updated) {
        this.updated = updated;
    }

    @Override
    public String toString() {
        return "User [id=" + id + ", userName=" + userName + ", password=" + password + ", name=" + name
                + ", age=" + age + ", sex=" + sex + ", birthday=" + birthday + ", created=" + created
                + ", updated=" + updated + "]";
    }

}

mybatis的高级查询一对一,一对多,多对多:

表的关系:

相关文件:接口OrderMapper.java,OrderMapper.xml,测试类OrderMapperTest,以及相关实体类

接口OrderMapper.java

package cn.itcast.mapper;


import cn.itcast.pojo.Order;
import cn.itcast.pojo.User;
import org.apache.ibatis.annotations.Param;

public interface OrderMapper {
    /**
     * 根据订单号查询订单信息，并且查询出下单人信息
     * @param number
     * @return
     */
    public Order queryOrderWithUserByOrdernumber(@Param("number")String number);

    /**
     * 查询订单，查询出下单人信息并且查询出订单详情中的商品数据。
     * @param number
     * @return
     */
    public Order queryOrderWithUserDetailItem(@Param("number")String number);

    /**
     * 查询订单，查询出下单人信息并且查询出订单详情中的商品数据。
     * @param number
     * @return
     */
    public Order queryOrderWithUserAndDetailAndItem(@Param("number")String number);


}

OrderMapper.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="cn.itcast.mapper.OrderMapper">
    <resultMap id="orderMap" type="Order" autoMapping="true">
        <id column="id" property="id"></id>
        <!--        association:一对一的映射
                    property：类中java的属性名
                    javaType：属性名对应的java类型
                    autoMapping:开启自动映射
                    子标签：参照resultMap-->
        <association property="user" javaType="User" autoMapping="true">
            <id column="user_id" property="id"></id>
        </association>
    </resultMap>
 <!--  resultType不能完成user信息的映射，必须使用resultMap，resultMap的值对应resultMap标签的id，resultMap和resultType必须二选一-->
    <select id="queryOrderWithUserByOrdernumber" resultMap="orderMap">
        SELECT * FROM tb_order a LEFT JOIN tb_user b ON a.user_id = b.id
        WHERE a.order_number = #{number}
    </select>


    <!--一对多映射
    property:属性名
    javaType:集合类型
    ofType:集合的泛型
    autoMapping:自动映射
    -->
    <resultMap id="orderDetailMap" type="Order" autoMapping="true" extends="orderMap"><!--通过extends简化配置-->
<!--        <id column="id" property="id"></id>
        <association property="user" javaType="User" autoMapping="true">
            <id column="user_id" property="id"></id>
        </association>-->
        <collection property="orderdetailList" javaType="List" ofType="Orderdetail" autoMapping="true">
            <id property="id" column="detail_id"></id>
        </collection>
    </resultMap>
    <select id="queryOrderWithUserDetailItem" resultMap="orderDetailMap">
        SELECT *,c.id AS detail_id FROM tb_order a
            LEFT JOIN tb_user b ON a.user_id = b.id
            LEFT JOIN tb_orderdetail c ON a.id = c.order_id
            WHERE a.order_number = #{number}
    </select>

    <!--多对多嵌套-->
    <resultMap id="orderDetailItemMap" type="Order" autoMapping="true" extends="orderMap">
      <!--  <id column="id" property="id"></id>
        <association property="user" javaType="User" autoMapping="true">
            <id column="user_id" property="id"></id>
        </association>-->
        <collection property="orderdetailList" javaType="List" ofType="Orderdetail" autoMapping="true">
            <id property="id" column="detail_id"></id>
            <association property="item" javaType="Item" autoMapping="true">
                <id property="id" column="item_id"></id>
            </association>
        </collection>
    </resultMap>

    <select id="queryOrderWithUserAndDetailAndItem" resultMap="orderDetailItemMap">
		select *,c.id as detail_id from tb_order a
			LEFT JOIN tb_user b on a.user_id=b.id
			LEFT JOIN tb_orderdetail c on a.id=c.order_id
			LEFT JOIN tb_item d on c.item_id=d.id
		where a.order_number=#{number}
    </select>
</mapper>

junit测试:

package cn.itcast.test;

import cn.itcast.mapper.OrderMapper;
import cn.itcast.pojo.Order;
import cn.itcast.pojo.User;
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 java.io.InputStream;

public class OrderMapperTest {
private OrderMapper orderMapper;
    @Before
    public void setUp() throws Exception {
        //指定全局配置文件
        String resource = "mybatis.config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        orderMapper = sqlSession.getMapper(OrderMapper.class);

    }

    @Test
    public void queryOrderWithUserByOrdernumber() {
        Order order = orderMapper.queryOrderWithUserByOrdernumber("20140921001");
        System.out.println(order);
    }
    @Test
    public  void queryOrderWithUserDetailItem(){
        Order order = orderMapper.queryOrderWithUserDetailItem("20140921001");
        System.out.println(order);
    }
    @Test
    public  void  queryOrderWithUserAndDetailAndItem(){
        Order order = orderMapper.queryOrderWithUserAndDetailAndItem("20140921001");
        System.out.println(order);
    }
}

实体类Order:

package cn.itcast.pojo;


import java.util.List;

/**
 * 订单表
 *
 */
public class Order {

    private Integer id;

    private Long userId;

    private String orderNumber;

    //添加与User的一对一关系
    private User user;

    //一对多
    private List<Orderdetail> orderdetailList;

    public List<Orderdetail> getOrderdetailList() {
        return orderdetailList;
    }

    public void setOrderdetailList(List<Orderdetail> orderdetailList) {
        this.orderdetailList = orderdetailList;
    }

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Long getUserId() {
        return userId;
    }

    public void setUserId(Long userId) {
        this.userId = userId;
    }

    public String getOrderNumber() {
        return orderNumber;
    }

    public void setOrderNumber(String orderNumber) {
        this.orderNumber = orderNumber;
    }


    @Override
    public String toString() {
        return "Order{" +
                "id=" + id +
                ", userId=" + userId +
                ", orderNumber='" + orderNumber + '\'' +
                ", user=" + user +
                ", orderdetailList=" + orderdetailList +
                '}';
    }
}

实体类OrderDetail

package cn.itcast.pojo;

public class Orderdetail {

    private Integer id;

    private Double totalPrice;

    private Integer status;

    private Item item;

    public Item getItem() {
        return item;
    }

    public void setItem(Item item) {
        this.item = item;
    }

    public Double getTotalPrice() {
        return totalPrice;
    }

    public void setTotalPrice(Double totalPrice) {
        this.totalPrice = totalPrice;
    }

    public Integer getStatus() {
        return status;
    }

    public void setStatus(Integer status) {
        this.status = status;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    @Override
    public String toString() {
        return "Orderdetail{" +
                "id=" + id +
                ", totalPrice=" + totalPrice +
                ", status=" + status +
                ", item=" + item +
                '}';
    }
}

实体类Item

package cn.itcast.pojo;

/**
 * 商品表
 */
public class Item {

    private Integer id;

    private String itemName;

    private Float itemPrice;

    private String itemDetail;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getItemName() {
        return itemName;
    }

    public void setItemName(String itemName) {
        this.itemName = itemName;
    }

    public Float getItemPrice() {
        return itemPrice;
    }

    public void setItemPrice(Float itemPrice) {
        this.itemPrice = itemPrice;
    }

    public String getItemDetail() {
        return itemDetail;
    }

    public void setItemDetail(String itemDetail) {
        this.itemDetail = itemDetail;
    }

    @Override
    public String toString() {
        return "Item{" +
                "id=" + id +
                ", itemName='" + itemName + '\'' +
                ", itemPrice=" + itemPrice +
                ", itemDetail='" + itemDetail + '\'' +
                '}';
    }
}

数据库建表语句

/*
Navicat MySQL Data Transfer

Source Server         : 127.0.0.1
Source Server Version : 50619
Source Host           : 127.0.0.1:3306
Source Database       : mybatis_test

Target Server Type    : MYSQL
Target Server Version : 50619
File Encoding         : 65001

Date: 2014-11-27 11:00:43
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for tb_item
-- ----------------------------
DROP TABLE IF EXISTS `tb_item`;
CREATE TABLE `tb_item` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_name` varchar(32) NOT NULL COMMENT '商品名称',
  `item_price` float(6,1) NOT NULL COMMENT '商品价格',
  `item_detail` text COMMENT '商品描述',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tb_item
-- ----------------------------
INSERT INTO `tb_item` VALUES ('1', 'iPhone 6', '5288.0', '苹果公司新发布的手机产品。');
INSERT INTO `tb_item` VALUES ('2', 'iPhone 6 plus', '6288.0', '苹果公司发布的新大屏手机。');

-- ----------------------------
-- Table structure for tb_order
-- ----------------------------
DROP TABLE IF EXISTS `tb_order`;
CREATE TABLE `tb_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `order_number` varchar(20) NOT NULL COMMENT '订单号',
  PRIMARY KEY (`id`),
  KEY `FK_orders_1` (`user_id`),
  CONSTRAINT `FK_orders_1` FOREIGN KEY (`user_id`) REFERENCES `tb_user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tb_order
-- ----------------------------
INSERT INTO `tb_order` VALUES ('1', '1', '20140921001');
INSERT INTO `tb_order` VALUES ('2', '2', '20140921002');
INSERT INTO `tb_order` VALUES ('3', '1', '20140921003');

-- ----------------------------
-- Table structure for tb_orderdetail
-- ----------------------------
DROP TABLE IF EXISTS `tb_orderdetail`;
CREATE TABLE `tb_orderdetail` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(32) DEFAULT NULL COMMENT '订单号',
  `item_id` int(32) DEFAULT NULL COMMENT '商品id',
  `total_price` double(20,0) DEFAULT NULL COMMENT '商品总价',
  `status` int(11) DEFAULT NULL COMMENT '状态',
  PRIMARY KEY (`id`),
  KEY `FK_orderdetail_1` (`order_id`),
  KEY `FK_orderdetail_2` (`item_id`),
  CONSTRAINT `FK_orderdetail_1` FOREIGN KEY (`order_id`) REFERENCES `tb_order` (`id`),
  CONSTRAINT `FK_orderdetail_2` FOREIGN KEY (`item_id`) REFERENCES `tb_item` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tb_orderdetail
-- ----------------------------
INSERT INTO `tb_orderdetail` VALUES ('1', '1', '1', '5288', '1');
INSERT INTO `tb_orderdetail` VALUES ('2', '1', '2', '6288', '1');
INSERT INTO `tb_orderdetail` VALUES ('3', '2', '2', '6288', '1');
INSERT INTO `tb_orderdetail` VALUES ('4', '3', '1', '5288', '1');

-- ----------------------------
-- Table structure for tb_user
-- ----------------------------
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(100) DEFAULT NULL COMMENT '用户名',
  `password` varchar(100) DEFAULT NULL COMMENT '密码',
  `name` varchar(100) DEFAULT NULL COMMENT '姓名',
  `age` int(10) DEFAULT NULL COMMENT '年龄',
  `sex` tinyint(1) DEFAULT NULL COMMENT '性别，1男性，2女性',
  `birthday` date DEFAULT NULL COMMENT '出生日期',
  `created` datetime DEFAULT NULL COMMENT '创建时间',
  `updated` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`user_name`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tb_user
-- ----------------------------
INSERT INTO `tb_user` VALUES ('1', 'zhangsan', '123456', '张三', '30', '1', '1984-08-08', '2014-09-19 16:56:04', '2014-09-21 11:24:59');
INSERT INTO `tb_user` VALUES ('2', 'lisi', '123456', '李四', '21', '2', '1991-01-01', '2014-09-19 16:56:04', '2014-09-19 16:56:04');
INSERT INTO `tb_user` VALUES ('3', 'wangwu', '123456', '王五', '22', '2', '1989-01-01', '2014-09-19 16:56:04', '2014-09-19 16:56:04');
INSERT INTO `tb_user` VALUES ('4', 'zhangwei', '123456', '张伟', '20', '1', '1988-09-01', '2014-09-19 16:56:04', '2014-09-19 16:56:04');
INSERT INTO `tb_user` VALUES ('5', 'lina', '123456', '李娜', '28', '1', '1985-01-01', '2014-09-19 16:56:04', '2014-09-19 16:56:04');
INSERT INTO `tb_user` VALUES ('6', 'lilei', '123456', '李磊', '23', '1', '1988-08-08', '2014-09-20 11:41:15', '2014-09-20 11:41:15');

补充:延迟加载

	/**
	 * 测试延迟加载
	 * @param number
	 * @return
	 */
	public Order queryOrderLazy(@Param("number")String number);

先改造高级查询:

处理组合键时，需要传递多个参数，可以使用column=”{prop1=col1, prop2=col2, prop3=col3…}”，设置多个列名传入到嵌套查询语句，mybatis会把prop1,prop2,prop3设置到目标嵌套的查询语句中的参数对象中。
子查询中，必须通过prop1,prop2,prop3获取对应的参数值，你也可以使用这种方式指定参数名例如：