MyBatis学习03-前篇之JdbcTemplate

最新推荐文章于 2024-04-04 14:23:15 发布

还有头发的老李

最新推荐文章于 2024-04-04 14:23:15 发布

阅读量828

点赞数

分类专栏： MyBatis 文章标签： java mybatis

本文链接：https://blog.csdn.net/wwww641788662/article/details/106697052

版权

MyBatis 专栏收录该内容

11 篇文章 1 订阅

订阅专栏

MyBatis学习03-前篇之JdbcTemplate

1 前言

使用 JDBC 进行数据库操作需要研发人员对数据库连接、结果集等资源进行管理，对于数据库连接的获取、预加载对象、结果集对象映射转换等内容进行大量的重复性的工作，且使用过程中容易出现资源泄露问题。实际上使用 JDBC 进行数据库操作的模式都是一样的，系统研发过程中，为了节约研发成本，大量研发或者团队选择对 JDBC 进行了一层封装。Spring 作为目前最流行的框架，在2001年5月份就开始了对 JDBC 的封装，该封装模块就是 JdbcTemplate。但在2001的时，Spring 还没有现在这么流行，在2014年时，推出 SpringBoot 后，Spring 开始火起来，JdbcTemplate 也跟着被带了起来。

2 JdbcTemplate

2.1 官网地址

https://docs.spring.io/spring-framework/docs/current/spring-framework-reference/data-access.html#jdbc

2.2 简介

JdbcTemplate 对 JDBC 进行了一些的封装，它简化了JDBC的使用并有助于避免常见错误。使用 JdbcTemplate 可以让程序对于 SQL 的查询、修改、删除、新增变得更为通用。

This is the central class in the JDBC core package.
It simplifies the use of JDBC and helps to avoid common errors.
It executes core JDBC workflow, leaving application code to provide SQL and extract results. This class executes SQL queries or updates, initiating iteration over ResultSets and catching JDBC exceptions and translating them to the generic

2.3 示例

运行环境：Win10 + Jdk1.8 + Maven3 + Intellij IDEA 2020 + mysql5.7

1、示例代码地址

https://gitee.com/lif/study-mybatis/tree/master

2、目录结构如下

在这里插入图片描述

3、pom.xml 文件引入依赖包

<?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.ddpyjqtd</groupId>
  <artifactId>simple-jdbctemplate</artifactId>
  <version>1.0-SNAPSHOT</version>

  <name>simple-jdbctemplate</name>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.8</maven.compiler.source>
    <maven.compiler.target>1.8</maven.compiler.target>
  </properties>

  <dependencies>
    <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.10</version>
      <scope>provided</scope>
    </dependency>

    <!-- https://mvnrepository.com/artifact/commons-dbutils/commons-dbutils -->
    <dependency>
      <groupId>commons-dbutils</groupId>
      <artifactId>commons-dbutils</artifactId>
      <version>1.7</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.20</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.slf4j/slf4j-api -->
    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-api</artifactId>
      <version>1.7.30</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.apache.logging.log4j/log4j-slf4j-impl -->
    <dependency>
      <groupId>org.apache.logging.log4j</groupId>
      <artifactId>log4j-slf4j-impl</artifactId>
      <version>2.13.3</version>
      <scope>test</scope>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.apache.logging.log4j/log4j-api -->
    <dependency>
      <groupId>org.apache.logging.log4j</groupId>
      <artifactId>log4j-api</artifactId>
      <version>2.13.3</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.apache.logging.log4j/log4j-core -->
    <dependency>
      <groupId>org.apache.logging.log4j</groupId>
      <artifactId>log4j-core</artifactId>
      <version>2.13.3</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>5.2.6.RELEASE</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.springframework/spring-beans -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-beans</artifactId>
      <version>5.2.6.RELEASE</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.springframework/spring-core -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-core</artifactId>
      <version>5.2.6.RELEASE</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.springframework/spring-tx -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-tx</artifactId>
      <version>5.2.6.RELEASE</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>5.2.6.RELEASE</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/com.mchange/c3p0 -->
    <dependency>
      <groupId>com.mchange</groupId>
      <artifactId>c3p0</artifactId>
      <version>0.9.5.5</version>
    </dependency>

    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
  </dependencies>

