测试之家

通用技术 [mybatis] 自学 mybatis 之单表查询

bauul · 2017年08月29日 · 最后由 围城 回复于 2017年08月30日 · 1816 次阅读

前言

spring+mybatis 是 java 常见 web 框架，由于工作中有用到，但以前没接触过 mybatis，所以学习一下这个部分

本文目标

连接库表并完成单表查询

库表信息

本文使用 mysql 数据库
表如下：
SysConfigID | keyName | keyValue | Comments
24 | myKey | myValue | myComment

新建一个 maven 工程

pom 依赖

<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.carl</groupId>
    <artifactId>spring_mybatis</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>spring_mybatis</name>
    <url>http://maven.apache.org</url>

    <properties>
        <slf4j.version>1.7.21</slf4j.version>
        <logback.version>1.1.7</logback.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.5</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>3.8.1</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.43</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>${slf4j.version}</version>
        </dependency>
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
            <version>${logback.version}</version>
        </dependency>
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-core</artifactId>
            <version>${logback.version}</version>
        </dependency>
    </dependencies>

</project>

新建查表接口

package com.carl.data.config;

import com.carl.beans.SAT_SysConfig;

public interface SysConfig {

    public SAT_SysConfig getSysConfig(int SysConfigID);
    public List<SAT_SysConfig> getSysConfigList(int SysConfigID);
}

增加表字段类

package com.carl.beans;

public class SAT_SysConfig {

    private int SysConfigID;
    private String KeyName;
    private String KeyValue;
    private String Comments;

    public int getSysConfigID() {
        return SysConfigID;
    }
    public void setSysConfigID(int sysConfigID) {
        SysConfigID = sysConfigID;
    }
    public String getKeyName() {
        return KeyName;
    }
    public void setKeyName(String keyName) {
        KeyName = keyName;
    }
    public String getKeyValue() {
        return KeyValue;
    }
    public void setKeyValue(String keyValue) {
        KeyValue = keyValue;
    }
    public String getComments() {
        return Comments;
    }
    public void setComments(String comments) {
        Comments = comments;
    }

}

数据源配置

配置数据库连接信息并增加单表映射 mapper

<?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/dev"/>
        <property name="username" value="admin"/>
        <property name="password" value="password"/>
      </dataSource>
    </environment>
  </environments>
  <mappers>
    <mapper resource="com/carl/xml/sysconfigmapper.xml"/>
  </mappers>
</configuration>

表映射配置

<?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.carl.data.config.SysConfig">
    <select id="getSysConfig" parameterType="int" resultType="com.carl.beans.SAT_SysConfig">
        <!-- select SysConfigID,KeyName,KeyValue,Comments from SAT_SysConfig where SysConfigID = #{SysConfigID} -->
        select * from SAT_SysConfig where SysConfigID = #{SysConfigID}
    </select>

    <resultMap type="com.carl.beans.SAT_SysConfig" id="getSysConfitResultMap">
        <id column="SysConfigID" property="sysConfigID"/><!-- 不区分大小写 -->
        <result column="KeyName" property="KeyName"/>
        <result column="KeyValue" property="KeyValue"/>
        <result column="Comments" property="Comments"/>

    </resultMap>

    <select id="getSysConfigList" parameterType="int" resultMap="getSysConfitResultMap">
        select * from SAT_SysConfig where SysConfigID like concat(concat('%',#{SysConfigID}),'%')
    </select>
</mapper>

数据库连接初始化并测试

package com.carl.spring_mybatis;

import java.io.IOException;
import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class DataConnection {

    private static String resource = "com/carl/xml/dataconfig.xml";
    private static SqlSessionFactory sqlSessionFactory = null;
    private static SqlSession sqlSession = null;
    private DataConnection() {

    }

    public static SqlSession getSession() {
        InputStream inputStream;

        try {
            if (sqlSessionFactory == null) {
                inputStream = Resources.getResourceAsStream(resource);
                sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            }
            if (sqlSession == null) {
                sqlSession = sqlSessionFactory.openSession();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return sqlSession;
    }
}

测试类

package com.carl.spring_mybatis;

import com.carl.beans.SAT_SysConfig;
import com.carl.data.config.SysConfig;

public class SysConfigQuery {

    public static void main(String[] args) {
            SAT_SysConfig sysConfig = DataConnection.getSession().getMapper(SysConfig.class).getSysConfig(24);
            System.out.println(sysConfig.getSysConfigID());

            List<SAT_SysConfig> sysConfigList = DataConnection.getSession().getMapper(SysConfig.class).getSysConfigList(24);
//          System.out.println(sysConfigList.size());
            System.out.println(sysConfigList.get(0).getSysConfigID());
            System.out.println(sysConfigList.get(1).getSysConfigID());
    }
}

结果

19:35:44.268 [main] DEBUG org.apache.ibatis.logging.LogFactory - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
19:35:44.439 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.
19:35:44.440 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.
19:35:44.440 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.
19:35:44.440 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.
19:35:44.547 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Opening JDBC Connection
19:35:44.796 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - Created connection 817406040.
19:35:44.797 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@30b8a058]
19:35:44.799 [main] DEBUG com.carl.data.config.SysConfig.getSysConfig - ==>  Preparing: select * from SAT_SysConfig where SysConfigID = ? 
19:35:44.860 [main] DEBUG com.carl.data.config.SysConfig.getSysConfig - ==> Parameters: 24(Integer)
19:35:44.882 [main] DEBUG com.carl.data.config.SysConfig.getSysConfig - <==      Total: 1
24
19:35:44.883 [main] DEBUG com.carl.data.config.SysConfig.getSysConfigList - ==>  Preparing: select * from SAT_SysConfig where SysConfigID like concat(concat('%',?),'%') 
19:35:44.884 [main] DEBUG com.carl.data.config.SysConfig.getSysConfigList - ==> Parameters: 24(Integer)
19:35:44.886 [main] DEBUG com.carl.data.config.SysConfig.getSysConfigList - <==      Total: 2
24
124

参考

深入浅出 mybatis 技术原理与实战

如果觉得我的文章对您有用，请随意打赏。您的支持将鼓励我继续创作！
打赏支持
共收到 4 条回复 时间 点赞
bauul #1 · 2017年08月29日 Author

增加匹配查询，增加接口方法获取列表

围城 #2 · 2017年08月30日

这书咋样？不会就是翻译下官方得 api 吧

bauul #3 · 2017年08月30日 Author
对 围城 回复

挺好的啊，我暂时没看官方的 API，目标就是先学会用

围城 #4 · 2017年08月30日
对 bauul 回复

那我也去买本看看

需要 登录 后方可回复, 如果你还没有账号请点击这里 注册。