Mybatis入门（二）一对一、一对多、多对一、多对多（详解）

配置SQL输出到日志

1.导入架包如下

在mybatis的配置文件中增如下代码：

<configuration>
<!-- 显示SQL语句信息 -->
	<settings>
		<setting name="logImpl" value="STDOUT_LOGGING" />
	</settings>

.....
....
</configuration>

配置成功后，每次查询都会有响应的SQL输出  如下

一对一

BookMapper.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.baidu.bookstroe.mapper.BookMapper">
  
  <!--查询所有  -->
  <select id="getBooks" resultMap="getBookMap">
  select book.bookid,bookname,bookprice,bookimg,rcontent from book,review where book.bookid=review.bookid
  </select>
  
   <resultMap type="Book" id="getBookMap">
        <id property="bookid" column="bookid"/>   
        <result property="bookname" column="bookname"/>
        <result property="bookprice" column="bookprice"/>
        <result property="bookimg" column="bookimg"/>
        <association property="review" javaType="Review">   
            <id property="rid" column="rid"/>
            <result property="rcontent" column="rcontent"/>
        </association>
    </resultMap>

</mapper>

关于一对一和一对多中  javaType 和ofTyep的区别： 因为一对一种的javaType  就是原有的JAVABean所有直接用javaType,所以直接用javaType,，而一对多中需要引用resultMap 所以使用ofType

其次一对一必须要查询主键，因为通过主键关联的，不然只会出来一条结果

 

SQL查询，一对多： 一本书，对应多条评论，数据库表结构如下

JAVA src 结构

mybatis-config.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>
<!-- 显示SQL语句信息 -->
	<settings>
		<setting name="logImpl" value="STDOUT_LOGGING" />
	</settings>
	<!-- 给javabean取别名alias为Userinfo -->
	<typeAliases>
		<typeAlias type="com.baidu.bookstroe.domain.Book" alias="Book"/>
		<typeAlias type="com.baidu.bookstroe.domain.Review" alias="Review"/>
	</typeAliases>
	
	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="com.mysql.jdbc.Driver" />
				<property name="url"
					value="jdbc:mysql://localhost:3306/bookdb?useUnicode=true&amp;characterEncoding=UTF-8" />
				<property name="username" value="root" />
				<property name="password" value="123" />
			</dataSource>
		</environment>
	</environments>

	<!-- 载入实现crud的类BookMapper.xml -->
	<mappers>
		<mapper resource="com/baidu/bookstroe/mapper/BookMapper.xml" />
		<mapper resource="com/baidu/bookstroe/mapper/ReviewMapper.xml"/>
	</mappers>
</configuration>

Book类

public class Book implements Serializable{

	private static final long serialVersionUID = 1L;
	private String bookname;
	private String bookid;
	private double bookprice;
	private String bookimg;
	private List<Review> reviews;//多条评论，List集合
    //get 和 set方法省略

	
}

接口  BookMapper

public interface BookMapper {
        //查询一本书的所有评论
	public Book getBook(String bookid);
}

BookMapper.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.baidu.bookstroe.mapper.BookMapper">

<!--定义一个bookMap  -->
  <resultMap type="Book" id="bookMap">
  <!--id  用来区分主键  column数据库中的列  property JAVA中Book的属性，与columnd对应,javaType:java数据类型,jdbcType:Sql数据类型  -->
  <id column="bookid" property="bookid" javaType="String"
   jdbcType="VARCHAR" />
   <result column="bookname" property="bookname" javaType="String"
   jdbcType="VARCHAR"/>
   <result column="bookimg" property="bookimg" javaType="String"
   jdbcType="VARCHAR"/>
   <!-- 一对多使用：collection     多对一使用：association  resultMap引用reviewMap集合-->
   <collection property="reviews" resultMap="reviewMap"></collection>
   <!--或者不用下面的直接这样定义   
    <collection property="reviews" ofType="Review">
	  		<id column="rid" property="rid" javaType="int" jdbcType="INTEGER"/>
			<result column="rcontent" property="rcontent" javaType="String" jdbcType="VARCHAR"/>	
	  	</collection>  -->
  </resultMap>
  
  <!--定义一个reviewMap  -->
  <resultMap type="Review" id="reviewMap">
  <id column="rid" property="rid" javaType="int" jdbcType="INTEGER"/>
  <result column="rcontent" property="rcontent" javaType="String" jdbcType="LONGVARCHAR"/>
  </resultMap>

 <!--查询ID为1的书的所有评论  -->
  <select id="getBook" resultMap="bookMap">
  select bookinfo.bookid,bookname,bookimg,rcontent from bookinfo,review 
  where bookinfo.bookid = review.bookid and bookinfo.bookid=#{bookid}
  </select>

