One, foreword

It has been a long time since I used Mybatis to do the project in 2017. I never used Mybatis again. Cause now learning SpringBoot process encountered some Mybatis problems, to make a summary (XML minimalist mode). Of course, this is just a practical summary, but not a detailed one. Here is only a summary of how to use!!

(Skip to eleven this time, because the middle is RabbitMQ, you see brother Smile enough)

Source: github.com/niaobulashi…

About Mybatis

1. What is Mybatis

(1) Mybatis is a semi-ORM (Object Relational Mapping) framework, which encapsulates JDBC internally. During development, we only need to pay attention to THE SQL statement itself, and do not need to spend energy to deal with the complex process of loading drivers, creating connections, creating statements and so on. Programmers directly write the original SQL, SQL execution performance can be strictly controlled, high flexibility.

(2) MyBatis can configure and map native information using XML or annotations to map POJOs to records in the database, avoiding almost all JDBC code and manually setting parameters and fetching result sets.

(3) All statements to be executed are configured through XML files or annotations, and the SQL statements to be executed are generated by mapping Java objects and dynamic parameters of SQL in statements. Finally, MYBatis framework executes SQL, maps the results to Java objects and returns them. (The process from executing SQL to returning result).

2. Advantages of Mybaits

(1) Based on SQL statement programming, quite flexible, will not cause any impact on the existing design of the application program or database, SQL written in XML, remove the COUPLING of SQL and program code, easy to unified management; Provides XML tags that support writing dynamic SQL statements and can be reused.

(2) compared with JDBC, reduce more than 50% of the code, eliminate a lot of JDBC redundant code, do not need to manually switch the connection;

(3) very good compatibility with a variety of databases (because MyBatis uses JDBC to connect to the database, so as long as JDBC support database MyBatis support).

(4) Good integration with Spring;

(5) Provide mapping labels to support ORM field relational mapping between objects and databases; Provides object-relational mapping labels to support object-relational component maintenance.

3. Disadvantages of MyBatis framework

(1) THE workload of SQL statement writing is large, especially when there are many fields and associated tables, there are certain requirements for developers to write SQL statement skills.

(2) SQL statements rely on the database, resulting in poor database portability, can not be replaced at will database.

4, MyBatis framework applicable occasions

(1) MyBatis focuses on SQL itself and is a flexible DAO layer solution.

(2) MyBatis will be a good choice for projects with high performance requirements or more variable requirements, such as Internet projects.

5. What are the differences between MyBatis and Hibernate

(1) Unlike Hibernate, Mybatis is not a complete ORM framework, because Mybatis requires programmers to write their own Sql statements.

(2) Mybatis directly writes the original SQL, which can strictly control the PERFORMANCE of SQL execution and has high flexibility. It is very suitable for software development with low requirements on relational data model, because such software needs to change frequently and output results rapidly once the requirements change. However, the premise of flexibility is that Mybatis cannot achieve database independence. If you need to implement software supporting a variety of databases, you need to customize multiple sets of SQL mapping files, and the workload is heavy.

(3) Hibernate object/relational mapping ability is strong, database independence is good, for software with high requirements of relational model, if Hibernate development can save a lot of code, improve efficiency.

Use summary

The following usage example suggests cloning the source code to run locally, all in XMl minimalist mode

Because I didn’t post the whole code, only the key processing parts

All tests have passed the Postman send request test.

However, I suggest that you can use the plug-in of IDEA: Restfultookit, which is very easy to use. It can quickly generate request packets according to the URL address defined by controller, and can be directly tested. This plugin is unbeatable for test messages! Highly recommended (already installed when I don’t say)

1, Java, JDBC and MySQL data type comparison data type relation table

Any MySQL data type can be converted to a Java data type.

Rounding, overflow, or precision loss may occur if the Selected Java numeric data type has less precision or capacity than the MySQL data type to be converted.

The following table lists transformations that are always guaranteed to work. The first column lists one or more MySQL data types, and the second column lists one or more Java types that can be converted to MySQL types.

