Implementation of MyBatis Association: One-to-One

Posted by davestewart on Tue, 07 Jan 2020 18:25:11 +0100

There are two entities: user and membership card. A user can only process one membership card, that is, one-to-one.

 

 

user_tb: The primary key card_no of card_tb is introduced as the foreign key.

 

 

card_tb:

 

 

 

 

Mode 1: Use extension classes to implement one-to-one

(1) Create a new User class under the pojo package:

package com.chy.pojo;

public class User {
    private Integer id;  //Primary key
    private String name;  //Full name
    private String tel;  //Cell-phone number
    private String address;  //address

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getTel() {
        return tel;
    }

    public void setTel(String tel) {
        this.tel = tel;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", tel='" + tel + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

 

 

(2) Create a new User extension class UserExt under the pojo package, inherit the User, and add the properties of Card to provide corresponding getter and setter methods.

package com.chy.pojo;

public class UserExt extends User {
    private Integer no;
    private float money;

    public Integer getNo() {
        return no;
    }

    public void setNo(Integer no) {
        this.no = no;
    }

    public float getMoney() {
        return money;
    }

    public void setMoney(float money) {
        this.money = money;
    }

    @Override
    public String toString() {
        return super.toString()+",Card{" +
                "no=" + no +
                ", money=" + money +
                '}';
    }
}

First alt+insert inserts toString(), then stitches the User's toString(), and then modifies it to ok.

 

 

(3) Write UserMapper interface, UserMapper.xml

package com.chy.mapper;

import com.chy.pojo.UserExt;

public interface UserMapper {
    public UserExt queryUserExtById(Integer 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.chy.mapper.UserMapper">
    <select id="queryUserExtById" parameterType="integer" resultType="userext">
        SELECT user_tb.*,card_tb.* FROM user_tb,card_tb WHERE user_tb.id=#{id} AND user_tb.card_no=card_tb.no
     </select>
</mapper>

 

 

(4) Use

package com.chy.utils;

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.InputStream;

public class MyBatisUtils {
    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession();
    }
}

 

     SqlSession sqlSession = MyBatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        UserExt userExt = mapper.queryUserExtById(1);
        System.out.println(userExt);
        sqlSession.close();

 

 

summary

  • One-to-one using extended classes, mainly depending on the sql statements in the mapping file, the sql statements written are often complex
  • Because to add another attribute of "one", if there are more attributes of another "one", it would be more cumbersome to add, that is, less suitable attributes.
  • Does not reflect the relationship between entities (one POJO contains another pojo)

 

 

 

 

Mode 2: One-to-one using nested queries

(1) To write pojo classes for both "one", one needs to be associated with another "one"

package com.chy.pojo;

public class User {
    private Integer id;  //Primary key
    private String name;  //Full name
    private String tel;  //Cell-phone number
    private String address;  //address
    private Card card;  //Another One associated with it

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getTel() {
        return tel;
    }

    public void setTel(String tel) {
        this.tel = tel;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public Card getCard() {
        return card;
    }

    public void setCard(Card card) {
        this.card = card;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", tel='" + tel + '\'' +
                ", address='" + address + '\'' +
                ", card=" + card +
                '}';
    }
}

 

package com.chy.pojo;

public class Card {
    private Integer no;  //Membership Card Number
    private Float money;  //balance

    public Integer getNo() {
        return no;
    }

    public void setNo(Integer no) {
        this.no = no;
    }

    public Float getMoney() {
        return money;
    }

    public void setMoney(Float money) {
        this.money = money;
    }

    @Override
    public String toString() {
        return "Card{" +
                "no=" + no +
                ", money=" + money +
                '}';
    }
}

 

 

(2) Write Mapper interface, xml Mapping file for both pojo classes

public interface CardMapper {
    public Card queryCardByUserId(Integer no);
}
<?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.chy.mapper.CardMapper">
    <select id="queryCardByNo" parameterType="integer" resultType="card">
        SELECT * FROM card_tb WHERE no=#{no}
     </select>
</mapper>

 

 

package com.chy.mapper;

import com.chy.pojo.User;

public interface UserMapper {
    public User queryUserById(Integer 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.chy.mapper.UserMapper">
    <select id="queryUserById" parameterType="integer" resultMap="userResultWithCard">
        SELECT * FROM user_tb WHERE id=#{id}
     </select>
    <resultMap id="userResultWithCard" type="user">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="tel" column="tel"/>
        <result property="address" column="address"/>
        <association property="card" column="card_no" javaType="card" select="com.chy.mapper.CardMapper.queryCardByNo"/>
    </resultMap>
</mapper>

The sql statements all query the data table corresponding to the current pojo class, but UserMapper specifies a nested query using the <association>element of <resultMap>

  • Properrty property specifies the name of another property representing "one" in the current pojo class
  • The column attribute specifies a column (foreign key) in the current data table that is associated with another One
  • The javaType property specifies another "one" data type associated with the current pojo class.
  • The select attribute specifies which sql element association (namespace+id) of the other "one" to use, and when the current <select>query is executed, the <select>of the other "one" is automatically nested for query.

 

 

(3) Use

package com.chy.utils;

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.InputStream;

public class MyBatisUtils {
    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession();
    }
}

 

package com.chy.test;

import com.chy.mapper.UserMapper;
import com.chy.pojo.User;
import com.chy.utils.MyBatisUtils;
import org.apache.ibatis.session.*;
import java.io.IOException;

public class Test {
    public static void main(String[] args) {
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = mapper.queryUserById(1);
        System.out.println(user);
        sqlSession.close();
    }
}

 

Summary:

The use of nested queries reflects the relationship between entities, but one query triggers another query associated with it, and another query triggers a chain reaction if it has a query associated with it. This greatly reduces query efficiency and database performance and is not recommended.

 

 

 

 

Mode 3: One-to-one using nested results

The other steps, like nested queries, differ in two ways:

(1) UserMapper.xml file

<?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.chy.mapper.UserMapper">
    <select id="queryUserById" parameterType="integer" resultMap="userResultWithCard">
        SELECT user_tb.*,card_tb.* FROM user_tb,card_tb WHERE id=#{id}
     </select>
    <resultMap id="userResultWithCard" type="user">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="tel" column="tel"/>
        <result property="address" column="address"/>
        <association property="card" javaType="card">
            <id property="no" column="card_no"/>
            <result property="money" column="money"/>
        </association>
    </resultMap>
</mapper>

The sql statement changed because it was a query, so all the data needed was found.

  • The property property property specifies the name of the property (member variable name) that represents another "one"
  • javaType specifies another "one" data type
  • The <id>element is the key, property specifies which attribute of another "one" pojo class is the primary key, and column specifies which column is associated with another "one" (foreign key).
  • <result>Configure another "one" mapping

 

 

(2) No CardMapper interface, CardMapper.xml, is required because <select>of the nested CardMapper is not required for querying.

 

summary

Nested results reflect the relationship between entities, write less code, and configure easily.Recommended.

Topics: Java Mybatis xml Apache Session

Hot Topics

©2024 Programmer Think