测试类

public class test {
	public static void main(String[] args) throws IOException {
		//读取实现连接池与添加方法的配置文件mybatis-config.xml
				InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
				
				//从这个配置里面构建一个工厂对象
				SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
				
				//从这个构建好的工厂里面生成出sqlSession(封装了JDBC)对象
				SqlSession session = sqlSessionFactory.openSession();
                //调用编写好的添加方法(直接从mybatis里面取出对象)
				//就是NEW实例化的意思
				BookMapper bookMapper = (BookMapper)session.getMapper(BookMapper.class);      
				for (Review review : bookMapper.getBooks().getReviews("1")) {
				System.out.println("评论为" + review.getRcontent());
				}

多对一 （本质是一对一）  多条评论 对应一本书

Review类

public class Review implements Serializable {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	private int rid;
	private String rcontent;
	private String bookid;
	private Book book;
//get和set方法省略
    }

接口 ReviewMapper

public interface ReviewMapper {
	//通过评论查询这个条评论是那本书的
	public Review getReviews(int rid);
}

ReviewMapper.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.baidu.bookstroe.mapper.ReviewMapper">

<!--  定义一个Book集合-->
<resultMap type="Book" id="bookMap">
<id column="bookid" property="bookid" javaType="String" jdbcType="VARCHAR"/>
<result column="bookname" property="bookname" javaType="String" jdbcType="VARCHAR"/>
</resultMap>

<!--定义一个Review  -->
<resultMap type="Review" id="reviewMap">
<id column="rid" property="rid" javaType="int" jdbcType="INTEGER"/>
<result column="rcontent" property="rcontent" javaType="String" jdbcType="LONGVARCHAR"/>
<!--一对多用association   -->
<association property="book" resultMap="bookMap"></association>
</resultMap>

  <select id="getReviews" parameterType="String" resultMap="reviewMap">
  <!-- select * from review -->
select bookinfo.bookid,bookname,bookimg,rcontent from bookinfo,review 
where bookinfo.bookid = review.bookid and rid=#{rid}
  </select>
</mapper>

测试类

package com.baidu.bookstroe.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 com.baidu.bookstroe.domain.Book;
import com.baidu.bookstroe.domain.Review;
import com.baidu.bookstroe.mapper.BookMapper;
import com.baidu.bookstroe.mapper.ReviewMapper;

public class test {
	public static void main(String[] args) throws IOException {
		//读取实现连接池与添加方法的配置文件mybatis-config.xml
				InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");				
				//从这个配置里面构建一个工厂对象
				SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);				
				//从这个构建好的工厂里面生成出sqlSession(封装了JDBC)对象
				SqlSession session = sqlSessionFactory.openSession();
				
				//调用编写好的添加方法(直接从mybatis里面取出对象)
				//就是NEW实例化的意思
				BookMapper bookMapper = (BookMapper)session.getMapper(BookMapper.class);
				ReviewMapper reviewMapper = session.getMapper(ReviewMapper.class);
				
                System.out.println("该评论的书籍为：" + reviewMapper.getReview(1).getBook().getBookname());
                for (Review review : bookMapper.getReview("1").getReviews()) {
					System.out.println("该书的评论为：" + review.getRcontent());
				}
				
	}

}

 

附件：JdbcType类型和Java类型的对应关系（图片来自转载）

SQL Server 类型	JDBC 类型 (java.sql.Types)	Java 语言类型
bigint	BIGINT	long
timestamp binary	BINARY	byte[]
bit	BIT	boolean
char	CHAR	String
decimal money smallmoney	DECIMAL	java.math.BigDecimal
float	DOUBLE	double
int	INTEGER	int
image varbinary(max)	LONGVARBINARY	byte[]
varchar(max) text	LONGVARCHAR	String
nchar	CHAR NCHAR (Java SE 6.0)	String
nvarchar	VARCHAR NVARCHAR (Java SE 6.0)	String
nvarchar(max) ntext	LONGVARCHAR LONGNVARCHAR (Java SE 6.0)	String
numeric	NUMERIC	java.math.BigDecimal
real	REAL	float
smallint	SMALLINT	short
datetime smalldatetime	TIMESTAMP	java.sql.Timestamp
varbinary udt	VARBINARY	byte[]
varchar	VARCHAR	String
tinyint	TINYINT	short
uniqueidentifier	CHAR	String
xml	LONGVARCHAR SQLXML (Java SE 6.0)	String SQLXML
time	TIME (1)	java.sql.Time (1)
date	DATE	java.sql.Date
datetime2	TIMESTAMP	java.sql.Timestamp
datetimeoffset (2)	microsoft.sql.Types.DATETIMEOFFSET	microsoft.sql.DateTimeOffset