</project>

4、增加 model 类： Demo.java

package com.ddpyjqtd.demo.jdbc.model;

import lombok.Getter;
import lombok.Setter;
import lombok.ToString;

import java.util.Date;

/***
 * 测试表对应的demo类
 *
 * @author ddpyjqtd
 * @date 2020/5/27 21:54
 */
@Getter
@Setter
@ToString
public class Demo {
    /**
     * int 类型字段
     */
    private int demoInt;
    /**
     * String 类型字段
     */
    private String demoVarchar;
    /**
     * text 类型字段
     */
    private String demoText;
    /**
     * datetime 类型字段
     */
    private Date demoDateTime;
}

5、增加数据库配置文件：db.properties，内容如下：

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
jdbc.username=root
jdbc.password=root

6、增加日志配置文件 log4j2.xml，内容如下：

<?xml version="1.0" encoding="UTF-8"?>
<!--官方文档：https://logging.apache.org/log4j/2.x/manual/appenders.html-->
<configuration status="OFF">
    <Properties>
        <!-- 只需要输入日志文件路径与当前项目名称 -->
        <property name="file_path">d://logs</property>
        <property name="project_name">simple-jdbctemplate</property>

        <property name="file_name">${file_path}/${project_name}.log</property>
        <property name="file_sql_name">${file_path}/${project_name}-sql.log</property>
        <property name="file_error">${file_path}/${project_name}-error.log</property>
        <property name="log_pattern">%d{yyyy-MM-dd HH:mm:ss.SSS} %-5p %c{36} %L %M - %m%n</property>
    </Properties>
    <appenders>
        <!-- 控制台输出 -->
        <Console name="Console" target="SYSTEM_OUT">
            <ThresholdFilter level="debug" onMatch="ACCEPT" onMismatch="DENY"/>
            <PatternLayout pattern="${log_pattern}"/>
        </Console>
        <!-- 业务输出 -->
        <File name="default_log" fileName="${file_name}" append="false">
            <ThresholdFilter level="info" onMatch="ACCEPT" onMismatch="DENY"/>
            <PatternLayout pattern="${log_pattern}"/>
        </File>
        <!-- error 级别日志输出 -->
        <File name="error_log" fileName="${file_error}" append="false">
            <PatternLayout pattern="${log_pattern}"/>
            <ThresholdFilter level="error" onMatch="ACCEPT" onMismatch="DENY"/>
        </File>
    </appenders>
    <loggers>
        <Logger name="org" level="info" additivity="true"/>
        <Logger name="com" level="info" additivity="true"/>

        <!--生产环境需要注释掉控制台配置-->
        <Root level="debug" includeLocation="true">
            <AppenderRef ref="Console"/>
            <AppenderRef ref="default_log"/>
            <AppenderRef ref="error_log"/>
        </Root>
    </loggers>
</configuration>

7、增加 Spring 配置文件 applicationContext.xml，内容如下：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
                 http://www.springframework.org/schema/beans/spring-beans.xsd
                 http://www.springframework.org/schema/context
                 http://www.springframework.org/schema/context/spring-context.xsd">

    <context:property-placeholder location="db.properties"/>
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="user" value="${jdbc.username}"></property>
        <property name="password" value="${jdbc.password}"></property>
        <property name="driverClass" value="${jdbc.driver}"></property>
        <property name="jdbcUrl" value="${jdbc.url}"></property>
    </bean>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

    <bean id="demoDao" class="com.ddpyjqtd.demo.dao.DemoDao">
        <constructor-arg ref="jdbcTemplate"></constructor-arg>

    </bean>
</beans>

