Mybatis学习
Mybatis
第一个mybatis程序
1、搭建环境
搭建数据库——>新建项目,普通maven项目——>删除src目录(作为父工程)——>导入maven依赖
<!-- 导入依赖-->
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<!-- 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>
<!-- log4j日志 -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<!-- lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.12</version>
</dependency>
</dependencies>
2、创建子模块
-
编写mybatis核心配置文件,注意注册mapper
<?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/demo?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC"/> <property name="username" value="root"/> <property name="password" value="123456"/> </dataSource> </environment> </environments> </configuration>
注册mapper
<!-- 每一个mapper.xml都需要在这个mybatis核心文件中注册--> <mappers> <mapper resource="com/luo/dao/UserMapper.xml"></mapper> </mappers>
-
编写mybatis工具类
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.InputStream;
//sqlsessionFactory构建sqlsession
public class MyBatisUtils {
private static SqlSessionFactory factory=null;
static {
try {
String resource = "mybatis-config.xml";
InputStream in= Resources.getResourceAsStream(resource);
//创建SqlSessionFactory对象
factory=new SqlSessionFactoryBuilder().build(in);
} catch (IOException e) {
e.printStackTrace();
}
}
//获取SqlSession的方法
public static SqlSession getSqlSession() {
SqlSession sqlSession=null;
if(factory!=null) {
sqlSession=factory.openSession();//非自动提交事务
}
return sqlSession;
}
}
3、编写代码
-
实体类
package com.luo.pojo; public class User { private int id; private String name; private String pwd; public User() { } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getPwd() { return pwd; } public void setPwd(String pwd) { this.pwd = pwd; } public User(int id, String name, String pwd) { this.id = id; this.name = name; this.pwd = pwd; } @Override public String toString() { return "User{" + "id=" + id + ", name='" + name + '\'' + ", pwd='" + pwd + '\'' + '}'; } }
-
Dao接口
package com.luo.dao; import com.luo.pojo.User; import java.util.List; public interface UserDao { List<User> getUserList(); }
-
接口实现类,由原来的UserDaoImpl转化为一个mapper配置文件
<?xml version="1.0" encoding="UTF8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--namespace绑定一个对应的Dao/mapper接口-->
<mapper namespace="com.luo.dao.UserDao">
<!-- select查询语句-->
<select id="getUserList" resultType="com.luo.pojo.User">
select *from user
</select>
</mapper>
4、测试
-
junit测试
package com.luo.dao; import com.luo.pojo.User; import com.luo.utils.MyBatisUtils; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import java.util.List; public class UserDaoTest { @Test public void test(){ //获取sqlSession对象 SqlSession sqlSession = MyBatisUtils.getSqlSession(); //getMapper() UserDao userDao = sqlSession.getMapper(UserDao.class); List<User> userList = userDao.getUserList(); for (User user : userList) { System.out.println(user); } //关闭sqlSession sqlSession.close(); } }
注意:如果遇到资源无法被导出的问题,在pom.xml中配置resources
<!--在build中配置resources,来防止我们资源导出失败的问题--> <build> <resources> <resource> <directory>src/main/resources</directory> <includes> <include>**/*.properties</include> <include>**/*.xml</include> </includes> </resource> <resource> <directory>src/main/java</directory> <includes> <include>**/*.properties</include> <include>**/*.xml</include> </includes> <filtering>true</filtering> </resource> </resources> </build>
CRUD
1、在接口中写方法
public interface UserDao {
List<User> getUserList();
//根据id查询用户
User getUserById(int id);
//添加用户
int addUser(User user);
//修改用户
int updateUser(User user);
//删除用户
int deleteUser(int id);
}
2、在xml文件中写标签
<?xml version="1.0" encoding="UTF8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--namespace绑定一个对应的Dao/mapper接口-->
<mapper namespace="com.luo.dao.UserDao">
<!-- select查询语句-->
<select id="getUserList" resultType="com.luo.pojo.User">
select *from user
</select>
<select id="getUserById" resultType="com.luo.pojo.User" parameterType="int">
select *from user where id = #{id}
</select>
<select id="addUser" parameterType="com.luo.pojo.User">
insert into user values (#{id},#{name},#{pwd})
</select>
<update id="updateUser" parameterType="com.luo.pojo.User">
update user
set id = #{id},name=#{name},pwd = #{pwd}
where id=4;
</update>
<delete id="deleteUser" parameterType="int">
delete
from user
where id = #{id};
</delete>
</mapper>
-
namespace中的包名要和mapper/dao接口的包名一致
-
id:就是对应的namespace中的方法名
-
resultType:sql语句中的返回值
-
parameterType:参数类型
-
注意:需要idea连接数据库,写sql语句才会有提示
3、写测试代码
public class UserDaoTest {
@Test
public void test(){
//获取sqlSession对象
SqlSession sqlSession = MyBatisUtils.getSqlSession();
//getMapper()
UserDao userDao = sqlSession.getMapper(UserDao.class);
List<User> userList = userDao.getUserList();
for (User user : userList) {
System.out.println(user);
}
//关闭sqlSession
sqlSession.close();
}
@Test
public void getUserById(){
//获取sqlSession对象
SqlSession sqlSession = MyBatisUtils.getSqlSession();
//getMapper()
UserDao userDao = sqlSession.getMapper(UserDao.class);
User user = userDao.getUserById(1);
System.out.println(user);
//关闭sqlSession
sqlSession.close();
}
@Test
public void addUser(){
//获取sqlSession对象
SqlSession sqlSession = MyBatisUtils.getSqlSession();
//getMapper()
UserDao userDao = sqlSession.getMapper(UserDao.class);
userDao.addUser(new User(4,"赵4","123"));
//增删改需要提交事务
sqlSession.commit();
//关闭sqlSession
sqlSession.close();
}
@Test
public void updateUser(){
//获取sqlSession对象
SqlSession sqlSession = MyBatisUtils.getSqlSession();
//getMapper()
UserDao userDao = sqlSession.getMapper(UserDao.class);
userDao.updateUser(new User(4,"赵四","123456"));
//增删改需要提交事务
sqlSession.commit();
//关闭sqlSession
sqlSession.close();
}
@Test
public void deleteUser(){
//获取sqlSession对象
SqlSession sqlSession = MyBatisUtils.getSqlSession();
//getMapper()
UserDao userDao = sqlSession.getMapper(UserDao.class);
userDao.deleteUser(4);
//增删改需要提交事务
sqlSession.commit();
//关闭sqlSession
sqlSession.close();
}
}
Map和模糊查询拓展
Map
假设我们的实体类或者数据库中的表,字段或参数过多,我们可以用map来传递参数;
//只修改密码,使用map传参
int updatePwd(Map<String,Object> map);
<update id="updatePwd" parameterType="Map">
update user
set pwd = #{pwd}
where id = #{id};
</update>
@Test
public void updatePwd(){
//获取sqlSession对象
SqlSession sqlSession = MyBatisUtils.getSqlSession();
//getMapper()
UserDao userDao = sqlSession.getMapper(UserDao.class);
Map<String, Object> map = new HashMap<String, Object>();
//只想修改密码,就不用把每个字段都写上
map.put("id",3);
map.put("pwd",11111);
userDao.updatePwd(map);
//增删改需要提交事务
sqlSession.commit();
//关闭sqlSession
sqlSession.close();
}
模糊查询拓展
java代码执行的时候,传递通配符%%;
//模糊查询
List<User> getUserLike(String name);
<select id="getUserLike" resultType="com.luo.pojo.User">
select *from user where name like #{value}
</select>
@Test
public void getUserLike(){
//获取sqlSession对象
SqlSession sqlSession = MyBatisUtils.getSqlSession();
//getMapper()
UserDao userDao = sqlSession.getMapper(UserDao.class);
//查询所有姓李的人
List<User> userList = userDao.getUserLike("%李%");
for (User user : userList) {
System.out.println(user);
}
//关闭sqlSession
sqlSession.close();
}
配置解析
核心配置文件:mybatis-config.xml
属性优化
我们可以通过properties属性来实现引用配置文件
-
编写一个配置文件 db.properties
driver = com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC username=root password=123456
-
在核心配置文件里面引入
<properties resource="db.properties"/>
别名优化(typeAliases)
-
给实体类写别名
<typeAliases> <typeAlias type="com.luo.pojo.User" alias="User"></typeAlias> </typeAliases>
这样在需要使用com.luo.pojo.User时,可以用User代替
-
给包别名
<typeAliases> <package name="com.luo.pojo"/> </typeAliases>
这样在需要使用这个包的实体类时间,默认别名为这个类的类名,首 字母小写。比如需要使用com.luo.pojo.User时,可以用user代替。也可以在实体类前通过注解指定别名@Alias。
映射器说明(mappers)
-
方式一:使用相对于类路径的资源引用[推荐使用]
<mappers> <mapper resource="com/luo/dao/UserMapper.xml"></mapper> </mappers>
-
方式二:使用映射器接口实现类的完全限定类名
<mappers> <mapper class="com.luo.dao.UserMapper"/> </mappers>
注意:接口和他的mapper配置文件必须同名和在同一个包下
-
方式三:通过pakage
<mappers> <package name="com.luo.dao"/> </mappers>
注意:接口和他的mapper配置文件必须同名和在同一个包下
解决属性名和字段名不一致的问题
如果实体类的属性名和数据库中的字段名不一致的话,查询出来可能会为空。
解决方法:
-
改sql语句,起别名:pwd as “password”
-
resultMap(结果集映射):
<resultMap id="UserMap" type="User"> <result column="pwd" property="password"/> </resultMap>
日志
如果一个数据库操作出了异常,日志可以帮助我们排错。在核心配置文件中配置,具体使用哪个日志。
标准日志,可以直接使用
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
log4j
1、导包
<!-- log4j日志 -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
2、log4j.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/luo.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、配置log4j为日志的实现
<settings>
<setting name="logImpl" value="LOG4J"/>
</settings>
4、log4j的使用
分页
使用Limit分页
SELECT *from user limit startIndex,pageSize;
SELECT *from user limit 3;#[0,n]
使用mybatis实现分页
- 接口
List<User> getUserLimit(Map<String,Integer> map);
- mapper.xml
<select id="getUserLimit" resultType="User" parameterType="map">
select *from user limit #{startIndex},#{pageSize}
</select>
- 测试
@Test
public void getUserLimit(){
//获取sqlSession对象
SqlSession sqlSession = MyBatisUtils.getSqlSession();
//getMapper()
UserMapper userDao = sqlSession.getMapper(UserMapper.class);
Map<String,Integer> map = new HashMap<String,Integer>();
map.put("startIndex",0);
map.put("pageSize",2);
List<User> userList = userDao.getUserLimit(map);
for (User user : userList) {
System.out.println(user);
}
//关闭sqlSession
sqlSession.close();
}
Lombok
1、安装插件
2、导包
<!-- lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.12</version>
</dependency>
3、使用
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
private int id;
private String name;
private String pwd;
}
@Data:无参构造,get,set,tostring,hashcode,equals
@AllArgsConstructor:有参构造
@NoArgsConstructor:无参构造
复杂查询环境搭建
步骤
- 新建表teacher,student
- 写对应实体类Teacher,Student
- 写实体类对应接口TeacherMapper,StudentMapper
- 写对应xml文件
- 测试运行
多对一处理:查询所有学生及对应的老师信息
方式一:按照查询嵌套处理(子查询)
//查询所有学生及对应的老师信息
List<Student> getStudentInfo();
<!-- 思路:1、查询所有学生2、查询tid对应的老师-->
<resultMap id="StudentInfo" type="student">
<!-- 对象用association,javaType说明teacher是一个对象,再嵌套查询-->
<association property="teacher" column="tid" javaType="teacher" select="getTeacher"></association>
</resultMap>
<select id="getStudentInfo" resultMap="StudentInfo">
select *from student
</select>
<select id="getTeacher" resultType="teacher">
##{tid}里面这个tid可以换成任意值,mybatis会自动匹配
select *from teacher where id = #{tid}
</select>
@Test
public void test(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
StudentMapper Mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> studentList = Mapper.getStudentInfo();
for (Student student : studentList) {
System.out.println(student);
}
sqlSession.close();
}
方式二:按照结果嵌套处理(联表查询)
<select id="getStudentInfo" resultMap="StudentInfo">
#注意:这里必须取别名
select s.id sid,s.name sname,t.name tname
from student s,teacher t
where s.tid = t.id
</select>
<resultMap id="StudentInfo" type="student">
<result property="id" column="sid"/>
<result property="name" column="sname"></result>
<!-- 使用association映射-->
<association property="teacher" javaType="teacher">
<result property="name" column="tname"/>
</association>
</resultMap>
一对多处理:查询指定老师及对应的学生信息
方式一:按照结果嵌套处理(联表查询)
//查询指定老师及其所有学生
Teacher getTeacher(int id);
<select id="getTeacher" resultMap="teacherInfo">
select t.id tid,t.name tname,s.id sid,s.name sname
from teacher t,student s
where t.id = #{id} and t.id = s.tid
</select>
<resultMap id="teacherInfo" type="teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
<!-- 集合中的泛型信息用ofType获取-->
<collection property="studentList" ofType="student" >
<result property="id" column="sid"/>
<result property="name" column="sname"/>
</collection>
</resultMap>
@Test
public void test(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
System.out.println(teacherMapper.getTeacher(1));
sqlSession.close();
}
方式二:按照查询嵌套处理(子查询)
<select id="getTeacher" resultMap="teacherInfo">
select *from teacher where id = #{id}
</select>
<resultMap id="teacherInfo" type="teacher">
<!-- 这里要写column,javaType两个属性,方式一无需写-->
<collection property="studentList" column="id" javaType="ArrayList" ofType="student" select="getStudentByTeacherId"/>
</resultMap>
<select id="getStudentByTeacherId" resultType="student">
select *from student where tid = #{tid}
</select>
小结
1、关联-association 多对一
2、集合-collection 一对多
3、javaType & ofType
javaType:用来指定实体类中属性的集合
ofType:用来指定映射到List或集合中的pojo类型
动态SQL
动态sql就是根据不同的条件生成不同的sql语句
搭建环境
与前面一致,可以添加一个生成随机id的工具类
//生成随机id的工具类
public class IDutils {
public static String getId(){
return UUID.randomUUID().toString().replaceAll("-","");
}
@Test
public void test(){
System.out.println(IDutils.getId());
}
}
IF语句
例子:有条件的查询
//查询
List<Blog> queryBlog(Map map);
<select id="queryBlog" parameterType="map" resultType="blog">
select *from blog
<where>
#查特定title的blog
<if test="title != null">
title = #{title}
</if >
<if test="author != null">
and author=#{author}
</if>
</where>
</select>
public void test(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Map map = new HashMap();
map.put("title","Mybatis");
List<Blog> blogs = mapper.queryBlog(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
choose,when,otherwise(相当于switch)
例子:给了什么就按什么查询
<select id="queryBlogChoose" resultType="blog" parameterType="map">
select *from blog
<where>
<choose>
<when test="title != null">
title = #{title}
</when>
<when test="views != null">
and views = #{views}
</when>
<otherwise>
and 1 = 1
</otherwise>
</choose>
</where>
</select>
trim(set where)
sql片段
有的时候,我们可以把一些功能的部分抽取出来,方便复用!
1、使用sql标签抽取公共部分
2、使用include调用