使用Spring JDBCTemplate简化JDBC操作

最新推荐文章于 2023-06-01 00:02:13 发布

Ricky_Fung

最新推荐文章于 2023-06-01 00:02:13 发布

阅读量5.7k

点赞数

分类专栏： Spring Framework 文章标签： spring JDBC

本文链接：https://blog.csdn.net/top_code/article/details/51957718

版权

Spring Framework 专栏收录该内容

18 篇文章 1 订阅

订阅专栏

Spring JDBCTemplate是一款轻量级ORM框架，使用它替换原生的JDBC操作数据库会轻松许多。

本文将将介绍Spring中关于JDBC的一个辅助类(JDBCTemplate)，它封装了JDBC的操作，使用起来非常方便。

maven依赖

<properties>
    <spring.version>4.2.4.RELEASE</spring.version>
    <aspectj.version>1.8.8</aspectj.version>
    <java.version>1.7</java.version>
    <maven.compiler.version>3.1</maven.compiler.version>
    <junit.version>4.12</junit.version>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>

<dependencies>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-context</artifactId>
        <version>${spring.version}</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-core</artifactId>
        <version>${spring.version}</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>${spring.version}</version>
    </dependency>

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.26</version>
    </dependency>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.0.21</version>
    </dependency>

</dependencies>

Model

employee表：

