mybatis的动态sql编写以及一对一关系查询和一对多的查询

创建mybatis数据库,运行以下sql语句

/*
SQLyog Ultimate v8.32
MySQL - 5.5.27 : Database - mybatis
*********************************************************************
*/

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=‘‘*/;

/*!40014 SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @[email protected]@SQL_MODE, SQL_MODE=‘NO_AUTO_VALUE_ON_ZERO‘ */;
/*!40111 SET @[email protected]@SQL_NOTES, SQL_NOTES=0 */;
/*Table structure for table `items` */

DROP TABLE IF EXISTS `items`;

CREATE TABLE `items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL COMMENT ‘商品名称‘,
  `price` float(10,1) NOT NULL COMMENT ‘商品定价‘,
  `detail` text COMMENT ‘商品描述‘,
  `pic` varchar(64) DEFAULT NULL COMMENT ‘商品图片‘,
  `createtime` datetime NOT NULL COMMENT ‘生产日期‘,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `items` */

insert  into `items`(`id`,`name`,`price`,`detail`,`pic`,`createtime`) values (1,‘台式机‘,3333.0,‘该电脑质量非常好!!!!‘,‘‘,‘2015-01-01 00:00:00‘),(2,‘笔记本‘,6000.0,‘笔记本性能好,质量好!!!!!‘,NULL,‘2015-02-09 13:22:57‘),(3,‘背包‘,200.0,‘名牌背包,容量大质量好!!!!‘,NULL,‘2015-02-06 13:23:02‘);

/*Table structure for table `orderdetail` */

DROP TABLE IF EXISTS `orderdetail`;

CREATE TABLE `orderdetail` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `orders_id` int(11) NOT NULL COMMENT ‘订单id‘,
  `items_id` int(11) NOT NULL COMMENT ‘商品id‘,
  `items_num` int(11) DEFAULT NULL COMMENT ‘商品购买数量‘,
  PRIMARY KEY (`id`),
  KEY `FK_orderdetail_1` (`orders_id`),
  KEY `FK_orderdetail_2` (`items_id`),
  CONSTRAINT `FK_orderdetail_1` FOREIGN KEY (`orders_id`) REFERENCES `orders` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_orderdetail_2` FOREIGN KEY (`items_id`) REFERENCES `items` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

/*Data for the table `orderdetail` */

insert  into `orderdetail`(`id`,`orders_id`,`items_id`,`items_num`) values (1,3,1,1),(2,3,2,3),(3,4,3,4),(4,4,2,3);

/*Table structure for table `orders` */

DROP TABLE IF EXISTS `orders`;

CREATE TABLE `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL COMMENT ‘下单用户id‘,
  `number` varchar(32) NOT NULL COMMENT ‘订单号‘,
  `createtime` datetime NOT NULL COMMENT ‘创建订单时间‘,
  `note` varchar(100) DEFAULT NULL COMMENT ‘备注‘,
  PRIMARY KEY (`id`),
  KEY `FK_orders_1` (`user_id`),
  CONSTRAINT `FK_orders_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

/*Data for the table `orders` */

insert  into `orders`(`id`,`user_id`,`number`,`createtime`,`note`) values (3,1,‘1000010‘,‘2015-02-04 13:22:35‘,NULL),(4,1,‘1000011‘,‘2015-02-03 13:22:41‘,NULL),(5,10,‘1000012‘,‘2015-02-12 16:13:23‘,NULL);

/*Table structure for table `user` */

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) NOT NULL COMMENT ‘用户名称‘,
  `birthday` date DEFAULT NULL COMMENT ‘生日‘,
  `sex` char(1) DEFAULT NULL COMMENT ‘性别‘,
  `address` varchar(256) DEFAULT NULL COMMENT ‘地址‘,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;

/*Data for the table `user` */

insert  into `user`(`id`,`username`,`birthday`,`sex`,`address`) values (1,‘王五‘,NULL,‘2‘,NULL),(10,‘张三‘,‘2014-07-10‘,‘1‘,‘北京市‘),(16,‘张小明‘,NULL,‘1‘,‘河南郑州‘),(22,‘陈小明‘,NULL,‘1‘,‘河南郑州‘),(24,‘张三丰‘,NULL,‘1‘,‘河南郑州‘),(25,‘陈小明‘,NULL,‘1‘,‘河南郑州‘),(26,‘王五‘,NULL,NULL,NULL);

