Mybatis框架知识整理

我们根据实体类的不同取值，使用不同的 SQL语句来进行查询。比如在 id如果不为空时可以根据id查询，如果username 不同空时还要加入用户名作为条件。这种情况在我们的多条件组合查询中经常会碰到。

接口类

package com.jtyhnet.dao;

import com.jtyhnet.domain.User;

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

public interface UserDao {

    List findAll() throws IOException;
    User findById(int id);
    User findByCondition(User user);
}

mapper配置

    
        select * from user
        
            
                and id = #{id}
            
            
                and username = #{username}
            
        
    
 
测试代码
 
    @Test
    public void test22() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserDao userDaoMapper = sqlSession.getMapper(UserDao.class);

        User user = new User();
        user.setId(1);
        user.setUsername("zhangsan");

        User user1 = userDaoMapper.findByCondition(user);
        System.out.println(user1);

    }
 

 改变条件再次测试
 
    @Test
    public void test22() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserDao userDaoMapper = sqlSession.getMapper(UserDao.class);

        User user = new User();
        user.setId(1);
        //user.setUsername("zhangsan");

        User user1 = userDaoMapper.findByCondition(user);
        System.out.println(user1);

    }
 

 可见查询语句随参数的改变动态变化
 
动态 SQL 之 
循环执行sql的拼接 *** 作，例如：SELECT * FROM USER WHERe id IN (1,2,5)。
 
接口增加
 
    List findByIds(List ids);
 
mapper增加
 
    
        select * from user
        
            
                #{id}
            
        
    
 
    @Test
    public void test23() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserDao userDaoMapper = sqlSession.getMapper(UserDao.class);
        ArrayList idList = new ArrayList<>();
        idList.add(1);
        idList.add(4);
        idList.add(5);
        List userList = userDaoMapper.findByIds(idList);
        System.out.println(userList);


    }
 
 

 foreach标签的属性含义如下：
 标签用于遍历集合，它的属性：
 • collection：代表要遍历的集合元素，注意编写时不要写#{}
 • open：代表语句的开始部分
 • close：代表结束部分
 • item：代表遍历集合的每个元素，生成的变量名
 • sperator：代表分隔符
 
SQL片段抽取 
Sql 中可将重复的 sql 提取出来，使用时用 include 引用即可，最终达到 sql 重用的目的
 
select * from user

 




    select * from user

    
        
    

    
         where id = #{id}
    
    
    
        
        
            
                and id = #{id}
            
            
                and username = #{username}
            
        
    

    
        
        
            
                #{id}
            
        
    

无论是 MyBatis 在预处理语句（PreparedStatement）中设置一个参数时，还是从结果集中取出一个值时， 都会用类型处理器将获取的值以合适的方式转换成 Java 类型。下表描述了一些默认的类型处理器（截取部分）。

你可以重写类型处理器或创建你自己的类型处理器来处理不支持的或非标准的类型。具体做法为：实现org.apache.ibatis.type.TypeHandler 接口， 或继承一个很便利的类org.apache.ibatis.type.baseTypeHandler， 然后可以选择性地将它映射到一个JDBC类型。例如需求：一个Java中的Date数据类型，我想将之存到数据库的时候存成一个1970年至今的毫秒数，取出来时转换成java的Date，即java的Date与数据库的varchar毫秒值之间转换。

开发步骤：
① 定义转换类继承类baseTypeHandler
② 覆盖4个未实现的方法，其中setNonNullParameter为java程序设置数据到数据库的回调方法，getNullableResult为查询时 mysql的字符串类型转换成 java的Type类型的方法
③ 在MyBatis核心配置文件中进行注册
④ 测试转换是否正确

创建测试用表

CREATE TABLE userinfo (
    id INT NOT NULL auto_increment,	 
    username VARCHAR ( 50 ) ,	
    password VARCHAR ( 50 )  ,		
    birthday VARCHAR ( 50 )  ,		 
    PRIMARY KEY ( id ) 				 
);
insert  into userinfo values (null,'zhangsan','123','1640784746140');

创建实体类

package com.jtyhnet.domain;

import java.util.Date;

public class Userinfo {

    private int id;
    private String username;
    private String password;
    private Date birthday;

    public int getId() {
        return id;
    }

