@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 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 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 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);
}
}
@Override
public void updateGroup(
DeviceGroup deviceGroup, String oldGroupName, String oldOwner, int tenantId)
throws GroupManagementDAOException {
PreparedStatement stmt = null;
try {
Connection conn = GroupManagementDAOFactory.getConnection();
String sql =
"UPDATE DM_GROUP SET DESCRIPTION = ?, GROUP_NAME = ?, DATE_OF_LAST_UPDATE = ?, OWNER = ? "
+ "WHERE GROUP_NAME = ? AND OWNER = ? AND TENANT_ID = ?";
stmt = conn.prepareStatement(sql);
stmt.setString(1, deviceGroup.getDescription());
stmt.setString(2, deviceGroup.getName());
stmt.setLong(3, deviceGroup.getDateOfLastUpdate());
stmt.setString(4, deviceGroup.getOwner());
stmt.setString(5, oldGroupName);
stmt.setString(6, oldOwner);
stmt.setInt(7, tenantId);
stmt.executeUpdate();
} catch (SQLException e) {
throw new GroupManagementDAOException(
"Error occurred while updating deviceGroup '" + deviceGroup.getName() + "'", e);
} finally {
GroupManagementDAOUtil.cleanupResources(stmt, null);
}
}
@Override
public int getDeviceCount(String groupName, String owner, int tenantId)
throws GroupManagementDAOException {
PreparedStatement stmt = null;
ResultSet resultSet = null;
try {
Connection conn = GroupManagementDAOFactory.getConnection();
String sql =
"SELECT COUNT(gm.ID) AS DEVICE_COUNT FROM DM_DEVICE_GROUP_MAP gm, (SELECT ID "
+ "FROM DM_GROUP WHERE GROUP_NAME = ? AND OWNER = ? AND TENANT_ID = ?) dg "
+ "WHERE gm.GROUP_ID = dg.ID AND gm.TENANT_ID = ?";
stmt = conn.prepareStatement(sql);
stmt.setString(1, groupName);
stmt.setString(2, owner);
stmt.setInt(3, tenantId);
stmt.setInt(4, tenantId);
resultSet = stmt.executeQuery();
if (resultSet.next()) {
return resultSet.getInt("DEVICE_COUNT");
} else {
return 0;
}
} catch (SQLException e) {
throw new GroupManagementDAOException(
"Error occurred while group Id listing by group name '" + groupName + "'", e);
} finally {
GroupManagementDAOUtil.cleanupResources(stmt, resultSet);
}
}
@Override
public boolean isDeviceMappedToGroup(String groupName, String owner, int deviceId, int tenantId)
throws GroupManagementDAOException {
PreparedStatement stmt = null;
ResultSet resultSet = null;
try {
Connection conn = GroupManagementDAOFactory.getConnection();
String sql =
"SELECT dm.ID FROM DM_DEVICE_GROUP_MAP dm, (SELECT ID as GROUP_ID FROM DM_GROUP "
+ "WHERE GROUP_NAME = ? AND OWNER = ? AND TENANT_ID = ?) dg "
+ "WHERE dm.GROUP_ID = dg.GROUP_ID AND dm.ID = ? AND dm.TENANT_ID = ?";
stmt = conn.prepareStatement(sql);
stmt.setString(1, groupName);
stmt.setString(2, owner);
stmt.setInt(3, tenantId);
stmt.setInt(4, deviceId);
stmt.setInt(5, tenantId);
resultSet = stmt.executeQuery();
return resultSet.next();
} catch (SQLException e) {
throw new GroupManagementDAOException(
"Error occurred while group Id listing by group name '" + groupName + "'", e);
} finally {
GroupManagementDAOUtil.cleanupResources(stmt, resultSet);
}
}
@Override
public int addGroup(DeviceGroup deviceGroup, int tenantId) throws GroupManagementDAOException {
PreparedStatement stmt = null;
ResultSet rs;
int groupId = -1;
try {
Connection conn = GroupManagementDAOFactory.getConnection();
String sql =
"INSERT INTO DM_GROUP(DESCRIPTION, GROUP_NAME, DATE_OF_CREATE, DATE_OF_LAST_UPDATE, "
+ "OWNER, TENANT_ID) VALUES (?, ?, ?, ?, ?, ?)";
stmt = conn.prepareStatement(sql, new String[] {"ID"});
stmt.setString(1, deviceGroup.getDescription());
stmt.setString(2, deviceGroup.getName());
stmt.setLong(3, new Date().getTime());
stmt.setLong(4, new Date().getTime());
stmt.setString(5, deviceGroup.getOwner());
stmt.setInt(6, tenantId);
stmt.executeUpdate();
rs = stmt.getGeneratedKeys();
if (rs.next()) {
groupId = rs.getInt(1);
}
return groupId;
} catch (SQLException e) {
throw new GroupManagementDAOException(
"Error occurred while adding deviceGroup '" + deviceGroup.getName() + "'", e);
} finally {
GroupManagementDAOUtil.cleanupResources(stmt, null);
}
}
@Override
public void addDevice(String groupName, String owner, int deviceId, int tenantId)
throws GroupManagementDAOException {
PreparedStatement stmt = null;
try {
Connection conn = GroupManagementDAOFactory.getConnection();
String sql =
"INSERT INTO DM_DEVICE_GROUP_MAP(DEVICE_ID, GROUP_ID, TENANT_ID) "
+ "VALUES (?, (SELECT ID as GROUP_ID FROM DM_GROUP "
+ "WHERE GROUP_NAME = ? AND OWNER = ? AND TENANT_ID = ?), ?)";
stmt = conn.prepareStatement(sql);
stmt.setInt(1, deviceId);
stmt.setString(2, groupName);
stmt.setString(3, owner);
stmt.setInt(4, tenantId);
stmt.setInt(5, tenantId);
stmt.executeUpdate();
stmt.getGeneratedKeys();
} catch (SQLException e) {
throw new GroupManagementDAOException(
"Error occurred while adding device to Group '" + groupName + "'", e);
} finally {
GroupManagementDAOUtil.cleanupResources(stmt, null);
}
}
@Override
public void deleteGroup(String groupName, String owner, int tenantId)
throws GroupManagementDAOException {
Connection conn;
PreparedStatement stmt = null;
try {
conn = GroupManagementDAOFactory.getConnection();
String sql =
"DELETE FROM DM_DEVICE_GROUP_MAP WHERE GROUP_ID = (SELECT ID AS GROUP_ID FROM DM_GROUP WHERE GROUP_NAME = ? AND OWNER = ? AND TENANT_ID = ?) AND TENANT_ID = ?";
stmt = conn.prepareStatement(sql);
stmt.setString(1, groupName);
stmt.setString(2, owner);
stmt.setInt(3, tenantId);
stmt.setInt(4, tenantId);
stmt.executeUpdate();
} catch (SQLException e) {
throw new GroupManagementDAOException(
"Error occurred while removing mappings for group '" + groupName + "'", e);
} finally {
GroupManagementDAOUtil.cleanupResources(stmt, null);
}
try {
conn = GroupManagementDAOFactory.getConnection();
String sql = "DELETE 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);
stmt.executeUpdate();
} catch (SQLException e) {
throw new GroupManagementDAOException(
"Error occurred while deleting group '" + groupName + "'", e);
} finally {
GroupManagementDAOUtil.cleanupResources(stmt, null);
}
}
@Override
public int getGroupCount(int tenantId) throws GroupManagementDAOException {
PreparedStatement stmt = null;
ResultSet resultSet = null;
try {
Connection conn = GroupManagementDAOFactory.getConnection();
String sql = "SELECT COUNT(ID) AS GROUP_COUNT FROM DM_GROUP WHERE TENANT_ID = ?";
stmt = conn.prepareStatement(sql);
stmt.setInt(1, tenantId);
resultSet = stmt.executeQuery();
if (resultSet.next()) {
return resultSet.getInt("GROUP_COUNT");
} else {
return 0;
}
} catch (SQLException e) {
throw new GroupManagementDAOException("Error occurred while getting group count'", e);
} finally {
GroupManagementDAOUtil.cleanupResources(stmt, resultSet);
}
}
@Override
public boolean isGroupExist(String groupName, String owner, int tenantId)
throws GroupManagementDAOException {
PreparedStatement stmt = null;
ResultSet resultSet = null;
try {
Connection conn = GroupManagementDAOFactory.getConnection();
String sql =
"SELECT GROUP_NAME 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();
return resultSet.next();
} catch (SQLException e) {
throw new GroupManagementDAOException(
"Error occurred while group Id listing by group name '" + groupName + "'", e);
} finally {
GroupManagementDAOUtil.cleanupResources(stmt, resultSet);
}
}