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

首页 > 代码库 > mybatis的动态sql编写以及一对一关系查询和一对多的查询

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

2024-11-12 12:19:02 204人阅读

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

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


/*!40101 SET NAMES utf8 */;

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

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=‘NO_AUTO_VALUE_ON_ZERO‘ */;
/*!40111 SET @OLD_SQL_NOTES=@@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 SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_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="http://www.mamicode.com/${jdbc.driverClassName}"/>
                <property name="url" value="http://www.mamicode.com/${jdbc.url}"/>
                <property name="username" value="http://www.mamicode.com/${jdbc.username}"/>
                <property name="password" value="http://www.mamicode.com/${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.(推荐使用)

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

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > mybatis的动态sql编写以及一对一关系查询和一对多的查询

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

看完仍有疑问？有类似问题直接问程序猿