Note: The code is hosted on GitHub at: https://github.com/Damaer/Mybatis-Learning, the project is mybatis – 11 – a one2one, need to invite, need to configure the maven and mysql environment (SQL statements test under the condition of the resource. In SQL). You can light a little star if you think it helps.

Docsify document address at: https://damaer.github.io/Mybatis-Learning/#/

The so-called one-to-one multi-table query, for example: we have many countries, each country has only one leader (assume), we need to query the country information according to the ID, with the leader’s information.

Create table

When designing the table, we need to consider that since it is one-to-many, we need to use a field in the country table for leader information.

Create databaseCREATE DATABASE `test` DEFAULT CHARACTER SET utf8 COLLATEutf8_general_ci; Create tableCREATE TABLE `test`.`country` ( `cid` INT(10) NOT NULL AUTO_INCREMENT ,`cname` VARCHAR(20) NOT NULL ,`mid` INT(10) NOT NULL.PRIMARY KEY(`cid`)) ENGINE = MyISAM;
CREATE TABLE `test`.`minister` ( `mid` INT(10) NOT NULL AUTO_INCREMENT ,`mname` VARCHAR(20) NOT NULL.PRIMARY KEY(`mid`)) ENGINE =MyISAM; Initialize the data tableINSERT INTO `country` (`cid`, `cname`, `mid`) VALUES ('1'.'aaa'.'1'); 
INSERT INTO `country` (`cid`, `cname`, `mid`) VALUES ('2'.'bbb'.'2');
INSERT INTO `country` (`cid`, `cname`, `mid`) VALUES ('3'.'ccc'.'3'); 

INSERT INTO `minister` (`mid`, `mname`) VALUES ('1'.'sam'); 
INSERT INTO `minister` (`mid`, `mname`) VALUES ('2'.'jane'); 
INSERT INTO `minister` (`mid`, `mname`) VALUES ('3'.'jone'); 
Copy the code

The data table is as follows:

Entity class

Country entity class:

public class Country {
	private Integer cid;
	private String cname;
	private Minister minister;

	public Integer getCid(a) {
		return cid;
	}

	public void setCid(Integer cid) {
		this.cid = cid;
	}

	public String getCname(a) {
		return cname;
	}

	public void setCname(String cname) {
		this.cname = cname;
	}

	public Minister getMinister(a) {
		return minister;
	}

	public void setMinister(Minister minister) {
		this.minister = minister;
	}

	@Override
	public String toString(a) {
		return "Country{" +
						"cid=" + cid +
						", cname='" + cname + '\' ' +
						", minister=" + minister +
						'} '; }}Copy the code

The entity class corresponding to Minister:

public class Minister {
	private Integer mid;
	private String mname;
	@Override
	public String toString(a) {
		return "Minister [mid=" + mid + ", mname=" + mname + "]";
	}
	public Integer getMid(a) {
		return mid;
	}
	public void setMid(Integer mid) {
		this.mid = mid;
	}
	public String getMname(a) {
		return mname;
	}
	public void setMname(String mname) {
		this.mname = mname; }}Copy the code

MybatisUtils utility class to get a singleton sqlSession object:

public class MyBatisUtils {
    static private SqlSessionFactory sqlSessionFactory;

    static public SqlSession getSqlSession(a) {
        InputStream is;
        try {
            is = Resources.getResourceAsStream("mybatis.xml");
            if (sqlSessionFactory == null) {
                sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
            }
            return sqlSessionFactory.openSession();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return null; }}Copy the code

To register mapper files in mybatis. XML:

<mappers>
    <mapper resource="mapper/mapper.xml"/>
</mappers>
Copy the code

Our two interfaces (corresponding to two SQL statements in the Mapper file) :

public interface ICountryDao {
	Country selectCountryById(int cid);
	Country selectCountryById2(int cid);
}
Copy the code

Mapper files, there are two ways to write, one is nested results, one is nested query.

