一、映射文件概述
二、动态sql(if、foreach、sql语句抽取)
select * from user WHERE id=? and username like concat('%',?,'%')
select * from user WHERE id in( ? , ? )
1、Dao
package com.lt.dao;
import com.lt.domain.User;
import java.util.List;
public interface UserMapper {
List<User> findAll();
List<User> findByCondition(User user);
List<User> findByIds(List<Integer> list);
}
2、映射文件
<?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.lt.dao.UserMapper">
<select id="findAll" resultType="user">
select * from user
</select>
<!--sql语句抽取-->
<sql id="selectUser">select * from user</sql>
<!-- 如果实体中id的是int类型,那么是 id!=0 -->
<!-- 如果实体中id的是Integer类型,那么是 id!=null -->
<select id="findByCondition" resultType="user" parameterType="user">
<include refid="selectUser"></include>
<where>
<if test="id!=0">
and id=#{id}
</if>
<if test="username!=null">
and username like concat('%',#{username},'%')
</if>
<if test="password!=null">
and password=#{password}
</if>
</where>
</select>
<!-- select * from user where id in(1,2,3) -->
<!-- collection="list" 传递过来的是list集合。如果传递过来是数组,那就 collection="array" -->
<!-- open="id in(" 开始字符串id in( -->
<!-- close=")" 结束字符串 ( -->
<!-- item="id" 每项用id接收 -->
<!-- separator="," 分割符 , -->
<select id="findByIds" parameterType="list" resultType="user">
<include refid="selectUser"></include>
<where>
<foreach collection="list" open="id in(" close=")" item="id" separator=",">
#{id}
</foreach>
</where>
</select>
</mapper>
2.1 批量添加数据
3、测试
package com.lt.server;
import com.lt.dao.UserMapper;
import com.lt.domain.User;
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 java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
public class ServerDemo {
public static void main(String[] args) throws IOException {
InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
// sqlSession.getMapper(接口)
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
//List<User> list = mapper.findAll();
User u =new User();
u.setId(6);
u.setUsername("t");
//u.setPassword("2222");
List<User> list = mapper.findByCondition(u);
System.out.println(list);
List<Integer> ids=new ArrayList<Integer>();
ids.add(1);
ids.add(2);
list = mapper.findByIds(ids);
System.out.println(list);
}
}
三、多表查询中 一对一
一个订单表对应一个用户表
1、数据库表结构
order表
user表
2、Order实体
package com.lt.domain;
public class Order {
private int id;
private String ordername;
//订单属于哪个用户
private User user;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getOrdername() {
return ordername;
}
public void setOrdername(String ordername) {
this.ordername = ordername;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
@Override
public String toString() {
return "Order{" +
"id=" + id +
", ordername='" + ordername + '\'' +
", user=" + user +
'}';
}
}
3、接口、映射
package com.lt.dao;
import com.lt.domain.Order;
import java.util.List;
public interface OrderMapper {
public List<Order> findAll();
}
<?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.lt.dao.OrderMapper">
<!--type="order" 实体类型(我们在核心配置文件中使用了别名)-->
<resultMap id="orderMap" type="order">
<!-- 手动指定字段与实体属性的映射关系
column 数据库里字段名
property 实体属性名称
-->
<!--id标签表示主键-->
<id column="oid" property="id"></id>
<result column="ordername" property="ordername"></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>
-->
<!--另种方法-->
<!--
association 匹配
property:当前实体(order)中属性名称(private User user)
javaType:当前实体属性的类型(user使用了别名)
-->
<association property="user" javaType="user">
<result column="uid" property="id"></result>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<result column="birthday" property="birthday"></result>
</association>
</resultMap>
<!--resultMap属性指定结果为map-->
<select id="findAll" resultMap="orderMap">
SELECT *,o.id oid from `order` o,`user` u where o.uid=u.id
</select>
</mapper>
4、核心文件配置
<!--2、加载映射文件-->
<mappers>
<!-- <mapper resource="com/lt/dao/UserMapper.xml"/>-->
<!-- <mapper resource="com/lt/dao/OrderMapper.xml"/>-->
<package name="com.lt.dao"/>
</mappers>
<!--类型别名-->
<typeAliases>
<typeAlias type="com.lt.domain.User" alias="user"/>
<typeAlias type="com.lt.domain.Order" alias="order"/>
</typeAliases>
5、测试
OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
List<Order> orderList = orderMapper.findAll();
System.out.println(orderList);
四、多表查询中 一对多
一个用户表对应多个订单
1、实体、映射
package com.lt.domain;
import java.util.Date;
import java.util.List;
public class User {
private int id;
private String username;
private String password;
private Date birthday;
//一个用户拥有多个订单
private List<Order> orderList;
public List<Order> getOrderList() {
return orderList;
}
public void setOrderList(List<Order> orderList) {
this.orderList = orderList;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", birthday=" + birthday +
", orderList=" + orderList +
'}';
}
}
<resultMap id="userMap" type="user">
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="password" property="password"/>
<result column="birthday" property="birthday"/>
<!--配置集合信息
property:user实体类的属性 orderList
ofType: 当前集合的数据类型(这里用了别名)
-->
<collection property="orderList" ofType="order">
<id column="oid" property="id"/>
<result column="ordername" property="ordername"/>
</collection>
</resultMap>
<select id="findAll" resultMap="userMap">
SELECT *,o.id oid from `user` u,`order` o where u.id=o.uid
</select>
2、核心文件配置加载映射文件,设置别名
3、测试
InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
// sqlSession.getMapper(接口)
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = mapper.findAll();
System.out.println(userList);
五、多表查询中 多对多
一个用户有多个角色、一个角色有多个用户(我们需要有3张表:用户表、角色表、用户角色中间表)
sys_user
sys_role
sys_user_role
1、实体(SysRole、SysUser)
package com.lt.domain;
public class SysRole {
private int id;
private String roleName;
private String roleDesc;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getRoleName() {
return roleName;
}
public void setRoleName(String roleName) {
this.roleName = roleName;
}
public String getRoleDesc() {
return roleDesc;
}
public void setRoleDesc(String roleDesc) {
this.roleDesc = roleDesc;
}
@Override
public String toString() {
return "SysRole{" +
"id=" + id +
", roleName='" + roleName + '\'' +
", roleDesc='" + roleDesc + '\'' +
'}';
}
}
package com.lt.domain;
import java.util.List;
public class SysUser {
private int id;
private String username;
private String email;
private String password;
private String phoneNum;
private List<SysRole> roleList;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getPhoneNum() {
return phoneNum;
}
public void setPhoneNum(String phoneNum) {
this.phoneNum = phoneNum;
}
public List<SysRole> getRoleList() {
return roleList;
}
public void setRoleList(List<SysRole> roleList) {
this.roleList = roleList;
}
@Override
public String toString() {
return "sysUser{" +
"id=" + id +
", username='" + username + '\'' +
", email='" + email + '\'' +
", password='" + password + '\'' +
", phoneNum='" + phoneNum + '\'' +
", roleList=" + roleList +
'}';
}
}
2、核心配置文件,配置别名和加在映射文件
<!--2、加载映射文件-->
<mappers>
<!-- <mapper resource="com/lt/dao/UserMapper.xml"/>-->
<!-- <mapper resource="com/lt/dao/OrderMapper.xml"/>-->
<package name="com.lt.dao"/>
</mappers>
<!--类型别名-->
<typeAliases>
<typeAlias type="com.lt.domain.User" alias="user"/>
<typeAlias type="com.lt.domain.Order" alias="order"/>
<typeAlias type="com.lt.domain.SysUser" alias="sysuser"/>
<typeAlias type="com.lt.domain.SysRole" alias="sysrole"/>
</typeAliases>
3、Dao 和 映射文件
package com.lt.dao;
import com.lt.domain.SysUser;
import java.util.List;
public interface SysUserMapper {
public List<SysUser> findAll();
}
<?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.lt.dao.SysUserMapper">
<resultMap id="userMap" type="sysuser">
<id column="userid" property="id"/>
<result column="username" property="username"/>
<result column="email" property="email"/>
<result column="password" property="password"/>
<result column="phoneNum" property="phoneNum"/>
<collection property="roleList" ofType="sysrole">
<id column="roleid" property="id"/>
<id column="roleName" property="roleName"/>
<id column="roleDesc" property="roleDesc"/>
</collection>
</resultMap>
<select id="findAll" resultMap="userMap">
SELECT * from sys_user u,sys_user_role ur,sys_role r where u.id=ur.userId and ur.roleId=r.id
</select>
</mapper>
4、测试、
package com.lt.server;
import com.lt.dao.OrderMapper;
import com.lt.dao.SysUserMapper;
import com.lt.dao.UserMapper;
import com.lt.domain.Order;
import com.lt.domain.SysUser;
import com.lt.domain.User;
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 java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
public class ServerDemo {
public static void main(String[] args) throws IOException {
InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
// sqlSession.getMapper(接口)
// UserMapper mapper = sqlSession.getMapper(UserMapper.class);
//
// List<User> userList = mapper.findAll();
// System.out.println(userList);
// User u =new User();
// u.setId(6);
// u.setUsername("t");
// //u.setPassword("2222");
// List<User> list = mapper.findByCondition(u);
// System.out.println(list);
//
// List<Integer> ids=new ArrayList<Integer>();
// ids.add(1);
// ids.add(2);
// list = mapper.findByIds(ids);
// System.out.println(list);
//
// OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
// List<Order> orderList = orderMapper.findAll();
// System.out.println(orderList);
SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);
List<SysUser> all = sysUserMapper.findAll();
System.out.println(all);
}
}
六、多表配置方式
七、Mybatis 注解开发