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

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添加依赖

<!-- https://mvnrepository.com/artifact/org.springframework.data/spring-data-jpa -->
  <!--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>

 

 

项目结构：

 

 

创建图书实体类：

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;

@Repository
public class BookDAO {
    @Autowired
    private JdbcOperations jdbcOperations;


    public List<Map<String, Object>> getToList() {
        List<Map<String, Object>> maps = jdbcOperations.queryForList("select * from book where id > 0");
        return maps;
    }

    public Map<String, Object> getToMap() {
        String sql = "select * from book where id > ?;";
        Map<String, Object> 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;

@Configuration
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;
    }

    @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.Driver
jdbc.url=jdbc:mariadb://localhost:3306/stu
jdbc.user=root
jdbc.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<Map<String, Object>> toList = bean.getToList();
        System.out.println(toList);
        
    }
}

 

结果：

 

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

 

 

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

测试结果：

1 2 3 4 5 6

------------------------- List ------------------------- [{userName=oukele, password=oukele}, {userName=oukele1, password=oukele}, {userName=JAVA, password=123}] ------------------------- 键值对应 ------------------------- {userName=oukele1, password=oukele} ------------------------ 对象数据 ------------------------------ User{userName='oukele', password='oukele'}

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

数据库数据：

运行效果：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

-------------------------添加------------------------- 十二月 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语法出错 之前的操作全部回滚。

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

 

分类: Spring

标签: Spring

好文要顶 已关注 收藏该文  

追梦滴小蜗牛
关注 - 4
粉丝 - 5

 

 

我在关注他 取消关注

0

0

 

 

 

« 上一篇：使用IDEA搭建一个Spring + AOP (权限管理 ) + Spring MVC + Mybatis的Web项目 (零配置文件)
» 下一篇：Spring整合Hibernate实现Spring Data JPA （简单使用）

posted @ 2018-12-21 10:48 追梦滴小蜗牛 阅读(14) 评论(0) 编辑 收藏

 

 

刷新评论刷新页面返回顶部

发表评论

昵称：

评论内容：

     

 

 退出 订阅评论

 

[Ctrl+Enter快捷键提交]

 

【推荐】超50万VC++源码: 大型组态工控、电力仿真CAD与GIS源码库！
【活动】华为云12.12会员节 云产品1折起 满额送Mate20 点击抢购
【推荐】服务器100%基准CPU性能，1核1G首年168元，限时特惠！

 

相关博文：
· spring+JdbcTemplate简单使用(一)
· 简单Spring整合JdbcTemplate
· spring jdbcTemplate
· Spring JDBCTemplate配置使用
· Spring JDBCTemplate 简单使用

热门推荐：
· 我去暗网里转了转（慎入）
· HTML中<input>和<textarea>的区别
· IntelliJ IDEA最新版配置Tomcat（完整版教程）
· Linux 删除文件夹和文件的命令
· pyCharm-激活码（2018）

 

公告

昵称：追梦滴小蜗牛
园龄：5个月
粉丝：5
关注：4

已关注 -取消

<

2018年12月

>

日

一

二

三

四

五

六

25

26

27

28

29

30

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

1

2

3

4

5

搜索

 

 

 

最新随笔

• 1. 将 对象序列化与反序化 (json格式)
• 2. Java 对象序列化和反序列化 （实现 Serializable 接口）
• 3. Spring 整合 Redis (零配置) 的简单使用
• 4. Redis 链表结构 和 常用命令
• 5. Redis hash结构 和常用命令
• 6. Redis 的简单运算
• 7. Redis 字符串结构和常用命令
• 8. Redis 安装 与 使用
• 9. Java 中 && 和 & 的区别
• 10. Spring整合Hibernate实现Spring Data JPA （简单使用）

我的标签

• Spring MVC(20)
• 软件、插件的安装(11)
• Vue的使用(8)
• JavaScript(8)
• Spring(7)
• Iview(6)
• Redis(5)
• ajax请求(5)
• 设计模式(5)
• Java(3)
• 更多

随笔分类(36)

• ES6 语法
• Iview(6)
• Java 笔记(3)
• JavaScript(8)