简单粗暴JavaWeb-第六篇：使用mybatis框架代替纯jdbc访问数据库

﹏ヽ暗。殇╰゛Y 2023-10-17 16:51 10阅读 0赞

前面说到为了方便，一些sql语句采用了硬编码，但是这种方式在工程逐渐变大的情况下很不方便，我就打算尽量用些变量来替换硬编码。写了两个方法来产生insert语句和select语句，如下：

## 1、纯jdbc下的插入、选择语句 ##

public String genInsertSql(String table, Map<String,String> maps){
        StringBuilder keys = new StringBuilder();
        StringBuilder values = new StringBuilder();
        for (String key:maps.keySet()){
            keys.append(key+",");
            values.append("\'"+maps.get(key)+"\'"+",");
        }
        String keyStr = keys.substring(0,keys.length()-1);
        String valueStr = values.substring(0,values.length()-1);
    
        StringBuilder sql = new StringBuilder()
                .append("insert into ")
                .append(table)
                .append(" (")
                .append(keyStr)
                .append(") values (")
                .append(valueStr)
                .append(");");
        return sql.toString();
    }
    public String genSelectSql(String database, Set<String> colSets, Map<String,String> valueMaps){
    
        StringBuilder colSetsStrBuild = new StringBuilder();
        for (String str:colSets){
            System.out.println(str);
            colSetsStrBuild.append(str)
                    .append(",");
        }
        String colSetsStr = colSetsStrBuild.substring(0,colSetsStrBuild.length()-1);
    
        StringBuilder valueMapsStrBuild = new StringBuilder();
        for (String key:valueMaps.keySet()){
            System.out.println(key);
            valueMapsStrBuild.append(key+"=\'"+valueMaps.get(key)+"\' and ");
        }
        String valueMapsStr = valueMapsStrBuild.substring(0,valueMapsStrBuild.length()-5);
    
        StringBuilder sql = new StringBuilder()
                .append("select ")
                .append(colSetsStr)
                .append(" from ")
                .append(database)
                .append(" where ")
                .append(valueMapsStr)
                .append(";");
        return sql.toString();
    }

看起来就很复杂就很头疼，因而我写完之后就打算上mybatis了，所以，以上代码完全不用看。

## 2、配置mybatis ##

### 2.1 在pom中引入mybatis和mybatis-spring包 ###

如下

<dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis-spring</artifactId>
      <version>1.3.0</version>
    </dependency>
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.2.3</version>
    </dependency>

由于之前我们已经引入了jdbc链接mysql的包，就不用引入了。

### 2.2 配置mybatis-config.xml文件 ###

上一步配置maven依赖，更新后会惊奇的发现，在resource文件夹下已经帮我们建立了一个mybatis-config.xml文件，是mybatis的配置文件，配置内容如下

<?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/ahellospringmvcdemodb" />
                    <property name="username" value="root" />
                    <property name="password" value="123" />
                </dataSource>
            </environment>
        </environments>
    </configuration>

### 2.3 设置一个数据库model类 ###

在mybatis中，对应一张数据表的列信息，我们可以建立一个相应的model类进行匹配，比如表tbl\_user\_info

![Center][]

那么我们建立一个相应的model类，叫做UserInfo

![Center 1][]

代码如下

package HelloSpringMvc.model;
    
    /**
     * Created by yuhan.shen on 2017/11/3.
     */
    public class UserInfo {
    
        private Integer id;
    
        private String userName;
    
        private String password;
    
        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;
        }
    }

其实就是一个pojo方法

### 2.4 我们配置一下mapper方法，并编写sql语句 ###

最能体现mybatis便捷性的地方就是这里！

我们可以在之前自动建立的mybatis文件夹中建立一个mapper文件夹，用来放我们要写的mapper文件

![Center 2][]

内容如下：

<?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="UserInfoMapper">
        <resultMap id="UserInfoMap" type="HelloSpringMvc.model.UserInfo">
            <result column="id" property="id" jdbcType="BIGINT" />
            <result column="user_name" property="userName" jdbcType="VARCHAR" />
            <result column="id" property="id" jdbcType="VARCHAR" />
        </resultMap>
    
    
        <select id="selectUserByID" resultMap="UserInfoMap">
            select * from tbl_user_info where id = '1'
        </select>
    </mapper>

重点是一定要将resultMap与type对应起来，最后，我们将此mapper在mybatis-config.xml进行一下声明，在mybatis-config.xml文件中加上以下代码

经过以上几个步骤，我们就可以进行一下测试了，写一个测试类，如下

import HelloSpringMvc.model.UserInfo;
    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.IOException;
    import java.io.Reader;
    
    /**
     * Created by yuhan.shen on 2017/11/3.
     */
    public class TestUserInfo {
    
        /**
         * SqlSessionFactory是用来创建SqlSession的工厂，SqlSession会执行映射的语句，进行事物提交、回滚等。
         * @return
         */
        public SqlSessionFactory getSqlSessionFactory() {
            Reader reader = null;
            SqlSessionFactory sqlSessionFactory = null;
            try {
                reader = Resources.getResourceAsReader("mybatis/mybatis-config.xml");
                sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
            } catch (IOException e) {
                e.printStackTrace();
            }
            return sqlSessionFactory;
        }
    
        @Test
        public void selectTest(){
            SqlSession session = getSqlSessionFactory().openSession();
            try {
                UserInfo userInfo = (UserInfo) session .selectOne("UserInfoMapper.selectUserByID");
                System.out.println(userInfo.getPassword());
            } finally {
                session.close();
            }
        }
    }

执行Test单元测试，就能得到结果。

其实，我们可以将session相关方法封装起来，使得自己要写的持久层代码（dao）层进一步简化，这种方式比jdbc简单很多，最重要的是不用去拼装sql啊！

[Center]: https://img-blog.csdn.net/20171116211914800?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQveWhhbl9zaGVu/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 1]: https://img-blog.csdn.net/20171116211940431?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQveWhhbl9zaGVu/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 2]: https://img-blog.csdn.net/20171116212809391?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQveWhhbl9zaGVu/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center