Spring的JdbcTemplate模板使用

最新推荐文章于 2023-08-21 02:59:57 发布

Student108

最新推荐文章于 2023-08-21 02:59:57 发布

阅读量314

点赞数

CC 4.0 BY-SA版权

分类专栏： Spring

本文链接：https://blog.csdn.net/Student108/article/details/108448618

Spring 专栏收录该内容

7 篇文章

订阅专栏

本文详细介绍了Spring的JdbcTemplate使用，从jar依赖到数据库准备，再到不使用Spring配置的测试，接着阐述了如何通过Spring配置JdbcTemplate，包括使用配置文件的方式。重点讲解了JdbcTemplate的四大基本操作：增加、删除、更新和查找，提供了不同场景下的代码示例。

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

1 Spring的JDBC Template

1.1 jar要求

基础的spring开发包
- spring-core.jar
- spring-beans.jar
- spring-context.jar
- spring-expression.jar
- commons-logging.jar
- log4j.jar
spring-jdbc.jar
spring-tx.jar
mysql-connection.jar
spring-aop.jar
spring-test.jar,方便测试
c3p0.jar,如果需要使用c3p0连接池功能

1.2 数据库和表准备

create database jdbc_template;
use jdbc_template;
create table account(
	id int primary key auto_increment,
	name varchar(20),
	money double
);

1.3 不使用spring配置测试适用jdbc template

public class JDBCTemplateTest01
{
    @Test
    public void test() throws SQLException
    {
        // Spring 内置的jdbc模板
        // 不使用xml配置,可以将DataSource设置为工具类
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql:///jdbc_template");
        dataSource.setUsername("root");
        dataSource.setPassword("123456");

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.update("insert into account (name,money) values(?,?)", "张三", 1000d);
    }
}

1.4 使用spring配置jdbcTemplate

1.4.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:context="http://www.springframework.org/schema/context"
	xmlns:aop="http://www.springframework.org/schema/aop"
	xmlns:tx="http://www.springframework.org/schema/tx"
	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
	http://www.springframework.org/schema/aop
	http://www.springframework.org/schema/aop/spring-aop.xsd
	http://www.springframework.org/schema/tx 
	http://www.springframework.org/schema/tx/spring-tx.xsd">

	<!-- 配置数据源 -->
	<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
		<property name="url" value="jdbc:mysql:///jdbc_template"/>
		<property name="username" value="root"/>
		<property name="password" value="123456"/>
	</bean>
	
	<!-- 配置JDBC模板 -->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource"/>
	</bean>
</beans>

1.4.2 测试代码

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:com/zgd/learn/spring/demo04/application-jdbcTemplate.xml")
public class JdbcTemplateTest02
{
    @Resource
    private JdbcTemplate jdbcTemplate;

    @Test
    public void test()
    {
        jdbcTemplate.update("insert into account (name,money) values(?,?)", "张三", 1000d);
    }
}

1.5 使用配置文件配置jdbcTemplate

1.5.1 jdbc.properties配置

jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql:///jdbc_template
jdbc.username=root
jdbc.password=123456

1.5.2 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"
	xmlns:aop="http://www.springframework.org/schema/aop"
	xmlns:tx="http://www.springframework.org/schema/tx"
	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
	http://www.springframework.org/schema/aop
	http://www.springframework.org/schema/aop/spring-aop.xsd
	http://www.springframework.org/schema/tx 
	http://www.springframework.org/schema/tx/spring-tx.xsd">

	<!-- 配置c3p0的数据源 -->
<!-- 	<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
		<property name="driverClass" value="com.mysql.jdbc.Driver"/>
		<property name="jdbcUrl" value="jdbc:mysql:///jdbc_template"/>
		<property name="user" value="root"/>
		<property name="password" value="123456"/>
	</bean> -->
	
	<!-- 使用bean标签引入配置文件 -->
	<!-- <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
		<property name="location" value="classpath:jdbc.properties"/>
	</bean> -->
	
	<!-- 使用context标签引入配置文件 -->
	<context:property-placeholder location="classpath:jdbc.properties"/>
	
	<!-- 使用properties文件中参数设置数据源 -->
	<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
		<property name="driverClass" value="${jdbc.driverClass}"/>
		<property name="jdbcUrl" value="${jdbc.url}"/>
		<property name="user" value="${jdbc.username}"/>
		<property name="password" value="${jdbc.password}"/>
	</bean>
	
	<!-- 配置JDBC模板 -->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource"/>
	</bean>
</beans>

1.5.3 测试代码

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:com/zgd/learn/spring/demo04/application-jdbcTemplate.xml")
public class JdbcTemplateTest02
{
    @Resource
    private JdbcTemplate jdbcTemplate;

    @Test
    public void test()
    {
        jdbcTemplate.update("insert into account (name,money) values(?,?)", "李四", 1000d);
    }
}

1.6 JdbcTemplate的增删查改

1.6.1 增加

jdbcTemplate.update("insert into account (name,money) values(?,?)", "张三2", 1000d);

1.6.2 删除

 jdbcTemplate.update("delete from account where id=?", 2);

1.6.3 更新

 jdbcTemplate.update("update account set name=? where id=?", "王五", 3);

1.6.4 查找

1.6.4.1 查找单行单独列值

String name = jdbcTemplate.queryForObject("select name from account where id=?", String.class, 3);

1.6.4.2 查找单行多列列值

  Account account = jdbcTemplate.queryForObject("select * from account where id=?", Account.class, 3);
        System.out.println(account);

1.6.4.3 查找多行多列列值

List<Account> list = jdbcTemplate.query("select * from account", new RowMapper<Account>()
        {
            @Override
            public Account mapRow(ResultSet rs, int row) throws SQLException
            {
                Account account = new Account();
                account.setId(rs.getInt("id"));
                account.setName(rs.getString("name"));
                account.setMoney(rs.getDouble("money"));
                return account;
            }

        });
        System.out.println(list);

1.6.1 增删查改代码示例

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:application-jdbcTemplate.xml")
public class JdbcTemplateTest02
{
    @Resource
    private JdbcTemplate jdbcTemplate;

    @Test
    public void save()
    {
        jdbcTemplate.update("insert into account (name,money) values(?,?)", "张三2", 1000d);
    }

    @Test
    public void delete()
    {
        jdbcTemplate.update("delete from account where id=?", 2);
    }

    @Test
    public void update()
    {
        jdbcTemplate.update("update account set name=? where id=?", "王五", 3);
    }

    /**
     * 查询单行单列
     */
    @Test
    public void findName()
    {
        String name = jdbcTemplate.queryForObject("select name from account where id=?", String.class, 3);
        System.out.println(name);
    }

    /**
     * 查询单行多列
     */
    @Test
    public void findAccount()
    {
        Account account = jdbcTemplate.queryForObject("select * from account where id=?", Account.class, 3);
        System.out.println(account);
    }

    /**
     * 查询多行多列
     */
    @Test
    public void findAccountList()
    {
        List<Account> list = jdbcTemplate.query("select * from account", new RowMapper<Account>()
        {
            @Override
            public Account mapRow(ResultSet rs, int row) throws SQLException
            {
                Account account = new Account();
                account.setId(rs.getInt("id"));
                account.setName(rs.getString("name"));
                account.setMoney(rs.getDouble("money"));
                return account;
            }
        });
        System.out.println(list);
    }
}