public void connectItems(String query)
// PRE: query must be initialized
// POST: Updates the list of Items based on the query.
{
String driver = "org.apache.derby.jdbc.ClientDriver"; // Driver for DB
String url = "jdbc:derby://localhost:1527/ShopDataBase"; // Url for DB
String user = "******"; // Username for db
String pass = "******"; // Password for db
Connection myConnection; // Connection obj to db
Statement stmt; // Statement to execute a result appon
ResultSet results; // A set containing the returned results
int rowcount; // Num objects in the resultSet
int i; // Index for the array
try { // Try connection to db
Class.forName(driver).newInstance(); // Create our db driver
myConnection = DriverManager.getConnection(url, user, pass); // Initalize our connection
stmt =
myConnection.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE); // Create a new statement
results = stmt.executeQuery(query); // Store the results of our query
rowcount = 0;
if (results.last()) // Go to the last result
{
rowcount = results.getRow();
results.beforeFirst();
}
itemsArray = new Item[rowcount];
i = 0;
while (results.next()) // Itterate through the results set
{
itemsArray[i] =
new Item(
results.getInt("item_id"),
results.getString("item_name"),
results.getString("item_type"),
results.getInt("price"),
results.getInt("owner_id"),
results.getString("item_path")); // Creat new Item
i++;
}
results.close(); // Close the ResultSet
stmt.close(); // Close the statement
myConnection.close(); // Close the connection to db
} catch (Exception e) // Cannot connect to db
{
System.err.println(e.toString());
System.err.println("Error, something went horribly wrong in connectItems!");
}
}
public SingleNameReport(WritableWorkbook workbook, Statement new_statement, String[] arguments) {
String main_tsn = "";
String main_rank = "";
String main_kingdom = "";
row = 0;
try {
statement = new_statement;
func = new AdditionalFunctions(statement);
copy = workbook;
kingdom = arguments[0];
hrank = arguments[1];
lrank = arguments[2];
scientificName = arguments[3];
date_from = arguments[4];
date_to = arguments[5];
user = arguments[6];
String temp = "";
temp =
"SELECT tsn,rank_id,kingdom_id from Tree where scientificName='" + scientificName + "'";
System.out.println(temp);
result = statement.executeQuery(temp);
metadata = result.getMetaData();
int numberOfRows = 0;
int hrankVal = Integer.parseInt(hrank);
int main_rankVal = 0;
if (result.last()) numberOfRows = result.getRow();
result.first();
if (numberOfRows > 0 && metadata.getColumnCount() > 0) {
main_tsn = result.getString(1);
main_rank = result.getString(2);
main_rankVal = Integer.parseInt(main_rank);
main_kingdom = result.getString(3);
}
if (HasChildren(main_tsn) == true) FindChildInformation(main_tsn, arguments, func);
FileInFrontPage frontPage = new FileInFrontPage(copy, statement, arguments);
copy.write();
copy.close();
} catch (Exception e) {
e.printStackTrace();
System.exit(1);
}
} // end of constructor
// public method that checks for existance of children
public boolean HasChildren(String tsn) {
String temp = "SELECT tsn from Tree where parent_tsn='" + tsn + "'";
if (lrank.compareTo("0") != 0 && lrank.compareTo("7") != 0)
temp = temp + " AND rank_id<=" + lrank;
System.out.println(temp);
int numberOfRows = 0;
boolean flag = false;
try {
result = statement.executeQuery(temp);
metadata = result.getMetaData();
if (result.last()) numberOfRows = result.getRow();
result.first();
if (numberOfRows > 0 && metadata.getColumnCount() > 0) flag = true;
} catch (SQLException sql) {
sql.printStackTrace();
System.exit(1);
}
return flag;
} // end of method HasChildren
public synchronized void service(HttpServletRequest request, HttpServletResponse response)
throws IOException, ServletException {
HttpSession dbSession = request.getSession();
JspFactory _jspxFactory = JspFactory.getDefaultFactory();
PageContext pageContext =
_jspxFactory.getPageContext(this, request, response, "", true, 8192, true);
ServletContext dbApplication = dbSession.getServletContext();
nseer_db_backup1 stock_db = new nseer_db_backup1(dbApplication);
try {
if (stock_db.conn((String) dbSession.getAttribute("unit_db_name"))) {
int i;
int intRowCount;
String sqll =
"select * from stock_config_public_char where describe1='\u51fa\u5165\u5e93\u7406\u7531'";
ResultSet rs = stock_db.executeQuery(sqll);
rs.next();
rs.last();
intRowCount = rs.getRow();
String[] del = new String[intRowCount];
del = (String[]) dbSession.getAttribute("del");
if (del != null) {
for (i = 1; i <= intRowCount; i++) {
String sql = "delete from stock_config_public_char where id='" + del[i - 1] + "'";
stock_db.executeUpdate(sql);
}
}
stock_db.commit();
stock_db.close();
response.sendRedirect("stock/config/apply_gather_pay/reason.jsp");
} else {
response.sendRedirect("error_conn.htm");
}
} catch (Exception ex) {
ex.printStackTrace();
}
}
// public method to extract and write the Publication information
public static void WritePublication(String publicationId, int rowNum) {
WritableSheet sheet2 = copy.getSheet(1);
try {
String query =
"SELECT address,author,publicationTitle,chapter,edition,editor,month,day,note,number,publisher,"
+ "series,title,volume,year,isbn,issn,pages FROM Publication WHERE id="
+ publicationId;
System.out.println(query);
ResultSet result_query = statement.executeQuery(query);
ResultSetMetaData metadata_query = result_query.getMetaData();
int numberOfRows = 0;
if (result_query.last()) numberOfRows = result_query.getRow();
result_query.first();
if (numberOfRows > 0 && metadata_query.getColumnCount() > 0) {
String aut_edi = result_query.getString(2);
System.out.println("autor is " + aut_edi);
if (result_query.getString(6).compareTo("") != 0)
aut_edi = aut_edi + " / " + result_query.getString(6) + ",eds.";
Label label = new Label(7, rowNum, aut_edi);
sheet2.addCell(label);
label = new Label(8, rowNum, result_query.getString(13));
sheet2.addCell(label);
String publication_name = result_query.getString(3);
if (result_query.getString(12).compareTo("") != 0)
publication_name = publication_name + ", ser. " + result_query.getString(12);
if (result_query.getString(14).compareTo("0") != 0)
publication_name = publication_name + ", vol. " + result_query.getString(14);
if (result_query.getString(10).compareTo("0") != 0)
publication_name = publication_name + ",no. " + result_query.getString(10);
label = new Label(9, rowNum, publication_name);
sheet2.addCell(label);
label = new Label(12, rowNum, result_query.getString(11));
sheet2.addCell(label);
label = new Label(13, rowNum, result_query.getString(1));
sheet2.addCell(label);
label = new Label(15, rowNum, result_query.getString(9));
sheet2.addCell(label);
label = new Label(16, rowNum, result_query.getString(16));
sheet2.addCell(label);
label = new Label(17, rowNum, result_query.getString(17));
sheet2.addCell(label);
label = new Label(14, rowNum, result_query.getString(18));
sheet2.addCell(label);
String date = result_query.getString(15);
String month = "Jan";
System.out.println("month is " + result_query.getString(7));
if (result_query.getString(7).compareTo("") != 0) month = result_query.getString(7);
month = func.ConvertMonth(month);
date = month + "/" + date;
String day = "01";
if (result_query.getString(8).compareTo("0") != 0) day = result_query.getString(8);
if (day.length() == 1) day = "0" + day;
date = day + "/" + date;
label = new Label(10, rowNum, date);
sheet2.addCell(label);
label = new Label(11, rowNum, date);
sheet2.addCell(label);
}
} catch (Exception e) {
e.printStackTrace();
System.exit(1);
}
} // end of WritePublication
// public method to find child information
public void FindChildInformation(String tsn, String[] arguments, AdditionalFunctions func) {
WritableSheet sheet2 = copy.getSheet(1);
Label label = null;
String children[] = null;
String temp = "", current_tsn = "", dateCreated = "";
int dateCheck = 1;
temp = "SELECT distinct tsn from Tree where parent_tsn='" + tsn + "'";
try {
result = statement.executeQuery(temp);
metadata = result.getMetaData();
int numberOfRows = 0;
if (result.last()) numberOfRows = result.getRow();
if (numberOfRows > 0 && metadata.getColumnCount() > 0) {
result.first();
children = new String[numberOfRows];
for (int j = 0; j < numberOfRows; j++) {
children[j] = result.getString(1);
result.next();
}
}
for (int j = 0; j < numberOfRows; j++) {
int current_tsnId = Integer.parseInt(children[j]);
if (current_tsnId >= 999000000) {
temp =
"SELECT distinct Tree.tsn,unit_name1,unit_name2,unit_name3,unit_name4,scientificName,`usage`,"
+ "taxon_author,parent_tsn,rank_name, pages, publicationId, userId, dateCreated, nameType FROM Tree,"
+ " BaseObject, TaxonConcepts, TaxonAuthors,TaxonUnitTypes"
+ " WHERE Tree.tsn=TaxonConcepts.tsn AND Tree.tsn='"
+ current_tsnId
+ "' AND "
+ "BaseObject.id=TaxonConcepts.id AND TaxonAuthors.taxon_author_id=Tree.taxon_author_id AND "
+ "Tree.rank_id=TaxonUnitTypes.rank_id";
System.out.println(temp);
result = statement.executeQuery(temp);
metadata = result.getMetaData();
int RowNumber = 0;
if (result.last()) RowNumber = result.getRow();
if (RowNumber > 0 && metadata.getColumnCount() > 0) {
current_tsn = result.getString(1);
dateCreated = result.getString(14);
DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
Date d1 = df.parse(dateCreated);
Date d2 = null;
if (date_from.compareTo("all") != 0) {
d2 = df.parse(date_from);
if (d2.before(d1) || d2.equals(d1)) dateCheck = 1;
else dateCheck = 0;
}
if (date_to.compareTo("today") != 0) {
d2 = df.parse(date_to);
if (d1.before(d2) || d1.equals(d2)) dateCheck = 1;
else dateCheck = 0;
}
if (result.getString(7).compareTo("public") == 0
&& result.getString(15).compareTo("Regular scientific name") == 0
&& result.getString(12).compareTo("") != 0
&& dateCheck == 1) {
String scientificName = result.getString(6);
String taxon_author = result.getString(8);
String publicationId = result.getString(12);
for (int k = 0; k < 4; k++) {
if (result.getString(k + 2).compareTo("null") != 0) {
label = new Label(k, row + 1, result.getString(k + 2));
sheet2.addCell(label);
}
}
label = new Label(4, row + 1, taxon_author);
sheet2.addCell(label);
label = new Label(5, row + 1, result.getString(10));
sheet2.addCell(label);
String parent_tsn = result.getString(9);
String userId = result.getString(13);
WritePublication(publicationId, row + 1);
label = new Label(18, row + 1, func.GetSubmitter(userId));
sheet2.addCell(label);
label = new Label(6, row + 1, func.GetParent(parent_tsn));
sheet2.addCell(label);
row = row + 1;
}
}
}
continue;
}
for (int j = 0; j < numberOfRows; j++) {
if (HasChildren(children[j]) == true) FindChildInformation(children[j], arguments, func);
else continue;
}
} catch (Exception e) {
e.printStackTrace();
System.exit(1);
}
} // end of FindChildInformation
public void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
resp.setContentType("text/html");
PrintWriter out = resp.getWriter();
Connection con = null; // init DB objects
PreparedStatement pstmt = null;
Statement stmt = null;
ResultSet rs = null;
try {
con = dbConn.Connect("demopaul");
} catch (Exception ignore) {
}
String stype_id = req.getParameter("type_id");
int type_id = 0;
String sgroup_id = req.getParameter("group_id");
int group_id = 0;
String sitem_id = req.getParameter("item_id");
int item_id = 0;
try {
type_id = Integer.parseInt(stype_id);
} catch (NumberFormatException ignore) {
}
try {
group_id = Integer.parseInt(sgroup_id);
} catch (NumberFormatException ignore) {
}
try {
item_id = Integer.parseInt(sitem_id);
} catch (NumberFormatException ignore) {
}
out.println(
"<!-- type_id=" + type_id + ", group_id=" + group_id + ", item_id=" + item_id + " -->");
out.println("<script>");
out.println("function load_types() {");
out.println(" try {document.forms['frmSelect'].item_id.selectedIndex = -1; } catch (err) {}");
out.println(" document.forms['frmSelect'].group_id.selectedIndex = -1;");
out.println(" document.forms['frmSelect'].submit();");
out.println("}");
out.println("function load_groups() {");
out.println(" document.forms['frmSelect'].submit();");
out.println("}");
out.println("</script>");
out.println("<form name=frmSelect>");
// LOAD ACTIVITY TYPES
out.println("<select name=type_id onchange=\"load_types()\">");
if (type_id == 0) {
out.println("<option>CHOOSE TYPE</option>");
}
try {
stmt = con.createStatement();
rs = stmt.executeQuery("SELECT * FROM activity_types");
while (rs.next()) {
Common_Config.buildOption(rs.getInt("type_id"), rs.getString("type_name"), type_id, out);
}
stmt.close();
} catch (Exception exc) {
out.println("<p>ERROR:" + exc.toString() + "</p>");
}
out.println("");
out.println("</select>");
// LOAD ACTIVITIES BY GROUP TYPE
out.println("<select name=group_id onchange=\"load_groups()\">");
if (type_id == 0) {
out.println("<option>CHOOSE TYPE</option>");
} else {
try {
stmt = con.createStatement();
rs =
stmt.executeQuery(
"SELECT group_id, group_name FROM activity_groups WHERE type_id = " + type_id);
rs.last();
if (rs.getRow() == 1) {
group_id = rs.getInt("group_id");
out.println("<!-- ONLY FOUND 1 GROUP -->");
} else {
out.println("<option value=\"0\">CHOOSE...</option>");
}
rs.beforeFirst();
while (rs.next()) {
Common_Config.buildOption(
rs.getInt("group_id"), rs.getString("group_name"), group_id, out);
}
stmt.close();
} catch (Exception exc) {
out.println("<p>ERROR:" + exc.toString() + "</p>");
}
}
out.println("");
out.println("</select>");
if (group_id > 0) { // || sitem_id != null
// LOAD ACTIVITIES BY ITEM TYPE
out.println("<select name=item_id onchange=\"load_times()\">");
if (group_id == 0) {
out.println("<option value=\"0\">CHOOSE GROUP</option>");
} else {
try {
stmt = con.createStatement();
rs =
stmt.executeQuery(
"SELECT item_id, item_name FROM activity_items WHERE group_id = " + group_id);
rs.last();
if (rs.getRow() == 1) {
item_id = rs.getInt("item_id");
out.println("<!-- ONLY FOUND 1 ITEM -->");
} else {
out.println("<option value=\"0\">CHOOSE...</option>");
}
rs.beforeFirst();
while (rs.next()) {
Common_Config.buildOption(
rs.getInt("item_id"), rs.getString("item_name"), item_id, out);
}
stmt.close();
} catch (Exception exc) {
out.println("<p>ERROR:" + exc.toString() + "</p>");
}
}
out.println("");
out.println("</select>");
}
out.println("</form>");
out.println("<p><a href=\"Member_genrez\">Reset</a></p>");
try {
con.close();
} catch (Exception ignore) {
}
out.close();
}
public static void createExternalTable(
Connection conn,
String osServer,
String refreshType,
String sourceTable,
String targetSchema,
String targetTable,
String maxId,
int queueId,
int jobPort)
throws SQLException {
String method = "createExternalTable";
int location = 1000;
try {
location = 2000;
String externalTable = getExternalTableName(targetSchema, targetTable);
location = 2100;
Statement stmt = conn.createStatement();
String createSQL =
"CREATE EXTERNAL TABLE \"" + externalSchema + "\".\"" + externalTable + "\" \n (";
location = 2309;
String strSQL =
"SELECT c.column_name, \n"
+ " CASE WHEN c.data_type = 'character' THEN c.data_type || '(' || c.character_maximum_length || ')' ELSE c.data_type END AS data_type \n"
+ "FROM INFORMATION_SCHEMA.COLUMNS c \n"
+ "WHERE table_schema = '"
+ targetSchema
+ "' \n"
+ " AND table_name = '"
+ targetTable
+ "' \n"
+ "ORDER BY ordinal_position";
location = 2400;
ResultSet rs = stmt.executeQuery(strSQL);
location = 2500;
while (rs.next()) {
location = 2600;
if (rs.getRow() == 1) {
location = 2700;
createSQL = createSQL + "\"" + rs.getString(1) + "\" " + rs.getString(2);
} else {
location = 2800;
createSQL = createSQL + ", \n \"" + rs.getString(1) + "\" " + rs.getString(2);
}
}
location = 2900;
createSQL = createSQL + ") \n";
////////////////////////////////////////////
// Create location for External Table
////////////////////////////////////////////
location = 3000;
// replace space in the maxId because this could now be a date
maxId = maxId.replace(" ", "SPACE");
location = 3100;
String extLocation =
"LOCATION ('gpfdist://"
+ osServer
+ ":"
+ jobPort
+ "/config.properties+"
+ queueId
+ "+"
+ maxId
+ "+"
+ refreshType
+ "+"
+ sourceTable
+ "#transform=externaldata"
+ "')";
location = 3400;
extLocation = extLocation + "\n" + "FORMAT 'TEXT' (delimiter '|' null 'null' escape '\\\\')";
////////////////////////////////////////////
// Add createSQL with Java Command to exec.
////////////////////////////////////////////
location = 3500;
createSQL = createSQL + extLocation;
////////////////////////////////////////////
// Create new external web table
////////////////////////////////////////////
location = 4000;
if (debug) Logger.printMsg("Creating External Table: " + createSQL);
stmt.executeUpdate(createSQL);
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
} catch (Exception e) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + e.getMessage() + ")");
}
}
