When using PageHelper in Mybatis, if you encounter multi table queries, especially associated queries (one-to-one, one to many)
If two or more tables contain the same column name, it will lead to:
1. For one to many and multiple parties, there will be an undefined column (the primary key name or a field name are the same)
### The error may exist in file [/Users/XX/XXMapper.xml] ### The error may involve com.XX.mapper.LklmesStandardpropathMapper.XX-Inline ### The error occurred while setting parameters ### SQL: SELECT count(0) FROM user u LEFT JOIN role r ON u.role_id = r.role_id WHERE status = ? ### Cause: java.sql.SQLSyntaxErrorException: ORA-00918: ambiguous column ; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-00918: Undefined sequence ]
2. When PageHelper is used at the same time, the problem of determining the semantic column will occur (tmp_page. * here
Equivalent to having the same column). In this case, the alias as can be used, such as
select name as n, country as c from user;
SELECT TMP_PAGE.*, ROWNUM PAGEHELPER_ROW_ID FROM (
select u.user_id, u.dept_id, u.user_name, u.nick_name,
d.dept_id, d.parent_id, d.dept_name, d.status as dept_status,
r.role_id, r.role_name, r.status as role_status
from sys_user u
left join sys_dept d on u.dept_id = d.dept_id
left join sys_user_role ur on u.user_id = ur.user_id
left join sys_role r on r.role_id = ur.role_id
) TMP_PAGE
ORA-00918: column ambiguously defined
, Time: 0.010000sNote that there are roles in the user table and the role table_ ID and status are two fields, so when you select a query
1. You can query only select u.user_id,u.user_name
from user u
left join role r on r.role_id = u.role_id, you do not need to query u.role_id, resulting in the u.role in the and user tables_ ID repeated error reporting
2. Use aliases to specify which columns belong to which tables. For example, select * from user u and use u.status
<if test="status != null and status != ''">
and u.status = #{status}
</if>
For example, there are three tables: user table, department table, dept, and permission table, role
Mybatis is one-on-one, using the association tag
Mybatis one to many, using the collection tag
The association tag has two uses: join query and nested query
<?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="XX.SysUserMapper">
<!--user User table mapping-->
<resultMap type="SysUser" id="SysUserResult">
<id property="userId" column="user_id" />
<result property="deptId" column="dept_id" />
<result property="userName" column="user_name" />
<result property="nickName" column="nick_name" />
<result property="status" column="status" />
<association property="dept" column="dept_id" javaType="SysDept" resultMap="deptResult" />
<collection property="roles" javaType="java.util.List" resultMap="RoleResult" />
</resultMap>
<!--dept Department table mapping-->
<resultMap id="deptResult" type="SysDept">
<id property="deptId" column="dept_id" />
<result property="parentId" column="parent_id" />
<result property="deptName" column="dept_name" />
<result property="status" column="dept_status" />
</resultMap>
<!--role Permission table mapping-->
<resultMap id="RoleResult" type="SysRole">
<id property="roleId" column="role_id" />
<result property="roleName" column="role_name" />
<result property="status" column="role_status" />
</resultMap>
<sql id="selectUserVo">
select u.user_id, u.dept_id, u.user_name, u.nick_name,
d.dept_id, d.parent_id, d.dept_name, d.status as dept_status,
r.role_id, r.role_name, r.status as role_status
from sys_user u
left join sys_dept d on u.dept_id = d.dept_id
left join sys_user_role ur on u.user_id = ur.user_id
left join sys_role r on r.role_id = ur.role_id
</sql>
<select id="selectUserList" parameterType="SysUser" resultMap="SysUserResult">
select u.user_id, u.dept_id, u.nick_name, u.user_name,
d.dept_name, d.leader
from sys_user u
left join sys_dept d on u.dept_id = d.dept_id
where u.del_flag = '0'
<if test="userName != null and userName != ''">
AND u.user_name like concat(concat('%',#{userName}),'%')
</if>
<if test="status != null and status != ''">
AND u.status = #{status}
</if>
<if test="deptId != null and deptId != 0">
AND (u.dept_id = #{deptId} OR u.dept_id IN ( SELECT t.dept_id FROM sys_dept t WHERE FIND_IN_SET(#{deptId}, ancestors) <![CDATA[ <> ]]> 0 ))
</if>
</select>
</mapper> Do not use intermediate table {sys_user_role, we can also realize association through keyword JOIN type
INNER JOIN: INNER JOIN, also known as equivalent join, returns rows with equal connection fields in two tables
LEFT JOIN: LEFT JOIN. Even if there is no match in the right table, all rows will be returned from the left table
RIGHT JOIN: RIGHT JOIN. Even if there is no match in the left table, all rows will be returned from the right table
FULL JOIN: external join. If there is a match in one of the tables, it will be returned; That is, the rows in the two tables are returned: left join + right join
CROSS JOIN: the result is Cartesian product, which is the number of rows in the first table multiplied by the number of rows in the second table
Keyword on
When the database returns records by connecting two or more tables, an intermediate temporary table will be generated, and then the temporary table will be returned to the user
When using left Jeon, the differences between on and where conditions are as follows:
(1) The on condition is the condition used when generating the temporary table. It will return the records in the left table regardless of whether the condition in on is true or not.
(2) The where condition is the condition for filtering the temporary table after the temporary table is generated. At this time, there is no meaning of left join (the records in the left table must be returned). If the condition is not true, all items will be filtered out.
<?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="XX.SysUserMapper">
<!--user User table mapping-->
<resultMap type="SysUser" id="SysUserResult">
<id property="userId" column="user_id" />
<result property="deptId" column="dept_id" />
<result property="userName" column="user_name" />
<result property="roleId" column="role_id" />
<result property="status" column="status" />
<association property="dept" column="dept_id" javaType="SysDept" resultMap="deptResult" />
<collection property="roles" javaType="java.util.List" resultMap="RoleResult" />
</resultMap>
<!--dept Department table mapping-->
<resultMap id="deptResult" type="SysDept">
<id property="deptId" column="dept_id" />
<result property="parentId" column="parent_id" />
<result property="deptName" column="dept_name" />
<result property="status" column="dept_status" />
</resultMap>
<!--role Permission table mapping-->
<resultMap id="RoleResult" type="SysRole">
<id property="roleId" column="role_id" />
<result property="roleName" column="role_name" />
<result property="status" column="role_status" />
</resultMap>
<sql id="selectUserVo">
select u.user_id, u.dept_id, u.user_name, u.role_id,
d.dept_id, d.parent_id, d.dept_name, d.status as dept_status,
r.role_id, r.role_name, r.status as role_status
from sys_user u
left join sys_dept d on d.dept_id = u.dept_id
left join sys_role r on r.role_id = u.role_id
</sql>
<select id="selectUserList" parameterType="SysUser" resultMap="SysUserResult">
<if test="userName != null and userName != ''">
AND u.user_name like concat(concat('%',#{userName}),'%')
</if>
<if test="status != null and status != ''">
AND u.status = #{status}
</if>
</select>
</mapper>