标签：mapper 基础 public blog sqlSession MyBatis log4j id

MyBatis基础

1.什么是mybatis

MyBatis 是一款优秀的持久层框架，它支持自定义 SQL、存储过程以及高级映射。

MyBatis 免除了几乎所有的 JDBC 代码以及设置参数和获取结果集的工作。

MyBatis 可以通过简单的 XML 或注解来配置和映射原始类型、接口和POJO（Plain Ordinary Java Object，简单的Java对象）为数据库中的记录。

1.2 持久化

持久化就是将程序的数据在持久状态和瞬时状态转化的过程

内存：断电及失

数据库（jdbc）,io文件持久化

1.3 持久层

Dao层，Servlet层

完成持久化工作的代码块‘
层界限明显

1.4 为什么需要mybatis

将数据存入到数据库中
传统的JDBC代码太复杂了，简化，框架。

2. 第一个Mybatis程序

思路：搭建环境--导入mybatis--编写代码--测试

2.1 搭建环境

搭建数据库

create database `mybatis`;

use `mybatis`;

create table `user`(
	`id` int(20) not null primary key,
	`name` varchar(30) default null,
	`pwd` varchar(20) default null
)engine=innodb default charset=utf8;

insert into `user`(`id`,`name`,`pwd`) values
(1,'张三','123456'),
(2,'李四','1234'),
(3,'王二','123')

导入依赖

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
    </dependency>
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.2</version>
    </dependency>
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
    </dependency>
</dependencies>

核心配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://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.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
</configuration>

编写工具类

package com.jf.utils;

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 {
            //获取SqlSessionFactory的对象
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    //既然有了 SqlSessionFactory，顾名思义，我们可以从中获得 SqlSession 的实例
    //SqlSession 提供了在数据库执行 SQL 命令所需的所有方法
    public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession();
    }
}

2.2Dao接口

package com.jf.dao;

import com.jf.pojo.User;

import java.util.List;

public interface UserDao {
    List<User> getUserList();
}

接口实现类由原来的UserDaoImpl转变为一个Mapper配置文件

<?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.jf.dao.UserDao">
    <select id="getUserList" resultType="com.jf.pojo.User">
        select * from mybatis.user
    </select>
</mapper>

3.CRUD

1.namespace

namespace中的包名要和Mapper的包名一致

2.select

id对应的是namespace中的包名
resultType:sql语句中的返回值
parameterType:数据类型

1.编写接口

List<User> getUserList();

2编写对应的mapper中的sql语句

<select id="getUserList" resultType="com.jf.pojo.User">
    select * from mybatis.user
</select>

3.测试

@Test
public void test(){
    //第一步获取SqlSession对象
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    //方式一：getMapper
    UserMapper userDao = sqlSession.getMapper(UserMapper.class);
    List<User> userList = userDao.getUserList();
    for (User user : userList) {
        System.out.println(user);
    }
    //关闭SqlSession
    sqlSession.close();
}

3.insert into

int addUser(User user);
<insert id="addUser" parameterType="com.jf.pojo.User">
        insert into mybatis.user (id,name,pwd) values (#{id},#{name},#{pwd});
</insert>
@Test
    public void addUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        mapper.addUser(new User(5, "一一", "123456"));
        sqlSession.commit();
        sqlSession.close();
    }

4.update

int updateUser(User user);
<update id="updateUser" parameterType="com.jf.pojo.User">
        update mybatis.user set  name= #{name},pwd=#{pwd} where id =#{id};
    </update>
@Test
    public void updateUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.updateUser(new User(5,"李毅","890675"));
        sqlSession.commit();
        sqlSession.close();
    }

5.delete

int deleteUser(int id);
<delete id="deleteUser" parameterType="com.jf.pojo.User">
        delete from mybatis.user where id = #{id};
    </delete>
@Test
    public void deleteUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.deleteUser(5);
        sqlSession.commit();
        sqlSession.close();
    }

注意点：

增删改需要提交事务

7.万能的map

//万能的map
int addUser2(Map<String,Object> map);

