实质:
在java程序中编写mysql语句
使用Mybatis查询所有用户数据
入门配置:
1、准备工作(创建springboot工程,引入相关依赖mybatis、mysq驱动、lombok)
2、引入Mybatis依赖,并配置
注意:还有lombok,这里少勾了lombok
(!!!这里注意数据库的名称改成自己的数据库名称:(如数据库:db01))
application.properties中引入数据库连接信息:
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/db01
spring.datasource.username=root
spring.datasource.password=1234
3、编写SQL语句(注解/XML)
数据库连接池:
(了解即可,会用接口就行)
- 是一个容器,负责分配、管理数据库连接(Connection)
- 优势:资源复用、提升系统响应速度
- 接口:DataSource
- 产品:C3P0、DBCP、Druid、Hikari
如何导入maven项目
导入好后就可以在此处找到项目:
记得将数据库名称,用户名,密码改为自己设置的:
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/db01
spring.datasource.username=root
spring.datasource.password=1234
#当设置为 true 时,MyBatis 会自动将数据库列名中的下划线转换为驼峰命名法。
# 例如,如果数据库中有一个列名为 first_name,MyBatis 会自动将其转换为 Java 对象中的 firstName。
mybatis.configuration.map-underscore-to-camel-case=true
mybatis基础操作:
删除:
//根据ID删除数据
@Delete("delete from emp where id = #{id}")
public void delete(Integer id);
在application.properties中引入:
# 配置mybatis日志,指定输出控制台
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
参数占位符,推荐用#{…},防止SQL注入(知道要用#{…}即可)
示例:
在Java中使用预编译SQL语句:
@Delete("delete from emp where id = #{id}")
void delete(Integer id);
而不是直接用SQL语句:
delete from emp where id = 16
预编译SQL语句
新增:
@Options(useGeneratedKeys = true, keyProperty = "id")//主键返回
@Insert("insert into emp(username, name, gender, image, job, entrydate, dept_id, create_time, update_time)" +
" values (#{username},#{name},#{gender},#{image},#{job},#{entrydate},#{deptId},#{createTime},#{updateTime})")
public void insert(Emp emp);
注意属性名只能用驼峰命名
更新:
@Update("update emp set username = #{username}, name = #{name}, gender = #{gender}, image = #{image}," +
" job = #{job}, entrydate = #{entrydate}, dept_id = #{deptId},update_time = #{updateTime} where id = #{id}")
public void update(Emp emp);
其他代码(感觉这部分先看看就行了,只有实操(学习案例,完成独立项目)才能牢记):
//方案三: 开启mybatis的驼峰命名自动映射开关 --- a_cloumn ------> aColumn
//根据ID查询员工
@Select("select * from emp where id = #{id}")
public Emp getById(Integer id);
//方案一: 给字段起别名, 让别名与实体类属性一致
//@Select("select id, username, password, name, gender, image, job, entrydate, " +
// "dept_id deptId, create_time createTime, update_time updateTime from emp where id = #{id}")
//public Emp getById(Integer id);
//方案二: 通过@Results, @Result注解手动映射封装
//@Results({
// @Result(column = "dept_id", property = "deptId"),
// @Result(column = "create_time", property = "createTime"),
// @Result(column = "update_time", property = "updateTime")
//})
//@Select("select * from emp where id = #{id}")
//public Emp getById(Integer id);
//条件查询员工
//方式一
//@Select("select * from emp where name like '%${name}%' and gender = #{gender} and " +
// "entrydate between #{begin} and #{end} order by update_time desc ")
//public List<Emp> list(String name, Short gender, LocalDate begin , LocalDate end);
//方式二
// @Select("select * from emp where name like concat('%',#{name},'%') and gender = #{gender} and " +
// "entrydate between #{begin} and #{end} order by update_time desc ")
// public List<Emp> list(String name, Short gender, LocalDate begin , LocalDate end);
XML映射文件(安装mybatisX插件便于XML代码的编写):
(比注解重要,企业开发都是用这个,前面都是注解使用SQL语句)
需要在EmpMapper.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.itheima.mapper.EmpMapper">
<!-- 注意路径改为自己项目的路径-->
动态SQL语句:
if、where标签:
foreach标签:
sql、include标签:
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.itheima.mapper.EmpMapper">
<sql id="commonSelect">
select id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time
from emp
</sql>
<!-- 动态更新员工-->
<update id="update2">
update emp
<set>
<if test="username != null">username = #{username},</if>
<if test="name != null">name = #{name},</if>
<if test="gender != null">gender = #{gender},</if>
<if test="image != null">image = #{image},</if>
<if test="job != null">job = #{job},</if>
<if test="entrydate != null">entrydate = #{entrydate},</if>
<if test="deptId != null">dept_id = #{deptId},</if>
<if test="updateTime != null">update_time = #{updateTime}</if>
</set>
where id = #{id}
</update>
<!--resultType: 单条记录封装的类型-->
<select id="list" resultType="com.itheima.pojo.Emp">
<include refid="commonSelect"/>
<where>
<if test="name != null">
name like concat('%', #{name}, '%')
</if>
<if test="gender != null">
and gender = #{gender}
</if>
<if test="begin != null and end != null">
and entrydate between #{begin} and #{end}
</if>
</where>
order by update_time desc
</select>
<!--批量删除员工 (18,19,20)-->
<!--
collection: 遍历的集合
item: 遍历出来的元素
separator: 分隔符
open: 遍历开始前拼接的SQL片段
close: 遍历结束后拼接的SQL片段
-->
<delete id="deleteByIds">
delete from emp where id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>
</mapper>
//动态条件查询
public List<Emp> list(String name, Short gender, LocalDate begin , LocalDate end);
//动态更新员工
public void update2(Emp emp);
//批量删除员工
public void deleteByIds(List<Integer> ids);