“This is the 24th day of my participation in the First Challenge 2022. For details: First Challenge 2022”
First, joint query
ResultMap Indicates a user-defined encapsulation rule
When entity class attributes and database fields are transformed by hump or alias in SQL statements, if the attributes and fields are inconsistent, the hump naming method cannot be used, and the alias method is cumbersome. In this case, you can use resultMap to customize encapsulation rules
Create a table T_cat
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for t_cat
-- ----------------------------
DROP TABLE IF EXISTS `t_cat`;
CREATE TABLE `t_cat` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`cName` varchar(255) DEFAULT NULL,
`cAge` int(2) DEFAULT NULL,
`cgender` int(1) DEFAULT NULL.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of t_cat
-- ----------------------------
BEGIN;
INSERT INTO `t_cat` VALUES (1.'pipi'.2.0);
INSERT INTO `t_cat` VALUES (2.' '.1.0);
INSERT INTO `t_cat` VALUES (3.'pig'.1.0);
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
Copy the codeAdd entity class Cat to entity package
@Data
public class Cat {
private Integer id;
private String name;
private Integer gender;
private Integer age;
}
Copy the codeAdd CatDao interface to DAI package
public interface CatDao {
Cat getCatById(Integer id);
}
Copy the codeAdd SQL mapping file cat.xml to mappers directory
<! DOCTYPEmapper
PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.citi.dao.CatDao">
<! -- No need to write parameter type -->
<select id="getCatById" resultType="com.citi.entity.Cat">
select * from t_cat where id = #{id}
</select>
</mapper>
Copy the codeRegister cat.xml with the MyBatis global configuration file
<mappers>
<mapper resource="mappers/employee.xml"/>
<mapper resource="mappers/cat.xml"/>
</mappers>
Copy the codeGenerate the CatDaoTest test class
public class CatDaoTest {
SqlSessionFactory sqlSessionFactory = null;
SqlSession openSession = null;
@Before
public void setUp(a) throws Exception {
// create an SqlSessionFactory from the global configuration file
//SqlSessionFactory: is the SqlSession factory, responsible for creating the SqlSession object;
//SqlSession: SQL session (representing a session with the database);
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
openSession = sqlSessionFactory.openSession();
}
@Test
public void getCatById(a) {
CatDao catDao = openSession.getMapper(CatDao.class);
Cat cat = catDao.getCatById(1); System.out.println(cat); }}Copy the codeExecute test method
All attributes except ID are null. MyBatis can automatically encapsulate the result set by default, but the premise is that the database fields and attribute values correspond one by one (case insensitive). The reason for this situation is that the database fields and entity class attributes are inconsistent, leading to the failure of assigning some attributes of the object. If the only difference between database field and entity class attribute names is the presence or absence of underscores, this can be resolved by turning on the hump nomenclature in the MyBatis global configuration file. If they are completely inconsistent, there are two solutions.
The first solution is to alias the query fields in the query SQL to match the entity class attribute names. Modifying a Mapping File
<select id="getCatById" resultType="com.citi.entity.Cat">
select id, cname name, cage age, cgender gender from t_cat where id = #{id}
</select>
Copy the codeRun the test again
The second method is to use the resultMap tag to customize the result set to achieve the mapping between database fields and entity class attributes. ResultType and resultMap are mutually exclusive, so if you use resultMap, do not use resultType
<select id="getCatById" resultMap="mycat">
select * from t_cat where id = #{id}
</select>
<resultMap id="mycat" type="com.citi.entity.Cat">
<! -- Define primary key mapping rules -->
<id property="id" column="id"></id>
<! -- Normal column mapping rule -->
<result property="gender" column="cgender"></result>
<result property="age" column="cage"></result>
<result property="name" column="cname"></result>
</resultMap>
Copy the code- Type: Specifies the name of the entity class for which to customize the encapsulation rule
- Id: unique identifier that is referenced by others
- Column: specifies the database field
- Property: Refers to the attributes of the entity class
- The result: tag is used to define the mapping rules for non-primary key columns
Perform the test
The joint query
Since entity classes and database tables are one-to-one correspondency, no entity class can be one-to-one corresponded with the results of the joint query, so resultMap can be used to encapsulate the results of the joint query
One-to-one query
Add two entity classes Key and Lock in the Entity package, and they are one-to-one
@Data
public class Key {
private Integer id;
private String keyName;
// Open the lock
private Lock lock;
}
Copy the code@Data
public class Lock {
private Integer id;
private String lockName;
}
Copy the codeCreate a database table, establish an association with a foreign key, and insert two pieces of data
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for t_key
-- ----------------------------
DROP TABLE IF EXISTS `t_key`;
CREATE TABLE `t_key` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`keyname` varchar(255) DEFAULT NULL,
`lockid` int(11) DEFAULT NULL.PRIMARY KEY (`id`),
KEY `fk_key_lock` (`lockid`),
CONSTRAINT `fk_key_lock` FOREIGN KEY (`lockid`) REFERENCES `t_lock` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for t_lock
-- ----------------------------
DROP TABLE IF EXISTS `t_lock`;
CREATE TABLE `t_lock` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`lockName` varchar(255) DEFAULT NULL.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
Copy the codeCreate a new KeyDao interface in the DAO package
public interface KeyDao {
Key getKeyById(Integer id);
}
Copy the codeAdd the key.xml mapping file in mappers
<! DOCTYPEmapper
PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.citi.dao.KeyDao">
<! -- No need to write parameter type -->
<select id="getKeyById" resultMap="mykey">
SELECT k.id,k.keyname,k.lockid, l.id lid, l.lockname
FROM t_key k
LEFT JOIN t_lock l ON k.lockid = l.id
WHERE k.id = #{id}
</select>
<resultMap id="mykey" type="com.citi.entity.Key">
<id property="id" column="id"></id>
<result property="keyName" column="keyname"></result>
<result property="lock.id" column="lid"></result>
<result property="lock.lockName" column="lockName"></result>
</resultMap>
</mapper>
Copy the codeLeft Join is recommended for SQL query statements. ResultMap must be used for multi-table joint query and encapsulation rules must be customized. The cascading properties are used here to get the attributes of the lock contained in the key
Register key.xml in the global configuration file
<mappers>
<mapper resource="mappers/employee.xml"/>
<mapper resource="mappers/cat.xml"/>
<mapper resource="mappers/key.xml"/>
</mappers>
Copy the codeAdd the test class KeyDaoTest
public class KeyDaoTest {
SqlSessionFactory sqlSessionFactory = null;
SqlSession openSession = null;
@Before
public void setUp(a) throws Exception {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
openSession = sqlSessionFactory.openSession();
}
@Test
public void getKeyById(a) {
KeyDao keyDao = openSession.getMapper(KeyDao.class);
Key key = keyDao.getKeyById(1); System.out.println(key); }}Copy the codePerform the test
The above approach uses cascading properties, whereas MyBatis recommends using association properties for custom encapsulation rules
Modify the resultMap
<resultMap id="mykey" type="com.citi.entity.Key">
<id property="id" column="id"></id>
<result property="keyName" column="keyname"></result>
<! --<result property="lock.id" column="lid"></result>-->
<! --<result property="lock.lockName" column="lockName"></result>-->
<association property="lock" javaType="com.citi.entity.Lock">
<id property="id" column="id"></id>
<result property="lockName" column="lockName"></result>
</association>
</resultMap>
Copy the code- Association: Defines an association of a complex type
- JavaType: Specifies the type of the object
Run the test again
One-to-many query
Add data to the database table a lock number 3, there are many keys can open the lock number 3, that is, a lock has many keys, this forms a one-to-many relationship
Modify the Lock entity class to add attributes to the key list
@Data
public class Lock {
private Integer id;
private String lockName;
private List<Key> keyList;
}
Copy the codeThe difference between one-to-one and one-to-many lies in the different angles. One-to-many means that the foreign key is established on the many party, and the many-to-many relationship is established through the middle table
Create a new LockDao in the DAO package
public interface LockDao {
Lock getLockById(Integer id);
}
Copy the codeCreate the SQL mapping file lock. XML
<! DOCTYPEmapper
PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.citi.dao.LockDao">
<select id="getLockById" resultMap="mylock">
SELECT l.id,l.lockName,k.id kid, k.keyname,k.lockid
FROM t_lock l
LEFT JOIN t_key k ON l.id = k.lockid
WHERE l.id=#{id}
</select>
<resultMap id="mylock" type="com.citi.entity.Lock">
<id property="id" column="id"></id>
<result property="lockName" column="lockName"></result>
<collection property="keyList" ofType="com.citi.entity.Key">
<! SQL > select * from 'column' where name = 'column';
<id property="id" column="kid"></id>
<result property="keyName" column="keyName"></result>
</collection>
</resultMap>
</mapper>
Copy the code- Collection: a collection of complex types
- OfType: Specifies the type of the element in the collection
Register lock. XML with the MyBatis global configuration file
<mappers>
<mapper resource="mappers/employee.xml"/>
<mapper resource="mappers/cat.xml"/>
<mapper resource="mappers/key.xml"/>
<mapper resource="mappers/lock.xml"/>
</mappers>
Copy the codeCreating a Test Class
public class LockDaoTest {
SqlSessionFactory sqlSessionFactory = null;
SqlSession openSession = null;
@Before
public void setUp(a) throws Exception {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
openSession = sqlSessionFactory.openSession();
}
@Test
public void getLockById(a) {
LockDao lockDao = openSession.getMapper(LockDao.class);
Lock lock = lockDao.getLockById(3);
System.out.println(lock);
List<Key> keyList = lock.getKeyList();
for (Key key : keyList) {
System.out.println("The Key."+ key); }}}Copy the codePerform the test
The joint query is more complicated and can be completed by step query. Query the lock information in the T_lock table based on the Key information
Step by step query Key -> Lock
Add a query method to KeyDao and a query method to LockDao
Lock getLockByIdSimplely(Integer id);
Copy the codeKey getKeyByIdSimplely(Integer id);
Copy the codeAdd SQL statements to the lock. XML mapping file
<select id="getLockByIdSimplely" resultType="com.citi.entity.Cat">
select * from t_lock where id = #{id}
</select>
Copy the codeStep by step query is completed in key.xml
<select id="getKeyByIdSimplely" resultMap="mykeysimplely">
select * from t_key where id = #{id}
</select>
<resultMap id="mykeysimplely" type="com.citi.entity.Key">
<id property="id" column="id"></id>
<result property="keyName" column="keyname"></result>
<association property="lock"
select="com.citi.dao.LockDao.getLockByIdSimplely"
column="lockid">
</association>
</resultMap>
Copy the code- Property: Specifies which property to encapsulate
- Select: specify the unique identifier of a query SQL. MyBatis automatically calls the specified SQL to encapsulate the results of the query into the specified attributes
- Colume: Specifies which column of data to pass through
Add the test code for the getKeyByIdSimplely method to KeyDaoTest
@Test
public void getKeyByIdSimplely(a) {
KeyDao keyDao = openSession.getMapper(KeyDao.class);
Key key = keyDao.getKeyByIdSimplely(1);
System.out.println(key);
}
Copy the codePerform the test
Enable lazy loading and properties on demand in MyBatis global configuration file
<settings>
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="true"/>
</settings>
Copy the codeThe code comment for the key is printed (not annotating will call the key’s toString method and cause lazy loading to fail), and the test is executed again
The console prints out only one SQL statement, that is, only one SQL statement is executed in the background
Modify the test code to get the Lock
@Test
public void getKeyByIdSimplely(a) {
KeyDao keyDao = openSession.getMapper(KeyDao.class);
Key key = keyDao.getKeyByIdSimplely(1);
// System.out.println(key);
String lockName = key.getLock().getLockName();
System.out.println(lockName);
}
Copy the codeThe test again
Override the global lazy loading configuration, add fetchType=”eager” to the Association TAB, comment out the code that gets the lock, and run the test again
Step by step query Lock -> Key
Add methods to the LockDao and KeyDao respectively
Lock getLockByIdStep(Integer id);
Copy the codeList<Key> getKeysByLockId(Integer id);
Copy the codeAdd SQL statements to query keys in key. XML
<select id="getKeysByLockId" resultType="com.citi.entity.Key">
select * from t_key where lockid = #{id}
</select>
Copy the codeAdd a mapping to the getLockByIdStep method in lock. XML
<select id="getLockByIdStep" resultMap="mylockstep">
select * from t_lock where id = #{id}
</select>
<resultMap id="mylockstep" type="com.citi.entity.Lock">
<id property="id" column="id"></id>
<result property="lockName" column="lockName"></result>
<collection property="keyList"
select="com.citi.dao.KeyDao.getKeysByLockId"
column="id">
</collection>
</resultMap>
Copy the codeAdd test methods
@Test
public void getLockByIdStep(a) {
LockDao lockDao = openSession.getMapper(LockDao.class);
Lock lock = lockDao.getLockByIdStep(3);
System.out.println(lock);
List<Key> keyList = lock.getKeyList();
for (Key key : keyList) {
System.out.println("The Key."+ key); }}Copy the codePerform the test
The column attribute in the reslutMap tag is used to pass the parameters of the query method specified by the SELECT attribute. If the query method has multiple parameters, you can use {key1=value1,key2=value2} to pass the data.
In practice, it is recommended to use the method of connection query rather than step by step query, which will reduce the pressure on the database