These MySQL Data Types Can always be converted to these Java types
CHAR, VARCHAR, BLOB, TEXT, ENUM, and SET java.lang.String, java.io.InputStream, java.io.Reader, java.sql.Blob, java.sql.Clob
FLOAT, REAL, DOUBLE PRECISION, NUMERIC, DECIMAL, TINYINT, SMALLINT, MEDIUMINT, INTEGER, BIGINT java.lang.String, java.lang.Short, java.lang.Integer, java.lang.Long, java.lang.Double, java.math.BigDecimal
DATE, TIME, DATETIME, TIMESTAMP java.lang.String, java.sql.Date, java.sql.Timestamp

The resultSet.getobject () method uses type conversions between MySQL and Java types, following the appropriate JDBC specification. ResultSetMetaData. GetColumnTypeName () and ResultSetMetaData GetColumnClassName () returns the value shown in the following table. For more information about JDBC Types, see the reference to the java.sql.types class.

MySQL Type Name Return value of GetColumnTypeName Return value of GetColumnClassName
BIT(1) BIT java.lang.Boolean
BIT( > 1) BIT byte[]
TINYINT TINYINT java.lang.Boolean if the configuration property tinyInt1isBit is set to true (the default) and the storage size is 1, or java.lang.Integer if not.
BOOL.BOOLEAN TINYINT See TINYINT, above as these are aliases for TINYINT(1), currently.
SMALLINT[(M)] [UNSIGNED] SMALLINT [UNSIGNED] java.lang.Integer (regardless of whether it is UNSIGNED or not)
MEDIUMINT[(M)] [UNSIGNED] MEDIUMINT [UNSIGNED] java.lang.Integer (regardless of whether it is UNSIGNED or not)
INT,INTEGER[(M)] [UNSIGNED] INTEGER [UNSIGNED] java.lang.Integer, if UNSIGNED java.lang.Long
BIGINT[(M)] [UNSIGNED] BIGINT [UNSIGNED] java.lang.Long, if UNSIGNED java.math.BigInteger
FLOAT[(M,D)] FLOAT java.lang.Float
DOUBLE[(M,B)] DOUBLE java.lang.Double
DECIMAL[(M[,D])] DECIMAL java.math.BigDecimal
DATE DATE java.sql.Date
DATETIME DATETIME java.sql.Timestamp
TIMESTAMP[(M)] TIMESTAMP java.sql.Timestamp
TIME TIME java.sql.Time
YEAR[(2|4)] YEAR If yearIsDateType configuration property is set to false, then the returned object type is java.sql.Short. If set to true (the default), then the returned object is of type java.sql.Date with the date set to January 1st, at midnight.
CHAR(M) CHAR java.lang.String (unless the character set for the column is BINARY, then byte[] is returned.
VARCHAR(M) [BINARY] VARCHAR java.lang.String (unless the character set for the column is BINARY, then byte[] is returned.
BINARY(M) BINARY byte[]
VARBINARY(M) VARBINARY byte[]
TINYBLOB TINYBLOB byte[]
TINYTEXT VARCHAR java.lang.String
BLOB BLOB byte[]
TEXT VARCHAR java.lang.String
MEDIUMBLOB MEDIUMBLOB byte[]
MEDIUMTEXT VARCHAR java.lang.String
LONGBLOB LONGBLOB byte[]
LONGTEXT VARCHAR java.lang.String
ENUM('value1','value2',...) CHAR java.lang.String
SET('value1','value2',...) CHAR java.lang.String

Reference: 6.5 Java, JDBC, and MySQL Types

2. What if the name of the attribute in the entity class is different from the name of the field in the table

One: Define field aliases to match entity class attribute names.

<! Query user information list 1 -->
<select id="queryUserList1" resultType="com.niaobulashi.entity.SysUser">
   SELECT
		u.user_id, u.username userNameStr, u.password, u.salt, u.email,
		u.mobile, u.status, u.dept_id, u.create_time
	FROM
		sys_user u
	where 1=1
</select>
Copy the code

Second, the name of the mapping field is consistent with the name of the entity class attribute through resultMap

<resultMap id="sysUserInfoMap" type="com.niaobulashi.entity.SysUser">
	<! UserId attribute to map primary key field userId-->
	<id property="id" column="userId"/>
	<! Property = entity class attribute name, column = data table attribute
	<result property="userNameStr" column="username"/>
</resultMap>

<! - the user Vo - >
<sql id="selectSysUserVo">
	SELECT
		u.user_id, u.username, u.password, u.salt, 
		u.email, u.mobile, u.status, u.dept_id, u.create_time
	FROM
		sys_user u
</sql>

<! Query user information list 2 -->
<select id="queryUserList2" resultMap="sysUserInfoMap">
    <include refid="selectSysUserVo"/>
    where 1=1
</select>
Copy the code

The second option is recommended.

2. Obtain Mybatis self-growing primary key

UseGeneratedKeys =”true” keyProperty=”id”

<! Get auto-generated (primary) key value -->
<insert id="insertSysTest" parameterType="com.niaobulashi.model.SysTest"
		useGeneratedKeys="true" keyProperty="id">
	INSERT INTO sys_test(name, age, nick_name) VALUES (#{name},#{age},#{nickName})
</insert>
Copy the code

Gets the self-growing primary key

/** * get auto-grow primary key ID *@param sysTest
 * @throws Exception
 */
@RequestMapping(value = "/add", method = RequestMethod.POST)
private void addSysTest(@RequestBody SysTest sysTest) throws Exception {
	try {
		SysTest sysTestParam = new SysTest();
        // Copy the incoming arguments to the newly declared object so that the self-growing primary key can be obtained from sysTestParam
		BeanUtils.copyProperties(sysTest, sysTestParam);
		this.sysTestService.insertSysTest(sysTestParam);
		log.info("Get self-growing primary key is:" + sysTestParam.getId());
	} catch (Exception e) {
		e.printStackTrace();
		throw newException(); }}Copy the code

3, fuzzy query

Using the %”#{value}”%” method causes SQL injection

CONCAT(‘%’,#{value},’%’)

<! -- uservo --> < SQL ID ="selectSysUserVo"> SELECT U. U. _ID, U. U. name, U. password, U.S Alt, U.mail, U.Mobile, U. SCtatus, u.dept_id, u.create_time FROM sys_user u </sql> <! ParameterType ="String" resultMap="sysUserInfoMap"> <include refid="selectSysUserVo"/> where 1=1 and u.username like concat('%',#{userName},'%') </select>Copy the code

4, multi-condition query

Use @param

List<SysUser> queryUserByNameAndEmail(@Param("userName") String userName, @Param("email") String email);
Copy the code
<! -- Query user information using username and email -->
<select id="queryUserByNameAndEmail" resultMap="sysUserInfoMap">
	<include refid="selectSysUserVo"/>
	<where>
        <if test="userName ! = null and userName ! = "">
            AND u.username like concat('%',#{userName},'%')
        </if>
        <if test="email ! = null and email ! = "">
            AND u.email like concat('%',#{email},'%')
        </if>
	</where>
</select>
Copy the code

2. Use JavaBean

Here are some common query criteria: date, amount.

List<SysUser> queryUserByUser(SysUser sysUser);
Copy the code
<select id="queryUserByUser" parameterType="com.niaobulashi.model.SysUser" resultMap="sysUserInfoMap">
	<include refid="selectSysUserVo"/>
	<where>1 = 1<if test="userNameStr ! = null and userNameStr ! = "">
			AND u.username like concat('%', #{userNameStr}, '%')
		</if>
		<if test="email ! = null and email ! = "">
			AND u.email like concat('%', #{email}, '%')
		</if>
		<if test="mobile ! = null and mobile ! = "">
			AND u.mobile like concat('%', #{mobile}, '%')
		</if>
		<if test="createDateStart ! = null and createDateStart ! = "">/ * * / start time retrieval AND date_format (u.c. reate_time, '% y % m % d') <! [CDATA[ >= ]]> date_format(#{createDateStart}, '%y%m%d')</if>
		<if test="createDateEnd ! = null and createDateEnd ! = "">/ * * / end time retrieval AND date_format (u.c. reate_time, '% y % m % d') <! [CDATA[ <= ]]> date_format(#{createDateEnd}, '%y%m%d')</if>
		<if test="amtFrom ! = null and amtFrom ! = "">/* start value */ AND u.amt <! [CDATA[ >= ]]> #{amtFrom}</if>
		<if test="amtTo ! = null and amtTo ! = "">/* AND u.amt <! [CDATA[ <= ]]> #{amtTo}</if>
		<if test="updateDateStart ! = null and updateDateStart ! = "">/ * * / start time retrieval AND date_format (u.u pdate_date, '% y % m % d') <! [CDATA[ >= ]]> date_format(#{updateDateStart}, '%y%m%d')</if>
        <if test="updateDateEnd ! = null and updateDateEnd ! = "">/ * * / end time retrieval AND date_format (u.u pdate_date, '% y % m % d') <! [CDATA[ <= ]]> date_format(#{updateDateEnd}, '%y%m%d')</if>
	</where>
</select>
Copy the code

5. Delete foreach in batches

The XML part

<delete id="deleteSysTestByIds" parameterType="String">
	delete from sys_test where id in
	<foreach collection="array" item="id" open="(" separator="," close=")">
		#{id}
	</foreach>
</delete>
Copy the code

Foreach contains the attribute description:

  • Open: string at the beginning of the loop content.
  • Close: String at the end of the loop.
  • Separator: Separates each loop.
  • Item: Each value taken from the iterator.
  • Index: If the parameter is set or array, the value is the current index value. If the parameter is Map, the value is the Map key.
  • Collection: The name of the property to iterate through.

The dao part

int deleteSysTestByIds(String[] ids);
Copy the code

The service layer

@Transactional(rollbackFor = Exception.class)
@Override
public int deleteDictDataByIds(String ids) throws Exception{
	try {
		return sysTestDao.deleteSysTestByIds(ids.split(","));
	} catch (Exception e) {
		e.printStackTrace();
		throw newException(); }}Copy the code

controller

@RequestMapping(value = "/deleteIds", method = RequestMethod.POST)
public int deleteIds(String ids) throws Exception {
	try {
		return sysTestService.deleteDictDataByIds(ids);
	} catch (Exception e) {
		e.printStackTrace();
		throw newException(); }}Copy the code

The requested URL: http://localhost:8081/test/deleteIds

Request message:

Ids: 1, 2Copy the code

Select association and collection from multiple tables

Multiple table query, multiple table sure first we need to make clear two keywords:

Association: one-to-one association (has one); Collection: One-to-many associations (has many)

The meanings of each attribute of:

Association and collection
Property: Field or attribute that maps a database column.

Colum: column name or column label alias of the database.

JavaTyp: Full Java class name or alias.

JdbcType: JDBC types listed in the supported JDBC types list. This attribute is only useful for columns that are allowed to be null during INSERT, UPDATE, or DELETE.

ResultMap: A resultMap that can map federated nested result sets to an appropriate object view. This is an alternative way to call another SELECT statement.

This might be a little confusing, but let me give you an example

In terms of the three tables, I drew them roughly:

– The users table Department of table Character sheet
The name of the table sys_user sys_dept sys_role
Relationship with user table – One-to-one (a user belongs to only one department) One-to-many (one user can have multiple roles)

So the user table is associated with the department table, we use association

The user table is associated with the role table, and we use collection

Of course, you can use such a painful keyword only if you want to query the associated field, if you just do not look it up, then you do not need to use this thing.

I combine these two multi-table query keywords association, collection for example.

User table entity class

@Data
public class SysUser implements Serializable {
	private static final long serialVersionUID = 1L;
	/** user ID */
	private Long userId;
	/** User name */
	private String userNameStr;
	/ * * * / passwords
	private String password;
	/ * * * / salt
	private String salt;
	/ * * E-mail * /
	private String email;
	/** Mobile phone number */
	private String mobile;
	/** Status 0: disabled 1: Normal */
	private Integer status;
	/** Department Id */
	private Long deptId;
	/** Create time */
	private Date createTime;
	/**************** Associated part ************** /** Department */
	private SysDept dept;
	/** Set of roles */
	private List<SysRole> roles;
}
Copy the code

2, department table entity class

@Data
public class SysDept implements Serializable {
    /** Department ID */
    private Long deptId;
    /** Department name */
    private String deptName;
}
Copy the code

3. Role table entity class

@Data
public class SysRole implements Serializable {
    /** Role ID */
    private Long roleId;
    /** Role name */
    private String roleName;
}
Copy the code

4. Mapper and Service (omitted)

List<SysUser> queryUserRoleDept(SysUser user);
Copy the code

5. XML

<! View user department and role information -->
<select id="queryUserRoleDept" parameterType="com.niaobulashi.model.SysUser" resultMap="UserResult">
	select u.user_id, u.username, u.dept_id, d.dept_name, r.role_id, r.role_name
	from sys_user u
	LEFT JOIN sys_dept d on d.dept_id = u.dept_id
	LEFT JOIN sys_user_role ur on ur.user_id = u.user_id
	LEFT JOIN sys_role r on r.role_id = ur.role_id
	WHERE 1=1
	<if test="userId ! = null and userId ! = "">
		AND u.user_id = #{userId}
	</if>
</select>
Copy the code

UserResult part

<! -- User table -->
<resultMap type="com.niaobulashi.model.SysUser" id="UserResult">
	<id property="userId" column="user_id"/>
	<result property="userNameStr" column="username"/>
	<result property="password" column="login_name"/>
	<result property="salt" column="password"/>
	<result property="email" column="email"/>
	<result property="mobile" column="mobile"/>
	<result property="status" column="status"/>
	<result property="deptId" column="dept_id"/>
	<result property="createTime" column="create_time"/>
	<association property="dept" column="dept_id" javaType="com.niaobulashi.model.SysDept" resultMap="DeptResult"/>
	<collection property="roles" javaType="java.util.List" resultMap="RoleResult"/>
</resultMap>

<! -- Department table -->
<resultMap id="DeptResult" type="com.niaobulashi.model.SysDept">
	<id property="deptId" column="dept_id"/>
	<result property="deptName" column="dept_name"/>
</resultMap>

<! -- Role table -->
<resultMap id="RoleResult" type="com.niaobulashi.model.SysRole">
	<id property="roleId" column="role_id"/>
	<result property="roleName" column="role_name"/>
</resultMap>
Copy the code

6. Controller

@RequestMapping(value = "/queryUserRoleDept", method = RequestMethod.POST)
private List<SysUser> queryUserRoleDept(@RequestBody SysUser sysUser) {
	List<SysUser> userList = sysUserService.queryUserRoleDept(sysUser);
	return userList;
}
Copy the code

7. Test section

Request result:

7. Paging plug-ins

PageHelper Spring Boot Starter: PageHelper Spring Boot Starter1.2.12

Application. Yml configuration

# PageHelper pagination plugin
pagehelper:
  helperDialect: mysql
  reasonable: true
  supportMethodsArguments: true
  params: count=countSql
Copy the code

controller

@RequestMapping(value = "/queryUserByPage", method = RequestMethod.GET)
private PageInfo queryUserByPage(Integer currentPage, Integer pageSize) {
	PageHelper.startPage(currentPage, pageSize);
	List<SysUser> userList = sysUserService.queryUserRoleDept(new SysUser());
	PageInfo info=new PageInfo(userList);
	return info;
}
Copy the code

I’m writing here for now and will continue to fill in this article

To be continued

Author: bird does not defecate Reference: https://juejin.cn/user/712139266080487

The copyright of this article belongs to the author and nuggets, welcome to reprint, but without the consent of the author must retain this statement, and give the original link in the obvious position of the article page, otherwise reserve the right to pursue legal responsibility. If you find it helpful, you can click “like” in the upper left corner.