这篇文章将为大家详细讲解有关Java中Mybatis框架多表操作与注解开发的示例分析,小编觉得挺实用的,因此分享给大家做个参考,希望大家阅读完这篇文章后可以有所收获。
一对一查询
一对一查询的模型
用户表和订单表的关系为,一个用户有多个订单,一个订单只从属于一个用户。
一对一查询的需求:查询一个订单,与此同时查询出该订单所属的用户
一对一查询的语句
对应的sql语句: select * from orders o,user u where o.uid=u.id;查询的结果如下:
创建Order和User实体
创建OrderMapper接口
public interface OrderMapper { //查询全部的方法 public List<Order> findAll();}
配置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"><mapper namespace="com.longdi.mapper.OrderMapper"> <resultMap id="orderMap" type="order"> <!--手动指定字段与实体属性的映射关系 column: 数据表的字段名称 property:实体的属性名称 --> <id column="oid" property="id"></id> <result column="ordertime" property="ordertime"></result> <result column="total" property="total"></result> <!--<result column="uid" property="user.id"></result> <result column="username" property="user.username"></result> <result column="password" property="user.password"></result> <result column="birthday" property="user.birthday"></result>--> <!-- property: 当前实体(order)中的属性名称(private User user) javaType: 当前实体(order)中的属性的类型(User) --> <association property="user" javaType="user"> <id column="uid" property="id"></id> <result column="username" property="username"></result> <result column="password" property="password"></result> <result column="birthday" property="birthday"></result> </association> </resultMap> <select id="findAll" resultMap="orderMap"> SELECT *,o.id oid FROM orders o,USER u WHERE o.uid=u.id </select> </mapper>
配置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.longdi.mapper.UserMapper"> <resultMap id="userMap" type="user"> <id column="uid" property="id"></id> <result column="username" property="username"></result> <result column="password" property="password"></result> <result column="birthday" property="birthday"></result> <!--配置集合信息 property:集合名称 ofType:当前集合中的数据类型 --> <collection property="orderList" ofType="order"> <!--封装order的数据--> <id column="oid" property="id"></id> <result column="ordertime" property="ordertime"></result> <result column="total" property="total"></result> </collection> </resultMap> <select id="findAll" resultMap="userMap"> SELECT *,o.id oid FROM USER u,orders o WHERE u.id=o.uid </select> <!--#############################################################################--> <resultMap id="userRoleMap" type="user"> <!--user的信息--> <id column="userId" property="id"></id> <result column="username" property="username"></result> <result column="password" property="password"></result> <result column="birthday" property="birthday"></result> <!--user内部的roleList信息--> <collection property="roleList" ofType="role"> <id column="roleId" property="id"></id> <result column="roleName" property="roleName"></result> <result column="roleDesc" property="roleDesc"></result> </collection> </resultMap> <select id="findUserAndRoleAll" resultMap="userRoleMap"> SELECT * FROM USER u,sys_user_role ur,sys_role r WHERE u.id=ur.userId AND ur.roleId=r.id </select> </mapper>
配置sqlMapConfig.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> <!--通过properties标签加载外部properties文件--> <properties resource="jdbc.properties"></properties> <!--自定义别名--> <typeAliases> <typeAlias type="com.longdi.domain.User" alias="user"></typeAlias> <typeAlias type="com.longdi.domain.Order" alias="order"></typeAlias> <typeAlias type="com.longdi.domain.Role" alias="role"></typeAlias> </typeAliases> <!--数据源环境--> <environments default="developement"> <environment id="developement"> <transactionManager type="JDBC"></transactionManager> <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> <mapper resource="com/longdi/mapper/UserMapper.xml"></mapper> <mapper resource="com/longdi/mapper/OrderMapper.xml"></mapper> </mappers></configuration>
测试代码
@Test public void test1() throws IOException { InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(); OrderMapper mapper = sqlSession.getMapper(OrderMapper.class); List<Order> orderList = mapper.findAll(); for (Order order : orderList) { System.out.println(order); } sqlSession.close(); }
一对多查询
一对多查询模型
用户表和订单表的关系为,一个用户有多个订单,一个订单只从属于一个用户一对多查询的需求:查询一个用户,与此同时查询出该用户具有的订单
对应的sql语句: select *,o.id oid from user u left join orders o on u.id=o.uid;查询的结果如下:
SELECT *,o.id oid FROM USER u, orders o WHERE u.id=o.uid
修改User实体
创建UserMapper接口
配置UserMapper.xml
测试结果
多对多查询
多对多查询的模型
用户表和角色表的关系为,一个用户有多个角色,一个角色被多个用户使用多对多查询的需求:查询用户同时查询出该用户的所有角色
多对多查询的语句
对应的sql语句: select u.*,r.*,r.id rid from user u left join user_role ur on u.id=ur.user_idinner join role r on ur.role_id=r.id;
查询的结果如下:
创建Role实体,修改User实体
添加UserMapper接口方法
配置UserMapper.xml(上面)
测试代码
总结:
MyBatis多表配置方式:
一对一配置:使用<resultMap>做配置
一对多配置:使用<resultMap>+<collection>做配置
多对多配置:使用<resultMap>+<collection>做配置
Mybatis的注解开发
Mybatis的常用注解
这几年来注解开发越来越流行,Mybatis也可以使用注解开发方式,这样我们就可以减少编写Mapper映射文件了。我们先围绕一些基本的CRUD来学习,再学习复杂映射多表操作。
@Insert:实现新增
@Update:实现更新@Delete:实现删除@Select:实现查询
@Result:实现结果集封装
@Results:可以与@Result一起使用,封装多个结果集@One:实现一对一结果集封装
@Many:实现一对多结果集封装
Mybatis的增删查改
private UserMapper mapper; @Before public void before() throws IOException { InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(true); mapper = sqlSession.getMapper(UserMapper.class); }
@Test public void testSave(){ User user = new User(); user.setUsername("tom"); user.setPassword("abc"); mapper.save(user); } @Test public void testUpdate(){ User user = new User(); user.setId(18); user.setUsername("lucy"); user.setPassword("123"); mapper.update(user); } @Test public void testDelete(){ mapper.delete(18); } @Test public void testFindById(){ User user = mapper.findById(2); System.out.println(user); } @Test public void testFindAll(){ List<User> all = mapper.findAll(); for (User user : all) { System.out.println(user); } }
修改MyBatis的核心配置文件,我们使用了注解替代的映射文件,所以我们只需要加载使用了注解的Mapper接口即可
<!--加载映射关系--> <mappers> <!--扫描使用注解的类--> <package name="com.longdi.mapper.UserMapper"></package> </mappers>
或者指定扫描包含映射关系的接口所在的包也可以
<!--加载映射关系--> <mappers> <!--指定接口所在的包--> <package name="com.longdi.mapper"></package> </mappers>
UserMapper:
public interface UserMapper { @Insert("insert into user values(#{id},#{username},#{password},#{birthday})") public void save(User user); @Update("update user set username=#{username},password=#{password} where id=#{id}") public void update(User user); @Delete("delete from user where id=#{id}") public void delete(int id); @Select("select * from user where id=#{id}") public User findById(int id); @Select("select * from user") public List<User> findAll(); @Select("select * from user") @Results({ @Result(id=true ,column = "id",property = "id"), @Result(column = "username",property = "username"), @Result(column = "password",property = "password"), @Result( property = "orderList", column = "id", javaType = List.class, many = @Many(select = "com.longdi.mapper.OrderMapper.findByUid") ) }) public List<User> findUserAndOrderAll(); @Select("SELECT * FROM USER") @Results({ @Result(id = true,column = "id",property = "id"), @Result(column = "username",property = "username"), @Result(column = "password",property = "password"), @Result( property = "roleList", column = "id", javaType = List.class, many = @Many(select = "com.longdi.mapper.RoleMapper.findByUid") ) }) public List<User> findUserAndRoleAll(); }
MyBatis的注解实现复杂映射开发
实现复杂关系映射之前我们可以在映射文件中通过配置<resultMap>来实现,使用注解开发后,我们可以使用@Results注解,@Result注解,@One注解,@Many注解组合完成复杂关系的配置
一对一查询(使用注解配置Mapper)
测试代码:
一对多查询(使用注解配置Mapper)
测试代码;
多对多查询(使用注解配置mapper)
测试代码:
关于“Java中Mybatis框架多表操作与注解开发的示例分析”这篇文章就分享到这里了,希望以上内容可以对大家有一定的帮助,使各位可以学到更多知识,如果觉得文章不错,请把它分享出去让更多的人看到。