private static List<String> selectCoordinateIdsByNodeIds(
Connection con, List<String> fatherPaths, CoordinatePK pk) throws SQLException {
ResultSet rs = null;
String fatherPath = "";
String whereClause = "";
String fatherId = "";
String rootFatherId = "";
int axisToMatch = fatherPaths.size();
if (fatherPaths != null) {
Iterator<String> it = fatherPaths.iterator();
while (it.hasNext()) {
fatherPath = it.next();
// enleve le premier /0/
fatherPath = fatherPath.substring(1);
fatherPath = fatherPath.substring(fatherPath.indexOf('/') + 1, fatherPath.length());
// extrait l'id
fatherId = fatherPath.substring(fatherPath.lastIndexOf('/') + 1, fatherPath.length());
// extrait l'id de la racine
rootFatherId = fatherPath.substring(0, fatherPath.indexOf('/'));
whereClause += " nodeId = " + fatherId;
whereClause += " or (nodeId = " + rootFatherId + " and coordinatesLeaf = '1') ";
if (it.hasNext()) {
whereClause += " Or ";
}
}
}
String selectStatement = "select coordinatesId, count(*) " + "from " + pk.getTableName() + " ";
if (fatherPaths != null && fatherPaths.size() > 0) {
selectStatement += "where " + whereClause;
}
selectStatement += " And instanceId = '" + pk.getComponentName() + "' ";
selectStatement += " GROUP BY coordinatesId";
PreparedStatement prepStmt = null;
try {
prepStmt = con.prepareStatement(selectStatement);
rs = prepStmt.executeQuery();
List<String> list = new ArrayList<String>();
int coordinateId = 0;
int nbMatches = 0;
while (rs.next()) {
coordinateId = rs.getInt(1);
nbMatches = rs.getInt(2);
if (nbMatches == axisToMatch) {
list.add(new Integer(coordinateId).toString());
}
}
return list;
} finally {
DBUtil.close(rs, prepStmt);
}
}
/**
* Method declaration
*
* @param con
* @param pk
* @param coordinatePoints
* @throws SQLException
* @see
*/
public static void removeCoordinatesByPoints(
Connection con, CoordinatePK pk, List<String> coordinatePoints) throws SQLException {
StringBuilder deleteQuery = new StringBuilder("DELETE FROM sb_coordinates_coordinates WHERE ");
if (coordinatePoints != null) {
Iterator<String> it = coordinatePoints.iterator();
deleteQuery.append("(");
while (it.hasNext()) {
String pointId = it.next();
deleteQuery.append(" nodeId = ").append(pointId);
if (it.hasNext()) {
deleteQuery.append(" or ");
}
}
deleteQuery.append(" ) ");
}
deleteQuery.append(" AND instanceId ='").append(pk.getComponentName()).append("'");
Statement stmt = null;
try {
stmt = con.createStatement();
stmt.executeUpdate(deleteQuery.toString());
} finally {
DBUtil.close(stmt);
}
}
/**
* Method declaration
*
* @param con
* @param pk
* @return
* @throws SQLException
* @see
*/
private static Coordinate selectCoordinateByCoordinatePK(Connection con, CoordinatePK pk)
throws SQLException {
List<CoordinatePoint> list = new ArrayList<CoordinatePoint>();
int id = Integer.parseInt(pk.getId());
PreparedStatement prepStmt = null;
ResultSet rs = null;
try {
prepStmt = con.prepareStatement(SELECT_BY_PK);
prepStmt.setInt(1, id);
prepStmt.setString(2, "1");
prepStmt.setString(3, pk.getComponentName());
rs = prepStmt.executeQuery();
while (rs.next()) {
list.add(getCoordinatePointFromResultSet(rs));
}
} finally {
DBUtil.close(rs, prepStmt);
}
return new Coordinate(id, list);
}
/**
* Method declaration
*
* @param con
* @param pk
* @return
* @throws SQLException
* @see
*/
static Collection<String> getCoordinateIds(Connection con, CoordinatePK pk) throws SQLException {
List<String> coordinateIds = new ArrayList<String>();
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = con.prepareStatement(SELECT_BY_COMPONENT);
pstmt.setString(1, pk.getComponentName());
rs = pstmt.executeQuery();
while (rs.next()) {
coordinateIds.add(rs.getString("coordinatesid"));
}
} finally {
DBUtil.close(rs, pstmt);
}
return coordinateIds;
}
/**
* Method declaration
*
* @param con
* @param pk
* @return
* @throws SQLException
* @see
*/
private static int getMaxDisplayOrder(Connection con, CoordinatePK pk) throws SQLException {
int maxFromTable = 0;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = con.prepareStatement(SELECT_MAX_ORDER);
pstmt.setString(1, pk.getComponentName());
rs = pstmt.executeQuery();
if (rs.next()) {
maxFromTable = rs.getInt(1);
}
} finally {
DBUtil.close(rs, pstmt);
}
return maxFromTable;
}
/**
* Method declaration
*
* @param con
* @param pk
* @param nodeId
* @return
* @throws SQLException
* @see
*/
public static Collection<String> getCoordinateIdsByNodeId(
Connection con, CoordinatePK pk, String nodeId) throws SQLException {
List<String> coordinateIds = new ArrayList<String>();
PreparedStatement prepStmt = null;
ResultSet rs = null;
try {
prepStmt = con.prepareStatement(SELECT_BY_NODEID);
prepStmt.setInt(1, new Integer(nodeId).intValue());
prepStmt.setString(2, pk.getComponentName());
rs = prepStmt.executeQuery();
while (rs.next()) {
coordinateIds.add(rs.getString("id"));
}
} finally {
DBUtil.close(rs, prepStmt);
}
return coordinateIds;
}
/**
* Method declaration
*
* @param con
* @param pk
* @param point
* @param coordinateId
* @throws SQLException
* @see
*/
private static void addCoordinatePoint(
Connection con, CoordinatePK pk, CoordinatePoint point, int coordinateId)
throws SQLException {
PreparedStatement prepStmt = null;
try {
prepStmt = con.prepareStatement(INSERT_COORDINATE);
prepStmt.setInt(1, coordinateId);
prepStmt.setInt(2, point.getNodeId());
if (point.isLeaf()) {
prepStmt.setString(3, "1");
} else {
prepStmt.setString(3, "0");
}
prepStmt.setInt(4, point.getOrder());
prepStmt.setString(5, pk.getComponentName());
prepStmt.executeUpdate();
} finally {
DBUtil.close(prepStmt);
}
}
/**
* Method declaration
*
* @param con the Connection to database
* @param fatherIds an ArrayList of nodeId
* @param pk a CoordinatePK
* @return an ArrayList which contains CoordinatePoint corresponding to fatherIds
* @throws SQLException
* @see
*/
private static List<CoordinatePoint> selectCoordinatePointsByNodeIds(
Connection con, List<Integer> fatherIds, CoordinatePK pk) throws SQLException {
List<CoordinatePoint> list = new ArrayList<CoordinatePoint>();
StringBuilder whereClause = new StringBuilder(20 * fatherIds.size() + 200);
if (fatherIds != null) {
Iterator<Integer> it = fatherIds.iterator();
whereClause.append("(");
while (it.hasNext()) {
whereClause.append(" nodeId = ").append(it.next());
if (it.hasNext()) {
whereClause.append(" OR ");
} else {
whereClause.append(" ) ");
}
}
}
String selectQuery =
"SELECT coordinatesId, nodeId, coordinatesLeaf, coordinatesDisplayOrder, "
+ "instanceId FROM sb_coordinates_coordinates WHERE "
+ whereClause
+ " AND instanceId = '"
+ pk.getComponentName()
+ "' "
+ " ORDER BY coordinatesId, nodeId ASC";
Statement stmt = null;
ResultSet rs = null;
try {
stmt = con.createStatement();
rs = stmt.executeQuery(selectQuery);
while (rs.next()) {
CoordinatePoint coordinatePoint = getCoordinatePointFromResultSet(rs);
list.add(coordinatePoint);
}
} finally {
DBUtil.close(rs, stmt);
}
return list;
}
