@Override
public List<DeviceGroupBuilder> findInGroups(String groupName, int tenantId)
throws GroupManagementDAOException {
PreparedStatement stmt = null;
ResultSet resultSet = null;
List<DeviceGroupBuilder> deviceGroups = new ArrayList<>();
try {
Connection conn = GroupManagementDAOFactory.getConnection();
String sql =
"SELECT ID, DESCRIPTION, GROUP_NAME, DATE_OF_CREATE, DATE_OF_LAST_UPDATE, OWNER "
+ "FROM DM_GROUP WHERE GROUP_NAME LIKE ? AND TENANT_ID = ?";
stmt = conn.prepareStatement(sql);
stmt.setString(1, "%" + groupName + "%");
stmt.setInt(2, tenantId);
resultSet = stmt.executeQuery();
while (resultSet.next()) {
deviceGroups.add(GroupManagementDAOUtil.loadGroup(resultSet));
}
} catch (SQLException e) {
throw new GroupManagementDAOException(
"Error occurred while listing Device Groups by name '"
+ groupName
+ "' in tenant '"
+ tenantId
+ "'",
e);
} finally {
GroupManagementDAOUtil.cleanupResources(stmt, resultSet);
}
return deviceGroups;
}
@Override
public List<DeviceGroupBuilder> getGroups(int deviceId, int tenantId)
throws GroupManagementDAOException {
PreparedStatement stmt = null;
ResultSet resultSet = null;
List<DeviceGroupBuilder> deviceGroupBuilders = new ArrayList<>();
try {
Connection conn = GroupManagementDAOFactory.getConnection();
String sql =
"SELECT G.ID, G.GROUP_NAME, G.DESCRIPTION, G.DATE_OF_CREATE, G.DATE_OF_LAST_UPDATE, \n"
+ "G.OWNER FROM DM_GROUP AS G INNER JOIN DM_DEVICE_GROUP_MAP AS GM ON G.ID = GM.GROUP_ID "
+ "WHERE GM.DEVICE_ID = ? AND GM.TENANT_ID = ?";
stmt = conn.prepareStatement(sql);
stmt.setInt(1, deviceId);
stmt.setInt(2, tenantId);
resultSet = stmt.executeQuery();
while (resultSet.next()) {
deviceGroupBuilders.add(GroupManagementDAOUtil.loadGroup(resultSet));
}
} catch (SQLException e) {
throw new GroupManagementDAOException(
"Error occurred while obtaining information of Device Groups ", e);
} finally {
GroupManagementDAOUtil.cleanupResources(stmt, resultSet);
}
return deviceGroupBuilders;
}
@Override
public List<DeviceGroupBuilder> getGroups(int startIndex, int rowCount, int tenantId)
throws GroupManagementDAOException {
PreparedStatement stmt = null;
ResultSet resultSet = null;
List<DeviceGroupBuilder> deviceGroupList = null;
try {
Connection conn = GroupManagementDAOFactory.getConnection();
String sql =
"SELECT ID, DESCRIPTION, GROUP_NAME, DATE_OF_CREATE, DATE_OF_LAST_UPDATE, OWNER "
+ "FROM DM_GROUP WHERE TENANT_ID = ? LIMIT ?, ?";
stmt = conn.prepareStatement(sql);
stmt.setInt(1, tenantId);
//noinspection JpaQueryApiInspection
stmt.setInt(2, startIndex);
//noinspection JpaQueryApiInspection
stmt.setInt(3, rowCount);
resultSet = stmt.executeQuery();
deviceGroupList = new ArrayList<>();
while (resultSet.next()) {
deviceGroupList.add(GroupManagementDAOUtil.loadGroup(resultSet));
}
} catch (SQLException e) {
throw new GroupManagementDAOException(
"Error occurred while listing all groups in tenant: " + tenantId, e);
} finally {
GroupManagementDAOUtil.cleanupResources(stmt, resultSet);
}
return deviceGroupList;
}
@Override
public DeviceGroupBuilder getGroup(String groupName, String owner, int tenantId)
throws GroupManagementDAOException {
PreparedStatement stmt = null;
ResultSet resultSet = null;
try {
Connection conn = GroupManagementDAOFactory.getConnection();
String sql =
"SELECT ID, DESCRIPTION, GROUP_NAME, DATE_OF_CREATE, DATE_OF_LAST_UPDATE, OWNER "
+ "FROM DM_GROUP WHERE GROUP_NAME = ? AND OWNER = ? AND TENANT_ID = ?";
stmt = conn.prepareStatement(sql);
stmt.setString(1, groupName);
stmt.setString(2, owner);
stmt.setInt(3, tenantId);
resultSet = stmt.executeQuery();
if (resultSet.next()) {
return GroupManagementDAOUtil.loadGroup(resultSet);
} else {
return null;
}
} catch (SQLException e) {
throw new GroupManagementDAOException(
"Error occurred while obtaining information of Device Group '" + groupName + "'", e);
} finally {
GroupManagementDAOUtil.cleanupResources(stmt, resultSet);
}
}