/*!40101 SET [email protected]_SQL_MODE */;
/*!40014 SET [email protected]_FOREIGN_KEY_CHECKS */;
/*!40014 SET [email protected]_UNIQUE_CHECKS */;
/*!40111 SET [email protected]_SQL_NOTES */;

创建工程包结构,以及导入jar包

log4j的配置文件

### direct log messages to stdout ###
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.err
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n

### direct messages to file mylog.log ###
log4j.appender.file=org.apache.log4j.FileAppender
log4j.appender.file.File=d:/mylog.log
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n

### set log levels - for more verbose logging change ‘info‘ to ‘debug‘ ###

log4j.rootLogger=debug, stdout

db.properties数据库连接配置文件

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis
jdbc.username=root
jdbc.password=123

SqlMapConfig.xml全局的配置文件,mybatis必须的配置文件

<?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>
        <properties resource="db.properties">
        </properties>
        <typeAliases>
        <!-- 批量别名定义,扫描整个包下的类,别名为类名(大小写不敏感) -->
        <package name="com.baoyuan.mybatis"/>
        </typeAliases>
                            <!--名称随意  -->
    <environments default="development">
            <!--id必须和上面的一致  -->
        <environment id="development">
                                <!--固定格式JDBC  -->
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driverClassName}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
    <!-- 注意:此种方法要求mapper接口名称和mapper映射文件名称相同,且放在同一个目录中。 -->
    <package name="com.baoyuan.mybatis.mapper"/>
    </mappers>
</configuration>

user这个pojo类

package com.baoyuan.mybatis.pojo;

import java.util.Date;
import java.util.List;

public class User {

    private Integer id;
    private String username;// 用户姓名
    private String sex;// 性别
    private Date birthday;// 生日
    private String address;// 地址
    private List<Order> orders;

    public List<Order> getOrders() {
        return orders;
    }
    public void setOrders(List<Order> orders) {
        this.orders = orders;
    }
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public String getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
    public Date getBirthday() {
        return birthday;
    }
    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
    @Override
    public String toString() {
        return "User [id=" + id + ", username=" + username + ", sex=" + sex + ", birthday=" + birthday + ", address="
                + address + ", orders=" + orders + "]";
    }

}

user和order一对多关联查询使用resultType方式定义的pojo类

package com.baoyuan.mybatis.pojo;

import java.util.Date;

public class UserOrder extends User{
    private String number;

    private Date createtime;

    private String note;

    public String getNumber() {
        return number;
    }

    public void setNumber(String number) {
        this.number = number;
    }

    public Date getCreatetime() {
        return createtime;
    }

    public void setCreatetime(Date createtime) {
        this.createtime = createtime;
    }

    public String getNote() {
        return note;
    }

    public void setNote(String note) {
        this.note = note;
    }

    @Override
    public String toString() {
        return "UserOrder ["+super.toString()+"number=" + number + ", createtime=" + createtime + ", note=" + note + "]";
    }

}

user进行xml(<if><foreach><where>等)标签使用测试用到的pojo类

package com.baoyuan.mybatis.pojo;

import java.util.List;

public class UserWarp {
    private User user;

    private List<Integer> ids;

    public List<Integer> getIds() {
        return ids;
    }

    public void setIds(List<Integer> ids) {
        this.ids = ids;
    }

    public User getUser() {
        return user;
    }

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

    @Override
    public String toString() {
        return "UserWarp [user=" + user + ", ids=" + ids + "]";
    }

}

user 的mapper接口

package com.baoyuan.mybatis.mapper;

import java.util.List;

