【Spring】JDBC的支持

最新推荐文章于 2020-10-11 22:04:29 发布

原创最新推荐文章于 2020-10-11 22:04:29 发布 · 259 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Java

Java 同时被 3 个专栏收录

66 篇文章

订阅专栏

MySQL

10 篇文章

订阅专栏

JDBC

6 篇文章

订阅专栏

该博客主要围绕Java展开，介绍了jdbcTemplate的操作，如INSERT、UPDATE、DELETE等，还提及批量操作、获取记录和列值等。同时提到了JdbcDaoSupport，不推荐使用，推荐直接用jdbcTemplate作成员变量，另外介绍了NamedParameterJdbcTemplate及相关配置。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

JDBC的支持

jdbcTemplate
JdbcDaoSupport
NamedParameterJdbcTemplate
- - JDBCTest.java(com.test.spring.jdbc.JDBCTest)
  - applicationContext.xml

jdbcTemplate

JDBCTest.java(com.test.spring.jdbc.JDBCTest)

INSERT，UPDATE，DELETE

/**
     * 执行 INSERT，UPDATE，DELETE
     */
    @Test
    public void testUpdate() {
        String sql = "UPDATE tb_student SET name = ? WHERE id = ?";
        jdbcTemplate.update(sql, "小兰", 3);
    }

    @Test
    public void test() throws SQLException {
        DataSource dataSource = ctx.getBean(DataSource.class);
        System.out.println(dataSource.getConnection());
    }

批量的 INSERT，UPDATE，DELETE

/**
     * 执行批量跟新：批量的 INSERT，UPDATE，DELETE
     * 最后一个参数是 Object[] 的 List 类型：因为修改一条记录需要一个 Object 的数组，那么多条就需要多个 Object 的数组
     */
    @Test
    public void testBatchUpdate() {
        String sql = "INSERT INTO tb_student(snum, name, age, sex, class_id) VALUES(?,?,?,?,?)";

        List<Object[]> batchArgs = new ArrayList<>();

        batchArgs.add(new Object[]{2147483647, "AA", 20, "男", 1013});
        batchArgs.add(new Object[]{2147483647, "BB", 21, "女", 1014});
        batchArgs.add(new Object[]{2147483647, "CC", 22, "女", 1013});

        jdbcTemplate.batchUpdate(sql, batchArgs);
    }

获取一条记录

/**
     * 从数据库中获取一条记录，实际得到对应的一个对象
     * 注意不是调用 queryForObject(String sql, Class<Employee> requiredType, Object... args) 方法!
     * 而需要调用 queryForObject(String sql, RowMapper<Employee> rowMapper, Object... args)
     * 1. 其中的 RowMapper 指定如何去映射结果集的行，常用的实现类为 BeanPropertyRowMapper
     * 2. 使用 SQL 中列的别名完成列名和类的属性名的映射，例如 Name name
     * 3. 不支持级联属性，JdbcTemplate 到底是一个 JDBC 的小工具，而不是 ORM 框架
     */
    @Test
    public void testQueryForObject() {
        String sql = "SELECT id, name, email FROM employees WHERE id = ?";
        RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);
        Employee employee = jdbcTemplate.queryForObject(sql, rowMapper, 1);

        System.out.println(employee);
    }

获取实体类的集合

/**
     * 查到实体类的集合
     * 注意调用的不是 queryForList 方法
     */
    @Test
    public void testQueryForList() {
        String sql = "SELECT id, name, email FROM employees WHERE id > ?";
        RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);
        List<Employee> employees = jdbcTemplate.query(sql, rowMapper, 3);

        System.out.println(employees);
    }

获取单个列的值

/**
     *  获取单个列的值，或做统计查询
     *  使用 queryForObject(String sql, Class<Long> requiredType)
     */
    @Test
    public void testQueryForObject2() {
        String sql = "SELECT count(id) FROM employees";
        long count = jdbcTemplate.queryForObject(sql, Long.class);

        System.out.println(count);
    }

