基础框架 MyBatis持久层框架增删改查

小鱼儿 2022-12-30 03:51 169阅读 0赞

## 基础框架 MyBatis持久层框架增删改查 ##

### 一、概念 ###

市场上常见的ORM框架很多Hibernate，Struts，mybatis等，各有利弊，目前mybatis使用比较广泛。

ORM框架  
Object Relational Mappging 对象关系映射。  
简单的说，就是把数据库表和实体类及实体类的属性对应起来，让我们可以操作实体类就实现操作数据库表。

这里只是简单认识感受一下mybatis的效果，与传统jdbc两种实现方式的对比，相比于jdbc方式硬编码，xml方式解耦了sql语句与业务逻辑代码，其实也可以理解为mybatis是对jdbc的封装。

我理解的mybatis主要干了3件事：  
第一通过sqlSessionFactoryBuild.build构建sqlSession初始化Configuration对象信息，包括数据库连接相关，动态sql相关的配置信息。  
第二就通过配置文件全限定类名+方法反射获取代理对象。  
第三就是对jdbc的封装，包括执行动态sql，封装ResultSet结果集。

当然了这里面也会涉及到缓存的问题，优先取缓存嘛，具体细节日后源码分析详解。

mybatis官方文档介绍很详细，我在这里只是mybatis简单增删改查。  
[mybatis官方文档][mybatis]

### 二、案例 ###

mybatis操作数据库的两种方式（配置+注解）与jdbc方式

