mybatis实战教程二:多对一关联查询(一对多)
多对一关联查询
一、数据库关系。article表和user表示多对一的关系
CREATE TABLE `article` ( `id` int(11) NOT NULL AUTO_INCREMENT, `userid` int(11) NOT NULL, `title` varchar(100) NOT NULL, `content` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `userName` varchar(50) DEFAULT NULL, `gender` int(11) DEFAULT NULL, `hobby` varchar(255) DEFAULT NULL, `member` int(11) DEFAULT NULL, `userAge` int(11) DEFAULT NULL, `userAddress` varchar(200) DEFAULT NULL, `reg_time` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
二、创建跟表对应关系的model
public class Article { private int id; private User user; private String title; private String content; setter()... getter()... }
public class User { private int id; private String userName; private String userAge; private Gender gender; private Hobby hobby; private Member member; private String userAddress; private Date regTime; setters()&getters() }
三、新建对应的mapper映射器组合
public interface ArticleMapper { public List<Article> getUserArticles(); }
组合一:
<?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.yihaomen.mybatis.dao.ArticleMapper"> <!-- User 联合文章进行查询 方法之一的配置 (多对一的方式) --> <resultMap id="resultUserArticleList" type="Article"> <!--注意这个column=aid一定要跟下面的<association>中的<id property='id' column='id' >区分开来--> <id property="id" column="aid" /> <result property="title" column="title" /> <result property="content" column="content" /> <association property="user" javaType="User"> <id property="id" column="id" /> <result property="userName" column="userName" /> <result property="userAddress" column="userAddress" />
<result property="gender" column="gender" typeHandler="org.apache.ibatis.type.EnumOrdinalTypeHandler"/>
</association>
</resultMap> <select id="getUserArticles" resultMap="resultUserArticleList"> <!--如果两个表的id字段有关联,那必须都查询出来--> SELECT u.id,u.userName,u.gender,u.userAddress,a.id aid,a.title,a.content FROM `user` u,`article` a WHERE u.id=a.userid </select> </mapper>
组合二:
<?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.yihaomen.mybatis.dao.ArticleMapper"> <resultMap id="resultUserArticleList2" type="Article"> <id property="id" column="aid" /> <result property="title" column="title" /> <result property="content" column="content" /> <association property="user" javaType="User" resultMap="resultListUser2" /> </resultMap> <resultMap id="resultListUser2" type="User"> <id column="id" property="id" /> <result column="userName" property="userName" /> <result column="userAge" property="userAge" /> <result column="gender" property="gender" typeHandler="org.apache.ibatis.type.EnumOrdinalTypeHandler"/> <result column="userAddress" property="userAddress" /> </resultMap> <select id="getUserArticles" resultMap="resultUserArticleList2"> <!--如果两个表的id字段有关联,那必须都查询出来--> SELECT u.id,u.userName,u.gender,u.userAddress,a.id aid,a.title,a.content FROM `user` u,`article` a WHERE u.id=a.userid </select> </mapper>
组合三:
<?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.yihaomen.mybatis.dao.ArticleMapper"> <resultMap id="resultUserArticleList3" type="Article"> <id property="id" column="aid" /> <result property="title" column="title" /> <result property="content" column="content" /> <association property="user" column="userid" select="com.yihaomen.mybatis.dao.UserMapper.selectUserByID" /> </resultMap> <select id="getUserArticles" resultMap="resultUserArticleList3"> <!--如果两个表的id字段有关联,那必须都查询出来-->
<!--这个a.userid一定要查出来,否则上面的column="userid"就没有值,在查询的时候就会报空指针异常-->
SELECT u.id,u.userName,u.gender,u.userAddress,a.id aid,a.userid,a.title,a.content FROM `user` u,`article` a WHERE u.id=a.userid </select> </mapper>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.yihaomen.mybatis.dao.UserMapper"> <resultMap id="resultListUser" type="User"> <id column="id" property="id" /> <result column="userName" property="userName" /> <result column="userAge" property="userAge" /> <result column="gender" property="gender" typeHandler="org.apache.ibatis.type.EnumOrdinalTypeHandler"/> <result column="hobby" property="hobby" typeHandler="org.apache.ibatis.type.EnumTypeHandler"/> <result column="member" property="member" typeHandler="com.yihaomen.mybatis.type.MyMemberTypeHandler"/> <result column="userAddress" property="userAddress" /> <result column="reg_time" property="regTime" javaType="java.util.Date" jdbcType="VARCHAR" typeHandler="com.yihaomen.mybatis.type.MyDateTypeHandler"/> </resultMap> <select id="selectUserByID" parameterType="int" resultType="User" > select * from `user` where id = #{id} </select> </mapper>
四、在configuration.xml 配置别名和注册映射器:
<typeAliases> <typeAlias alias="User" type="com.yihaomen.mybatis.model.User"/> <typeAlias alias="Article" type="com.yihaomen.mybatis.model.Article" /> </typeAliases> <mappers> <mapper resource="com/yihaomen/mybatis/model/User.xml"/> <mapper resource="com/yihaomen/mybatis/model/Article.xml"/> </mappers>
五、测试
public class ArticleTest extends BaseTest{ public static void main(String[] args) { selectArticlesWithUser(); } public static void selectArticlesWithUser() { SqlSessionFactory sessionFactory =getSession(); SqlSession session = sessionFactory.openSession(); ArticleMapper mapper = session.getMapper(ArticleMapper.class); List<Article> list = mapper.getUserArticles(); for(Article a : list) { System.out.println(a.getTitle() + "," + a.getUser().getUserName() + "," + a.getUser().getGender()); } } }
一对多关联查询
一、数据库关系。user表和article表示一对多的关系
二、创建跟表对应关系的model
public class User { private int id; private String userName; private String userAge; private Gender gender; private Hobby hobby; private Member member; private String userAddress; private Date regTime; private List<Article> articleList; setters()&getters() }
三、新建对应的mapper映射器组合
@Repository public interface UserMapper { public List<User> getUserArticlesById(Long id); }
组合一:
<?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.yihaomen.mybatis.dao.UserMapper"> <resultMap id="resultUserArticleList3" type="User"> <id column="id" property="id" /> <result column="userName" property="userName" /> <result column="userAge" property="userAge" /> <result column="gender" property="gender" typeHandler="org.apache.ibatis.type.EnumOrdinalTypeHandler"/> <result column="hobby" property="hobby" typeHandler="org.apache.ibatis.type.EnumTypeHandler"/> <result column="member" property="member" typeHandler="com.yihaomen.mybatis.type.MyMemberTypeHandler"/> <result column="userAddress" property="userAddress" /> <result column="reg_time" property="regTime" javaType="java.util.Date" jdbcType="VARCHAR" typeHandler="com.yihaomen.mybatis.type.MyDateTypeHandler"/> <!--一对多--> <collection property="articleList" ofType="Article"> <!--这个column=aid 一定要跟上面id的区分开来,否则会查询不出来--> <id property="id" column="aid"></id> <result column="title" property="title" /> <result column="content" property="content"/> </collection> </resultMap> <select id="getUserArticlesById" resultMap="resultUserArticleList3" parameterType="Long"> SELECT u.id, u.userName, u.userAddress, u.gender, a.id aid,a.userid, a.title, a.content FROM `user` u,`article` a WHERE u.id=a.userid and u.id=#{id} </select> </mapper>
组合二:这种方法适合SQL类似 SELECT * FROM table_name,就是没有where条件的情况
<resultMap id="resultUserArticleList4" type="User"> <id column="id" property="id" /> <result column="userName" property="userName" /> <result column="userAge" property="userAge" /> <result column="gender" property="gender" typeHandler="org.apache.ibatis.type.EnumOrdinalTypeHandler"/> <result column="hobby" property="hobby" typeHandler="org.apache.ibatis.type.EnumTypeHandler"/> <result column="member" property="member" typeHandler="com.yihaomen.mybatis.type.MyMemberTypeHandler"/> <result column="userAddress" property="userAddress" /> <result column="reg_time" property="regTime" javaType="java.util.Date" jdbcType="VARCHAR" typeHandler="com.yihaomen.mybatis.type.MyDateTypeHandler"/> <!--一对多--> <collection property="articleList" column="userid" select="com.yihaomen.mybatis.dao.ArticleMapper.getArticlesByUserId"> </collection> </resultMap> <select id="getUserArticlesById" resultMap="resultUserArticleList4" parameterType="Long"> SELECT u.id, u.userName, u.userAddress, u.gender, a.id aid,a.userid, a.title, a.content FROM `user` u,`article` a WHERE u.id=a.userid and u.id=#{id} </select>
Article.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.yihaomen.mybatis.dao.ArticleMapper"> <resultMap id="resultArticles" type="Article"> <id column="id" property="id" /> <result column="title" property="title" /> <result column="content" property="content" /> </resultMap> <select id="getArticlesByUserId" parameterType="Integer" resultMap="resultArticles"> SELECT id, title, content FROM article a WHERE a.userid=#{userId} </select> </mapper>
四、测试
import com.yihaomen.mybatis.dao.UserMapper; import com.yihaomen.mybatis.model.Article; import com.yihaomen.mybatis.model.User; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import java.io.IOException; import java.io.Reader; import java.util.List; public class TestGetUser { private static SqlSessionFactory sqlSessionFactory; private static Reader reader; static { try { reader = Resources.getResourceAsReader("configuration.xml"); sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader); } catch (IOException e) { e.printStackTrace(); } } public static SqlSessionFactory getSession() { return sqlSessionFactory; } public static void getUserArticlesById(Long id) { SqlSession session = sqlSessionFactory.openSession(); UserMapper userMapper = session.getMapper(UserMapper.class); List<User> users = userMapper.getUserArticlesById(id); for(User u : users) { System.out.println("------------------"); System.out.println(u.getUserName()); for(Article a : u.getArticleList()) { System.out.println(a.getTitle() + "," + a.getContent()); } } } public static void main(String[] args) { getUserArticlesById(11L); } }
https://gitee.com/huayicompany/springmvc-mybatis
参考:
[1] 《mybati实战教程》
[2] CSDN博客,http://www.cnblogs.com/hq233/p/6752335.html