1. Identify your needs
The requirement mentioned in the book is a role-based Access Control requirement (RBAC, role-based Access Control). I believe that everyone is familiar with permission management, because most systems need permission management. One of the systems I was in charge of in my last company was the permission system. The design ideas are similar to those mentioned in the book, and the general description is as follows:
1) Permission point is used to manage resources to control permission, such as a page or a button.
2) Create a role and assign some permission points to this role, such as the permission to all pages of the goods module.
3) Create a new user and assign some roles to the user.
The data relationship diagram is as follows:
2. Prepare data
First, execute the following script to create the 5 tables in the figure above: user table, role table, permission table, user role association table, role permission association table.
CREATE TABLE sys_user (id BIGINT NOT NULL AUTO_INCREMENT COMMENT 'user id ', user_name VARCHAR(50) COMMENT' user id ', User_password VARCHAR(50) COMMENT 'password ', user_email VARCHAR(50) COMMENT' email ', user_info TEXT COMMENT 'introduction ', Head_img BLOB COMMENT '1 ', create_time DATETIME COMMENT' 1 ', PRIMARY KEY (id)); ALTER TABLE sys_user COMMENT '表'; CREATE TABLE sys_ROLE (id BIGINT NOT NULL AUTO_INCREMENT COMMENT 'id ', role_name VARCHAR(50) COMMENT' id ', Enabled INT COMMENT 'valid flag ', create_by BIGINT COMMENT' creator ', create_time DATETIME COMMENT 'create_time ', PRIMARY KEY (id)); ALTER TABLE sys_role COMMENT 'sys_role '; CREATE TABLE sys_privilege (id BIGINT NOT NULL AUTO_INCREMENT COMMENT 'id ', Privilege_name VARCHAR(50) COMMENT 'Privilege_URL VARCHAR(200) COMMENT' privilege_URL VARCHAR ', PRIMARY KEY (ID)); ALTER TABLE sys_privilege COMMENT 'privileges '; CREATE TABLE sys_user_role (user_id BIGINT COMMENT 'user ID', role_id BIGINT COMMENT' role ID'); ALTER TABLE sys_user_role COMMENT '表 名 '; CREATE TABLE sys_ROLE_privilege (role_id BIGINT COMMENT 'role ID', privilege_id BIGINT COMMENT' privilege ID'); ALTER TABLE sys_roLE_privilege COMMENT 'sys_role_privilege ';Copy the codeThen execute the following script to add the test data:
INSERT INTO sys_user VALUES (1,'admin','123456','[email protected]',' administrator ',NULL,current_timestamp); INSERT INTO sys_user VALUES (1,'admin','123456','[email protected]',' administrator ',NULL,current_timestamp); INSERT INTO sys_user VALUES (1001,'test','123456','[email protected]',' test user ',NULL,current_timestamp); INSERT INTO sys_role VALUES (1,' sys_role ',1,1,current_timestamp); INSERT INTO sys_role VALUES (2,' sys_role ',1, current_timestamp); INSERT INTO sys_user_role VALUES (1,1); INSERT INTO sys_user_role VALUES (1,2); INSERT INTO sys_user_role VALUES (1001,2); INSERT INTO sys_privilege VALUES (1,' user management ','/users'); INSERT INTO sys_values (2,' roles','/roles'); INSERT INTO sys_privilege VALUES (3,' sys_log ','/logs'); INSERT INTO sys_privilege VALUES (4,' persons','/persons'); INSERT INTO sys_privilege VALUES (5,' unit maintenance ','/companies'); INSERT INTO sys_role_privilege VALUES (1,1); INSERT INTO sys_role_privilege VALUES (1,2); INSERT INTO sys_role_privilege VALUES (1,3); INSERT INTO sys_role_privilege VALUES (2,4); INSERT INTO sys_role_privilege VALUES (2,5);Copy the code3. Create an entity class
In the package. Com zwwhnly. Mybatisaction. Under the model in order to create this form the corresponding entity class:
package com.zwwhnly.mybatisaction.model;
import java.util.Date;
/** * user table */
public class SysUser {
/** * user ID */
private Long id;
/** * User name */
private String userName;
/** * Password */
private String userPassword;
/** * email */
private String userEmail;
/** ** /
private String userInfo;
/** ** avatar */
private byte[] headImg;
/** * create time */
private Date createTime;
// Press Alt+Insert shortcut keys to generate get and set methods
}
Copy the codepackage com.zwwhnly.mybatisaction.model;
import java.util.Date;
/** * Role table */
public class SysRole {
/** * Role ID */
private Long id;
/** * Role name */
private String roleName;
/** * Valid flag */
private Integer enabled;
/** ** founder */
private Long createBy;
/** * create time */
private Date createTime;
// Press Alt+Insert shortcut keys to generate get and set methods
}
Copy the codeJava, sysuserrole-java, sysroleprivilege.java can be created using similar names.
You can also download the source code at the source address provided at the end of this article.
Matters needing attention:
1)MyBatis default to follow the “underline to hump” naming method.
For example, the entity class name corresponding to the sys_USER table is sys_user, and the entity class field corresponding to the database field user_name is userName.
2) Do not use Java primitive types in entity classes. Primitive types include byte, int, short, long, float, doubule, char, Boolean.
Because there are default values for primitive types in Java, such as private int age in a class; Field, the default value of age is 0, so it cannot satisfy the condition that age is null. =null, the result is always true, leading to some hidden bugs.
4. Create the mapper. XML file
Com under SRC/main/resources/zwwhnly/mybatisaction/mapper directory in turn create 5 table corresponding to the mapper. The XML file.
To create mapper.xml files more quickly, we can add templates as follows:
Enter the following in the content box above:
<? The 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 > </mapper>Copy the codeThen select the directory and right-click to add a file, as shown below:
The content of sysusermapper.xml just generated is as follows:
<? The 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 > </mapper>Copy the codeWe just need to add a namespace attribute to the mapper tag:
<? The 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.zwwhnly.mybatisaction.mapper.SysUserMapper"> </mapper>Copy the codeIn the same way, in turn, create SysRoleMapper. XML, SysPrivilegeMapper. XML, SysUserRoleMapper. XML and SysRolePrivilegeMapper. XML.
Once created, open the mybatis-config. XML file we created in the previous blog and modify the contents of the node as follows:
<mappers>
<mapper resource="com/zwwhnly/mybatisaction/mapper/CountryMapper.xml"/>
<mapper resource="com/zwwhnly/mybatisaction/mapper/SysUserMapper.xml"/>
<mapper resource="com/zwwhnly/mybatisaction/mapper/SysRoleMapper.xml"/>
<mapper resource="com/zwwhnly/mybatisaction/mapper/SysPrivilegeMapper.xml"/>
<mapper resource="com/zwwhnly/mybatisaction/mapper/SysUserRoleMapper.xml"/>
<mapper resource="com/zwwhnly/mybatisaction/mapper/SysRolePrivilegeMapper.xml"/>
</mappers>
Copy the codeThe most obvious disadvantage of using this method is that if we add mapper. XML file later, we still need to modify the file, which is very difficult to maintain, so we changed the configuration mode as follows, configure a package name:
<mappers>
<package name="com.zwwhnly.mybatisaction.mapper"/>
</mappers>
Copy the codeCountryMapperTest, unit test, unit test.
In our last blog, we didn’t create an interface for countrymapper.xml file. Using package name configuration, we needed to create, So the solution is the new package under SRC/main/Java com. Zwwhnly. Mybatisaction. Mapper, and new interface CountryMapper under the package, then add methods in the interface of selectAll ().
package com.zwwhnly.mybatisaction.mapper;
import com.zwwhnly.mybatisaction.model.Country;
import java.util.List;
public interface CountryMapper {
/** * query all countries **@return* /
List<Country> selectAll(a);
}
Copy the code5. Create a Mapper interface
Find the SRC/main/Java directory package com. Zwwhnly. Mybatisaction. Mapper, under the package to create XML file corresponding interface classes, respectively SysUserMapper. Java, SysRoleMapper. Java, SysPrivilegeMapper. Java, SysUserRoleMapper. Java, SysRolePrivilegeMapper. Java.
Here is the code for sysusermapper.java:
package com.zwwhnly.mybatisaction.mapper;
public interface SysUserMapper {}Copy the codeNote: When Mapper interfaces are associated with XML files, the namespace value needs to be configured as the fully qualified name of the interface. MyBatis uses this value internally to associate interfaces with XML.
For example SysUserMapper. Configured in the XML namespace is com. Zwwhnly. Mybatisaction. Mapper. SysUserMapper
6. Select usage
6.1 Querying Single Data
Suppose we need to query user information by ID. First, we need to open the SysuserMapper.java interface definition method:
/** * Query user ** by id@param id
* @return* /
SysUser selectById(Long id);
Copy the codeThen open the corresponding sysusermapper. XML file and add the following:
<resultMap id="sysUserMap" type="com.zwwhnly.mybatisaction.model.SysUser">
<id property="id" column="id"/>
<result property="userName" column="user_name"/>
<result property="userPassword" column="user_password"/>
<result property="userEmail" column="user_email"/>
<result property="userInfo" column="user_info"/>
<result property="headImg" column="head_img" jdbcType="BLOB"/>
<result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
</resultMap>
<select id="selectById" resultMap="sysUserMap">
SELECT * FROM sys_user WHERE id = #{id}
</select>
Copy the codeDescription:
1)MyBatis associates the name of the interface with the ID attribute value of the SELECT tag.
2) Tag ID attribute value cannot appear English period “.”.
3) The id attribute value of the tag cannot be repeated in the same namespace.
4) Because interface methods can be overloaded, multiple methods with the same name and different parameters can appear in the interface, but the VALUE of THE ID in XML cannot be repeated, so all methods with the same name in the interface will correspond to methods with the same ID in XML.
To verify point 2, we change selectById to select.byid:
<select id="select.ById" resultMap="sysUserMap">
SELECT * FROM sys_user WHERE id = #{id}
</select>
Copy the codeIf this method is called, the following error is reported:
To verify point 3, we change the XML content to the following:
<select id="selectById" resultMap="sysUserMap">
SELECT * FROM sys_user WHERE id = #{id}
</select>
<select id="selectById" resultMap="sysUserMap">
SELECT * FROM sys_user WHERE id = #{id}
</select>
Copy the codeIf this method is called, the following error is reported:
XML code:
- Select: label used by the mapping query statement.
- Id: The unique identifier of the query statement that can be used to represent the statement.
- ResultMap: Used to set the mapping between columns returned by the database and Java objects.
- SELECT * FROM sys_user WHERE id = #{id}
- {id} : a way to use a precompiled parameter in MyBatis SQL. The id in braces represents the name of the parameter passed in.
The resultMap tag is used to configure the mapping between Java object attributes and query result columns. The column and Property configured in the resultMap map can map the query column values to type object attributes.
The resultMap tag used by the above query statement explains:
- Id: Mandatory and unique. The value of the resultMap attribute of the SELECT tag is the value set for this id.
- Type: Mandatory. Used to configure the Java object model to which query columns are mapped.
- Column: The column name or alias obtained from the database.
- Property: The property of the column result to map to, that is, the property of the Java object model.
- JdbcType: database type corresponding to the column.
6.2 Querying Multiple Pieces of Data
Suppose we need to query all user information. First, we need to open the SysuserMapper.java interface definition method:
/** * Query all users **@return* /
List<SysUser> selectAll(a);
Copy the codeThen open the corresponding sysusermapper. XML file and add the following:
<select id="selectAll" resultType="com.zwwhnly.mybatisaction.model.SysUser">
SELECT id,
user_name userName,
user_password userPassword,
user_email userEmail,
user_info userInfo,
head_img headImg,
create_time createTime
FROM sys_user
</select>
Copy the codeNote: Instead of using the resultMap attribute to set the type of result to return, we specify it directly using the resultType attribute
To return the type of the result, use this method to set the alias of the query column. The alias must be the same as the attribute name of the object specified by resultType.
To achieve automatic mapping.
MyBatis provides a global attribute called mapUnderscoreToCamelCase. Setting the value of this attribute to true automatically maps the underscoretocamelCase database columns with underlined names to the camel case named properties of Java objects.
So how do you open it?
To do this, open the mybatis-config file we created in the previous blog and add the following configuration to the Settings node:
<settings>
<! -- Other configuration -->
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
Copy the codeAt this point, the previous selectAll statement can be simplified as follows.
<select id="selectAll" resultType="com.zwwhnly.mybatisaction.model.SysUser">
SELECT id,
user_name,
user_password,
user_email,
user_info,
head_img,
create_time
FROM sys_user
</select>
Copy the codeUnit testing
Create a new base test class, BaseMapperTest, with the following code.
package com.zwwhnly.mybatisaction.mapper;
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 org.junit.BeforeClass;
import java.io.IOException;
import java.io.Reader;
/** * Basic test class */
public class BaseMapperTest {
private static SqlSessionFactory sqlSessionFactory;
@BeforeClass
public static void init(a) {
try {
Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
reader.close();
} catch(IOException e) { e.printStackTrace(); }}public SqlSession getSqlSession(a) {
returnsqlSessionFactory.openSession(); }}Copy the codeChange the CountryMapperTest class code from the previous blog to the following.
package com.zwwhnly.mybatisaction.mapper;
import com.zwwhnly.mybatisaction.model.Country;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class CountryMapperTest extends BaseMapperTest {
@Test
public void testSelectAll(a) {
SqlSession sqlSession = getSqlSession();
try {
List<Country> countryList = sqlSession.selectList("com.zwwhnly.mybatisaction.mapper.CountryMapper.selectAll");
printCountryList(countryList);
} finally{ sqlSession.close(); }}private void printCountryList(List<Country> countryList) {
for (Country country : countryList) {
System.out.printf("%-4d%4s%4s\n", country.getId(), country.getCountryname(), country.getCountrycode()); }}}Copy the codeModify points:
1) Inherit the basic test class BaseMapperTest, call the getSqlSession() method to obtain the SqlSession object, to achieve code reuse.
2) the selectList () method of the parameter value by selectAll modification for the com. Zwwhnly. Mybatisaction. Mapper. CountryMapper. SelectAll,
Because a selectAll() method was also added to SysUserMapper, selectAll is no longer unique, so it must be called with a namespace.
Create a SysUserMapperTest test class. The code is as follows.
package com.zwwhnly.mybatisaction.mapper;
import com.zwwhnly.mybatisaction.model.SysUser;
import org.apache.ibatis.session.SqlSession;
import org.junit.Assert;
import org.junit.Test;
import java.util.List;
public class SysUserMapperTest extends BaseMapperTest {
@Test
public void testSelectById(a) {
SqlSession sqlSession = getSqlSession();
try {
SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);
SysUser sysUser = sysUserMapper.selectById(1L);
Assert.assertNotNull(sysUser);
Assert.assertEquals("admin", sysUser.getUserName());
} finally{ sqlSession.close(); }}@Test
public void testSelectAll(a) {
SqlSession sqlSession = getSqlSession();
try {
SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);
List<SysUser> sysUserList = sysUserMapper.selectAll();
Assert.assertNotNull(sysUserList);
Assert.assertTrue(sysUserList.size() > 0);
} finally{ sqlSession.close(); }}}Copy the codeRun the test class code, the test passes, and the output log is as follows.
DEBUG [main] – ==> Preparing: SELECT id, user_name, user_password, user_email, user_info, head_img, create_time FROM sys_user
DEBUG [main] – ==> Parameters:
TRACE [main] – <== Columns: id, user_name, user_password, user_email, user_info, head_img, create_time
TRACE [main] – <== Row: 1, admin, 123456, [email protected], <>, <>, 2019-06-27 18:21:07.0
TRACE [main] – <== Row: 1001, test, 123456, [email protected], <>, <>, 2019-06-27 18:21:07.0
DEBUG [main] – <== Total: 2
DEBUG [main] – ==> Preparing: SELECT * FROM sys_user WHERE id = ?
DEBUG [main] – ==> Parameters: 1(Long)
TRACE [main] – <== Columns: id, user_name, user_password, user_email, user_info, head_img, create_time
TRACE [main] – <== Row: 1, admin, 123456, [email protected], <>, <>, 2019-06-27 18:21:07.0
DEBUG [main] – <== Total: 1
8. Source code and reference
Source code address: github.com/zwwhnly/myb… Welcome to download.
MyBatis from Entry to Mastery by Liu Zenghui
Create XML files in IntelliJ IDEA