源码

package com.test.spring.jdbc;


import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class JDBCTest {

    private ApplicationContext ctx = null;
    private JdbcTemplate jdbcTemplate;

    {
        ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
        jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate");
    }

    /**
     *  获取单个列的值，或做统计查询
     *  使用 queryForObject(String sql, Class<Long> requiredType)
     */
    @Test
    public void testQueryForObject2() {
        String sql = "SELECT count(id) FROM employees";
        long count = jdbcTemplate.queryForObject(sql, Long.class);

        System.out.println(count);
    }

    /**
     * 查到实体类的集合
     * 注意调用的不是 queryForList 方法
     */
    @Test
    public void testQueryForList() {
        String sql = "SELECT id, name, email FROM employees WHERE id > ?";
        RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);
        List<Employee> employees = jdbcTemplate.query(sql, rowMapper, 3);

        System.out.println(employees);
    }

    /**
     * 从数据库中获取一条记录，实际得到对应的一个对象
     * 注意不是调用 queryForObject(String sql, Class<Employee> requiredType, Object... args) 方法!
     * 而需要调用 queryForObject(String sql, RowMapper<Employee> rowMapper, Object... args)
     * 1. 其中的 RowMapper 指定如何去映射结果集的行，常用的实现类为 BeanPropertyRowMapper
     * 2. 使用 SQL 中列的别名完成列名和类的属性名的映射，例如 Name name
     * 3. 不支持级联属性，JdbcTemplate 到底是一个 JDBC 的小工具，而不是 ORM 框架
     */
    @Test
    public void testQueryForObject() {
        String sql = "SELECT id, name, email FROM employees WHERE id = ?";
        RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);
        Employee employee = jdbcTemplate.queryForObject(sql, rowMapper, 1);

        System.out.println(employee);
    }

    /**
     * 执行批量跟新：批量的 INSERT，UPDATE，DELETE
     * 最后一个参数是 Object[] 的 List 类型：因为修改一条记录需要一个 Object 的数组，那么多条就需要多个 Object 的数组
     */
    @Test
    public void testBatchUpdate() {
        String sql = "INSERT INTO tb_student(snum, name, age, sex, class_id) VALUES(?,?,?,?,?)";

        List<Object[]> batchArgs = new ArrayList<>();

        batchArgs.add(new Object[]{2147483647, "AA", 20, "男", 1013});
        batchArgs.add(new Object[]{2147483647, "BB", 21, "女", 1014});
        batchArgs.add(new Object[]{2147483647, "CC", 22, "女", 1013});

        jdbcTemplate.batchUpdate(sql, batchArgs);
    }

    /**
     * 执行 INSERT，UPDATE，DELETE
     */
    @Test
    public void testUpdate() {
        String sql = "UPDATE tb_student SET name = ? WHERE id = ?";
        jdbcTemplate.update(sql, "小兰", 3);
    }

    @Test
    public void test() throws SQLException {
        DataSource dataSource = ctx.getBean(DataSource.class);
        System.out.println(dataSource.getConnection());
    }
}

其他文件

配置Bean-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"></context:property-placeholder>

    <!-- 配置 C3P0 数据源 -->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="user" value="${jdbc.user}"></property>
        <property name="password" value="${jdbc.password}"></property>
        <property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>
        <property name="driverClass" value="${jdbc.driverClass}"></property>

        <property name="initialPoolSize" value="${jdbc.initPoolSize}"></property>
        <property name="maxPoolSize" value="${jdbc.maxPoolSize}"></property>
    </bean>

    <!-- 配置 Spring 的 JDBCTemplate -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

</beans>

SQL地址文件-db.properties.txt

jdbc.user=root
jdbc.password=123456
jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.jdbcUrl=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8

jdbc.initPoolSize=5
jdbc.maxPoolSize=10

Employee.java(com.test.spring.jdbc.Employee)

这里面用toString只是为了方便后面的输出打印

package com.test.spring.jdbc;

public class Employee {

