• 日期: 2026-04-01 04:51:05
  • 标签: 常规

3、配置 Spring JDBC 相关的 applicationContext 内容：

首先需要在 applicationContext.xml 文件中添加以下代码：

<!-- 配置数据源 -->
<bean id='dataSource' class='org.apache.commons.dbcp.BasicDataSource'>
    <property name='driverClassName' value='com.mysql.jdbc.Driver'/>
    <property name='url' value='jdbc:mysql://localhost:3306/MyDB'/>
    <property name='username' value='root'/>
    <property name='password' value='123456'/>
    <property name='initialSize' value='5'/>
</bean>

<!-- 配置 Spring JDBC 模板 -->
<bean id='jdbcTemplate' class='org.springframework.jdbc.core.JdbcTemplate'>
    <property name='dataSource' ref='dataSource'/>
</bean>

4、建立数据库 MyDB：

可以使用 MySQL 的客户端工具或者命令行工具创建数据库，例如：

CREATE DATABASE MyDB;

5、采用 Spring JdbcTemplate 的 execute() 创建数据表 student：

在 applicationContext.xml 文件中添加以下代码：

<!-- 创建数据表 -->
<bean id='createTable' class='org.springframework.jdbc.core.JdbcTemplate' lazy-init='true'>
    <property name='dataSource' ref='dataSource'/>
</bean>
<bean id='createTableSql' class='java.lang.String'>
    <constructor-arg value='CREATE TABLE student (id INT NOT NULL AUTO_INCREMENT, name VARCHAR(20) NOT NULL, gender CHAR(1) NOT NULL, birthday DATE, score INT, PRIMARY KEY (id))'/>
</bean>
<bean id='createTableTask' class='org.springframework.jdbc.core.JdbcOperations'>
    <property name='operations' ref='createTable'/>
    <property name='sql' ref='createTableSql'/>
</bean>
<bean id='createTableExecutor' class='org.springframework.jdbc.core.JdbcTemplate' lazy-init='true'>
    <property name='dataSource' ref='dataSource'/>
    <property name='skipUndeclaredResults' value='true'/>
</bean>
<bean id='createTableTaskExecutor' class='org.springframework.jdbc.core.JdbcOperations'>
    <property name='operations' ref='createTableExecutor'/>
    <property name='sql' ref='createTableSql'/>
</bean>

<!-- 执行创建数据表的任务 -->
<bean id='taskExecutor' class='org.springframework.core.task.SimpleAsyncTaskExecutor'/>
<bean id='createTableJobDetail' class='org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean'>
    <property name='targetObject' ref='taskExecutor'/>
    <property name='targetMethod' value='execute'/>
    <property name='arguments'>
        <list>
            <ref bean='createTableTask'/>
            <ref bean='createTableTaskExecutor'/>
        </list>
    </property>
</bean>
<bean id='createTableTrigger' class='org.springframework.scheduling.quartz.SimpleTriggerFactoryBean'>
    <property name='jobDetail' ref='createTableJobDetail'/>
    <property name='repeatInterval' value='1000'/>
    <property name='repeatCount' value='0'/>
</bean>
<bean class='org.springframework.scheduling.quartz.SchedulerFactoryBean'>
    <property name='triggers'>
        <list>
            <ref bean='createTableTrigger'/>
        </list>
    </property>
</bean>

6、采用 Spring JdbcTemplate 的 update() 对数据表 student 插入 5 个记录，并修改最后一条记录的 score 为 377：

在 applicationContext.xml 文件中添加以下代码：

<!-- 插入数据 -->
<bean id='insertData' class='org.springframework.jdbc.core.JdbcTemplate' lazy-init='true'>
    <property name='dataSource' ref='dataSource'/>
</bean>
<bean id='insertDataSql' class='java.lang.String'>
    <constructor-arg value='INSERT INTO student (name, gender, birthday, score) VALUES (?, ?, ?, ?)'/>
</bean>
<bean id='insertDataTask' class='org.springframework.jdbc.core.JdbcOperations'>
    <property name='operations' ref='insertData'/>
    <property name='sql' ref='insertDataSql'/>
    <property name='parameters'>
        <list>
            <list>
                <value>小明</value>
                <value>男</value>
                <value>1999-01-01</value>
                <value>100</value>
            </list>
            <list>
                <value>小红</value>
                <value>女</value>
                <value>1999-02-02</value>
                <value>200</value>
            </list>
            <list>
                <value>小刚</value>
                <value>男</value>
                <value>1999-03-03</value>
                <value>300</value>
            </list>
            <list>
                <value>小丽</value>
                <value>女</value>
                <value>1999-04-04</value>
                <value>400</value>
            </list>
            <list>
                <value>小李</value>
                <value>男</value>
                <value>1999-05-05</value>
                <value>500</value>
            </list>
        </list>
    </property>
</bean>
<bean id='insertDataExecutor' class='org.springframework.jdbc.core.JdbcTemplate' lazy-init='true'>
    <property name='dataSource' ref='dataSource'/>
    <property name='skipUndeclaredResults' value='true'/>
