mybatis的应用_CRUD操作+LOG4J日志

db.properties

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/db3?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true
username=root
password=root

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>

    <!--读取jdbc的配置文件-->
    <properties resource="db.properties">
    <!--  在这里可以设置属性，但是idea运行时候会先运行标签体里的，然后在读取resource的配置文件会覆盖掉它-->
    <property name="username" value="root"/>
    <property name="password" value="aaaaaaa"/>
    </properties>
    
    <settings>
        <!--标准的日志工厂,下面内容不能动-->
<!--        <setting name="logImpl" value="STDOUT_LOGGING"/> -->
<!--   普遍用log4j 粘贴时候有空格，删除！-->
        <setting name="logImpl" value="LOG4J"/>
    </settings>
    
    <!--起一个别名，这个包下的类在mapper调用时候不用输入全部的路径，只需要输入类小写就行-->
    <typeAliases>
        <package name="com.itcase.domain"/>
    </typeAliases>
    <!-- 环境可以有很多，但是在哪个环境运行需要设置为默认-->
    <environments default="development">
        <environment id="development">
            <!--事务管理器 JDBC 或者MANAGED -->
            <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>

    <!--每一个Mapper都需要在这里声名 不声明的话报错：Type interface com.itcase.dao.UserDao is not known to the MapperRegistry. -->
    <mappers>
<!-- 报错：Could not find resource com/itcase/dao/UserMapper.xml-->
        <mapper resource="com/itcase/dao/UserMapper.xml"/>
    </mappers>
</configuration>

Util工具类

package com.itcase.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;

//mybatis的配置工具类
public class MybatisUtil {
    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            //使用mybatis创建sqlSessionFactory类
            String resources = "mybatis-config.xml";
            //这里注意要Resources要使用阿帕奇下的包
            InputStream resourceAsStream = Resources.getResourceAsStream(resources);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    //得到sqlSession
    public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession();
    }

}

UserMapper接口

public interface UserMapper {
    //查询所有的user
    public List<user> getList();
    //通过id来获取user
    public user getById(int id);
    //通过name模糊查询user
    public List<user> getByName(String name);
    //插入一个user
    public int insertUser(user user);
    //修改一个user
    public int updateUser(user user);
    //删除一个user
    public int deleteUser(int id);
    //当需要传递的参数多的时候用map 或者注解
    public int mapInsert(Map<String,Object> map);
}

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.UserMapper">
<!-- id就是上面那个接口里的方法名  select标签就相当于实现了这个查询方法  resultType 是返回结果根据方法可以得出来是User-->
    <select id="getList" resultType="com.user">
             select * from db3.user
    </select>
<!--模糊查询这样可以 "%"#{}"%" 直接拼接可以防止sql注入 虽然有红色编译器报错，但是运行是没错的。-->
    <select id="getByName" resultType="com.user" parameterType="String">
        select * from db3.user where name like "%"#{name}"%"
    </select>

<!--parameterType设置传来的参数类型 #{}里面是参数-->
    <select id="getById" resultType="com.user" parameterType="int">
             select * from db3.user where id =#{id}
    </select>
<!-- 插入-->
    <insert id="insertUser"  parameterType="com.user" >
        insert into db3.user values (#{id},#{name},#{psw})
    </insert>
<!-- 使用map插入数据 可以自定义#{}里面的内容就是map的key -->
    <insert id="mapInsert" parameterType="map">
        insert into db3.user values (#{userId},#{userName},#{password})
    </insert>

    <update id="updateUser" parameterType="com.user">
        update db3.user set name = #{name},psw=#{psw} where id=#{id}
    </update>
    <delete id="deleteUser" parameterType="int" >
        delete from db3.user where id = #{id}
    </delete>
</mapper>

Test

public class UserDaoTest {

    public UserDaoTest(){ }
    @Test
    public void test1(){
        //得到sqlSession对象
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        //获取到UserDao的对象
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        //执行sql操作
        List<user> list = mapper.getList();
        for (user user : list) {
            System.out.println(user);
        }
        sqlSession.close();
    }
    //通过id查人
    @Test
    public void getByIdTest(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        user byId = mapper.getById(1);
        System.out.println(byId);
        sqlSession.close();
    }
    //模糊查询
    @Test
    public void getByNameTest(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<user> user = mapper.getByName("王");
        for (user user1 : user) {
            System.out.println(user1);
        }
        sqlSession.close();
    }

    //插入一个user
    @Test
    public void insertTest(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int a = mapper.insertUser(new user(1,"小凯","222"));
        if (a>0){
            System.out.println("插入成功");
        }
        //提交事务  增删改一定要提交事务！！！
        sqlSession.commit();
        sqlSession.close();
    }
    //使用map的方式插入数据
    @Test
    public void insertMap(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        Map<String,Object> map = new HashMap<String,Object>();
        map.put("userId",8);
        map.put("userName","小王");
        map.put("password","222");
        int i = mapper.mapInsert(map);
        if (i>0){
            System.out.println("map插入成功");
        }
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void updateTest(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int a = mapper.updateUser(new user(1, "小开", "222"));
        if (a>0){
            System.out.println("修改成功");
        }
        sqlSession.commit();
        sqlSession.close();
    }
    @Test
    public void deleteTest(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int i = mapper.deleteUser(1);
        if (i>0){
            System.out.println("删除成功");
        }
        sqlSession.commit();
        sqlSession.close();
    }
}

resultMap的使用+分页查找

UserMapper接口下添加一个分页方法

    //分页查询
    public List<user> getLimitPage(Map<String,Integer> map);

UserMapper.xml添加resultMap返回结果集合,property就是定义数据库的列名，使user的属性和数据库列名一致
resultMap就是将查询到的表返回的列名，进行重命名，将结果列名修改成和user的属性一致，返回user对象，输出。

    <resultMap id="UserMap" type="user">
<!--  id是主键 colunm是数据库的列，property是自己重命名的user属性-->
        <id column="id" property="id"/>
        <result column ="name" property="name"/>
        <result column = "psw" property="psw"/>

    <!--分页查询-->
    <select id="getLimitPage" parameterType="map" resultMap="UserMap">
        select * from db3.user limit #{startIndex},#{pageSize}
    </select>

Test

//测试分页
    @Test
    public void testLimit(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        map.put("startIndex",0);
        map.put("pageSize",2);
        List<user> limitPage = mapper.getLimitPage(map);
        for (user user : limitPage) {
            System.out.println(user);
        }
    }

标准日志和LOG4J日志

标准工厂日志
直接在mybatis配置内加即可，setting放在最上方

    <settings>
        <!--标准的日志工厂,下面内容不能动-->
<!--        <setting name="logImpl" value="STDOUT_LOGGING"/> -->
    </settings>

LOG4J日志

添加依赖

        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>

创建一个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/kai.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

mybatis-config.xml内的setting设置

    <settings>
<!--   普遍用log4j 粘贴时候有空格，删除！-->
        <setting name="logImpl" value="LOG4J"/>
    </settings>

log4j的使用

    Logger logger = Logger.getLogger(UserDaoTest.class);
   @Test
    public void testlog4j(){
        logger.info("info:测试了info");
        logger.debug("debug:测试了debug");
    }