  • Nested results is to use an SQL query to complete the query, you need to use
    in the self-defined resultMap to organize the result attributes.

  • Nested queries also have an
    tag, but the outermost < SELECT > tag does not find all the required items. Instead, the
    tag specifies the ID of the SQL statement to be associated with the query. This is lazy loading:


      
<! DOCTYPEmapper 
PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="dao.ICountryDao">
<! ResultMap can solve the problem of different fields and attributes.
	<! We will use it more in the future because we can use lazy loading.
	<! -- Nested query -->
	<select id="selectMinisterByCountry" resultType="Minister">
	select mid,mname from minister where mid=#{ooo}
	</select>
	<resultMap type="Country" id="countryMapper">
		<id column="cid" property="cid"/>
		<result column="cname" property="cname"/>
		<! -- There is a member variable in country called ministers, whose generic is Minister -->
		<association property="minister"
					select="selectMinisterByCountry"
					column="mid">
		</association>
	</resultMap>
	<select id="selectCountryById" resultMap="countryMapper">
		select cid,cname,mid
		from country 
		where 
		cid=#{cid}
	</select>

    <! -- Nested result -->
    <select id="selectCountryById2" resultMap="countryMapper2">
        select * from country c,minister m where c.mid = m.mid and c.cid= #{cid}
    </select>
    <resultMap id="countryMapper2" type="Country">
        <id column="cid" property="cid"/>
        <result column="cname" property="cname"/>
        <association property="minister" javaType="Minister">
			<id property="mid" column="mid"/>
			<result property="mname" column="mname"/>
		</association>
    </resultMap>
</mapper>
Copy the code

Unit tests:

public class MyTest {
  private ICountryDao dao;
  private SqlSession sqlSession;
  @Before
  public void Before(a){
    sqlSession=MyBatisUtils.getSqlSession();
    dao=sqlSession.getMapper(ICountryDao.class);
  }
  @Test
  public void TestselectCountryById(a){
    Country country=dao.selectCountryById(1);
    System.out.println(country);
  }

  @Test
  public void TestselectCountryById2(a){
    Country country=dao.selectCountryById2(1);
    System.out.println(country);
  }


  @After
  public void after(a){
    if(sqlSession! =null){ sqlSession.close(); }}}Copy the code

Results:

[service] the 15:23:38 2018-07-12, 971 - the dao. ICountryDao. [the main] the DEBUG selectCountryById - 537 dao.ICountryDao.selectCountryById - ==> Preparing: select cid,cname,mid from country where cid=? [service] the 15:23:39 2018-07-12, 004 - the dao. ICountryDao. [the main] the DEBUG selectCountryById - 570 dao.ICountryDao.selectCountryById - ==> Parameters: 1 (Integer) [service] 15:23:39 2018-07-12, 037 - the dao. ICountryDao. [the main] the DEBUG selectMinisterByCountry - 603 dao.ICountryDao.selectMinisterByCountry - ====> Preparing: select mid,mname from minister where mid=? [service] the 15:23:39 2018-07-12, 037 - the dao. ICountryDao. [the main] the DEBUG selectMinisterByCountry - 603 dao.ICountryDao.selectMinisterByCountry - ====> Parameters: 1 (Integer) [service] 15:23:39 2018-07-12, 040 - the dao. ICountryDao. [the main] the DEBUG selectMinisterByCountry - 606 dao.ICountryDao.selectMinisterByCountry - <==== Total: 1 [service] 15:23:39 2018-07-12, 041 - the dao. ICountryDao. [the main] the DEBUG selectCountryById - 607 dao.ICountryDao.selectCountryById - <== Total: 1 Country{cid=1, cname='aaa', minister=Minister [mid=1, mname=sam]}Copy the code

[Author profile] : Qin Huai, public number [Qin Huai Grocery store] author, the road of technology is not at that time, mountain high water long, even slow, chi and not stop. The world wants everything to be fast and faster, but I hope I can take every step, write every article, and look forward to communicating with you.