</bean>
<bean id='insertDataTaskExecutor' class='org.springframework.jdbc.core.JdbcOperations'>
    <property name='operations' ref='insertDataExecutor'/>
    <property name='sql' ref='insertDataSql'/>
    <property name='parameters'>
        <list>
            <list>
                <value>小明</value>
                <value>男</value>
                <value>1999-01-01</value>
                <value>100</value>
            </list>
            <list>
                <value>小红</value>
                <value>女</value>
                <value>1999-02-02</value>
                <value>200</value>
            </list>
            <list>
                <value>小刚</value>
                <value>男</value>
                <value>1999-03-03</value>
                <value>300</value>
            </list>
            <list>
                <value>小丽</value>
                <value>女</value>
                <value>1999-04-04</value>
                <value>400</value>
            </list>
            <list>
                <value>小李</value>
                <value>男</value>
                <value>1999-05-05</value>
                <value>500</value>
            </list>
            <list>
                <value>377</value>
                <value>5</value>
            </list>
        </list>
    </property>
</bean>

<!-- 执行插入数据的任务 -->
<bean id='insertDataJobDetail' class='org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean'>
    <property name='targetObject' ref='taskExecutor'/>
    <property name='targetMethod' value='execute'/>
    <property name='arguments'>
        <list>
            <ref bean='insertDataTask'/>
            <ref bean='insertDataTaskExecutor'/>
        </list>
    </property>
</bean>
<bean id='insertDataTrigger' class='org.springframework.scheduling.quartz.SimpleTriggerFactoryBean'>
    <property name='jobDetail' ref='insertDataJobDetail'/>
    <property name='repeatInterval' value='1000'/>
    <property name='repeatCount' value='0'/>
</bean>
<bean class='org.springframework.scheduling.quartz.SchedulerFactoryBean'>
    <property name='triggers'>
        <list>
            <ref bean='insertDataTrigger'/>
        </list>
    </property>
</bean>

7、采用 Spring JdbcTemplate 的 query() 对数据表 student 查询生日是 7 月份的女同学：

在 applicationContext.xml 文件中添加以下代码：

<!-- 查询数据 -->
<bean id='queryData' class='org.springframework.jdbc.core.JdbcTemplate' lazy-init='true'>
    <property name='dataSource' ref='dataSource'/>
</bean>
<bean id='queryDataSql' class='java.lang.String'>
    <constructor-arg value='SELECT * FROM student WHERE MONTH(birthday) = 7 AND gender = '女'/>
</bean>
<bean id='queryDataTask' class='org.springframework.jdbc.core.JdbcOperations'>
    <property name='operations' ref='queryData'/>
    <property name='sql' ref='queryDataSql'/>
</bean>
<bean id='queryDataExecutor' class='org.springframework.jdbc.core.JdbcTemplate' lazy-init='true'>
    <property name='dataSource' ref='dataSource'/>
    <property name='skipUndeclaredResults' value='true'/>
</bean>
<bean id='queryDataTaskExecutor' class='org.springframework.jdbc.core.JdbcOperations'>
    <property name='operations' ref='queryDataExecutor'/>
    <property name='sql' ref='queryDataSql'/>
</bean>

<!-- 执行查询数据的任务 -->
<bean id='queryDataJobDetail' class='org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean'>
    <property name='targetObject' ref='taskExecutor'/>
    <property name='targetMethod' value='execute'/>
    <property name='arguments'>
        <list>
            <ref bean='queryDataTask'/>
            <ref bean='queryDataTaskExecutor'/>
        </list>
    </property>
</bean>
<bean id='queryDataTrigger' class='org.springframework.scheduling.quartz.SimpleTriggerFactoryBean'>
    <property name='jobDetail' ref='queryDataJobDetail'/>
    <property name='repeatInterval' value='1000'/>
    <property name='repeatCount' value='0'/>
</bean>
<bean class='org.springframework.scheduling.quartz.SchedulerFactoryBean'>
    <property name='triggers'>
        <list>
            <ref bean='queryDataTrigger'/>
        </list>
    </property>
</bean>

8、采用基于 XML 的声明式事务管理，删除 score 大于 200 的同学，自己模拟在有异常和无异常的情况下数据表中记录情况：

在 applicationContext.xml 文件中添加以下代码：

<!-- 声明式事务管理 -->
<tx:annotation-driven transaction-manager='transactionManager'/>

<bean id='transactionManager' class='org.springframework.jdbc.datasource.DataSourceTransactionManager'>
    <property name='dataSource' ref='dataSource'/>
</bean>

<!-- 删除数据 -->
<bean id='deleteData' class='org.springframework.jdbc.core.JdbcTemplate' lazy-init='true'>
    <property name='dataSource' ref='dataSource'/>
</bean>
<bean id='deleteDataSql' class='java.lang.String'>
    <constructor-arg value='DELETE FROM student WHERE score > 200'/>
</bean>
<bean id='deleteDataTask' class='org.springframework.jdbc.core.JdbcOperations'>
    <property name='operations' ref='deleteData'/>
    <property name='sql' ref='deleteDataSql'/>
</bean>
<bean id='deleteDataTaskExecutor' class='org.springframework.jdbc.core.JdbcOperations'>
    <property name='operations' ref='deleteData'/>
    <property name='sql' ref='deleteDataSql'/>
