I am trying to test the below UserRepositoryImpl class that inserts a User into a MySql database.
@Repository
public class UserRepositoryImpl implements UserRepository {
private final JdbcTemplate template;
public UserRepositoryImpl(DataSource dataSource) {
this.template = new JdbcTemplate(dataSource);
}
@Override
public Integer insert(User user) {
String sql = "INSERT INTO EXPENSE_TRACKER.USERS (USERNAME, PASSWORD, FIRSTNAME, LASTNAME, EMAIL) VALUES (?, ?, ?, ?, ?)";
return template.update(sql, user.getUsername(), user.getPassword(), user.getFirstName(), user.getLastName(), user.getEmail());
}
}
I need to test this method and for this reason, I have created the next simple test that uses Testcontainers.
@SpringBootTest
class UserRepositoryImplSpec extends TestContainersSpec {
@Subject
UserRepositoryImpl repository
def setup() {
repository = new UserRepositoryImpl(dataSource)
}
def "Successfully insert a new user in the database"() {
given: "a user"
def user = new User(username: 'testUser', password: 'myPassword', firstName: 'myName',
lastName: 'myLastName', email: '[email protected]')
when: "calling the insert method"
def result = repository.insert(user)
then: "the user is saved"
def dbUser = sql.firstRow("SELECT * FROM EXPENSE_TRACKER.USERS WHERE USERNAME = ?", [user.username])
assert dbUser != null
assert dbUser.get('ID') == result
assert dbUser.get('USERNAME') == user.username
assert dbUser.get('PASSWORD') == user.password
assert dbUser.get('FIRSTNAME') == user.firstName
assert dbUser.get('LASTNAME') == user.lastName
assert dbUser.get('EMAIL') == user.email
}
}
The TestContainersSpec class besides the container itself utilizes the creation of a reusable network so that I can use the same container. I needed to find a way to configure my Datasource and I thought to mock it based on the Sql instance shown below. Since this is an integration test, I know that this is not the way to go (mocking), but I will see on how to change this later on.
class TestContainersSpec extends Specification {
@Shared
public static MySQLContainer mySQL
public static Network network = createReusableNetwork('expense-network')
@Shared
Sql sql
@Shared
DataSource dataSource
def setup() {
mySQL = new MySQLContainer("mysql:8.0.28")
.withExposedPorts(3306)
.withDatabaseName('EXPENSE_TRACKER')
.withUsername('root')
.withPassword('test')
.withNetwork(network)
.withReuse(true)
.withStartupTimeout(Duration.ofMinutes(3))
.withLogConsumer(new Slf4jLogConsumer(LoggerFactory.getLogger("mySQL")))
mySQL.start()
dataSource = Mock()
sql = Sql.newInstance(mySQL.jdbcUrl, mySQL.username, mySQL.password, mySQL.driverClassName)
dataSource.connection >> sql.connection
createLocalDb(sql)
}
def createLocalDb(Sql sql) {
['cleanup.sql', 'schema.sql'].collect{ path ->
getClass().getResource("/$path").text.trim()
}.each { queries ->
queries.tokenize(";").each{
try {
sql.execute(it)
} catch(Exception e) {
println e.message
}
}
}
println 'Local database ready'
}
static Network createReusableNetwork(String name) {
String id = DockerClientFactory.instance().client().listNetworksCmd().exec().stream()
.filter(network -> network.getName().equals(name)
&& network.getLabels() == DockerClientFactory.DEFAULT_LABELS)
.map(com.github.dockerjava.api.model.Network::getId)
.findFirst()
.orElseGet(() -> DockerClientFactory.instance().client().createNetworkCmd()
.withName(name)
.withOptions(['mtu': '1350'])
.withCheckDuplicate(true)
.withLabels(DockerClientFactory.DEFAULT_LABELS)
.exec().getId())
return new Network() {
@Override
String getId() {
return id
}
@Override
void close() {
}
@Override
Statement apply(@NotNull Statement statement, @NotNull Description description) {
return statement
}
}
}
}
However, when I execute my test, I am getting the next error:
May 19, 2024 2:22:04 PM groovy.sql.Sql$AbstractQueryCommand execute
WARNING: Failed to execute: SELECT * FROM EXPENSE_TRACKER.USERS WHERE USERNAME = ? because: No operations allowed after connection closed.
May 19, 2024 2:22:04 PM groovy.sql.Sql$AbstractQueryCommand execute
WARNING: Failed to execute: SELECT * FROM EXPENSE_TRACKER.USERS WHERE USERNAME = ? because: No operations allowed after connection closed.
No operations allowed after connection closed.
java.sql.SQLNonTransientConnectionException: No operations allowed after connection closed.
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:111)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:98)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:90)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:64)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:74)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:73)
at com.mysql.cj.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:1610)
at groovy.sql.Sql$CreatePreparedStatementCommand.execute(Sql.java:4808)
at groovy.sql.Sql$CreatePreparedStatementCommand.execute(Sql.java:4786)
at groovy.sql.Sql.getAbstractStatement(Sql.java:4625)
at groovy.sql.Sql.getPreparedStatement(Sql.java:4640)
at groovy.sql.Sql.getPreparedStatement(Sql.java:4729)
at groovy.sql.Sql.access$1000(Sql.java:234)
at groovy.sql.Sql$PreparedQueryCommand.runQuery(Sql.java:4925)
at groovy.sql.Sql$AbstractQueryCommand.execute(Sql.java:4856)
at groovy.sql.Sql.rows(Sql.java:2050)
at groovy.sql.Sql.rows(Sql.java:1980)
at groovy.sql.Sql.rows(Sql.java:1824)
at groovy.sql.Sql.firstRow(Sql.java:2295)
at com.expense.tracker.repository.UserRepositoryImplSpec.Successfully insert a new user in the database(UserRepositoryImplSpec.groovy:27)
Caused by: com.mysql.cj.exceptions.ConnectionIsClosedException: No operations allowed after connection closed.
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:104)
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:149)
at com.mysql.cj.NativeSession.checkClosed(NativeSession.java:756)
at com.mysql.cj.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:556)
at com.mysql.cj.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:1539)
I can't see the reason, my connection closes. Can you propose me a solution?
I always recommend to use Testcontainer's JDBC support when trying to test with a real database in spring-boot. Just define jdbc:tc:mysql:8.0.36:///databasename as datasource url.
You can also let it execute an init-script jdbc:tc:mysql:8.0.36:///databasename?TC_INITSCRIPT=somepath/init_mysql.sql