目录
一 、概述
MyBatis 是一个开源、轻量级的数据持久化框架,是 JDBC 和 Hibernate 的替代方案。MyBatis 内部封装了 JDBC,简化了加载驱动、创建连接、创建 statement 等繁杂的过程,开发者只需要关注 SQL 语句本身。
什么是持久化?
数据持久化是将内存中的数据模型转换为存储模型,以及将存储模型转换为内存中数据模型的统称。例如,文件的存储、数据的读取以及对数据表的增删改查等都是数据持久化操作。
MyBatis 支持定制化 SQL、存储过程以及高级映射,可以在实体类和 SQL 语句之间建立mapping映射关系,是一种半自动化的 ORM 实现。其封装性低于 Hibernate,但性能优秀、小巧、简单易学、应用广泛。
什么是ORM?
ORM(Object Relational Mapping,对象关系映射)是一种数据持久化技术,它在对象模型和关系型数据库之间建立起对应关系,并且提供了一种机制,通过 JavaBean 对象去操作数据库表中的数据。
当然需要导入依赖包
官网地址:https://github.com/mybatis/mybatis-3/releases
二 、SQL映射文件
<?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.apesource.mapper.WebsiteMapper">
<!-- 添加一个网站 -->
<insert id="insertWebsite" parameterType="com.apesource.entity.Website">
insert into website
(name,url,age,country)
values(#{name},#{url},#{age},#{country})
</insert>
<!-- 查询所有网站信息 -->
<select id="selectAllWebsite"
resultType="com.apesource.entity.Website">
select * from website
</select>
</mapper>
三 、核心配置文件
<?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="LOG4J" />
</settings>
<!-- 配置mybatis运行环境 -->
<environments default="development">
<environment id="development">
<!-- 使用JDBC的事务管理 -->
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<!-- MySQL数据库驱动 -->
<property name="driver" value="com.mysql.cj.jdbc.Driver" />
<!-- 连接数据库的URL -->
<property name="url"
value="jdbc:mysql://localhost:3306/my_db?charset=utf8mb4&useSSL=false&useTimezone=true&serverTimezone=GMT%2B8" />
<property name="username" value="root" />
<property name="password" value="......" />
</dataSource>
</environment>
</environments>
<!-- 将mapper文件加入到配置文件中 -->
<mappers>
<!-- 按照路径加载-->
<!-- <mapper resource="com/cc/mapper/WebsiteMapper.xml" />-->
<!-- <mapper resource="com/cc/mapper/UserMapper.xml" />-->
<!-- 按照包路径加载-->
<!-- <mapper class="com.cc.mapper.UserMapper"/>-->
<!-- <mapper class="com.cc.mapper.WebsiteMapper"/>-->
<!-- 将该包类所有加载啊-->
<package name="com.cc.mapper"/>
</mappers>
</configuration>
四 、日志文件
注意改包名
# Global logging configuration
log4j.rootLogger=ERROR,stdout
# MyBatis logging configuration...
log4j.logger.com.cc=DEBUG
# 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
五 、MyBatisUtils工具类
public class MyBatisUtils {
private static SqlSessionFactory factory = null;
static {
String config = "mybatis-config.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;
}
}
六 、一些简单的查询案例
website实体类
public class Website {
private int id;
private String name;
private String url;
private int age;
private int alexa;
private int uv;
private String country;
public String getCountry() {
return country;
}
public void setCountry(String country) {
this.country = country;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public int getAlexa() {
return alexa;
}
public void setAlexa(int alexa) {
this.alexa = alexa;
}
public int getUv() {
return uv;
}
public void setUv(int uv) {
this.uv = uv;
}
@Override
public String toString() {
return "Website{" +
"id=" + id +
", name='" + name + '\'' +
", url='" + url + '\'' +
", age=" + age +
", alexa=" + alexa +
", uv=" + uv +
", country='" + country + '\'' +
'}';
}
}
WebsiteMapper接口
public interface WebsiteMapper {
// 添加一个网站
int insertWebsite(Website website);
//查询所有网站信息
List<Website> selectAllWebsite();
//根据name模糊查询
List<Website> selectWebsiteLikeName(String name);
//根据age区间查询
List<Website> selectWebsiteBetweenAge(@Param("ParamBegin") int begin,@Param("ParamEnd")int end);
//模糊 分段查询
List<Website> selectWebsiteLikeCondition(WebsiteCondition websiteCondition);
//返回一个结果
int selectWebsiteCount();
//返回一条记录 多个结果
Map<String,Integer> selectWebsiteInfo1();
//返回多条记录
List<Map<String,?>> selectWebsiteInfo2();
}
WebsiteMapper.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.cc.mapper.WebsiteMapper">
<!-- 添加一个网站 -->
<insert id="insertWebsite" parameterType="com.cc.entity.Website">
insert into website
(name,url,age,country,alexa,uv)
values(#{name},#{url},#{age},#{country},#{alexa},#{uv})
</insert>
<!-- 查询所有网站信息 -->
<select id="selectAllWebsite"
resultType="com.cc.entity.Website">
select * from website
</select>
<!-- 根据name模糊查询-->
<select id="selectWebsiteLikeName" resultType="com.cc.entity.Website" parameterType="string">
select * from website where name like concat('%',#{name},'%')
</select>
<!-- 根据age区间查询-->
<select id="selectWebsiteBetweenAge" resultType="com.cc.entity.Website" >
select * from Website where age between #{ParamBegin} and #{ParamEnd}
</select>
<!-- 模糊 分段查询-->
<select id="selectWebsiteLikeCondition" resultType="com.cc.entity.Website" parameterType="com.cc.entity.condition.WebsiteCondition">
select * from Website
where name like concat('%',#{name},'%') or
url like concat('%',#{url},'%') or
age between #{ageBegin} and #{ageEnd} or
alexa between #{alexaBegin} and #{alexaEnd} or
uv between #{uvBegin} and #{uvEnd} or
country = #{country}
</select>
<!-- 聚合函数-->
<!-- 返回一个结果-->
<select id="selectWebsiteCount" resultType="int">
select count(id) from Website
</select>
<!-- 返回一条记录 多个结果-->
<select id="selectWebsiteInfo1" resultType="map">
select count(id) as count,max(uv) as max,min(uv) as min,avg(uv) as avg from Website
</select>
<!-- 返回多条记录-->
<select id="selectWebsiteInfo2" resultType="map">
select count(id) as count,max(uv) as max,min(uv) as min,avg(uv) as avg from Website group by country
</select>
</mapper>
七 、联合查询案例
User实体类
public class User {
private int id;
private String name;
private String pwd;
private List<Order> orderList;
@Override
public String toString() {
return "User [id=" + id + ", name=" + name + ", orderList=" + orderList + "]";
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
public List<Order> getOrderList() {
return orderList;
}
public void setOrderList(List<Order> orderList) {
this.orderList = orderList;
}
}
Product实体类
public class Product {
private int pid;
private String name;
private Double price;
private List<Order> orders;
public int getPid() {
return pid;
}
public void setPid(int pid) {
this.pid = pid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Double getPrice() {
return price;
}
public void setPrice(Double price) {
this.price = price;
}
public List<Order> getOrders() {
return orders;
}
public void setOrders(List<Order> orders) {
this.orders = orders;
}
@Override
public String toString() {
return "Product [id=" + pid + ", name=" + name + ", price=" + price + "]";
}
}
Order实体类
public class Order {
private int orderid;
private int ordernum;
private User user;
private List<Product> products;
public User getUser() {
return user;
}
public List<Product> getProducts() {
return products;
}
public void setProducts(List<Product> products) {
this.products = products;
}
public void setUser(User user) {
this.user = user;
}
@Override
public String toString() {
return "Order{" +
"orderid=" + orderid +
", ordernum=" + ordernum +
", user=" + user +
'}';
}
public int getOrderid() {
return orderid;
}
public void setOrderid(int orderid) {
this.orderid = orderid;
}
public int getOrdernum() {
return ordernum;
}
public void setOrdernum(int ordernum) {
this.ordernum = ordernum;
}
}
UserMapper接口
public interface UserMapper {
//根据id查询User 分布查询多个订单
public User selectUserOrderById1(int id);
//根据id 一次查询多个订单
public User selectUserOrderById2(int id);
}
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.cc.mapper.UserMapper">
<resultMap type="com.cc.entity.User" id="userResultMap1">
<id property="id" column="id" />
<result property="name" column="name" />
<result property="pwd" column="pwd" />
<!-- 一对多级联查询,ofType表示集合中的元素类型,将id传递给selectOrderById -->
<collection property="orderList"
ofType="com.cc.entity.Order" column="id"
select="com.cc.mapper.OrderMapper.selectOrderById" />
</resultMap>
<select id="selectUserOrderById1" parameterType="Integer"
resultMap="userResultMap1">
select * from user where id=#{id}
</select>
<resultMap type="com.cc.entity.User"
id="userResultMap2">
<id property="id" column="id" />
<result property="name" column="name" />
<result property="pwd" column="pwd" />
<!-- 一对多级联查询,ofType表示集合中的元素类型 -->
<collection property="orderList"
ofType="com.cc.entity.Order">
<id property="orderid" column="orderid" />
<result property="ordernum" column="ordernum" />
</collection>
</resultMap>
<select id="selectUserOrderById2" parameterType="Integer"
resultMap="userResultMap2">
SELECT u.*,o.orderid,o.ordernum FROM `user` u
INNER JOIN `order_info` o ON o.userId = u.id
WHERE u.id=#{id}
</select>
</mapper>
OrderMapper接口
public interface OrderMapper {
//根据用户id查询所有订单
List<Order> selectOrderById(int userId);
//根据订单id查询用户 所有商品
Order selectOrderByOrderId(int orderId);
}
OrderMapper.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.cc.mapper.OrderMapper">
<!-- 根据用户编号,查询该用户的所有订单 -->
<select id="selectOrderById"
resultType="com.cc.entity.Order" parameterType="Integer">
SELECT * FROM order_info where userId =#{id}
</select>
<resultMap id="orderResultMap" type="com.cc.entity.Order">
<id property="orderid" column="orderid"/>
<result property="ordernum" column="ordernum"/>
<association property="user" javaType="com.cc.entity.User">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="pwd" column="pwd"/>
</association>
<collection property="products" ofType="com.cc.entity.Product">
<id property="pid" column="pid"/>
<result property="name" column="pname"/>
<result property="price" column="price"/>
</collection>
</resultMap>
<select id="selectOrderByOrderId" resultMap="orderResultMap">
SELECT oi.*,u.*,p.pid as pid,p.name as pname,p.price as price
FROM order_info AS oi
JOIN user AS u ON u.id=oi.userId
JOIN order_detail AS od ON od.order_id =oi.orderid
JOIN product_info AS p ON p.pid = od.product_id
WHERE oi.orderid = #{id}
</select>
</mapper>
测试类
public class test {
public static void main(String[] args) {
// try (SqlSession sqlSession = MyBatisUtils.getSqlSession()) {
// UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
// User user = userMapper.selectUserOrderById2(2);
// System.out.println("用户姓名:" + user.getName());
// System.out.println("订单总数:" + user.getOrderList().size());
// System.out.println("订单详情:" + user.getOrderList());
// } catch (Exception e) {
// e.printStackTrace();
// }
try(SqlSession sqlSession = MyBatisUtils.getSqlSession()){
OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
// 按照订单查询订单信息及用户信息
Order order = orderMapper.selectOrderByOrderId(2);
System.out.println("订单编号:" + order.getOrdernum());
System.out.println("订单用户:" + order.getUser().getName());
System.out.println("订单中的商品列表");
for (Product product : order.getProducts()) {
System.out.println(product);
}
}
}
}