From eb5d8534c950390460405c78c64fc28da57d9ef6 Mon Sep 17 00:00:00 2001 From: Gustavo Martin Morcuende Date: Sun, 2 Oct 2016 23:27:41 +0200 Subject: [PATCH] Using PreparedStatement with parameters --- .../integration/database/DataBaseAccess.java | 7 +- .../database/impl/DataBaseAccessImpl.java | 16 +++-- .../impl/LiquibaseContextIntegrationTest.java | 83 ++++++++++++++++++++-- 3 files changed, 96 insertions(+), 10 deletions(-) diff --git a/src/main/java/com/prueba/core/context/integration/database/DataBaseAccess.java b/src/main/java/com/prueba/core/context/integration/database/DataBaseAccess.java index 94cc2a9..84922b6 100644 --- a/src/main/java/com/prueba/core/context/integration/database/DataBaseAccess.java +++ b/src/main/java/com/prueba/core/context/integration/database/DataBaseAccess.java @@ -16,5 +16,10 @@ public interface DataBaseAccess { List> executeResultSet(final TResult resultSet) throws SQLException; } - List> executeQuery(final String query, final ExecuteResultSet executeResultSet); + public interface FillPreparedStatement { + void doFill(final PreparedStatement statement) throws SQLException; + } + + List> executeQuery( + final String query, final ExecuteResultSet executeResultSet, FillPreparedStatement fillStatement); } diff --git a/src/main/java/com/prueba/core/context/integration/database/impl/DataBaseAccessImpl.java b/src/main/java/com/prueba/core/context/integration/database/impl/DataBaseAccessImpl.java index c97d563..ad4f76d 100644 --- a/src/main/java/com/prueba/core/context/integration/database/impl/DataBaseAccessImpl.java +++ b/src/main/java/com/prueba/core/context/integration/database/impl/DataBaseAccessImpl.java @@ -4,7 +4,6 @@ import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; -import java.sql.Statement; import java.util.List; import java.util.Map; @@ -20,10 +19,11 @@ public class DataBaseAccessImpl implements DataBaseAccess { private static final Logger LOGGER = LoggerFactory.getLogger(DataBaseAccessImpl.class); @Override - public List> executeQuery(String query, ExecuteResultSet executeResultSet) { + public List> executeQuery( + String query, ExecuteResultSet executeResultSet, FillPreparedStatement fillStatement) { List> result = null; try { - result = this.executeQueryThrowable(query, executeResultSet); + result = this.executeQueryThrowable(query, executeResultSet, fillStatement); } catch (SQLException exception) { LOGGER.error("Query error: ", exception); } @@ -31,7 +31,8 @@ public class DataBaseAccessImpl implements DataBaseAccess { return result; } - protected List> executeQueryThrowable(String query, ExecuteResultSet executeResultSet) throws SQLException { + protected List> executeQueryThrowable( + String query, ExecuteResultSet executeResultSet, FillPreparedStatement fillStatement) throws SQLException { final DataSource dataSource = DoDataSourceContext.getInstance().getDataSource(); try { final Connection connection = dataSource.getConnection(); @@ -39,7 +40,12 @@ public class DataBaseAccessImpl implements DataBaseAccess { return this.doExecuteQuery( query, connection, - preparedStatement -> preparedStatement.executeQuery(), + preparedStatement -> + { + fillStatement.doFill(preparedStatement); + + return preparedStatement.executeQuery(); + }, executeResultSet ); } finally { diff --git a/src/test/java/com/prueba/core/context/integration/liquibase/impl/LiquibaseContextIntegrationTest.java b/src/test/java/com/prueba/core/context/integration/liquibase/impl/LiquibaseContextIntegrationTest.java index ceeb4bf..c074bcf 100644 --- a/src/test/java/com/prueba/core/context/integration/liquibase/impl/LiquibaseContextIntegrationTest.java +++ b/src/test/java/com/prueba/core/context/integration/liquibase/impl/LiquibaseContextIntegrationTest.java @@ -9,6 +9,7 @@ import java.util.Map; import javax.sql.DataSource; +import org.junit.Before; import org.junit.Test; import com.prueba.core.context.integration.database.DataBaseAccess; @@ -22,12 +23,18 @@ public class LiquibaseContextIntegrationTest { private static final String PASSWORD = "PASSWORD"; private static final String APP_ROLE_CODE = "APPLICATION_ROLE_CODE"; + private DataSource dataSource; + private LiquibaseContext liquibaseContext; + + @Before + public void init() { + dataSource = DoDataSourceContext.getInstance().getDataSource(); + liquibaseContext = new LiquibaseContext(dataSource); + liquibaseContext.init(); + } + @Test public void whenLoadLiquibaseContextThenReturnResultsFromDatabase() { - final DataSource dataSource = DoDataSourceContext.getInstance().getDataSource(); - final LiquibaseContext liquibaseContext = new LiquibaseContext(dataSource); - liquibaseContext.init(); - final DataBaseAccess dataBaseAccess = new DataBaseAccessImpl(); final List> dataResult = dataBaseAccess.executeQuery("SELECT * FROM ACCOUNT", answer -> @@ -44,6 +51,47 @@ public class LiquibaseContextIntegrationTest { } return result; + }, + preparedStatement -> { + + }); + + final String expectedCode = "GUMARTIN"; + final String expectedName = "Gustavo"; + final String expectedSurname = "Martin Morcuende"; + final String expectedPassword = "lame"; + final String expectedAppRoleCode = "ROLE_APP_ADMIN"; + + final Map row = dataResult.get(0); + assertNotNull(row); + assertEquals(expectedCode, row.get(CODE)); + assertEquals(expectedName, row.get(NAME)); + assertEquals(expectedSurname, row.get(SURNAME)); + assertEquals(expectedPassword, row.get(PASSWORD)); + assertEquals(expectedAppRoleCode, row.get(APP_ROLE_CODE)); + } + + @Test + public void whenLoadLiquibaseContextThenReturnResultsFromDatabaseUsingParameters() { + final DataBaseAccess dataBaseAccess = new DataBaseAccessImpl(); + final List> dataResult = dataBaseAccess.executeQuery("SELECT * FROM ACCOUNT WHERE CODE = ?", + answer -> + { + final List> result = new ArrayList<>(); + while (answer.next()) { + final Map row = new HashMap<>(); + row.put(CODE, answer.getString(CODE)); + row.put(NAME, answer.getString(NAME)); + row.put(SURNAME, answer.getString(SURNAME)); + row.put(PASSWORD, answer.getString(PASSWORD)); + row.put(APP_ROLE_CODE, answer.getString(APP_ROLE_CODE)); + result.add(row); + } + + return result; + }, + preparedStatement -> { + preparedStatement.setString(1, "GUMARTIN"); }); final String expectedCode = "GUMARTIN"; @@ -61,4 +109,31 @@ public class LiquibaseContextIntegrationTest { assertEquals(expectedAppRoleCode, row.get(APP_ROLE_CODE)); } + @Test + public void whenLoadLiquibaseContextAndNotUserFoundThenDoNotReturResultsFromDatabase() { + final DataBaseAccess dataBaseAccess = new DataBaseAccessImpl(); + final List> dataResult = dataBaseAccess.executeQuery("SELECT * FROM ACCOUNT WHERE CODE = ?", + answer -> + { + final List> result = new ArrayList<>(); + while (answer.next()) { + final Map row = new HashMap<>(); + row.put(CODE, answer.getString(CODE)); + row.put(NAME, answer.getString(NAME)); + row.put(SURNAME, answer.getString(SURNAME)); + row.put(PASSWORD, answer.getString(PASSWORD)); + row.put(APP_ROLE_CODE, answer.getString(APP_ROLE_CODE)); + result.add(row); + } + + return result; + }, + preparedStatement -> { + preparedStatement.setString(1, "DONOTEXIST"); + }); + + + assertTrue(dataResult.isEmpty()); + } + } -- 2.1.4