import com.baoyuan.mybatis.pojo.User;
import com.baoyuan.mybatis.pojo.UserOrder;
import com.baoyuan.mybatis.pojo.UserWarp;

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

    public List<User> findUserByusername(String username); 

    public  List<User> findUserByWhere(User user);
    //select * from user wehre id in (1,3,5)
    public List<User> findUserByIds(UserWarp userWarp);

    //查询所有
    public List<User> findUserByAll();

    //查询用户及其下的订单详情
    public List<UserOrder> findUserOrdersById(Integer id);

    public List<User> findUserOrdersByIdToMap(Integer id);
}

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 namespace="com.baoyuan.mybatis.mapper.UserMapper">
    <select id="findUserById" parameterType="int" resultType="user">
        select
        * from user where id =#{id}
    </select>
    <select id="findUserByusername" parameterType="java.lang.String"
        resultType="user">
        select * from user where username like ‘%${value}%‘
    </select>

    <select id="findUserByWhere" parameterType="user" resultType="user">
        select * from user
        <!-- where 自动去掉第一个and -->
        <where>
            <!--判断然后添加条件 -->
            <if test="id != null and id != ‘‘">
                and id =#{id}
            </if>
            <if test="username != null and username != ‘‘">
                and username like ‘%${username}%‘
            </if>
        </where>
    </select>

    <!-- 使用包装类型查询用户 使用ognl从对象中取属性值,如果是包装对象可以使用【属性.XXX.XXX】来取内容的属性 -->
    <select id="findUserByWarp" parameterType="userWarp" resultType="user">
        select * from user where username like ${user.username}
    </select>

    <!--查询Id为(1,3,5)的用户 select * from user where id in (1,2,3) -->
    <select id="findUserByIds" parameterType="userWarp" resultType="user">
        select * from user
        <where>
            <!--如果Ids不为null,并且部位‘‘ -->
            <if test="ids!=null">
                <!-- open:表示用什么开始 close:表示用什么结束 separator:表示用什么分割 -->
                <foreach collection="ids" open="and id in (" close=")"
                    separator="," item="id">
                    #{id}
                </foreach>
            </if>
        </where>
    </select>

    <select id="findUserByAll" resultType="user">
        <!--include 表示运哪个sql片段 注意:如果引用其它mapper.xml的sql片段,则在引用时需要加上namespace,如下:<include
            refid="namespace.sql片段”/> -->
        <include refid="query_all" />
        user
    </select>
    <!--sql片段的抽取 -->
    <sql id="query_all">
        select * from
    </sql>

    <!--set自动去掉多余的,为null和‘‘就不进行更新 -->
    <update id="updateUserByWhere" parameterType="user">
        update ‘user‘
        <set>
            <if test="username!=null and username !=‘‘">
                username=#{username}
            </if>
            <if test="sex!=null and sex !=‘‘">
                sex=#{sex}
            </if>
            <if test="address!=null and address !=‘‘">
                address=#{address}
            </if>
            <if test="birthday!=null and birthday!=‘‘">
                birthday=#{birthday}
            </if>
        </set>
        where id =#{id}
    </update>

    <!--一对多关联查询  :查询用户及其下的订单详情  -->
    <select id="findUserOrdersById" resultType="UserOrder" parameterType="int">
        SELECT
            u.*, o.id ,
            o.createtime,
            o.note,
            o.number
        FROM
            `user` u
        LEFT JOIN
            `order` o
        ON
            u.id = o.user_id
        WHERE
            u.id=#{id}
    </select>

    <resultMap type="user" id="find_user_order_byid">
        <!-- 用户信息映射 -->
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="birthday" column="birthday"/>
        <result property="sex" column="sex"/>
        <result property="address" column="address"/>

        <!-- 一对多关联映射
            property;表示属性名称
            ofType:表示映射的类的全路径,这里写的是别名
        -->
        <collection property="orders" ofType="order">
            <!--用户id已经在user对象中存在,此处可以不设置-->
            <!-- <result property="userId" column="id"/> -->
            <result property="number" column="number"/>
            <result property="createtime" column="createtime"/>
            <result property="note" column="note"/>
        </collection>
    </resultMap>
    <!--一对多关联查询  :查询用户及其下的订单详情 ,使用resultMap -->
    <select id="findUserOrdersByIdToMap" resultMap="find_user_order_byid" parameterType="int">
        SELECT
            u.*, o.id ,
            o.createtime,
            o.note,
            o.number
        FROM
            `user` u
        LEFT JOIN
            `order` o
        ON
            u.id = o.user_id
        WHERE
            u.id=#{id}
    </select>

</mapper>

user的测试代码

package com.baoyuan.mybatis.test;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

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 com.baoyuan.mybatis.mapper.OrderMapper;
import com.baoyuan.mybatis.mapper.UserMapper;
import com.baoyuan.mybatis.pojo.User;
import com.baoyuan.mybatis.pojo.UserOrder;
import com.baoyuan.mybatis.pojo.UserWarp;

public class TestUserMapper {
    private SqlSessionFactory sqlSessionFactory;
    //在运行test之前给sqlsessionfactory 赋值
    @Before
    public void init() throws IOException{
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        InputStream inputStream=Resources.getResourceAsStream("SqlMapConfig.xml");
        this.sqlSessionFactory= builder.build(inputStream);

    }

