idea搭建mybatis框架以及注解和xml两种方式CRUD（增删改查）

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层面，去执行一个逻辑代码。