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
![复制代码](https://i-blog.csdnimg.cn/blog_migrate/3261cce84d531bb17b20112813d07e17.gif)
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 @Configuration
18 @PropertySource(value = "classpath:jdbc.properties")//加载资源
19 @ComponentScan(basePackages = "com.oukele.jdbcTemplate")//扫描 spring 注解
20 public class JDBCTemplateConfig {
21
22 //数据源
23 @Bean
24 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 @Bean
34 JdbcTemplate jdbcTemplate (DataSource dataSource){
35 return new JdbcTemplate(dataSource);
36 }
37 //事务管理器
38 @Bean
39 DataSourceTransactionManager transactionManager(DataSource dataSource){//事务管理
40 return new DataSourceTransactionManager(dataSource);
41 }
42 //事务模板
43 @Bean
44 TransactionTemplate transactionTemplate(PlatformTransactionManager platformTransactionManager){
45 return new TransactionTemplate(platformTransactionManager);
46 }
47
48
49
50
51
52 }
![复制代码](https://i-blog.csdnimg.cn/blog_migrate/3261cce84d531bb17b20112813d07e17.gif)
UserDao类
![复制代码](https://i-blog.csdnimg.cn/blog_migrate/3261cce84d531bb17b20112813d07e17.gif)
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;
}
}
![复制代码](https://i-blog.csdnimg.cn/blog_migrate/3261cce84d531bb17b20112813d07e17.gif)
Main类 (测试类)
![复制代码](https://i-blog.csdnimg.cn/blog_migrate/3261cce84d531bb17b20112813d07e17.gif)
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 }
![复制代码](https://i-blog.csdnimg.cn/blog_migrate/3261cce84d531bb17b20112813d07e17.gif)
jdbc.properties 资源文件
1 jdbc.driver=org.mariadb.jdbc.Driver
2 jdbc.url=jdbc:mariadb://localhost:3306/test
3 jdbc.user=oukele
4 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