Spring JdbcTemplate + transactionTemplate 简单示例 (零配置)

jdbcTemplate简介

　　Spring对数据库的操作在jdbc上面做了深层次的封装，使用spring的注入功能，可以把DataSource注册到JdbcTemplate之中。

　　JdbcTemplate位于 Spring JdbcTemplate + transactionTemplate 简单示例 (零配置) 中。其全限定命名为org.springframework.jdbc.core.JdbcTemplate。要使用JdbcTemlate还需一个这个包包含了一下事务和异常控制。

JdbcTemplate主要提供以下五类方法：

execute方法：可以用于执行任何SQL语句，一般用于执行DDL语句；
update方法及batchUpdate方法：update方法用于执行新增、修改、删除等语句；batchUpdate方法用于执行批处理相关语句；
query方法及queryForXXX方法：用于执行查询相关语句；
call方法：用于执行存储过程、函数相关语句。

添加依赖（spring-jdbc ，数据库驱动，数据库连接池）

         <!--MariaDB 数据库驱动  -->

         <dependency>

             <groupId>org.mariadb.jdbc</groupId>

             <artifactId>mariadb-java-client</artifactId>

             <version>2.3.0</version>

         </dependency>

         <!--c3p0 数据库连接池-->

         <dependency>

             <groupId>com.mchange</groupId>

             <artifactId>c3p0</artifactId>

             <version>0.9.5.2</version>

         </dependency>

            <dependency>

             <groupId>org.springframework</groupId>

             <artifactId>spring-jdbc</artifactId>

             <version>5.1.3.RELEASE</version>

         </dependency>

文件结构：

Spring JdbcTemplate + transactionTemplate 简单示例 (零配置)

entity 包类代码

 package com.oukele.entity;

 public class User {

     private String userName;

     private String password;

     public User() {

     }

     public User(String userName, String password) {

         this.userName = userName;

         this.password = password;

     }

     public String getUserName() {

         return userName;

     }

     public void setUserName(String userName) {

         this.userName = userName;

     }

     public String getPassword() {

         return password;

     }

     public void setPassword(String password) {

         this.password = password;

     }

     @Override

     public String toString() {

         return "User{" +

                 "userName='" + userName + '\'' +

                 ", password='" + password + '\'' +

                 '}';

     }

 }

jdbcTemplate包各类代码

JDBCTemplateConfig 类

 package com.oukele.jdbcTemplate;

 import com.mchange.v2.c3p0.ComboPooledDataSource;

 import org.springframework.context.annotation.Bean;

 import org.springframework.context.annotation.ComponentScan;

 import org.springframework.context.annotation.Configuration;

 import org.springframework.context.annotation.PropertySource;

 import org.springframework.core.env.Environment;

 import org.springframework.jdbc.core.JdbcTemplate;

 import org.springframework.jdbc.datasource.DataSourceTransactionManager;

 import org.springframework.transaction.PlatformTransactionManager;

 import org.springframework.transaction.support.TransactionTemplate;

 import javax.sql.DataSource;

 import java.beans.PropertyVetoException;

 @Configuration

 @PropertySource(value = "classpath:jdbc.properties")//加载资源

 @ComponentScan(basePackages = "com.oukele.jdbcTemplate")//扫描 spring 注解

 public class JDBCTemplateConfig {

     //数据源

     @Bean

     DataSource dataSource(Environment env) throws PropertyVetoException {

         ComboPooledDataSource dataSource = new ComboPooledDataSource();

         dataSource.setDriverClass(env.getProperty("jdbc.driver"));

         dataSource.setJdbcUrl(env.getProperty("jdbc.url"));

         dataSource.setUser(env.getProperty("jdbc.user"));

         dataSource.setPassword(env.getProperty("jdbc.password"));

         return dataSource;

     }

     //jdbc 模板

     @Bean

     JdbcTemplate jdbcTemplate (DataSource dataSource){

         return new JdbcTemplate(dataSource);

     }

     //事务管理器

     @Bean

     DataSourceTransactionManager transactionManager(DataSource dataSource){//事务管理

         return new DataSourceTransactionManager(dataSource);

     }

     //事务模板

     @Bean

     TransactionTemplate transactionTemplate(PlatformTransactionManager platformTransactionManager){

         return new TransactionTemplate(platformTransactionManager);

     }

 }

UserDao类

package com.oukele.jdbcTemplate;

import com.oukele.entity.User;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.jdbc.core.JdbcOperations;

import org.springframework.stereotype.Repository;

import org.springframework.transaction.support.TransactionTemplate;

import java.util.List;

import java.util.Map;

@Repository

public class UserDao {

    @Autowired

    private JdbcOperations jdbcOperations;

    @Autowired

    private TransactionTemplate transactionTemplate;

　　

    public List<Map<String, Object>> getToList () {

        List<Map<String, Object>> maps = jdbcOperations.queryForList("select * from user");

        return maps;

    }

    public Map<String, Object> getToMap () {

        String sql = "select * from user where userName = ?";

        Map<String, Object> ret = jdbcOperations.queryForMap(sql, "oukele1");

        return ret;

    }

    public User getToUser () {

        String sql = "select * from user where userName = 'oukele'";

        User user = jdbcOperations.queryForObject(

                sql,

                (rs, rowNum) -> new User(rs.getString(1),rs.getString(2))

        );

        return user;

    }