    public void setId(int 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 Date getBirthday() {
        return birthday;
    }

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

    @Override
    public String toString() {
        return "Userinfo{" +
                "id=" + id +
                ", username='" + username + ''' +
                ", password='" + password + ''' +
                ", birthday=" + birthday +
                '}';
    }
}

插入数据接口方法

package com.jtyhnet.dao;

import com.jtyhnet.domain.Userinfo;

public interface UserinfoDao {
    void add(Userinfo userinfo);
}

定义转换类

package com.jtyhnet.typeHandler;

import org.apache.ibatis.type.baseTypeHandler;
import org.apache.ibatis.type.JdbcType;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class MyDateTypeHandler extends baseTypeHandler {
    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, Date date, JdbcType jdbcType) throws SQLException {
        preparedStatement.setString(i,date.getTime()+"");
    }

    @Override
    public Date getNullableResult(ResultSet resultSet, String s) throws SQLException {
        return new Date(resultSet.getLong(s));
    }

    @Override
    public Date getNullableResult(ResultSet resultSet, int i) throws SQLException {
        return new Date(resultSet.getLong(i));
    }

    @Override
    public Date getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        return callableStatement.getDate(i);
    }
}

mybatis核心配置类
添加userinfo别名，注册自定义转换类

mapper类

    
        insert into userinfo values(#{id},#{username},#{password},#{birthday});
    

测试

    @Test
    public void test31() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserinfoDao userinfoDaoMapper = sqlSession.getMapper(UserinfoDao.class);

        Userinfo userinfo = new Userinfo();
        userinfo.setUsername("lisi");
        userinfo.setPassword("1234");
        userinfo.setBirthday(new Date());
        userinfoDaoMapper.add(userinfo);

        sqlSession.commit();
        sqlSession.close();
    }

查询数据库确认插入成功，date类型被转为varchar

测试查询

    @Test
    public void test32() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserinfoDao userinfoDaoMapper = sqlSession.getMapper(UserinfoDao.class);

        Userinfo userinfo = userinfoDaoMapper.findById(1);

        System.out.println(userinfo);
        sqlSession.close();
    }

可以发现查询结果已经由varchar转为Date类型

package com.jtyhnet.typeHandler;

import org.apache.ibatis.type.baseTypeHandler;
import org.apache.ibatis.type.JdbcType;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class MyDateTypeHandler extends baseTypeHandler {
    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, Date date, JdbcType jdbcType) throws SQLException {
        preparedStatement.setString(i,date.getTime()+"");
    }

    @Override
    public Date getNullableResult(ResultSet resultSet, String s) throws SQLException {
        return new Date(resultSet.getLong(s));
    }

    @Override
    public Date getNullableResult(ResultSet resultSet, int i) throws SQLException {
        return new Date(resultSet.getLong(i));
    }

    @Override
    public Date getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        return callableStatement.getDate(i);
    }
}

1. 自定义转换类继承baseTypeHandler，其中T是javaType，上述案例中，birthday字段在java中为Date类型，在数据库中为varchar类型，因此继承baseTypeHandler
2. 继承后需复写4个方法
   setNonNullParameter，用于preparedStatement执行sql时，对参数进行设置，i为参数角标，即第几个参数，上述案例中，java端设置的birthday为Date类型，在执行数据库insert *** 作时，需转为String类型才能插入到数据库Varchar类型字段中，因此，preparedStatement.setString(i,date.getTime()+"");
   getNullableResult(ResultSet resultSet, String s)，getNullableResult(ResultSet resultSet, int i)，用于将从数据库查询的结果封装为javaBean，即将查询出的String类型转为Date类型，其中s为字段名，i为字段角标。
   getNullableResult(CallableStatement callableStatement, int i)为存储过程使用，i为字段角标。

MyBatis可以使用第三方的插件来对功能进行扩展，分页助手PageHelper是将分页的复杂 *** 作进行封装，使用简单的方式即可获得分页的相关数据
开发步骤：
① 导入通用PageHelper的坐标
② 在mybatis核心配置文件中配置PageHelper插件
③ 测试分页数据获取

        
            com.github.pagehelper
            pagehelper
            3.7.5
        
        
            com.github.jsqlparser
            jsqlparser
            0.9.1
        

核心配置文件中配置分页插件

新建查询所有接口方法

    List findAll();

配置mapper

    
        select * from userinfo
    
 
