| tags | projects |
|---|---|
This guide walks you through the process of wrapping database operations with non-intrusive transactions.
You’ll build a simple JDBC application wherein you make database operations transactional without having to write specialized JDBC code.
First, use the BookingService class to create a JDBC-based service that books people into the system by name.
src/main/java/hello/BookingService.java
link:complete/src/main/java/hello/BookingService.java[role=include]The code has an autowired JdbcTemplate, a handy template class that does all the database interactions needed by the code below.
You also have a book method aimed at booking multiple people. It loops through the list of people, and for each person, inserts them into the BOOKINGS table using the JdbcTemplate. This method is tagged with @Transactional, meaning that any failure causes the entire operation to roll back to its previous state, and to re-throw the original exception. This means that none of the people will be added to BOOKINGS if one person fails to be added.
You also have a findAllBookings method to query the database. Each row fetched from the database is converted into a String and then assembled into a List.
src/main/java/hello/Application.java
link:complete/src/main/java/hello/Application.java[role=include]Your application has actually zero configuration. Spring Boot will detect spring-jdbc on the classpath and h2 and will create a DataSource and a JdbcTemplate for you automatically. Because such infrastructure is now available and you have no dedicated configuration, a DataSourceTransactionManager will also be created for you: this is the component that intercepts the @Transactional annotated method (e.g. the book on BookingService). The BookingService is detected via classpath scanning.
Another Spring Boot feature demonstrated in this guide is the ability to initialize the schema on startup:
src/main/resources/schema.sql
link:complete/src/main/resources/schema.sql[role=include]There is also a CommandLineRunner that injects the BookingService and showcases various transactional use cases.
src/main/java/hello/AppRunner.java
link:complete/src/main/java/hello/AppRunner.java[role=include]You should see the following output:
2016-09-01 09:47:55.031 INFO 16911 --- [ main] o.s.jdbc.datasource.init.ScriptUtils : Executing SQL script from URL [file:/Users/foo/workspace/gs-managing-transactions/target/classes/schema.sql] 2016-09-01 09:47:55.052 INFO 16911 --- [ main] o.s.jdbc.datasource.init.ScriptUtils : Executed SQL script from URL [file:/Users/foo/workspace/gs-managing-transactions/target/classes/schema.sql] in 21 ms. 2016-09-01 09:47:55.259 INFO 16911 --- [ main] o.s.j.e.a.AnnotationMBeanExporter : Registering beans for JMX exposure on startup 2016-09-01 09:47:55.282 INFO 16911 --- [ main] hello.BookingService : Booking Alice in a seat... 2016-09-01 09:47:55.292 INFO 16911 --- [ main] hello.BookingService : Booking Bob in a seat... 2016-09-01 09:47:55.292 INFO 16911 --- [ main] hello.BookingService : Booking Carol in a seat... 2016-09-01 09:47:55.306 INFO 16911 --- [ main] hello.AppRunner : Alice, Bob and Carol have been booked 2016-09-01 09:47:55.306 INFO 16911 --- [ main] hello.BookingService : Booking Chris in a seat... 2016-09-01 09:47:55.306 INFO 16911 --- [ main] hello.BookingService : Booking Samuel in a seat... 2016-09-01 09:47:55.312 INFO 16911 --- [ main] o.s.b.f.xml.XmlBeanDefinitionReader : Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml] 2016-09-01 09:47:55.419 INFO 16911 --- [ main] o.s.jdbc.support.SQLErrorCodesFactory : SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase, Hana] 2016-09-01 09:47:55.424 INFO 16911 --- [ main] hello.AppRunner : v--- The following exception is expect because 'Samuel' is too big for the DB ---v 2016-09-01 09:47:55.424 ERROR 16911 --- [ main] hello.AppRunner : PreparedStatementCallback; SQL [insert into BOOKINGS(FIRST_NAME) values (?)]; Value too long for column "FIRST_NAME VARCHAR(5) NOT NULL": "'Samuel' (6)"; SQL statement: insert into BOOKINGS(FIRST_NAME) values (?) [22001-192]; nested exception is org.h2.jdbc.JdbcSQLException: Value too long for column "FIRST_NAME VARCHAR(5) NOT NULL": "'Samuel' (6)"; SQL statement: insert into BOOKINGS(FIRST_NAME) values (?) [22001-192] 2016-09-01 09:47:55.424 INFO 16911 --- [ main] hello.AppRunner : So far, Alice is booked. 2016-09-01 09:47:55.424 INFO 16911 --- [ main] hello.AppRunner : So far, Bob is booked. 2016-09-01 09:47:55.424 INFO 16911 --- [ main] hello.AppRunner : So far, Carol is booked. 2016-09-01 09:47:55.424 INFO 16911 --- [ main] hello.AppRunner : You shouldn't see Chris or Samuel. Samuel violated DB constraints, and Chris was rolled back in the same TX 2016-09-01 09:47:55.425 INFO 16911 --- [ main] hello.BookingService : Booking Buddy in a seat... 2016-09-01 09:47:55.425 INFO 16911 --- [ main] hello.BookingService : Booking null in a seat... 2016-09-01 09:47:55.427 INFO 16911 --- [ main] hello.AppRunner : v--- The following exception is expect because null is not valid for the DB ---v 2016-09-01 09:47:55.427 ERROR 16911 --- [ main] hello.AppRunner : PreparedStatementCallback; SQL [insert into BOOKINGS(FIRST_NAME) values (?)]; NULL not allowed for column "FIRST_NAME"; SQL statement: insert into BOOKINGS(FIRST_NAME) values (?) [23502-192]; nested exception is org.h2.jdbc.JdbcSQLException: NULL not allowed for column "FIRST_NAME"; SQL statement: insert into BOOKINGS(FIRST_NAME) values (?) [23502-192] 2016-09-01 09:47:55.427 INFO 16911 --- [ main] hello.AppRunner : So far, Alice is booked. 2016-09-01 09:47:55.427 INFO 16911 --- [ main] hello.AppRunner : So far, Bob is booked. 2016-09-01 09:47:55.427 INFO 16911 --- [ main] hello.AppRunner : So far, Carol is booked. 2016-09-01 09:47:55.427 INFO 16911 --- [ main] hello.AppRunner : You shouldn't see Buddy or null. null violated DB constraints, and Buddy was rolled back in the same TX
The BOOKINGS table has two constraints on the first_name column:
-
Names cannot be longer than five characters.
-
Names cannot be null.
The first three names inserted are Alice, Bob, and Carol. The application asserts that three people were added to that table. If that had not worked, the application would have exited early.
Next, another booking is done for Chris and Samuel. Samuel’s name is deliberately too long, forcing an insert error. Transactional behavior stipulates that both Chris and Samuel; that is, this transaction, should be rolled back. Thus there should still be only three people in that table, which the assertion demonstrates.
Finally, Buddy and null are booked. As the output shows, null causes a rollback as well, leaving the same three people booked.
Congratulations! You’ve just used Spring to develop a simple JDBC application wrapped with non-intrusive transactions.
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent