MyBatis入门 --- SQL映射文件（添加、修改、删除用户信息、联表查询，获取指定用户的相关信息及其地址）
一、SQL映射的xml文件
- MyBatis真正的强大在于映射语句，专注于SQL，功能强大，SQL映射的配置却是相当于简单
- SQL映射文件的几个顶级元素（按照定义的顺序）
mapper - namespace
- cache - 配置给定命名空间的缓存
- cache-ref - 从其他命名空间引用缓存配置
- resultMap - 用来描述数据库结果集和对象的对应关系
- sql - 可以重用的SQL块，也可以被其他语句引用
- insert - 映射插入语句
- update - 映射更新语句
- delete - 映射删除语句
- select - 映射查询语句
mapper元素
mapper
namespace：命名空间
namespace和子元素的id联合保证唯一，区别不同的mapper
绑定DAO接口
namespace的命名必须跟某个接口同名
接口中的方法与映射文件中的SQL语句id一一对应
select
select是MyBatis中最常用的元素之一
select语句有很多属性可以详细配置每一条语句
id
命名空间中唯一的标识符
接口中的方法与映射文件中的SQL语句id一一对应
parameterType
传入SQL语句的参数类型
基础数据类型：
int、String、Date等
只能传入一个，通过#{参数名}即可获取传入的值
复杂数据类型
Java实体类、Map等
通过#{属性名}或者#{map的keyName}即可获取传入值
resultType：直接表示返回类型
SQL语句返回值类型的完整类名或别名
resultMap：对外部resultMap的引用
应用场景：
数据库字段信息与对象属性不一致
复杂的联合查询，自由控制映射结果
二、添加、修改、删除用户信息
编写user实体类 User.java
package net.togogo.bean;
import java.io.Serializable;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
public class User {
private Integer id; //id
private String userCode; //用户编码
private String userName; //用户名称
private String userPassword; //用户密码
private Integer gender; //性别
private Date birthday; //出生日期
private String phone; //电话
private String address; //地址
private Integer userRole; //用户角色
private Integer createdBy; //创建者
private Date creationDate; //创建时间
private Integer modifyBy; //更新者
private Date modifyDate; //更新时间
//年龄
private int age;
private String sex;
//用户和角色是1对1的关系
private Role role;
//用户和收货地址是1对多的关系
private List<Address> addressList;
public Role getRole() {
return role;
}
public void setRole(Role role) {
this.role = role;
}
public List<Address> getAddressList() {
return addressList;
}
public void setAddressList(List<Address> addressList) {
this.addressList = addressList;
}
public int getAge() {
//日历对象
Calendar instance = Calendar.getInstance();
//获取到当前的年月日期
int nowYear = instance.get(Calendar.YEAR);
int nowMonth = instance.get(Calendar.MONTH);
int nowDayOfMonth = instance.get(Calendar.DAY_OF_MONTH);
//给日历设置时间
instance.setTime(this.getBirthday());
//获取出生日期的年月日
int birYear = instance.get(Calendar.YEAR);
int birMonth = instance.get(Calendar.MONTH);
int birDayOfMonth = instance.get(Calendar.DAY_OF_MONTH);
age = nowYear - birYear;
if(nowMonth <= birMonth){
if(nowMonth == birMonth){
if(nowDayOfMonth < birDayOfMonth){
age --;
}
}else {
age--;
}
}
return age;
}
public String getSex() {
if(this.getGender() ==1){
return "男";
}else{
return "女";
}
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUserCode() {
return userCode;
}
public void setUserCode(String userCode) {
this.userCode = userCode;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getUserPassword() {
return userPassword;
}
public void setUserPassword(String userPassword) {
this.userPassword = userPassword;
}
public Integer getGender() {
return gender;
}
public void setGender(Integer gender) {
this.gender = gender;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public Integer getUserRole() {
return userRole;
}
public void setUserRole(Integer userRole) {
this.userRole = userRole;
}
public Integer getCreatedBy() {
return createdBy;
}
public void setCreatedBy(Integer createdBy) {
this.createdBy = createdBy;
}
public Date getCreationDate() {
return creationDate;
}
public void setCreationDate(Date creationDate) {
this.creationDate = creationDate;
}
public Integer getModifyBy() {
return modifyBy;
}
public void setModifyBy(Integer modifyBy) {
this.modifyBy = modifyBy;
}
public Date getModifyDate() {
return modifyDate;
}
public void setModifyDate(Date modifyDate) {
this.modifyDate = modifyDate;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", userCode='" + userCode + '\'' +
", userName='" + userName + '\'' +
", userPassword='" + userPassword + '\'' +
", gender=" + gender +
", birthday=" + birthday +
", phone='" + phone + '\'' +
", address='" + address + '\'' +
", userRole=" + userRole +
", createdBy=" + createdBy +
", creationDate=" + creationDate +
", modifyBy=" + modifyBy +
", modifyDate=" + modifyDate +
", age=" + this.getAge() +
", sex='" + this.getSex() + '\'' +
'}';
}
}
Role实体类
UserMapper.xml
<?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">
<!--namespace 命名空间-->
<mapper namespace="net.togogo.dao.UserMapper">
<!--添加用户-->
<insert id="insertUser" parameterType="net.togogo.bean.User">
insert into smbms_user
(userCode,userName,userPassword,gender,birthday,phone,address,userRole)
values (#{userCode},#{userName},#{userPassword},#{gender},#{birthday},#{phone},#{address},#{userRole})
</insert>
<!--修改用户-->
<update id="updateUserById" parameterType="net.togogo.bean.User">
update smbms_user
set userName = #{userName}
where id = #{id}
</update>
<!--删除用户-->
<delete id="deleteUserById" parameterType="net.togogo.bean.User">
delete from smbms_user
where id = #{id}
</delete>
</mapper>
UserMapper.java
package net.togogo.dao; import net.togogo.bean.User; import org.apache.ibatis.annotations.Mapper; import java.util.List; import java.util.Map; @Mapper public interface UserMapper {/*添加用户*/ public int insertUser(User user); /**修改用户 根据用户id**/ public int updateUserById(User user); /**删除用户**/ public int deleteUserById(int i); }
MybatisUtil文件
编写测试文件Test.java
package net.togogo; import net.togogo.bean.User; import net.togogo.dao.UserMapper; import net.togogo.util.MybatisUtil; 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.Before; import java.io.IOException; import java.io.InputStream; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; public class Test { @Before public void doInit(){ MybatisUtil.init(); }//添加用户信息 @org.junit.Test public void test7(){ SqlSession sqlSession = MybatisUtil.openSqlSessionDS(); //通过getMapper方法进行查询 UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user = new User(); user.setUserName("陈丽端"); user.setUserCode("chenliduan"); user.setAddress("广州"); user.setBirthday(new Date()); user.setGender(1); user.setUserRole(3); int count = mapper.insertUser(user); System.out.println(count); sqlSession.commit(); } //修改用户信息 @org.junit.Test public void test8(){ SqlSession sqlSession = MybatisUtil.openSqlSessionDS(); //通过getMapper方法进行修改 UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user = new User(); user.setUserName("张三"); user.setId(30); int count = mapper.updateUserById(user); System.out.println(count); sqlSession.commit(); } //删除用户信息 @org.junit.Test public void test9(){ SqlSession sqlSession = MybatisUtil.openSqlSessionDS(); //通过getMapper方法进行查询 UserMapper mapper = sqlSession.getMapper(UserMapper.class); int count = mapper.deleteUserById(30); System.out.println(count); sqlSession.commit(); }
运行结果：
数据库新增一条用户信息
修改用户姓名
删除用户信息
三、联表查询，获取指定用户的相关信息及其地址
编写Address实体类
编写UserMapper.xml文件
UserMapper.java文件
Test.java文件
运行结果：