测试
 
    @Test
    public void test33() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserinfoDao userinfoDaoMapper = sqlSession.getMapper(UserinfoDao.class);
        PageHelper.startPage(1,2);
        List userinfoList = userinfoDaoMapper.findAll();
        System.out.println("userinfoList-------"+userinfoList);
        for (Userinfo userinfo : userinfoList) {
            System.out.println(userinfo);
        }
        System.out.println("==================================");
        PageInfo userinfoPageInfo = new PageInfo<>(userinfoList);
        System.out.println("总条数:"+userinfoPageInfo.getTotal());
        System.out.println("总页数:"+userinfoPageInfo.getPages());
        System.out.println("当前页:"+userinfoPageInfo.getPageNum());
        System.out.println("每页显示长度:"+userinfoPageInfo.getPageSize());
        System.out.println("是否第一页:"+userinfoPageInfo.isIsFirstPage());
        System.out.println("是否最后一页:"+userinfoPageInfo.isIsLastPage());
    }
 
 
MyBatis的多表 *** 作 
Mybatis多表查询 
一对一查询 
案例：
 用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户
 一对一查询的需求：查询一个订单，与此同时查询出该订单所属的用户
 
create table orderlist (
id INT NOT NULL auto_increment,
ordertime VARCHAR ( 255 ) ,
totalmoney decimal(20,2),
uid int,
PRIMARY KEY ( id ) 
)

insert into orderlist values (null,'2021-12-30 21:46:30',5000,1);
insert into orderlist values (null,'2021-12-29 21:46:30',400,1);
insert into orderlist values (null,'2021-12-28 21:46:30',40,2);
insert into orderlist values (null,'2021-12-27 21:46:30',2321,2);
insert into orderlist values (null,'2021-12-26 21:46:30',4211,3);
insert into orderlist values (null,'2021-12-25 21:46:30',2000,4);

CREATE TABLE userinfo (
id INT NOT NULL auto_increment,	 
username VARCHAR ( 50 ) ,	
password VARCHAR ( 50 )  ,		
birthday VARCHAR ( 50 )  ,		 
PRIMARY KEY ( id ) 				 
);

insert  into userinfo values (null,'zhangsan','123','1640784746140');
insert  into userinfo values (null,'lisi','1234','1640785271387');
insert  into userinfo values (null,'wangwu','123','1640784746140');
insert  into userinfo values (null,'zhaoliu','123','1640784746140');

 

 
 对应的sql语句：select * from orderlist o,userinfo u where o.uid=u.id;
 

 IDEA中创建实体类
 
package com.jtyhnet.domain;

import java.util.Date;

public class Order {

    private int id;
    private Date ordertime;
    private double totalmoney;

    //代表当前订单属于哪个客户
    private OrderUserinfo orderUserinfo;

    public int getId() {
        return id;
    }

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

    public Date getOrdertime() {
        return ordertime;
    }

    public void setOrdertime(Date ordertime) {
        this.ordertime = ordertime;
    }

    public double getTotalmoney() {
        return totalmoney;
    }

    public void setTotalmoney(double totalmoney) {
        this.totalmoney = totalmoney;
    }

    public OrderUserinfo getOrderUserinfo() {
        return orderUserinfo;
    }

    public void setOrderUserinfo(OrderUserinfo orderUserinfo) {
        this.orderUserinfo = orderUserinfo;
    }

    @Override
    public String toString() {
        return "Order{" +
                "id=" + id +
                ", ordertime=" + ordertime +
                ", totalmoney=" + totalmoney +
                ", orderUserinfo=" + orderUserinfo +
                '}';
    }
}

 
package com.jtyhnet.domain;


public class OrderUserinfo {

    private int id;
    private String username;
    private String password;

    public int getId() {
        return id;
    }

    public void setId(int 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;
    }

    @Override
    public String toString() {
        return "OrderUserinfo{" +
                "id=" + id +
                ", username='" + username + ''' +
                ", password='" + password + ''' +
                '}';
    }

}

 
创建接口类
 
package com.jtyhnet.mapper;

import com.jtyhnet.domain.Order;

import java.util.List;

public interface OrderMapper {
    List findAll();
}

 
mybatis核心配置文件
 




    

    
        
        
    
    

    
        
            
            
                
                
                
                
            
        
    

    
        
        
    

 
配置mapper
 



    
    
        
        
        
        
        
        
    

    
        
        
        
        
            
            
            
        
    
    
    
        select * from orderlist T1,userinfo T2 where T1.uid = T2.id
    

 
