1 数据库操作框架的历程
1.1 JDBC
JDBC(Java Data Base Connection,java数据库连接)是一种用于执行SQL语句的Java API,可以为多种关系数据库提供统一访问,它由一组用Java语言编写的类和接口组成。JDBC提供了一种基准,据此可以构建更高级的工具和接口,使数据库开发人员能够编写数据库应用程序 优点:运行期:快捷、高效 缺点:编辑期:代码量大、繁琐异常处理、不支持数据库跨平台
1.2 DBUtils
DBUtils是Java编程中的数据库操作实用工具,小巧简单实用 DBUtils封装了对JDBC的操作,简化了JDBC操作,可以少写代码 DBUtils三个核心功能介绍
QueryRunner中提供对sql语句操作的API ResultSetHandler接口,用于定义select操作后,怎样封装结果集 DBUtils类,它就是一个工具类,定义了关闭资源与事务处理的方法
1.3 Hibernate
Hibernate 是由 Gavin King 于 2001 年创建的开放源代码的对象关系框架。它强大且高效的构建具有关系对象持久性和查询服务的 Java 应用程序 Hibernate 将 Java 类映射到数据库表中,从 Java 数据类型中映射到 SQL 数据类型中,并把开发人员从 95% 的公共数据持续性编程工作中解放出来 Hibernate 是传统 Java 对象和数据库服务器之间的桥梁,用来处理基于 O/R 映射机制和模式的那些对象。
1.3.1 Hibernate优点
Hibernate 使用 XML 文件来处理映射 Java 类别到数据库表格中,并且不用编写任何代码 为在数据库中直接储存和检索 Java 对象提供简单的 APIs 如果在数据库中或任何其它表格中出现变化,那么仅需要改变 XML 文件属性 抽象不熟悉的 SQL 类型,并为我们提供工作中所熟悉的 Java 对象 Hibernate 不需要应用程序服务器来操作 操控你数据库中对象复杂的关联 最小化与访问数据库的智能提取策略 提供简单的数据询问 数据移植性强
1.3.2 Hibernate缺点
过度工程化,性能较差,难以优化 学习难度较大 无法进行细致操作,例如减少查询出的列数 缓存机制不如redis 寻找bug困难 批量数据操作需要大量的内存空间而且执行过程中需要的对象太多
1.4 JDBCTemplate
JdbcTemplate针对数据查询提供了多个重载的模板方法,你可以根据需要选用不同的模板方法。如果你的查询很简单,仅仅是传入相应SQL或者相关参数,然后取得一个单一的结果,那么你可以选择如下一组便利的模板方法 优点:运行期:高效、内嵌Spring框架中、支持基于AOP的声明式事务 缺点:必须于Spring框架结合在一起使用、不支持数据库跨平台、默认没有缓存
2 Mybatis
MyBatis 是一款优秀的持久层框架,它支持自定义 SQL、存储过程以及高级映射。MyBatis 免除了几乎所有的 JDBC 代码以及设置参数和获取结果集的工作。MyBatis 可以通过简单的 XML 或注解来将原始类型、接口和 Java POJO(Plain Old Java Objects,普通老式 Java 对象)映射成数据库中的记录 优点
与JDBC相比,减少了50%的代码量 最简单的持久化框架,简单易学 SQL代码从程序代码中彻底分离出来,可以重用 提供XML标签,支持编写动态SQL 提供映射标签,支持对象与数据库的ORM字段关系映射:
O就是Object,即java对象 R表示关系,其实就是数据库中的表 M表示他们之间的映射关系 缺点
SQL语句编写工作量大,熟练度要高 数据库移植性比较差,如果需要切换数据库的话,SQL语句会有很大的差异
3 第一个Mybatis项目
创建普通的maven项目 添加pom依赖
<?xml version="1.0" encoding="UTF-8"?>
< project xmlns = " http://maven.apache.org/POM/4.0.0"
xmlns: xsi= " http://www.w3.org/2001/XMLSchema-instance"
xsi: schemaLocation= " http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd" >
< modelVersion> 4.0.0</ modelVersion>
< groupId> org.example</ groupId>
< artifactId> mybatis</ artifactId>
< version> 1.0-SNAPSHOT</ version>
< dependencies>
< dependency>
< groupId> org.mybatis</ groupId>
< artifactId> mybatis</ artifactId>
< version> 3.5.4</ version>
</ dependency>
< dependency>
< groupId> mysql</ groupId>
< artifactId> mysql-connector-java</ artifactId>
< version> 8.0.16</ version>
</ dependency>
< dependency>
< groupId> log4j</ groupId>
< artifactId> log4j</ artifactId>
< version> 1.2.17</ version>
</ dependency>
< dependency>
< groupId> junit</ groupId>
< artifactId> junit</ artifactId>
< version> 4.13</ version>
< scope> test</ scope>
</ dependency>
</ dependencies>
</ project>
创建对应的数据表
create table emp select * from scott. emp;
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0 ;
DROP TABLE IF EXISTS ` dept` ;
CREATE TABLE ` dept` (
` deptno` int ( 2 ) NOT NULL ,
` dname` varchar ( 15 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
` loc` varchar ( 15 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY ( ` deptno` ) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
INSERT INTO ` dept` VALUES ( 10 , 'ACCOUNTING' , 'NewYork' ) ;
INSERT INTO ` dept` VALUES ( 20 , 'RESEARCH' , 'Dallas' ) ;
INSERT INTO ` dept` VALUES ( 30 , 'SALES' , 'Chicago' ) ;
INSERT INTO ` dept` VALUES ( 40 , 'OPERATIONS' , 'Boston' ) ;
DROP TABLE IF EXISTS ` emp` ;
CREATE TABLE ` emp` (
` empno` int ( 4 ) NOT NULL COMMENT '雇员编号' ,
` ename` varchar ( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
` job` varchar ( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
` mgr` int ( 4 ) NULL DEFAULT NULL ,
` hiredate` date NULL DEFAULT NULL ,
` sal` decimal ( 7 , 0 ) NULL DEFAULT NULL ,
` comm` decimal ( 7 , 0 ) NULL DEFAULT NULL ,
` deptno` int ( 2 ) NULL DEFAULT NULL ,
PRIMARY KEY ( ` empno` ) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
INSERT INTO ` emp` VALUES ( 7369 , 'SMITH' , 'CLERK' , 7902 , '1980-12-17' , 800 , NULL , 20 ) ;
INSERT INTO ` emp` VALUES ( 7499 , 'ALLEN' , 'SALESMAN' , 7698 , '1981-02-20' , 1600 , 300 , 30 ) ;
INSERT INTO ` emp` VALUES ( 7521 , 'WARD' , 'SALESMAN' , 7698 , '1981-02-22' , 1250 , 500 , 30 ) ;
INSERT INTO ` emp` VALUES ( 7566 , 'JONES' , 'MANAGER' , 7839 , '1981-04-02' , 2975 , NULL , 20 ) ;
INSERT INTO ` emp` VALUES ( 7654 , 'MARTIN' , 'SALESMAN' , 7698 , '1981-09-28' , 1250 , 1400 , 30 ) ;
INSERT INTO ` emp` VALUES ( 7698 , 'BLAKE' , 'MANAGER' , 7839 , '1981-05-01' , 2850 , NULL , 30 ) ;
INSERT INTO ` emp` VALUES ( 7782 , 'CLARK' , 'MANAGER' , 7839 , '1981-06-09' , 2450 , NULL , 10 ) ;
INSERT INTO ` emp` VALUES ( 7788 , 'SCOTT' , 'ANALYST' , 7566 , '1987-07-13' , 3000 , NULL , 20 ) ;
INSERT INTO ` emp` VALUES ( 7839 , 'KING' , 'PRESIDENT' , NULL , '1981-11-17' , 5000 , NULL , 10 ) ;
INSERT INTO ` emp` VALUES ( 7844 , 'TURNER' , 'SALESMAN' , 7698 , '1981-09-08' , 1500 , 0 , 30 ) ;
INSERT INTO ` emp` VALUES ( 7876 , 'ADAMS' , 'CLERK' , 7788 , '1987-07-13' , 1100 , NULL , 20 ) ;
INSERT INTO ` emp` VALUES ( 7900 , 'JAMES' , 'CLERK' , 7698 , '1981-12-03' , 950 , NULL , 30 ) ;
INSERT INTO ` emp` VALUES ( 7902 , 'FORD' , 'ANALYST' , 7566 , '1981-12-03' , 3000 , NULL , 20 ) ;
INSERT INTO ` emp` VALUES ( 7934 , 'MILLER' , 'CLERK' , 7782 , '1982-01-23' , 1300 , NULL , 10 ) ;
DROP TABLE IF EXISTS ` salgrade` ;
CREATE TABLE ` salgrade` (
` grade` int ( 7 ) NULL DEFAULT NULL ,
` losal` int ( 7 ) NULL DEFAULT NULL ,
` hisal` int ( 7 ) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
INSERT INTO ` salgrade` VALUES ( 1 , 700 , 1200 ) ;
INSERT INTO ` salgrade` VALUES ( 2 , 1201 , 1400 ) ;
INSERT INTO ` salgrade` VALUES ( 3 , 1401 , 2000 ) ;
INSERT INTO ` salgrade` VALUES ( 4 , 2001 , 3000 ) ;
INSERT INTO ` salgrade` VALUES ( 5 , 3001 , 9999 ) ;
SET FOREIGN_KEY_CHECKS = 1 ;
Emp.java:表对应的实体类对象
package com. mashibing. bean;
import java. util. Date;
public class Emp {
private Integer empno;
private String ename;
private String job;
private Integer mgr;
private Date hiredate;
private Double sal;
private Double common;
private Integer deptno;
public Emp ( ) {
}
public Emp ( Integer empno, String ename) {
this . empno = empno;
this . ename = ename;
}
public Emp ( Integer empno, String ename, String job, Integer mgr, Date hiredate, Double sal, Double common, Integer deptno) {
this . empno = empno;
this . ename = ename;
this . job = job;
this . mgr = mgr;
this . hiredate = hiredate;
this . sal = sal;
this . common = common;
this . deptno = deptno;
}
public Integer getEmpno ( ) {
return empno;
}
public void setEmpno ( Integer empno) {
this . empno = empno;
}
public String getEname ( ) {
return ename;
}
public void setEname ( String ename) {
this . ename = ename;
}
public String getJob ( ) {
return job;
}
public void setJob ( String job) {
this . job = job;
}
public Integer getMgr ( ) {
return mgr;
}
public void setMgr ( Integer mgr) {
this . mgr = mgr;
}
public Date getHiredate ( ) {
return hiredate;
}
public void setHiredate ( Date hiredate) {
this . hiredate = hiredate;
}
public Double getSal ( ) {
return sal;
}
public void setSal ( Double sal) {
this . sal = sal;
}
public Double getCommon ( ) {
return common;
}
public void setCommon ( Double common) {
this . common = common;
}
public Integer getDeptno ( ) {
return deptno;
}
public void setDeptno ( Integer deptno) {
this . deptno = deptno;
}
@Override
public String toString ( ) {
return "Emp{" +
"empno=" + empno +
", ename='" + ename + '\'' +
", job='" + job + '\'' +
", mgr=" + mgr +
", hiredate=" + hiredate +
", sal=" + sal +
", common=" + common +
", deptno=" + deptno +
'}' ;
}
}
EmpDao.java:进行数据库操作的dao层接口
package com. mashibing. dao;
import com. mashibing. bean. Emp;
public interface EmpDao {
public Emp findEmpByEmpno ( Integer empno) ;
}
mybatis-config.xml:resources下建立配置文件
<?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>
< environments default = " development" >
< environment id = " development" >
< transactionManager type = " JDBC" />
< dataSource type = " POOLED" >
< property name = " driver" value = " com.mysql.cj.jdbc.Driver" />
< property name = " url" value = " jdbc:mysql://localhost:3306/demo?serverTimezone=UTC" />
< property name = " username" value = " root" />
< property name = " password" value = " c50hst" />
</ dataSource>
</ environment>
</ environments>
< mappers>
< mapper resource = " EmpDao.xml" />
</ mappers>
</ configuration>
EmpDao.xml:resource下建立映射文件,注意这个文件名要和Dao层的类名相同,我们不必自己再写Dao层的具体实现
<?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.mashibing.dao.EmpDao" >
< select id = " findEmpByEmpno" resultType = " com.mashibing.bean.Emp" >
select * from emp where empno = #{empno}
</ select>
</ mapper>
log4j.properties:类路径上添加
# Global logging configuration
log4j.rootLogger=ERROR, stdout
# MyBatis logging configuration...
log4j.logger.com.mashibing=TRACE
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
MyTest.java:测试类
package com. mashibing. test;
import com. mashibing. bean. Emp;
import com. mashibing. dao. EmpDao;
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. Test;
import java. io. IOException;
import java. io. InputStream;
public class MyTest {
@Test
public void test01 ( ) {
String resource = "mybatis-config.xml" ;
InputStream inputStream = null;
try {
inputStream = Resources. getResourceAsStream ( resource) ;
} catch ( IOException e) {
e. printStackTrace ( ) ;
}
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder ( ) . build ( inputStream) ;
SqlSession sqlSession = sqlSessionFactory. openSession ( ) ;
Emp empByEmpno = null;
try {
EmpDao mapper = sqlSession. getMapper ( EmpDao. class ) ;
empByEmpno = mapper. findEmpByEmpno ( 7369 ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
sqlSession. close ( ) ;
}
System. out. println ( empByEmpno) ;
}
}
4 增删改查的基本操作
EmpDao.java
package com. mashibing. dao;
import com. mashibing. bean. Emp;
public interface EmpDao {
public Emp findEmpByEmpno ( Integer empno) ;
public int updateEmp ( Emp emp) ;
public int deleteEmp ( Integer empno) ;
public int insertEmp ( Emp emp) ;
}
EmpDao.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.mashibing.dao.EmpDao" >
< select id = " findEmpByEmpno" resultType = " com.mashibing.bean.Emp" >
select * from emp where empno = #{empno}
</ select>
< insert id = " insertEmp" >
insert into emp(empno,ename) values(#{empno},#{ename})
</ insert>
< update id = " updateEmp" >
update emp set ename=#{ename} where empno = #{empno}
</ update>
< delete id = " deleteEmp" >
delete from emp where empno = #{empno}
</ delete>
</ mapper>
MyTest.java
package com. mashibing. test;
import com. mashibing. bean. Emp;
import com. mashibing. dao. EmpDao;
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 org. junit. Test;
import java. io. IOException;
import java. io. InputStream;
public class MyTest {
SqlSessionFactory sqlSessionFactory = null;
@Before
public void init ( ) {
String resource = "mybatis-config.xml" ;
InputStream inputStream = null;
try {
inputStream = Resources. getResourceAsStream ( resource) ;
sqlSessionFactory= new SqlSessionFactoryBuilder ( ) . build ( inputStream) ;
} catch ( IOException e) {
e. printStackTrace ( ) ;
}
}
@Test
public void test01 ( ) {
SqlSession sqlSession = sqlSessionFactory. openSession ( ) ;
Emp empByEmpno = null;
try {
EmpDao mapper = sqlSession. getMapper ( EmpDao. class ) ;
empByEmpno = mapper. findEmpByEmpno ( 7369 ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
sqlSession. close ( ) ;
}
System. out. println ( empByEmpno) ;
}
@Test
public void test02 ( ) {
SqlSession sqlSession = sqlSessionFactory. openSession ( ) ;
EmpDao mapper = sqlSession. getMapper ( EmpDao. class ) ;
int zhangsan = mapper. insertEmp ( new Emp ( 1111 , "zhangsan" ) ) ;
System. out. println ( zhangsan) ;
sqlSession. commit ( ) ;
sqlSession. close ( ) ;
}
@Test
public void test03 ( ) {
SqlSession sqlSession = sqlSessionFactory. openSession ( ) ;
EmpDao mapper = sqlSession. getMapper ( EmpDao. class ) ;
int zhangsan = mapper. updateEmp ( new Emp ( 1111 , "lisi" ) ) ;
System. out. println ( zhangsan) ;
sqlSession. commit ( ) ;
sqlSession. close ( ) ;
}
@Test
public void test04 ( ) {
SqlSession sqlSession = sqlSessionFactory. openSession ( ) ;
EmpDao mapper = sqlSession. getMapper ( EmpDao. class ) ;
int zhangsan = mapper. deleteEmp ( 1111 ) ;
System. out. println ( zhangsan) ;
sqlSession. commit ( ) ;
sqlSession. close ( ) ;
}
}
5 注解的写法
mybatis支持使用注解的方式,可以不用写配置文件,但如果sql非常复杂,还是建议写配置文件 EmpDaoAnnotation.java
package com. mashibing. dao;
import com. mashibing. bean. Emp;
import org. apache. ibatis. annotations. Delete;
import org. apache. ibatis. annotations. Insert;
import org. apache. ibatis. annotations. Select;
import org. apache. ibatis. annotations. Update;
public interface EmpDaoAnnotation {
@Select ( "select * from emp where empno = #{empno}" )
public Emp findEmpByEmpno ( Integer empno) ;
@Update ( "update emp set ename=#{ename} where empno = #{empno}" )
public int updateEmp ( Emp emp) ;
@Delete ( "delete from emp where empno = #{empno}" )
public int deleteEmp ( Integer empno) ;
@Insert ( "insert into emp(empno,ename) values(#{empno},#{ename})" )
public int insertEmp ( Emp emp) ;
}
mybatis-config.xml
< mappers>
< mapper resource = " EmpDao.xml" />
< mapper class = " com.mashibing.dao.EmpDaoAnnotation" />
</ mappers>
测试代码
@Test
public void test06 ( ) {
SqlSession sqlSession = sqlSessionFactory. openSession ( ) ;
EmpDaoAnnotation mapper = sqlSession. getMapper ( EmpDaoAnnotation. class ) ;
int zhangsan = mapper. insertEmp ( new Emp ( 1111 , "zhangsan" ) ) ;
System. out. println ( zhangsan) ;
sqlSession. commit ( ) ;
sqlSession. close ( ) ;
}
6 配置文件详解
mybatis-config.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>
< properties resource = " db.properties" > </ properties>
< settings>
< setting name = " mapUnderscoreToCamelCase" value = " true" />
</ settings>
< typeAliases>
< package name = " com.mashibing.bean" />
</ typeAliases>
< typeHandlers>
< typeHandler handler = " " > </ typeHandler>
< package name = " " />
</ typeHandlers>
< objectFactory type = " " > </ objectFactory>
< plugins>
< plugin interceptor = " " > </ plugin>
</ plugins>
< environments default = " development" >
< environment id = " development" >
< transactionManager type = " JDBC" />
< dataSource type = " POOLED" >
< property name = " driver" value = " ${driverClassname}" />
< property name = " url" value = " ${url}" />
< property name = " username" value = " ${username}" />
< property name = " password" value = " ${password}" />
</ dataSource>
</ environment>
</ environments>
< databaseIdProvider type = " DB_VENDOR" >
< property name = " MySQL" value = " mysql" />
< property name = " SQL Server" value = " sqlserver" />
< property name = " Oracle" value = " orcl" />
</ databaseIdProvider>
< mappers>
< package name = " com.mashibing.dao" />
</ mappers>
</ configuration>