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 package com.jcabi.jdbc;
31
32 import com.mysql.cj.jdbc.MysqlDataSource;
33 import java.sql.CallableStatement;
34 import java.sql.Types;
35 import javax.sql.DataSource;
36 import org.apache.commons.lang3.StringUtils;
37 import org.hamcrest.MatcherAssert;
38 import org.hamcrest.Matchers;
39 import org.junit.jupiter.api.Test;
40 import org.testcontainers.containers.JdbcDatabaseContainer;
41 import org.testcontainers.containers.MySQLContainer;
42 import org.testcontainers.junit.jupiter.Container;
43 import org.testcontainers.junit.jupiter.Testcontainers;
44 import org.testcontainers.utility.DockerImageName;
45
46
47
48
49
50
51 @Testcontainers(disabledWithoutDocker = true)
52 @SuppressWarnings("PMD.AvoidDuplicateLiterals")
53 final class JdbcSessionMySqlITCase {
54
55
56
57
58 @Container
59 private final JdbcDatabaseContainer<?> container =
60 new MySQLContainer<>(
61 DockerImageName
62 .parse("mysql/mysql-server:latest")
63 .asCompatibleSubstituteFor("mysql")
64 );
65
66 @Test
67 void worksWithExecute() throws Exception {
68 final DataSource source = this.source();
69 new JdbcSession(source)
70 .autocommit(false)
71 .sql("CREATE TABLE foo (name VARCHAR(50))")
72 .execute()
73 .sql("INSERT INTO foo (name) VALUES (?)")
74 .set("Jeff Lebowski")
75 .execute()
76 .commit();
77 }
78
79 @Test
80 void worksLastInsertId() throws Exception {
81 new JdbcSession(this.source())
82 .sql(
83 StringUtils.join(
84 "CREATE TABLE IF NOT EXISTS foo (",
85 "id INT NOT NULL AUTO_INCREMENT, ",
86 "name VARCHAR(50), ",
87 "PRIMARY KEY (id)",
88 ")"
89 )
90 )
91 .execute();
92 MatcherAssert.assertThat(
93 new JdbcSession(this.source())
94 .sql("INSERT INTO foo (name) VALUES (?)")
95 .set("test")
96 .insert(Outcome.LAST_INSERT_ID),
97 Matchers.is(1L)
98 );
99 }
100
101 @Test
102 void worksLastInsertIdAndTransaction() throws Exception {
103 new JdbcSession(this.source())
104 .sql(
105 StringUtils.join(
106 "CREATE TABLE IF NOT EXISTS foo (",
107 "id INT NOT NULL AUTO_INCREMENT, ",
108 "name VARCHAR(50), ",
109 "PRIMARY KEY (id)",
110 ")"
111 )
112 )
113 .execute();
114 final JdbcSession session = new JdbcSession(this.source());
115 MatcherAssert.assertThat(
116 session
117 .autocommit(false)
118 .sql("START TRANSACTION")
119 .execute()
120 .sql("INSERT INTO foo (name) VALUES (?)")
121 .set("test")
122 .insert(Outcome.LAST_INSERT_ID),
123 Matchers.is(1L)
124 );
125 session.rollback();
126 }
127
128 @Test
129 void callsFunctionWithOutParam() throws Exception {
130 new JdbcSession(this.source())
131 .autocommit(false)
132 .sql("CREATE TABLE IF NOT EXISTS users (name VARCHAR(50))")
133 .execute()
134 .sql("INSERT INTO users (name) VALUES (?)")
135 .set("Jeff Charles").execute()
136 .sql(
137 StringUtils.join(
138 "CREATE PROCEDURE proc(OUT username text, OUT day date) ",
139 "BEGIN SELECT name, CURDATE() INTO username, day ",
140 "FROM users; ",
141 "SELECT username, day; ",
142 "END"
143 )
144 ).execute().commit();
145 final Object[] result = new JdbcSession(this.source())
146 .sql("CALL proc(?, ?)")
147 .prepare(
148 stmt -> {
149 ((CallableStatement) stmt).registerOutParameter(1, Types.VARCHAR);
150 ((CallableStatement) stmt).registerOutParameter(2, Types.DATE);
151 }
152 )
153 .call(new StoredProcedureOutcome<>(1, 2));
154 MatcherAssert.assertThat(
155 result,
156 Matchers.arrayContaining(
157 Matchers.is("Jeff Charles"),
158 Matchers.notNullValue()
159 )
160 );
161 }
162
163
164
165
166
167
168 private DataSource source() {
169 final MysqlDataSource src = new MysqlDataSource();
170 src.setUrl(this.container.getJdbcUrl());
171 src.setUser(this.container.getUsername());
172 src.setPassword(this.container.getPassword());
173 return src;
174 }
175
176 }