    //根据Id查询用户
    @Test
    public  void findUserById(){
        SqlSession sqlSession=null;
        try {
            sqlSession = sqlSessionFactory.openSession();
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            User user = mapper.findUserById(1);
            System.out.println(user);
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            if(sqlSession!=null){
                sqlSession.close();
            }
        }
    }
    //根据用户名模糊查询
    @Test
    public  void findUserByusername(){
        SqlSession sqlSession=null;
        try {
            sqlSession = sqlSessionFactory.openSession();
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            List<User> list = mapper.findUserByusername("王");
            System.out.println(list);
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            if(sqlSession!=null){
                sqlSession.close();
            }
        }
    }
    //根据不同条件查询
    @Test
    public  void findUserByWhere(){
        SqlSession sqlSession=null;
        try {
            sqlSession = sqlSessionFactory.openSession();
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            User user=new User();
            user.setId(1);
            user.setUsername("狗");
            List<User> list = mapper.findUserByWhere(user);
            System.out.println(list);
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            if(sqlSession!=null){
                sqlSession.close();
            }
        }
    }

    //根据一堆id查询是这些id的用户
    @Test
    public  void findUserByIds(){
        SqlSession sqlSession=null;
        try {
            sqlSession = sqlSessionFactory.openSession();
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);

            UserWarp userWarp=new UserWarp();
            List<Integer> ids=new ArrayList<>();
            ids.add(1);
            ids.add(3);
            ids.add(5);
            userWarp.setIds(ids);
            List<User> list = mapper.findUserByIds(userWarp);
            System.out.println(list);
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            if(sqlSession!=null){
                sqlSession.close();
            }
        }
    }

    //查询所有用户
    @Test
    public  void findUserByAll(){
        SqlSession sqlSession=null;
        try {
            sqlSession = sqlSessionFactory.openSession();
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);

            List<User> list = mapper.findUserByAll();
            System.out.println(list);
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            if(sqlSession!=null){
                sqlSession.close();
            }
        }
    }
    //查询用户及其下的订单详情
    @Test
    public  void findUserOrdersById(){
        SqlSession sqlSession=null;
        try {
            sqlSession = sqlSessionFactory.openSession();
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);

            List<UserOrder> list = mapper.findUserOrdersById(1);
            System.out.println(list);
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            if(sqlSession!=null){
                sqlSession.close();
            }
        }
    }

    //查询用户及其下的订单详情
    @Test
    public  void findUserOrdersByIdToMap(){
        SqlSession sqlSession=null;
        try {
            sqlSession = sqlSessionFactory.openSession();
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);

            List<User> list = mapper.findUserOrdersByIdToMap(1);
            System.out.println(list);
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            if(sqlSession!=null){
                sqlSession.close();
            }
        }
    }

}


下面是关于订单的

order的pojo类

package com.baoyuan.mybatis.pojo;

import java.util.Date;

public class Order {
    private Integer id;

    private Integer userId;

    private String number;

    private Date createtime;

    private String note;

    private User user;

    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 Integer getUserId() {
        return userId;
    }

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

    public String getNumber() {
        return number;
    }

    public void setNumber(String number) {
        this.number = number == null ? null : number.trim();
    }

    public Date getCreatetime() {
        return createtime;
    }

    public void setCreatetime(Date createtime) {
        this.createtime = createtime;
    }

    public String getNote() {
        return note;
    }

    public void setNote(String note) {
        this.note = note == null ? null : note.trim();
    }

    @Override
    public String toString() {
        return "Order [id=" + id + ", userId=" + userId + ", number=" + number + ", createtime=" + createtime
                + ", note=" + note + "]";
    }

}

一对一查询订单和对应用户用到的pojo

package com.baoyuan.mybatis.pojo;

public class OrderToUser extends Order{
    private String username;
    private  String address;

    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
    @Override
    public String toString() {
        return super.toString()+"OrderToUser [username=" + username + ", address=" + address + "]";
    }

}

order定义的mapper接口

package com.baoyuan.mybatis.mapper;

import com.baoyuan.mybatis.pojo.Order;
import com.baoyuan.mybatis.pojo.OrderToUser;

public interface OrderMapper {
    public OrderToUser findOrderUserById(Integer id);

    public Order findOrderByIdToMap(Integer id);

    public Order findOrderUserByIdToMap(Integer id);
}

order的mybatis配置文件