CREATE TABLE `tb_employee` (
  `ID` bigint(10) NOT NULL AUTO_INCREMENT,
  `NAME` VARCHAR(100) NOT NULL,
  `AGE` smallint(4) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Employee.java

package com.ricky.codelab.domain;

public class Employee {

    private int id;

    private String name;

    private int age;

    public Employee(){

    }

    public Employee(int id, String name, int age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return Employee ["id= "+ id + ", name= "+ name + ", age= "+ age
                +"]";
    }

}

Spring配置

1、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:util="http://www.springframework.org/schema/util"
       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/util
       http://www.springframework.org/schema/util/spring-util.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd"
       default-lazy-init="false">

    <context:annotation-config/>
    <context:component-scan base-package="com.ricky.codelab.spring"/>

    <!-- 引入配置文件 -->
    <util:properties id="jdbc" location="classpath:jdbc.properties"/>

    <import resource="spring-dao.xml"/>

    <bean id="threadPool" class="java.util.concurrent.ThreadPoolExecutor">
        <constructor-arg index="0" value="10"/>
        <constructor-arg index="1" value="20"/>
        <constructor-arg index="2" value="1"/>
        <constructor-arg index="3" value="MINUTES"/>
        <constructor-arg index="4">
            <bean class="java.util.concurrent.LinkedBlockingQueue"/>
        </constructor-arg>
    </bean>

</beans>

其中，jdbc.properties

test1.jdbc.driver=com.mysql.jdbc.Driver
test1.jdbc.url=jdbc:mysql://localhost:3306/process?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull
test1.jdbc.username=root
test1.jdbc.password=root

test2.jdbc.driver=com.mysql.jdbc.Driver
test2.jdbc.url=jdbc:mysql://localhost:3306/process?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull
test2.jdbc.username=root
test2.jdbc.password=root

2、spring-dao.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:tx="http://www.springframework.org/schema/tx"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:util="http://www.springframework.org/schema/util"
       xsi:schemaLocation="
       http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans.xsd
       http://www.springframework.org/schema/tx
       http://www.springframework.org/schema/tx/spring-tx.xsd
       http://www.springframework.org/schema/util
       http://www.springframework.org/schema/util/spring-util.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context.xsd"
       default-lazy-init="false">

    <context:annotation-config />
    <context:component-scan base-package="com.ricky.codelab.spring" />


    <bean id="dataSource_Abstract" class="com.alibaba.druid.pool.DruidDataSource"
           destroy-method="close"  abstract="true" init-method="init" >
        <!-- 初始化连接大小 -->
        <property name="initialSize" value="2" />
        <!-- 连接池最大使用连接数量 -->
        <property name="maxActive" value="10" />
        <!-- 连接池最小空闲 -->
        <property name="minIdle" value="5" />
        <!-- 获取连接最大等待时间 -->
        <property name="maxWait" value="30000" />
        <!-- <property name="poolPreparedStatements" value="true" /> -->
        <!-- <property name="maxPoolPreparedStatementPerConnectionSize" value="33" /> -->
        <property name="validationQuery" value="SELECT 1" />
        <property name="testOnBorrow" value="false" />
        <property name="testOnReturn" value="false" />
        <property name="testWhileIdle" value="true" />
        <!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->
        <property name="timeBetweenEvictionRunsMillis" value="60000" />
        <!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->
        <property name="minEvictableIdleTimeMillis" value="25200000" />
        <!-- 打开removeAbandoned功能 -->
        <property name="removeAbandoned" value="true" />
        <!-- 1800秒，也就是30分钟 -->
        <property name="removeAbandonedTimeout" value="1800" />
        <!-- 关闭abanded连接时输出错误日志 -->
        <property name="logAbandoned" value="true" />
        <!-- 监控数据库 -->
        <!-- <property name="filters" value="stat" /> -->
        <property name="filters" value="mergeStat" />

    </bean>
    <!-- 配置来源数据 -->
    <bean id="Test1DataSource" parent="dataSource_Abstract">
        <property name="url" value="#{jdbc['test1.jdbc.url']}" />
        <property name="username" value="#{jdbc['test1.jdbc.username']}" />
        <property name="password" value="#{jdbc['test1.jdbc.password']}" />
        <property name="driverClassName" value="#{jdbc['test1.jdbc.driver']}" />
        <!-- 连接池最大使用连接数量 -->
        <property name="maxActive" value="50" />
    </bean>

    <bean id="Test2DataSource" parent="dataSource_Abstract">
        <property name="url" value="#{jdbc['test2.jdbc.url']}" />
        <property name="username" value="#{jdbc['test2.jdbc.username']}" />
        <property name="password" value="#{jdbc['test2.jdbc.password']}" />
        <property name="driverClassName" value="#{jdbc['test2.jdbc.driver']}" />
    </bean>


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

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

</beans>

3、EmployeeDao

package com.ricky.codelab.spring.jdbc.dao;

import com.ricky.codelab.spring.domain.Employee;
import java.util.List;

/**
 * ${DESCRIPTION}
 *
 * @author Ricky Fung
 * @create 2016-07-19 17:43
 */
public interface EmployeeDao {

    public int insert(Employee employee);
    public Employee findById(long id);
    public List<Employee> queryAllEmployees();
}

EmployeeDaoImpl.java

package com.ricky.codelab.spring.jdbc.dao.impl;

import com.ricky.codelab.spring.domain.Employee;
import com.ricky.codelab.spring.jdbc.dao.EmployeeDao;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * ${DESCRIPTION}
 *
 * @author Ricky Fung
 * @create 2016-07-19 17:44
 */
@Repository("employeeDao")
public class EmployeeDaoImpl implements EmployeeDao {

    @Resource(name = "test1JdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    @Override
    public int insert(Employee employee) {

        String sql = "INSERT INTO tb_employee(NAME, AGE) VALUES (?, ?)";

        return jdbcTemplate.update(sql, new Object[] {employee.getName(), employee.getAge()});
    }

    @Override
    public Employee findById(long id) {

        String sql = "SELECT * FROM tb_employee WHERE ID = ?";

        return jdbcTemplate.queryForObject(sql, new Object[]{ id }, new RowMapper<Employee>() {
            public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
                if(rs!=null){
                    Employee employee = new Employee();
                    employee.setId(rs.getInt("ID"));
                    employee.setName(rs.getString("NAME"));
                    employee.setAge(rs.getInt("AGE"));
                    return employee;
                }
                return null;
            }
        });
    }

    @Override
    public List<Employee> queryAllEmployees() {

        String sql = "SELECT * FROM tb_employee";

        return jdbcTemplate.query(sql, new RowMapper<Employee>() {
            public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
                if(rs!=null){
                    Employee employee = new Employee();
                    employee.setId(rs.getInt("ID"));
                    employee.setName(rs.getString("NAME"));
                    employee.setAge(rs.getInt("AGE"));
                    return employee;
                }
                return null;
            }
        });
    }
}

主要使用JdbcTemplate的update、queryForObject和query方法。

参考资料

http://docs.spring.io/spring/docs/current/spring-framework-reference/html/jdbc.html

代码下载

所有代码均已上传到Github，点此下载

Ricky_Fung

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
使用Spring JDBCTemplate简化JDBC操作

Spring JDBCTemplate是一款轻量级ORM框架，使用它替换原生的JDBC操作数据库会轻松许多。本文将将介绍Spring中关于JDBC的一个辅助类(JDBCTemplate)，它封装了JDBC的操作，使用起来非常方便。maven依赖<properties> <spring.version>4.2.4.RELEASE</spring.version> <aspectj.ve
复制链接

扫一扫