MyBatis开发框架
开发步骤
- 创建user表,添加数据
- 创建模块,导入坐标
- 编写MyBatis核心配置文件 – >替换连接信息,解决硬编码问题
- 编写SQL映射文件 --> 统一管理sql语句,解决硬编码问题
- 编码:
– 定义POJO类
– 加载核心配置文件,获取SqlSessionFactory对象
– 获取SqlSession对象,执行SQL语句
– 释放资源
项目结构:
- 创建user表,添加数据
- 创建模块,导入坐标
<dependencies>
<dependency>
<!--导入MyBatis坐标依赖-->
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.9</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.8</version>
</dependency>
<!--其他的基本配置,日志、单元测试、jdbc——jar包-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.30</version>
</dependency>
<!-- <dependency>-->
<!-- <groupId>org.slf4j</groupId>-->
<!-- <artifactId>slf4j-api</artifactId>-->
<!-- <version>1.7.12</version>-->
<!-- </dependency>-->
<!-- <dependency>-->
<!-- <groupId>log4j</groupId>-->
<!-- <artifactId>log4j</artifactId>-->
<!-- <version>1.2.17</version>-->
<!-- </dependency>-->
<!-- 添加logback-classic依赖 -->
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>1.2.3</version>
</dependency>
<!-- 添加logback-core依赖 -->
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-core</artifactId>
<version>1.2.3</version>
</dependency>
</dependencies>
- 编写MyBatis核心配置文件 – >替换连接信息,解决硬编码问题
lock.xml
<?xml version="1.0" encoding="UTF-8"?>
<configuration>
<appender name="CONSOLE" class="ch.qos.logback.core.ConsoleAppender">
<target>System.out</target>
<encoder>
<!-- 日志输出格式:%d表示日期时间,%-5level:日志级别 ,%c取类 %thread表示线程名(在哪个方法执行),
%msg:日志消息,%n是换行符 ,不会用就去百度一下logback的日志格式-->
<pattern>%d{yyyy-MM-dd HH:mm:ss.SSS}[%-5level] %c [%thread] :%msg%n</pattern>
</encoder>
</appender>
<!-- 滚动记录文件,先将日志记录到指定文件,当符合某个条件时,将日志记录到其他文件 -->
<appender name="FILE"
class="ch.qos.logback.core.rolling.RollingFileAppender">
<encoder>
<pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{36}-%msg%n</pattern>
<charset>utf-8</charset>
</encoder>
<!-- 日志输出路径 -->
<file>D:/zhu/logback/itheima-data1.log</file>
<!-- 指定日志文件拆分和压缩规则 -->
<rollingPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedRollingPolicy">
<!-- 通过指定压缩文件的名称,来确定分割文件方式 -->
<fileNamePattern>D:/DataSpace/java_project/Spring/MyBatis/logback/itheima-data2-%d{yyyy-MMdd}.log%i.gz
</fileNamePattern>
<!-- 文件拆分大小 -->
<maxFileSize>1MB</maxFileSize>
</rollingPolicy>
</appender>
<!-- level:用来设计打印级别,大小写无关:TRACE,DEBUG,INFO,WARN,ERROR,ALL和OFF,默认debug
<root>可以包含零个或多个<appender-ref>元素,标识这个输出位置将会被本日志级别控制。
-->
<root level="ALL">
<!-- 注意:如果这里不配置关联打印位置,该位置将不会记录日志 -->
<appender-ref ref="CONSOLE"/>
<appender-ref ref="FILE"/>
</root>
</configuration>
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>
<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/mybatis?useSSL=false"/>
<property name="username" value="sa"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="UserMapper.xml"/>
</mappers>
</configuration>
- 编写SQL映射文件 --> 统一管理sql语句,解决硬编码问题
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="test1"> //test1表示每一个数据库操作的模块名
<select id="selectAll" resultType="com.example.MyBatis.pojo.User"> //id 实行操作的唯一标识,调用为 test1.selectAll
select * from tb_user //tb_user 代表表名
</select>
</mapper>
- 编码:
– 定义POJO类
– 加载核心配置文件,获取SqlSessionFactory对象
– 获取SqlSession对象,执行SQL语句
– 释放资源
public class MyBatisDemo {
public static void main(String[] args) throws IOException {
//加载mybatis核心配置文件,获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//获取SqlSession对象,用来执行sql
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行sql
List<User> Users = sqlSession.selectList("test1.selectAll");
System.out.println(Users);
//释放资源
sqlSession.close();
}
}
进行包名整合,使配置文件和类文件加载在一起:
没有Run Maven需要插件安装Maven Helper
出现这个问题:
添加 log4j.properties
log4j.rootLogger=debug,stdout,D,E
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{yyyy-MM-dd HH\:mm\:ss} %p %c\:%L - %m%n
log4j.appender.D=org.apache.log4j.DailyRollingFileAppender
log4j.appender.D.File=D://DataSpace/java_project/logs/log.log
log4j.appender.D.Append=true
log4j.appender.D.Threshold=DEBUG
log4j.appender.D.layout=org.apache.log4j.PatternLayout
log4j.appender.D.layout.ConversionPattern=%d{yyyy-MM-dd HH\:mm\:ss} %p %c\:%L - %m%n
log4j.appender.E=org.apache.log4j.DailyRollingFileAppender
log4j.appender.E.File=D://DataSpace/java_project/logs/log.log
log4j.appender.E.Append=true
log4j.appender.E.Threshold=ERROR
log4j.appender.E.layout=org.apache.log4j.PatternLayout
log4j.appender.E.layout.ConversionPattern=%-d{yyyy-MM-dd HH:mm:ss} [ %t:%r ] - [ %p ] %m%n
项目:
数据库查询:
文件配置
brand类对象:
public class Brand {
private int id;
private String brandName;
private String companyName;
private String ordered;
private String description;
private int status;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getBrandName() {
return brandName;
}
public void setBrandName(String brandName) {
this.brandName = brandName;
}
public String getCompanyName() {
return companyName;
}
public void setCompanyName(String companyName) {
this.companyName = companyName;
}
public String getOrdered() {
return ordered;
}
public void setOrdered(String ordered) {
this.ordered = ordered;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
public int getStatus() {
return status;
}
public void setStatus(int status) {
this.status = status;
}
public String toString() {
return "Brand{" +
"id=" + id +
", brandName='" + brandName + '\'' +
", companyName='" + companyName + '\'' +
", ordered='" + ordered + '\'' +
", description='" + description + '\'' +
", status='" + status + '\'' +
'}';
}
}
mapper文件
public interface BrandMapper {
/*注解开发*/
("select * from tb_brand")
List<Brand> selectAll(); //返回值采用列表类型 selectAll()对应sql语句的id
// User selectById(int id);
/*
* 条件查询:
* 参数接收:
*1:散装参数:如果方法中有多个参数,需要使用@Param("SQL参数占位符名称") 定义参数
*2:对象参数
*3:map集合参数
*/
//通过map集合查询数据
List<Brand> selectMap(Map map);
//通过类封装对象查询数据
List<Brand> selectByCondition(Brand brand);
/*添加操作*/
void add(Brand brand);
/*修改操作,全部字段*/
int update(Brand brand);
/*修改动态字段,就是部分字段*/
void update2(Brand brand);
/*删除一个*/
void delById(int id);
/*删除多个*/
void delByIds(int[] ids);
}
Mapper.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">
<!--namespace:命名空间 -->
<mapper namespace="com.example.MyBatis.mapper.BrandMapper">
<!--数据库表的字段名称 和 实体类不一样,会导致查询出来出现空值
resultMap方式解决:
1:定义<resultMap标签>
2:在<select>标签中,使用resultMap属性替换resultType属性
-->
<resultMap id="userResultMap" type="user">
<result column="password" property="pass"/>
</resultMap>
<resultMap id="brandResultMap" type="brand">
<result column="brand_Name" property="brandName"/>
<result column="company_Name" property="companyName"/>
</resultMap>
<!--resultType: 为返回值 -->
<!-- 参数占位符:
1:#{}:会将其替换为 ? 防止Sql注入
2:${}:拼sql,会存在sql注入问题
3:使用时机:
*参数传递的时候:#{}
*表名或者列明不固定的情况下,:${}会存在sql注入的问题
特殊字符:如(< >) !现在可以直接使用,不用转义了!!!
(以前的方法)1:使用转义字符 <: <
2:CDATA区
-->
<select id="select1" resultMap="brandResultMap">
select *
from tb_brand
where status = #{status}
and brand_name like #{brandName}
and company_name like #{companyName}
</select>
<select id="selectMap" resultMap="brandResultMap">
select *
from tb_brand
where status = #{status}
and brand_name like #{brandName}
and company_name like #{companyName}
</select>
<!--动态SQL查询 brandName才是查询条件,所以在if中使用brandName
*if条件判断:
*test逻辑表达式
*问题,当where后边连接and出现报错时,可以使用连接恒等式进行条件过滤
*恒等式 1=1
*<where> 替换where关键字 :自动生成where标签,并且自动去掉and
-->
<select id="selectCondition" resultMap="brandResultMap">
select *
from tb_brand
where 1=1
<where>
<if test="status !=null">
and status = #{status}
</if>
<if test="brandName !=null and brandName!=''">
and brand_name like #{brandName}
</if>
<if test="companyName !=null and companyName!=''">
and company_name like #{companyName}
</if>
</where>
</select>
<!--
单条件查询,就是当查询的语句只有一个查询条件时,但是同时几种可能切换
使用 choose .....when 相当于 switch....case
otherwise 语句用于当查询条件为空时 包含于choose,但是使用<where>效果更理想,直接过滤掉多余语句
-->
<select id="selectByCondition" resultMap="brandResultMap">
select *
from tb_brand
-- where 1=1
<where>
<choose>
<when test="status !=null and status!=''">
and status = #{status}
</when>
<when test="brandName !=null and brandName!=''">
and brand_name like #{brandName}
</when>
<when test="companyName !=null and companyName!=''">
and company_name like #{companyName}
</when>
</choose>
</where>
</select>
<!--添加操作
返回添加信息的主键:加属性useGeneratedKeys="true" keyProperty="id" id为想要返回的主键
-->
<insert id="add" useGeneratedKeys="true" keyProperty="id">
insert into tb_brand (brand_name, company_name, ordered, description, status)
values (#{brandName}, #{companyName}, #{ordered}, #{description}, #{status})
</insert>
<!--修改操作-->
<update id="update">
update tb_brand
set brand_name=#{brandName},
company_name=#{companyName},
ordered=#{ordered},
description=#{description},
status=#{status}
where id = #{id}
</update>
<!--修改部分字段-->
<update id="update2">
update tb_brand
<set>
<if test="brandName !=null and brandName!=''">
brand_name=#{brandName}
</if>
<if test="companyName !=null and companyName!=''">
company_name=#{companyName}
</if>
<if test="ordered !=null">
ordered=#{ordered}
</if>
<if test="description !=null and description!=''">
description=#{description}
</if>
<if test="status !=null">
status=#{status}
</if>
</set>
where id = #{id}
</update>
<delete id="delById">
delete
from tb_brand
where id = #{id}
</delete>
<!-- 删除多个 collection="array" :默认参数使用array表示列表 item="id"
separator=",":当接收多个参数时因为无法进行分割,使用参数进行分割 open="(" close="):表示语句的开始和结束 -->
<delete id="delByIds">
delete
from tb_brand
where id in
<foreach collection="array" item="id" separator="," open="(" close=")">
#{id}
</foreach>;
</delete>
</mapper>
测试类:
//通过封装类查询数据
public void SelectBrand() throws IOException {
//接收参数
int status = 1;
String brandName = "华为";
String companyName = "华为";
brandName = "%" + brandName + "%";
companyName = "%" + companyName + "%";
//实例化参数
Brand brand = new Brand();
brand.setStatus(status);
brand.setCompanyName(companyName);
brand.setBrandName(brandName);
Map map = new HashMap();
map.put("status", status);
map.put("brandName", brandName);
map.put("companyName", companyName);
//加载mybatis核心配置文件,获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//获取SqlSession对象,用来执行sql
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行sql
//获取UserMapper的接口代理
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<Brand> brands = mapper.selectByCondition(brand);
// List<Brand> brands =mapper.selectCondition(map);
System.out.println(brands);
//释放资源
sqlSession.close();
}
//添加
public void SelectByCondition() throws IOException {
//接收参数
int status = 1;
String brandName = "菠萝手机";
String companyName = "菠萝";
String description = "美国有苹果,中国有菠萝,菠萝手机";
String ordered = "100";
//实例化参数
Brand brand = new Brand();
brand.setStatus(status);
brand.setCompanyName(companyName);
brand.setBrandName(brandName);
brand.setDescription(description);
brand.setOrdered(ordered);
//加载mybatis核心配置文件,获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//获取SqlSession对象,用来执行sql
SqlSession sqlSession = sqlSessionFactory.openSession(true); //true为自动提交事务
//执行sql
//获取UserMapper的接口代理
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
mapper.add(brand);
Integer id = brand.getId();
System.out.println(id);
//提交事务
//sqlSession.commit();
//释放资源
sqlSession.close();
}
//修改
public void update() throws IOException {
//接收参数
int status = 2;
String brandName = "印度神油";
String companyName = "神油";
String description = "美国有金刚身,印度有神油";
String ordered = "130";
int id = 51;
//实例化参数
Brand brand = new Brand();
brand.setStatus(status);
brand.setCompanyName(companyName);
brand.setBrandName(brandName);
brand.setDescription(description);
brand.setOrdered(ordered);
brand.setId(id);
//加载mybatis核心配置文件,获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//获取SqlSession对象,用来执行sql
SqlSession sqlSession = sqlSessionFactory.openSession(true); //true为自动提交事务
//执行sql
//获取UserMapper的接口代理
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int count = mapper.update(brand);
System.out.println(count);
//提交事务
// sqlSession.commit();
//释放资源
sqlSession.close();
}
//修改2
public void update2() throws IOException {
//接收参数
String brandName = "印度神油";
int status = 2;
int id = 1;
//实例化参数
Brand brand = new Brand();
brand.setBrandName(brandName);
brand.setId(id);
brand.setStatus(status);
//加载mybatis核心配置文件,获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//获取SqlSession对象,用来执行sql
SqlSession sqlSession = sqlSessionFactory.openSession(true); //true为自动提交事务
//执行sql
//获取UserMapper的接口代理
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
mapper.update2(brand);
//提交事务
// sqlSession.commit();
//释放资源
sqlSession.close();
}
//删除一个
public void del() throws IOException {
//接收参数
int id = 22;
//加载mybatis核心配置文件,获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//获取SqlSession对象,用来执行sql
SqlSession sqlSession = sqlSessionFactory.openSession(true); //true为自动提交事务
//执行sql
//获取UserMapper的接口代理
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
mapper.delById(id);
//释放资源
sqlSession.close();
}
//删除多个
public void del2() throws IOException {
//接收参数
int[] id = {1, 3, 5, 7};
//加载mybatis核心配置文件,获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//获取SqlSession对象,用来执行sql
SqlSession sqlSession = sqlSessionFactory.openSession(true); //true为自动提交事务
//执行sql
//获取UserMapper的接口代理
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
mapper.delByIds(id);
//释放资源
sqlSession.close();
}