/**
* Convert the string input param value to its typed object value.
*
* @param value The string value of the input param
* @param type The type of the input value, defined at DBConstants.DataTypes.
* @return The typed object value of the input param
*/
public static Object convertInputParamValue(String value, String type) throws DataServiceFault {
try {
if (DBConstants.DataTypes.INTEGER.equals(type)) {
return Integer.parseInt(value);
} else if (DBConstants.DataTypes.LONG.equals(type)) {
return Long.parseLong(value);
} else if (DBConstants.DataTypes.FLOAT.equals(type)) {
return Float.parseFloat(value);
} else if (DBConstants.DataTypes.DOUBLE.equals(type)) {
return Double.parseDouble(value);
} else if (DBConstants.DataTypes.BOOLEAN.equals(type)) {
return Boolean.parseBoolean(value);
} else if (DBConstants.DataTypes.DATE.equals(type)) {
return new java.util.Date(DBUtils.getDate(value).getTime());
} else if (DBConstants.DataTypes.TIME.equals(type)) {
Calendar cal = Calendar.getInstance();
cal.setTimeInMillis(DBUtils.getTime(value).getTime());
return cal;
} else if (DBConstants.DataTypes.TIMESTAMP.equals(type)) {
Calendar cal = Calendar.getInstance();
cal.setTimeInMillis(DBUtils.getTimestamp(value).getTime());
return cal;
} else {
return value;
}
} catch (Exception e) {
throw new DataServiceFault(e);
}
}
@Test
public void testDropIndexOnTable() throws SQLException {
Connection connection = null;
Statement statement = null;
try {
ConnectionManager connectionManager = temporaryFileDatabase.getConnectionManager(true);
connection = spy(connectionManager.getConnection(null));
statement = spy(connection.createStatement());
when(connection.createStatement()).thenReturn(statement);
DBQueries.createIndexTable(NAME, Integer.class, String.class, connection);
DBQueries.createIndexOnTable(NAME, connection);
assertObjectExistenceInSQLIteMasterTable(TABLE_NAME, "table", true, connectionManager);
assertObjectExistenceInSQLIteMasterTable(INDEX_NAME, "index", true, connectionManager);
DBQueries.dropIndexOnTable(NAME, connection);
assertObjectExistenceInSQLIteMasterTable(TABLE_NAME, "table", true, connectionManager);
assertObjectExistenceInSQLIteMasterTable(INDEX_NAME, "index", false, connectionManager);
verify(statement, times(3)).close();
} finally {
DBUtils.closeQuietly(connection);
DBUtils.closeQuietly(statement);
}
}
private static void createTable() throws Exception {
// Create the DDL
String sql =
"CREATE TABLE CUSTOMERS"
+ "("
+ "FIRSTNAME VARCHAR(50),"
+ "MIDDLENAME VARCHAR(2),"
+ "LASTNAME VARCHAR(50),"
+ "AGE NUMERIC(2),"
+ "SSN NUMERIC(10),"
+ "CITY VARCHAR(32),"
+ "STATE VARCHAR(2),"
+ "COUNTRY VARCHAR(40)"
+ ")";
// Get the connection using our utils.
Connection con = DBUtils.getConnection();
if (con != null) {
// Create statement from connection
Statement stmt = con.createStatement();
// Execute the statement by passing the sql
int result = stmt.executeUpdate(sql);
if (result != -1) {
System.out.println("Table created sucessfully!");
} else {
System.out.println("Could'nt create table. Please check your SQL syntax...");
}
// Close the statements and Connections
stmt.close();
DBUtils.closeConnection();
} else {
System.out.println("Unable to get the connection !!!");
}
}
@Test
public void getDistinctKeysAndCounts_SortByKeyDescending() {
Connection connection = null;
ResultSet resultSet = null;
try {
ConnectionManager connectionManager = temporaryFileDatabase.getConnectionManager(true);
initWithTestData(connectionManager);
connection = connectionManager.getConnection(null);
resultSet = DBQueries.getDistinctKeysAndCounts(true, NAME, connection);
Map<String, Integer> resultSetToMap = resultSetToMap(resultSet);
assertEquals(3, resultSetToMap.size());
Iterator<Map.Entry<String, Integer>> entriesIterator = resultSetToMap.entrySet().iterator();
Map.Entry entry = entriesIterator.next();
assertEquals("gps", entry.getKey());
assertEquals(1, entry.getValue());
entry = entriesIterator.next();
assertEquals("airbags", entry.getKey());
assertEquals(1, entry.getValue());
entry = entriesIterator.next();
assertEquals("abs", entry.getKey());
assertEquals(2, entry.getValue());
} finally {
DBUtils.closeQuietly(resultSet);
DBUtils.closeQuietly(connection);
}
}
public void assertObjectExistenceInSQLIteMasterTable(
final String name,
final String type,
boolean exists,
final ConnectionManager connectionManager) {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = connectionManager.getConnection(null);
statement = connection.prepareStatement("SELECT name FROM sqlite_master WHERE type=?");
statement.setString(1, type);
java.sql.ResultSet indices = statement.executeQuery();
boolean found = false;
StringBuilder objectsFound = new StringBuilder();
String next;
while (indices.next()) {
next = indices.getString(1);
objectsFound.append("'").append(next).append("' ");
if (name.equals(next)) {
found = true;
}
}
if (exists)
Assert.assertTrue(
"Object '"
+ name
+ "' must exists in 'sqlite_master' but it doesn't. found: "
+ found
+ ". Objects found: "
+ objectsFound,
found);
else
Assert.assertFalse(
"Object '"
+ name
+ "' must NOT exists in 'sqlite_master' but it does. found: "
+ found
+ " Objects found: "
+ objectsFound,
found);
} catch (Exception e) {
throw new IllegalStateException(
"Unable to verify existence of the object '" + name + "' in the 'sqlite_master' table",
e);
} finally {
DBUtils.closeQuietly(connection);
DBUtils.closeQuietly(statement);
}
}
@Test
public void testSearch_Has() throws SQLException {
Connection connection = null;
ResultSet resultSet = null;
try {
ConnectionManager connectionManager = temporaryFileDatabase.getConnectionManager(true);
initWithTestData(connectionManager);
connection = connectionManager.getConnection(null);
resultSet = DBQueries.search(has(self(Car.class)), NAME, connection);
assertResultSetObjectKeysOrderAgnostic(resultSet, Arrays.asList(1, 2, 3));
} finally {
DBUtils.closeQuietly(connection);
DBUtils.closeQuietly(resultSet);
}
}
@Test
public void testSearch_StringStartsWith() throws SQLException {
Connection connection = null;
ResultSet resultSet = null;
try {
ConnectionManager connectionManager = temporaryFileDatabase.getConnectionManager(true);
initWithTestData(connectionManager);
StringStartsWith<Car, String> startsWith = startsWith(Car.FEATURES, "ab");
connection = connectionManager.getConnection(null);
resultSet = DBQueries.search(startsWith, NAME, connection);
assertResultSetObjectKeysOrderAgnostic(resultSet, Arrays.asList(1, 3));
} finally {
DBUtils.closeQuietly(connection);
DBUtils.closeQuietly(resultSet);
}
}
void initWithTestData(final ConnectionManager connectionManager) {
createSchema(connectionManager);
Connection connection = null;
Statement statement = null;
try {
connection = connectionManager.getConnection(null);
statement = connection.createStatement();
assertEquals(statement.executeUpdate("INSERT INTO " + TABLE_NAME + " values (1, 'abs')"), 1);
assertEquals(statement.executeUpdate("INSERT INTO " + TABLE_NAME + " values (1, 'gps')"), 1);
assertEquals(
statement.executeUpdate("INSERT INTO " + TABLE_NAME + " values (2, 'airbags')"), 1);
assertEquals(statement.executeUpdate("INSERT INTO " + TABLE_NAME + " values (3, 'abs')"), 1);
} catch (Exception e) {
throw new IllegalStateException("Unable to initialize test database", e);
} finally {
DBUtils.closeQuietly(connection);
DBUtils.closeQuietly(statement);
}
}
public void assertQueryResultSet(
final String query,
final List<DBQueries.Row<Integer, String>> rows,
final ConnectionManager connectionManager)
throws SQLException {
Connection connection = null;
Statement statement = null;
try {
connection = connectionManager.getConnection(null);
statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(query);
assertResultSetOrderAgnostic(resultSet, rows);
} catch (Exception e) {
throw new IllegalStateException("Unable to verify resultSet", e);
} finally {
DBUtils.closeQuietly(connection);
DBUtils.closeQuietly(statement);
}
}
void createSchema(final ConnectionManager connectionManager) {
Connection connection = null;
Statement statement = null;
try {
connection = connectionManager.getConnection(null);
statement = connection.createStatement();
assertEquals(
statement.executeUpdate(
"CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (objectKey INTEGER, value TEXT)"),
0);
assertEquals(
statement.executeUpdate(
"CREATE INDEX IF NOT EXISTS " + INDEX_NAME + " ON " + TABLE_NAME + "(value)"),
0);
} catch (Exception e) {
throw new IllegalStateException("Unable to create test database schema", e);
} finally {
DBUtils.closeQuietly(connection);
DBUtils.closeQuietly(statement);
}
}
@Test
public void getDistinctKeysAndCounts() {
Connection connection = null;
ResultSet resultSet = null;
try {
ConnectionManager connectionManager = temporaryFileDatabase.getConnectionManager(true);
initWithTestData(connectionManager);
connection = connectionManager.getConnection(null);
resultSet = DBQueries.getDistinctKeysAndCounts(false, NAME, connection);
Map<String, Integer> resultSetToMap = resultSetToMap(resultSet);
assertEquals(3, resultSetToMap.size());
assertEquals(new Integer(2), resultSetToMap.get("abs"));
assertEquals(new Integer(1), resultSetToMap.get("airbags"));
assertEquals(new Integer(1), resultSetToMap.get("gps"));
} finally {
DBUtils.closeQuietly(resultSet);
DBUtils.closeQuietly(connection);
}
}
@Test
public void testClearIndexTable() throws SQLException {
Connection connection = null;
Statement statement = null;
try {
ConnectionManager connectionManager = temporaryFileDatabase.getConnectionManager(true);
createSchema(connectionManager);
assertObjectExistenceInSQLIteMasterTable(TABLE_NAME, "table", true, connectionManager);
assertObjectExistenceInSQLIteMasterTable(INDEX_NAME, "index", true, connectionManager);
connection = spy(connectionManager.getConnection(null));
statement = spy(connection.createStatement());
when(connection.createStatement()).thenReturn(statement);
DBQueries.clearIndexTable(NAME, connection);
List<DBQueries.Row<Integer, String>> expectedRows = Collections.emptyList();
assertQueryResultSet("SELECT * FROM " + TABLE_NAME, expectedRows, connectionManager);
verify(statement, times(1)).close();
} finally {
DBUtils.closeQuietly(connection);
DBUtils.closeQuietly(statement);
}
}
@Test
public void getCountOfDistinctKeys() {
Connection connection = null;
try {
ConnectionManager connectionManager = temporaryFileDatabase.getConnectionManager(true);
initWithTestData(connectionManager);
connection = connectionManager.getConnection(null);
int countOfDistinctKeys = DBQueries.getCountOfDistinctKeys(NAME, connection);
assertEquals(3, countOfDistinctKeys);
} finally {
DBUtils.closeQuietly(connection);
}
}
@Test
public void testContains() throws SQLException {
Connection connection = null;
try {
ConnectionManager connectionManager = temporaryFileDatabase.getConnectionManager(true);
initWithTestData(connectionManager);
Equal<Car, String> equal = equal(Car.FEATURES, "abs");
connection = connectionManager.getConnection(null);
Assert.assertTrue(DBQueries.contains(1, equal, NAME, connection));
Assert.assertFalse(DBQueries.contains(4, equal, NAME, connection));
} finally {
DBUtils.closeQuietly(connection);
}
}
@Test
public void testGetIndexEntryByObjectKey() throws SQLException {
Connection connection = null;
try {
ConnectionManager connectionManager = temporaryFileDatabase.getConnectionManager(true);
initWithTestData(connectionManager);
connection = connectionManager.getConnection(null);
ResultSet resultSet = DBQueries.getIndexEntryByObjectKey(3, NAME, connection);
List<DBQueries.Row<Integer, String>> expectedRows =
new ArrayList<DBQueries.Row<Integer, String>>(2);
expectedRows.add(new DBQueries.Row<Integer, String>(3, "abs"));
assertResultSetOrderAgnostic(resultSet, expectedRows);
} finally {
DBUtils.closeQuietly(connection);
}
}
@Test
public void testBulkRemove() throws SQLException {
Connection connection = null;
try {
ConnectionManager connectionManager = temporaryFileDatabase.getConnectionManager(true);
initWithTestData(connectionManager);
List<DBQueries.Row<Integer, String>> expectedRows =
new ArrayList<DBQueries.Row<Integer, String>>(2);
expectedRows.add(new DBQueries.Row<Integer, String>(2, "airbags"));
expectedRows.add(new DBQueries.Row<Integer, String>(3, "abs"));
connection = connectionManager.getConnection(null);
DBQueries.bulkRemove(Collections.singletonList(1), NAME, connection);
assertQueryResultSet("SELECT * FROM " + TABLE_NAME, expectedRows, connectionManager);
} finally {
DBUtils.closeQuietly(connection);
}
}
@Test
public void testBulkAdd() throws SQLException {
Connection connection = null;
try {
ConnectionManager connectionManager = temporaryFileDatabase.getConnectionManager(true);
createSchema(connectionManager);
List<DBQueries.Row<Integer, String>> rowsToAdd =
new ArrayList<DBQueries.Row<Integer, String>>(4);
rowsToAdd.add(new DBQueries.Row<Integer, String>(1, "abs"));
rowsToAdd.add(new DBQueries.Row<Integer, String>(1, "gps"));
rowsToAdd.add(new DBQueries.Row<Integer, String>(2, "airbags"));
rowsToAdd.add(new DBQueries.Row<Integer, String>(2, "abs"));
connection = connectionManager.getConnection(null);
DBQueries.bulkAdd(rowsToAdd, NAME, connection);
assertQueryResultSet("SELECT * FROM " + TABLE_NAME, rowsToAdd, connectionManager);
} finally {
DBUtils.closeQuietly(connection);
}
}
/**
* Retrieves the current user's roles given the message context.
*
* @param msgContext The message context to be used to retrieve the username
* @return The user roles
* @throws DataServiceFault
*/
public static String[] getUserRoles(MessageContext msgContext) throws DataServiceFault {
return getUserRoles(DBUtils.getUsername(msgContext));
}