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文件
運行結果：