1.父工程的pom.xml文件要导入的依赖
<dependencies>
//MySQL依赖
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.16</version>
</dependency>
//mybatis依赖
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.3</version>
</dependency>
//junit测试
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
</dependencies>
//处理配置时导入资源找不到的问题
<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>
2.resource资源下的mybatis-config.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>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=UTF-8&serverTime=Asia/shanghai"/>
<property name="username" value="root"/>
<property name="password" value="1234567"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/ke/dao/UserMapper.xml"/>
</mappers>
</configuration>
3.工具类(utils)-工厂模式获取连接
private static SqlSessionFactory sqlSessionFactory;
static {
try {
//获取SQLsessionMybatis对象
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSession getSqlSession(){
SqlSession sqlSession = sqlSessionFactory.openSession();
return sqlSession;
}
4.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">
<mapper namespace="com.ke.dao.UserDao">
<!--使用resultMap查询-->
<resultMap id="resultMap" type="Admin">
<association property="pwd" column="apwd"/>
</resultMap>
<select id="selectAllAdmin" resultMap="resultMap">
select * from test.Admin
</select>
<!--正常查询-->
<select id="selectAllAdmin" resultType="Admin">
select * from test.Admin
</select>
</mapper>
5.起别名
1.给整个包起别名,别名就是包里面的实体类的名称
<typeAliases>
<package name="com.ke.pojo"/>
</typeAliases>
2.给具体的类起别名
<typeAliases>
<typeAlias type="com.ke.pojo.Admin" alias="Admin"></typeAlias>
</typeAliases>
以上两种是要在mybatis-config.xml中加入的代码
3.也可以直接在实体类上面使用@Alias("User")的方式起别名
6.外部配置文件
<properties resource="db.properties"></properties>
以上的一句代码加在mybatis-config.xml中
以下是db.properties里面的代码
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=UTF-8&serverTime=Asia/shanghai
username=root
password=1234567
如果不用配置文件,url中的&要使用&来代替
如果要使用外部配置文件,<environment></environment>就要使用以下代码,可以创建多个<environment></environment>
<environment id="test">
<transactionManager type="JDBC"></transactionManager>
<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>
7.日志配置
1.标准的日志工厂实现
以下是mybatis-config.xml默认的日志文件
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
2.Log4j日志实现
(1)导入依赖
<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/ke.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)使用
1.在要使用的类中导入包import org.apache.log4j.Logger;注意别导错了。
2.生成log4j的对象 static Logger logger = Logger.getLogger(UserDaoTest.class);里面的参数为当前类的反射。
3.日志级别
logger.info("进入了模糊查询");
logger.debug("debug进入了log4j");
logger.error("erroe进入了log4j");
8.mybatis分页
1.使用Map的方式传入参数
//分页查询
@Test
public void limitSelect(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserDao mapper = sqlSession.getMapper(UserDao.class);
Map<String,Integer> map = new HashMap<String, Integer>();
map.put("startsize",2);
map.put("pagesize",4);
List<User> list = mapper.limitSelect(map);
for (User user : list) {
System.out.println(user);
}
}
2.具体的SQL语句
<select id="limitSelect" resultType="User" parameterType="map">
select * from mybatis.user limit #{startsize},#{pagesize}
</select>
1
2
3
9.lombok的使用
1.在idea中安装Lombok插件
2.在项目中导入jar包
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.10</version>
</dependency>
3.实体类上使用注解
10.一对一,一对多处理
1.一对一处理(多对一)
先在学生类中创建一个老师对象
StudentMapper.xml中的代码为
<select id="getStudent" resultMap="StudentTeacher2">
select s.sid,s.sname,s.sid,t.tid,t.tname
from student s,teacher t
where s.tid = t.tid
</select>
<resultMap id="StudentTeacher2" type="Student">
<id property="sid" column="sid"></id>
<result property="sname" column="sname"></result>
<result property="tid" column="tid"></result>
<--property是老师类的实例,javaType是实体类老师-->
<association property="teacher" javaType="Teacher">
<id property="tid" column="tid"/>
<result property="tname" column="tname"></result>
</association>
</resultMap>
2.一对多处理
先在老师类中创建一个学生类型的集合
<select id="getTeacherAndStudent" resultMap="StudentTeacher">
select * from student s,teacher t where s.tid = t.tid
</select>
<!---->
<resultMap id="StudentTeacher" type="Teacher">
<result property="tid" column="tid"/>
<result property="tname" column="tname"/>
<--property是集合的实例,ofType是集合的泛型-->
<collection property="students" ofType="Student">
<result property="sid" column="sid"/>
<result property="sname" column="sname"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>
11.动态SQL
1.if标签
测试类中的代码
BlogDao mapper = sqlSession.getMapper(BlogDao.class);
HashMap map = new HashMap();
map.put("title","");
map.put("author","千木枫");
List<Blog> blogs = mapper.selectBlogByX(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
BlogMapper.xml中的代码
select * from blog
<where>
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</where>
2.where标签
可以用来代替where 1 = 1
如何where里面的条件不成立,那么就会去除and以及后面的语句( select * from blog)
成立则拼接上(select * from blog WHERE author = ?)
3.模糊查询
(1)直接传带%%的字符串过去
blog.setAuthor("%千%");
List<Blog> blogs = mapper.selectBlogLike(blog);
for (Blog blog1 : blogs) {
System.out.println(blog1);
}
//BlogMapper.xml中的代码
<select id="selectBlogLike" resultType="Blog" parameterType="Blog">
select * from blog
<where>
<if test="title != null">
and title like #{title}
</if>
<if test="author != null">
and author like #{author}
</if>
</where>
</select>
(2)方法二:在写SQL时使用concat函数拼接sql语句–and author like concat(concat("%",#{author},"%"))
@Test
public void selectBlogLike2(){
BlogDao mapper = sqlSession.getMapper(BlogDao.class);
HashMap map = new HashMap();
map.put("name","view");
map.put("author","千");
List<Blog> blogs = mapper.selectBlogLike2(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
}
//BlogMapper.xml中的代码
<select id="selectBlogLike2" resultType="Blog" parameterType="map">
select * from blog
<where>
<if test="author != null">
and author like concat(concat("%",#{author},"%"))
</if>
</where>
order by ${name} desc
</select>
注意使用order by排序时传入的参数使用${参数名}的方式,#{可能会出错}
(3)方法三:使用bind标签将传入的值‘改变’一下
@Test
public void selectBlogLike3(){
BlogDao mapper = sqlSession.getMapper(BlogDao.class);
HashMap map = new HashMap();
map.put("title","spr");
map.put("author","千");
List<Blog> blogs = mapper.selectBlogLike3(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
}
//BlogMapper.xml中的代码
<select id="selectBlogByList" resultType="Blog">
select * from blog where id in
<foreach collection="list" item="blog" index="index" open="(" separator="," close=")">
#{blog.id}
</foreach>
</select>
4.foreach批量查询和批量插入
(1)批量插入
@Test
public void selectBlogByList(){
BlogDao mapper = sqlSession.getMapper(BlogDao.class);
List<Blog> list = new ArrayList<Blog>();
list.add(new Blog(1));
list.add(new Blog(3));
list.add(new Blog(5));
List<Blog> blogs1 = mapper.selectBlogByList(list);
for (Blog blog : blogs1) {
System.out.println(blog);
}
}
//BlogMapper.xml中的代码
<select id="selectBlogByList" resultType="Blog">
select * from blog where id in
<foreach collection="list" item="blog" index="index" open="(" separator="," close=")">
#{blog.id}
</foreach>
</select>
(2)批量插入
@Test
public void insertBlogList(){
BlogDao mapper = sqlSession.getMapper(BlogDao.class);
List<Blog> list = new ArrayList<Blog>();
list.add(new Blog(7,"aaa","bbb",800));
list.add(new Blog(8,"qqq","www",900));
list.add(new Blog(9,"rrr","eee",1000));
list.add(new Blog(10,"ttt","bbb",2000));
list.add(new Blog(11,"yyy","bbb",3000));
int i = mapper.insertBlogList(list);
sqlSession.commit();
System.out.println(i);
}
//BlogMapper.xml中的代码
<insert id="insertBlogList" >
insert into blog values
<foreach collection="list" item="blog" separator=",">
(#{blog.id},#{blog.title},#{blog.author},#{blog.view})
</foreach>
</insert>
5.set动态更新语句
@Test
public void updateBlog(){
BlogDao mapper = sqlSession.getMapper(BlogDao.class);
Blog blog = new Blog(7,"学生卡","千木",1111);
Blog blog1 = new Blog(1);
int i = mapper.updateBlog(blog1);
sqlSession.commit();
System.out.println(i);
}
//BlogMapper.xml中的代码
<update id="updateBlog" parameterType="Blog">
update blog
<set>
<if test="title != null">
title = #{title},
</if>
<if test="author != null">
author = #{author}
</if>
</set>
where id = #{id}
</update>