由于关联后的结果，映射到order类中，字段orderUserinfo引用了另一个类OrderUserinfo，因此不能直接将结果封装，需定义resultMap，设置java类中的属性与数据库表字段相关联。设置方式有两种，一种是column为数据库中字段，property采用类名.属性，另一种是使用association 标签，单独定义引用类。
 
测试
 
    @Test
    public void test11() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
        List orderList = orderMapper.findAll();
        for (Order order : orderList) {
            System.out.println(order);
        }

        sqlSession.close();
    }
 
 
一对多查询 
案例：
 用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户
 一对多查询的需求：查询一个用户，与此同时查询出该用户具有的订单
 
改造用户OrderUserinfo类，添加订单属性
 
package com.jtyhnet.domain;


import java.util.List;

public class OrderUserinfo {

    private int id;
    private String username;
    private String password;

    //当前用户下的所有订单
    private List orderList;

    public int getId() {
        return id;
    }

    public void setId(int 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 List getOrderList() {
        return orderList;
    }

    public void setOrderList(List orderList) {
        this.orderList = orderList;
    }

    @Override
    public String toString() {
        return "OrderUserinfo{" +
                "id=" + id +
                ", username='" + username + ''' +
                ", password='" + password + ''' +
                ", orderList=" + orderList +
                '}';
    }
}

 
编写接口类
 
package com.jtyhnet.mapper;

import com.jtyhnet.domain.OrderUserinfo;

import java.util.List;

public interface OrderUserinfoMapper {
    List findAll();
}

 
编写mapper配置文件
 



    
        
        
        
        
            
            
            
        
    
    
        select *,o.id oid from userinfo u left join orderlist o on o.uid=u.id
    

由于一个用户对应多个订单，此处订单List使用collection标签定义
测试

    @Test
    public void test12() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        OrderUserinfoMapper orderUserinfoMapper = sqlSession.getMapper(OrderUserinfoMapper.class);
        List orderUserinfoList = orderUserinfoMapper.findAll();
        for (OrderUserinfo orderUserinfo : orderUserinfoList) {
            System.out.println(orderUserinfo.getUsername());
            for (Order order : orderUserinfo.getOrderList()) {
                System.out.println(order);
            }
            System.out.println("--------------------------");
        }
        sqlSession.close();
    }

案例：
用户表和角色表的关系为，一个用户有多个角色，一个角色被多个用户使用
多对多查询的需求：查询用户同时查询出该用户的所有角色

用户表增加属性角色列表

package com.jtyhnet.domain;


import java.util.List;

public class OrderUserinfo {

    private int id;
    private String username;
    private String password;

    //当前用户下的所有订单
    private List orderList;

    //当前用户所具有的角色
    private List roleList;

    public int getId() {
        return id;
    }

    public void setId(int 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 List getOrderList() {
        return orderList;
    }

    public void setOrderList(List orderList) {
        this.orderList = orderList;
    }

    public List getRoleList() {
        return roleList;
    }

    public void setRoleList(List roleList) {
        this.roleList = roleList;
    }

    @Override
    public String toString() {
        return "OrderUserinfo{" +
                "id=" + id +
                ", username='" + username + ''' +
                ", password='" + password + ''' +
                ", orderList=" + orderList +
                ", roleList=" + roleList +
                '}';
    }
}

接口类增加方法

    List findAllUserAndRole();

设置mapper

    
        
        
        
        
            
            
            
        
    
    
    
        select * from orderlist T1,userinfo T2 where T1.uid = T2.id
    
 
测试
 
    @Test
    public void test13() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        OrderUserinfoMapper orderUserinfoMapper = sqlSession.getMapper(OrderUserinfoMapper.class);
        List allUserAndRole = orderUserinfoMapper.findAllUserAndRole();
        for (OrderUserinfo orderUserinfo : allUserAndRole) {
            System.out.println(orderUserinfo.getUsername());
            for (Role role : orderUserinfo.getRoleList()) {
                System.out.println(role);
            }
            System.out.println("---------------");
        }
        sqlSession.close();
    }
 

 总结：
 MyBatis多表配置方式：
 一对一配置：使用做配置
 一对多配置：使用+做配置
 多对多配置：使用+做配置
 