<?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.baoyuan.mybatis.mapper.OrderMapper">
    <!--关联查询:查询对应订单详情和用户名,用户地址运用resultType  -->
    <select id="findOrderUserById" parameterType="int" resultType="com.baoyuan.mybatis.pojo.OrderToUser">
        SELECT
            o.*, u.username,
            u.address
        FROM
            `order` o,
            `user` u
        WHERE
            o.user_id = u.id
        AND o.id =#{id}
    </select>

    <resultMap type="order" id="find_order_resultMap">
    <!-- 定义单表查询列名和pojo属性名相同可以不写 -->
        <!-- <id column="id" property="id"/> -->
        <result column="user_id" property="userId"/>
    </resultMap>

    <!--一对一关联查询时:查询对应订单详情和用户名,用户地址,发现userid为null,原因:数据库列名与pojo类的属性名不一样 ,所以用resultMap -->
    <select id="findOrderByIdToMap" parameterType="int" resultMap="find_order_resultMap" >
        select * from `order` where id =#{id}
    </select>

    <!--关联查询的所有字段都要写 ,先要在order中要有User的引用属性 -->
    <resultMap type="order" id="find_order_user_byid">
         <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"/>
        <association property="user" javaType="com.baoyuan.mybatis.pojo.User">
        <!-- 一对一关联映射 -->
        <!--
        property:Orders对象的user属性
        javaType:user属性对应 的类型
         -->
            <id column="user_id" property="id"/>
            <result column="username" property="username"/>
            <result column="address" property="address"/>
            <result column="sex" property="sex"/>
            <result column="birthday" property="birthday"/>
        </association>
    </resultMap>

    <!--一对一关联查询:查询对应订单详情和用户名,用户地址运用resultMap  -->
    <select id="findOrderUserByIdToMap" parameterType="int" resultMap="find_order_user_byid">
        SELECT
            o.*, u.username,
            u.address
        FROM
            `order` o,
            `user` u
        WHERE
            o.user_id = u.id
        AND o.id =#{id}
    </select>

</mapper>

oreer订单的测试代码

package com.baoyuan.mybatis.test;

import java.io.IOException;
import java.io.InputStream;

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 com.baoyuan.mybatis.mapper.OrderMapper;
import com.baoyuan.mybatis.pojo.Order;
import com.baoyuan.mybatis.pojo.OrderToUser;

