01_原始JDBC存在的问题
原始JDBC代码
Class. forName ( "com.mysql.jdbc.Driver" ) ;
Connection connection =
DriverManager. getConnection ( "jdbc:mysql://localhost:3306/test" , "root" , "123456" ) ;
String sql = "insert into tb_user(username,password) values(?,?)" ;
PreparedStatement statement = connection. prepareStatement ( sql) ;
statement. setString ( 1 , "zhangsan" ) ;
statement. setString ( 2 , "zhangsan" ) ;
statement. executeUpdate ( ) ;
connection. close ( ) ;
statement. close ( ) ;
原始jdbc开发存在的问题如下:
数据库连接创建、释放频繁造成系统资源浪费从而影响系统性能 sql 语句在代码中硬编码,造成代码不易维护,实际应用 sql 变化的可能较大,sql 变动需要改变java代码。 查询操作时,需要手动将结果集中的数据手动封装到实体中。插入操作时,需要手动将实体的数据设置到sql语句的占位符位置
应对上述问题给出的解决方案:
使用数据库连接池初始化连接资源 将sql语句抽取到xml配置文件中 使用反射、内省等底层技术,自动将实体与表进行属性与字段的自动映射
02_MyBatis基本概念
A.概述
mybatis是一个优秀的基于 java 的持久层框架,它内部封装了 jdbc,使开发者只需要关注sql 语句本身,而不需要花费精力去处理加载驱动、创建连接、创建statement 等繁杂的过程。 mybatis通过xml配置或注解的方式将要执行的各种 statement 配置起来,并通过java 对象和 statement 中sql 的动态参数进行映射生成最终执行的 sql 语句. 最后mybatis框架执行sql并将结果映射为java对象并返回。采用ORM思想解决了实体和数 据库映射的问题,对jdbc 进行了封装,屏蔽了jdbc api 底层访问细节,使我们不用与jdbc api 打交道,就可以完成对数据库的持久化操作。
B.MyBatis结构
SqlMapConfig.xml, 此文件作为mybatis的核心配置文件,配置了mybatis的运行环境等信息。 Mapper.xml, 即sql映射文件,文件中配置了操作数据库的sql语句。此文件需要在SqlMapConfig.xml中加载。 SqlSessionFactory, 通过mybatis配置信息创建,即会话工厂对象 SqlSession, 由SqlSessionFactory获取,用来操作数据库。 Executor, mybatis底层自定的接口,用来操作数据库。 MappedStatement, mybatis的底层封装对象,它包装了mybatis配置信息及sql映射信息等。mapper.xml文件中一个sql对应一个MappedStatement对象。 输入映射,包括HashMap、基本类型、pojo,Executor通过MappedStatement在执行sql前将输入的java对象映射至sql中,输入映射相当于JDBC编程中对preparedStatement设置参数。 输出映射,包括HashMap、基本类型、pojo,Executor通过MappedStatement在执行sql后将输出结果映射至java对象中,输出映射相当于JDBC编程中对结果的解析处理过程。
03_MyBatis资源下载
04_Mybatis入门程序
需求:查询所有记录
a.加入jar包
b.创建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 = " test" >
< !‐‐配置查询所有‐‐>
< select id = " selectUserList" resultType = " com.aaa.bean.User" >
select * from user
</ select>
</ mapper>
namespace :命名空间,用于隔离sql语句,后面会讲另一层非常重要的作用。
c.创建SqlMapConfig.xml文件,并加载mapper映射文件
<?xml version="1.0" encoding="UTF‐8" ?>
<!DOCTYPE configuration
PUBLIC "‐//mybatis.org//DTD Config 3.0//EN"
"http://www.mybatis.org/dtd/mybatis‐3‐config.dtd">
< configuration>
< !‐‐ 和spring整合后 environments配置将废除‐‐ >
< environments default = " development" >
< environment id = " development" >
< !‐‐ 使用jdbc事务管理‐‐ >
< transactionManager type = " JDBC" />
< !‐‐ 数据库连接池‐‐ >
< dataSource type = " POOLED" >
< property name = " driver" value = " com.mysql.cj.jdbc.Driver" />
< property name = " url" value = " jdbc:mysql://localhost:3306/test?
serverTimezone=UTC" />
< property name = " username" value = " root" />
< property name = " password" value = " 123456" />
</ dataSource>
</ environment>
</ environments>
< mappers>
< mapper resource = " mapper01.xml" > </ mapper>
</ mappers>
</ configuration>
d.代码测试
@Override
public List< User> selectUserList ( ) throws Exception {
SqlSessionFactory sqlSessionFactory = new
SqlSessionFactoryBuilder ( ) . build ( Resources. getResourceAsStream ( "sqlMapperConfig.xml" )
) ;
SqlSession sqlSession = sqlSessionFactory. openSession ( ) ;
List< User> userList = sqlSession. selectList ( "selectUserList" ) ;
sqlSession. close ( ) ;
return userList;
}
05_mapper映射文件说明
06_根据id查询用户信息
A.映射文件:
< select id = " selectUserById" parameterType = " int" resultType = " cn.aaa.bean.User" >
select * from user where id = #{id}
</ select>
B.测试代码
07_根据用户名查询用户信息(模糊查询)
A.方式一:使用#{username}
< select id = " selectUserListLikeName1" parameterType = " String"
resultType = " com.aaa.bean.User" >
select * from user where username like #{username}
</ select>
< select id = " selectUserListLikeName2" parameterType = " java.lang.String"
resultType = " com.aaa.bean.User" >
select * from user where username like "%"#{username}"%"
</ select>
@Override
public List< User> selectUserListLikeName1 ( String name) throws Exception {
SqlSessionFactory sqlSessionFactory = new
SqlSessionFactoryBuilder ( ) . build ( Resources. getResourceAsStream ( "SqlMapConfig.xml" ) ) ;
SqlSession sqlSession = sqlSessionFactory. openSession ( ) ;
List< User> userList =
sqlSession. selectList ( "selectUserListLikeName1" , "%" + name+ "%" ) ;
sqlSession. close ( ) ;
return userList;
}
@Override
public List< User> selectUserListLikeName2 ( String name) throws Exception {
SqlSessionFactory sqlSessionFactory = new
SqlSessionFactoryBuilder ( ) . build ( Resources. getResourceAsStream ( "SqlMapConfig.xml" ) ) ;
SqlSession sqlSession = sqlSessionFactory. openSession ( ) ;
List< User> userList = sqlSession. selectList ( "selectUserListLikeName2" , name) ;
sqlSession. close ( ) ;
return userList;
}
B.方式二:使用${value}
<select id="selectUserListByName3"parameterType="java.lang.String"
resultType="com.aaa.bean.User">
select * from user where username like '%${username}%'
</ select>
@Override
public List< User> selectUserListLikeName3 ( String name) throws Exception {
SqlSessionFactory sqlSessionFactory = new
SqlSessionFactoryBuilder ( ) . build ( Resources. getResourceAsStream ( "SqlMapConfig.xml" ) ) ;
SqlSession sqlSession = sqlSessionFactory. openSession ( ) ;
List< User> userList = sqlSession. selectList ( "selectUserListLikeName3" , name) ;
sqlSession. close ( ) ;
return userList;
}
C.#{}和${}的区别
#{}可以防止sql注入漏洞 ${}不可以防止sql注入漏洞
08_添加用户
mapper映射文件:
< insert id = " insertUser" parameterType = " cn.aaa.bean.User" >
insert into user(id,username) values(#{id},#{username})
</ insert>
dao层代码
@Override
public void addUser ( User user) throws Exception {
SqlSessionFactory sqlSessionFactory = new
SqlSessionFactoryBuilder ( ) . build ( Resources. getResourceAsStream ( "SqlMapConfig.xml" ) ) ;
SqlSession sqlSession = sqlSessionFactory. openSession ( ) ;
sqlSession. insert ( "addUser" , user) ;
sqlSession. commit ( ) ;
sqlSession. close ( ) ;
}
注意事项
09_insert实现主键返回
主键返回
在insert标签中加入selectKey标签
< insert id = " addUser" parameterType = " com.aaa.bean.User" >
< selectKey keyProperty = " id" order = " AFTER" resultType = " long" >
select last_insert_id();
</ selectKey>
insert into user(username,password) values(#{username},#{password});
</ insert>
属性介绍:
keyProperty:返回的主键存储在pojo中的哪个属性 order:selectKey的执行顺序,是相对与insert语句来说,由于mysql的自增原理执行完insert语句之后才将主键生成,所以这里selectKey的执行顺序为after resultType:返回的主键是什么类型
测试代码:
UserDao userDao = new UserDao ( ) ;
User user = new User ( 1 , "张三" , "张三" ) ;
long id = user. getId ( ) ;
userDao. addUser ( user) ;
id = user. getId ( ) ;
10_删除用户
mapper映射文件
< delete id = " deleteUserById" parameterType = " int" >
delete from tb_user where id = #{id}
</ delete>
dao层代码
public void deleteUserById ( int id) throws Exception {
SqlSessionFactory sqlSessionFactory = new
SqlSessionFactoryBuilder ( ) . build ( Resources. getResourceAsStream ( "SqlMapConfig.xml" ) ) ;
SqlSession sqlSession = sqlSessionFactory. openSession ( ) ;
sqlSession. delete ( "deleteUserById" , id) ;
sqlSession. commit ( ) ;
sqlSession. close ( ) ;
}
注意事项
11_修改用户
mapper映射文件
< update id = " updateUser" parameterType = " com.aaa.bean.User" >
update tb_user set username = #{username} ,password = #{password} where id = #{id}
</ update>
dao层代码
public void updateUser ( User user) throws Exception {
SqlSessionFactory sqlSessionFactory = new
SqlSessionFactoryBuilder ( ) . build ( Resources. getResourceAsStream ( "SqlMapConfig.xml" ) ) ;
SqlSession sqlSession = sqlSessionFactory. openSession ( ) ;
sqlSession. update ( "updateUser" , user) ;
sqlSession. commit ( ) ;
sqlSession. close ( ) ;
}
12_核心API介绍说明
SqlSessionFactoryBuilder
SqlSessionFactoryBuilder用于创建SqlSessionFacoty,SqlSessionFacoty一旦创建完成就不需要SqlSessionFactoryBuilder了,因此SqlSessionFactoryBuilder可以是匿名对象,可以及时回收.
SqlSessionFactory
SqlSessionFactory是一个接口,接口中定义了openSession的不同重载方法,SqlSessionFactory的最佳使用范围是整个应用运行期间,一旦创建后可以重复使用,通常以单例模式管理SqlSessionFactory。
SqlSession
SqlSession是一个面向用户的接口,每个数据库操作都应该有它自己的SqlSession实例。SqlSession的实例不能共享使用,它也是线程不安全的。因此最佳的范围是请求或方法中。绝对不能将SqlSession实例的引用放在一个类的静态字段或实例字段中。
13_原始dao开发方法
概述
使用Mybatis操作数据库,通常有两个方法,即原始Dao开发方法和Mapper接口开发方法。
dao层代码
public class UserDaoImpl implements UserDao {
private SqlSessionFactory sqlSessionFactory ;
public UserDaoImpl ( SqlSessionFactory sqlSessionFactory) {
this . sqlSessionFactory = sqlSessionFactory;
}
@Override
public List< User> selectUserList ( ) throws Exception {
SqlSession sqlSession = sqlSessionFactory. openSession ( ) ;
List< User> userList = sqlSession. selectList ( "selectUserList" ) ;
return userList;
}
@Override
public void updateUser ( User user) throws Exception {
SqlSession sqlSession = sqlSessionFactory. openSession ( ) ;
sqlSession. update ( "updateUser" , user) ;
sqlSession. commit ( ) ;
sqlSession. close ( ) ;
}
}
测试代码
public class DaoTest {
UserDao userDao;
@Before
public void init ( ) throws IOException {
SqlSessionFactory sqlSessionFactory = new
SqlSessionFactoryBuilder ( ) . build ( Resources. getResourceAsStream ( "SqlMapConfig.xml" ) ) ;
userDao = new UserDaoImpl ( sqlSessionFactory) ;
}
@Test
public void updateUser ( ) throws Exception {
userDao. updateUser ( new User ( 1 , "root" , "123456" ) ) ;
}
}
原始dao方式存在的问题
SqlSession操作方法需要指定statement的id,配置文件和实现类的耦合度太高了, statement的id改变,实现类代码也要改变原始Dao开发方法需要程序员编写Dao接口和Dao实现类,存在大量的dao接口实现类
14_Mapper动态代理方式
A.映射文件
B.dao接口
接口中方法的名称和映射文件中定义的statement的id相同 接口中方法的输入参数类型和映射文件中定义的statement的parameterType相同 接口方法的返回值类型和映射文件中定义的statement的resultType相同
C.在SqlMapConfig.xml中加载映射文件
D.代码测试
SqlSessionFactory sessionFactory = new
SqlSessionFactoryBuilder ( ) . build ( Resources. getResourceAsStream ( "SqlMapConfig.xml" ) ) ;
SqlSession session = sessionFactory. openSession ( ) ;
IUserDao userDao = session. getMapper ( IUserDao. class ) ;
List< User> users = userDao. selectUserList ( ) ;