Spring JdbcTemplate + transactionTemplate 简单示例 (零配置)-白红宇

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

阅读量：3934 次

发布时间：2019-05-23

本文共 14977 字，大约阅读时间需要 49 分钟。

jdbcTemplate简介

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

　　JdbcTemplate位于中。其全限定命名为org.springframework.jdbc.core.JdbcTemplate。要使用JdbcTemlate还需一个这个包包含了一下事务和异常控制。

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

execute方法：可以用于执行任何SQL语句，一般用于执行DDL语句；

update方法及batchUpdate方法：update方法用于执行新增、修改、删除等语句；batchUpdate方法用于执行批处理相关语句；

query方法及queryForXXX方法：用于执行查询相关语句；

call方法：用于执行存储过程、函数相关语句。

首先在pom.xml添加依赖


         
                 
                     
        
         org.mariadb.jdbc
                      
        
         mariadb-java-client
                      
        
         2.3.0
                  
                  
                
                    
        
         com.mchange
                     
        
         c3p0
                     
        
         0.9.5.2
                 
                    
                    
        
         org.springframework
                     
        
         spring-jdbc
                     
        
         5.1.3.RELEASE

项目结构：

创建图书实体类：

package the_template_data_jpa;public class Book {    private int id;    private String bookname;    private float price;    public Book(int id, String bookname, float price) {        this.id = id;        this.bookname = bookname;        this.price = price;    }    public int getId() {        return id;    }    public void setId(int id) {        this.id = id;    }    public String getBookname() {        return bookname;    }    public void setBookname(String bookname) {        this.bookname = bookname;    }    public float getPrice() {        return price;    }    public void setPrice(float price) {        this.price = price;    }    @Override    public String toString() {        return "Book{" +                "id=" + id +                ", bookname='" + bookname + '\'' +                ", price=" + price +                '}';    }}

创建BookDAO

package the_template_data_jpa;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;@Repositorypublic class BookDAO {    @Autowired    private JdbcOperations jdbcOperations;    public List
        
         > getToList() {        List
         
          
           > maps = jdbcOperations.queryForList("select * from book where id > 0");        return maps;    }    public Map
           
             getToMap() {        String sql = "select * from book where id > ?;";        Map
            
              ret = jdbcOperations.queryForMap(sql,2); return ret; } public Book getToBook() { String sql = "select * from book where id > 2;"; Book book = jdbcOperations.queryForObject( sql, (rs, rowNum) -> new Book(rs.getInt(1), rs.getString(2), rs.getFloat(3)) ); return book; } public void create(String bookname, int price) { String sql = " insert into book (bookname,price) values (?,?)"; int javaWeb = jdbcOperations.update(sql, bookname, price); if (javaWeb > 0) { System.out.println("数据插入成功"); } }}

jdbcTemplate包各类代码

JDBCTemplateConfig 类

package the_template_data_jpa;import com.mchange.v2.c3p0.ComboPooledDataSource;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.Configuration;import org.springframework.core.env.Environment;import org.springframework.jdbc.core.JdbcTemplate;import javax.sql.DataSource;import java.beans.PropertyVetoException;@Configurationpublic 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;    }    @Bean    JdbcTemplate jdbcTemplate (DataSource dataSource){        return new JdbcTemplate(dataSource);    }}

配置：扫描

package the_template_data_jpa;import org.springframework.context.annotation.ComponentScan;import org.springframework.context.annotation.Configuration;import org.springframework.context.annotation.Import;import org.springframework.context.annotation.PropertySource;@Configuration //声明当前配置类@ComponentScan(basePackages = "the_template_data_jpa") // 扫描当前包 使用 spring 注解@PropertySource("classpath:application.properties")//加载 资源文件@Import({JDBCTemplateConfig.class})//扫描 使用 jpa 注解的接口public class SpringConfig {}

jdbc.properties 资源文件：

jdbc.driver=org.mariadb.jdbc.Driverjdbc.url=jdbc:mariadb://localhost:3306/stujdbc.user=rootjdbc.password=666666

测试：

package the_template_data_jpa;        import org.springframework.context.annotation.AnnotationConfigApplicationContext;        import java.util.List;        import java.util.Map;public class Main {    public static void main(String[] args) {        AnnotationConfigApplicationContext Ioc = new AnnotationConfigApplicationContext(SpringConfig.class);        BookDAO bean = Ioc.getBean(BookDAO.class);        List
        
         > toList = bean.getToList();        System.out.println(toList);    }}

结果：

转存失败

源码地址:https://github.com/nongzihong/Spring_Data_jps_Hibernate

