MyBatis multi table operation
Multi table query means that when the query returns the result, it cannot automatically match the number. We will manually seal a Map for it and manually Map the data manually.
The following two tables are used as examples
orders table:
users table:
example: one to one query. One order corresponds to one user
private int id;
private Date ordertime;
private double total;
// Note that the foreign key private int uid is not directly used here
// No problem in terms of function implementation, but java pays attention to object-oriented uid. Here is the foreign key connecting another table
// The relationship between tables is achieved through the main foreign key
// The relationship between entities is achieved through the reference of entities
// The idea of object-oriented is to reference through entities
// Which user does the current order belong to
private User user;
<!-- Here type Object data indicating who is encapsulated is encapsulated here order Physical
Object data, so type by order-->
<resultMap id="orderMap" type="order">
<!-- Manually specify the mapping relationship between fields and entity attributes
column: Field name of the data table
property:Attribute name of the entity
-->
<!-- here property The properties of are order Properties of
The result of the query is order of id
-->
<id column="oid" property="id"></id>
<!-- zhu key id The label of is id ，Other common attributes are labeled result-->
<result column="ordertime" property="ordertime"></result>
<result column="total" property="total"></result>
<result column="uid" property="user.id"></result>
<result column="username" property="user.username"></result>
<result column="password" property="user.password"></result>
<result column="birthday" property="user.birthday"></result>
</resultMap>
<select id="findAll" resultMap="orderMap">
SELECT *,o.id oid,u.id uid FROM orders o,USER u WHERE o.uid=u.id
</select>
Here is to query the order table and the user table. The second method:
<resultMap id="orderMap" type="order">
<!-- Manually specify the mapping relationship between fields and entity attributes
column:Field name of the data table
property:Attribute name of the entity
-->
<id column="oid" property="id"></id>
<result column="ordertime" property="ordertime"></result>
<result column="total" property="total"></result>
<result column="uid" property="user.id"></result>
<!--
property:Current entity( order)Attribute name in(private User user)
javaType:Current entity( order)Type of property in(User)
Lower part association Medium property Medium user by order Attributes in entities
-->
<association property="user" javaType="user">
<id column="uid" property="id"></id>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
</association>
</resultMap>
example: one to many query. One user corresponds to multiple orders
private int id;
private String username;
private String password;
// Description of which orders exist
private List<Order> orderList;
public List<Order> getOrderList() {
return orderList;
}
public void setOrderList(List<Order> orderList) {
this.orderList = orderList;
}
<resultMap id="userMap" type="user">
<id column="uid" property="id"></id>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<result column="birthday" property="birthday"></result>
<!-- Configure collection information
property:The name of the collection
ofType:Data types in the current collection
-->
<collection property="orderList" ofType="order">
<!-- encapsulation order Data-->
<id column="oid" property="id"></id>
<result column="ordertime" property="ordertime"></result>
<result column="total" property="total"></result>
</collection>
</resultMap>
<select id="findAll" resultMap="userMap">
SELECT *,o.id oid FROM USER u,orders o WHERE u.id=o.uid
</select>
example: many to many query (three tables), introducing an intermediate table
example:
Introduce a collection into the current user table
//What roles does the current user have
private List<Role> roleList;
public List<Role> getRoleList() {
return roleList;
}
public void setRoleList(List<Role> roleList) {
this.roleList = roleList;
}
Write the xml file:
<resultMap id="userRoleMap" type="user">
<id column="userId" property="id"></id>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<result column="birthday" property="birthday"></result>
<!-- user Information-->
<collection property="roleList" ofType="role">
<id column="roleId" property="id"></id>
<result column="roleName" property="roleName"></result>
<result column="roleDesc" property="roleDesc"></result>
</collection>
<!-- user Internal roleList information-->
</resultMap>
<select id="findUserAndRoleAll" resultMap="userRoleMap">
SELECT * FROM USER u,sys_user_role ur,sys_role r WHERE u.id=ur.userId AND ur.roleId=r.id
</select>
Knowledge summary:
- One to one configuration: (or + association tag)
- One to many configuration:+
- Many to many configuration:+
MyBatis annotation development
1.1MyBatis common notes
@Insert: add @ Update: Update @ Delete: Delete @ Select: query
@Result: implement result set encapsulation @ Results: can be used together with @ result to encapsulate multiple result sets
@One: implement one-to-one result set encapsulation @ Many: implement one to Many result set encapsulation
example:
public interface UserMapper {
@Insert("insert into user values(#{id},#{username},#{password},#{birthday})")
public void save(User user);
@Update("update user set username=#{username},password=#{password} where id=#{id}")
public void update(User user);
@Delete("delete from user where id=#{id}")
public void delete(int id);
@Select("select * from user where id = #{id}")
public User findById(int id);
@Select("select * from user")
public List<User> findAll();
}
Using annotation development, you don't need the mapping file of the interface, and you don't need to load the mapping file. However, you still need to load the mapping relationship in the mybatis core configuration file, for example:
<!-- Load mapping relationship -->
<mappers>
<!-- Specify the package where the interface is located-->
<package name="com.lxz.mapper"></package>
</mappers>
1.2MyBatis annotation to realize complex mapping development
Use @ Results @One @Many annotation combination to complete the configuration of complex relationships
example:
one-on-one:
One user corresponds to one order
@Select("select * from orders o,user u where o.uid=u.id")
@Results({
@Result(column="oid",property="id"),
@Result(column="ordertime",property="ordertime"),
@Result(column="total",property="total"),
@Result(column="uid",property="user.id"),
@Result(column="username",property="user.username"),
@Result(column="password",property="user.password")
})
public List<Order> findAll();
The second way:
@Select("select * from orders o,user u where o.uid=u.id")
@Results({
@Result(column="oid",property="id"),
@Result(column="ordertime",property="ordertime"),
@Result(column="total",property="total"),
@Result(
property="user",//The name of the attribute to encapsulate
column="uid",//Query the data of user table according to that field
javaType = User.class, //Entity type to encapsulate
// The select attribute represents the method of querying that interface to obtain data
one = @One(select = "com.lxz.mapper.UserMapper.findById")//The nested query contains another query
)
})
public List<Order> findAll();
One to many:
One user has multiple orders, that is, one data in the above user table corresponds to multiple data in the order table.
private int id;
private String username;
private String password;
private Date birthday;
// Describes the orders that the current user has
private List<Order> orderList;
@Select("select * from orders where uid=#{uid}")
public List<Order> findByUid(int uid);
@Select("select * from user")
@Results({
@Result(id=true,column="id",property="id"),
@Result(column="username",property="username"),
@Result(column="password",property="password"),
@Result(
property = "orderList",
column = "id",
javaType = List.class,
many = @Many(select = "com.lxz.mapper.OrderMapper.findByUid") //nested queries
)
})
public List<User> findUserAndOrderAll();
Many to many:
A user has multiple roles, and a role can be used by multiple users.
Query requirements: when querying a user, find out multiple roles of the user at the same time.
user table:
sys_user_role table:
sys_role table:
SELECT * FROM USER" SELECT * FROM sys_user_role ur,sys_role r WHERE ur.roleId=r.id AND ur.userId=Last sentence sql Consequential id Value of
private int id;
private String username;
private String password;
private Date birthday;
//What roles does the current user have
private List<Role> roleList;
@Select("SELECT * FROM sys_user_role ur,sys_role r WHERE ur.roleId=r.id AND ur.userId=#{uid}")
public List<Role> findByUid(int uid);
@Select("SELECT * FROM USER")
@Results({
@Result(id=true,column="id",property="id"),
@Result(column = "username",property="username"),
@Result(column = "password",property="password"),
@Result(
property = "roleList",
column = "id",
javaType = List.class,
many = @Many(select="com.lxz.mapper.RoleMapper.findByUid")
)
})
public List<User> findUserAndRoleAll();
Summary: I spent three days reviewing MyBatis I learned before
! Confucius said that we should review the old and know the new. So, guys, rush!