<insert id="addUser2" parameterType="map">
    insert into mybatis.user (id,pwd) values (#{userid},#{password});
</insert>

@Test
public void addUser2() {
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    HashMap<String, Object> map = new HashMap<>();
    map.put("userid",5);
    map.put("password","123123");
    mapper.addUser2(map);
    sqlSession.close();
}

Map传递参数，直接在sql中取出key即可

对象传递参数，直接在sql中取对象的属性即可

只有一个基本类型参数的情况下，可以直接在sql中取到

多个参数用Map，或者注解

8.模糊查询

1.java代码执行的时候，传递通配符%%

List<User> getUserLike(String value);

<select id="getUserLike" resultType="com.jf.pojo.User">
    select * from mybatis.user where name like #{value};
</select>

@Test
public void getUserLike(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    List<User> userLike = mapper.getUserLike("%王%");
    for (User user : userLike) {
        System.out.println(user);
    }
    sqlSession.close();
}

2.在sql拼接的时候使用通配符

<select id="getUserLike" resultType="com.jf.pojo.User">
    select * from mybatis.user where name like "%"#{value}"%";
</select>

9.结果集映射resultMap

<resultMap id="UserMap" type="com.jf.pojo.UserList">
    <result column="id" property="id"/>
    <result column="name" property="name"/>
    <result column="pwd" property="password"/>
</resultMap>
<select id="getUserList" resultType="com.jf.pojo.UserList" resultMap="UserMap">
    select * from mybatis.user
</select>

4 配置解析

1.环境配置

<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>

2.properties

db.properties配置文件

driver = com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatisuseSSL=true&useUnicode=true&characterEncoding=UTF-8
username=root
password=123456

在核心配置文件中引入

<properties resource="db.properties"/>

可以直接引入外部文件
可以在其中增加一些属性配置
如果两个文件有同一个字段，优先使用外部配置文件

3类型别名（typeAliases）

类型别名可为 Java 类型设置一个缩写名字。它仅用于 XML 配置，意在降低冗余的全限定类名书写。

<typeAliases>
    <typeAlias type="com.jf.pojo.User" alias="User"/>
</typeAliases>

也可以指定包名，它的默认别名就会为这个类的类名，首字母小写

<typeAliases>
    <package name="com.jf.pojo"/>
</typeAliases>

在实体类比较少的时候，使用第一种

实体类比较多的时候，使用第二种

第一种可以自定义，第二种不能自定义,如果非要改，可以在实体上增加注解

@Alias("hello")
public class User {

4.映射器

<mappers>
<!--        <mapper resource="com/jf/dao/UserMapper.xml"/>-->
        <mapper class="com.jf.dao.UserMapper"/>
    </mappers>

5日志

5.1日志工厂

<settings>
    <setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>

5.2LOG4J

1.先导入包

<!-- https://mvnrepository.com/artifact/log4j/log4j -->
<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.17</version>
</dependency>

2.log4j.properties

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/kuang.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.在使用Log4j的类中，导入包import org.apache.log4j.Logger;

2.日志对象，参数为当前类的class

static Logger logger = Logger.getLogger(UserDaoTest.class);

3.日志级别

logger.info("info:进入了testLog4j");
logger.debug("debug:进入了testLog4j");
logger.error("error:进入了testLog4j");

6分页

List<User> getUserByLimit(Map<String,Integer> map);

<select id="getUserByLimit" parameterType="map" resultType="user">
    select * from mybatis.user limit #{startIndex},#{pageSize};
</select>

@Test
public void getUserByLimit(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    HashMap<String, Integer> map = new HashMap<>();
    map.put("startIndex",0);
    map.put("pageSize",3);
    List<User> userList = mapper.getUserByLimit(map);
    for (User user : userList) {
        System.out.println(user);
    }
    sqlSession.close();
}

7.注解开发

我们在工具类创建的时候实现自动提交事务

public static SqlSession getSqlSession(){
    return sqlSessionFactory.openSession(true);
}

编写接口，增加注解

//根据用户查询id
//方法存在多个参数，所有的参数前面必须加上@Param("id")注解
@Select("select * from user where id=#{id}")
User getUserById(@Param("id") int id);

测试

@Test
public void getUserById() {
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    User userById = mapper.getUserById(1);
    System.out.println(userById);
    sqlSession.close();
}

注意：我们必须要将接口注册绑定到我们的核心配置文件中

关于@Param（）注解

基本类型的参数或者String类型，需要加上
引用类型不需要加
在sql中引用的就是我们这里@param("id")中设定的属性名

8 lombok

1.在idea中setting添加lombok

2.导入jar包

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.10</version>
</dependency>

3.测试

@Data //无参构造，get,set,toString,hashcode,equals
@AllArgsConstructor
@NoArgsConstructor
public class User {
    //实体类
    private int id;
    private String name;
    private String pwd;

}

9多对一处理

9.1 按照查询嵌套处理

package com.jf.dao;


import com.jf.pojo.Student;

import java.util.List;

public interface StudentMapper {
    public List<Student> getStudent();
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.jf.dao.StudentMapper">
    <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
-->
        <association property="teacher" column="tid" javaType="teacher" select="getTeacher"/>
    </resultMap>
    <select id="getTeacher" resultType="teacher">
        select * from teacher where id=#{id};
    </select>

</mapper>

package MyTest;

import com.jf.dao.StudentMapper;
import com.jf.pojo.Student;
import com.jf.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class MyTest {
    @Test
    public void test(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList = mapper.getStudent();
        for (Student student : studentList) {
            System.out.println(student);
        }
        sqlSession.close();
    }
}

9.2按照结果嵌套查询

public List<Student> getStudent2();

<select id="getStudent2" resultMap="StudentTeacher2">
    select s.id sid ,s.name sname,t.name tname from student s, teacher t where s.tid=t.id
</select>
<resultMap id="StudentTeacher2" type="student">
    <result property="id" column="sid"/>
    <result property="name" column="sname"/>
    <association property="teacher" javaType="teacher">
        <result property="name" column="tname"/>
    </association>
</resultMap>

@Test
public void test2(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    List<Student> studentList = mapper.getStudent2();
    for (Student student : studentList) {
        System.out.println(student);
    }
    sqlSession.close();
}

10.一对多处理

按照结果嵌套处理

package com.jf.pojo;

import lombok.Data;

import java.util.List;

@Data
public class Teacher {
    private int id;
    private String name;
    //一个老师拥有多个学生
    private List<Student> students;
}

Teacher getTeacher(@Param("tid") int id);

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.jf.dao.TeacherMapper">
<select id="getTeacher" resultMap="TeacherStudent">
    select s.id sid ,s.name sname,t.name tname,t.id tid  from student s, teacher t where s.tid=t.id and t.id=#{tid}
</select>
    <resultMap id="TeacherStudent" type="teacher">
        <result property="id" column="id"/>
        <result property="name" column="tname"/>
<!--        javaType=""指定属性的类型
            集合中泛型信息，使用ofType获取
-->
        <collection property="students" ofType="student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>

</mapper>

import com.jf.dao.TeacherMapper;
import com.jf.pojo.Teacher;
import com.jf.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class MyTest {
    @Test
    public void test() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher(1);
        System.out.println(teacher);
        sqlSession.close();
    }
}

注意：

javaType用来指定实体类中属性的类型
ofType用来指定映射List或者集合中的pojo类型，泛型的约束类型

11动态sql

搭建环境

CREATE TABLE `blog`(
`id` VARCHAR(50) NOT NULL COMMENT '博客id',
`title` VARCHAR(100) NOT NULL COMMENT '博客标题',
`auther` VARCHAR(30) NOT NULL COMMENT '博客作者',
`create_time` DATETIME NOT NULL COMMENT '创建时间',
`views` INT(30) NOT NULL COMMENT '浏览量'
)ENGINE=INNODB DEFAULT CHARSET=utf8

int addBlog(Blog blog);

<insert id="addBlog" parameterType="blog">
    insert into mybatis.blog(id,title,auther,views) values(#{id},#{title},#{auther},#{views});
</insert>
    
@Test
    public void test() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Blog blog = new Blog();
        blog.setId(IDutils.getId());
        blog.setTitle("Mybatis");
        blog.setAuther("狂神说");
        blog.setViews(9999);

        mapper.addBlog(blog);

        blog.setId(IDutils.getId());
        blog.setTitle("Java");
        mapper.addBlog(blog);

        blog.setId(IDutils.getId());
        blog.setTitle("Spring");
        mapper.addBlog(blog);

        blog.setId(IDutils.getId());
        blog.setTitle("微服务");
        mapper.addBlog(blog);

        sqlSession.close();
    }

if where choose when set

List<Blog> queryBlogIF(Map map);

List<Blog> queryBlogChoose(Map map);

int updateBlog(Map map);

<select id="queryBlogIF" parameterType="map" resultType="blog">
    select * from mybatis.blog
    <where>
        <if test="title !=null">
            and title =#{title}
        </if>
        <if test="auther !=null">
            and auther =#{auther}
        </if>
    </where>
</select>
    <select id="queryBlogChoose" parameterType="map" resultType="blog">
        select * from mybatis.blog
        <where>
            <choose>
                <when test="title !=null">
                    title =#{title}
                </when>
                <when test="auther !=null">
                    and auther=#{auther}
                </when>
                <otherwise>
                    and views =#{views}
                </otherwise>
            </choose>
        </where>
    </select>
    <update id="updateBlog" parameterType="map" >
        update mybatis.blog
        <set>
            <if test="title !=null">
                title =#{title},
            </if>
            <if test="auther !=null">
                auther=#{auther}
            </if>
        </set>
        where id =#{id}
    </update>

 @Test
    public void test1(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        map.put("title","Java1");
        //map.put("auther","狂神说1");
        map.put("id" , "50f71d7274834e90b1e0941a1b886300");
//        List<Blog> blogs = mapper.queryBlogChoose(map);
//        for (Blog blog : blogs) {
//            System.out.println(blog);
//        }
        mapper.updateBlog(map);
        sqlSession.close();
    }

SQL片段

使用sql标签抽取公共部分
在需要使用的地方使用include标签即可

<sql id="IF-title-author">
        <if test="title !=null">
            and title =#{title}
        </if>
        <if test="auther !=null">
            and auther =#{auther}
        </if>
    </sql>
<select id="queryBlogIF" parameterType="map" resultType="blog">
    select * from mybatis.blog
    <where>
        <include refid="IF-title-author"></include>
    </where>
</select>

foreach

List<Blog> queryForeach(Map map);

<select id="queryForeach" parameterType="map" resultType="blog">
    select * from mybatis.blog
    <where>
        <foreach collection="ids" item="id" open="and (" close=")" separator="or">
            id =#{id}
        </foreach>
    </where>
</select>

@Test
public void test2(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();

    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    HashMap map = new HashMap();
    ArrayList<Integer> ids = new ArrayList<Integer>();
    ids.add(1);
    ids.add(2);
    ids.add(3);
    map.put("ids",ids);
    List<Blog> blogs = mapper.queryForeach(map);
    for (Blog blog : blogs) {
        System.out.println(blog);
    }
    sqlSession.close();
}

动态sql就是在拼接sql语句，我们只要sql的正确性，按照sql的格式，去排列组合就可以了

标签：mapper,基础,public,blog,sqlSession,MyBatis,log4j,id
From： https://www.cnblogs.com/huangjiangfei/p/18419510

MyBatis基础

MyBatis基础

1.什么是mybatis

1.2 持久化

1.3 持久层

1.4 为什么需要mybatis

2. 第一个Mybatis程序

2.1 搭建环境

2.2Dao接口

3.CRUD

1.namespace

2.select

3.insert into

4.update

5.delete

7.万能的map

8.模糊查询

9.结果集映射resultMap

4 配置解析

1.环境配置

2.properties

3类型别名（typeAliases）

4.映射器

5日志

5.1日志工厂

5.2LOG4J

6分页

7.注解开发

8 lombok

9多对一处理

9.1 按照查询嵌套处理

9.2按照结果嵌套查询

10.一对多处理

11动态sql

if where choose when set

SQL片段

foreach

相关文章

赞助商

阅读排行