Mybatis实现CRUD操作

迷南。 2021-12-12 00:31 320阅读 0赞

# 项目实现的功能 #

查询所有用户信息  
通过Id查询用户信息  
添加用户（回显主键）  
修改用户信息  
删除用户信息  
通过用户名字模糊查询

# 一、引入依赖和工程结构 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzM5MTc5OTkz_size_16_color_FFFFFF_t_70]

<?xml version="1.0" encoding="UTF-8"?>
    <project xmlns="http://maven.apache.org/POM/4.0.0"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
    
        <groupId>com.william</groupId>
        <artifactId>MybatisCRUD</artifactId>
        <version>1.0-SNAPSHOT</version>
    
        <properties>
            <maven.compiler.source>1.8</maven.compiler.source>
            <maven.compiler.target>1.8</maven.compiler.target>
        </properties>
        <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.4.5</version>
            </dependency>
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>4.12</version>
            </dependency>
            <dependency>
                <groupId>log4j</groupId>
                <artifactId>log4j</artifactId>
                <version>1.2.17</version>
            </dependency>
        </dependencies>
    
    </project>

# 二、创建实体类 #

## User ##

package com.william.domain;
    
    import java.util.Date;
    
    /**
     * @author ：lijunxuan
     * @date ：Created in 2019/7/9  19:28
     * @description ：
     * @version: 1.0
     */
    public class User {
        private  Integer id;
        private String username;
        private String password;
        private String  sex;
        private Date birthday;
    
        @Override
        public String toString() {
            return "User{" +
                    "id=" + id +
                    ", username='" + username + '\'' +
                    ", password='" + password + '\'' +
                    ", sex='" + sex + '\'' +
                    ", birthday=" + birthday +
                    '}';
        }
    
        public Date getBirthday() {
            return birthday;
        }
    
        public void setBirthday(Date birthday) {
            this.birthday = birthday;
        }
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getUsername() {
            return username;
        }
    
        public void setUsername(String username) {
            this.username = username;
        }
    
        public String getPassword() {
            return password;
        }
    
        public void setPassword(String password) {
            this.password = password;
        }
    
        public String getSex() {
            return sex;
        }
    
        public void setSex(String sex) {
            this.sex = sex;
        }
    
    }

# 三、映射文件 #

