什么框架基本都有一个配置文件,这里是maven的pom.xml文件,在里面配置这些依赖
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>5.3.9</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.3.9</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.5</version>
</dependency>
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.16</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
在maven项目下resources文件夹新建一个配置文件(mybatisConfig.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>
<!-- 使用jdbc的getGeneratedKeys获取数据库自增主键值 -->
<setting name="useGeneratedKeys" value="true" />
<!-- 使用列标签替换列别名 默认:true -->
<setting name="useColumnLabel" value="true" />
<!-- 开启驼峰命名转换:Table{create_time} -> Entity{createTime} -->
<setting name="mapUnderscoreToCamelCase" value="true" />
</settings>
</configuration>
在java下项目路径创建一个映射配置文件(mapper.xml),用来关联实体类和数据库中的表和字段,同样先配置模板
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="">
<insert id="" parameterType="">
</insert>
</mapper>
配置好两个文件后,在配置好一个实体类,就是你数据库表的一个实体类,这个时候再去配置好映射,现在继续配置mapper.xml文件,把sql语句放这里面,然后加上作用域和返回值类型
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.entor.entity.User">
<select id="queryById" parameterType="int" resultType="com.entor.entity.User">
select id,name,sex,phone,email,entry_date,create_time,password from emp where id = #{id}
</select>
</mapper>
然后再主配置文件里面(mybatisConfig.xml)的数据库连接池和加载映射文件,注意type写大写的POOLED
<?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>
<!-- 设置数据库字段下划线跟类对象驼峰命名转换,数据库命名是下划线分开,java是驼峰命名-->
<!-- <setting name="mapUnderscoreToCamelCase" value="true"/>-->
<!-- 控制台输出sql日志-->
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<typeAliases>
<typeAlias type="com.entor.entity.User" alias="User"></typeAlias>
<!-- 指定包下所有类以简写类名做别名-->
<!-- <package name="com.entor.entity"/>-->
</typeAliases>
<!-- 配置数据库默认环境,支持多种环境 -->
<environments default="mysql">
<environment id="mysql">
<!-- jdbc事务管理 -->
<transactionManager type="JDBC"></transactionManager>
<!-- 数据源使用池化技术 -->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
<property name="url" value="jdbc:mysql://localhost:3306/user_test?useUnicode=true&useSSL=false&characterEncoding=utf-8&serverTimezone=Asia/Shanghai"/>
</dataSource>
</environment>
</environments>
<!--给类取别名-->
<!--<mappers>-->
<!--<!– <mapper resource="mapper/mapper.xml"></mapper>–>-->
<!-- <mapper resource="mapper/mapper2.xml"></mapper>-->
<!--</mappers>-->
<mappers>
<mapper class="com.entor.mappers.UserMapper"></mapper>
</mappers>
</configuration>
<!-- <!– 配置全局属性 –>-->
<!-- <settings>-->
<!-- <!– 使用jdbc的getGeneratedKeys获取数据库自增主键值 –>-->
<!-- <setting name="useGeneratedKeys" value="true" />-->
<!-- <!– 使用列标签替换列别名 默认:true –>-->
<!-- <setting name="useColumnLabel" value="true" />-->
<!-- <!– 开启驼峰命名转换:Table{create_time} -> Entity{createTime} –>-->
<!-- <setting name="mapUnderscoreToCamelCase" value="true" />-->
<!-- </settings>-->
mapper.xml最后的编写
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="User">
<sql id="common">
id
,name,sex,phone,email,entry_date,create_time,password
</sql>
<sql id="common2">
name,sex,phone,email,entry_date,create_time,password
</sql>
<!--接收一个参数,用任意名字占位符就行 parameterType可写可不写-->
<select id="queryById" parameterType="int" resultType="User">
select<include refid="common"/>from emp where id = #{id}
</select>
<!-- 如果要sql接收多个参数,可以把多个参数封装到一个对象中,#{startPage}意思是通过调用map中对应键值startPage的值-->
<select id="queryByPage" parameterType="map" resultType="com.entor.entity.User">
select<include refid="common"/>from emp
order by id limit #{startPage}, #{pageSize}
</select>
<!--returnType必须要有,用来封装查询到的字段,通过反射把查询到的字段值注入调用对象的set方法中-->
<!-- 如果要sql接收多个参数,可以把多个参数封装到一个对象中,#{name}意思是通过调用对象中getName方法渠道name的值-->
<!-- 使用自动主键增长,然后选择自动增长的列,然后通过id这个属性的set把id设置进user这个类中-->
<insert id="add" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
insert into emp(<include refid="common2"/>)
values(#{name},#{sex},#{phone},#{email},#{entry_date},#{create_time},#{password})
</insert>
<insert id="addMore" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
insert into emp(<include refid="common2"/>)values
<foreach collection="list" separator="," item="user">
(#{user.name},
#{user.sex},
#{user.phone},
#{user.email},
#{user.entry_date},
#{user.create_time},
#{user.password})
</foreach>
</insert>
<delete id="deleteById" >
delete
from emp
where id = #{id}
</delete>
<!--#是占位符,会根据参数类型自动判断是否增加单引号,可以防止sql注入,后者是字符串拼接,不加单引号-->
<delete id="deleteByIds" >
delete
from emp
where id in
<foreach collection="array" open="(" close=")" separator="," item="id">
#{id}
</foreach>
</delete>
<select id="getCount" resultType="int">
select count(id)
from emp
</select>
<update id="update" >
update emp
set name=#{name},
sex=#{sex},
phone=#{phone},
email=#{email},
entry_date=#{entry_date},
create_time=#{create_time},
password=#{password}
where id = #{id}
</update>
</mapper>
报错什么的自己搞定吧,然后配置好一个工具类,可以使用映射来获取数据库中的值了,这下我们不要get和set了
package com.entor.dao.impl;
import com.entor.dao.UserDao;
import com.entor.entity.User;
import com.entor.util.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import java.util.HashMap;
import java.util.List;
public class UserDaoImpl2 implements UserDao {
@Override
public int add(User user) {
SqlSession session = MybatisUtils.getSession();
session.insert("User.add", user);
System.out.println(user.getId());
session.commit();
session.close();
return user.getId();
}
@Override
public int addMore(List<User> list) {
SqlSession session = MybatisUtils.getSession();
session.insert("User.addMore", list);
session.commit();
session.close();
return 1;
}
@Override
public int update(User user) {
SqlSession session = MybatisUtils.getSession();
int infulence = session.update("User.update", user);
session.commit();
session.close();
return infulence;
}
@Override
public int deleteById(int id) {
SqlSession session = MybatisUtils.getSession();
int infulence = session.delete("User.deleteById", id);
session.commit();
session.close();
return infulence;
}
@Override
public int deleteByIds(String ids) {
SqlSession session = MybatisUtils.getSession();
int infulence = session.delete("User.deleteByIds",ids.split(","));
session.commit();
session.close();
return infulence;
}
@Override
public User queryById(int id) {
SqlSession session = MybatisUtils.getSession();
User user = session.selectOne("User.queryById", id);
session.close();
return user;
}
@Override
public List<User> queryByPage(int pageNo, int pageSize) {
SqlSession session = MybatisUtils.getSession();
if (pageNo<0){
pageNo=1;
}
int startPage = (pageNo-1)*pageSize;
HashMap<String,Integer> hm = new HashMap<>();
hm.put("startPage",startPage);
hm.put("pageSize",pageSize);
List<User> list = session.selectList("User.queryByPage", hm);
session.close();
return list;
}
@Override
public User login(String name, String password) {
return null;
}
@Override
public int getCount() {
SqlSession session = MybatisUtils.getSession();
int count = session.selectOne("User.getCount");
session.commit();
session.close();
return count;
}
}
最后进行调试代码,替换业务层的仓管就行了,然后通过业务层调用
动态SQL,再次修改mapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="User">
<sql id="common">
id,name,sex,phone,email,entry_date,create_time,password
</sql>
<insert id="add">
insert into emp (
<if test="name!=null and name !=''">
name,
</if>
<if test="sex!=null and sex !=''">
sex,
</if>
<if test="phone!=null and phone !=''">
phone,
</if>
<if test="email!=null and email !=''">
email,
</if>
<if test="entry_date!=null ">
entry_date,
</if>
<if test="create_time!=null">
create_time,
</if>
<if test="password!=null and password !=''">
password
</if>
)
values (
<if test="name!=null and name !=''">
#{name},
</if>
<if test="sex!=null and sex !=''">
#{sex},
</if>
<if test="phone!=null and phone !=''">
#{phone},
</if>
<if test="email!=null and email !=''">
#{email},
</if>
<if test="entry_date!=null">
#{entry_date},
</if>
<if test="create_time!=null ">
#{create_time},
</if>
<if test="password!=null and password !=''">
#{password}
</if>
)
</insert>
<update id="update">
update emp
<set>
<if test="name!=null and name !=''">
name=#{name},
</if>
<if test="sex!=null and sex !=''">
sex=#{sex},
</if>
<if test="phone!=null and phone !=''">
phone=#{phone},
</if>
<if test="email!=null and email !=''">
email=#{email},
</if>
<if test="entry_date!=null">
entry_date=#{entry_date},
</if>
<if test="create_time!=null ">
create_time=#{create_time},
</if>
<if test="password!=null and password !=''">
password=#{password}
</if>
</set>
where id = #{id}
</update>
<!--使用${}拼接参数有sql注入的风险-->
<!--mysql自带拼接参数concat,可以防止sql注入(推荐使用)-->
<!-- 在调用的时候再参数两侧加%号 -->
<select id="queryByParam" resultType="User">
select <include refid="common"/> from emp
<where>
<if test="name!=null and name!=''">
name like concat('%' ,#{name},'%')
</if>
<if test="sex!=null">
and sex = #{sex}
</if>
<if test="phone!=null and phone!=''">
and phone like concat('%' ,#{phone},'%')
</if>
</where>
</select>
</mapper>
一些说明已经在代码上面注释了
最后还是通过配置类来代替配置文件
首先新建 一个接口,在接口里面定义接口方法,最后将这个类的路径配置到主mybatis配置文件当中
package com.entor.mappers;
import com.entor.entity.User;
import org.apache.ibatis.annotations.*;
import java.util.List;
public interface UserMapper {
/**
*@describe 插入单条记录,返回新增的主键
**/
@Insert("insert into emp(id,name,sex,phone,email,entry_date,create_time,password)" +
"values(#{name},#{sex},#{phone},#{email},#{entry_date},#{create_time},#{password})")
public abstract int add(User user);
/**
*@describe 向数据库中插入多条记录
* @param
* @return
**/
public abstract int addMore(List<User> list);
/**
*@describe 更新数据库中的记录
**/
@Update("update empset name=#{name},sex=#{sex},phone=#{phone},email=#{email},entry_date=#{entry_date},create_time=#{create_time},password=#{password}" +
"where id = #{id}")
public abstract int update(User user);
/**
*@describe 根据id删除单条记录
**/
@Delete("delete from emp where id = #{id}")
public abstract int deleteById(int id);
/**
*@describe 根据ids删除多条记录
**/
@Delete("delete from emp where id in (${ids})")
public abstract int deleteByIds(String ids);
/**
*@describe 根据id查询用户
**/
@Select("select id,name,sex,phone,email,entry_date,create_time,password from emp where id = #{id}")
public abstract User queryById(int id);
/**
*@describe 分页查询
**/
@Select("select id,name,sex,phone,email,entry_date,create_time,password from emp limit #{pageNum},#{pageSize}")
public abstract List<User> queryByPage(@Param("pageNum") int pageNo, @Param("pageSize") int pageSize);
/**
*@describe 方法上有多个参数,为了区分不同参数需要在参数前面加上@Param的注解来指定参数名称,单个参数不需要
**/
/**
*@describe 用户登录
*
* @return User
**/
public abstract User login(String name,String password);
/**
*@describe 查询数据库中的总记录数
**/
public abstract int getCount();
}
<mappers>
<mapper class="com.entor.mappers.UserMapper"></mapper>
</mappers>
之后进行测试加入工具类
package com.entor.util;
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;
public class MybatisUtils {
private static SqlSessionFactory sqlSessionFactory;
static {
Reader rs = null;
try {
rs = Resources.getResourceAsReader("mybatisConfig.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(rs);
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
rs = null;
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
public static SqlSession getSession() {
return sqlSessionFactory.openSession();
}
public static void close(SqlSession session) {
if (session != null) {
session.close();
session = null;
}
}
}
SqlSession session = MybatisUtils.getSession();
UserMapper mapper = session.getMapper(UserMapper.class);
User user = mapper.queryById(124);
System.out.println(user);
关于缓存和开启二级缓存
mybatis配置加入
<setting name="cacheEnabled" value="true"/>
实体类开启序列化
public class User implements Serializable {
private static final long serialVersionUID = 3295737292117499605L;
private int id;
映射类开启注解
@CacheNamespace
public interface UserMapper {
开启测试
package com.entor.test;
import com.entor.entity.User;
import com.entor.mappers.UserMapper;
import com.entor.util.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
public class Test6 {
public static void main(String[] args) {
/**
*@describe mybatis缓存机制,
* 一级缓存
* 同一个session内部共享,每次查询数据时先从一级缓存中拿,如果有则直接返回,否则查询数据库
* 并将缓存存入一级缓存,每次执行更新操作(删除,修改,更新),自动清除一级缓存内容
* 二级缓存,开启二级缓存后
* 不同session之间共享,sessionFactory级别,数据库实例级别,默认不开启
* 查询数据后存入一级缓存和二级缓存,同一个session对象先从一级缓存查,如果没有再从二级缓存,再没有从数据库查询
* 最后返回
* 有更新操作清除一级缓存和二级缓存内容
**/
SqlSession session = MybatisUtils.getSession();
/**
*@describe 同一个session对象查询相同的sql语句只执行一次,结果会被复用(从缓存当中拿结果)
**/
UserMapper mapper = session.getMapper(UserMapper.class);
User user = mapper.queryById(128);
System.out.println(user);
}
}
mybatis多表查询
分页工具,pom.xml增加依赖
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.2.0</version>
</dependency>
</dependencies>
代码
SqlSession session = MybatisUtils.getSession();
UserMapper mapper = session.getMapper(UserMapper.class);
// 必须对紧跟的查询语句才能实现分页,中间不能有其他代码
PageHelper.startPage(1, 10);
List<User> list1 = mapper.queryAll();
多表查询代码,通过返回映射和标签拿到数据库对应的值返回
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.entor.mappers.StuMapper">
<resultMap id="studentMap" type="com.entor.entity.Student">
<id property="id" column="id"></id>
<result property="name" column="name"></result>
<result property="phone" column="phone"></result>
<result property="classID" column="class_id"></result>
<association property="classes" javaType="com.entor.entity.Classes">
<id property="id" column="class_id"></id>
<result property="name" column="className"></result>
</association>
</resultMap>
<select id="queryByClassesName" parameterType="String" resultMap="studentMap">
select s.*, c.name className
from student s,
classes c
where s.class_id = c.id
and c.name like concat('%', #{name},'%')
</select>
</mapper>
多表查询
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.entor.mappers.StuMapper">
<resultMap id="studentMap" type="com.entor.entity.Student">
<id property="id" column="id"></id>
<result property="name" column="name"></result>
<result property="phone" column="phone"></result>
<result property="classId" column="class_id"></result>
<association property="classes" javaType="com.entor.entity.Classes">
<!-- <id property="id" column="class_id"></id>-->
<result property="name" column="className"></result>
</association>
</resultMap>
<resultMap id="studentMap2" type="com.entor.entity.Student">
<result property="name" column="name"></result>
<result property="phone" column="phone"></result>
<association property="classes" javaType="com.entor.entity.Classes">
<!-- <id property="id" column="class_id"></id>-->
<result property="name" column="className"></result>
</association>
<collection property="scores" ofType="com.entor.entity.Score">
<id property="id" column="scoreId"></id>
<result property="score" column="score"></result>
<association property="course" javaType="com.entor.entity.Course">
<id property="id" column="courseId"></id>
<result property="name" column="courseName"></result>
</association>
</collection>
</resultMap>
<select id="queryByClassesName" resultMap="studentMap">
select s.*, c.name className
from student s,
classes c
where s.class_id = c.id
and c.name like concat('%', #{name},'%')
</select>
<select id="queryAll" resultMap="studentMap">
select s.* ,c.name ClassName from student s ,classes c left join c on c.id =
</select>
<select id="queryAlls" resultMap="studentMap">
select s.* ,c.name ClassName from student s ,classes c left join c on c.id =
</select>
</mapper>
mybatisplus,依赖什么的不说了,直接看官方文档