1、helloMybatis
1.1执行流程
1.2具体操作
- 新建maven工程,配置pom.xml,导入依赖,资源过滤,中文注解
- Mybatis包
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.2</version>
</dependency>
- Junit包(测试)
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
- MYSQL驱动
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
- 防止Maven过滤掉配置文件
<build>
<resources>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>true</filtering>
</resource>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>true</filtering>
</resource>
</resources>
</build>
- 在xml可中文注解
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
- 编写配置文件mybatis-config.xml
(数据库名称为mybatis)
<?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核心配置文件-->
<configuration>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf-8&useSSL=false"/><!--amp;为转义字符-->
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<!--每一个mapper.xml都需要mybatis核心配置文件中注册 -->
<mappers>
<!--<mapper resource="com/palen/dao/UserMapper.xml"></mapper>-->
</mappers>
</configuration>
- 编写mybatis使用封装到MybatisUtils
封装之后,可以直接是有getSqlSession()方法获取sqlSession,不用每次用的时候都输入下面代码。
public class MybatisUtils {
private static SqlSessionFactory sqlSessionFactory;
static {
try {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}//static
public static SqlSession getSqlSession(){
return sqlSessionFactory.openSession();
}
}
-
编写代码
- 实体类:User
public class User { private int id; private String name; private String pwd; }
- 接口类:UserDao
package com.palen.dao; import com.palen.pojo.User; import java.util.List; public interface UserDao { List<User> getUserList(); }
- 实现类: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"> <!--namespace绑定一个对应的dao/mapper--> <mapper namespace="com.palen.dao.UserDao"> <!--查询语句--> <select id="getUserList" resultType="com.palen.pojo.User"> select * from mybatis.user; </select> </mapper>
-
在mybatis-config.xml绑定mapper(重要)
<mappers>
<mapper resource="com/palen/dao/UserMapper.xml"></mapper>
</mappers>
- 测试
public class UserDaoTest {
@Test
public void test() {
//获取sqlSession
SqlSession sqlSession = MybatisUtils.getSqlSession();
//执行SQL
UserDao userDao = sqlSession.getMapper(UserDao.class);
List<User> userList = userDao.getUserList();
for (User user:userList){
System.out.println(user);
}
//关闭sqlSession
sqlSession.close();
}
}
1.3 小结
- Resources包里mybatis-config.xml(用于链接数据库以及mapper注册)
- Utils包里放MybatisUtils.java(工具包获取sqlSessionFactory)
- 在mapper包里UserMapper接口(原来是UserDao)(记得在mybatis-config.xml中注册)
- 在mapper包里UserMapper.xml实现类(原本是UserDaoImpl.java)(UserMapper.xml中的namespace就是用来指定接口包的)
- 在测试类里调用mapper就可以了。
2、注册和配置
2.1 注册mapper方法
- 方法一:resources(接口和mapper同名且同包下)
<mappers>
<mapper resource="com/palen/mapper/UserMapper.xml"></mapper>
- 方法二:class
<mappers>
<mapper class="com.palen.mapper.UserMapper.xml"/>
</mappers>
2.2 数据库链接配置
核心配置mybatis-config.xml可以把数据库链接内容分离出来
1.编写properties文件,如application.properties或者db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?useSSL=false&useUnicode=true&characterEncoding=utf8
username=root
password=123456
注意:这里useSSL=false!!!useSSL=true是进行安全验证,一般通过证书或者令牌等,useSSL=false就是通过账号密码进行连接,通常使用useSSL=false!!!
2.mybatis-config.xml中配置
<configuration>
<!--导入properties文件-->
<properties resource="db.properties"/>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mapper/UserMapper.xml"/>
</mappers>
</configuration>
3、基本操作
3.1 CRUD
1.在接口UserMapper里面写函数
//查询全部用户
List<User> getUserList();
//根据ID查询用户
User getUserById(int id);
//增加一个user
int addUser(User user);
//修改一个userById
int updateUser(User user);
//删除一个user
int deleteUserById(int id);
2.实现UserMapper.xml里写实现
<mapper namespace="com.palen.mapper.UserMapper">
<!--查询语句-->
<select id="getUserList" resultType="com.palen.pojo.User">
select * from mybatis.user;
</select>
<select id="getUserById" resultType="com.palen.pojo.User" parameterType="int">
select * from mybatis.user where id = #{id};
</select>
<!-- #号里面可以直接拿到User的属性-->
<insert id="addUser" parameterType="com.palen.pojo.User">
insert into mybatis.user(id,name,pwd) values(#{id},#{name},#{pwd});
</insert>
<update id="updateUser" parameterType="com.palen.pojo.User">
update mybatis.user set name=#{name},pwd=#{pwd} where id=#{id};
</update>
<delete id="deleteUserById" parameterType="int">
delete from mybatis.user where id=#{id};
</delete>
</mapper>
3.在业务层,暂时在test类里写
注意增删改需要提交事务!!!
@Test
public void mapperTest() {
//获取sqlSession
SqlSession sqlSession = MybatisUtils.getSqlSession();
//获取mapper
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//使用mapper里的函数
User user = userMapper.getUserById(4);
System.out.println(user);
int succeed = userMapper.updateUser(new User(7, "hahaha", "123456"));
System.out.println(succeed);
int i = userMapper.deleteUserById(7);
System.out.println(i);
List<User> userList = userMapper.getUserList();
for(User user:userList){
System.out.println(user);
}
//增删改需要提交事务!!
sqlSession.commit();
//关闭sqlSession
sqlSession.close();
}
3.2模糊查询like
(此方法容易导致sql注入)
1.UserMapper接口
//模糊查询
List<User> getUserLike(String value);
2.UserMapper.xml
<select id="getUserLike" parameterType="String" resultType="com.palen.pojo.User">
select * from mybatis.user where name like "%"#{value}"%";
</select>
3.test
List<User> userList = mapper.getUserLike("小");
for(User user:userList){
System.out.println(user);
}
3.3 分页limit
#语法
SELECT * FROM table LIMIT stratIndex,pageSize
SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15
#为了检索从某一个偏移量到记录集的结束所有的记录行,可以指定第二个参数为 -1:
SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.
#如果只给定一个参数,它表示返回最大的记录行数目:
SELECT * FROM table LIMIT 5; //检索前 5 个记录行
#换句话说,LIMIT n 等价于 LIMIT 0,n。
1.UserMapper接口
//选择全部用户实现分页
List<User> selectUser(Map<String,Integer> map);
2.UserMapper.xml
<select id="selectUser" parameterType="map" resultType="user">
select * from user limit #{startIndex},#{pageSize}
</select>
3.test
//分页查询 , 两个参数startIndex , pageSize
@Test
public void testSelectUser() {
SqlSession session = MybatisUtils.getSession();
UserMapper mapper = session.getMapper(UserMapper.class);
int currentPage = 1; //第几页
int pageSize = 2; //每页显示几个
Map<String,Integer> map = new HashMap<String,Integer>();
map.put("startIndex",(currentPage-1)*pageSize);
map.put("pageSize",pageSize);
List<User> users = mapper.selectUser(map);
for (User user: users){
System.out.println(user);
}
session.close();
}
4、其他操作
4.1 起别名(常用)
方法一:在mybatis-config.xml中配置(在properties下面)
<!--起别名-->
<typeAliases>
<!--单独一个类起别名-->
<typeAlias type="com.palen.pojo.User" alias="User"/>
<!--包下所有类起别名-->
<package name="com.palen.pojo"/>
</typeAliases>
取别名之后,使用类的时候不需要全名,只需要写其类名即可(小写)
方法二:注解起别名
@Alias("user_anotherName")
public class User {
private int id;
private String name;
private String pwd;
}
4.2 map使用
参数过多时候,map可以随意做传个参数,可定制化。
1.UserMapper接口
getUserById(Map<String,Object> map);
2.UserMapper.xml
<select id="getUserById" resultType="com.palen.pojo.User" parameterType="map">
select * from mybatis.user where id = #{user_id}; and name=#{user_name}
</select>
3.test
Map<String,Object> map = new HashMap<String,Object>();
map.put(“user_id”,5);
map.put(“user_name”,”名字”);
Mapper.getUserById(map);
这样就不用一定要把User的所有属性初始化。
4.3 log日记
方法一:mybatis内置日志工厂
1.mybatis-config.xml配置
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
2.test之后结果
方法二:Log4j
1.pom.xml导入包
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
2.log.properties配置
#将等级为DEBUG的日志信息输出到console和file这两个目的地,console和file的定义在下面的代码
log4j.rootLogger=DEBUG,console,file
#控制台输出的相关设置
log4j.appender.console = org.apache.log4j.ConsoleAppender
log4j.appender.console.Target = System.out
log4j.appender.console.Threshold=DEBUG
log4j.appender.console.layout = org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%c]-%m%n
#文件输出的相关设置
log4j.appender.file = org.apache.log4j.RollingFileAppender
log4j.appender.file.File=./log/palen.log
log4j.appender.file.MaxFileSize=10mb
log4j.appender.file.Threshold=DEBUG
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}][%c]%m%n
#日志输出级别
log4j.logger.org.mybatis=DEBUG
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG
3.mybatis-config.xml配置
<settings>
<setting name="logImpl" value="LOG4J"/>
</settings>
4.MyTest使用
//注意导包:org.apache.log4j.Logger
static Logger logger = Logger.getLogger(MyTest.class);
@Test
public void selectUser() {
logger.info("info:进入selectUser方法");
logger.debug("debug:进入selectUser方法");
logger.error("error: 进入selectUser方法");
SqlSession session = MybatisUtils.getSession();
UserMapper mapper = session.getMapper(UserMapper.class);
List<User> users = mapper.selectUser();
for (User user: users){
System.out.println(user);
}
session.close();
}
可以看到还生成了一个日志文件。
4.4 注解
1.UserMapper接口
//用注解来写
@Select("SELECT * FROM mybatis.user")
List<User> getUserList2();
若属性和字段不一致,可搭配@Param()使用
2.mybatis-config.xml绑定
<mapper class="com.palen.mapper.UserMapper"/>
注意:如果既有class又有resources,一定得class在前面,否则注解将无效。
5、字段和属性对应
5.1 字段属性不一致resultMap
数据库的字段和实体中的属性不一致,需要映射,使用resultMap
例如:数据库中密码是pwd,实体类User密码是password
<resultMap id="UserMap" type="User">
<!-- column是数据库的,property是实体类的-->
<result column="id" property="id"/>
<result column="name" property="name"/>
<result column="pwd" property="password"/>
</resultMap>
<select id="getUserList" resultMap="UserMap">
select * from mybatis.user;
</select>
5.2 association 多对一/一对一(重点)
1.数据库中,两个对象,student和teacher,一个老师对应多个学生
2.实体类
- teacher
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
private int id;
private String name;
}
- student
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
private int id;
private String name;
//学生关联一个老师
private Teacher teacher;
}
3.Mapper接口
//查询所有学生的信息
List<Student> getStudents();
4.Mapper.xml
- 方法一:使用association中,select属性,再次查询该学生的老师属性
<select id="getStudents" resultMap="studentTeacher">
select * from students
</select>
<resultMap id="studentTeacher" type="student">
<result property="id" column="id"/>
<result property="name" column="name"/>
<association property="teacher" column="t_id" javaType="Teacher"
select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="Teacher">
select * from teacher where id = #{id};
</select>
- 方法二:查询的时候,连同学生的老师信息也查出来,再使用association的result属性来实现映射。
<select id="getStudents" resultMap="studentTeacher">
select s.id,s.name,t.name t_name
from teacher t,students s
where s.t_id = t.id
</select>
<resultMap id="studentTeacher" type="student">
<result property="id" column="id"/>
<result property="name" column="name"/>
<association property="teacher" javaType="Teacher">
<result property="name" column="t_name"/>
</association>
</resultMap>
5.3 一对多(重点)
1.数据库,两个对象
2.实体类(区别于多对一,在于pojo里的实体类属性不一样)
- teacher
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
private int id;
private String name;
//一个老师多个学生
private List<Student> studentList;
}
- student
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
private int id;
private String name;
}
3.Mapper接口
//查询所有学生的信息
Teacher getTeacherById(int id);
4.Mapper.xml,使用collection
<select id="getTeacherById" resultMap="teacherStudents" parameterType="int">
select s.id s_id,s.name s_name,t.name t_name,t_id
from students s,teacher t
where s.t_id=t.id and t.id=#{id}
</select>
<resultMap id="teacherStudents" type="Teacher">
<result property="id" column="t_id"/>
<result property="name" column="t_name"/>
<collection property="studentList" ofType="Student">
<result property="id" column="s_id"/>
<result property="name" column="s_name"/>
</collection>
</resultMap>
5.3 区别
-
一对多:
- 实体类属性是引用类型,使用association的javaType映射
-
多对一:
- 实体类属性是一个引用类型的集合,用collection的ofType映射
6、sql动态语句
6.1 if 语句
1.在BlogMapper.java接口中用map来传参数
//查询博客
List<Blog> getBlogListIF(Map map);
2.在BlogMapper.xml中使用if
- 实现如果有author或者title查询,就查询其对应结果集,否则全部查询。
<select id="getBlogListIF" parameterType="map" resultType="blog">
select * from blog where 1=1
<if test="title!= null">
and title = #{title}
</if>
<if test="author!= null">
and author = #{author}
</if>
</select>
- where标签
这个“where”标签会知道如果它包含的标签中有返回值的话,它就插入一个‘where’。此外,如果标签返回的内容是以AND 或OR 开头的,则它会剔除掉。
<select id="getBlogListIF" parameterType="map" resultType="blog">
select * from blog
<where>
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</where>
</select>
- set标签(扩展)
在更新的时候,如果使用set标签会,会自动增加除去不必要的逗号
<!--注意set是用的逗号隔开-->
<update id="updateBlog" parameterType="map">
update blog
<set>
<if test="title != null">
title = #{title},
</if>
<if test="author != null">
author = #{author}
</if>
</set>
where id = #{id};
</update>
3.在test中使用
@Test
public void queryIfTest(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap hashMap = new HashMap();
hashMap.put("title","hello my dear!");
List<Blog> blogList = mapper.getBlogListIF(hashMap);
for (Blog blog : blogList) {
System.out.println(blog);
}
sqlSession.close();
}
6.2 Choose语句
1.在BlogMapper.java接口中用map来传参数
//查询博客
List<Blog> getBlogListChoose(Map map);
2.在BlogMapper.xml中使用choose
- 实现如果有title约束就查出其结果集,若没有在查看author约束,如果都没有执行otherwise
<select id="getBlogListChoose" resultType="blog" parameterType="map">
select * from blog
<where>
<choose>
<when test="title!=null">
title = #{title}
</when>
<when test="author!= null">
and author = #{author}
</when>
<otherwise>
and views = #{views}
</otherwise>
</choose>
</where>
</select>
3.在test中使用
@Test
public void queryChooseTest(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap hashMap = new HashMap();
//hashMap.put("title","hello my dear!");
hashMap.put("views","999");
hashMap.put("author","ll");
List<Blog> blogList = mapper.getBlogListChoose(hashMap);
for (Blog blog : blogList) {
System.out.println(blog);
}
sqlSession.close();
}
其结果:
6.3 Foreach语句
1.在BlogMapper.java接口中用map来传参数
List<Blog> queryBlogForeach(Map map);
2.在BlogMapper.xml中使用foreach
<select id="queryBlogForeach" parameterType="map" resultType="blog">
select * from blog
<where>
<!--
collection:指定输入对象中的集合属性
item:每次遍历生成的对象
open:开始遍历时的拼接字符串
close:结束时拼接的字符串
separator:遍历对象之间需要拼接的字符串
select * from blog where 1=1 and (id=1 or id=2 or id=3)
-->
<foreach collection="ids" item="id" open="and (" close=")" separator="or">
id=#{id}
</foreach>
</where>
</select>
3.在test中使用
@Test
public void testQueryBlogForeach(){
SqlSession session = MybatisUtils.getSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
HashMap map = new HashMap();
List<Integer> ids = new ArrayList<Integer>();
ids.add(1);
ids.add(2);
ids.add(3);
map.put("ids",ids);
List<Blog> blogs = mapper.queryBlogForeach(map);
System.out.println(blogs);
session.close();
}
6.4 sql片段
1.编写sql片段
<sql id="if-title-author">
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</sql>
2.引用片段
<select id="queryBlogIf" parameterType="map" resultType="blog">
select * from blog
<where>
<!-- 引用 sql 片段,如果refid 指定的不在本文件中,那么需要在前面加上 namespace -->
<include refid="if-title-author"></include>
<!-- 在这里还可以引用其他的 sql 片段 -->
</where>
</select>
注意:
- 最好基于 单表来定义 sql 片段,提高片段的可重用性
- 在 sql 片段中不要包括 where