Mybatis基础入门
Mybatis的工作流程
1.创建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>
<!-- 设置驼峰命名转换-->
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
<setting name="logImpl" value="LOG4J" />
</settings>
<!--设置默认指向的数据库-->
<environments default="dev">
<!--配置环境不同环境名字不同-->
<environment id="dev">
<!--采用JDBC方式对数据库事务进行comint/roollback-->
<transactionManager type="JDBC"/>
<!--采用连接池方式管理数据库连接-->
<dataSource type="POOLED">
<!-- 数据库驱动 -->
<property name="driver" value="com.mysql.jdbc.Driver"/>
<!-- 数据库连接地址使用Unicode编码,编码 格式是UTF-8 &是&的意思在xml文件中&有特殊意义-->
<property name="url" value="jdbc:mysql://localhost:3306/babytun?useUnicode=true&CharacterEncoding=UTF-8"/>
<!-- 数据库用户名称-->
<property name="username" value="root"/>
<!-- 数据库密码-->
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<!-- 添加映射器的声明-->
<mappers>
<mapper resource="mappers/goods.xml" />
</mappers>
</configuration>
2.创建MybatisUtils工具类专门用于生产sqlsession和关闭sqlsession对象
package com.imooc.mybatis.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.Reader;
/**
* @Package: com.imooc.mybatis.utils
* @ClassName: MybatisUtils
* @Author: Jack
* @CreateTime: 2021/3/24 0024 16:39
* @Description:创建全局唯一的SqlSessionFactory对象,生产和关闭SqlSession对象
*/
public class MybatisUtils {
// 保证全局SqlSessionFactory对象唯一
private static SqlSessionFactory sqlSessionFactory=null;
static {
Reader reader= null;
try {
// 读取配置文件
reader = Resources.getResourceAsReader("mybatis-config.xml");
// 读取文件后采用SqlSessionFactoryBuilder()方法生成sqlSessionFactory工厂
sqlSessionFactory =new SqlSessionFactoryBuilder().build(reader);
} catch (IOException e) {
e.printStackTrace();
throw new ExceptionInInitializerError(e);
}
}
// 创建SqlSession
public static SqlSession openSession(){
return sqlSessionFactory.openSession();
}
// 关闭SqlSession
public static void closeSession(SqlSession session){
if(session!=null){
session.close();
}
}
}
3.创建实体类最好与数据库中表的字段名称一致方便数据传递
package com.imooc.mybatis.entity;
/**
* @Package: com.imooc.mybatis.entity
* @ClassName: Goods
* @Author: Jack
* @CreateTime: 2021/3/24 0024 17:08
* @Description:
*/
public class Goods {
private Integer goodsId;
private String title;
private String subTitle;
private Float originalCost;
private Float currentPrice;
private Float discount;
private Integer isFreeDelivery;
private Integer categoryId;
public Integer getgoodsId() {
return goodsId;
}
public void setgoodsId(Integer goodsId) {
this.goodsId = goodsId;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getSubTitle() {
return subTitle;
}
public void setSubTitle(String subTitle) {
this.subTitle = subTitle;
}
public Float getOriginalCost() {
return originalCost;
}
public void setOriginalCost(Float originalCost) {
this.originalCost = originalCost;
}
public Float getCurrentPrice() {
return currentPrice;
}
public void setCurrentPrice(Float currentPrice) {
this.currentPrice = currentPrice;
}
public Float getDiscount() {
return discount;
}
public void setDiscount(Float discount) {
this.discount = discount;
}
public Integer getIsFreeDelivery() {
return isFreeDelivery;
}
public void setIsFreeDelivery(Integer isFreeDelivery) {
this.isFreeDelivery = isFreeDelivery;
}
public Integer getCategoryId() {
return categoryId;
}
public void setCategoryId(Integer categoryId) {
this.categoryId = categoryId;
}
}
package com.imooc.mybatis.dto;
import com.imooc.mybatis.entity.Category;
import com.imooc.mybatis.entity.Goods;
/**
* @Package: com.imooc.mybatis.dto
* @ClassName: GoodsDTO
* @Author: Jack
* @CreateTime: 2021/3/25 0025 13:54
* @Description:
*/
public class GoodsDTO {
public Goods getGoods() {
return goods;
}
public void setGoods(Goods goods) {
this.goods = goods;
}
public Category getCategory() {
return category;
}
public void setCategory(Category category) {
this.category = category;
}
// 数据传输对象
private Goods goods = new Goods();
private Category category = new Category();
}
package com.imooc.mybatis.entity;
/**
* @Package: com.imooc.mybatis.entity
* @ClassName: Category
* @Author: Jack
* @CreateTime: 2021/3/25 0025 14:24
* @Description:
*/
public class Category {
private Integer categoryId;
private String categoryName;
private Integer parentId;
private Integer categoryLevel;
private Integer categoryOrder;
public Integer getCategoryId() {
return categoryId;
}
public void setCategoryId(Integer categoryId) {
this.categoryId = categoryId;
}
public String getCategoryName() {
return categoryName;
}
public void setCategoryName(String categoryName) {
this.categoryName = categoryName;
}
public Integer getParentId() {
return parentId;
}
public void setParentId(Integer parentId) {
this.parentId = parentId;
}
public Integer getCategoryLevel() {
return categoryLevel;
}
public void setCategoryLevel(Integer categoryLevel) {
this.categoryLevel = categoryLevel;
}
public Integer getCategoryOrder() {
return categoryOrder;
}
public void setCategoryOrder(Integer categoryOrder) {
this.categoryOrder = categoryOrder;
}
}
4.创建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="goods">
<!-- 查询所有数据并且以Goods对象形式存储 resultType是结果以什么形式返回,parameterType是参数以什么类型传入-->
<select id="selectAll" resultType="com.imooc.mybatis.entity.Goods">
select *
from t_goods
order by goods_id desc
limit 10
</select>
<!-- 根据某个字段查询数据并且以Goods对象形式存储-->
<select id="selectById" parameterType="Integer" resultType="com.imooc.mybatis.entity.Goods">
select *
from t_goods
where goods_id = #{value}
</select>
<!-- 根据多个字段查询数据并且以Goods对象形式存储 -->
<select id="selectByPriceRange" parameterType="java.util.Map" resultType="com.imooc.mybatis.entity.Goods">
select *
from t_goods
where current_price between #{min} and #{max}
order by current_price
limit 0,#{limit}
</select>
<!-- 查询数据后字段按照顺序排列 -->
<select id="selectGoodsMap" resultType="java.util.LinkedHashMap">
select g.*, c.category_name
from t_goods g,
t_category c
where g.category_id = c.category_id
</select>
<!-- 将数据中的字段和DTO对象的对象的属性映射-->
<resultMap id="rmGoods" type="com.imooc.mybatis.dto.GoodsDTO">
<!-- 主键-->
<id property="goods.goodsId" column="goods_id"/>
<result property="goods.title" column="title"/>
<!-- Goods对象的属性 property是实体属性 column是表中属性-->
<result property="goods.subTitle" column="sub_title"/>
<result property="goods.originalCost" column="original_cost"/>
<result property="goods.currentPrice" column="current_price"/>
<result property="goods.discount" column="discount"/>
<result property="goods.isFreeDelivery" column="is_free_delivery"/>
<result property="goods.categoryId" column="category_id"/>
<!-- Category对象的属性 -->
<result property="category.categoryId" column="category_id"/>
<result property="category.categoryName" column="category_name"/>
<result property="category.parentId" column="parent_id"/>
<result property="category.categoryLevel" column="category_level"/>
<result property="category.categoryOrder" column="category_order"/>
</resultMap>
<!--通过DTO将两个表中的数据联合起来存储在GoodsDTO,DTO就是数据传输对象可以将多个对象整合在一个对象中 -->
<select id="selectGoodsDTO" resultMap="rmGoods">
select g.*, c.*
from t_goods g,
t_category c
where g.category_id = c.category_id
</select>
<!-- 插入数据到表中因为主键是自动增长生成所以插入方式有两种方式一种使用UseGenneratedKeys,keyProperty代表实体类属性,
keyColumn代表数据库中表的属性,第二种能够在数据插入后查询最后插入数据的id返回给实体类的属性,keyProperty代表主键属性
order代表发生在insert语句前还是后,第一种简单但是只能在可以自动生成主键的数据库中使用,第二种书写复杂但是一般数据库都能用 -->
<insert id="insert" parameterType="com.imooc.mybatis.entity.Goods"
useGeneratedKeys="true" keyProperty="goodsId" keyColumn="goods_id">
insert into t_goods(title, sub_title, original_cost, current_price, discount, is_free_delivery) values (#{title},#{subTitle},#{originalCost},#{currentPrice},#{discount},#{isFreeDelivery})
<!-- <selectKey resultType="Integer" keyProperty="goodsId" order="AFTER">-->
<!-- select last_insert_id()-->
<!-- </selectKey>-->
</insert>
<!-- 根据字段更新数据-->
<update id="update" parameterType="com.imooc.mybatis.entity.Goods">
update t_goods
set
title=#{title},
sub_title=#{subTitle},
original_cost=#{originalCost},
current_price=#{currentPrice},
discount=#{discount},
is_free_delivery=#{isFreeDelivery},
category_id=#{categoryId}
where goods_id=#{goodsId}
</update>
<!-- 根据字段删除数据-->
<delete id="delete" parameterType="Integer">
delete from t_goods where goods_id=#{goodsId}
</delete>
<!-- 分辨#{}和${}的作用,${}在程序内部还能使用不过最好不用,它采用原文拼接,#{}比较常用采用预编译的方式防止sql注入-->
<select id="xx" parameterType="java.util.Map" resultType="com.imooc.mybatis.entity.Goods">
select * from t_goods where title=#{title}
</select>
</mapper>
5.通过测试类测试SqlSession对象对数据库的增删改查操作
package com.imooc;
import com.imooc.mybatis.dto.GoodsDTO;
import com.imooc.mybatis.entity.Goods;
import com.imooc.mybatis.utils.MybatisUtils;
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.Reader;
import java.sql.Connection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @Package: com.imooc
* @ClassName: MyBatisTest
* @Author: Jack
* @CreateTime: 2021/3/24 0024 16:08
* @Description:
*/
//junit单元测试类
/*成功运行sql会将数据生成到sql事务日志需要commit提交错误需要回滚最后需要关闭sqlSession对象*/
public class MyBatisTest {
@Test
public void testSqlSessionFactory() throws IOException {
Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
//构造者模式 初始化SqlSessionFactory对象同时解析mybatis-config.xml文件
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
System.out.println("SqlSessionFacotry初始成功");
//创建一个SqlSession对象,SqlSession是JDBC的扩展类,用于与数据库的连接
SqlSession sqlSession = null;
try {
sqlSession = sqlSessionFactory.openSession();
//通过sqlSession对象获取数据库连接
Connection connection = sqlSession.getConnection();
System.out.println(connection);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (sqlSession != null) {
//如果type是pooled代表连接池,close回收到连接池
//如果是type=“UNPOOLED”,代表直连,close则会调用connection.close方法
sqlSession.close();
}
}
}
@Test
public void testMyBatisUtils() {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtils.openSession();
Connection connection = sqlSession.getConnection();
System.out.println(connection);
} catch (Exception e) {
e.printStackTrace();
} finally {
MybatisUtils.closeSession(sqlSession);
}
}
// 查询对象集合
@Test
public void testSelectALl() throws Exception {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtils.openSession();
Connection connection = sqlSession.getConnection();
System.out.println(connection);
List<Goods> list = sqlSession.selectList("goods.selectAll");
if (list != null) {
for (Goods g : list) {
System.out.println(g.getTitle() + " ");
}
} else {
System.out.println("表中无值");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (sqlSession != null)
MybatisUtils.closeSession(sqlSession);
}
}
//查询一个对象
@Test
public void testSelectById() throws Exception {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtils.openSession();
Goods goods = sqlSession.selectOne("goods.selectById", 739);
if(goods==null){
System.out.println("查找不到数据");
}else{
System.out.println(goods.getTitle());
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (sqlSession != null)
MybatisUtils.closeSession(sqlSession);
}
}
//通过多个参数查询多个对象
@Test
public void testSelectByPriceRange() throws Exception {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtils.openSession();
Map m=new HashMap();
m.put("min",100);
m.put("max",500);
m.put("limit",10);
List<Goods> list = sqlSession.selectList("goods.selectByPriceRange",m);
for(Goods g:list){
System.out.println(g.getgoodsId()+" "+g.getCurrentPrice()+" "+g.getTitle());
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (sqlSession != null)
MybatisUtils.closeSession(sqlSession);
}
}
/*利用LinkedHashMap保存多表关联结果
Mybatis会将每一条记录包装为LinkedHashMap对象
key是字段名,value是字段值,字段类型自动判断
优点:易于扩展,易于使用
缺点:太过灵活,无法进行编译时检查
* */
@Test
public void testSelectGoodsMap() throws Exception {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtils.openSession();
List<Map> list = sqlSession.selectList("goods.selectGoodsMap");
for(Map m:list){
System.out.println(m);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (sqlSession != null)
MybatisUtils.closeSession(sqlSession);
}
}
//测试数据传输对象
@Test
public void testSelectGoodsDTO() throws Exception {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtils.openSession();
List<GoodsDTO> list = sqlSession.selectList("goods.selectGoodsDTO");
for(GoodsDTO m:list){
System.out.println(m);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (sqlSession != null)
MybatisUtils.closeSession(sqlSession);
}
}
//测试数据插入
@Test
public void testInsert() throws Exception {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtils.openSession();
Goods goods=new Goods();
goods.setTitle("测试商品");
goods.setSubTitle("测试子标题");
goods.setOriginalCost(200f);
goods.setCurrentPrice(100f);
goods.setDiscount(0.5f);
goods.setIsFreeDelivery(1);
goods.setCategoryId(43);
int i=sqlSession.insert("goods.insert",goods);
System.out.println(goods.getgoodsId());
sqlSession.commit();
} catch (Exception e) {
if(sqlSession!=null){
sqlSession.rollback();
}
e.printStackTrace();
} finally {
if (sqlSession != null)
MybatisUtils.closeSession(sqlSession);
}
}
//测试数据更新
@Test
public void testUpdate() throws Exception {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtils.openSession();
Goods goods=sqlSession.selectOne("goods.selectById",739);
goods.setTitle("更新测试商品");
int num=sqlSession.update("goods.update",goods);
sqlSession.commit();
System.out.println("更新"+num+"条数据");
} catch (Exception e) {
if(sqlSession!=null){
sqlSession.rollback();
}
e.printStackTrace();
} finally {
if (sqlSession != null)
MybatisUtils.closeSession(sqlSession);
}
}
//测试数据删除
@Test
public void testDelete() throws Exception {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtils.openSession();
int num=sqlSession.delete("goods.delete",739);
sqlSession.commit();
System.out.println("删除"+num+"条数据");
} catch (Exception e) {
if(sqlSession!=null){
sqlSession.rollback();
}
e.printStackTrace();
} finally {
if (sqlSession != null)
MybatisUtils.closeSession(sqlSession);
}
}
//测试mysql数据传值和防止sql注入
@Test
public void testSql() throws Exception{
SqlSession sqlSession=null;
try {
sqlSession=MybatisUtils.openSession();
Map<String,String> m=new HashMap<String,String>();
/*传值方式有两种一种是#{}一种是${}
* #{}是会预编译
* ${}是原文拼接
* 如m.put("title","''or 1=1 or title='爱恩幼 孕妇护肤品润养颜睡眠面膜 100g'");
* 采用${}发生sql注入得到很多数据 采用#{}不发生sql注入找不到这个标题无数据出现是因为预编译了找不到
* 采用${}格式后的sql语句
* select * from t_goods where title=‘’or 1=1 or title=‘爱恩幼 孕妇护肤品润养颜睡眠面膜 100g’
* 采用#{}格式后的sql语句
* select * from t_goods where title="‘’or 1=1 or title=‘爱恩幼 孕妇护肤品润养颜睡眠面膜 100g’"
* */
m.put("title","''or 1=1 or title='爱恩幼 孕妇护肤品润养颜睡眠面膜 100g'");
List<Goods> list= sqlSession.selectList("goods.xx",m);
if(list.size()==0){
System.out.println("未找到所选商品");
}else {
for (Goods g:list){
System.out.println(g.getTitle());
}
}
}catch (Exception e){
if(sqlSession!=null){
sqlSession.rollback();
}
}
}
}
6.Maven中pom.xml引入的依赖
<?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>
<!--属性文件-->
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
<!--远程仓库下载jar包-->
<repositories>
<repository>
<id>aliyun</id>
<name>aliyun</name>
<url>https://maven.aliyun.com/repository/public</url>
</repository>
</repositories>
<!-- 依赖注入-->
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.12</version>
</dependency>
</dependencies>
</project>
7.日志文件
1.注入依赖pom.xml文件有
2.书写log4j.properties
log4j.rootLogger=DEBUG,Console
#DEBUG:级别 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
3.在mybatis-config.xml文件中设置setting
Mybatis基础入门
Mybatis的工作流程
1.创建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>
<!-- 设置驼峰命名转换-->
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
<setting name="logImpl" value="LOG4J" />
</settings>
<!--设置默认指向的数据库-->
<environments default="dev">
<!--配置环境不同环境名字不同-->
<environment id="dev">
<!--采用JDBC方式对数据库事务进行comint/roollback-->
<transactionManager type="JDBC"/>
<!--采用连接池方式管理数据库连接-->
<dataSource type="POOLED">
<!-- 数据库驱动 -->
<property name="driver" value="com.mysql.jdbc.Driver"/>
<!-- 数据库连接地址使用Unicode编码,编码 格式是UTF-8 &是&的意思在xml文件中&有特殊意义-->
<property name="url" value="jdbc:mysql://localhost:3306/babytun?useUnicode=true&CharacterEncoding=UTF-8"/>
<!-- 数据库用户名称-->
<property name="username" value="root"/>
<!-- 数据库密码-->
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<!-- 添加映射器的声明-->
<mappers>
<mapper resource="mappers/goods.xml" />
</mappers>
</configuration>
2.创建MybatisUtils工具类专门用于生产sqlsession和关闭sqlsession对象
package com.imooc.mybatis.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.Reader;
/**
* @Package: com.imooc.mybatis.utils
* @ClassName: MybatisUtils
* @Author: Jack
* @CreateTime: 2021/3/24 0024 16:39
* @Description:创建全局唯一的SqlSessionFactory对象,生产和关闭SqlSession对象
*/
public class MybatisUtils {
// 保证全局SqlSessionFactory对象唯一
private static SqlSessionFactory sqlSessionFactory=null;
static {
Reader reader= null;
try {
// 读取配置文件
reader = Resources.getResourceAsReader("mybatis-config.xml");
// 读取文件后采用SqlSessionFactoryBuilder()方法生成sqlSessionFactory工厂
sqlSessionFactory =new SqlSessionFactoryBuilder().build(reader);
} catch (IOException e) {
e.printStackTrace();
throw new ExceptionInInitializerError(e);
}
}
// 创建SqlSession
public static SqlSession openSession(){
return sqlSessionFactory.openSession();
}
// 关闭SqlSession
public static void closeSession(SqlSession session){
if(session!=null){
session.close();
}
}
}
3.创建实体类最好与数据库中表的字段名称一致方便数据传递
package com.imooc.mybatis.entity;
/**
* @Package: com.imooc.mybatis.entity
* @ClassName: Goods
* @Author: Jack
* @CreateTime: 2021/3/24 0024 17:08
* @Description:
*/
public class Goods {
private Integer goodsId;
private String title;
private String subTitle;
private Float originalCost;
private Float currentPrice;
private Float discount;
private Integer isFreeDelivery;
private Integer categoryId;
public Integer getgoodsId() {
return goodsId;
}
public void setgoodsId(Integer goodsId) {
this.goodsId = goodsId;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getSubTitle() {
return subTitle;
}
public void setSubTitle(String subTitle) {
this.subTitle = subTitle;
}
public Float getOriginalCost() {
return originalCost;
}
public void setOriginalCost(Float originalCost) {
this.originalCost = originalCost;
}
public Float getCurrentPrice() {
return currentPrice;
}
public void setCurrentPrice(Float currentPrice) {
this.currentPrice = currentPrice;
}
public Float getDiscount() {
return discount;
}
public void setDiscount(Float discount) {
this.discount = discount;
}
public Integer getIsFreeDelivery() {
return isFreeDelivery;
}
public void setIsFreeDelivery(Integer isFreeDelivery) {
this.isFreeDelivery = isFreeDelivery;
}
public Integer getCategoryId() {
return categoryId;
}
public void setCategoryId(Integer categoryId) {
this.categoryId = categoryId;
}
}
package com.imooc.mybatis.dto;
import com.imooc.mybatis.entity.Category;
import com.imooc.mybatis.entity.Goods;
/**
* @Package: com.imooc.mybatis.dto
* @ClassName: GoodsDTO
* @Author: Jack
* @CreateTime: 2021/3/25 0025 13:54
* @Description:
*/
public class GoodsDTO {
public Goods getGoods() {
return goods;
}
public void setGoods(Goods goods) {
this.goods = goods;
}
public Category getCategory() {
return category;
}
public void setCategory(Category category) {
this.category = category;
}
// 数据传输对象
private Goods goods = new Goods();
private Category category = new Category();
}
package com.imooc.mybatis.entity;
/**
* @Package: com.imooc.mybatis.entity
* @ClassName: Category
* @Author: Jack
* @CreateTime: 2021/3/25 0025 14:24
* @Description:
*/
public class Category {
private Integer categoryId;
private String categoryName;
private Integer parentId;
private Integer categoryLevel;
private Integer categoryOrder;
public Integer getCategoryId() {
return categoryId;
}
public void setCategoryId(Integer categoryId) {
this.categoryId = categoryId;
}
public String getCategoryName() {
return categoryName;
}
public void setCategoryName(String categoryName) {
this.categoryName = categoryName;
}
public Integer getParentId() {
return parentId;
}
public void setParentId(Integer parentId) {
this.parentId = parentId;
}
public Integer getCategoryLevel() {
return categoryLevel;
}
public void setCategoryLevel(Integer categoryLevel) {
this.categoryLevel = categoryLevel;
}
public Integer getCategoryOrder() {
return categoryOrder;
}
public void setCategoryOrder(Integer categoryOrder) {
this.categoryOrder = categoryOrder;
}
}
4.创建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="goods">
<!-- 查询所有数据并且以Goods对象形式存储 resultType是结果以什么形式返回,parameterType是参数以什么类型传入-->
<select id="selectAll" resultType="com.imooc.mybatis.entity.Goods">
select *
from t_goods
order by goods_id desc
limit 10
</select>
<!-- 根据某个字段查询数据并且以Goods对象形式存储-->
<select id="selectById" parameterType="Integer" resultType="com.imooc.mybatis.entity.Goods">
select *
from t_goods
where goods_id = #{value}
</select>
<!-- 根据多个字段查询数据并且以Goods对象形式存储 -->
<select id="selectByPriceRange" parameterType="java.util.Map" resultType="com.imooc.mybatis.entity.Goods">
select *
from t_goods
where current_price between #{min} and #{max}
order by current_price
limit 0,#{limit}
</select>
<!-- 查询数据后字段按照顺序排列 -->
<select id="selectGoodsMap" resultType="java.util.LinkedHashMap">
select g.*, c.category_name
from t_goods g,
t_category c
where g.category_id = c.category_id
</select>
<!-- 将数据中的字段和DTO对象的对象的属性映射-->
<resultMap id="rmGoods" type="com.imooc.mybatis.dto.GoodsDTO">
<!-- 主键-->
<id property="goods.goodsId" column="goods_id"/>
<result property="goods.title" column="title"/>
<!-- Goods对象的属性 property是实体属性 column是表中属性-->
<result property="goods.subTitle" column="sub_title"/>
<result property="goods.originalCost" column="original_cost"/>
<result property="goods.currentPrice" column="current_price"/>
<result property="goods.discount" column="discount"/>
<result property="goods.isFreeDelivery" column="is_free_delivery"/>
<result property="goods.categoryId" column="category_id"/>
<!-- Category对象的属性 -->
<result property="category.categoryId" column="category_id"/>
<result property="category.categoryName" column="category_name"/>
<result property="category.parentId" column="parent_id"/>
<result property="category.categoryLevel" column="category_level"/>
<result property="category.categoryOrder" column="category_order"/>
</resultMap>
<!--通过DTO将两个表中的数据联合起来存储在GoodsDTO,DTO就是数据传输对象可以将多个对象整合在一个对象中 -->
<select id="selectGoodsDTO" resultMap="rmGoods">
select g.*, c.*
from t_goods g,
t_category c
where g.category_id = c.category_id
</select>
<!-- 插入数据到表中因为主键是自动增长生成所以插入方式有两种方式一种使用UseGenneratedKeys,keyProperty代表实体类属性,
keyColumn代表数据库中表的属性,第二种能够在数据插入后查询最后插入数据的id返回给实体类的属性,keyProperty代表主键属性
order代表发生在insert语句前还是后,第一种简单但是只能在可以自动生成主键的数据库中使用,第二种书写复杂但是一般数据库都能用 -->
<insert id="insert" parameterType="com.imooc.mybatis.entity.Goods"
useGeneratedKeys="true" keyProperty="goodsId" keyColumn="goods_id">
insert into t_goods(title, sub_title, original_cost, current_price, discount, is_free_delivery) values (#{title},#{subTitle},#{originalCost},#{currentPrice},#{discount},#{isFreeDelivery})
<!-- <selectKey resultType="Integer" keyProperty="goodsId" order="AFTER">-->
<!-- select last_insert_id()-->
<!-- </selectKey>-->
</insert>
<!-- 根据字段更新数据-->
<update id="update" parameterType="com.imooc.mybatis.entity.Goods">
update t_goods
set
title=#{title},
sub_title=#{subTitle},
original_cost=#{originalCost},
current_price=#{currentPrice},
discount=#{discount},
is_free_delivery=#{isFreeDelivery},
category_id=#{categoryId}
where goods_id=#{goodsId}
</update>
<!-- 根据字段删除数据-->
<delete id="delete" parameterType="Integer">
delete from t_goods where goods_id=#{goodsId}
</delete>
<!-- 分辨#{}和${}的作用,${}在程序内部还能使用不过最好不用,它采用原文拼接,#{}比较常用采用预编译的方式防止sql注入-->
<select id="xx" parameterType="java.util.Map" resultType="com.imooc.mybatis.entity.Goods">
select * from t_goods where title=#{title}
</select>
</mapper>
5.通过测试类测试SqlSession对象对数据库的增删改查操作
package com.imooc;
import com.imooc.mybatis.dto.GoodsDTO;
import com.imooc.mybatis.entity.Goods;
import com.imooc.mybatis.utils.MybatisUtils;
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.Reader;
import java.sql.Connection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @Package: com.imooc
* @ClassName: MyBatisTest
* @Author: Jack
* @CreateTime: 2021/3/24 0024 16:08
* @Description:
*/
//junit单元测试类
/*成功运行sql会将数据生成到sql事务日志需要commit提交错误需要回滚最后需要关闭sqlSession对象*/
public class MyBatisTest {
@Test
public void testSqlSessionFactory() throws IOException {
Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
//构造者模式 初始化SqlSessionFactory对象同时解析mybatis-config.xml文件
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
System.out.println("SqlSessionFacotry初始成功");
//创建一个SqlSession对象,SqlSession是JDBC的扩展类,用于与数据库的连接
SqlSession sqlSession = null;
try {
sqlSession = sqlSessionFactory.openSession();
//通过sqlSession对象获取数据库连接
Connection connection = sqlSession.getConnection();
System.out.println(connection);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (sqlSession != null) {
//如果type是pooled代表连接池,close回收到连接池
//如果是type=“UNPOOLED”,代表直连,close则会调用connection.close方法
sqlSession.close();
}
}
}
@Test
public void testMyBatisUtils() {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtils.openSession();
Connection connection = sqlSession.getConnection();
System.out.println(connection);
} catch (Exception e) {
e.printStackTrace();
} finally {
MybatisUtils.closeSession(sqlSession);
}
}
// 查询对象集合
@Test
public void testSelectALl() throws Exception {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtils.openSession();
Connection connection = sqlSession.getConnection();
System.out.println(connection);
List<Goods> list = sqlSession.selectList("goods.selectAll");
if (list != null) {
for (Goods g : list) {
System.out.println(g.getTitle() + " ");
}
} else {
System.out.println("表中无值");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (sqlSession != null)
MybatisUtils.closeSession(sqlSession);
}
}
//查询一个对象
@Test
public void testSelectById() throws Exception {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtils.openSession();
Goods goods = sqlSession.selectOne("goods.selectById", 739);
if(goods==null){
System.out.println("查找不到数据");
}else{
System.out.println(goods.getTitle());
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (sqlSession != null)
MybatisUtils.closeSession(sqlSession);
}
}
//通过多个参数查询多个对象
@Test
public void testSelectByPriceRange() throws Exception {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtils.openSession();
Map m=new HashMap();
m.put("min",100);
m.put("max",500);
m.put("limit",10);
List<Goods> list = sqlSession.selectList("goods.selectByPriceRange",m);
for(Goods g:list){
System.out.println(g.getgoodsId()+" "+g.getCurrentPrice()+" "+g.getTitle());
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (sqlSession != null)
MybatisUtils.closeSession(sqlSession);
}
}
/*利用LinkedHashMap保存多表关联结果
Mybatis会将每一条记录包装为LinkedHashMap对象
key是字段名,value是字段值,字段类型自动判断
优点:易于扩展,易于使用
缺点:太过灵活,无法进行编译时检查
* */
@Test
public void testSelectGoodsMap() throws Exception {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtils.openSession();
List<Map> list = sqlSession.selectList("goods.selectGoodsMap");
for(Map m:list){
System.out.println(m);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (sqlSession != null)
MybatisUtils.closeSession(sqlSession);
}
}
//测试数据传输对象
@Test
public void testSelectGoodsDTO() throws Exception {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtils.openSession();
List<GoodsDTO> list = sqlSession.selectList("goods.selectGoodsDTO");
for(GoodsDTO m:list){
System.out.println(m);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (sqlSession != null)
MybatisUtils.closeSession(sqlSession);
}
}
//测试数据插入
@Test
public void testInsert() throws Exception {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtils.openSession();
Goods goods=new Goods();
goods.setTitle("测试商品");
goods.setSubTitle("测试子标题");
goods.setOriginalCost(200f);
goods.setCurrentPrice(100f);
goods.setDiscount(0.5f);
goods.setIsFreeDelivery(1);
goods.setCategoryId(43);
int i=sqlSession.insert("goods.insert",goods);
System.out.println(goods.getgoodsId());
sqlSession.commit();
} catch (Exception e) {
if(sqlSession!=null){
sqlSession.rollback();
}
e.printStackTrace();
} finally {
if (sqlSession != null)
MybatisUtils.closeSession(sqlSession);
}
}
//测试数据更新
@Test
public void testUpdate() throws Exception {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtils.openSession();
Goods goods=sqlSession.selectOne("goods.selectById",739);
goods.setTitle("更新测试商品");
int num=sqlSession.update("goods.update",goods);
sqlSession.commit();
System.out.println("更新"+num+"条数据");
} catch (Exception e) {
if(sqlSession!=null){
sqlSession.rollback();
}
e.printStackTrace();
} finally {
if (sqlSession != null)
MybatisUtils.closeSession(sqlSession);
}
}
//测试数据删除
@Test
public void testDelete() throws Exception {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtils.openSession();
int num=sqlSession.delete("goods.delete",739);
sqlSession.commit();
System.out.println("删除"+num+"条数据");
} catch (Exception e) {
if(sqlSession!=null){
sqlSession.rollback();
}
e.printStackTrace();
} finally {
if (sqlSession != null)
MybatisUtils.closeSession(sqlSession);
}
}
//测试mysql数据传值和防止sql注入
@Test
public void testSql() throws Exception{
SqlSession sqlSession=null;
try {
sqlSession=MybatisUtils.openSession();
Map<String,String> m=new HashMap<String,String>();
/*传值方式有两种一种是#{}一种是${}
* #{}是会预编译
* ${}是原文拼接
* 如m.put("title","''or 1=1 or title='爱恩幼 孕妇护肤品润养颜睡眠面膜 100g'");
* 采用${}发生sql注入得到很多数据 采用#{}不发生sql注入找不到这个标题无数据出现是因为预编译了找不到
* 采用${}格式后的sql语句
* select * from t_goods where title=‘’or 1=1 or title=‘爱恩幼 孕妇护肤品润养颜睡眠面膜 100g’
* 采用#{}格式后的sql语句
* select * from t_goods where title="‘’or 1=1 or title=‘爱恩幼 孕妇护肤品润养颜睡眠面膜 100g’"
* */
m.put("title","''or 1=1 or title='爱恩幼 孕妇护肤品润养颜睡眠面膜 100g'");
List<Goods> list= sqlSession.selectList("goods.xx",m);
if(list.size()==0){
System.out.println("未找到所选商品");
}else {
for (Goods g:list){
System.out.println(g.getTitle());
}
}
}catch (Exception e){
if(sqlSession!=null){
sqlSession.rollback();
}
}
}
}
6.Maven中pom.xml引入的依赖
<?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>
<!--属性文件-->
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
<!--远程仓库下载jar包-->
<repositories>
<repository>
<id>aliyun</id>
<name>aliyun</name>
<url>https://maven.aliyun.com/repository/public</url>
</repository>
</repositories>
<!-- 依赖注入-->
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.12</version>
</dependency>
</dependencies>
</project>
7.日志文件
1.注入依赖pom.xml文件有
2.书写log4j.properties
log4j.rootLogger=DEBUG,Console
#DEBUG:级别 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
3.在mybatis-config.xml文件中设置setting