CREATE TABLE `book` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `bookname` varchar(256) NOT NULL COMMENT '书名',
      `author` varchar(256) DEFAULT NULL COMMENT '作者',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8;
    
    INSERT INTO `test_db`.`book`(`id`, `bookname`, `author`) VALUES (1, '雪国', '村上');
    INSERT INTO `test_db`.`book`(`id`, `bookname`, `author`) VALUES (2, '围城', '钱钟书');
    
    CREATE TABLE `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `username` varchar(32) NOT NULL COMMENT '用户名称',
      `birthday` datetime DEFAULT NULL COMMENT '生日',
      `sex` char(1) DEFAULT NULL COMMENT '性别',
      `address` varchar(256) DEFAULT NULL COMMENT '地址',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8;
    
    INSERT INTO `test_db`.`user`(`id`, `username`, `birthday`, `sex`, `address`) VALUES (41, '张三', '2018-02-27 17:47:08', '男', '上海');
    INSERT INTO `test_db`.`user`(`id`, `username`, `birthday`, `sex`, `address`) VALUES (42, '李四', '2018-03-02 15:09:37', '女', '深圳');
    INSERT INTO `test_db`.`user`(`id`, `username`, `birthday`, `sex`, `address`) VALUES (43, '王二', '2018-03-04 11:34:34', '女', '南京');
    INSERT INTO `test_db`.`user`(`id`, `username`, `birthday`, `sex`, `address`) VALUES (45, '赵六', '2018-03-04 12:04:06', '男', '西安');
    INSERT INTO `test_db`.`user`(`id`, `username`, `birthday`, `sex`, `address`) VALUES (46, '老王', '2018-03-07 17:37:26', '男', '北京');
    INSERT INTO `test_db`.`user`(`id`, `username`, `birthday`, `sex`, `address`) VALUES (48, '田七', '2018-03-08 11:44:00', '女', '拉萨');

<?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.mybatis</groupId>
        <artifactId>mybatis01</artifactId>
        <version>1.0-SNAPSHOT</version>
        <packaging>jar</packaging>
    
        <dependencies>
            <dependency>
                <groupId>org.mybatis</groupId>
                <artifactId>mybatis</artifactId>
                <version>3.4.5</version>
            </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.6</version>
            </dependency>
            <dependency>
                <groupId>log4j</groupId>
                <artifactId>log4j</artifactId>
                <version>1.2.12</version>
            </dependency>
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>4.13</version>
            </dependency>
    
        </dependencies>
    
    </project>

package com.mybatis.domain;
    
    import java.io.Serializable;
    
    /** * @author zrj * @date 2020/12/23 * @since V1.0 **/
    public class Book implements Serializable { 
        private Integer id;
        private String bookname;
        private String author;
    
        public Book() { 
        }
    
        public Book(Integer id, String bookname, String author) { 
            this.id = id;
            this.bookname = bookname;
            this.author = author;
        }
    
        public Integer getId() { 
            return id;
        }
    
        public void setId(Integer id) { 
            this.id = id;
        }
    
        public String getBookname() { 
            return bookname;
        }
    
        public void setBookname(String bookname) { 
            this.bookname = bookname;
        }
    
        public String getAuthor() { 
            return author;
        }
    
        public void setAuthor(String author) { 
            this.author = author;
        }
    
        @Override
        public String toString() { 
            return "Book{" +
                    "id=" + id +
                    ", bookname='" + bookname + '\'' +
                    ", author='" + author + '\'' +
                    '}';
        }
    }

package com.mybatis.domain;
    
    import java.io.Serializable;
    import java.util.Date;
    
    /** * 用户实体类 * * @author zrj * @date 2020/12/23 * @since V1.0 **/
    public class User implements Serializable { 
        private Integer id;
        private String username;
        private Date birthday;
        private String sex;
        private String address;
    
        public User() { 
        }
    
        public User(Integer id, String username, Date birthday, String sex, String address) { 
            this.id = id;
            this.username = username;
            this.birthday = birthday;
            this.sex = sex;
            this.address = address;
        }
    
        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 Date getBirthday() { 
            return birthday;
        }
    
        public void setBirthday(Date birthday) { 
            this.birthday = birthday;
        }
    
        public String getSex() { 
            return sex;
        }
    
        public void setSex(String sex) { 
            this.sex = sex;
        }
    
        public String getAddress() { 
            return address;
        }
    
        public void setAddress(String address) { 
            this.address = address;
        }
    
        @Override
        public String toString() { 
            return "User{" +
                    "id=" + id +
                    ", username='" + username + '\'' +
                    ", birthday=" + birthday +
                    ", sex='" + sex + '\'' +
                    ", address='" + address + '\'' +
                    '}';
        }
    }

package com.mybatis.dao;
    
    import com.mybatis.domain.Book;
    import com.mybatis.domain.User;
    import org.apache.ibatis.annotations.Select;
    
    import java.util.List;
    
    /** * 书持久层接口 * * @author zrj * @date 2020/12/23 * @since V1.0 **/
    public interface BookDao { 
        /** * 查询所有操作 * * @return */
        @Select("select * from book")
        List<Book> findAll();
    }

package com.mybatis.dao;
    
    import com.mybatis.domain.User;
    
    import java.util.List;
    
    /** * 用户持久层接口 * * @author zrj * @date 2020/12/23 * @since V1.0 **/
    public interface UserDao { 
        /** * 查询所有操作 * * @return */
        List<User> findAll();
    
        /** * 保存用户 * * @param user */
        void saveUser(User user);
    
        /** * 更新用户 * * @param user */
        void updateUser(User user);
    
        /** * 根据Id删除用户 * * @param userId */
        void deleteUser(Integer userId);
    
        /** * 根据id查询用户信息 * * @param userId * @return */
        User findById(Integer userId);
    
        /** * 根据名称模糊查询用户信息 * * @param username * @return */
        List<User> findByName(String username);
    
        /** * 查询总用户数 * * @return */
        int findTotal();
    
        /** * 根据queryVo中的条件查询用户 * * @param vo * @return */
        List<User> findUserByVo(User user);
    }

mybatis\\mybatis01\\src\\main\\resources\\SqlMapConfig.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="mysql">
            
            <environment id="mysql">
                
                <transactionManager type="JDBC"></transactionManager>
                
                <dataSource type="POOLED">
                    
                    <property name="driver" value="com.mysql.jdbc.Driver"/>
                    <property name="url" value="jdbc:mysql://localhost:3306/test_db"/>
                    <property name="username" value="root"/>
                    <property name="password" value="123456"/>
                </dataSource>
            </environment>
        </environments>
    
        
    
        
        <mappers>
            <mapper resource="com/mybatis/dao/UserDao.xml"/>
        </mappers>
    
        
        
        
        
    </configuration>

mybatis\\mybatis01\\src\\main\\resources\\com\\mybatis\\dao\\UserDao.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.mybatis.dao.UserDao">
    
        
        <resultMap id="userMap" type="com.mybatis.domain.User">
            
            <id property="id" column="id"></id>
            
            <result property="username" column="username"></result>
            <result property="address" column="address"></result>
            <result property="sex" column="sex"></result>
            <result property="birthday" column="birthday"></result>
        </resultMap>
    
        
        <select id="findAll" resultType="com.mybatis.domain.User">
            select * from user
        </select>
    
        
        <insert id="saveUser" parameterType="com.mybatis.domain.User">
            
            <selectKey keyProperty="id" keyColumn="id" resultType="int" order="AFTER">
                select last_insert_id();
            </selectKey>
            INSERT INTO `user`(`username`, `birthday`, `sex`, `address`) VALUES (#{username}, #{birthday}, #{sex},
            #{address});
        </insert>
    
        
        <update id="updateUser" parameterType="com.mybatis.domain.User">
            update user set username=#{username},address=#{address},sex=#{sex},birthday=#{birthday} where id=#{id}
        </update>
    
        
        <delete id="deleteUser" parameterType="java.lang.Integer">
            delete from user where id = #{id}
        </delete>
    
        
        <select id="findById" parameterType="INT" resultMap="userMap">
            select * from user where id = #{id}
        </select>
    
        
        <select id="findByName" parameterType="string" resultMap="userMap">
            
             select * from user where username like '%${value}%'
        </select>
    
        
        <select id="findTotal" resultType="int">
            select count(id) from user;
        </select>
    
        
        <select id="findUserByVo" parameterType="com.mybatis.domain.User" resultMap="userMap">
            select * from user where username like #{user.username}
        </select>
    
    </mapper>

mybatis\\mybatis01\\src\\main\\resources\\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
    # 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

测试类

package com.mybatis.test;
    
    import com.mybatis.dao.BookDao;
    import com.mybatis.dao.UserDao;
    import com.mybatis.domain.Book;
    import com.mybatis.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.After;
    import org.junit.Before;
    import org.junit.Test;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.sql.*;
    import java.util.Date;
    import java.util.List;
    
    /** * myBatis测试类 * * @author zrj * @date 2020/12/23 * @since V1.0 **/
    public class MyBatisTest { 
    
        /** * jdbc测试 */
        @Test
        public void jdbcTest() { 
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
    
            try { 
                //加载数据库驱动
                Class.forName( "com.mysql.jdbc.Driver" );
                //通过驱动管理类获取数据库链接
                connection = DriverManager.getConnection( "jdbc:mysql://localhost:3306/test_db?characterEncoding=utf-8", "root", "123456" );
                //定义 sql 语句 ?表示占位符
                String sql = "select * from user where username = ?";
                //获取预处理 statement
                preparedStatement = connection.prepareStatement( sql );
                //设置参数，第一个参数为 sql 语句中参数的序号（从 1 开始），第二个参数为设置的参数值
                preparedStatement.setString( 1, "老王" );
                //向数据库发出 sql 执行查询，查询出结果集
                resultSet = preparedStatement.executeQuery();
                //遍历查询结果集
                while (resultSet.next()) { 
                    System.out.println( resultSet.getString( "id" ) + resultSet.getString( "username" ) );
                }
    
            } catch (Exception e) { 
                e.printStackTrace();
            } finally { 
                //释放资源
                if (resultSet != null) { 
                    try { 
                        resultSet.close();
                    } catch (SQLException e) { 
                        resultSet = null;
                        e.printStackTrace();
                    }
                }
    
                if (preparedStatement != null) { 
                    try { 
                        preparedStatement.close();
                    } catch (SQLException e) { 
                        preparedStatement = null;
                        e.printStackTrace();
                    }
                }
    
                if (connection != null) { 
                    try { 
                        connection.close();
                    } catch (SQLException e) { 
                        connection = null;
                        e.printStackTrace();
                    }
                }
            }
        }
    
        /** * mybatis测试 * 基于注解,查询操作 * * @throws IOException */
        @Test
        public void myBatisAnnoFindAllTest() throws IOException { 
            // 1.读取配置文件
            InputStream in = Resources.getResourceAsStream( "SqlMapConfig.xml" );
    
            // 2.创建SqlSessionFactory工厂
            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
            SqlSessionFactory factory = builder.build( in );
    
            // 3.使用工厂生产SQLSession对象
            SqlSession session = factory.openSession();
    
            // 4.使用SQLSession创建Dao接口的代理对象
            BookDao bookDao = session.getMapper( BookDao.class );
    
            // 5.使用代理对象执行方法
            List<Book> bookList = bookDao.findAll();
            System.out.println( "-----------mybatis查询---------" );
            bookList.forEach( System.out::println );
    
            // 6.释放资源
            session.close();
            in.close();
        }
    
        /** * 初始化配置信息 */
        private InputStream in;
        private SqlSession session;
        private UserDao userDao;
    
        /** * 测试方法之前执行 * * @throws IOException */
        @Before
        public void init() throws IOException { 
            // 1.读取配置文件
            in = Resources.getResourceAsStream( "SqlMapConfig.xml" );
    
            // 2.创建SqlSessionFactory工厂
            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
            SqlSessionFactory factory = builder.build( in );
    
            // 3.使用工厂生产SQLSession对象
            session = factory.openSession();
    
            // 4.使用SQLSession创建Dao接口的代理对象
            userDao = session.getMapper( UserDao.class );
        }
    
        /** * 测试方法之后执行 * * @throws IOException */
        @After
        public void destory() throws IOException { 
            // 手动提交事务
            session.commit();
    
            // 6.释放资源
            session.close();
            in.close();
        }
    
        /** * mybatis测试 * 基于配置文件,查询操作 */
        @Test
        public void myBatisFindAllTest() { 
            // 5.使用代理对象执行方法
            List<User> userList = userDao.findAll();
            userList.forEach( System.out::println );
            System.out.println( "查询成功" );
        }
    
        /** * mybatis测试 * 基于配置文件,保存操作 */
        @Test
        public void myBatisSaveUserTest() { 
            // 执行保存
            User user = new User( 1, "777", new Date(), "1", "beiji" );
            userDao.saveUser( user );
            // 主要看返回值id
            System.out.println( "保存成功:" + user );
        }
    
        /** * 测试更新操作 */
        @Test
        public void testUpdate() { 
            //5.执行保存方法
            userDao.updateUser( new User( 50, "666", new Date(), "1", "helan" ) );
            System.out.println( "测试更新操作成功" );
        }
    
        /** * 测试删除操作 */
        @Test
        public void testDelete() { 
            //5.执行删除方法
            userDao.deleteUser( 50 );
            System.out.println( "测试删除操作成功" );
        }
    
        /** * 测试删除操作 */
        @Test
        public void testFindOne() { 
            //5.执行查询一个方法
            User user = userDao.findById( 51 );
            System.out.println( "根据id查找：" + user );
        }
    
        /** * 测试模糊查询操作 */
        @Test
        public void testFindByName() { 
            //5.执行查询一个方法
            // 拼接字符串方式，不常用
            //List<User> users = userDao.findByName( "%6%" );
            // 占位符，预处理方式更好些
            List<User> users = userDao.findByName( "6" );
            for (User user : users) { 
                System.out.println( "测试模糊查询操作:" + user );
            }
        }
    
        /** * 测试查询总记录条数 */
        @Test
        public void testFindTotal() { 
            //5.执行查询一个方法
            int count = userDao.findTotal();
            System.out.println( "测试查询总记录条数:" + count );
        }
    
    
        /** * 测试使用QueryVo作为查询条件 */
        @Test
        public void testFindByVo() { 
            User user = new User();
            user.setUsername( "%王%" );
            //5.执行查询一个方法
            List<User> users = userDao.findUserByVo( user );
            for (User u : users) { 
                System.out.println( u );
            }
        }
    
    }

[mybatis]: https://mybatis.org/mybatis-3/zh/index.html