    public int create(String userName, String password) {

        transactionTemplate.execute(status ->{

            String sql = "insert into user (userName, password) values (?, ?)";

            String sq2 = "insert into user (userName, passord) values (?, ?)";

            jdbcOperations.update(sq2,userName,password);

            return jdbcOperations.update(sql, userName, password);

        });

        return 0;

    }

}

Main类 (测试类)

 package com.oukele.jdbcTemplate;

 import com.oukele.entity.User;

 import org.springframework.context.annotation.AnnotationConfigApplicationContext;

 public class Main {

     public static void main(String[] args) {

         AnnotationConfigApplicationContext context = new AnnotationConfigApplicationContext(JDBCTemplateConfig.class);

         UserDao dao = context.getBean(UserDao.class);

         System.out.println("------------------------- List -------------------------");

         System.out.println(dao.getToList());

         System.out.println("-------------------------键值对应-------------------------");

         System.out.println(dao.getToMap());

         System.out.println();

         User user = dao.getToUser();

         System.out.println(user);

         System.out.println("-------------------------添加-------------------------");

         System.out.println(dao.create("JAVA", "123"));

     }

 }

jdbc.properties 资源文件

 jdbc.driver=org.mariadb.jdbc.Driver

 jdbc.url=jdbc:mariadb://localhost:3306/test

 jdbc.user=oukele

 jdbc.password=oukele

测试结果：

Spring JdbcTemplate + transactionTemplate 简单示例 (零配置)

------------------------- List -------------------------

[{userName=oukele, password=oukele}, {userName=oukele1, password=oukele}, {userName=JAVA, password=123}]

------------------------- 键值对应 -------------------------

{userName=oukele1, password=oukele}

------------------------ 对象数据 ------------------------------

User{userName='oukele', password='oukele'}

添加的时候，添加事务管理

Spring JdbcTemplate + transactionTemplate 简单示例 (零配置)

数据库数据：

Spring JdbcTemplate + transactionTemplate 简单示例 (零配置)

运行效果：

-------------------------添加-------------------------

十二月 21, 2018 10:42:58 上午 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource

信息: 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 -> 1hge1v29z1lc434s1wp62cl|77167fb7, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> org.mariadb.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hge1v29z1lc434s1wp62cl|77167fb7, idleConnectionTestPeriod -> 0, initialPoolSize -> 3, jdbcUrl -> jdbc:mariadb://localhost:3306/test, 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 ]

Exception in thread "main" org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [insert into user (userName, passord) values ('JavaScript', '2134')]; SQL state [null]; error code [0]; Could not set parameter at position 1 (values was 'JAVA1')

Query - conn:124(M)  - "insert into user (userName, passord) values ('JavaScript', '2134')"; nested exception is java.sql.SQLException: Could not set parameter at position 1 (values was 'JAVA1')

Query - conn:124(M)  - "insert into user (userName, passord) values ('JavaScript', '2134')"

	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89)

	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)

	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)

	at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1444)

	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:632)

	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:862)

	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:917)

	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:927)

	at com.oukele.jdbcTemplate.UserDao.lambda$create$1(UserDao.java:47)

	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)

	at com.oukele.jdbcTemplate.UserDao.create(UserDao.java:44)

	at com.oukele.jdbcTemplate.Main.main(Main.java:19)

Caused by: java.sql.SQLException: Could not set parameter at position 1 (values was 'JAVA1')

Query - conn:124(M)  - "insert into user (userName, passord) values ('JavaScript', '2134')"

	at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getSqlException(ExceptionMapper.java:271)

	at org.mariadb.jdbc.MariaDbPreparedStatementClient.setParameter(MariaDbPreparedStatementClient.java:480)

	at org.mariadb.jdbc.BasePrepareStatement.setString(BasePrepareStatement.java:1533)

	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.setString(NewProxyPreparedStatement.java:540)

	at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:400)

	at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:232)

	at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:163)

	at org.springframework.jdbc.core.ArgumentPreparedStatementSetter.doSetValue(ArgumentPreparedStatementSetter.java:69)

	at org.springframework.jdbc.core.ArgumentPreparedStatementSetter.setValues(ArgumentPreparedStatementSetter.java:50)

	at org.springframework.jdbc.core.JdbcTemplate.lambda$update$0(JdbcTemplate.java:865)

	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617)

	... 7 more

　　数据库数据显示：

Spring JdbcTemplate + transactionTemplate 简单示例 (零配置)

其中有一条sql语法出错之前的操作全部回滚。

示例源码下载：https://github.com/oukele/Spring-JdbcTemplate-transactionTemplate

秒客网

Spring JdbcTemplate + transactionTemplate 简单示例 (零配置)

jdbcTemplate简介

JdbcTemplate主要提供以下五类方法：

添加依赖（spring-jdbc ，数据库驱动，数据库连接池）

文件结构：

entity 包类代码

jdbcTemplate包各类代码

JDBCTemplateConfig 类

UserDao类

Main类 (测试类)

jdbc.properties 资源文件

相关文章

Spring JdbcTemplate + transactionTemplate 简单示例 (零配置)

jdbcTemplate简介

JdbcTemplate主要提供以下五类方法：

添加依赖 （spring-jdbc ，数据库驱动，数据库连接池）

文件结构：

entity 包 类 代码

jdbcTemplate包 各类 代码

JDBCTemplateConfig 类

UserDao类

Main类 (测试类)

jdbc.properties 资源文件

相关文章

添加依赖（spring-jdbc ，数据库驱动，数据库连接池）

entity 包类代码

jdbcTemplate包各类代码