</bean>

<!-- 执行删除数据的任务 -->
<bean id='deleteDataJobDetail' class='org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean'>
    <property name='targetObject' ref='taskExecutor'/>
    <property name='targetMethod' value='execute'/>
    <property name='arguments'>
        <list>
            <ref bean='deleteDataTask'/>
            <ref bean='deleteDataTaskExecutor'/>
        </list>
    </property>
</bean>
<bean id='deleteDataTrigger' class='org.springframework.scheduling.quartz.SimpleTriggerFactoryBean'>
    <property name='jobDetail' ref='deleteDataJobDetail'/>
    <property name='repeatInterval' value='1000'/>
    <property name='repeatCount' value='0'/>
</bean>
<bean class='org.springframework.scheduling.quartz.SchedulerFactoryBean'>
    <property name='triggers'>
        <list>
            <ref bean='deleteDataTrigger'/>
        </list>
    </property>
</bean>

<!-- 模拟有异常和无异常的情况 -->
<bean id='dataService' class='com.example.DataServiceImpl'>
    <property name='deleteData' ref='deleteData'/>
</bean>

<bean id='dataServiceProxy' class='org.springframework.transaction.interceptor.TransactionProxyFactoryBean'>
    <property name='transactionManager' ref='transactionManager'/>
    <property name='target' ref='dataService'/>
    <property name='transactionAttributes'>
        <props>
            <prop key='*'>PROPAGATION_REQUIRED</prop>
        </props>
    </property>
</bean>

对应的 DataServiceImpl 类代码如下：

public class DataServiceImpl implements DataService {

    private JdbcTemplate deleteData;

    public void setDeleteData(JdbcTemplate deleteData) {
        this.deleteData = deleteData;
    }

    @Transactional
    public void deleteStudents() {
        deleteData.update('DELETE FROM student WHERE score > 200');
        throw new RuntimeException('模拟异常');
    }

}

9、采用基于 Annotation 的声明式事务管理，删除 score 大于 200 的同学，自己模拟在有异常和无异常的情况下数据表中记录情况：

在 applicationContext.xml 文件中添加以下代码：

<!-- 声明式事务管理 -->
<tx:annotation-driven transaction-manager='transactionManager'/>

<bean id='transactionManager' class='org.springframework.jdbc.datasource.DataSourceTransactionManager'>
    <property name='dataSource' ref='dataSource'/>
</bean>

<!-- 删除数据 -->
<bean id='deleteData' class='org.springframework.jdbc.core.JdbcTemplate' lazy-init='true'>
    <property name='dataSource' ref='dataSource'/>
</bean>
<bean id='deleteDataSql' class='java.lang.String'>
    <constructor-arg value='DELETE FROM student WHERE score > 200'/>
</bean>
<bean id='deleteDataTask' class='org.springframework.jdbc.core.JdbcOperations'>
    <property name='operations' ref='deleteData'/>
    <property name='sql' ref='deleteDataSql'/>
</bean>

<!-- 执行删除数据的任务 -->
<bean id='deleteDataJobDetail' class='org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean'>
    <property name='targetObject' ref='taskExecutor'/>
    <property name='targetMethod' value='execute'/>
    <property name='arguments'>
        <list>
            <ref bean='deleteDataTask'/>
            <ref bean='deleteDataTask'/>
        </list>
    </property>
</bean>
<bean id='deleteDataTrigger' class='org.springframework.scheduling.quartz.SimpleTriggerFactoryBean'>
    <property name='jobDetail' ref='deleteDataJobDetail'/>
    <property name='repeatInterval' value='1000'/>
    <property name='repeatCount' value='0'/>
</bean>
<bean class='org.springframework.scheduling.quartz.SchedulerFactoryBean'>
    <property name='triggers'>
        <list>
            <ref bean='deleteDataTrigger'/>
        </list>
    </property>
</bean>

<!-- 模拟有异常和无异常的情况 -->
<bean id='dataService' class='com.example.DataServiceImpl'>
    <property name='deleteData' ref='deleteData'/>
</bean>

<bean id='dataServiceProxy' class='org.springframework.transaction.interceptor.TransactionProxyFactoryBean'>
    <property name='transactionManager' ref='transactionManager'/>
    <property name='target' ref='dataService'/>
    <property name='transactionAttributes'>
        <props>
            <prop key='deleteStudents'>PROPAGATION_REQUIRED</prop>
        </props>
    </property>
</bean>

对应的 DataServiceImpl 类代码如下：

public class DataServiceImpl implements DataService {

    private JdbcTemplate deleteData;

    public void setDeleteData(JdbcTemplate deleteData) {
        this.deleteData = deleteData;
    }

    @Transactional
    public void deleteStudents() {
        deleteData.update('DELETE FROM student WHERE score > 200');
        throw new RuntimeException('模拟异常');
    }

}
Spring JDBC 实战：使用 JdbcTemplate 操作数据库

原文地址: https://www.cveoy.top/t/topic/nxvw 著作权归作者所有。请勿转载和采集!

免费AI点我,无需注册和登录