1 package com.oukele.jdbcTemplate; 2  3 import com.mchange.v2.c3p0.ComboPooledDataSource; 4 import org.springframework.context.annotation.Bean; 5 import org.springframework.context.annotation.ComponentScan; 6 import org.springframework.context.annotation.Configuration; 7 import org.springframework.context.annotation.PropertySource; 8 import org.springframework.core.env.Environment; 9 import org.springframework.jdbc.core.JdbcTemplate;10 import org.springframework.jdbc.datasource.DataSourceTransactionManager;11 import org.springframework.transaction.PlatformTransactionManager;12 import org.springframework.transaction.support.TransactionTemplate;13 14 import javax.sql.DataSource;15 import java.beans.PropertyVetoException;16 17 @Configuration18 @PropertySource(value = "classpath:jdbc.properties")//加载资源19 @ComponentScan(basePackages = "com.oukele.jdbcTemplate")//扫描 spring 注解20 public class JDBCTemplateConfig {21 22     //数据源23     @Bean24     DataSource dataSource(Environment env) throws PropertyVetoException {25         ComboPooledDataSource dataSource = new ComboPooledDataSource();26         dataSource.setDriverClass(env.getProperty("jdbc.driver"));27         dataSource.setJdbcUrl(env.getProperty("jdbc.url"));28         dataSource.setUser(env.getProperty("jdbc.user"));29         dataSource.setPassword(env.getProperty("jdbc.password"));30         return dataSource;31     }32     //jdbc 模板33     @Bean34     JdbcTemplate jdbcTemplate (DataSource dataSource){35         return new JdbcTemplate(dataSource);36     }37     //事务管理器38     @Bean39     DataSourceTransactionManager transactionManager(DataSource dataSource){//事务管理40         return new DataSourceTransactionManager(dataSource);41     }42     //事务模板43     @Bean44     TransactionTemplate transactionTemplate(PlatformTransactionManager platformTransactionManager){45         return new TransactionTemplate(platformTransactionManager);46     }47 48 49 50 51 52 }

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;@Repositorypublic class UserDao {    @Autowired    private JdbcOperations jdbcOperations;    @Autowired    private TransactionTemplate transactionTemplate;　　    public List
               
                > getToList () {        List
                
                 
                  > maps = jdbcOperations.queryForList("select * from user");        return maps;    }    public Map
                  
                    getToMap () {        String sql = "select * from user where userName = ?";        Map
                   
                     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类 (测试类)

1 package com.oukele.jdbcTemplate; 2  3 import com.oukele.entity.User; 4 import org.springframework.context.annotation.AnnotationConfigApplicationContext; 5  6 public class Main { 7     public static void main(String[] args) { 8         AnnotationConfigApplicationContext context = new AnnotationConfigApplicationContext(JDBCTemplateConfig.class); 9         UserDao dao = context.getBean(UserDao.class);10 11         System.out.println("------------------------- List -------------------------");12         System.out.println(dao.getToList());13         System.out.println("-------------------------键值对应-------------------------");14         System.out.println(dao.getToMap());15         System.out.println();16         User user = dao.getToUser();17         System.out.println(user);18         System.out.println("-------------------------添加-------------------------");19         System.out.println(dao.create("JAVA", "123"));20 21     }22 }

jdbc.properties 资源文件

1 jdbc.driver=org.mariadb.jdbc.Driver2 jdbc.url=jdbc:mariadb://localhost:3306/test3 jdbc.user=oukele4 jdbc.password=oukele

测试结果：

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

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

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

{userName=oukele1, password=oukele}

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

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

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

转存失败

数据库数据：

运行效果：

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

十二月 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

　　数据库数据显示：

转存失败

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

示例源码下载：

转载地址：http://bthgn.baihongyu.com/

你可能感兴趣的文章

预训练语言模型 | (2) transformer

预训练语言模型 | (5) StructBert和RoBerta

查看>>

GNN在文本分类上的应用 | (1) TextGCN

查看>>

GNN在文本分类上的应用 | (2) Text Level Graph Neural Network for Text Classification

查看>>

GNN在文本分类上的应用 | (3) TensorGCN

查看>>

SemEval2019Task3_ERC | (1) Affect Classification in Dialogue using Attentive BiLSTMs

查看>>

SemEval2019Task3_ERC | (2) Attentive Conversation Modeling for Emotion Detection and Classification

查看>>

SemEval2019Task3_ERC | (3) Using Deep Sentiment Analysis Models and Transfer Learning for ERC

查看>>

SemEval2019Task3_ERC | (4) Emotion detection in conversations through Tweets,CNN and LSTM DNN

查看>>

Python杂谈 | (15) 使用Pycharm执行带命令行参数的脚本

查看>>

从源码分析：分析Java中的StringBuilder

查看>>

Linux(Ubuntu18）中启动ssh时的报错

查看>>

Java中的左移时的负数问题

查看>>

从数组形式创建一棵树（用于leetcode测试）

查看>>

线程进阶：多任务处理（17）——Java中的锁（Unsafe基础）

查看>>

Spring/Boot/Cloud系列知识（1）——开篇

查看>>

线程基础：多任务处理（15）——Fork/Join框架（要点2）

查看>>

线程基础：多任务处理（16）——Fork/Join框架（排序算法性能补充）

查看>>

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

jdbcTemplate包 各类 代码

jdbcTemplate包各类代码