8、增加数据库配置文件读取工具类：DbUtils.java，内容如下：

package com.ddpyjqtd.demo.utils;

import lombok.extern.slf4j.Slf4j;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ResourceBundle;

/**
 * 数据库工具类
 *
 * @author: ddpyjqtd
 * @date: 2020/5/27 22:10
 */
@Slf4j
public class DbUtils {
    private DbUtils() {
    }

    public static final String DRIVER;
    public static final String URL;
    public static final String USER_NAME;
    public static final String PASS_WORD;

    static {
        ResourceBundle bundle = ResourceBundle.getBundle("db");
        DRIVER = bundle.getString("driver");
        URL = bundle.getString("url");
        USER_NAME = bundle.getString("username");
        PASS_WORD = bundle.getString("password");
    }

    /**
     * 获取数据库连接
     *
     * @return java.sql.Connection
     * @author ddpyjqtd
     * @date 2020/5/28 23:36
     */
    public static Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName(DRIVER);
            conn = DriverManager.getConnection(URL, USER_NAME, PASS_WORD);
        } catch (ClassNotFoundException | SQLException e) {
            log.error("getConnection error : ", e);
        }
        return conn;
    }

}

9、编写数据库操作类：DemoDao.java，内容如下：

package com.ddpyjqtd.demo.dao;

import com.ddpyjqtd.demo.model.Demo;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

/**
 * 使用 jdbctemplate方式操作数据库
 *
 * @author: ddpyjqtd
 * @date: 2020/5/29 22:10
 */
@Repository
@Slf4j
public class DemoDao {

    private final JdbcTemplate jdbcTemplate;

