文章目录
POM.xml
<!--导入依赖-->
<dependencies>
<!--mysql驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.46</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>
</dependencies>
<build>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
<include>**/*.properties</include>
</includes>
</resource>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.xml</include>
<include>**/*.properties</include>
</includes>
</resource>
</resources>
</build>
com.jcjava.util.MybatisUtils.java
package com.jcjava.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.InputStream;
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();
}
}
public static SqlSession getSqlSession(){
return sqlSessionFactory.openSession(true);
}
}
resources
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>
<!-- 引入外部配置 -->
<properties resource="config.properties"/>
<!-- log4j日志-->
<settings>
<setting name="logImpl" value="LOG4J"/>
</settings>
<!-- 给实体类起命名-->
<typeAliases>
<typeAlias type="com.jcjava.pojo.User" alias="User"/>
</typeAliases>
<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="com/jcjava/dao/UserMapper.xml"/>
</mappers>
</configuration>
config.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?&useSSL=true&useUnicode=true&characterEncoding=UTF-8
username=root
password=root
log4j.properties
#将等级为DEBUG的日志信息输出到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/jcjava.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
com.jcjava
UserMapper.java
package com.jcjava.dao;
import com.jcjava.pojo.User;
import org.apache.ibatis.annotations.*;
import java.util.List;
public interface UserMapper {
/*
不使用注解
CRUD(增删改查)
*/
//查找所有用户
List<User> getUserList();
//增加一条用户信息
int addUser(User user);
//更新一条用户信息
int updateUser(User user);
//删除一条用户信息
int deleteUser(int id);
/*
使用注解
CRUD(增删改查)
*/
@Select("select id id,username name,password pwd from user ")
List<User> getUserList1();
@Insert("insert into user(id,username,password) values(#{id},#{name},#{pwd})")
int addUser1(User user);
@Update("update user set username=#{name},password=#{pwd} where id =#{id}")
int updateUser1(User user);
@Delete("delete from user where id=#{id}")
int deleteUser1(@Param("id") int id);
}
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.jcjava.dao.UserMapper">
<select id="getUserList" resultMap="UserMap">
select * from user
</select>
<insert id="addUser" parameterType="User">
insert into user(id,username,password) values(#{id},#{name},#{pwd})
</insert>
<update id="updateUser" parameterType="User">
update user set username=#{name},password=#{pwd} where id=#{id}
</update>
<delete id="deleteUser" parameterType="Integer">
delete from user where id=#{id};
</delete>
<resultMap id="UserMap" type="User">
<result column="id" property="id"/>
<result column="username" property="name"/>
<result column="password" property="pwd"/>
</resultMap>
</mapper>
模糊查询
//模糊查询
List<User> getUserLike(String value);
<select id="getUserLike" parameterType="String" resultType="com.jcjava.pojo.User">
select * from user where username like #{value}
</select>
//like
@Test
public void getUserLike(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> userList =mapper.getUserLike("%王%");
for (User user : userList) {
System.out.println(user);
}
sqlSession.close();
}
万能的map
int addUser2(Map<String,Object> map);
<insert id="addUser2" parameterType="map">
insert into user values (#{id},#{username},#{pwd})
</insert>
//map添加用户
public void addUser2(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
Map<String,Object> map =new HashMap<String,Object>();
map.put("id",10);
map.put("username","历史");
map.put("pwd","35620");
int i = mapper.addUser2(map);
System.out.println(i);
sqlSession.commit();
sqlSession.close();
}
多对一的处理
学生类
public class Student{
private int id;
private String name;
private Teacher teacher;
}
老师类
public class Teacher{
private int tid;
private String name;
}
按照查询嵌套处理
<!--
思路:
1.查询所有学生信息
2.根据查询的学生的tid,寻找对应的老师 子查询
-->
<select id="getStudent" resultMap="StudentTeacher">
select * from student
</select>
<resultMap id="StudentTeacher" type="Student">
<result property="id" column="id"/>
<result property="name" column="name"/>
<!--复杂的属性,我们需要单独处理,对象:association 集合 :collection
javaType:指定属性的类型
集合中的泛型信息,我们使用ofType获取
-->
<association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="Teacher">
select * from teacher where id=#{id}
</select>
按照结果嵌套处理
<!--按照结果嵌套处理-->
<select id="getStudent2" resultMap="StudentTeacher2">
select s.id sid ,s.name sname ,t.name tname
from student s,teacher t
where s.tid =t.tid;
</select>
<resultMap id ="StudentTeacher2" type="Student">
<result property ="id" column="sid"/>
<result property ="name" column="sname"/>
<assocication property="teacher" javaType="Teacher">
<result property="name" column="tname"/>
</assocication>
</resultMap>
一对多的处理
学生类
public class Student{
private int id;
private String name;
private int tid;
}
老师类
public class Teacher{
private int tid;
private String name;
private List<Student> students;
}
按照结果嵌套处理
<!--按照结果嵌套处理-->
<select id="getTeacher" resultMap="TeacherMap">
select t.name tname, t.tid tid ,s.id sid ,s.name sname
from teacher t,student s
where t.tid=s.tid and t.tid=#{id}
</select>
<resultMap id="TeacherMap">
<result property="id" column="tid"/>
<result property="name" column="tname" >
<collection property ="students" ofType="Student">
<result property="id" column="sid" />
<result property="name" column="sname" />
<result property="tid" column="tid" />
</collection>
</resultMap>
动态SQL
if
<!-- 动态SQL -->
<select id="getBlogList" 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>
<select id="getList" resultMap="BlogMap">
select * from blog
</select>
<resultMap id="BlogMap" type="Blog">
<result property="createtime" column="create_time"/>
</resultMap>
@Test
public void testgetBlogList(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Map<Object,Object> map =new HashMap<Object, Object>();
map.put("title","Java");
List<Blog> blogList = mapper.getBlogList(map);
for (Blog blog : blogList) {
System.out.println(blog);
}
sqlSession.close();
}
choose(when ,otherwise) trim( where set)
where
<!-- 动态SQL -->
<select id="getBlogList" parameterType="map" resultMap="BlogMap">
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>
@Test
public void testgetBlogList(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Map<Object,Object> map =new HashMap<Object, Object>();
map.put("title","Java");
map.put("author","狂神说");
map.put("views",9999);
List<Blog> blogList = mapper.getBlogList(map);
for (Blog blog : blogList) {
System.out.println(blog);
}
sqlSession.close();
}
update
<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>
所谓的动态SQL,
本质还是SQL语句,只是我们可以在SQL层面,去执行一个逻辑代码。
List blogList = mapper.getBlogList(map);
for (Blog blog : blogList) {
System.out.println(blog);
}
sqlSession.close();
}
#### update
```xml
<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>
所谓的动态SQL,
本质还是SQL语句,只是我们可以在SQL层面,去执行一个逻辑代码。