MyBatis注解开发 
MyBatis的常用注解 
这几年来注解开发越来越流行，Mybatis也可以使用注解开发方式，这样我们就可以减少编写Mapper映射文件了。我们先围绕一些基本的CRUD来学习，再学习复杂映射多表 *** 作。
 @Insert：实现新增
 @Update：实现更新
 @Delete：实现删除
 @Select：实现查询
 @Result：实现结果集封装
 @Results：可以与@Result 一起使用，封装多个结果集
 @One：实现一对一结果集封装
 @Many：实现一对多结果集封装
 
MyBatis的增删改查 
@Insert插入数据 
测试注解开发向数据库表中insert数据
 
实体类
 
package com.jtyhnet.domain;

import java.util.Date;

public class Userinfo {

    private int id;
    private String username;
    private String password;
    private Date birthday;

    public int getId() {
        return id;
    }

    public void setId(int 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 Date getBirthday() {
        return birthday;
    }

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

    @Override
    public String toString() {
        return "Userinfo{" +
                "id=" + id +
                ", username='" + username + ''' +
                ", password='" + password + ''' +
                ", birthday=" + birthday +
                '}';
    }
}

 
接口类
 
package com.jtyhnet.mapper;

import com.jtyhnet.domain.Userinfo;
import org.apache.ibatis.annotations.Insert;

public interface UserinfoMapper {
    @Insert("insert into userinfo values (#{id},#{username},#{password},#{birthday})")
    void add(Userinfo userinfo);
}

 
自定义转换器
 
package com.jtyhnet.typeHandler;

import org.apache.ibatis.type.baseTypeHandler;
import org.apache.ibatis.type.JdbcType;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class MyDateTypeHandler3 extends baseTypeHandler {
    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, Date date, JdbcType jdbcType) throws SQLException {
        preparedStatement.setString(i,date.getTime()+"");
    }

    @Override
    public Date getNullableResult(ResultSet resultSet, String s) throws SQLException {
        return new Date(resultSet.getLong(s));
    }

    @Override
    public Date getNullableResult(ResultSet resultSet, int i) throws SQLException {
        return new Date(resultSet.getLong(i));
    }

    @Override
    public Date getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        return callableStatement.getDate(i);
    }
}

 
核心配置类
 




    

    
        
    

    
        
            
            
                
                
                
                
            
        
    

    
        
        

        

    


 
使用注解开发后，标签内使用扫描使用注解的类或扫描使用注解的类所在的包
 
测试
 
import com.jtyhnet.domain.Userinfo;
import com.jtyhnet.mapper.UserinfoMapper;
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.IOException;
import java.io.InputStream;
import java.util.Date;

public class test1 {

    private UserinfoMapper userinfoMapper;
    private SqlSession sqlSession;

    @Before
    public void before() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        sqlSession = sqlSessionFactory.openSession();
        userinfoMapper = sqlSession.getMapper(UserinfoMapper.class);
    }

    @Test
    public void testAdd() {
        Userinfo userinfo = new Userinfo();
        userinfo.setUsername("张三");
        userinfo.setPassword("1234");
        userinfo.setBirthday(new Date());
        userinfoMapper.add(userinfo);
        sqlSession.commit();
        sqlSession.close();
    }

}

 
 
@Delete删除数据 
接口类中增加
 
    @Delete("delete from userinfo where id = #{id}")
    void delete(int id);
 
测试
 
    @Test
    public void testDelete(){
        userinfoMapper.delete(8);
        sqlSession.commit();
        sqlSession.close();
    }
 
 
@Update更新数据 
    @Update("update userinfo set username=#{username},password=#{password},birthday=#{birthday} where id = #{id}")
    void update(Userinfo userinfo);
 
    @Test
    public void testUpdate(){
        Userinfo userinfo = new Userinfo();
        userinfo.setId(7);
        userinfo.setUsername("李四1");
        userinfo.setPassword("wer111");
        userinfo.setBirthday(new Date());
        userinfoMapper.update(userinfo);
    }
 
 
@Select查询数据 
    @Select("select * from userinfo")
    List findAll();

    @Select("select * from userinfo where id = #{id}")
    Userinfo findById(int id);
 
    @Test
    public void testFindAll(){
        List userinfoList = userinfoMapper.findAll();
        for (Userinfo userinfo : userinfoList) {
            System.out.println(userinfo);
        }
    }

    @Test
    public void testFindById(){
        Userinfo userinfo = userinfoMapper.findById(1);
        System.out.println(userinfo);
    }
 
 
 