    public DemoDao(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * 通过主键查询 demo 对象信息
     *
     * @param demoInt 主键ID
     * @return com.ddpyjqtd.demo.model.Demo
     * @author ddpyjqtd
     * @date 2020/5/29 22:44
     */
    public Demo findDemoByKey(int demoInt) {
        Demo demo = jdbcTemplate.queryForObject("select * from demo where demo_int = ?",
                new BeanPropertyRowMapper<Demo>(Demo.class), demoInt);
        log.info("demo info is : {}", demo.toString());
        return demo;
    }
}

10、执行 SQL

CREATE TABLE demo  (
  demo_int int(255) NOT NULL COMMENT 'int 类型字段',
  demo_varchar varchar(255)  NULL DEFAULT NULL COMMENT 'String 类型字段',
  demo_text text  NULL COMMENT 'text 类型字段',
  demo_date_time datetime(0) NULL DEFAULT NULL COMMENT 'datetime 类型字段',
  PRIMARY KEY (demo_int) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '示例表，各个字段为数据库的各种类型' ROW_FORMAT = Dynamic;


insert into demo (demo_int, demo_varchar, demo_text, demo_date_time)
VALUES (1, 'this is demo_varchar', 'this is demo_text', now());

11、编写测试类：TestDemoDao.java，内容如下：

package com.ddpyjqtd.demo.dao;

import com.ddpyjqtd.demo.model.Demo;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

/**
 * 测试类
 *
 * @author: ddpyjqtd
 * @date: 2020/5/29 22:45
 */
public class TestDemoDao {

    private ApplicationContext applicationContext;

    /**
     * 测试前方法
     *
     * @author ddpyjqtd
     * @date 2020/5/29 22:47
     */
    @Before
    public void before() {
        applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
    }

    /**
     * 测试通过主键查询 demo 对象方法
     *
     * @author ddpyjqtd
     * @date 2020/5/29 22:46
     */
    @Test
    public void testFindDemoByKey() {
        DemoDao dao = (DemoDao) applicationContext.getBean("demoDao");
        Demo demo = dao.findDemoByKey(1);
        Assert.assertNotNull(demo);
    }
}

12、运行测试类，执行结果如下：

C:\soft\java\jdk1.8.0_191\bin\java.exe -ea -Didea.test.cyclic.buffer.size=1048576 "-javaagent:D:\develop\IntelliJ IDEA 2020.1\lib\idea_rt.jar=50089:D:\develop\IntelliJ IDEA 2020.1\bin" -Dfile.encoding=UTF-8 -classpath "D:\develop\IntelliJ IDEA 2020.1\lib\idea_rt.jar;D:\develop\IntelliJ IDEA 2020.1\plugins\junit\lib\junit5-rt.jar;D:\develop\IntelliJ IDEA 2020.1\plugins\junit\lib\junit-rt.jar;C:\soft\java\jdk1.8.0_191\jre\lib\charsets.jar;C:\soft\java\jdk1.8.0_191\jre\lib\deploy.jar;C:\soft\java\jdk1.8.0_191\jre\lib\ext\access-bridge-64.jar;C:\soft\java\jdk1.8.0_191\jre\lib\ext\cldrdata.jar;C:\soft\java\jdk1.8.0_191\jre\lib\ext\dnsns.jar;C:\soft\java\jdk1.8.0_191\jre\lib\ext\jaccess.jar;C:\soft\java\jdk1.8.0_191\jre\lib\ext\jfxrt.jar;C:\soft\java\jdk1.8.0_191\jre\lib\ext\localedata.jar;C:\soft\java\jdk1.8.0_191\jre\lib\ext\nashorn.jar;C:\soft\java\jdk1.8.0_191\jre\lib\ext\sunec.jar;C:\soft\java\jdk1.8.0_191\jre\lib\ext\sunjce_provider.jar;C:\soft\java\jdk1.8.0_191\jre\lib\ext\sunmscapi.jar;C:\soft\java\jdk1.8.0_191\jre\lib\ext\sunpkcs11.jar;C:\soft\java\jdk1.8.0_191\jre\lib\ext\zipfs.jar;C:\soft\java\jdk1.8.0_191\jre\lib\javaws.jar;C:\soft\java\jdk1.8.0_191\jre\lib\jce.jar;C:\soft\java\jdk1.8.0_191\jre\lib\jfr.jar;C:\soft\java\jdk1.8.0_191\jre\lib\jfxswt.jar;C:\soft\java\jdk1.8.0_191\jre\lib\jsse.jar;C:\soft\java\jdk1.8.0_191\jre\lib\management-agent.jar;C:\soft\java\jdk1.8.0_191\jre\lib\plugin.jar;C:\soft\java\jdk1.8.0_191\jre\lib\resources.jar;C:\soft\java\jdk1.8.0_191\jre\lib\rt.jar;D:\develop\z_code\study-mybatis\simple-jdbctemplate\target\test-classes;D:\develop\z_code\study-mybatis\simple-jdbctemplate\target\classes;D:\develop\z_maven_package\org\projectlombok\lombok\1.18.10\lombok-1.18.10.jar;D:\develop\z_maven_package\commons-dbutils\commons-dbutils\1.7\commons-dbutils-1.7.jar;D:\develop\z_maven_package\mysql\mysql-connector-java\8.0.20\mysql-connector-java-8.0.20.jar;D:\develop\z_maven_package\com\google\protobuf\protobuf-java\3.6.1\protobuf-java-3.6.1.jar;D:\develop\z_maven_package\org\slf4j\slf4j-api\1.7.30\slf4j-api-1.7.30.jar;D:\develop\z_maven_package\org\apache\logging\log4j\log4j-slf4j-impl\2.13.3\log4j-slf4j-impl-2.13.3.jar;D:\develop\z_maven_package\org\apache\logging\log4j\log4j-api\2.13.3\log4j-api-2.13.3.jar;D:\develop\z_maven_package\org\apache\logging\log4j\log4j-core\2.13.3\log4j-core-2.13.3.jar;D:\develop\z_maven_package\org\springframework\spring-jdbc\5.2.6.RELEASE\spring-jdbc-5.2.6.RELEASE.jar;D:\develop\z_maven_package\org\springframework\spring-beans\5.2.6.RELEASE\spring-beans-5.2.6.RELEASE.jar;D:\develop\z_maven_package\org\springframework\spring-core\5.2.6.RELEASE\spring-core-5.2.6.RELEASE.jar;D:\develop\z_maven_package\org\springframework\spring-jcl\5.2.6.RELEASE\spring-jcl-5.2.6.RELEASE.jar;D:\develop\z_maven_package\org\springframework\spring-tx\5.2.6.RELEASE\spring-tx-5.2.6.RELEASE.jar;D:\develop\z_maven_package\org\springframework\spring-context\5.2.6.RELEASE\spring-context-5.2.6.RELEASE.jar;D:\develop\z_maven_package\org\springframework\spring-aop\5.2.6.RELEASE\spring-aop-5.2.6.RELEASE.jar;D:\develop\z_maven_package\org\springframework\spring-expression\5.2.6.RELEASE\spring-expression-5.2.6.RELEASE.jar;D:\develop\z_maven_package\com\mchange\c3p0\0.9.5.5\c3p0-0.9.5.5.jar;D:\develop\z_maven_package\com\mchange\mchange-commons-java\0.2.19\mchange-commons-java-0.2.19.jar;D:\develop\z_maven_package\junit\junit\4.11\junit-4.11.jar;D:\develop\z_maven_package\org\hamcrest\hamcrest-core\1.3\hamcrest-core-1.3.jar" com.intellij.rt.junit.JUnitStarter -ideVersion5 -junit4 com.ddpyjqtd.demo.dao.TestDemoDao,testFindDemoByKey
2020-06-11 12:56:22.971 INFO  com.mchange.v2.log.MLog 212 log - MLog clients using slf4j logging.
2020-06-11 12:56:23.230 INFO  com.mchange.v2.c3p0.C3P0Registry 212 log - Initializing c3p0-0.9.5.5 [built 11-December-2019 22:18:33 -0800; debug? true; trace: 10]
2020-06-11 12:56:23.390 INFO  com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource 212 log - Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 1hge9jjaa18te7ha1837vom|23c30a20, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.cj.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hge9jjaa18te7ha1837vom|23c30a20, idleConnectionTestPeriod -> 0, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 15, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
2020-06-11 12:56:23.726 INFO  com.ddpyjqtd.demo.dao.DemoDao 36 findDemoByKey - demo info is : Demo(demoInt=1, demoVarchar=this is demo_varchar, demoText=this is demo_text, demoDateTime=2020-06-06 06:37:37.0)

Process finished with exit code 0

2.4 优缺点

2.4.1 优点

是 Spring 对 JDBC 的一层封装，可灵活的进行 Sql 语句的编写。
性能基本上与纯 JDBC 方式一致。
基于 Spring，目前大部分份系统都会集成 Spring 进行开发，所以可以天然的使用 jdbcTemplate。
与 Spring 配合使用，数据源、数据库连接池等其它资源都交由 Spring 进行管理

2.4.2 缺点

只是 Spring 进行了封装，封装程度不够高，开发过程中还是需要写大量的代码。
SQL 存在硬编码。
要使用时必须基于 Spring 框架进行使用。
在代码中存在硬编码。

还有头发的老李

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MyBatis学习03-前篇之JdbcTemplate

MyBatis学习03-前篇之JdbcTemplate1 前言2 JdbcTemplate2.1 官网地址2.2 简介2.3 示例2.4 优缺点2.4.1 优点2.4.2 缺点1 前言使用 JDBC 进行数据库操作需要研发人员对数据库连接、结果集等资源进行管理，对于数据库连接的获取、预加载对象、结果集对象映射转换等内容进行大量的重复性的工作，且使用过程中容易出现资源泄露问题。实际上使用 JDBC 进行数据库操作的模式都是一样的，系统研发过程中，为了节约研发成本，大量研发或者团队选择对 JDBC 进行了一
复制链接

扫一扫