## 1.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.william.mapper.UserMapper">
        
        <select id="findAll" resultType="com.william.domain.User">
            select * from user
        </select>
        
        <select id="findById" parameterType="java.lang.Integer" resultType="com.william.domain.User">
            select * from user where id=#{id}
        </select>
        
        <select id="findByUsername" parameterType="java.lang.String" resultType="com.william.domain.User">
            select * from user where username like "%"#{value}"%"
        </select>
        
        <insert id="insert" parameterType="com.william.domain.User">
        -- 主键回显
            <selectKey resultType="java.lang.Integer" keyColumn="id" keyProperty="id" order="AFTER" >
                  select last_insert_id()
            </selectKey>
            insert into user values (null ,#{username},#{password},#{sex},#{birthday})
        </insert>
        
        <update id="update" parameterType="com.william.domain.User">
            update  user set username=#{username},password=#{password},sex=#{sex},birthday=#{birthday} where id=#{id}
        </update>
        
        <delete id="delete" parameterType="java.lang.Integer">
            delete  from user where id =#{id}
        </delete>
    </mapper>

## 2.Mybatis-configuration.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.jdbc.Driver"/>
                    <property name="url" value="jdbc:mysql://localhost:3306/web02"/>
                    <property name="username" value="root"/>
                    <property name="password" value="root"/>
                </dataSource>
            </environment>
        </environments>
        <mappers>
            <mapper resource="com/william/mapper/UserMapper.xml"/>
        </mappers>
    </configuration>

# 四、引入log4j #

## log4j.properties ##

# Set root category priority to INFO and its only appender to CONSOLE.
    #log4j.rootCategory=INFO, CONSOLE            debug   info   warn error fatal
    log4j.rootCategory=debug, CONSOLE, LOGFILE, info
    
    # Set the enterprise logger category to FATAL and its only appender to CONSOLE.
    log4j.logger.org.apache.axis.enterprise=FATAL, CONSOLE
    
    # CONSOLE is set to be a ConsoleAppender using a PatternLayout.
    log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender
    log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout
    log4j.appender.CONSOLE.layout.ConversionPattern=%d{ISO8601} %-6r [%15.15t] %-5p %30.30c %x - %m\n
    
    # LOGFILE is set to be a File appender using a PatternLayout.
    log4j.appender.LOGFILE=org.apache.log4j.FileAppender
    log4j.appender.LOGFILE.File=d:\axis.log
    log4j.appender.LOGFILE.Append=true
    log4j.appender.LOGFILE.layout=org.apache.log4j.PatternLayout
    log4j.appender.LOGFILE.layout.ConversionPattern=%d{ISO8601} %-6r [%15.15t] %-5p %30.30c %x - %m\n

# 五、测试类 #

## TestMybatis ##

package com.william;
    import com.william.domain.User;
    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 org.junit.Test;
    import java.io.InputStream;
    import java.util.Date;
    import java.util.List;
    /**
     * @author ：lijunxuan
     * @date ：Created in 2019/7/9  19:45
     * @description ：
     * @version: 1.0
     */
    public class TestMybatis {
        /**
         * 查询所有用户信息
         */
        @Test
        public void selectList(){
            InputStream inputStream = Resources.class.getClassLoader().getResourceAsStream("Mybatis-configuration.xml");
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession sqlSession = sqlSessionFactory.openSession();
            List<User> selectList = sqlSession.selectList("com.william.mapper.UserMapper.findAll");
            for (Object o : selectList) {
                System.out.println(o);
            }
            sqlSession.close();
        }
    
        /**
         *通过Id查询用户信息
         *
         */
        @Test
        public void findById(){
            InputStream inputStream = Resources.class.getClassLoader().getResourceAsStream("Mybatis-configuration.xml");
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession sqlSession = sqlSessionFactory.openSession();
            User selectOne = sqlSession.selectOne("com.william.mapper.UserMapper.findById", 43);
            System.out.println(selectOne.getUsername());
            sqlSession.close();
        }
        /**
         * 添加用户
         */
        @Test
        public void InsertUser(){
            InputStream inputStream = Resources.class.getClassLoader().getResourceAsStream("Mybatis-configuration.xml");
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession sqlSession = sqlSessionFactory.openSession();
            User user = new User();
            user.setUsername("黑衣人");
            user.setPassword("123456");
            user.setSex("男");
            user.setBirthday(new Date());
            sqlSession.insert("com.william.mapper.UserMapper.insert",user);
            //回显刚刚添加用户的ID
            System.out.println("添加之后的主键id为："+user.getId());
            sqlSession.commit();
            sqlSession.close();
        }
    
        /**
         * 修改用户信息
         */
        @Test
        public void UpdateUser(){
            InputStream inputStream = Resources.class.getClassLoader().getResourceAsStream("Mybatis-configuration.xml");
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession sqlSession = sqlSessionFactory.openSession();
            User user = new User();
            user.setId(43);
            user.setUsername("黑衣人");
            user.setPassword("123456");
            user.setSex("男");
            user.setBirthday(new Date());
            sqlSession.update("com.william.mapper.UserMapper.update",user);
            sqlSession.commit();
            sqlSession.close();
        }
    
        /**
         * 删除用户信息
         */
        @Test
        public void deleteUser(){
            InputStream inputStream = Resources.class.getClassLoader().getResourceAsStream("Mybatis-configuration.xml");
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession sqlSession = sqlSessionFactory.openSession();
            sqlSession.delete("com.william.mapper.UserMapper.delete",42);
            sqlSession.commit();
            sqlSession.close();
        }
    
        /**
         * 模糊查询
         * 通过用户名字模糊查询
         */
        @Test
        public void findByUsername(){
            InputStream inputStream = Resources.class.getClassLoader().getResourceAsStream("Mybatis-configuration.xml");
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession sqlSession = sqlSessionFactory.openSession();
            List<User> selectList = sqlSession.selectList("com.william.mapper.UserMapper.findByUsername","a");
            for (Object o : selectList) {
                System.out.println(o);
            }
            sqlSession.close();
        }
    }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzM5MTc5OTkz_size_16_color_FFFFFF_t_70]: /images/20211211/c1cd10580c154df4bce726c6ce0b97b8.png