MyBatis
一、MyBatis
之前jdbc连接数据库
选择:mybatis框架连接数据库
(一)mybatis概述
Mybatis是一个ORM(Object Relational Mapping 对象关系映射)数据持久层框架.把实体类和SQL语句之间建立了映射关系,是一种半自动化(需要我们自己写sql语句)的ORM实现
MyBatis 本是apache的一个开源项目iBatis, 2010年这个项目由apache software foundation 迁移到了google code,并且改名为MyBatis 。2013年11月迁移到Github,通俗说法Ibatis3 = MyBatis
iBATIS一词来源于“internet”和“abatis”的组合,是一个基于Java的持久层框架。iBATIS提供的持久层框架包括SQL Maps和Data Access Objects(DAO)
(二)MyBatis的优点:
基于SQL语法,简单易学
能了解底层组装过程
SQL语句封装在配置文件中,便于统一管理与维护,降低了程序的耦合度
程序调试方便
二、创建工程
(一)创建project
(二)新建Module
(三)选择maven
(四)输入组织名称和工程名
(五)确定模块名
(六)点击OK按钮
(七)引入依赖 mybatis,mysql,junit
pom.xml
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.3</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.21</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
</dependencies>
<build>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
</resource>
</resources>
</build>
项目本地仓库
(八)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>
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<typeAliases>
<package name="com.tjetc.domain"/>
</typeAliases>
<environments default="dev">
<environment id="dev">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql:///aa"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/tjetc/mapper/UserMapper.xml"></mapper>
</mappers>
</configuration>
(九)写跟数据库表对应的java实体类
package com.tjetc.domain;
public class User {
private int id;
private String username;
private String password;
public User(int id, String username, String password) {
this.id = id;
this.username = username;
this.password = password;
}
public User() {
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
'}';
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
(十)映射文件UserMapper.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="test">
<select id="findById" parameterType="int" resultType="com.tjetc.domain.User">
SELECT * FROM user where id=#{id}
</select>
</mapper>
(十一)测试程序
package com.tjetc.test;
import com.tjetc.domain.User;
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.net.UnknownServiceException;
public class Test1 {
@Test
public void testFind(){
try {
// 创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis.xml"));
// 得到session对象
SqlSession session = sqlSessionFactory.openSession();
// 通过session对象操作数据库
User user = session.selectOne("test.findById", 1);
// 控制台打印user对象
System.out.println(user);
// 关闭session
session.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
(十二)运行结果
三、列表
<select id="findAll" resultType="com.tjetc.domain.User">
SELECT * FROM user
</select>
@Test
public void testList(){
try {
// 创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis.xml"));
// 得到session对象
SqlSession session = sqlSessionFactory.openSession();
// 通过session对象操作数据库
List<User> list = session.selectList("test.findAll");
// 控制台打印list对象
System.out.println(list);
// 关闭session
session.close();
} catch (IOException e) {
e.printStackTrace();
}
}
<select id="list" parameterType="string" resultType="com.tjetc.domain.User">
select * from user where username like '%${value}%'
</select>
@Test
public void testList(){
try {
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis.xml"));
SqlSession session = factory.openSession();
List<User> list = session.selectList("test.list","z");
//打印list
System.out.println("list = " + list);
//关闭session
session.close();
} catch (IOException e) {
e.printStackTrace();
}
}
四、增删改
<insert id="add" parameterType="com.tjetc.domain.User">
insert into user(username,password) values(#{username},#{password})
</insert>
<update id="update" parameterType="com.tjetc.domain.User">
update user set username=#{username},password=#{password} where id=#{id}
</update>
<delete id="del" parameterType="int">
delete from user where id=#{id}
</delete>
/**
* 增加
*/
@Test
public void testAdd() {
// 创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = null;
try {
sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis.xml"));
// 得到session对象
SqlSession session = sqlSessionFactory.openSession();
//创建user实体类对象
User user = new User("ls", "123");
int i = session.insert("test.add", user);
System.out.println(i);
//对于增删改要提交事务
session.commit();
//关闭资源
session.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 修改
*/
@Test
public void testUpdate() {
// 创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = null;
try {
sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis.xml"));
// 得到session对象
SqlSession session = sqlSessionFactory.openSession();
//创建user实体类对象
User user = new User(8,"ls2", "123456");
int i =session.update("test.update", user);
System.out.println(i);
//对于增删改要提交事务
session.commit();
//关闭资源
session.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 删除
*/
@Test
public void testDel() {
// 创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = null;
try {
sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis.xml"));
// 得到session对象
SqlSession session = sqlSessionFactory.openSession();
int i =session.delete("test.del", 8);
System.out.println(i);
//对于增删改要提交事务
session.commit();
//关闭资源
session.close();
} catch (IOException e) {
e.printStackTrace();
}
}
五、map
<select id="list" parameterType="map" resultType="com.tjetc.domain.User">
select * from user where username like '%${username}%' limit #{startRecord},#{pageSize}
</select>
@Test
public void testList2(){
try {
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis.xml"));
SqlSession session = factory.openSession();
Map<String,Object> map=new HashMap<>();
map.put("username", "s");
map.put("startRecord", 0);
map.put("pageSize",2);
List<User> list = session.selectList("test.list",map);
//打印list
System.out.println("list = " + list);
//关闭session
session.close();
} catch (IOException e) {
e.printStackTrace();
}
}
六、getCount
<select id="getCount" parameterType="string" resultType="int">
SELECT count(*) FROM user where username like '%${value}%'
</select>
@Test
public void testGetCount(){
try {
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis.xml"));
SqlSession session = factory.openSession();
Object o = session.selectOne("test.getCount", "s");
System.out.println("o = " + o);
session.close();
} catch (IOException e) {
e.printStackTrace();
}
}
七、Mybatis架构
1、 mybatis配置
SqlMapConfig.xml,此文件作为mybatis的全局配置文件,配置了mybatis的运行环境等信息。
mapper.xml文件即sql映射文件,文件中配置了操作数据库的sql语句。此文件需要在SqlMapConfig.xml中加载。
2、 通过mybatis环境等配置信息构造SqlSessionFactory即会话工厂
3、 由会话工厂创建sqlSession即会话,操作数据库需要通过sqlSession进行。
4、 通过Executor(负责动态SQL的生成和查询缓存的维护)将MappedStatement对象进行解析,sql参数转化、动态sql拼接,生成jdbc Statement对象。
5、 Mapped Statement也是mybatis一个底层封装对象,它包装了mybatis配置信息及sql映射信息等。mapper.xml文件中一个sql对应一个Mapped Statement对象,sql的id即是Mapped statement的id,当API接口层接收到调用请求时,会接收到传入SQL的ID和传入对象(可以是Map、JavaBean或者基本数据类型),Mybatis会根据SQL的ID找到对应的MappedStatement,然后根据传入参数对象对MappedStatement进行解析,解析后可以得到最终要执行的SQL语句和参数。
6、 Mapped Statement对sql执行输入参数进行定义,包括HashMap、基本类型、pojo,Executor通过Mapped Statement在执行sql前将输入的java对象映射至sql中,输入参数映射就是jdbc编程中对preparedStatement设置参数。
7、 Mapped Statement对sql执行输出结果进行定义,包括HashMap、基本类型、pojo,Executor通过Mapped Statement在执行sql后将将操作数据库的结果按照映射的配置进行转换,可以转换成HashMap、JavaBean或者基本数据类型,并将最终结果返回,输出结果映射过程相当于jdbc编程中对结果的解析处理过程,。
八、mybatis的配置文件
(一)属性文件
src/main/resources写db.properties
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql:///aa
jdbc.username=root
jdbc.password=123456
mybatis.xml
<properties resource="db.properties"></properties>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
(二)别名配置
<!-- 起别名-->
<typeAliases>
<!-- 单个起别名-->
<!-- <typeAlias type="com.tjetc.domain.User" alias="user"></typeAlias>-->
<!-- 批量起别名:com.tjetc.domain包下的所有类的别名是类名,类名首字母大小写都可以-->
<package name="com.tjetc.domain"/>
</typeAliases>
<select id="findAll" resultType="User">
SELECT * FROM user
</select>
(三)enviroments mapper
<environments default="dev">
<environment id="dev">
<!-- 使用JDBC事务过滤器-->
<transactionManager type="JDBC"></transactionManager>
<!-- POOLED 采用数据库连接池连接数据库 -->
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/tjetc/mapper/UserMapper.xml"></mapper>
</mappers>
九、Mybatis.api
SqlSessionFactory 会话的工厂
SqlSession 会话
SqlSession session = sqlSessionFactory.openSession()
查询单条对象:User user = session.selectOne(“test.findById”, 1)
查询列表:List list = session.selectList(“test.findAll”);
增加: int i = session.insert(“test.add”, user);
修改: int i =session.update(“test.update”, user);
删除: int i =session.delete(“test.del”, 8);
@Test
public void testFind() {
try {
// 创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis.xml"));
// 得到session对象
SqlSession session = sqlSessionFactory.openSession();
// 通过session对象操作数据库
User user = session.selectOne("test.findById", 1);
// 控制台打印user对象
System.out.println(user);
// 关闭session
session.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 查询所有记录
*/
@Test
public void testList() {
try {
// 创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis.xml"));
// 得到session对象
SqlSession session = sqlSessionFactory.openSession();
// 通过session对象操作数据库
List<User> list = session.selectList("test.findAll");
// 控制台打印list对象
System.out.println(list);
// 关闭session
session.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 增加
*/
@Test
public void testAdd() {
// 创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = null;
try {
sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis.xml"));
// 得到session对象
SqlSession session = sqlSessionFactory.openSession();
//创建user实体类对象
User user = new User("ls", "123");
int i = session.insert("test.add", user);
System.out.println(i);
//对于增删改要提交事务
session.commit();
//关闭资源
session.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 修改
*/
@Test
public void testUpdate() {
// 创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = null;
try {
sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis.xml"));
// 得到session对象
SqlSession session = sqlSessionFactory.openSession();
//创建user实体类对象
User user = new User(8,"ls2", "123456");
int i =session.update("test.update", user);
System.out.println(i);
//对于增删改要提交事务
session.commit();
//关闭资源
session.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 删除
*/
@Test
public void testDel() {
// 创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = null;
try {
sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis.xml"));
// 得到session对象
SqlSession session = sqlSessionFactory.openSession();
int i =session.delete("test.del", 8);
System.out.println(i);
//对于增删改要提交事务
session.commit();
//关闭资源
session.close();
} catch (IOException e) {
e.printStackTrace();
}
}
十、mybatis数据源
在 Mybatis 中我们将它的数据源 dataSource 分为以下几类:
UNPOOLED 不使用连接池的数据源
POOLED 使用连接池的数据源
JNDI 使用 JNDI 实现的数据源
十一、MyBatis 有两种事务管理类型
(1)JDBC:
这个类型直接全部使用 JDBC 的提交和回滚功能。它依靠使用连接的数据源来管理事务的作用域
(2)MANAGED:
这个类型什么不做,它从不提交、回滚和关闭连接,而是委托给第三方管理事务的全部生命周期(如Spring或者JavaEE服务器)
十二、用户名验证
1.form提交数据到servlet
2.在servlet做判断
3.跟数据库表数据比对
4.username参数到数据库表查对应的记录
5.没有代表该用户名不存在
6.把提示信息放到作用域里,将请求转发到login.jsp,进行提示信息的显示
package com.tjetc.servlet;
import com.tjetc.domain.User;
import com.tjetc.service.UserService;
import com.tjetc.service.impl.UserServiceImpl;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
@WebServlet("/userlogin")
public class UserServlet extends HttpServlet {
private UserService userService=new UserServiceImpl();
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String username = request.getParameter("username");
String password = request.getParameter("password");
System.out.println(username);
System.out.println(password);
User user=userService.findByUsername(username);
//判断user==null
if (user==null){
request.setAttribute("err_user", "该用户名不存在");
request.getRequestDispatcher("/login.jsp").forward(request, response);
return;
}else{//用户名存在
if (!password.equals(user.getPassword())){
request.setAttribute("err_pwd", "密码不正确");
request.getRequestDispatcher("/login.jsp").forward(request, response);
return;
}else{
request.getSession().setAttribute("user", user);
response.sendRedirect(request.getContextPath()+"/welcome.jsp");
}
}
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}
}
public class UserServiceImpl implements UserService {
@Override
public User findByUsername(String username) {
try {
// 创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis.xml"));
// 得到session对象
SqlSession session = sqlSessionFactory.openSession();
// 通过session对象操作数据库
User user = session.selectOne("test.findByUsername", username);
// 控制台打印user对象
System.out.println(user);
// 关闭session
session.close();
return user;
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
}
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<base href="${pageContext.request.contextPath}/">
<title>Title</title>
</head>
<body>
<form action="userlogin" method="post">
用户名:<input type="text" name="username"/>${err_user}<br/>
密码:<input type="text" name="password"/>${err_pwd}<br/>
<input type="submit" value="提交"/>
</form>
</body>
</html>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>Title</title>
</head>
<body>
欢迎${user.username}
</body>
</html>
【总结】
1.什么是Mybatis?
Mybatis是ORM数据持久层框架,实体类和sql语句的映射,半自动化的框架(需要我们自己写sql)
2.Mybatis优点?
1.简单易学
2.调试方便
3.易于维护
4.能了解底层的运行原理
3.搭建mybatis运行环境
1.新建maven工程
2.pom.xml引入依赖:
1.mybatis
2.mysql
3.junit
3.建立mybatis.xml
<configuration>
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"></setting>
</settings>
<environments default="dev">
<environment id="dev">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql:///aa?serverTimezone=GMT%2B8"></property>
<property name="username" value="root"></property>
<property name="password" value="123456"></property>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/tjetc/mapper/UserMapper.xml"></mapper>
</mappers>
</configuration>
4.表:user:id username password
5.实体类:User id,username,password setter getter Constructor toString
6.UserMapper.xml
<mapper namespace="test">
<select id="findById" parameterType="int" resultType="com.tjetc.domain.User">
select * from user where id=#{id}
</select>
</mapper>
7.测试
@Test
public void testFindById(){
SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis.xml"));
SqlSession session=factory.openSession();
User user=session.selectOne("test.findById",1);
System.out.print(user);
session.close();
}
4.列表
1.UserMapper.xml
<select id="list" parameterType="string" resultType="com.tjetc.domain.User">
select * from user where username like '%${value}%'
</select>
2.测试
@Test
public void testList(){
SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis.xml"));
SqlSession session=factory.openSession();
List<User> list=session.selectList("test.list","s");
System.out.println(list);
session.close();
}
5.增加
1.UserMapper.xml
<insert id="add" parameterType="com.tjetc.domain.User">
insert into user(username,password) values(#{username},#{password})
</insert>
2.测试
@Test
public void testAdd(){
SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis.xml"));
SqlSession session=factory.openSession();
session.insert("test.add",new User("liubei","123"));
session.commit();
session.close();
}
6.修改
1.UserMapper.xml
<update id="update" parameterType="com.tjetc.domain.User">
update user set username=#{username},password=#{password} where id=#{id}
</update>
2.测试
@Test
public void testUpdate(){
SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis.xml"));
SqlSession session=factory.openSession();
session.update("test.upadte",new User(4,"guanyu","678"));
session.commit();
session.close();
}
6.删除
1.UserMapper.xml
<delete id="del" parameterType="int">
delete from user where id=#{id}
</delete>
2.测试
@Test
public void testDel(){
SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis.xml"));
SqlSession session=factory.openSession();
session.delote("test.del",4);
session.commit();
session.close();
}
7.mybatis架构
1.mybatis配置:mybatis.xml mapper.xml
2.SqlSessionFactory
3.SqlSession
4.Executor
5.MappedStatement 操作数据库
6.入参类型:
1.int String 基本类型
2.pojo
3.map
7.出参类型:
1.int String 基本类型
2.pojo
3.ma
8.Mybatis配置
1.属性文件db.properties ${name}
2.别名
1.单个起别名 <typeAlias type="com.tjetc.domain.User" alias="user"></typeAlias>
2.批量起别名 <package name="com.tjetc.domain"/>
3.映射文件配置
<mapper resource="com/tjetc/mapper/UserMapper.xml"></mapper>
9.mybatis api
SqlSessionFactory 会话的工厂
SqlSession 会话
SqlSession session = sqlSessionFactory.openSession()
查询单条对象:User user = session.selectOne(“test.findById”, 1)
查询列表:List list = session.selectList(“test.findAll”);
增加: int i = session.insert(“test.add”, user);
修改: int i =session.update(“test.update”, user);
删除: int i =session.delete(“test.del”, 8);
10.mybatis数据源
UNPOOLED 不使用连接池的数据源
POOLED 使用连接池的数据源
JNDI 使用 JNDI 实现的数据源
11.MyBatis 有两种事务管理类型
1.JDBC
2.MANAGED 容器管理事务