复杂映射 
实现复杂关系映射之前我们可以在映射文件中通过配置来实现，使用注解开发后，我们可以使用@Results注解，@Result注解，@One注解，@Many注解组合完成复杂关系的配置
 
注解说明@Results代替的是标签该注解中可以使用单个@Result注解，也可以使用@Result集合。使用格式：@Results（{@Result（），@Result（）}）或@Results（@Result（））@Result代替了标签和标签 
 @Result中属性介绍：
 column：数据库的列名
 property：需要装配的属性名
 one：需要使用的@One 注解（@Result（one=@One）（）））
 many：需要使用的@Many 注解（@Result（many=@many）（）））@One(一对一)代替了 标签，是多表查询的关键，在注解中用来指定子查询返回单一对象。
 @One注解属性介绍：select: 指定用来多表查询的 sqlmapper
 使用格式：@Result(column=" “,property=”",one=@One(select=""))@Many(多对一)代替了标签, 是是多表查询的关键，在注解中用来指定子查询返回对象集合。
 使用格式：@Result(property="",column="",many=@Many(select=""))
一对一查询 
订单与客户案例
 实体类
 
package com.jtyhnet.domain;


public class Order {

    private int id;
    private Double totalMoney;
    private Userinfo userinfo;

    public int getId() {
        return id;
    }

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

    public Double getTotalMoney() {
        return totalMoney;
    }

    public void setTotalMoney(Double totalMoney) {
        this.totalMoney = totalMoney;
    }

    public Userinfo getUserinfo() {
        return userinfo;
    }

    public void setUserinfo(Userinfo userinfo) {
        this.userinfo = userinfo;
    }

    @Override
    public String toString() {
        return "Order{" +
                "id=" + id +
                ", totalMoney=" + totalMoney +
                ", userinfo=" + userinfo +
                '}';
    }
}

 
接口类
 
package com.jtyhnet.mapper;

import com.jtyhnet.domain.Order;
import com.jtyhnet.domain.Userinfo;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface OrderMapper {

    @Select("select * from orderlist")
    @Results({
            @Result(id = true,property = "id",column = "id"),
            @Result(property = "totalMoney",column = "totalmoney"),
            @Result(property = "userinfo",column = "uid",javaType = Userinfo.class,
                    one = @One(select = "com.jtyhnet.mapper.UserinfoMapper.findById"))
    })
    List findAll();


}

 
测试
 
    @Test
    public void testFindAllOrder(){
        List orderList = orderMapper.findAll();
        for (Order order : orderList) {
            System.out.println(order);
        }
    }
 

 一对一查询，使用注解开发，由于结果中有引用对象，使用@Results定义结果集，@Result定义每一个字段，
 
@Result(property = "userinfo",column = "uid",javaType = Userinfo.class,
                    one = @One(select = "com.jtyhnet.mapper.UserinfoMapper.findById"))
 
引用类的结果，property 为java类中属性名称，column 为主体类关联引用类，即数据库中主表关联附表使用的关联字段，javaType 定义该属性的类型，one用来定义根据该关联字段查询该条数据。
 
一对多查询 
用户下有多个订单
 
package com.jtyhnet.domain;

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

public class Userinfo {

    private int id;
    private String username;
    private String password;
    private Date birthday;

    //用户下所有订单
    private List orderList;


    public int getId() {
        return id;
    }

    public void setId(int 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 Date getBirthday() {
        return birthday;
    }

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

    public List getOrderList() {
        return orderList;
    }

    public void setOrderList(List orderList) {
        this.orderList = orderList;
    }

    @Override
    public String toString() {
        return "Userinfo{" +
                "id=" + id +
                ", username='" + username + ''' +
                ", password='" + password + ''' +
                ", birthday=" + birthday +
                ", orderList=" + orderList +
                '}';
    }
}

 
    @Select("select * from userinfo")
    @Results({
            @Result(id = true,property = "id",column = "id"),
            @Result(property = "username",column = "username"),
            @Result(property = "password",column = "password"),
            @Result(property = "birthday",column = "birthday"),
            @Result(property = "orderList",column = "id",javaType = List.class,
                    many = @Many(select = "com.jtyhnet.mapper.OrderMapper.findByUid"))
    })
    List findAllUserAndOrder();
 
