Skip to content

Commit e685ca6

Browse files
alessiostallamaibin
alessiostalla
authored and
maibin
committed
BAEL-1560 Code for the article: JDBC with Groovy (eugenp#3707)
1 parent 612159c commit e685ca6

4 files changed

Lines changed: 352 additions & 0 deletions

File tree

‎core-groovy/README.md‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,4 @@
1+
# Groovy
2+
3+
## Relevant articles:
4+

‎core-groovy/pom.xml‎

Lines changed: 118 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,118 @@
1+
<?xml version="1.0" encoding="UTF-8"?>
2+
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
3+
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
4+
<modelVersion>4.0.0</modelVersion>
5+
6+
<artifactId>core-groovy</artifactId>
7+
<version>1.0-SNAPSHOT</version>
8+
<packaging>jar</packaging>
9+
10+
<parent>
11+
<groupId>com.baeldung</groupId>
12+
<artifactId>parent-modules</artifactId>
13+
<version>1.0.0-SNAPSHOT</version>
14+
</parent>
15+
16+
<repositories>
17+
<repository>
18+
<id>central</id>
19+
<url>http://jcenter.bintray.com</url>
20+
</repository>
21+
</repositories>
22+
23+
<dependencies>
24+
<dependency>
25+
<groupId>org.codehaus.groovy</groupId>
26+
<artifactId>groovy</artifactId>
27+
<version>2.4.13</version>
28+
</dependency>
29+
<dependency>
30+
<groupId>org.codehaus.groovy</groupId>
31+
<artifactId>groovy-sql</artifactId>
32+
<version>2.4.13</version>
33+
</dependency>
34+
<dependency>
35+
<groupId>org.junit.jupiter</groupId>
36+
<artifactId>junit-jupiter-engine</artifactId>
37+
<version>${junit.jupiter.version}</version>
38+
<scope>test</scope>
39+
</dependency>
40+
<dependency>
41+
<groupId>org.junit.platform</groupId>
42+
<artifactId>junit-platform-runner</artifactId>
43+
<version>${junit.platform.version}</version>
44+
<scope>test</scope>
45+
</dependency>
46+
<dependency>
47+
<groupId>org.hsqldb</groupId>
48+
<artifactId>hsqldb</artifactId>
49+
<version>2.4.0</version>
50+
<scope>test</scope>
51+
</dependency>
52+
</dependencies>
53+
54+
<build>
55+
<plugins>
56+
<plugin>
57+
<groupId>org.codehaus.gmavenplus</groupId>
58+
<artifactId>gmavenplus-plugin</artifactId>
59+
<version>1.6</version>
60+
<executions>
61+
<execution>
62+
<goals>
63+
<goal>addSources</goal>
64+
<goal>addTestSources</goal>
65+
<goal>compile</goal>
66+
<goal>compileTests</goal>
67+
</goals>
68+
</execution>
69+
</executions>
70+
</plugin>
71+
<plugin>
72+
<artifactId>maven-surefire-plugin</artifactId>
73+
<version>${maven-surefire-plugin.version}</version>
74+
</plugin>
75+
<plugin>
76+
<artifactId>maven-failsafe-plugin</artifactId>
77+
<version>2.19.1</version>
78+
<dependencies>
79+
<dependency>
80+
<groupId>org.junit.platform</groupId>
81+
<artifactId>junit-platform-surefire-provider</artifactId>
82+
<version>${junit.platform.version}</version>
83+
</dependency>
84+
</dependencies>
85+
<executions>
86+
<execution>
87+
<id>junit5</id>
88+
<goals>
89+
<goal>integration-test</goal>
90+
<goal>verify</goal>
91+
</goals>
92+
<configuration>
93+
<includes>
94+
<include>**/*Test5.java</include>
95+
</includes>
96+
</configuration>
97+
</execution>
98+
</executions>
99+
</plugin>
100+
</plugins>
101+
</build>
102+
103+
<properties>
104+
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
105+
<kotlin-maven-plugin.version>1.1.2</kotlin-maven-plugin.version>
106+
<kotlin-test-junit.version>1.1.2</kotlin-test-junit.version>
107+
<kotlin-stdlib.version>1.1.2</kotlin-stdlib.version>
108+
<kotlin-reflect.version>1.1.2</kotlin-reflect.version>
109+
<kotlinx.version>0.15</kotlinx.version>
110+
<mockito-kotlin.version>1.5.0</mockito-kotlin.version>
111+
112+
<junit.jupiter.version>5.0.0</junit.jupiter.version>
113+
<junit.platform.version>1.0.0</junit.platform.version>
114+
<junit.vintage.version>4.12.0</junit.vintage.version>
115+
<junit4.version>4.12</junit4.version>
116+
</properties>
117+
118+
</project>

‎core-groovy/src/test/groovy/com/baeldung/groovy/sql/SqlTest.groovy‎

Lines changed: 229 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,229 @@
1+
package com.baeldung.groovy.sql
2+
3+
import groovy.sql.GroovyResultSet
4+
import groovy.sql.GroovyRowResult
5+
import groovy.sql.Sql
6+
import groovy.transform.CompileStatic
7+
import static org.junit.Assert.*
8+
import org.junit.Test
9+
10+
class SqlTest {
11+
12+
final Map dbConnParams = [url: 'jdbc:hsqldb:mem:testDB', user: 'sa', password: '', driver: 'org.hsqldb.jdbc.JDBCDriver']
13+
14+
@Test
15+
void whenNewSqlInstance_thenDbIsAccessed() {
16+
def sql = Sql.newInstance(dbConnParams)
17+
sql.close()
18+
sql.close()
19+
}
20+
21+
@Test
22+
void whenTableDoesNotExist_thenSelectFails() {
23+
try {
24+
Sql.withInstance(dbConnParams) { Sql sql ->
25+
sql.eachRow('select * from PROJECT') {}
26+
}
27+
28+
fail("An exception should have been thrown")
29+
} catch (ignored) {
30+
//Ok
31+
}
32+
}
33+
34+
@Test
35+
void whenTableCreated_thenSelectIsPossible() {
36+
Sql.withInstance(dbConnParams) { Sql sql ->
37+
def result = sql.execute 'create table PROJECT_1 (id integer not null, name varchar(50), url varchar(100))'
38+
39+
assertEquals(0, sql.updateCount)
40+
assertFalse(result)
41+
42+
result = sql.execute('select * from PROJECT_1')
43+
44+
assertTrue(result)
45+
}
46+
}
47+
48+
@Test
49+
void whenIdentityColumn_thenInsertReturnsNewId() {
50+
Sql.withInstance(dbConnParams) { Sql sql ->
51+
sql.execute 'create table PROJECT_2 (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100))'
52+
def ids = sql.executeInsert("INSERT INTO PROJECT_2 (NAME, URL) VALUES ('tutorials', 'github.com/eugenp/tutorials')")
53+
54+
assertEquals(0, ids[0][0])
55+
56+
ids = sql.executeInsert("INSERT INTO PROJECT_2 (NAME, URL) VALUES ('REST with Spring', 'github.com/eugenp/REST-With-Spring')")
57+
58+
assertEquals(1, ids[0][0])
59+
}
60+
}
61+
62+
@Test
63+
void whenUpdate_thenNumberOfAffectedRows() {
64+
Sql.withInstance(dbConnParams) { Sql sql ->
65+
sql.execute 'create table PROJECT_3 (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100))'
66+
sql.executeInsert("INSERT INTO PROJECT_3 (NAME, URL) VALUES ('tutorials', 'github.com/eugenp/tutorials')")
67+
sql.executeInsert("INSERT INTO PROJECT_3 (NAME, URL) VALUES ('REST with Spring', 'github.com/eugenp/REST-With-Spring')")
68+
def count = sql.executeUpdate("UPDATE PROJECT_3 SET URL = 'https://' + URL")
69+
70+
assertEquals(2, count)
71+
}
72+
}
73+
74+
@Test
75+
void whenEachRow_thenResultSetHasProperties() {
76+
Sql.withInstance(dbConnParams) { Sql sql ->
77+
sql.execute 'create table PROJECT_4 (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100))'
78+
sql.executeInsert("INSERT INTO PROJECT_4 (NAME, URL) VALUES ('tutorials', 'https://github.com/eugenp/tutorials')")
79+
sql.executeInsert("INSERT INTO PROJECT_4 (NAME, URL) VALUES ('REST with Spring', 'https://github.com/eugenp/REST-With-Spring')")
80+
81+
sql.eachRow("SELECT * FROM PROJECT_4") { GroovyResultSet rs ->
82+
assertNotNull(rs.name)
83+
assertNotNull(rs.url)
84+
assertNotNull(rs[0])
85+
assertNotNull(rs[1])
86+
assertNotNull(rs[2])
87+
assertEquals(rs.name, rs['name'])
88+
assertEquals(rs.url, rs['url'])
89+
}
90+
}
91+
}
92+
93+
@Test
94+
void whenPagination_thenSubsetIsReturned() {
95+
Sql.withInstance(dbConnParams) { Sql sql ->
96+
sql.execute 'create table PROJECT_5 (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100))'
97+
sql.executeInsert("INSERT INTO PROJECT_5 (NAME, URL) VALUES ('tutorials', 'github.com/eugenp/tutorials')")
98+
sql.executeInsert("INSERT INTO PROJECT_5 (NAME, URL) VALUES ('REST with Spring', 'github.com/eugenp/REST-With-Spring')")
99+
def rows = sql.rows('SELECT * FROM PROJECT_5 ORDER BY NAME', 1, 1)
100+
101+
assertEquals(1, rows.size())
102+
assertEquals('REST with Spring', rows[0].name)
103+
}
104+
}
105+
106+
@Test
107+
void whenParameters_thenReplacement() {
108+
Sql.withInstance(dbConnParams) { Sql sql ->
109+
sql.execute 'create table PROJECT_6 (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100))'
110+
sql.execute('INSERT INTO PROJECT_6 (NAME, URL) VALUES (?, ?)', 'tutorials', 'github.com/eugenp/tutorials')
111+
sql.execute("INSERT INTO PROJECT_6 (NAME, URL) VALUES (:name, :url)", [name: 'REST with Spring', url: 'github.com/eugenp/REST-With-Spring'])
112+
113+
def rows = sql.rows("SELECT * FROM PROJECT_6 WHERE NAME = 'tutorials'")
114+
115+
assertEquals(1, rows.size())
116+
117+
rows = sql.rows("SELECT * FROM PROJECT_6 WHERE NAME = 'REST with Spring'")
118+
119+
assertEquals(1, rows.size())
120+
}
121+
}
122+
123+
@Test
124+
void whenParametersInGString_thenReplacement() {
125+
Sql.withInstance(dbConnParams) { Sql sql ->
126+
sql.execute 'create table PROJECT_7 (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100))'
127+
sql.execute "INSERT INTO PROJECT_7 (NAME, URL) VALUES (${'tutorials'}, ${'github.com/eugenp/tutorials'})"
128+
def name = 'REST with Spring'
129+
def url = 'github.com/eugenp/REST-With-Spring'
130+
sql.execute "INSERT INTO PROJECT_7 (NAME, URL) VALUES (${name}, ${url})"
131+
132+
def rows = sql.rows("SELECT * FROM PROJECT_7 WHERE NAME = 'tutorials'")
133+
134+
assertEquals(1, rows.size())
135+
136+
rows = sql.rows("SELECT * FROM PROJECT_7 WHERE NAME = 'REST with Spring'")
137+
138+
assertEquals(1, rows.size())
139+
}
140+
}
141+
142+
@Test
143+
void whenTransactionRollback_thenNoDataInserted() {
144+
Sql.withInstance(dbConnParams) { Sql sql ->
145+
sql.withTransaction {
146+
sql.execute 'create table PROJECT_8 (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100))'
147+
sql.executeInsert("INSERT INTO PROJECT_8 (NAME, URL) VALUES ('tutorials', 'https://github.com/eugenp/tutorials')")
148+
sql.executeInsert("INSERT INTO PROJECT_8 (NAME, URL) VALUES ('REST with Spring', 'https://github.com/eugenp/REST-With-Spring')")
149+
sql.rollback()
150+
151+
def rows = sql.rows("SELECT * FROM PROJECT_8")
152+
153+
assertEquals(0, rows.size())
154+
}
155+
}
156+
}
157+
158+
@Test
159+
void whenTransactionRollbackThenCommit_thenOnlyLastInserted() {
160+
Sql.withInstance(dbConnParams) { Sql sql ->
161+
sql.withTransaction {
162+
sql.execute 'create table PROJECT_9 (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100))'
163+
sql.executeInsert("INSERT INTO PROJECT_9 (NAME, URL) VALUES ('tutorials', 'https://github.com/eugenp/tutorials')")
164+
sql.rollback()
165+
sql.executeInsert("INSERT INTO PROJECT_9 (NAME, URL) VALUES ('REST with Spring', 'https://github.com/eugenp/REST-With-Spring')")
166+
sql.rollback()
167+
sql.executeInsert("INSERT INTO PROJECT_9 (NAME, URL) VALUES ('tutorials', 'https://github.com/eugenp/tutorials')")
168+
sql.executeInsert("INSERT INTO PROJECT_9 (NAME, URL) VALUES ('REST with Spring', 'https://github.com/eugenp/REST-With-Spring')")
169+
}
170+
171+
def rows = sql.rows("SELECT * FROM PROJECT_9")
172+
173+
assertEquals(2, rows.size())
174+
}
175+
}
176+
177+
@Test
178+
void whenException_thenTransactionIsRolledBack() {
179+
Sql.withInstance(dbConnParams) { Sql sql ->
180+
try {
181+
sql.withTransaction {
182+
sql.execute 'create table PROJECT_10 (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100))'
183+
sql.executeInsert("INSERT INTO PROJECT_10 (NAME, URL) VALUES ('tutorials', 'https://github.com/eugenp/tutorials')")
184+
throw new Exception('rollback')
185+
}
186+
} catch (ignored) {}
187+
188+
def rows = sql.rows("SELECT * FROM PROJECT_10")
189+
190+
assertEquals(0, rows.size())
191+
}
192+
}
193+
194+
@Test
195+
void givenCachedConnection_whenException_thenDataIsPersisted() {
196+
Sql.withInstance(dbConnParams) { Sql sql ->
197+
try {
198+
sql.cacheConnection {
199+
sql.execute 'create table PROJECT_11 (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100))'
200+
sql.executeInsert("INSERT INTO PROJECT_11 (NAME, URL) VALUES ('tutorials', 'https://github.com/eugenp/tutorials')")
201+
throw new Exception('This does not rollback')
202+
}
203+
} catch (ignored) {}
204+
205+
def rows = sql.rows("SELECT * FROM PROJECT_11")
206+
207+
assertEquals(1, rows.size())
208+
}
209+
}
210+
211+
/*@Test
212+
void whenModifyResultSet_thenDataIsChanged() {
213+
Sql.withInstance(dbConnParams) { Sql sql ->
214+
sql.execute 'create table PROJECT_5 (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100))'
215+
sql.executeInsert("INSERT INTO PROJECT_5 (NAME, URL) VALUES ('tutorials', 'github.com/eugenp/tutorials')")
216+
sql.executeInsert("INSERT INTO PROJECT_5 (NAME, URL) VALUES ('REST with Spring', 'github.com/eugenp/REST-With-Spring')")
217+
218+
sql.eachRow("SELECT * FROM PROJECT_5 FOR UPDATE ") { GroovyResultSet rs ->
219+
rs['name'] = "The great ${rs.name}!" as String
220+
rs.updateRow()
221+
}
222+
223+
sql.eachRow("SELECT * FROM PROJECT_5") { GroovyResultSet rs ->
224+
assertTrue(rs.name.startsWith('The great '))
225+
}
226+
}
227+
}*/
228+
229+
}

‎pom.xml‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -49,6 +49,7 @@
4949
<module>core-java</module>
5050
<module>core-java-io</module>
5151
<module>core-java-8</module>
52+
<module>core-groovy</module>
5253
<!--
5354
<module>core-java-concurrency</module>
5455
-->

0 commit comments

Comments
 (0)