1.如何使用
1.1引入MyBatis与MySQL的jar包
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.4</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.9</version>
</dependency>
1.2配置MyBatis的配置文件
在resources下新建mybatis.xml在里面沾入以下代码
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTDConfig 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<!--数据源的配置:name的值固定 value的值要根据客
户自己修改-->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/mydb?serverTimezone=Asia/Shanghai" />
<property name="username" value="root"/>
<property name="password" value="sa"/>
</dataSource>
</environment>
</environments>
<!-- 配置文件-->
<mappers>
<mapper resource="mapper/studentMapper.xml"/>
<mapper resource="mapper/userMapper.xml"/>
<mapper resource="mapper/deptMapper.xml"/>
<mapper resource="mapper/empMapper.xml"/>
</mappers>
</configuration>
1.3创建实体类
引入lombok包
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student {
private int id;
private String name;
private int age;
private String address;
}
1.4 创建mybatis与数据库的映射文件
在resources下新建文件夹mapper,在mapper下新建studentMapper.xml,里面加入以下代码
注意:还需要把映射文件注册到配置文件mybatis.xml中(前面我已提前放入)
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--namespace:命名空间
可以随便起名,但是后期我们要求命名空间的值必须和所对应的
dao相同
-->
<mapper namespace="com.sfc.dao.StudentDao">
<!--查询 根据id查询用户信息
select标签用于查询的标签
id:标签的唯一标识
resultType: 定义返回的类型 把sql查询的结果封装
到哪个实体类钟
#{id}===表示占位符等价于? 这是mybatis框架的语法
-->
<!-- 查出信息-->
<select id="selectAll" resultType="com.sfc.entity.Student">
select * from student where 1=1
<if test="name!=null and name!=''">
and name = #{name}
</if>
<if test="address!=null">
and address = #{address}
</if>
</select>
<select id="selectAll1" resultType="com.sfc.entity.Student">
select * from student where 1=1
<choose>
<when test="name!=null and name!=''">
and name=#{name}
</when>
<when test="address!=null">
and money=#{money}
</when>
<otherwise>
and age=0
</otherwise>
<!-- <when test="address!=null">-->
<!-- and address = #{address}-->
<!-- </when>-->
<!-- <otherwise>-->
<!-- age = 11-->
<!-- </otherwise>-->
</choose>
</select>
<insert id="insert" parameterType="com.sfc.entity.Student" useGeneratedKeys="true" keyProperty="id">
insert into student values (null ,#{name} ,#{age} ,#{address});
</insert>
<update id="update" parameterType="com.sfc.entity.Student">
update student set name = #{name} ,age= #{age} ,address=#{address} where id=#{id}
</update>
<delete id="delete" parameterType="com.sfc.entity.Student">
delete from student where id=#{id}
</delete>
<!-- <!–添加–>-->
<!-- <insert id="insert" parameterType="com.sfc.entity.Student">-->
<!-- insert into student values(null ,#{name} ,#{age} ,#{address})-->
<!-- </insert>-->
</mapper>
1.5企业开发模式
通过dao和映射文件的关联完成
注意:studentMapper.xml里面的namespace必须与dao接口名字对应,而且标签的id必须和你定义的接口名字一样
package com.sfc.dao;
import com.sfc.entity.Student;
import java.util.List;
/**
* author : sfc
*
* @date : 2022-06-03 9:41
*/
public interface StudentDao {
public List<Student> selectAll(Student student);
public List<Student> selectAll1(Student student);
public int insert(Student student);
public int update(Student student);
public int delete(Student student);
}
1.6添加sql日志
添加log4j方法(快捷)
此处会出现这个文件
在文件中添加如下代码
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
1.7安装MyBatis插件
Mybatis Tools
作用检查dao和映射文件是否匹配
1.8传递多个参数
我们在dao接口某些方法中可能需要传递多个参数,如登录(password,username)
dao接口中
关键字(@Param() )
public interface UserDao {
public User selectOne(@Param("username") String username,@Param("password") String password);
public int update(@Param("id") int id, @Param("username") String username, @Param("password") String password);
}
映射中
<select id="selectOne" resultMap="userMap" >
select * from tb_user where tb_username=#{username} and tb_password=#{password}
</select>
1.9 添加时如何返回递增的id值
useGeneratedkeys="true":不隐藏
keyproperty="id" :显示id
<insert id="insert" parameterType="com.sfc.entity.Student" useGeneratedKeys="true" keyProperty="id">
insert into student values (null ,#{name} ,#{age} ,#{address});
</insert>
1.10解决表列名和实体类属性名不一致的问题
第一种方法
为查询起别的名字,而别的名和属性名一致
第二种:使用resultMap完成列和属性之间的映射关系
id:主键的映射关系
result:其他映射关系
column:表列名
property: 属性名
<resultMap id="resid" type="com.sfc.entity.Dept">
<id column="t_id" property="id"/>
<result column="t_name" property="name"/>
<result column="t_address" property="address"/>
</resultMap>
2动态SQL
if 条件判断 单条件判断
choose(when,otherwise) 条件选择相当于java when 多条件分支判断
where 、set 辅助 处理sql语句拼接问题
foreach 循环 循环
2.1 if标签
<select id="select2" resultMap="resid">
select
<include refid="sql"/>
from tb_dept where 1=1
<if test="name!=null and name!=''">
and name = #{name}
</if>
<if test="age!=null">
and age = #{age}
</if>
</select>
2.2 choose标签 多条件分支判断
otherwise:如果其他条件都不满足则执行此句话
<select id="selectAll1" resultType="com.sfc.entity.Student">
select * from student where 1=1
<choose>
<when test="name!=null and name!=''">
and name=#{name}
</when>
<when test="address!=null">
and money=#{money}
</when>
<otherwise>
and age=0
</otherwise>
</choose>
</select>
2.3 where 语句
<select id="select2" resultMap="resid">
select
<include refid="sql"/>
from tb_dept
<where>
<if test="name!=null and name!=''">
and name = #{name}
</if>
<if test="age!=null">
and age = #{age}
</if>
</where>
</select>
2.4set标签(配合if使用)
<update id="update">
update student
<set>
<if test="name!=null and name!=''">
name = #{name}
</if>
<if test="age!=null">
age = #{age}
</if>
<if test="address">
address = #{address}
</if>
</set>
</update>
2.5 foreach标签
collecion:类型,如果传过来使用的是数组就用array,
如果传过来使用的是集合就用list
item:传过来list或array的别名
open:以谁开始
close:以谁结束
separator:分隔符
<!--添加-->
<insert id="add">
insert into tb_dept(t_name,t_address) values
<foreach collection="list" item="dept" separator=",">
(#{dept.name},#{dept.address})
</foreach>
</insert>
2.6 SQL片段
在执行SQL时不建议使用select *
<sql id="sql">
t_id , t_name , t_address
</sql>
<select id="select2" resultMap="resid">
select
<include refid="sql"/>
from tb_dept
<where>
<if test="name!=null and name!=''">
and name = #{name}
</if>
<if test="age!=null">
and age = #{age}
</if>
</where>
</select>
3 连表查询
实体类:Emp(多)
private Dept dept:直接在多的一方定义这个类
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Emp {
private int eid;
private String ename;
private Integer eage;
private String ejob;
private Integer esalary;
private Date eentrydate;
private Integer emanagerid;
private Integer dept_id;
private Dept dept;
}
实体类 :Dept(少)
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Dept {
private Integer id;
private String name;
private String address;
}
xml中:
association:一的一方
<mapper namespace="com.sfc.dao.EmpDao">
<resultMap id="select" type="com.sfc.entity.Emp">
<id column="id" property="eid"/>
<result column="name" property="ename"/>
<result column="age" property="eage"/>
<result column="job" property="ejob"/>
<result column="salary" property="esalary"/>
<result column="entrydate" property="eentrydate"/>
<result column="managerid" property="emanagerid"/>
<result column="dept_id" property="dept_id"/>
<association property="dept" javaType="com.sfc.entity.Dept">
<id column="t_id" property="id"/>
<result column="t_name" property="name"/>
<result column="t_address" property="address"/>
</association>
</resultMap>
<select id="selectAll" resultMap="select">
select * from tb_dept d join tb_emp e on d.t_id = e.dept_id where name = #{ename}
</select>
</mapper>