第一章:框架概述
1.三层架构
界面层:和用户打交道,接受用户的请求参数,显示处理结果的。(jsp,servlet,html都是属于界面层的)
业务逻辑层:接受了界面层传递的数据,计算逻辑,调用数据库,获取数据
数据访问层:就是访问数据库,执行数据库的查询、修改、删除等等。
2.三层对应的包:
界面层:controler包(servlet)
业务逻辑层:service包(各种XXXservice类)
数据访问层:dao包(各种XXXDao类)
三层中类的交互:
用户使用界面层——业务逻辑层——数据库访问层(持久层)——数据库(Mysql)
3.三层对应的处理框架:
界面层——servlet——Spring MVC
业务逻辑层——service类——Spring
数据访问层——dao类——Mybatis
4.框架:
框架是一个模块:
1.框架中定义好了一些功能,这些功能是可用的
2.可以在项目中加入自己的功能,这些功能可以利用框架中写好的功能
可以把框架看作为一个半成品的软件,定义好了一些基础的功能,需要加入你的功能后就是完整的
基础的功能是可重复使用的,可升级的
框架特点:
1.框架一般不是全能的,不能做所有的事情
2.框架是针对某一个领域有效。特长在某一个方面,比如Mybatis做数据库操作强,但是不能做其他的
3.框架可看作一个软件
5.Mybatis框架
早期叫做ibatis,代码在GitHub
Mybatis是 Mybatis SQL Mapper Framework for Java(sql映射框架)
1)sql maper:sql映射
可以把数据库表中的一行数据映射为一个Java对象,也可以说可以把一行数据看作一个对象
操作这个对象就相当于操作表中的数据
2)Data Access Objects(DAOs):数据访问功能,对数据库进行增删改查
6.Mybatis提供了哪些功能?
1.提供了创建Connection,Statement,ResultSet的能力,而不用开发人员创建这些对象
2.提供了执行sql语句的能力,不用你执行sql
3.提供了循环数据记录集,把sql查询的结果转换为Java对象,List集合的能力(不需要再遍历输出数据)
4.提供了关闭资源的能力,不需要自行关闭Connection,Statement,ResultSet
开发人员只需提供SQL语句——Mybatis处理SQL——开发人员拿到List集合或Java对象(表中的数据)
7.总结
Mybatis是一个SQL框架,可以看作是一个增强的,封装好的JDBC,开发人员只需集中精力写SQL就行,不必关心Connection,Statement,ResultSet的创建,销毁以及SQL的执行
模板:
1.规定好了一些条款,内容
2.可以加入自己的东西
第二章:Mybatis入门
1.Myabtis的使用准备
官方中文文档: https://mybatis.org/mybatis-3/zh/getting-started.html
2.实现步骤:
2.1.新建Student表
建表语句:
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int(11) NOT NULL,
`name` varchar(80) DEFAULT NULL,
`email` varchar(100) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2.2加入maven的mybatis坐标,MySQL驱动的坐标
打开pom.xml文件,添加mybatis和MySQL驱动的依赖
<!-- Mybatis依赖 -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.2</version>
</dependency>
<!-- JDBC依赖 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.9</version>
</dependency>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
<filtering>true</filtering>
</resource>
</resources>
2.3.创建实体类
package com.bjpowernode.domain;
//推荐和表名称一样,方便记忆
public class Student {
//定义属性,目前要求属性名和列名一样
private Integer id;
private String name;
private String email;
private Integer age;
//Setter和Getter方法.......
//Setter和Getter方法.......
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", email='" + email + '\'' +
", age=" + age +
'}';
}
}
2.4.创建持久层dao接口,定义操作数据库的方法
package com.bjpowernode.dao;
import com.bjpowernode.domain.Student;
import java.util.List;
//接口操作student表
public interface StudentDao {
//查询student表中的所有数据
public List<Student> selectStudents();
}
2.5.创建一个mybatis使用的配置文件
叫做SQL映射文件:写SQL语句的,一般一个表一个SQL映射文件,这个文件是xml文件
写在接口所在的目录中,名称需和接口名称保持一致:StudentDao.xml
基本的配置在https://mybatis.org/mybatis-3/zh/getting-started.html 探究已映射的 SQL 语句
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.bjpowernode.dao.StudentDao">
<select id="selectStudents" resultType="com.bjpowernode.domain.Student">
select id,name,email,age from student order by id
</select>
</mapper>
内容解释说明:
1.指定约束文件
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
mybatis-3-mapper.dtd是约束文件
2.约束文件的作用:限制,检查在当前文件中出现的标签,属性必须符合mybatis的标准
3.mapper 是当前文件的跟标签,必须的
namespace:叫做命名空间,唯一值的,可以自定义的字符串。
要求使用dao接口的全限定名称
select:表示查询操作
id:你要执行的sql语法的唯一标识,mybatis会使用这个id的值来找到执行的sql语句
可以定义,但是要求你使用接口中的方法的名称
resultType:表示结果类型的,是sql语句执行后的到的ResultSet,遍历这个ResultSet得到的
java对象类型值写的是类型的全限定名称
在当前的文件中,标签里可以使用以下特定的标签来执行特定的操作
<select>:表示执行查询 select语句
<update>:表示更新数据库的操作,就是在<update>标签中 写的是update sql语句
<insert>:表示插入,放的是insert语句
<delete>:表示删除,放的是delete语句
2.6.创建mybatis的主配置文件
一个项目就一个主配置文件,主配置文件提供了数据库的连接信息和SQL映射文件的位置信息
在Maven项目中,main文件夹下创建resources文件夹并设置为Test resources Root文件夹
在resources文件夹中创建mybatis的主配置文件(mybatis.xml)
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--环境配置:数据库的连接信息
default必须和某个environment的id值一样
告诉mybatis使用那个数据库的连接信息。也就是访问那个数据库
-->
<environments default="mydev">
<!--environment:一个数据库信息的配置,称作环境
id:一个唯一值,自定义的,表示环境的名称
-->
<environment id="mydev">
<!--transactionManager:mybatis的事务类型
type:JDBC表示使用jdbc中的Connection对象的commit,rollb事务处理
-->
<transactionManager type="JDBC"/>
<!--
dataSource:表示数据源,链接数据库的
type:表示数据源的类型,POOLED表示使用连接池
-->
<dataSource type="POOLED">
<!--
driver,user,username,password 是固定的,不能自定义
-->
<!--数据库的驱动类名-->
<property name="driver" value="com.mysql.jdbc.Driver"/>
<!--连接数据库的url字符串-->
<property name="url" value="jdbc:mysql://localhost:3306/java"/>
<!--访问数据库的用户名-->
<property name="username" value="root"/>
<!--密码-->
<property name="password" value="333"/>
</dataSource>
</environment>
</environments>
<!--指定sql mapper(sql映射文件)的位置-->
<mappers>
<!--一个mapper标签指定一个文件的位置
从类路径开始的路径信息 target/classes(类路径)
-->
<!--有几个xml文件写几个mapper标签-->
<mapper resource="com/bjpowernode/dao/StudentDao.xml"/>
</mappers>
</configuration>
<!--
mybatis的主配置文件:主要定义了数据库的配置信息,sql映射文件的位置
1.约束文件:
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
mybatis-3-config.dtd:约束文件的名称
2.configuration:根标签
-->
2.7.创建使用mybatis的类,通过mybatis访问数据库
package com.bjpowernode;
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.example.domain.Student;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class MyApp {
public static void main(String[] args) throws IOException {
String config = "mybatis.xml";
InputStream in = Resources.getResourceAsStream(config);
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
SqlSessionFactory factory = builder.build(in);
SqlSession sqlSession = factory.openSession();
String sqlId = "com.bjpowernode.dao.StudentDao" + "." + "selectStudents";
List<Student> studentList = sqlSession.selectList(sqlId);
// studentList.forEach( stu -> System.out.println(stu));
for (Student stu : studentList){
System.out.println(stu);
}
sqlSession.close();
}
}
步骤:
访问mybatis读取Student数据
1.定义mybatis主配置文件的名称,从类路径的根开始(target/classes)
String config = “mybatis.xml”;
2.读取这个config表示的文件
InputStream in = Resources.getResourceAsStream(config);
3.创建SqlSessionFactoryBuilder对象
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
4.创建SqlSessionFactory对象
SqlSessionFactory factory = builder.build(in);
5.【重要】 获取SqlSession对象,从SqlSessionFactory中获取SqlSession
SqlSession sqlSession = factory.openSession();
6.【重要】指定要执行的sql语句的标识。sql映射文件中的namespace + “.” + 标签的id值
String sqlId = “org.example.dao.StudentDao” + “.” + “selectStudents”;
7.执行sql语句,通过sqlId找到语句
List studentList = sqlSession.selectList(sqlId);
8.输出结果
studentList.forEach( stu -> System.out.println(stu));
9.关闭sqlSession对象
sqlSession.close();
2.8.执行程序遍历输出查询结果集
3.插入操作
在上述查找数据的基础之上
3.1 接口中定义插入数据的方法
package com.bjpowernode.dao;
import com.bjpowernode.domain.Student;
import java.util.List;
//接口操作student表
public interface StudentDao {
//查询student表中的所有数据
public List<Student> selectStudents();
//在student表中插入数据
public int insertStudent(Student student);
}
3.2 创建实体类
package com.bjpowernode.domain;
//推荐和表名称一样,方便记忆
public class Student {
//定义属性,目前要求属性名和列名一样
private Integer id;
private String name;
private String email;
private Integer age;
//Setter和Getter方法.......
//Setter和Getter方法.......
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", email='" + email + '\'' +
", age=" + age +
'}';
}
}
3.3 Mybatis使用的配置文件
<?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.bjpowernode.dao.StudentDao">
<select id="selectStudents" resultType="com.bjpowernode.domain.Student">
select id,name,email,age from student order by id
</select>
<insert id="">
insert into student values(#{id},#{name},#{email},#{age})
</insert>
</mapper>
3.4 创建测试类
public class MyApp {
public static void main(String[] args) throws IOException {
String config = "mybatis.xml";
InputStream in = Resources.getResourceAsStream(config);
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
SqlSessionFactory factory = builder.build(in);
SqlSession sqlSession = factory.openSession();
String sqlId = "com.bjpowernode.dao.StudentDao" + "." + "insertStudent";
Student student = new Student;
student.setId(1005);
student.setName("里斯");
student.setEmail("lisi@qq.com");
student.setAge(29);
int nums = sqlSession.insert(sqlId,student);
System.out.println("成功插入数据:"+nums);
sqlSession.commit();//手工提交事务
sqlSession.close();
}
}
4.加入打印日志功能:
在myabtis主配置文件中加入打印日志的功能:
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
5.主要类介绍:
Resources:
mybatis中的一个类,负责读取主配置文件
SqlSessionFactoryBuilder:
创建SqlSessionFactory对象
SqlSessionFactory:
重量级对象,程序创建一个对象耗时比较长,使用资源比较多。在整个项目中有一个就足够了
openSession():
方法说明:
openSession():无参数的,获取是非自动提交事务的SqlSession对象
openSession(boolean):有参数的
true 获取自动提交事务的SqlSession
false 非自动提交事务的SqlSession对象
SqlSession
SqlSession接口定义了操作数据的方法,是非线程安全的,需要在方法的内部使用
在执行SQL语句之前,使用openSession()获取SqlSessiom,在执行完SQL语句后需要关闭它,执行.close(),这样能保证它的使用是线程安全的
6.封装为工具类:
将上述代码封装为工具类使用更方便:
package com.bjpowernode.Utils;
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;
public class MyUtils {
private static SqlSessionFactory factory = null;
static {
String config = "mybatis.xml";
try {
InputStream in = Resources.getResourceAsStream(config);
factory = new SqlSessionFactoryBuilder().build(in);
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSession getSqlSession(){
SqlSession sqlSession = null;
if (factory != null){
sqlSession = factory.openSession();
}
return sqlSession;
}
}
编写测试类:
package com.bjpowernode;
import com.bjpowernode.Utils.MyUtils;
import com.bjpowernode.domain.Student;
import org.apache.ibatis.session.SqlSession;
import java.io.IOException;
import java.util.List;
public class Test {
public static void main(String[] args) throws IOException {
SqlSession sqlSession = MyUtils.getSqlSession();
String sqlId = "com.bjpowernode.dao.StudentDao" + "." + "selectStudents";
List<Student> studentList = sqlSession.selectList(sqlId);
for (Student stu : studentList) {
System.out.println(stu);
}
sqlSession.close();
}
}
7.实现dao接口
实现StudentDao接口的方法:
package com.bjpowernode.Impl;
import com.bjpowernode.Utils.MyUtils;
import com.bjpowernode.dao.StudentDao;
import com.bjpowernode.domain.Student;
import org.apache.ibatis.session.SqlSession;
import java.util.List;
public class StudentDaoImpl implements StudentDao {
@Override
public List<Student> selectStudents() {
SqlSession sqlSession = MyUtils.getSqlSession();
String sqlId = "com.bjpowernode.dao.StudentDao" + "." + "selectStudents";
List<Student> studentList = sqlSession.selectList(sqlId);
return studentList;
}
}
创建测试类:
package com.bjpowernode;
import com.bjpowernode.Impl.StudentDaoImpl;
import com.bjpowernode.dao.StudentDao;
import com.bjpowernode.domain.Student;
import java.io.IOException;
import java.util.List;
public class Test {
public static void main(String[] args) throws IOException {
StudentDao studentDao = new StudentDaoImpl();
List<Student> studentList = studentDao.selectStudents();
for (Student student : studentList) {
System.out.println(student);
}
}
}
第三章 Dao代理
1.通过动态代理实现查询
在前面代码的基础之上创建测试类:
package com.bjpowernode;
import com.bjpowernode.Utils.MyUtils;
import com.bjpowernode.dao.StudentDao;
import com.bjpowernode.domain.Student;
import org.apache.ibatis.session.SqlSession;
import java.io.IOException;
import java.util.List;
public class Test {
public static void main(String[] args) throws IOException {
SqlSession sqlSession = MyUtils.getSqlSession();
StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
List<Student> studentList = studentDao.selectStudents();
for (Student student : studentList) {
System.out.println(student);
}
}
}
使用动态代理的要求:
2.parameterType属性指定传参类型
2.1 Dao接口中提供查询方法
package com.bjpowernode.dao;
import com.bjpowernode.domain.Student;
import java.util.List;
public interface StudentDao {
//查询student表中的所有数据
public List<Student> selectStudents();
//通过ID查询数据
public Student selectStudentById(Integer id);
}
2.2 XML文件中说明SQL语句并指定传参类型
使用的是parameterType属性:
<?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.bjpowernode.dao.StudentDao">
<select id="selectStudents" resultType="com.bjpowernode.domain.Student">
select id,name,email,age from student order by id
</select>
<select id="selectStudentById" parameterType="java.lang.Integer" resultType="com.bjpowernode.domain.Student">
select id,name,email,age from student where id=#{id}
</select>
</mapper>
parameterType:dao接口中方法参数的数据类型,parameterType的值是数据类型的全限定名称或者是mybatis定义的别名,但不是强制的
例如:parameterType = “java.lang.Integer”
parameterType = “int”
Myabtis数据类型别名:
2.3 测试类
package com.bjpowernode;
import com.bjpowernode.Utils.MyUtils;
import com.bjpowernode.dao.StudentDao;
import com.bjpowernode.domain.Student;
import org.apache.ibatis.session.SqlSession;
import java.io.IOException;
public class Test {
public static void main(String[] args) throws IOException {
SqlSession sqlSession = MyUtils.getSqlSession();
StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
Student student = studentDao.selectStudentById(1002);
System.out.println(student);
}
}
3.传入一个简单类型的参数
一个简单类型的参数:
简单类型:mybatis把java的基本数据类型和String都叫简单类型
在mapper文件中获取简单类型的一个参数的值,使用#{任意的字符}
接口方法:
public Student selectStudentById(Integer id)
mapper文件:
select id,name,email,age from student where id=#{selectStudentId}
4.传递多个参数,使用@Param命名参数
4.1 dao接口方法:
添加**@Param** 属性自定义参数名称:
package com.bjpowernode.dao;
import com.bjpowernode.domain.Student;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface StudentDao {
List<Student> selectStudents(@Param("myname") String name,
@Param("myage") Integer id);
}
4.2 Mapper映射文件
#{ }中的名称与@Param命名的名称 一致
<?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.bjpowernode.dao.StudentDao">
<select id="selectStudents" resultType="com.bjpowernode.domain.Student">
select id,name,email,age from student where name=#{myname} or age=#{myage}
</select>
</mapper>
4.3 编写测试类
package com.bjpowernode;
import com.bjpowernode.Utils.MyUtils;
import com.bjpowernode.dao.StudentDao;
import com.bjpowernode.domain.Student;
import org.apache.ibatis.session.SqlSession;
import java.io.IOException;
import java.util.List;
public class Test {
public static void main(String[] args) throws IOException {
SqlSession sqlSession = MyUtils.getSqlSession();
StudentDao dao = sqlSession.getMapper(StudentDao.class);
List<Student> studentList = dao.selectStudents("张三",1002);
for (Student student : studentList) {
System.out.println(student);
}
}
}
5.传递多个参数,使用对象传递
多个参数使用Java对象的属性值作为参数实际值:
使用对象语法:#{属性名,javaType=类型名称,jdbcType=数据类型},但这种方式很少用
javaType:指Java中的属性数据类型
jdbcType:在数据库中的数据类型
例如:#{name,javaType=java.lang.String.jdbcType=VARCHAR}
我们直接使用简化方式 :#{属性名}
javaType、jdbcType的值Myabtis可以通过反射来获取,不用提供
接口:
package com.bjpowernode.dao;
import com.bjpowernode.domain.Student;
import java.util.List;
public interface StudentDao {
List<Student> selectStudents(Student student);
}
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.bjpowernode.dao.StudentDao">
<select id="selectStudents" resultType="com.bjpowernode.domain.Student">
select id,name,email,age from student where
name=#{name} or age=#{age}
</select>
</mapper>
测试类:
package com.bjpowernode;
import com.bjpowernode.Utils.MyUtils;
import com.bjpowernode.dao.StudentDao;
import com.bjpowernode.domain.Student;
import org.apache.ibatis.session.SqlSession;
import java.io.IOException;
import java.util.List;
public class Test {
public static void main(String[] args) throws IOException {
SqlSession sqlSession = MyUtils.getSqlSession();
StudentDao dao = sqlSession.getMapper(StudentDao.class);
Student student = new Student();
student.setName("张三");
student.setId(1002);
List<Student> studentList = dao.selectStudents(student);
for (Student student1 : studentList) {
System.out.println(student1);
}
}
}
6. #和$
使用#不用担心SQL注入的风险,效率较低
使用$会有SQL注入的风险,效率高
7.封装Mybatis输出结果
resultType:查询后的数据 返回值类型
7.1 使用自定义别名typeAlias标签
在myabtis主配置文件中添加typeAlias标签:
<typeAliases>
<typeAlias type="com.bjpowernode.domain.Student" alias="stu">
</typeAliases>
type: 自定义类型的全限定名称
alias: 自定义的别名(短小,容易记忆的)
在Mapper文件中使用自定义的别名:
<select id="selectStudents" resultType="stu">
select id,name,email,age from student where
name=#{name} or age=#{age}
</select>
7.2 使用package标签
在myabtis主配置文件中添加typeAlias标签:
<typeAliases>
<package name="com.bjpowernode.domain"/>
</typeAliases>
name: 包名,这个包中所有的类就是别名(类名不区分大小写)
在Mapper文件中使用自定义的别名:
<select id="selectStudents" resultType="Student">
select id,name,email,age from student where
name=#{name} or age=#{age}
</select>
7.3 返回值为Map集合
当返回值为map集合时:
1)接口中的方法:
package com.bjpowernode.dao;
import com.bjpowernode.domain.Student;
import java.util.List;
import java.util.Map;
public interface StudentDao {
Map<Object,Object> selectStudentById(Integer id);
}
2)mapper文件:
<?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.bjpowernode.dao.StudentDao">
<select id="selectStudentById" resultType="map">
select id,name,email,age from student where id=#{id}
</select>
</mapper>
3)测试程序:
package com.bjpowernode;
import com.bjpowernode.Utils.MyUtils;
import com.bjpowernode.dao.StudentDao;
import com.bjpowernode.domain.Student;
import org.apache.ibatis.session.SqlSession;
import java.io.IOException;
import java.util.List;
import java.util.Map;
public class Test {
public static void main(String[] args){
SqlSession sqlSession = MyUtils.getSqlSession();
StudentDao dao = sqlSession.getMapper(StudentDao.class);
Map<Object,Object> ss = dao.selectStudentById(20);
System.out.println(ss);
}
}
执行的结果:{name=张三, id=1002, email=zhangsan@sina.com, age=28}
这样的缺点是,只能输出一条数据,多条数据会报错
7.4 使用Map集合返回多条数据
当返回值为map集合时,返回多条数据:
1)接口中的方法:
package com.bjpowernode.dao;
import com.bjpowernode.domain.Student;
import java.util.List;
import java.util.Map;
public interface StudentDao {
List<Student> selectAllStudents();
}
2)mapper文件:
<?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.bjpowernode.dao.StudentDao">
<resultMap id="studentMap" type="com.bjpowernode.domain.Student">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="email" property="email"/>
<result column="age" property="age"/>
</resultMap>
<select id="selectAllStudents" resultMap="studentMap">
select id,name,email,age from student
</select>
</mapper>
在mapper文件中加入resultMap标签:
id: 自定义的名称,表示你自定义的这个resultMap的名称
**type: **Java类型的全限定性名称
子标签中:
id: 主键列使用id标签
result: 非主键列使用result标签
column: 列名,这里的列名需与表中的数据列名称是一致的,否则查询出的数据会显示在错误的列中
property: Java类型的属性名,Student类文件中定义的属性,与Set和Get方法中的属性名一样
例如:
<id column="id" property="Ad"/>
则Student类中的Set和Get方法则需改为getAd()和setAD()
3)测试程序:
package com.bjpowernode;
import com.bjpowernode.Utils.MyUtils;
import com.bjpowernode.dao.StudentDao;
import com.bjpowernode.domain.Student;
import org.apache.ibatis.session.SqlSession;
import java.io.IOException;
import java.util.List;
import java.util.Map;
public class Test {
public static void main(String[] args){
SqlSession sqlSession = MyUtils.getSqlSession();
StudentDao dao = sqlSession.getMapper(StudentDao.class);
Map<Object,Object> ss = dao.selectStudentById(20);
System.out.println(ss);
}
}
输出结果:成功查询出所有的学生数据:
Student{id=1001, name=‘李四’, email=‘lisi@qq.com’, age=20}
Student{id=1002, name=‘张三’, email=‘zhangsan@sina.com’, age=28}
Student{id=1003, name=‘周峰’, email=‘zhoufeng@qq.com’, age=20}
Student{id=1004, name=‘李胜利’, email=‘lishengli@qq.com’, age=24}
Student{id=1005, name=‘里昂’, email=‘leon@qq.com’, age=21}
8. 注意的点
resultMap常用在列明和实体类属性名不一样时
解决可以查询出数据结果但是无法给对象赋值时
例如:
1)定义接口方法:
public interface StudentDao {
List<MyStudent> selectAllMyStudents();
}
2)创建实体类:MyStudent
package com.bjpowernode.domain;
public class MyStudent {
private Integer myid;
private String myname;
private String myemail;
private Integer myage;
//getter和setter方法
@Override
public String toString() {
return "MyStudent{" +
"myid=" + myid +
", myname='" + myname + '\'' +
", myemail='" + myemail + '\'' +
", myage=" + myage +
'}';
}
}
3)mapper文件:
<?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.bjpowernode.dao.StudentDao">
<select id="selectAllMyStudents" resultType="com.bjpowernode.domain.MyStudent">
select id,name,email,age from student
</select>
</mapper>
4)创建执行测试类:
package com.bjpowernode;
import com.bjpowernode.Utils.MyUtils;
import com.bjpowernode.dao.StudentDao;
import com.bjpowernode.domain.MyStudent;
import com.bjpowernode.domain.Student;
import org.apache.ibatis.session.SqlSession;
import java.util.List;
public class Test {
public static void main(String[] args){
SqlSession sqlSession = MyUtils.getSqlSession();
StudentDao dao = sqlSession.getMapper(StudentDao.class);
List<MyStudent> students = dao.selectAllMyStudents();
for (MyStudent student : students) {
System.out.println(student);
}
}
}
输出结果:
null null null null null
因为实体类列名与表中列名不一致,导致mybatis无法正确给对象赋值
8.1 解决办法一:
给查询出的结果起个别名,别名跟MyStudent类中的属性名一致:
<?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.bjpowernode.dao.StudentDao">
<select id="selectAllMyStudents" resultType="com.bjpowernode.domain.MyStudent">
select id as myid,name as myname,email as myemail ,age as myage from student
</select>
</mapper>
8.2 解决办法二:
自定义result标签:
<?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.bjpowernode.dao.StudentDao">
<resultMap id="studentMap" type="com.bjpowernode.domain.MyStudent">
<id column="id" property="myid"/>
<result column="name" property="myname"/>
<result column="email" property="myemail"/>
<result column="age" property="myage"/>
</resultMap>
<select id="selectAllMyStudents" resultMap="studentMap">
select id,name,email,age from student
</select>
</mapper>
两种方法输出同样的结果:
MyStudent{myid=1001, myname=‘李四’, myemail=‘lisi@qq.com’, myage=20}
MyStudent{myid=1002, myname=‘张三’, myemail=‘zhangsan@sina.com’, myage=28}
MyStudent{myid=1003, myname=‘周峰’, myemail=‘zhoufeng@qq.com’, myage=20}
MyStudent{myid=1004, myname=‘李胜利’, myemail=‘lishengli@qq.com’, myage=24}
MyStudent{myid=1005, myname=‘里昂’, myemail=‘leon@qq.com’, myage=21}
9. 模糊查询Like
9.1 第一种Like方式:
1)接口中的方法:
package com.bjpowernode.dao;
import com.bjpowernode.domain.MyStudent;
import com.bjpowernode.domain.Student;
import java.util.List;
import java.util.Map;
public interface StudentDao {
List<Student> selectLike(String name);
}
2)Mapper文件:
<?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.bjpowernode.dao.StudentDao">
<select id="selectLike" resultType="com.bjpowernode.domain.Student">
select id,name,email ,age from student where name like#{name}
</select>
</mapper>
3)测试程序:
package com.bjpowernode;
import com.bjpowernode.Utils.MyUtils;
import com.bjpowernode.dao.StudentDao;
import com.bjpowernode.domain.Student;
import org.apache.ibatis.session.SqlSession;
import java.util.List;
public class Test {
public static void main(String[] args){
SqlSession sqlSession = MyUtils.getSqlSession();
StudentDao dao = sqlSession.getMapper(StudentDao.class);
List<Student> students = dao.selectLike("%李%");
for (Student student : students) {
System.out.println(student);
}
}
}
9.2 第二种Like方式:
1)mapper文件:
<?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.bjpowernode.dao.StudentDao">
<select id="selectLike" resultType="com.bjpowernode.domain.Student">
select id,name,email ,age from student where name like#{name}
</select>
</mapper>
2)测试程序:
package com.bjpowernode;
import com.bjpowernode.Utils.MyUtils;
import com.bjpowernode.dao.StudentDao;
import com.bjpowernode.domain.Student;
import org.apache.ibatis.session.SqlSession;
import java.util.List;
public class Test {
public static void main(String[] args){
SqlSession sqlSession = MyUtils.getSqlSession();
StudentDao dao = sqlSession.getMapper(StudentDao.class);
List<Student> students = dao.selectLike("李");
for (Student student : students) {
System.out.println(student);
}
}
}
第四章:动态SQL
1. if 标签
if标签是用来判断条件的,语法格式:
<if test="判断Java对象的属性值">
部分SQL语句
</if>
使用方法步骤:
1)接口方法:
package com.bjpowernode.dao;
import com.bjpowernode.domain.Student;
import java.util.List;
public interface StudentDao {
List<Student> selectAllStudents(Student student);
}
2)mapper文件:
<?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.bjpowernode.dao.StudentDao">
<select id="selectAllStudents" resultType="com.bjpowernode.domain.Student">
select id,name,email ,age from student
where
<if test="name != null and name != '' ">
name = #{name}
</if>
<if test="age > 0">
or age > #{age}
</if>
</select>
</mapper>
3)测试程序:
package com.bjpowernode;
import com.bjpowernode.Utils.MyUtils;
import com.bjpowernode.dao.StudentDao;
import com.bjpowernode.domain.Student;
import org.apache.ibatis.session.SqlSession;
import java.util.List;
public class Test {
public static void main(String[] args){
SqlSession sqlSession = MyUtils.getSqlSession();
StudentDao dao = sqlSession.getMapper(StudentDao.class);
Student student = new Student();
student.setName("张三");
student.setAge(20);
List<Student> students = dao.selectAllStudents(student);
for (Student student1 : students) {
System.out.println(student1);
}
}
}
注意:
但是这样做有个缺点,如果第一个条件没有成立则会造成SQL语法错误
也就是:
select id,name,email ,age from student where or age > 20
解决办法:
可以在where 条件后面加入一行绝对True的条件:
例如:where 1 = 1 也就是
<select id="selectAllStudents" resultType="com.bjpowernode.domain.Student">
select id,name,email ,age from student
where 1 = 1
<if test="name != null and name != '' ">
name = #{name}
</if>
<if test="age > 0">
or age > #{age}
</if>
</select>
这样做就解决了上述的问题,当然也可以使用where标签
2. where标签
where标签用来包含多个if标签的,当有多个if标签有一个成立时where标签会自动增加一个where关键字,并去掉多余的and or等
与上面if标签同样的代码,但本次使用where标签:
<?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.bjpowernode.dao.StudentDao">
<select id="selectAllStudents" resultType="com.bjpowernode.domain.Student">
select id,name,email ,age from student
<where>
<if test="name != null and name != '' ">
or name = #{name}
/*
where标签会自动去掉or关键字
*/
</if>
<if test="age > 0">
or age > #{age}
</if>
</where>
</select>
</mapper>
查询结果与使用if标签时相同
3.foreach标签
3.1 第一种方式:
1)接口方法:
package com.bjpowernode.dao;
import com.bjpowernode.domain.Student;
import java.util.List;
public interface StudentDao {
List<Student> selectAllStudents(List<Integer> idList);
}
2)mapper文件:
<?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.bjpowernode.dao.StudentDao">
<select id="selectAllStudents" resultType="com.bjpowernode.domain.Student">
select id,name,email ,age from student where id in
<foreach collection="list" item="MyId" open="(" close=")" separator=",">
#{MyId}
</foreach>
</select>
</mapper>
相当于:
select id,name,email ,age from student where id in ( ? , ? , ? )
collection: 表示接口中的方法 参数的类型,如果数组就使用array,如果是List集合就使用list
item: 自定义的,表示数组或者是集合成员的变量
open: 循环开始时的字符
close: 循环结束时的字符
separator: 集合成员之间的分割符
3)主测试类:
package com.bjpowernode;
import com.bjpowernode.Utils.MyUtils;
import com.bjpowernode.dao.StudentDao;
import com.bjpowernode.domain.Student;
import org.apache.ibatis.session.SqlSession;
import java.util.ArrayList;
import java.util.List;
public class Test {
public static void main(String[] args){
SqlSession sqlSession = MyUtils.getSqlSession();
StudentDao dao = sqlSession.getMapper(StudentDao.class);
List<Integer> list = new ArrayList<>();
list.add(1001);
list.add(1002);
list.add(1003);
List<Student> students = dao.selectAllStudents(list);
for (Student student1 : students) {
System.out.println(student1);
}
}
}
3.2 第二种方式:
1)接口方法:(传递的是Student类型的List集合)
package com.bjpowernode.dao;
import com.bjpowernode.domain.Student;
import java.util.List;
public interface StudentDao {
List<Student> selectAllStudents(List<Student> studentList);
}
2)mapper文件:
<?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.bjpowernode.dao.StudentDao">
<select id="selectAllStudents" resultType="com.bjpowernode.domain.Student">
select id,name,email ,age from student where id in
<foreach collection="list" item="MyId" open="(" close=")" separator=",">
#{MyId.id}
</foreach>
</select>
</mapper>
需用.id属性值,相当于调用了Student.getId()方法
3)创建测试类:
package com.bjpowernode;
import com.bjpowernode.Utils.MyUtils;
import com.bjpowernode.dao.StudentDao;
import com.bjpowernode.domain.Student;
import org.apache.ibatis.session.SqlSession;
import java.util.ArrayList;
import java.util.List;
public class Test {
public static void main(String[] args){
SqlSession sqlSession = MyUtils.getSqlSession();
StudentDao dao = sqlSession.getMapper(StudentDao.class);
List<Student> list = new ArrayList<>();
Student student = new Student();
student.setId(1001);
list.add(student);
student = new Student();
student.setId(1002);
list.add(student);
student = new Student();
student.setId(1003);
list.add(student);
List<Student> students = dao.selectAllStudents(list);
for (Student student1 : students) {
System.out.println(student1);
}
}
}
两种方式使用的SQL和结果都相同
4. SQL代码片段
当我们遇到SQL查询语句重复的时候,可以使用sql标签声明,然后使用include标签引用:
<?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.bjpowernode.dao.StudentDao">
<sql id="StudentSql">
select id,name,email ,age from student
</sql>
<select id="selectAllStudents" resultType="com.bjpowernode.domain.Student">
<include refid="StudentSql"/>where id in
<foreach collection="list" item="MyId" open="(" close=")" separator=",">
#{MyId.id}
</foreach>
</select>
</mapper>
第五章:Myabtis配置文件
1. Settings标签设置
可参考:
https://blog.csdn.net/qq_36761831/article/details/88375215
2.使用属性配置文件
把数据库的连接信息放到一个单独的文件中:
2.1 创建属性文件
在resources文件夹中创建**.properties** 后缀的文件
在属性文件中定义数据,格式是key=value
文件的内容为数据库的连接信息:
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/java
jdbc.user=root
jdbc.pwd=333
2.2 配置mybatis主配置文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<properties resource="jdbc.properties"/>
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<environments default="mydev">
<environment id="mydev">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.user}"/>
<property name="password" value="${jdbc.pwd}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/bjpowernode/dao/StudentDao.xml"/>
</mappers>
</configuration>
使用properties标签说明.properties文件的位置
配置连接信息使用**${key部分}** 来使用
3. 指定多个mapper文件
第一种方式:
写多个mapper标签:
<mappers>
<mapper resource="com/bjpowernode/dao/StudentDao.xml"/>
<mapper resource="com/bjpowernode/dao/StudentDao1.xml"/>
<mapper resource="com/bjpowernode/dao/StudentDao2.xml"/>
</mappers>
第二种方式:
使用包名:
<mappers>
<package name="com.bjpowernode.dao"/>
<package name="com.bjpowernode.dao1"/>
<package name="com.bjpowernode.dao2"/>
</mappers>
第六章:拓展
PageHelper分页插件
实现数据分页:(设置每页显示几条数据)
1)pom文件中添加依赖:
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.1.10</version>
</dependency>
2)在Mybatis主配置文件中配置插件
在configuration标签中加入:
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor"/>
</plugins>
如何使用:
1)接口方法:
package com.bjpowernode.dao;
import com.bjpowernode.domain.Student;
import java.util.List;
public interface StudentDao {
List<Student> selectAllStudents();
}
2)主测试程序:
加入依赖和驱动后,在程序中写入:PageHelper.startPage()方法
package com.bjpowernode;
import com.bjpowernode.Utils.MyUtils;
import com.bjpowernode.dao.StudentDao;
import com.bjpowernode.domain.Student;
import com.github.pagehelper.PageHelper;
import org.apache.ibatis.session.SqlSession;
import java.util.List;
public class Test {
public static void main(String[] args){
SqlSession sqlSession = MyUtils.getSqlSession();
StudentDao dao = sqlSession.getMapper(StudentDao.class);
PageHelper.startPage(1,3);
List<Student> students = dao.selectAllStudents();
for (Student student : students) {
System.out.println(student);
}
}
}
PageHelper.startPage()de 源代码:
public static <E> Page<E> startPage(int pageNum, int pageSize)
其中:
pageNum:页数,第几页
pageSize:一页中显示多少条数据