    private Integer id;
    private String name;
    private String email;
    private Integer age;

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Integer getAge() {
        return age;
    }

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

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", email='" + email + '\'' +
                ", age=" + age +
                '}';
    }
}

JdbcDaoSupport

不推荐使用 JdbcDaoSupport，而推荐直接使用 jdbcTemplate 作为 Dao 类的成员变量

DepartmentDao.java(com.test.spring.jdbc.DepartmentDao)

package com.test.spring.jdbc;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;
import javax.sql.DataSource;

@Repository
public class DepartmentDao extends JdbcDaoSupport {

    @Autowired
    public void setDataSource2(DataSource dataSource){
        setDataSource(dataSource);
    }

    public Department get(Integer id){
        String sql = "SELECT id, name FROM employees WHERE id = ?";
        RowMapper<Department> rowMapper = new BeanPropertyRowMapper<>(Department.class);
        return getJdbcTemplate().queryForObject(sql, rowMapper, id);
    }
}

EmployeeDao.java(com.test.spring.jdbc.EmployeeDao)

package com.test.spring.jdbc;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

@Repository
public class EmployeeDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public Employee get(Integer id) {
        String sql = "SELECT id, name, email FROM employees WHERE id = ?";
        RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);
        Employee employee = jdbcTemplate.queryForObject(sql, rowMapper, 1);

        return employee;
    }
}

JDBCTest.java(com.test.spring.jdbc.JDBCTest)

package com.test.spring.jdbc;


import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class JDBCTest {

    private ApplicationContext ctx = null;
    private EmployeeDao employeeDao;
    private DepartmentDao departmentDao;

    {
        ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
        employeeDao = ctx.getBean(EmployeeDao.class);
        departmentDao = ctx.getBean(DepartmentDao.class);
    }

    @Test
    public void testDepartmentDao(){
        System.out.println(departmentDao.get(1));
    }

    @Test
    public void testEmployeeDao() {
        System.out.println(employeeDao.get(1));
    }
}

NamedParameterJdbcTemplate

JDBCTest.java(com.test.spring.jdbc.JDBCTest)

public class JDBCTest {

    private ApplicationContext ctx = null;
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    {
        ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
        namedParameterJdbcTemplate = ctx.getBean(NamedParameterJdbcTemplate.class);
    }

   /**
     * 使用具名参数时可以使用 update(String sql, SqlParameterSource paramSource) 方法进行更新操作
     * 1. SQL 语句中的参数名和类的属性一致！
     * 2. 使用 SqlParameterSource 的 BeanPropertySqlParameterSource 实现类作为参数。
     */
    @Test
    public void testNamedParameterJdbcTemplate2(){
        String sql = "INSERT INTO employees(name, email, age) VALUES (:name,:email,:age)";

        Employee employee = new Employee();
        employee.setName("ZZ");
        employee.setEmail("zz@test.com");
        employee.setAge(22);

        SqlParameterSource paramSource = new BeanPropertySqlParameterSource(employee);
        namedParameterJdbcTemplate.update(sql, paramSource);
    }

    /**
     * 可以为参数起名字。
     * 优点： 若有多个参数，则不用再去对应位置，直接对应参数名就可以了，方便维护
     * 缺点： 较为麻烦。
     */
    @Test
    public void testNamedParameterJdbcTemplate() {
        String sql = "INSERT INTO employees(name, email, age) VALUES (:name,:email,:age)";

        Map<String, Object> paramMap = new HashMap<>();
        paramMap.put("name", "GG");
        paramMap.put("email", "GG@test.com");
        paramMap.put("age", 23);
        namedParameterJdbcTemplate.update(sql, paramMap);
    }
}

applicationContext.xml

在Bean中加入以下代码即可

<!-- 配置 NamedParameterJdbcTemplate，该对象可以使用具名参数，其没有无参数的构造器，所有必须为其构造器指定参数 -->
    <bean id="namedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
        <constructor-arg ref="dataSource"></constructor-arg>
    </bean>