1.XML映射器
2.select
Select元素來定義查詢操作
Id：唯一識別符號
- 用來參考這條陳述句，需要和介面的方法名一致
parameterType：引數型別
- 可以不傳，MyBatis會根據TypeHandler自動推斷
resultType：回傳值型別
- 別名或者全類名，如果回傳的是集合，定義集合中元素的型別，不能和resultMap同時使用
1)回傳List
public List<Employee> getEmpsByLastNameLike(String lastName);
<!-- public List<Employee> getEmpsByLastNameLike(String lastName) -->
<select id="getEmpsByLastNameLike" resultType="com.atguigu.mybatis.bean.Employee">
select * from tbl_employee where last_name like #{lastName}
</select>
List<Employee> like = mapper.getEmpsByLastNameLike("%e%");
for(Employee employee : like) {
System.out.println(employee);
}
2)回傳單條Map資料
//回傳一條記錄的map key:列名 value:值 public Map<String,Object> getEmpByIdReturnMap(Integer id);
<!-- public Map<String,Object> getEmpByIdReturnMap(Integer id) -->
<!-- resultType:期望從這條陳述句中回傳結果的類全限定名或別名.因為Map型別已經內置了所以我們只需要填map即可 -->
<select id="getEmpByIdReturnMap" resultType="map">
select * from tbl_employee where id=#{id}
</select>
Map<String, Object> map = mapper.getEmpByIdReturnMap(1); System.out.println(map);
3)回傳多條Map資料
//多條記錄封裝為一個map Map<String, Employee> key:記錄的主鍵 value:記錄封裝后的javabean
//通知mybatis封裝成這個map的時候使用那個屬性作為主鍵
// @MapKey("lastName")
// public Map<String,Employee> getEmpByLastNameLikeReturnMap(String lastName);
@MapKey("id")
public Map<Integer,Employee> getEmpByLastNameLikeReturnMap(String lastName);
<!-- public Map<Integer,Employee> getEmpByLastNameLikeReturnMap(String lastName) -->
<select id="getEmpByLastNameLikeReturnMap" resultType="com.atguigu.mybatis.bean.Employee">
select * from tbl_employee where last_name like #{lastName}
</select>
// Map<String, Employee> map = mapper.getEmpByLastNameLikeReturnMap("%r%");
// System.out.println(map);
Map<Integer, Employee> map = mapper.getEmpByLastNameLikeReturnMap("%r%");
System.out.println(map);
4)結果映射
5)高級結果映射
(1)自動映射
①.開啟駝峰命名(默認規則)
對比開啟mapUnderscoreToCamelCase和不開啟mapUnderscoreToCamelCase的區別.
開啟:Employee [id=1, lastName=plutoo, [email protected], gender=1]
關閉:Employee [id=1, lastName=null, [email protected], gender=1]
通過'開啟'和'關閉'兩個對比，我們清楚到在開啟駝峰命名的情況下，mybatis會自動幫我們進行last_name的封裝.
<settings > <setting name="mapUnderscoreToCamelCase" value="https://www.cnblogs.com/CSAH/p/true"/> </settings>
<!-- public Employee getEmpByid(Integer id) -->
<!-- resultType:使用了emp是因為我們起了別名@Alias("emp") -->
<select id="getEmpById" resultType="emp">
select * from tbl_employee where id = #{id}
</select>
@Test
public void test05() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class);
Employee empById = mapper.getEmpById(1);
System.out.println(empById);
}finally {
openSession.close();
}
}
②.關閉駝峰命名(自定義規則)
對比開啟mapUnderscoreToCamelCase和不開啟mapUnderscoreToCamelCase的區別.
開啟:Employee [id=1, lastName=plutoo, [email protected], gender=1]
關閉:Employee [id=1, lastName=plutoo, [email protected], gender=1]
通過'開啟'和'關閉'兩個對比，我們是否開啟駝峰命名的情況下，對mybatis我們進行last_name的封裝并無影響,因為我們自定義了封裝規則.
public Employee getEmpById(Integer id);
<settings>
<!-- <setting name="mapUnderscoreToCamelCase" value="https://www.cnblogs.com/CSAH/p/true"/> -->
</settings>
<!-- 自定義某個javaBean的封裝規則 -->
<!-- type:自定義規則的Java型別 -->
<!-- id:唯一id方便參考 -->
<resultMap type="com.atguigu.mybatis.bean.Employee" id="MySimpleEmp">
<!-- 指定主鍵列的封裝規則,id定義主鍵底層會有優化 -->
<!-- column:指定列 -->
<!-- property:指定對應的javaBean屬性 -->
<id column="id" property="id"/>
<!-- 定義普通封裝規則 -->
<result column="last_name" property="lastName"/>
<!-- 其他不指定的列會自動封裝.只要寫resultMap就把全部的映射規則補全 -->
<result column="email" property="email"/>
<result column="gender" property="gender"/>
</resultMap>
<!-- public Employee getEmpByid(Integer id) -->
<!-- resultType:使用了emp是因為我們起了別名@Alias("emp") -->
<!-- resultMap:對外部 resultMap 的命名參考，結果映射是 MyBatis 最強大的特性，如果你對其理解透徹，許多復雜的映射問題都能迎刃而解， -->
<!-- resultType 和 resultMap 之間只能同時使用一個 -->
<select id="getEmpById" resultMap="MySimpleEmp">
select * from tbl_employee where id = #{id}
</select>
@Test
public void test05() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class);
Employee empById = mapper.getEmpById(1);
System.out.println(empById);
}finally {
openSession.close();
}
}
(2)resultMap的應用場景
①.查詢Employee的同時查詢員工對應的部門(聯合查詢) Employee===Department
(1.資料表的創建
CREATE TABLE tbl_dept( id INT(11) PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR(255) )
(2.添加外鍵約束
ALTER TABLE tbl_employee ADD COLUMN d_id INT(11); ALTER TABLE tbl_employee ADD CONSTRAINT fk_emp_dept FOREIGN KEY(d_id) REFERENCES tbl_dept(id);
(3.配置resultMap
public Employee getEmpAndDept(Integer id);
<!-- 聯合查詢:級聯屬性封裝結果集 -->
<resultMap type="com.atguigu.mybatis.bean.Employee" id="MyDifEmp">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="gender" property="gender"/>
<result column="did" property="dept.id"/>
<result column="dept_name" property="dept.departmentName"/>
</resultMap>
<!-- public Employee getEmpAndDept(Integer id) -->
<!-- 場景一:查詢Employee的同時查詢員工對應的部門 -->
<!-- 員工與對應部門資訊:id last_name gender d_id did dept_name (private Department dept;) -->
<select id="getEmpAndDept" resultMap="MyDifEmp">
SELECT e.id id,e.last_name last_name,e.gender gender,e.d_id d_id,d.id did,d.dept_name dept_name
FROM tbl_employee e,tbl_dept d
WHERE e.d_id=d.id AND e.id=#{id}
</select>
@Test
public void test05() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class);
Employee empAndDept = mapper.getEmpAndDept(1);
System.out.println(empAndDept);
System.out.println(empAndDept.getDept());
}finally {
openSession.close();
}
}
②.查詢Employee的同時查詢員工對應的部門(association) Employee===Department 
public Employee getEmpAndDept(Integer id);
<!-- 使用association定義關聯的單個物件的封裝規則 -->
<resultMap type="com.atguigu.mybatis.bean.Employee" id="MyDifEmp2">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="gender" property="gender"/>
<!-- association:可以指定聯合的javaBean物件 -->
<!-- property:映射到列結果的欄位或屬性，如果用來匹配的 JavaBean 存在給定名字的屬性，那么它將會被使用， -->
<!-- javaType:JDBC型別.只需要在可能執行插入、更新和洗掉的且允許空值的列上指定 JDBC 型別，這是 JDBC 的要求而非 MyBatis 的要求，如果你直接面向 JDBC 編程，你需要對可能存在空值的列指定這個型別， -->
<!-- <id column="did" property="id"/>|<id column="id" property="id"/> 這兩個property都表示是id,但是column不可一樣否則認定為同一個id -->
<association property="dept" javaType="com.atguigu.mybatis.bean.Department">
<id column="did" property="id"/>
<result column="dept_name" property="departmentName"/>
</association>
</resultMap>
<!-- public Employee getEmpAndDept(Integer id) -->
<!-- 場景一:查詢Employee的同時查詢員工對應的部門 -->
<!-- 員工與對應部門資訊:id last_name gender d_id did dept_name (private Department dept;) -->
<select id="getEmpAndDept" resultMap="MyDifEmp2">
SELECT e.id id,e.last_name last_name,e.gender gender,e.d_id d_id,d.id did,d.dept_name dept_name
FROM tbl_employee e,tbl_dept d
WHERE e.d_id=d.id AND e.id=#{id}
</select>
@Test
public void test05() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class);
Employee empAndDept = mapper.getEmpAndDept(1);
System.out.println(empAndDept);
System.out.println(empAndDept.getDept());
}finally {
openSession.close();
}
}
③.查詢Employee的同時查詢員工對應的部門(association分步查詢)
(1.Department.java
public class Department {
private Integer id;
private String departmentName;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getDepartmentName() {
return departmentName;
}
public void setDepartmentName(String departmentName) {
this.departmentName = departmentName;
}
@Override
public String toString() {
return "Department [id=" + id + ", departmentName=" + departmentName + "]";
}
}
(2.DepartmentMapper.java
public interface DepartmentMapper {
public Department getDeptById(Integer id);
}
(3.EmployeeMapperPlus.java
public interface EmployeeMapperPlus {
public Employee getEmpByIdStep(Integer id);
}
(4.DepartmentMapper.xml
<mapper namespace="com.atguigu.mybatis.dao.DepartmentMapper">
<!-- public Department getDeptById(Integer id) -->
<select id="getDeptById" resultType="com.atguigu.mybatis.bean.Department">
select dept_name departmentName,id from tbl_dept where id=#{id}
</select>
</mapper>
(5.Employee.java
public class Employee {
private Integer id;
private String lastName;
private String email;
private String gender;
private Department dept;
public Department getDept() {
return dept;
}
public void setDept(Department dept) {
this.dept = dept;
}
public Employee() {
super();
// TODO Auto-generated constructor stub
}
public Employee(Integer id, String lastName, String email, String gender) {
this.id = id;
this.lastName = lastName;
this.email = email;
this.gender = gender;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
@Override
public String toString() {
return "Employee [id=" + id + ", lastName=" + lastName + ", email=" + email + ", gender=" + gender + "]";
}
}
(6.EmployeeMapperPlus.xml
<mapper namespace="com.atguigu.mybatis.dao.EmployeeMapperPlus">
<!-- id last_name email gender d_id -->
<resultMap type="com.atguigu.mybatis.bean.Employee" id="MyEmpByStep">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="email" property="email"/>
<result column="gender" property="gender"/>
<!-- association定義關聯物件的封裝規則 -->
<!-- select:表明當前屬性是呼叫select指定的方法查出的結果 -->
<!-- column:指定將一列的值傳給這個方法 -->
<!-- 流程:使用select指定的方法（傳入column指定的這列引數的值）查出物件，并封裝給property指定的屬性-->
<association property="dept"
select="com.atguigu.mybatis.dao.DepartmentMapper.getDeptById"
column="d_id">
</association>
</resultMap>
<!-- 使用association進行分步查詢 -->
<!-- 1、先按照員工id查詢員工資訊 -->
<!-- 2、根據查詢員工資訊中的d_id值去部門表查出部門資訊 -->
<!-- 3、部門設定到員工中 -->
<!-- public Employee getEmpByIdStep(Integer id) -->
<select id="getEmpByIdStep" resultMap="MyEmpByStep">
select * from tbl_employee where id=#{id}
</select>
</mapper>
(7.MyBatisTest.java
@Test
public void test05() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class);
Employee employee = mapper.getEmpByIdStep(1);
System.out.println(employee);
System.out.println(employee.getDept());
}finally {
openSession.close();
}
}
(8.延遲加載
觀察Test中的查詢兩條的結果.通過控制臺我們可以知道，開啟了延遲加載后.我們查詢的結果為單條單條的查詢,按查詢需要給控制臺，不會一下子把查詢結果全部給控制臺.
舊版本的MyBatis需要額外的支持包
– asm-3.3.1.jar
– cglib-2.2.2.jar
<!-- 使用延遲加載(懶加載) (按需加載) --> <!-- 分段查詢的基礎之上加上兩個配置 --> <!-- mybatis-config.xml全域配置中在<settings>中添加一下兩條 --> <!-- <setting name="lazyLoadingEnabled" value="https://www.cnblogs.com/CSAH/p/true"/> --> <!-- <setting name="aggressiveLazyLoading" value="https://www.cnblogs.com/CSAH/p/false"/> -->
<settings > <!--顯示的指定我們需要更改的配置的值，即使默認的，防止版本迭代后帶來的問題 --> <setting name="lazyLoadingEnabled" value="https://www.cnblogs.com/CSAH/p/true"/> <setting name="aggressiveLazyLoading" value="https://www.cnblogs.com/CSAH/p/false"/> </settings>
System.out.println(employee); ------------------------------------------------------------------------------------------- System.out.println(employee); System.out.println(employee.getLastName());
④.查詢部門的時候將部門對應的所有員工資訊也查詢出來
public class Department {
private Integer id;
private String departmentName;
private List<Employee> emps;
}
<mapper namespace="com.atguigu.mybatis.dao.DepartmentMapper">
<!-- private Integer id; private String departmentName; private List<Employee> emps; -->
<!-- did dept_name ||(分割) eid last_name email gender -->
<!-- 嵌套結果集的方式，使用collection標簽定義關聯的集合型別的屬性封裝規則 -->
<resultMap type="com.atguigu.mybatis.bean.Department" id="MyDept">
<id column="did" property="id"/>
<result column="dept_name" property="departmentName"/>
<!-- collection:定義關聯集合型別的屬性的封裝規則 -->
<!-- ofType:指定集合里面元素的型別 -->
<collection property="emps" ofType="com.atguigu.mybatis.bean.Employee">
<!-- 定義集合中元素的封裝規則 -->
<id column="eid" property="id"/>
<result column="last_name" property="lastName"/>
<result column="email" property="email"/>
<result column="gender" property="gender"/>
</collection>
</resultMap>
<!-- public Department getDeptByIdPlus(Integer id) -->
<select id="getDeptByIdPlus" resultMap="MyDept">
SELECT d.id did,d.dept_name dept_name,e.id eid,e.last_name last_name,e.email email,e.gender gender
FROM tbl_dept d
LEFT JOIN tbl_employee e
ON d.id=e.d_id
WHERE d.id=#{id}
</select>
</mapper>
@Test
public void test06() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
DepartmentMapper mapper = openSession.getMapper(DepartmentMapper.class);
Department department = mapper.getDeptByIdPlus(1);
System.out.println(department);
System.out.println(department.getEmps());
}finally {
openSession.close();
}
}
⑤.查詢部門的時候將部門對應的所有員工資訊也查詢出來(分步查詢)
public interface EmployeeMapperPlus {
public List<Employee> getEmpsByDeptId(Integer deptId);
}
<mapper namespace="com.atguigu.mybatis.dao.EmployeeMapperPlus">
<!-- 場景二:查詢部門的時候將部門對應的所有員工資訊也查詢出來;注釋在DepartmentMapper.xml中 -->
<!-- public List<Employee> getEmpsByDeptId(Integer deptId) -->
<select id="getEmpsByDeptId" resultType="com.atguigu.mybatis.bean.Employee">
select * from tbl_employee where d_id=#{deptId}
</select>
</mapper>
<mapper namespace="com.atguigu.mybatis.dao.DepartmentMapper">
<!-- collection分段查詢 -->
<resultMap type="com.atguigu.mybatis.bean.Department" id="MyDeptStep">
<id column="id" property="id"/>
<id column="dept_name" property="departmentName"/>
<collection property="emps"
select="com.atguigu.mybatis.dao.EmployeeMapperPlus.getEmpsByDeptId"
column="{deptId=id}" fetchType="lazy">
</collection>
</resultMap>
<!-- public Department getDeptByIdStep(Integer id) -->
<select id="getDeptByIdStep" resultMap="MyDeptStep">
select id,dept_name departmentName from tbl_dept where id=#{id}
</select>
</mapper>
@Test
public void test06() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
DepartmentMapper mapper = openSession.getMapper(DepartmentMapper.class);
Department deptByIdStep = mapper.getDeptByIdStep(1);
System.out.println(deptByIdStep);
System.out.println(deptByIdStep.getEmps());
}finally {
openSession.close();
}
}
延遲加載
<!-- 場景二:查詢部門的時候將部門對應的所有員工資訊也查詢出來;注釋在DepartmentMapper.xml中 -->
<!-- public List<Employee> getEmpsByDeptId(Integer deptId) -->
<select id="getEmpsByDeptId" resultType="com.atguigu.mybatis.bean.Employee">
select * from tbl_employee where d_id=#{deptId}
</select>
<!-- collection分段查詢 -->
<!-- 擴展:需要將多列的值傳遞 -->
<!-- 將多列的值封裝map傳遞 -->
<!-- column="{key1=column1,key2=column2}" -->
<!-- fetchType="lazy|eager":表示使用延遲加載 lazy:延遲 eager:立即 -->
<resultMap type="com.atguigu.mybatis.bean.Department" id="MyDeptStep">
<id column="id" property="id"/>
<id column="dept_name" property="departmentName"/>
<collection property="emps"
select="com.atguigu.mybatis.dao.EmployeeMapperPlus.getEmpsByDeptId"
column="{deptId=id}" fetchType="lazy">
</collection>
</resultMap>
6).鑒別器
Department deptByIdStep = mapper.getDeptByIdStep(1); System.out.println(deptByIdStep); System.out.println(deptByIdStep.getEmps());
<!-- <discriminator javaType=""></discriminator> -->
<!-- 鑒別器:一個資料庫查詢可能會回傳多個不同的結果集（但總體上還是有一定的聯系的）， 鑒別器元素就是被設計來應對這種情況的 -->
<!-- 如果查出的是女生:就把部門資訊查詢出來,否則不查詢 -->
<!-- 如果是男生:把last_name這一列的值賦值給email -->
<resultMap type="com.atguigu.mybatis.bean.Employee" id="MyEmpDis">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="email" property="email"/>
<result column="gender" property="gender"/>
<!-- column:指定判定的列名 -->
<!-- javaType:列值對應的java型別 -->
<discriminator javaType="string" column="gender">
<!-- resultType:指定封裝的結果型別；不能缺少 -->
<!-- 女生:就把部門資訊查詢出來 -->
<case value="https://www.cnblogs.com/CSAH/p/0" resultType="com.atguigu.mybatis.bean.Employee">
<association property="dept"
select="com.atguigu.mybatis.dao.DepartmentMapper.getDeptById"
column="d_id">
</association>
</case>
<!-- 男生:把last_name這一列的值賦值給email -->
<case value="https://www.cnblogs.com/CSAH/p/1" resultType="com.atguigu.mybatis.bean.Employee">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="last_name" property="email"/>
<result column="gender" property="gender"/>
</case>
</discriminator>
</resultMap>
<select id="getEmpByIdStep" resultMap="MyEmpDis">
select * from tbl_employee where id=#{id}
</select>
3.insert update delete
1)insert
(1)支持自增方式資料庫
若資料庫支持自動生成主鍵的欄位（比如 MySQL和 SQL Server）,則可以設定useGeneratedKeys=”true”，然后再把keyProperty 設定到目標屬性上
public void addEmp(Employee employee);
<!-- public void addEmp(Employee employee) -->
<!-- parameterType:將會傳入這條陳述句的引數的類全限定名或別名，這個屬性是可選的， -->
<!-- 獲取自增主鍵的值 -->
<!-- mysql支持自增主鍵，自增主鍵值的獲取，mybatis也是利用statement.getGenreatedKeys() -->
<!-- useGeneratedKeys:使用自增主鍵獲取主鍵值策略 true|false -->
<!-- keyProperty:指定對應的主鍵屬性;mybatis獲取到主鍵值以后，將值封裝給指定的javaBean屬性 -->
<insert id="addEmp" parameterType="com.atguigu.mybatis.bean.Employee" useGeneratedKeys="true"
keyProperty="id">
insert into tbl_employee(last_name,email,gender) values(#{lastName},#{email},#{gender})
</insert>
(2)不支持自增方式資料庫
而對于不支持自增型主鍵的資料庫（例如Oracle），則可以使用 selectKey 子元素：selectKey 元素將會首先運行，id 會被設定，然后插入陳述句會被呼叫
public void addEmp(Employee employee);
<!-- 獲取非自增主鍵的值:Oracle不支持自增.Oracle使用序列來模擬自增.每次插入的資料的主鍵是從序列中拿到的值.-->
<!-- selectKey:執行查詢Key操作 -->
<!-- keyProperty:指定對應的主鍵屬性;mybatis獲取到主鍵值以后，將值封裝給指定的javaBean屬性 -->
<!-- statementType:MyBatis 支持 STATEMENT，PREPARED 和 CALLABLE 型別的映射陳述句，分別代表 Statement, PreparedStatement 和 CallableStatement 型別， -->
<!-- resultType:結果的型別 -->
<!-- order:可以設定為 BEFORE 或 AFTER，如果設定為 BEFORE，它首先會生成主鍵，設定 keyProperty 再執行插入陳述句，如果設定為 AFTER，先執行插入陳述句，然后 selectKey 中的陳述句 -->
<insert id="addEmp" databaseId="oracle">
<selectKey keyProperty="id" order="BEFORE" resultType="Integer">
<!-- 撰寫查詢主鍵的sql陳述句 -->
<!-- BEFORE -->
select EMPLOYEES_SEQ.nextval from dual
<!-- AFTER：-->
<!-- select EMPLOYEES_SEQ.currval from dual -->
</selectKey>
<!-- 插入時的主鍵是從序列中拿到的 -->
<!-- BEFORE:-->
insert into employees(EMPLOYEE_ID,LAST_NAME,EMAIL) values(#{id},#{lastName},#{email<!-- ,jdbcType=NULL -->})
<!-- AFTER：-->
<!-- insert into employees(EMPLOYEE_ID,LAST_NAME,EMAIL) values(employees_seq.nextval,#{lastName},#{email}) -->
</insert>
2)update
public void updateEmp(Employee employee);
<!-- public void updateEmp(Employee employee) -->
<update id="updateEmp">
update tbl_employee set last_name=#{lastName},email=#{email},gender=#{gender} where id=#{id}
</update>
3)delete
public void deleteEmpById(Integer id);
<!-- public void deleteEmpById(Integer id) -->
<delete id="deleteEmpById">
delete from tbl_employee where id=#{id}
</delete>
4)Test
/**
* 測驗增刪改
* 1、mybatis允許增刪改直接定義一下的型別回傳值 Integer Long Boolean void
* 2、我們需要手動提交資料
* 手動提交:sqlSessionFactory.openSession()
* 自動提交:sqlSessionFactory.openSession(true)
* @throws IOException
*
*/
@Test
public void test03() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
// 測驗添加
Employee employee = new Employee(null, "jerry", "[email protected]", "1");
mapper.addEmp(employee);
System.out.println(employee.getId());
//測驗修改
// Employee employee = new Employee(1, "pluto", "[email protected]", "0");
// mapper.updateEmp(employee);
//測驗洗掉
// mapper.deleteEmpById(2);
openSession.commit();
}finally {
openSession.close();
}
}
4.MyBatis引數
1)單個引數
可以接受基本型別，物件型別，集合型別的值，這種情況MyBatis可直接使用這個引數，不需要經過任何處理
public Employee getEmpById(Integer id);
select * from tbl_employee where id = #{id}
2)多個引數
例外：org.apache.ibatis.binding.BindingException: Parameter 'id' not found. Available parameters are [1, 0, param1, param2]
public Employee getEmpByIdAndLastName(Integer id,String lastName);
select * from tbl_employee where id = #{id} and last_name=#{lastName}
任意多個引數，都會被MyBatis重新包裝成一個Map傳入.
Map的key是param1，param2，0，1…，值就是引數的值.
select * from tbl_employee where id=#{param1} and last_name=#{param2}
(1)命名引數
引數使用@Param起一個名字，MyBatis就會將這些引數封裝進map中，key就是我們自己指定的名字.
- 明確指定封裝引數時map的key；@Param("id");
- 多個引數會被封裝成一個map
- key：使用@Param注解指定的值|value：引數值
- #{指定的key}取出對應的引數值
public Employee getEmpByIdAndLastName(@Param("id")Integer id,@Param("lastName")String lastName);
select * from tbl_employee where id=#{id} and last_name=#{lastName}
@Test
public void test04() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
//1.獲取到sqlSessionFactory但是不會自動提交資料
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
Employee employee = mapper.getEmpByIdAndLastName(1, "plutoo");
System.out.println(employee);
}finally {
openSession.close();
}
}
(2)POJO
當這些引數屬于我們業務POJO時.換句話說，如果多個引數正好是我們業務邏輯的資料模型，我們直接傳遞POJO.
public void getEmpPoJo(Employee employee);
<!-- public Employee getEmpPoJo(Integer id,String email) -->
<select id="getEmpPoJo" resultType="com.atguigu.mybatis.bean.Employee">
update tbl_employee
set last_name=#{lastName},email=#{email},gender=#{gender}
where id=#{id}
</select>
@Test
public void test04() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession(true);
try {
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
Employee employee = new Employee(1, "plutoo", "[email protected]", "1");
mapper.getEmpPoJo(employee);
}finally {
openSession.close();
}
}
(3)Map
我們也可以封裝多個引數為map，直接傳遞.
如果多個引數不是業務模型中的資料，沒有對應的pojo，不經常使用，為了方便，我們也可以傳入map
public Employee getEmpByMap(Map<String,Object> map);
<!-- public Employee getEmpByMap(Map<String,Object> map) -->
<select id="getEmpByMap" resultType="com.atguigu.mybatis.bean.Employee">
select * from tbl_employee where id=#{id} and last_name=#{lastName}
</select>
@Test
public void test04() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
//1.獲取到sqlSessionFactory但是不會自動提交資料
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
// Employee employee = mapper.getEmpByIdAndLastName(1, "pluto");
Map<String, Object> map = new HashMap<>();
map.put("id", 1);
map.put("lastName", "pluto");
Employee employee = mapper.getEmpByMap(map);
mapper.getEmpByMap(map);
System.out.println(employee);
}finally {
openSession.close();
}
}
(4)To:
如果多個引數不是業務模型中的資料，沒有對應的pojo，不經常使用，為了方便，我們也可以傳入map
(5)引數舉例
public Employee getEmp(@Param("id")Integer id,String lastName);
取值：id==>#{id/param1} lastName==>#{param2}
public Employee getEmp(Integer id,@Param("e")Employee emp);
取值：id==>#{param1} lastName===>#{param2.lastName/e.lastName}
##特別注意：如果是Collection（List、Set）型別或者是陣列，
也會特殊處理，也是把傳入的list或者陣列封裝在map中，
key：Collection（collection）,如果是List還可以使用這個key(list)
陣列(array)
public Employee getEmpById(List<Integer> ids);
取值：取出第一個id的值： #{list[0]}
(6)查看原始碼 MyBati怎么處理引數
引數多時會封裝map，為了不混亂，我們可以使用@Param來指定封裝時使用的key
public Object getNamedParams(Object[] args) {
final int paramCount = names.size();
//1、引數為null直接回傳
if (args == null || paramCount == 0) {
return null;
//2、如果只有一個元素，并且沒有Param注解；args[0]：單個引數直接回傳
} else if (!hasParamAnnotation && paramCount == 1) {
return args[names.firstKey()];
//3、多個元素或者有Param標注
} else {
final Map<String, Object> param = new ParamMap<Object>();
int i = 0;
//4、遍歷names集合；{0=id, 1=lastName,2=2}
for (Map.Entry<Integer, String> entry : names.entrySet()) {
//names集合的value作為key; names集合的key又作為取值的參考args[0]:args【1，"Tom"】:
//eg:{id=args[0]:1,lastName=args[1]:Tom,2=args[2]}
param.put(entry.getValue(), args[entry.getKey()]);
// add generic param names (param1, param2, ...)param
//額外的將每一個引數也保存到map中，使用新的key：param1...paramN
//效果：有Param注解可以#{指定的key}，或者#{param1}
final String genericParamName = GENERIC_NAME_PREFIX + String.valueOf(i + 1);
// ensure not to overwrite parameter named with @Param
if (!names.containsValue(genericParamName)) {
param.put(genericParamName, args[entry.getKey()]);
}
i++;
}
return param;
}
}
}
(@Param("id")Integer id,@Param("lastName")String lastName);
ParamNameResolver決議引數封裝map的
//names：{0=id, 1=lastName}；構造器的時候就確定好了
流程：
①.獲取每個標了param注解的引數的@Param的值:id，lastName賦值給name
②.每次決議一個引數給map中保存資訊：（key：引數索引，value：name的值）
- name的值：
- 標注
- param注解：注解的值
- 無標注：
- 全域配置：useActualParamName(jdk1.8):name=引數名
- name=map.size()；相當于當前元素的索引
(7)引數值的獲取
#{}：可以獲取map中的值或者pojo物件屬性的值；
${}：可以獲取map中的值或者pojo物件屬性的值；
select * from tbl_employee where id=${id} and last_name=#{lastName}
Preparing: select * from tbl_employee where id=2 and last_name=?
select * from tbl_employee where id=#{id} and last_name=#{lastName}
Preparing: select * from tbl_employee where id=? and last_name=?
①.區別
#{}:是以預編譯的形式，將引數設定到sql陳述句中；PreparedStatement；防止sql注入
${}:取出的值直接拼裝在sql陳述句中；會有安全問題；
大多情況下，我們去引數的值都應該去使用#{}；
原生jdbc不支持占位符的地方我們就可以使用${}進行取值
比如分表、排序，，，；按照年份分表拆分
select * from ${year}_salary where xxx;
select * from tbl_employee order by ${f_name} ${order}
<!-- public Employee getEmpByMap(Map<String,Object> map) -->
<select id="getEmpByMap" resultType="com.atguigu.mybatis.bean.Employee">
select * from #{tableName} where id=#{id} and last_name=#{lastName}
</select>
<!-- public Employee getEmpByMap(Map<String,Object> map) -->
<select id="getEmpByMap" resultType="com.atguigu.mybatis.bean.Employee">
select * from ${tableName} where id=#{id} and last_name=#{lastName}
</select>
@Test
public void test04() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
//1.獲取到sqlSessionFactory但是不會自動提交資料
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
Map<String, Object> map = new HashMap<>();
map.put("id", 2);
map.put("lastName", "plutoo");
map.put("tableName", "tbl_employee");
Employee employee = mapper.getEmpByMap(map);
mapper.getEmpByMap(map);
System.out.println(employee);
}finally {
openSession.close();
}
}
②.#{}用法
jdbcType
在我們資料為null的時候，有些資料庫可能不能識別mybatis對null的默認處理，比如Oracle(報錯)
JdbcType OTHER:無效的型別.因為mybatis對所有的null都映射的是原生Jdbc的OTHER型別，oracle不能正確處理.
全域配置中:jdbcTypeForNull=OTHER；oracle不支持；
兩種辦法
- #{email,jdbcType=OTHER};
- jdbcTypeForNull=NULL
<setting name="jdbcTypeForNull" value="https://www.cnblogs.com/CSAH/p/NULL"/>
參考檔案:https://mybatis.org/mybatis-3/zh/sqlmap-xml.html#
https://mybatis.org/mybatis-3/zh/configuration.html#settings
|
<settings> <!-- <setting name="mapUnderscoreToCamelCase" value="https://www.cnblogs.com/CSAH/p/true"/> --> </settings> |
|
<!-- 自定義某個javaBean的封裝規則 --> <!-- type:自定義規則的Java型別 --> <!-- id:唯一id方便參考 --> <resultMap type="com.atguigu.mybatis.bean.Employee" id="MySimpleEmp"> <!-- 指定主鍵列的封裝規則,id定義主鍵底層會有優化 --> <!-- column:指定列 --> <!-- property:指定對應的javaBean屬性 --> <id column="id" property="id"/>
<!-- 定義普通封裝規則 --> <result column="last_name" property="lastName"/>
<!-- 其他不指定的列會自動封裝.只要寫resultMap就把全部的映射規則補全 --> <result column="email" property="email"/> <result column="gender" property="gender"/> </resultMap>
<!-- public Employee getEmpByid(Integer id) --> <!-- resultType:使用了emp是因為我們起了別名@Alias("emp") --> <!-- resultMap:對外部 resultMap 的命名參考，結果映射是 MyBatis 最強大的特性，如果你對其理解透徹，許多復雜的映射問題都能迎刃而解， --> <!-- resultType 和 resultMap 之間只能同時使用一個 --> <select id="getEmpById" resultMap="MySimpleEmp"> select * from tbl_employee where id = #{id} </select> |
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/162675.html
標籤：Java