public class TestOrderMapper {
    private SqlSessionFactory sqlSessionFactory;
    @Before
    public void init() throws IOException{
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        InputStream inputStream=Resources.getResourceAsStream("SqlMapConfig.xml");
        this.sqlSessionFactory= builder.build(inputStream);

    }
    //查询订单及用户
    @Test
    public void findOrderUserById(){
        SqlSession sqlSession=null;
        try {
            //获取sqlsesison
             sqlSession = sqlSessionFactory.openSession();
            //获取mapper代理类
            OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
            OrderToUser orderToUser = mapper.findOrderUserById(3);
            System.out.println(orderToUser);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally{
            if(sqlSession!=null){
                sqlSession.close();
            }
        }
    }
    //根据resultMap查询订单
    @Test
    public void findOrderByIdToMap(){
        SqlSession sqlSession=null;
        try {
            //获取sqlsesison
            sqlSession = sqlSessionFactory.openSession();
            //获取mapper代理类
            OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
            Order order = mapper.findOrderByIdToMap(3);
            System.out.println(order);
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            if(sqlSession!=null){
                sqlSession.close();
            }
        }
    }

    //根据resultMap查询订单及用户
    @Test
    public void findOrderUserByIdToMap(){
        SqlSession sqlSession=null;
        try {
            //获取sqlsesison
            sqlSession = sqlSessionFactory.openSession();
            //获取mapper代理类
            OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
            Order order = mapper.findOrderUserByIdToMap(3);
            System.out.println(order);
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            if(sqlSession!=null){
                sqlSession.close();
            }
        }
    }
}

注意点:dtd约束是有顺序的

关联查询的配置:

当POJO中属性名和查询的列名不一致的时候使用resultmap比较多。

当POJO中的属性名和查询的列名一致的时候,是使用resutltype.(推荐使用)

时间: 07-28

mybatis的动态sql编写以及一对一关系查询和一对多的查询的相关文章

MyBatis的动态SQL详解

MyBatis的动态SQL是基于OGNL表达式的,它可以帮助我们方便的在SQL语句中实现某些逻辑,本文详解mybatis的动态sql,需要的朋友可以参考下 MyBatis 的一个强大的特性之一通常是它的动态 SQL 能力.如果你有使用 JDBC 或其他 相似框架的经验,你就明白条件地串联 SQL 字符串在一起是多么的痛苦,确保不能忘了空 格或在列表的最后省略逗号.动态 SQL 可以彻底处理这种痛苦. 通常使用动态SQL不可能是独立的一部分,MyBatis当然使用一种强大的动态SQL语言来改进这种

mybatis入门-动态sql

什么是动态sql 判断的动态sql mybatis核心就是对sql语句进行灵活操作,通过表达式进行判断,对sql进行灵活拼接.组装. 现有需求如下:需要查询用户,输入的是用户类,如果用户的性别类不为空,则将性别作为查询条件之一,如果用户的姓名不为空,则将用户姓名作为查询条件之一.如果用户两个属性都为空,则查询所有用户. 我们知道,在mapper中,我们的传入参数只有一个,多个参数只能通过包转类来实现,现在这种问题怎么解决呢?答案就是在xml文件中加入判断,使sql语句动态生成.刚才的需求所对应的

MyBatis的动态SQL查询-让查询更灵活多变!

序言 MyBatis,大家都知道,半自动的ORM框架,原来叫ibatis,后来好像是10年apache软件基金组织把它托管给了goole code,就重新命名了MyBatis,功能相对以前更强大了.它相对全自动的持久层框架Hibernate,更加灵活,更轻量级,这点我还是深有体会的. MyBatis的一个强大特性之一就是动态SQL能力了,能省去我们很多串联判断拼接SQL的痛苦,根据项目而定,在一定的场合下使用,能大大减少程序的代码量和复杂程度,不过还是不是过度太过复杂的使用,以免不利于后期的维护

mybatis 之动态 SQL

1.动态 SQL 简介: 动态 SQL 是 MyBatis 强大特性之一.极大的简化我们拼装 SQL 的操作. 动态 SQL 元素和使用 JSTL 或者其他类似基于 XML 的文本处理器相似. MyBatis 采用功能强大的基于 OGNL 的表达式来简化操作. - if - choose(when,otherwise) - trim(where,set) - foreach 2.if:判断(要求是携带了哪个字段查询条件,就带上这个字段的值): a.创建一个 EmployeeMapperDynam

MyBatis:动态SQL

https://mybatis.github.io/mybatis-3/zh/dynamic-sql.html ———————————————————————————————————————————————————————— MyBatis 最强大的特性之一就是它的动态语句功能.如果您以前有使用JDBC或者类似框架的经历,您就会明白把SQL语句条件连接在一起是多么的痛苦,要确保不能忘记空格或者不要在columns列后面省略一个逗号等.动态语句能够完全解决掉这些痛苦. 尽管与动态SQL一起工作不是

MyBatis执行动态sql及存储过程

1 利用MyBatis进行模糊查询 在利用MyBatis进行模糊查询时候,需要用到concat,注意在Oracle中concat连接的是两个参数,不是三个参数: 持久化类: package com.web.entity; public class Student { private Integer studentId; private String studentName; private Classes classes; public Integer getStudentId() { retu

mybatis 详解(五)------动态SQL

前面几篇博客我们通过实例讲解了用mybatis对一张表进行的CRUD操作,但是我们发现写的 SQL 语句都比较简单,如果有比较复杂的业务,我们需要写复杂的 SQL 语句,往往需要拼接,而拼接 SQL ,稍微不注意,由于引号,空格等缺失可能都会导致错误. 那么怎么去解决这个问题呢?这就是本篇所讲的使用 mybatis 动态SQL,通过 if, choose, when, otherwise, trim, where, set, foreach等标签,可组合成非常灵活的SQL语句,从而在提高 SQL

MyBatis之级联——一对一关系

在学数据库概论的时候会听到这么几个词:数据库的关系分为一对一.一对多.多对多.对于在学校里学的知识已经忘得差不多了,在这里简单的提一下数据库的关系.此篇是介绍MyBatis是如何实现数据库中一对一关系的,我们就暂且先介绍一对一关系.所谓一对一关系其实在生活中很常见,比如一个学生有且只对应一个属于他的学生证.下面就是我们的所假设的数据库物理模型. 在这个数据库模型中,学生证和学生表是1对1的关系.那么基于此,我们会在Java代码的POJO包中就会有两个POJO对象,Student和SelfCard

MyBatis 动态sql详解

MyBatis的动态sql语句 1.if 条件 2.choose , when 和 otherwise条件 3.where 条件 where条件:1.自动加上where.2.如果where子句以and或者or开头,则自动删除第一个and或者or..所以我们不需要自己加where 4.trim 条件 trim条件和where条件类似但是功能更强大:不仅可以替换掉子句开头的and或者or,还提供了加前缀和后缀的功能. 5.forEach循环 6.set 条件 set条件:自动加上set,自动去除最后