    @Select("select * from orderlist where uid = #{uid}")
    List findByUid(int uid);
 
    @Test
    public void findAllUserAndOrder(){
        List allUserAndOrder = userinfoMapper.findAllUserAndOrder();
        for (Userinfo userinfo : allUserAndOrder) {
            System.out.println(userinfo.getUsername());
            for (Order order : userinfo.getOrderList()){
                System.out.println(order);
            }
        }

    }
 

 一对多查询时，与一对一查询基本类似，只是将引用类javaType改为List.class，使用many查询附表
 
#####多对多查询
 
用户与角色关联案例
 
角色类
 
package com.jtyhnet.domain;

public class Role {

    private int id;
    private String roleName;

    public int getId() {
        return id;
    }

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

    public String getRoleName() {
        return roleName;
    }

    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }

    @Override
    public String toString() {
        return "Role{" +
                "id=" + id +
                ", roleName='" + roleName + ''' +
                '}';
    }
}

 
用户类
 
package com.jtyhnet.domain;

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

public class Userinfo {

    private int id;
    private String username;
    private String password;
    private Date birthday;

    //用户下所有订单
    private List orderList;

    private List roleList;


    public int getId() {
        return id;
    }

    public void setId(int 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 Date getBirthday() {
        return birthday;
    }

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

    public List getOrderList() {
        return orderList;
    }

    public void setOrderList(List orderList) {
        this.orderList = orderList;
    }

    public List getRoleList() {
        return roleList;
    }

    public void setRoleList(List roleList) {
        this.roleList = roleList;
    }

    @Override
    public String toString() {
        return "Userinfo{" +
                "id=" + id +
                ", username='" + username + ''' +
                ", password='" + password + ''' +
                ", birthday=" + birthday +
                ", orderList=" + orderList +
                ", roleList=" + roleList +
                '}';
    }
}

 
    @Select("select * from userinfo")
    @Results({
            @Result(id = true,property = "id",column = "id"),
            @Result(property = "username",column = "username"),
            @Result(property = "password",column = "password"),
            @Result(property = "birthday",column = "birthday"),
            @Result(property = "roleList",column = "id",javaType = List.class,
                    many = @Many(select = "com.jtyhnet.mapper.RoleMapper.findRoleByUid"))
    })
    List findAllUserAndRole();
 
    @Select("select T1.* from role T1 inner join user_role T2 on T1.id = T2.role_id where T2.user_id = #{uid}")
    List findRoleByUid(int uid);
 
测试
 
    @Test
    public void findAllUserAndRole(){
        List allUserAndRole = userinfoMapper.findAllUserAndRole();
        for (Userinfo userinfo : allUserAndRole) {
            System.out.println(userinfo.getUsername());
            for (Role role : userinfo.getRoleList()){
                System.out.println(role);
            }
            System.out.println("+++++++++++++++++++++");
        }
    }
 
					
										


					

						
欢迎分享，转载请注明来源：内存溢出
原文地址: http://outofmemory.cn/zaji/5686661.html
					
				
								

										查询
										注解
										配置
										类型
										映射
									
								

					
						
							
						 赞
						(0)
					
					

						
							 打赏
						
							
								 微信扫一扫
							
							
								 支付宝扫一扫
							
						
					
				
				

					

						

							
								
								u盘安装系统教程
								一级用户组
							
						
						

							

								
									
										
									
									0
								
								
									
										
									
									0
								
							
							

								
									
										
									
									生成海报
								
								
									
										
									
								
								
									
										
									
								
								
									
										
									
								
								
									
										
									
								
							
							

								
									
										
									
								
							
						
					
				
			
			
            

            	            
            
			

								

					
						【算法】删除链表的倒数第N个结点
					
					

         				
							
								
							 上一篇
						
						2022-12-17
					
				
												

					
						理解Android的四种启动模式
					
					

         				
							下一篇 
           						
							
						
						2022-12-17
					
				
							

			
			

				

					

						发表评论
					
										

						

							请登录后评论...
						
						

							

								登录后才能评论
							
							提交
						
					
									
								
 评论列表（0条）
				

									
				
											
		

	

	





	

























    

    

    

        
            保存