package net.codjo.dataprocess.server.util;
import net.codjo.database.common.api.JdbcFixture;
import net.codjo.database.common.api.structure.SqlTable;
import net.codjo.datagen.DatagenFixture;
import net.codjo.test.common.fixture.CompositeFixture;
import net.codjo.tokio.TokioFixture;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;
import static net.codjo.test.common.matcher.JUnitMatchers.*;
/**
*
*/
public class SQLUtilTest {
private static final long NUMBER_OF_ROW = 100;
private static final String TABLE_NAME = "TEST1";
private static final String TABLE_NAME2 = "TEST2";
private static final DatagenFixture DATAGEN = new DatagenFixture(SQLUtilTest.class);
private static final TokioFixture TOKIO = new TokioFixture(SQLUtilTest.class);
private static final CompositeFixture COMPOSITE_FIXTURE = new CompositeFixture(TOKIO, DATAGEN);
@BeforeClass
public static void beforeClass() throws Exception {
COMPOSITE_FIXTURE.doSetUp();
JdbcFixture jdbcFixture = TOKIO.getJdbcFixture();
jdbcFixture.advanced().dropAllObjects();
try {
DATAGEN.generate();
TestUtils.initScript(jdbcFixture, DATAGEN, "T_TRANSFER.tab");
dropAndCreateTestTables();
}
catch (Exception ex) {
COMPOSITE_FIXTURE.doTearDown();
fail(ex.getLocalizedMessage());
}
}
@AfterClass
public static void afterClass() throws Exception {
dropTestTables(TOKIO.getJdbcFixture());
COMPOSITE_FIXTURE.doTearDown();
}
@Test
public void truncateTable() throws Exception {
fillTestTables();
assertThat(NUMBER_OF_ROW, equalTo(countRow(TABLE_NAME)));
SQLUtil.truncateTable(TABLE_NAME, 5, TOKIO.getJdbcFixture().getConnection());
assertThat(0L, equalTo(countRow(TABLE_NAME)));
}
@Test
public void transfertTable() throws Exception {
fillTestTables();
assertThat(NUMBER_OF_ROW, equalTo(countRow(TABLE_NAME)));
assertThat(0L, equalTo(countRow(TABLE_NAME2)));
Map<String, String> treatmentOnColumn = new HashMap<String, String>();
treatmentOnColumn.put("dataTest", "convert(varchar(100),dataTest)");
SQLUtilCopyTable
.copyTable(TOKIO.getJdbcFixture().getConnection(), TABLE_NAME, "id", TABLE_NAME2, null,
5, treatmentOnColumn, true, new SQLUtilCopyTable.ResultCopyTable());
assertThat(NUMBER_OF_ROW, equalTo(countRow(TABLE_NAME)));
assertThat(NUMBER_OF_ROW, equalTo(countRow(TABLE_NAME2)));
}
@Test
public void executeUpdateSql() throws Exception {
JdbcFixture fixture = TOKIO.getJdbcFixture();
fixture.executeUpdate("delete from " + TABLE_NAME);
fixture.executeUpdate("insert into " + TABLE_NAME + " (id, dataTest) values (1, 'poire')");
fixture.executeUpdate("insert into " + TABLE_NAME + " (id, dataTest) values (2, 'pomme')");
fixture.executeUpdate("insert into " + TABLE_NAME + " (id, dataTest) values (3, 'fraise')");
fixture.executeUpdate("insert into " + TABLE_NAME + " (id, dataTest) values (4, 'fraise')");
fixture.executeUpdate("update " + TABLE_NAME + " set dataTest = 'pomme frite' where id = 2");
ResultSet rs = fixture.executeQuery(
"select id from " + TABLE_NAME + " where dataTest = 'pomme frite'");
assertThat(rs.next(), equalTo(true));
assertThat(2, equalTo(rs.getInt(1)));
rs.close();
}
@Test
public void countRowsTable() throws Exception {
JdbcFixture fixture = TOKIO.getJdbcFixture();
fixture.executeUpdate("delete from " + TABLE_NAME);
fixture.executeUpdate("insert into " + TABLE_NAME + " (id, dataTest) values (1, 'poire')");
fixture.executeUpdate("insert into " + TABLE_NAME + " (id, dataTest) values (2, 'pomme')");
fixture.executeUpdate("insert into " + TABLE_NAME + " (id, dataTest) values (3, 'fraise')");
fixture.executeUpdate("insert into " + TABLE_NAME + " (id, dataTest) values (4, 'fraise')");
Connection con = fixture.getConnection();
int result = SQLUtil.countRowsTable(TABLE_NAME, "where dataTest = 'fraise'", con);
assertThat(2, equalTo(result));
result = SQLUtil.countRowsTable(TABLE_NAME, " dataTest = 'fraise'", con);
assertThat(2, equalTo(result));
result = SQLUtil.countRowsTable(TABLE_NAME, " dataTest = 'pomme'", con);
assertThat(1, equalTo(result));
result = SQLUtil.countRowsTable(TABLE_NAME, null, con);
assertThat(4, equalTo(result));
}
private static void createTestTables(JdbcFixture fixture) throws SQLException {
fixture.create(SqlTable.table(TABLE_NAME), "id integer, dataTest varchar(50) not null");
fixture.create(SqlTable.table(TABLE_NAME2),
"id integer,dataTest varchar(100) not null, notFilled varchar(10) null");
}
private static void dropTestTables(JdbcFixture fixture) throws SQLException {
fixture.drop(SqlTable.table(TABLE_NAME));
fixture.drop(SqlTable.table(TABLE_NAME2));
}
private static void dropAndCreateTestTables() throws SQLException {
try {
dropTestTables(TOKIO.getJdbcFixture());
}
catch (Exception ex) {
;
}
createTestTables(TOKIO.getJdbcFixture());
}
private static long countRow(String tableName) throws SQLException {
String sql = "select count(*) from " + tableName;
ResultSet rs = TOKIO.getJdbcFixture().executeQuery(sql);
try {
rs.next();
return rs.getLong(1);
}
finally {
rs.close();
}
}
private static void fillTestTables() throws SQLException {
JdbcFixture fixture = TOKIO.getJdbcFixture();
fixture.executeUpdate("delete from " + TABLE_NAME);
fixture.executeUpdate("delete from " + TABLE_NAME2);
ExtendedPreparedStatement exStmt = new ExtendedPreparedStatement(
"insert into " + TABLE_NAME + "(id, dataTest) values (${id}, ${data})");
for (int count = 0; count < NUMBER_OF_ROW; count++) {
exStmt.setParameterValue("id", count);
exStmt.setParameterValue("data", Long.toHexString(System.currentTimeMillis()) + "-" + count);
exStmt.createAndSetPreparedStatement(fixture.getConnection()).executeUpdate();
}
}
@Test
public void getTableColumns() throws SQLException {
Connection con = TOKIO.getJdbcFixture().getConnection();
List<String> result = SQLUtil.getTableFields(con, TABLE_NAME);
assertThat(result.toString(), equalTo("[TEST1.id, TEST1.dataTest]"));
result = SQLUtil.getTableFields(con, TABLE_NAME2);
assertThat(result.toString(), equalTo("[TEST2.id, TEST2.dataTest, TEST2.notFilled]"));
}
}