mybaits是一种用Java编写的持久层框架,它使用对象关系映射(ORM)实现结果集的封装。
简单来说,就是把数据库表和实体类以及实体类的属性对应起来,让我们通过操控实体类来操作数据库表。减少了JDBC的代码和手工参数的设置,它可以使用简单的XML或者注解来配置和映射信息。
一,环境配置
1、创建动态web工程
2、导入相关的jar包
3,在src下面创建db.properties文件连接数据库
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/hqyj?useUnicode=true&characterEncoding=utf8
jdbc.username=root
jdbc.password=123456
4、在src下面创建mybatis的核心配置文件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>
//配置连接数据库的四大基本参数(db.properties)
<properties resource="db.properties"></properties>
//环境配置
<environments default="development">
<environment id="development">
//事务管理
<transactionManager type="JDBC"/>
//连接数据库(mybatis默认的连接池)
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driverClassName}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
<!-- <environment id="test">-->
<!-- <transactionManager type="JDBC"/>-->
<!-- <dataSource type="POOLED">-->
<!-- <property name="driver" value="${driver}"/>-->
<!-- <property name="url" value="${url}"/>-->
<!-- <property name="username" value="${username}"/>-->
<!-- <property name="password" value="${password}"/>-->
<!-- </dataSource>-->
<!-- </environment>-->
</environments>
//加载对应的mapper映射文件
<mappers>
<mapper resource="com\hqyj\gyq\entity\UserMapper.xml"/>
</mappers>
</configuration>
5,在src下面创建log4j.properties文件,设置输出日志格式,方便观察
log4j.rootLogger=DEBUG, Console
#设置输出日志格式,方便观察
#Console
log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%n
log4j.logger.java.sql.ResultSet=INFO
log4j.logger.org.apache=INFO
log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG
二,简单应用
Mybatis是通过SqlSession来实现与数据库会话的。
1,创建util工具类层,写一个工具类DBUtil创建sqlSessionFactory工厂,
package com.hqyj.cq.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 DBUtil {
private static SqlSessionFactory sqlSessionFactory = null;
static {
// 获取sqlSessionFactory----->得到SqlSession(类似于连接对象)
InputStream inputStream = null;
try {
//创建sqlSessionFactory工厂
inputStream = Resources.getResourceAsStream("mybatis-config.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
//使用工厂产生SqlSession对象
SqlSession session = sqlSessionFactory.openSession();
}
//执行方法
public static SqlSession getSqlSession(){
return sqlSessionFactory.openSession();
}
}
2,编写实体类层,创建实体类User
package com.hqyj.cq.entity;
import java.util.List;
public class User {
private Integer id;
private String userName;
private String userSex;
private String userAge;
private String userTel;
private String userPwd;
private IdCard idCard;
private List<Order> orders;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getUserSex() {
return userSex;
}
public void setUserSex(String userSex) {
this.userSex = userSex;
}
public String getUserAge() {
return userAge;
}
public void setUserAge(String userAge) {
this.userAge = userAge;
}
public String getUserTel() {
return userTel;
}
public void setUserTel(String userTel) {
this.userTel = userTel;
}
public String getUserPwd() {
return userPwd;
}
public void setUserPwd(String userPwd) {
this.userPwd = userPwd;
}
public IdCard getIdCard() {
return idCard;
}
public void setIdCard(IdCard idCard) {
this.idCard = idCard;
}
public List<Order> getOrders() {
return orders;
}
public void setOrders(List<Order> orders) {
this.orders = orders;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", userName='" + userName + '\'' +
", userSex='" + userSex + '\'' +
", userAge='" + userAge + '\'' +
", userTel='" + userTel + '\'' +
", userPwd='" + userPwd + '\'' +
", idCard=" + idCard +
", orders=" + orders +
'}';
}
}
3,创建mapper类层,创建接口定义方法,创建xml文件编写对应的映射文件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="com.hqyj.cq.mapper.UserMapper">
<!--当属性名和字段名不一致的时候需要配置,如果属性名和字段名保持一致则不需要配置resultMap-->
<resultMap id="findUser" type="User">
<id column="id" property="id"></id>
<result column="user_name" property="userName"></result>
<result column="user_age" property="userAge"></result>
<result column="user_sex" property="userSex"></result>
<result column="user_tel" property="userTel"></result>
<result column="user_pwd" property="userPwd"></result>
<!--配置一对一的级联操作-->
<association property="idCard" javaType="IdCard">
<id column="id" property="id"></id>
<result column="card_name" property="cardName"></result>
<result column="card_address" property="cardAddress"></result>
<result column="card_num" property="cardNum"></result>
</association>
</resultMap>
<resultMap id="findUser2" type="User">
<id column="id" property="id"></id>
<result column="user_name" property="userName"></result>
<result column="user_age" property="userAge"></result>
<result column="user_sex" property="userSex"></result>
<result column="user_tel" property="userTel"></result>
<result column="user_pwd" property="userPwd"></result>
<!--配置一对多的级联操作-->
<collection property="orders" ofType="Order">
<id column="oder_id" property="id"></id>
<result column="order_name" property="orderName"></result>
<result column="order_price" property="orderPrice"></result>
<result column="order_time" property="orderTime"></result>
</collection>
</resultMap>
<!--User queryUserById(Integer id);-->
<!--接口方式访问,id值为对应接口中的方法名-->
<select id="queryUserById" resultMap="findUser">
select * from user where id = #{id}
</select>
<!--一对一查询-->
<select id="selectUserById" resultMap="findUser">
select * from user u ,idcard i where u.id = #{id} and u.card_id=i.id
</select>
<!--一对多查询-->
<select id="selectUserById2" resultMap="findUser2">
select * ,o.id oder_id from user u ,t_order o where u.id = #{id} and u.id=o.user_id
</select>
<select id="queryUserAll" resultMap="findUser">
select * from user
</select>
<!--插入成功后获取id-->
<insert id="insertUser" keyProperty="id" useGeneratedKeys="true">
insert into user (user_name,user_age,user_sex) values (#{userName},#{userAge},#{userSex})
</insert>
<update id="updateUserById">
update user set user_sex = #{userSex} where id = #{id}
</update>
<update id="updateUser">
update user set user_sex = #{userSex} where id = #{id}
</update>
</mapper>
4,创建测试类层,写测试方法
package com.hqyj.cq.test;
import com.hqyj.cq.entity.Teacher;
import com.hqyj.cq.entity.User;
import com.hqyj.cq.mapper.TeacherMapper;
import com.hqyj.cq.mapper.UserMapper;
import com.hqyj.cq.utils.DBUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class MybatisTestMapper {
@Test
public void queryUserById(){
//获取session对象
SqlSession session = DBUtil.getSqlSession();
//使用动态代理模式创建了对应mapper接口的实现类
UserMapper mapper = session.getMapper(UserMapper.class);
//调用方法
User user = mapper.queryUserById(223);
System.out.println(user);
}
@Test
public void insertUser(){
SqlSession session = DBUtil.getSqlSession();
// 代理模式
UserMapper mapper = session.getMapper(UserMapper.class);
User user = new User();
user.setUserName("hmm666");
user.setUserSex("男");
user.setUserAge("27");
mapper.insertUser(user);
try {
mapper.updateUser(user);
//提交事务
session.commit();
}catch (Exception e){
//事物回滚
session.rollback();
}finally {
//关闭session
session.close();
}
System.out.println(user.getId());
}
}
三,sql查询
1.一对一查询
就是一个对象只有一个对应关系,在mapper文件中使用
<association property="属性名" javaType="属性类型">字段集</association>在对应的外键处插入 <resultMap></resultMap>标签中
<resultMap id="findUser" type="User">
<id column="id" property="id"></id>
<result column="user_name" property="userName"></result>
<result column="user_age" property="userAge"></result>
<result column="user_sex" property="userSex"></result>
<result column="user_tel" property="userTel"></result>
<result column="user_pwd" property="userPwd"></result>
<!--配置一对一的级联操作-->
<association property="idCard" javaType="IdCard">
<id column="id" property="id"></id>
<result column="card_name" property="cardName"></result>
<result column="card_address" property="cardAddress"></result>
<result column="card_num" property="cardNum"></result>
</association>
</resultMap>
2.一对多,多对多
一个表中的对象对应另一张表的对象有多个关系叫一对多,一对多是多对多的一个特殊情况
多对多就是一个表中的内容对应另一张表的多个内容,使用格式两者相同
<collection property="属性" ofType="属性类型">字段集 </collection>
<resultMap id="findUser2" type="User">
<id column="id" property="id"></id>
<result column="user_name" property="userName"></result>
<result column="user_age" property="userAge"></result>
<result column="user_sex" property="userSex"></result>
<result column="user_tel" property="userTel"></result>
<result column="user_pwd" property="userPwd"></result>
<collection property="orders" ofType="Order">
<id column="oder_id" property="id"></id>
<result column="order_name" property="orderName"></result>
<result column="order_price" property="orderPrice"></result>
<result column="order_time" property="orderTime"></result>
</collection>
</resultMap>
四,动态sql
主要分为,动态查询,模糊查询,批量删除,批量插入
1.动态查询
动态sql:
<where>+<if test="">结合使用:用来实现动态查询的
where标签的作用:生成关键字where,去除where后面第一个出现的and|or
test:判断条件
collection 表示遍历的数组或者集合,填写对应 key 的值
item 每次遍历给元素取的变量名
separator 每遍历元素拼接字符串
open 遍历开始拼接的字符串
close 遍历结束拼接字符串
index 遍历索引
使用场景:实现批量删除或者批量插入
<select id="selectPeople" resultMap="findPeople">
select * from t_people
<where>
<if test="pName != null">
and p_name=#{pName}
</if>
<if test="pSex != null">
and p_sex = #{pSex}
</if>
</where>
</select>
2.批量插入和删除
主要使用foreach标签进行遍历和set标签搭配
foreach参数解释
collection:collection 属性的值有三个分别是 list、array、map 三种,分别对应的参数类型 为:List、数组、map 集合。
item:表示在迭代过程中每一个元素的别名
open:前缀
close:后缀
separator:分隔符,表示迭代时每个元素之间以什么分隔
index:表示迭代中每次迭代到的下标位置
<delete id="deletePeopleByPatch">
delete from
t_people
where
id
in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>
<!--批量插入-->
<insert id="insertPeopleByPatch">
insert into
t_people (p_name,p_sex,p_age)
values
<foreach collection="peoples" item="people" separator=",">
(#{people.pName},#{people.pSex},#{people.pAge})
</foreach>
</insert>
set标签参数解释
prefix:在trim标签内sql语句加上前缀
suffix:在trim标签内sql语句加上后缀
prefixOverrides:指定去除多余的前缀内容,如:prefixOverrides=“AND”,去除trim标签内 sql语句多余的前缀"AND"。
suffixOverrides:指定去除多余的后缀内容,suffixOverrides=“OR”,去除trim标签内sql语句 多余的前缀"OR"。
<update id="updateById">
update user
<set>
<trim suffixOverrides=",">
<if test="name != null and age != ''">
name=#{name},
</if>
<if test="age != null and name != ''">
age=#{age},
</if>
</trim>
</set>
where id=#{id}
</update>
3.模糊查询
(查询以z开头的数据):
方式一(了解):
参数pName:"z%"
like #{pName}
方式二(字符串拼接)(了解):
参数pName:"z"
like '${pName}%'
方式三(掌握):
参数pName:"z"
#{pName}"%"
方式四(掌握):
参数pName:"z"
CONCAT('%',#{pName},'%')
<select id="selectPeopleLike" resultMap="findPeople">
select * from t_people where p_name like CONCAT('%', #{pName},'%')
</select>