package org.smap.server;
import org.smap.client.mvc.model.DataTable;
import org.smap.client.interfaces.DatabaseService;
import org.smap.shared.FieldVerifier;
import com.google.gwt.user.server.rpc.RemoteServiceServlet;
import java.util.ArrayList;
import java.util.List;
import java.sql.*;
// Server side database service implementation
public class DatabaseServiceImpl extends RemoteServiceServlet implements
DatabaseService {
/**
*
*/
private static final long serialVersionUID = -1169372042797338569L;
// Database Conn settings
//private String url = "jdbc:postgresql://192.168.1.10/openstreetmap";
//private final String USERNAME = "openstreetmap";
//private final String PASSWORD = "bernard";
private String url = "jdbc:postgresql://127.0.0.1:5432/smapsurvey_test";
private final String USERNAME = "ws";
private final String PASSWORD = "ws1234";
//private final String PASSWORD = "pass1234.";
private Connection con = null;
private ResultSet rs = null;
private Statement st = null;
private String sqlstr;
private String getRGCount;
private String getCount;
private String getTableNames;
private String sqlOpt;
// these are for tmpSurveyQA table
private List<String> rTABLENAMES;
private List<String> rNAME;
private List<String> rANS;
private List<String> rWAYID;
private List<String> rQUESTION;
private List<String> rPWAYID;
private List<String> rCOLUMNNAMES;
private String[] RGTABLENAMES;
private String[] RNAME;
private String[] RANS;
private String[] RWAYID;
private String[] RPWAYID;
private String[] RQUESTION;
private String[] RCOLUMNNAMES;
// these are for tmpOptions table
private List<String> oRELID;
private List<String> oOPT;
private List<String> oSEL;
private List<String> oOTHER;
private List<String> oOPTION;
private List<String> oANS;
private String[] ORELID;
private String[] OOPT;
private String[] OSEL;
private String[] OOTHER;
private String[] OOPTION;
private String[] OANS;
private int count = 0;
private DataTable[] dataTable = null;
public DataTable[] getTableData(String surveyname)
throws IllegalArgumentException {
// verify survey name
if (!FieldVerifier.isValidSName(surveyname)) {
throw new IllegalArgumentException(
"Survey name must be at least 2 characters long");
}
String listSurveys = "SELECT DISTINCT tablename from smaptablecolumns WHERE surveyname = '"+surveyname+"'";
// query to get table names
getTableNames = "SELECT DISTINCT table_name FROM tmpSurveyQA;";
// query to get column header count
getCount = "SELECT count(node_id) FROM way_nodes "
+ "WHERE id = (SELECT member_id FROM relation_members WHERE member_role='firstWay' "
+ "AND id = (SELECT id FROM relation_tags WHERE v='"
+ surveyname + "' LIMIT 1));";
// get data from options table
sqlOpt = "SELECT relation_id,qoption,selected,other,textlabel,othertext FROM tmpoptions;";
try {
Class.forName("org.postgresql.Driver");
// connect to postgreSQL db
con = DriverManager.getConnection(url, USERNAME, PASSWORD);
st = con.createStatement();
// run the PL/PGSQL function
st.executeQuery(listSurveys);
rs = st.executeQuery(listSurveys);
rTABLENAMES = new ArrayList<String>();
while (rs.next()) {
//Need to append the "s_"value for the search
rTABLENAMES.add("s_" + rs.getString("tablename"));
}
RGTABLENAMES = rTABLENAMES.toArray(new String[0]);
int tableCount = RGTABLENAMES.length;
// create one dataTable object for each table, if tables exist
if (tableCount != 0)
dataTable = new DataTable[tableCount + 1];
// one table for each question group
for (int k = 0; k < tableCount; k++) {
dataTable[k] = new DataTable();
// set 0 for tmpSurveyQA
dataTable[k].setTableType(0);
// sql query to get specific table data
String sqlstr3 = "SELECT p.parent_id, w.* FROM smapWayParent p, "+ RGTABLENAMES[k] + " WHERE p.way_id = w.way_id;";
// Dump resultset into Array lists
rNAME = new ArrayList<String>();
rQUESTION = new ArrayList<String>();
rANS = new ArrayList<String>();
rWAYID = new ArrayList<String>();
rPWAYID = new ArrayList<String>();
rCOLUMNNAMES = new ArrayList<String>();
// see the query description
rs = st.executeQuery(sqlstr3);
while (rs.next()) {
// null check for column way_id
String rWayid = rs.getString("way_id");
if (rs.wasNull())
rWAYID.add(" ");
else
rWAYID.add(rWayid);
// null check for column parentway_id
String rPWayid = rs.getString("parent_id");
if (rs.wasNull())
rPWAYID.add(" ");
else
rPWAYID.add(rPWayid);
// null check for column name
String rName = rs.getString("name");
if (rs.wasNull())
rNAME.add(" ");
else
rNAME.add(rName);
// null check for column squestion
String rQuestion = rs.getString("squestion");
if (rs.wasNull())
rQUESTION.add(" ");
else
rQUESTION.add(rQuestion);
// null check for column answer
String rAns = rs.getString("answer");
if (rs.wasNull())
rANS.add(" ");
else
rANS.add(rAns);
}
/*
// List to String[]
RNAME = rNAME.toArray(new String[0]);
RANS = rANS.toArray(new String[0]);
RWAYID = rWAYID.toArray(new String[0]);
RPWAYID = rPWAYID.toArray(new String[0]);
RQUESTION = rQUESTION.toArray(new String[0]);
// sql query to get id of the repeating group
String sqlstr4 = "SELECT id from tmpSurveyQA where table_name='"
+ RGTABLENAMES[k] + "' LIMIT 1;";
rs = st.executeQuery(sqlstr4);
int id = 0;
while (rs.next()) {
id = Integer.parseInt(rs.getString("id"));
}
// get table specific column count, different for each table
try {
if (!RPWAYID[0].equals("")) {
// query to get repeat count
getRGCount = "SELECT id,count(node_id) FROM way_nodes where id =(SELECT id from way_nodes WHERE node_id="
+ id
+ ") AND id IN (SELECT member_id from relation_members where member_role='newWay'"
+ "AND ID IN (SELECT member_id FROM relation_members where member_role='repeat' AND id IN"
+ "(SELECT id FROM relation_tags WHERE v='"
+ surveyname + "'))) GROUP by id ORDER BY id;";
rs = st.executeQuery(getRGCount);
}
while (rs.next()) {
count = Integer.parseInt(rs.getString("count"));
}
} catch (NullPointerException NPE) {
rs = st.executeQuery(getCount);
while (rs.next()) {
count = Integer.parseInt(rs.getString("count"));
}
}
*/
int RQLENGTH = RQUESTION.length;
// transpose the table
if(!RWAYID[0].equals(" "))
rCOLUMNNAMES.add("way_id");
else
rCOLUMNNAMES.add("parentway_id");
for (int x = 0; x < RQLENGTH; x++) {
if (x == count) {
break;
}
if (!RNAME[x].equals(" "))
rCOLUMNNAMES.add(RNAME[x]);
else
rCOLUMNNAMES.add(RQUESTION[x]);
}
RCOLUMNNAMES = rCOLUMNNAMES.toArray(new String[0]);
// store result set in dataTable object
dataTable[k].setTableName(RGTABLENAMES[k]);
dataTable[k].setrCOLUMNNAMES(RCOLUMNNAMES);
dataTable[k].setrNAME(RNAME);
dataTable[k].setrANS(RANS);
dataTable[k].setrWAYID(RWAYID);
dataTable[k].setrPWAYID(RPWAYID);
dataTable[k].setrQUESTION(RQUESTION);
dataTable[k].setCount(count);
}
if (tableCount != 0) {
dataTable[tableCount] = new DataTable();
// set dataTable as options table
dataTable[tableCount].setTableType(1);
dataTable[tableCount].setTableName("Options");
// see the query description
rs = st.executeQuery(sqlOpt);
oRELID = new ArrayList<String>();
oOPT = new ArrayList<String>();
oSEL = new ArrayList<String>();
oOTHER = new ArrayList<String>();
oOPTION = new ArrayList<String>();
oANS = new ArrayList<String>();
while (rs.next()) {
oRELID.add(rs.getString("relation_id"));
oOPT.add(rs.getString("qoption"));
oSEL.add(rs.getString("selected"));
oOTHER.add(rs.getString("other"));
oOPTION.add(rs.getString("textlabel"));
oANS.add(rs.getString("othertext"));
}
// List to String[]
ORELID = oRELID.toArray(new String[0]);
OOPT = oOPT.toArray(new String[0]);
OSEL = oSEL.toArray(new String[0]);
OOTHER = oOTHER.toArray(new String[0]);
OOPTION = oOPTION.toArray(new String[0]);
OANS = oANS.toArray(new String[0]);
dataTable[tableCount].setoRELID(ORELID);
dataTable[tableCount].setoOPT(OOPT);
dataTable[tableCount].setoSEL(OSEL);
dataTable[tableCount].setoOTHER(OOTHER);
dataTable[tableCount].setoOPTION(OOPTION);
dataTable[tableCount].setoANS(OANS);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
// close the connection
try {
rs.close();
st.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
return dataTable;
}
}
|