文章目录
1. 工具类的使用
我们在第一个实例中,有很多的工作实际上是重复的,所以我们可以将这些工作抽离出来包装成工具类,以供后期使用。
MyBatisUtils.java
package com.sdnu.utils;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
public class MyBatisUtils {
private static SqlSessionFactory factory = null;
static{
String config = "mybatis.xml";
SqlSessionFactoryBuilder builder = null;
try{
InputStream in = Resources.getResourceAsStream(config);
builder = new SqlSessionFactoryBuilder();
factory = builder.build(in);
}catch(Exception e){
e.printStackTrace();
}
}
public static SqlSession getSqlSession(){
SqlSession sqlSession = null;
if(factory != null){
sqlSession = factory.openSession();
}
return sqlSession;
}
}
于是我们可以使用工具类使得我们的程序变得简单。
MyApp2.java
package com.sdnu;
import com.sdnu.domain.Student;
import com.sdnu.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import java.io.IOException;
import java.util.List;
public class MyApp2 {
public static void main(String[] args) throws IOException {
SqlSession sqlSession = MyBatisUtils.getSqlSession();
//6.指定要执行的sql语句的标识。 sql映射文件中的namespace + "." + 标签的id值
String sqlId = "com.sdnu.dao.StudentDao" + "." + "selectStudents";
//7.执行sql语句,通过sqlId找到语句
List<Student> studentList = sqlSession.selectList(sqlId);
//8.输出结果
studentList.forEach(stu -> System.out.println(stu));
//9.关闭SqlSession对象
sqlSession.close();
}
}
2.传统MyBatis的使用
我们使用一个类去实现StudentDao。
StudentDao.impl
package com.sdnu.dao.Impl;
import com.sdnu.dao.StudentDao;
import com.sdnu.domain.Student;
import com.sdnu.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import java.util.List;
public class StudentDaoImpl implements StudentDao {
@Override
public List<Student> selectStudents() {
SqlSession sqlSession = MyBatisUtils.getSqlSession();
String sqlId = "com.sdnu.dao.StudentDao" + "." + "selectStudents";
List<Student> students = sqlSession.selectList(sqlId);
sqlSession.close();
return students;
}
@Override
public Integer insertStudents(Student student) {
SqlSession sqlSession = MyBatisUtils.getSqlSession();
String sqlId = "com.sdnu.dao.StudentDao" + "." + "insertStudents";
Integer number = sqlSession.insert(sqlId, student);
sqlSession.commit();
sqlSession.close();
return number;
}
}
然后再写一个测试类。
TestMybatis.class
package com.sdnu;
import com.sdnu.dao.Impl.StudentDaoImpl;
import com.sdnu.dao.StudentDao;
import com.sdnu.domain.Student;
import org.junit.Test;
import java.util.List;
public class TestMybatis {
@Test
public void testSelectStudents() {
StudentDao dao = new StudentDaoImpl();
List<Student> students = dao.selectStudents();
for(Student stu : students) {
System.out.println("stu-->" + stu);
}
}
@Test
public void testInsertStudents() {
StudentDao dao = new StudentDaoImpl();
Student stu = new Student();
stu.setId(202110);
stu.setName("ceng");
stu.setEmail("202110@gmail.com");
stu.setAge(18);
Integer number = dao.insertStudents(stu);
System.out.println("修改的纪录数是 :" + number);
}
}
3.MyBatis的动态代理
我们观察上面的测试程序,我们通过分析发现:
(1)dao对象,类型是StudentDao,全限定名称是com.sdnu.dao.StudentDao,和namespace是一样的。
(2)方法名称,selectStudents,这个方法就是mapper文件中的id值 selectStudents。
(3)通过dao中的方法的返回值也可以确定MyBatis要调用的sqlSession()方法。如果返回的是List, 调用的是SqlSession.selectList()方法。如果返回的是int,或是是非List的,看mapper文件中的标签是,就会调用SqlSession的insert, update等方法。
MyBatis的动态代理:mybatis根据dao的方法调用,获取执行sql语句的信息。mybatis根据你的dao接口,创建出一个dao接口的实现类,并创建这个对象。完成SqlSession调用方法,并访问数据库。
testSelectStudents
package com.sdnu;
import com.sdnu.dao.StudentDao;
import com.sdnu.domain.Student;
import com.sdnu.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class TestMybatis {
@Test
public void testSelectStudents() {
SqlSession sqlSession = MyBatisUtils.getSqlSession();
StudentDao dao = sqlSession.getMapper(StudentDao.class);
List<Student> students = dao.selectStudents();
for(Student student : students) {
System.out.println(student);
}
}
@Test
public void testInsertStudents() {
SqlSession sqlSession = MyBatisUtils.getSqlSession();
StudentDao dao = sqlSession.getMapper(StudentDao.class);
Student student = new Student();
student.setId(202109);
student.setName("shang");
student.setEmail("202109@qq.com");
student.setAge(21);
Integer number = dao.insertStudents(student);
sqlSession.commit();
System.out.println(number);
}
}
有了动态代理,我们之后只需要修改我们的StudentDao和StudentDao.xml,以及添加调用程序就可以简化使用我们的MyBatis程序。
4.Parameter Type的使用
StudentDao.java
package com.sdnu.dao;
import com.sdnu.domain.Student;
import java.util.List;
public interface StudentDao {
List<Student> selectStudents();
Student selectStudentById(Integer id);
Integer insertStudents(Student student);
}
StudentDao.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">
<mapper namespace="com.sdnu.dao.StudentDao">
<select id="selectStudentById" resultType="com.sdnu.domain.Student">
select id, name, email, age from student where id=#{id}
</select>
</mapper>
testSelectStudents.java
package com.sdnu;
import com.sdnu.dao.StudentDao;
import com.sdnu.domain.Student;
import com.sdnu.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
public class TestMybatis {
@Test
public void testSelectStudents() {
SqlSession sqlSession = MyBatisUtils.getSqlSession();
StudentDao dao = sqlSession.getMapper(StudentDao.class);
Student student = dao.selectStudentById(202104);
System.out.println(student);
}
@Test
public void testInsertStudents() {
}
}
5.传多个参数
5.1方法一:命名参数
当Dao接口方法需要多个参数,需要通过名称使用参数,在方法形参前面加入@Param(“自定义参数名”),mapper文件使用#(自定义参数名)
接口方法
List<Student> selectMultiParam(@Param("myName")String name, @Param("myAge")Integer age);
mapper文件
<mapper>
<select id="selectMultiParam" resultType="com.sdnu.domain.Student">
select id, name, email, age from student where name=#{myName} OR age=#{myAge}
</select>
</mapper>
@Test
public void testInsertStudents() {
SqlSession sqlSession = MyBatisUtils.getSqlSession();
StudentDao dao = sqlSession.getMapper(StudentDao.class);
List<Student> student = dao.selectMultiParam("liu", 22);
for(Student stu : student) {
System.out.println(stu);
}
}
5.2 方法二:使用对象传参
StudentDao.java
List<Student> selectMultiParam2(Student student);
StudentDao.xml
<mapper>
<select id="selectMultiParam2" resultType="com.sdnu.domain.Student">
SELECT id, name, email, age from student where id=#{id} OR age=#{age}
</select>
</mapper>
@Test
public void testSelectStudents2(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
StudentDao dao = sqlSession.getMapper(StudentDao.class);
Student student = new Student();
student.setId(202101);
student.setAge(30);
List<Student> studentLists = dao.selectMultiParam2(student);
for(Student stu: studentLists) {
System.out.println(stu);
}
}
5.3方法三:使用位置传参
接口方法
List<Student> selectMultiParam3(String name, Integer age);
mapper文件
<mapper>
<select id="selectMultiParam3" resultType="com.sdnu.domain.Student">
SELECT id, name, email, age from student where name=#{arg0} OR age=#{arg1}
</select>
<mapper>
测试方法
@Test
public void testSelectStudents3(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
StudentDao dao = sqlSession.getMapper(StudentDao.class);
List<Student> studentLists = dao.selectMultiParam3("PPP", 30);
for(Student stu: studentLists) {
System.out.println(stu);
}
}
5.4方法四:Map传参
接口方法
List<Student> selectMultiParam4(Map<String, Object> map);
mapper文件
<select id="selectMultiParam4" resultType="com.sdnu.domain.Student">
SELECT id, name, email, age from student where name=#{nameKey} OR age=#{ageKey}
</select>
测试方法
@Test
public void testSelectStudents4(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
StudentDao dao = sqlSession.getMapper(StudentDao.class);
Map<String, Object> map = new HashMap<>();
map.put("nameKey", "xu");
map.put("ageKey", 21);
List<Student> studentLists = dao.selectMultiParam4(map);
for(Student stu: studentLists) {
System.out.println(stu);
}
}
注意:我们推荐使用方法一和方法二,方法三和方法四不推荐使用。
5.5 # 和 $
#
:#{}可以有效防止sql注入,类似于JDBC中讲的PreparedStatement。
$
: ${}则可能导致sql注入成功, 类似于JDBC中讲的Statement。
5.6 使用占位符替换列名
接口方法
List<Student> selectTest(@Param("colName")String colName);
mapper文件
<select id="selectTest" resultType="com.sdnu.domain.Student">
SELECT * from student order by ${colName}
</select>
测试方法
@Test
public void selectTest(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
StudentDao dao = sqlSession.getMapper(StudentDao.class);
List<Student> studentLists = dao.selectTest("age");
for(Student stu: studentLists) {
System.out.println(stu);
}
}
6.封装MyBatis结果
6.1 resultType
执行 sql 得到 ResultSet 转换的类型,使用类型的完全限定名或别名。 注意如果返回的是集合,那应该设置为集合包含的类型,而不是集合本身。resultType 和 resultMap,不能同时使用。
6.1.1简单类型
接口方法:
int countStudent();
mapper文件:
<select id="countStudent" resultType="Java.lang.Integer">
select count(*) from student
</select>
补充: Java.lang.Integer的别名是int 我们自定义的类也可以定义别名,我们只需要在mybatis的主配置文件中加入如下:
测试方法:
public void testReturnInt() {
int count = studentDao.countStudent();
System.out.println("学生" + count);
}
6.1.2对象类型
接口方法:
Student selectById(@Param("myId") int id);
mapper文件:
<select id="selectById" resultType="com.sdnu.domain.Student">
select name, email, age from student where id = #{myId};
</select>
测试方法:
public void selectById() {
Student student = dao.selectById(202109);
System.out.println(student);
}
6.1.3 map
接口方法:
Map<Object, Object> selectMap(@Param("myId") int id);
mapper文件:
<select id="selectMap" resultType="java.util.HashMap">
select name, email, age from student where id = #{myId};
</select>
测试方法:
public void selectMap() {
Map<Object, Object> map = dao.selectMap(202108);
System.out.println(map);
}
6.2 resultMap
接口方法:
List<Student> selectAllStudents();
mapper文件:
<resultMap id="resultMap" type="com.sdnu.domain.Student">
<!--列名和java属性的关系-->
<!--注解列,使用id标签
column: 列名
property: java类型的属性名
-->
<id column="id" property="id"/>
<!--非主键列,使用result-->
<result column="name" property="email"/>
<result column="email" property="name"/>
<result column="age" property="age"/>
</resultMap>
<select id="selectAllStudents" resultMap="resultMap">
select id, name, email, age from student
</select>
(我们有意将name和email交互值)
测试方法:
@Test
public void selectAllStudents() {
SqlSession sqlSession = MyBatisUtils.getSqlSession();
StudentDao dao = sqlSession.getMapper(StudentDao.class);
List<Student> students = dao.selectAllStudents();
for(Student stu : students) {
System.out.println(stu);
}
}
我们分析结果发现name的value与email交互了。
6.3 列名和属性名不同的第二种方式
我们使用resultMap能轻易使得解决列名和属性名不同,当然我们使用resultType也能实现,只需在mapper文件中指定列名即可。
<select id="selectStudent" result="com.sdnu.domain.Student">
select id as stuId, name as stuName, email as stuEmail from student
</select>
6.4 like查询的两种方案
6.4.1 Java代码指定like内容
接口方法:
List<Student> selectLike(String name);
mapper文件:
<select id="selectLike" resultType="com.sdnu.domain.Student">
select id, name, email, age from student where name like #{name}
</select>
测试方法:
@Test
public void selectLike() {
SqlSession sqlSession = MyBatisUtils.getSqlSession();
StudentDao dao = sqlSession.getMapper(StudentDao.class);
String name = "徐%";
List<Student> students = dao.selectLike(name);
for(Student stu : students) {
System.out.println(stu);
}
}
6.4.2 在mapper文件中拼接like内容
接口方法:
List<Student> selectLike2(String name);
mapper文件:
<select id="selectLike2" resultType="com.sdnu.domain.Student">
select id, name, email, age from student where name like #{name} "%"
</select>
测试方法:
public void selectLike2() {
SqlSession sqlSession = MyBatisUtils.getSqlSession();
StudentDao dao = sqlSession.getMapper(StudentDao.class);
List<Student> students = dao.selectLike2("徐");
for(Student stu : students) {
System.out.println(stu);
}
}
作者:Beyong
出处:Beyong博客
github地址:https://github.com/beyong2019
本博客中未标明转载的文章归作者Beyong有,欢迎转载,但未经作者同意必须保留此段声明,且在文章明显位置给出原文连接,否则保留追究法律责任的权利。