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 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 main(String arg[]) {
Hashtable ignoreList = new Hashtable();
Class cl = null;
Model model = null;
System.out.println("Synchronizing forms with database...");
Db.init();
try {
DatabaseMetaData meta = Db.getCon().getMetaData();
String[] types = {"TABLE"};
ResultSet rs = meta.getTables(null, null, "%", types);
// read ignore.list
ignoreList = AutogenerateUtil.readIgnoreList();
// prepare directory
File fDir = new File("../../web/WEB-INF/views/crud_form");
if (!fDir.exists()) fDir.mkdir();
while (rs.next()) {
// proper file name generationm
String className = "";
String tableName = rs.getString("TABLE_NAME");
className = StringUtil.toProperClassName(tableName); // space allowed...
// tableName = tableName.toUpperCase(); //If Oracle that need uppercase tablename. In MySQL
// in Mac OS X (and probably Linux), it mustbe case sensitive
// open table
String sql = "select * from " + tableName;
PreparedStatement pstmt =
Db.getCon()
.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet resultSet = pstmt.executeQuery();
ResultSetMetaData metaColumn = resultSet.getMetaData();
int nColoumn = metaColumn.getColumnCount();
// get foreign keys,and stored it in hashtable
ResultSet rsFk = meta.getImportedKeys(Db.getCon().getCatalog(), null, tableName);
Hashtable hashFk = new Hashtable();
System.out.println("FK Infos for table " + tableName);
while (rsFk.next()) {
String pkTableName = rsFk.getString("PKTABLE_NAME");
String pkColumnName = rsFk.getString("PKCOLUMN_NAME");
String fkColumnName = rsFk.getString("FKCOLUMN_NAME");
int fkSequence = rsFk.getInt("KEY_SEQ");
System.out.println(
tableName + "." + fkColumnName + " => " + pkTableName + "." + pkColumnName);
hashFk.put(fkColumnName, pkColumnName);
hashFk.put(fkColumnName + "_table", pkTableName);
}
rsFk.close();
// create form page
System.out.println(
"Creating form page for "
+ tableName
+ " from table + "
+ application.config.Database.DB
+ "."
+ tableName);
fDir = new File("../../web/WEB-INF/views/" + tableName);
if (!fDir.exists()) fDir.mkdir();
File f = new File("../../web/WEB-INF/views/" + tableName + "/form_" + tableName + ".jsp");
if (ignoreList.get("form_" + tableName + ".jsp") != null) {
Logger.getLogger(GenerateForm.class.getName())
.log(Level.INFO, "Ignoring creation of form_" + tableName + ".jsp");
} else {
Writer out = new FileWriter(f);
out.write(
"<%@ page contentType=\"text/html; charset=UTF-8\" language=\"java\" import=\"java.sql.*,recite18th.library.Db,application.config.Config,recite18th.library.Pagination\" %>");
out.write("<%@ taglib uri=\"http://java.sun.com/jsp/jstl/core\" prefix=\"c\" %>\n");
out.write("<%@ taglib uri=\"http://java.sun.com/jsp/jstl/functions\" prefix=\"fn\" %>\n");
// create model for this class, use in detecting its PK Field
cl = Class.forName("application.models." + className + "Model");
model = (Model) cl.newInstance();
// iterate all columns
resultSet.beforeFirst();
resultSet.next();
out.write(
"<table border=\"1\" align=\"center\" cellpadding=\"0\" cellspacing=\"0\" bordercolor=\"#E8EDFF\">\n");
out.write("<tr>\n");
out.write("<td>\n");
out.write(
"<form action=\"<%=Config.base_url%>index/"
+ className
+ "/save\" method=\"post\" enctype=\"multipart/form-data\">\n"); // I hope it's
// okay to
// default it to
// multipart data
out.write("<table id=\"hor-zebra\" summary=\"Form " + className + "\">\n");
out.write("<thead>\n");
out.write("<tr>\n");
out.write("<th colspan=\"2\" class=\"odd\" scope=\"col\">Form " + className + " </th>\n");
out.write("</tr>\n");
out.write("</thead>\n");
out.write("<tbody>\n");
for (int i = 1; i <= nColoumn; i++) {
String columnName = metaColumn.getColumnName(i);
String dataType = metaColumn.getColumnClassName(i);
out.write("<tr>\n");
// if(!columnName.equals(model.getPkFieldName())) // implementing the case of PK not
// AutoIncrement
// if(!metaColumn.isAutoIncrement(i))
// {
// varying field input for type
// foreign field, as chooser page view
if (hashFk.get(columnName)
!= null) // TODO: what if PK is chooser also?? :) CUrrently I add it manually the
// hidden_*Pk_nama* field
{
String fkTableName = hashFk.get(columnName + "_table") + "";
String fkColumnName = hashFk.get(columnName) + "";
String fkController = StringUtil.toProperClassName(fkTableName);
out.write("<td>" + columnName + "</td>\n");
out.write("<td>\n");
out.write(
"<input name=\""
+ columnName
+ "\" type=\"hidden\" id=\""
+ columnName
+ "\" value=\"${model."
+ columnName
+ "}\"/>\n");
out.write(
"<input name=\"label_"
+ columnName
+ "\" readonly=\"true\" type=\"text\" id=\"label_"
+ columnName
+ "\" value=\"${model."
+ columnName
+ "}\"/>\n"); // TODO : translate I
out.write(
"<a href=\"<%=Config.base_url%>index/"
+ fkController
+ "/chooseView?height=220&width=700\" class=\"thickbox\">Pilih</a>");
out.write("</td>\n");
} else {
// regular field input, not foreign key case
if (!columnName.equals(model.getPkFieldName())) {
out.write("<td>" + columnName + "</td>\n");
out.write("<td>\n");
// ENUM Column, displayed as HTML SELECT. May will only work for mysql only...
Logger.getLogger(GenerateForm.class.getName())
.log(Level.INFO, columnName + " type is " + metaColumn.getColumnType(i));
if (metaColumn.getColumnType(i) == 1) {
String enum_content[][] =
Db.getDataSet(
"SELECT SUBSTRING(COLUMN_TYPE,6,length(SUBSTRING(COLUMN_TYPE,6))-1) as enum_content "
+ " FROM information_schema.COLUMNS "
+ " WHERE TABLE_NAME='"
+ tableName
+ "' "
+ " AND COLUMN_NAME='"
+ columnName
+ "'");
if (enum_content.length > 0) {
// Logger.getLogger(Model.class.getName()).log(Level.INFO, "Enum Content = " +
// enum_content[0][0]);
String enum_list[] = enum_content[0][0].split(",");
out.write("<select name=\"" + columnName + "\" id=\"" + columnName + "\">\n");
for (int ienum_list = 0; ienum_list < enum_list.length; ienum_list++)
out.write(
"\t<option <c:if test=\"${model."
+ columnName
+ "=='"
+ enum_list[ienum_list].substring(
1, enum_list[ienum_list].length() - 1)
+ "'}\"> selected=\"selected\" </c:if> value=\""
+ enum_list[ienum_list].substring(
1, enum_list[ienum_list].length() - 1)
+ "\">"
+ enum_list[ienum_list].substring(
1, enum_list[ienum_list].length() - 1)
+ "</option>\n");
out.write("</select>\n\n");
} else {
// no enum content detected.. :)
out.write(
"<input name=\""
+ columnName
+ "\" type=\"text\" id=\""
+ columnName
+ "\" value=\"${model."
+ columnName
+ "}\"/>\n");
}
} else if (metaColumn.getColumnType(i) == 91) {
out.write(
"<input name=\""
+ columnName
+ "\" type=\"text\" id=\""
+ columnName
+ "\" value=\"${model."
+ columnName
+ "}\"/>\n");
out.write("<script>\n");
out.write(
" if(!isValidDate($('#"
+ columnName
+ "').val())) $('#"
+ columnName
+ "').val('1980-1-1');\n"); // TODO: default value
out.write(" (function($){\n");
out.write(" $('#" + columnName + "').click(function() {\n");
out.write(" $('#" + columnName + "').DatePickerShow();\n");
out.write(" });\n");
out.write(" $('#" + columnName + "').DatePicker({\n");
out.write(" format:'Y-m-d',\n");
out.write(" date: $('#" + columnName + "').val(),\n");
out.write(" current: $('#" + columnName + "').val(),\n");
out.write(" starts: 1,\n");
out.write(" position: 'r',\n");
out.write(" onBeforeShow: function(){\n");
out.write(
" $('#"
+ columnName
+ "').DatePickerSetDate($('#"
+ columnName
+ "').val(), true);\n");
out.write(" },\n");
out.write(" onChange: function(formated, dates){\n");
out.write(" $('#" + columnName + "').DatePickerHide();\n");
out.write(" $('#" + columnName + "').val(formated);\n");
out.write(" }\n");
out.write(" });\n");
out.write(" })(jQuery)\n");
out.write(" </script>\n");
} else {
out.write(
"<input name=\""
+ columnName
+ "\" type=\"text\" id=\""
+ columnName
+ "\" value=\"${model."
+ columnName
+ "}\"/>\n");
out.write("${" + columnName + "_error}\n"); // regular input field
}
} else { // PK case
if (metaColumn.isAutoIncrement(i)) {
out.write(
"<input name=\"hidden_"
+ columnName
+ "\" type=\"hidden\" id=\"hidden_"
+ columnName
+ "\" value=\"${model."
+ columnName
+ "}\"/>\n");
} else {
out.write("<td>" + columnName + "</td>\n");
out.write("<td>\n");
out.write(
"<input name=\""
+ columnName
+ "\" type=\"text\" id=\""
+ columnName
+ "\" value=\"${model."
+ columnName
+ "}\"/>\n");
out.write("${" + columnName + "_error}\n");
out.write(
"<input name=\"hidden_"
+ columnName
+ "\" type=\"hidden\" id=\"hidden_"
+ columnName
+ "\" value=\"${model."
+ columnName
+ "}\"/>\n");
}
}
out.write("</td>\n");
}
out.write("</tr>\n");
}
out.write("<tr class=\"odd\">\n");
out.write("<td> </td>\n");
out.write("<td><input type=\"submit\" name=\"Submit\" value=\"Simpan\">");
out.write(
"<input name=\"Button\" type=\"button\" id=\"Submit\" value=\"Batal\" onClick=\"javascript:history.back(-1);\"></td>\n");
out.write("</tr>\n");
out.write("</tbody>\n");
out.write("</table>\n");
out.write("</form></td>\n");
out.write("</tr>\n");
out.write("</table>\n");
out.flush();
out.close();
}
// create viewPage
if (ignoreList.get("view_" + tableName + ".jsp") != null) {
Logger.getLogger(GenerateForm.class.getName())
.log(Level.INFO, "Ignoring creation of view_" + tableName + ".jsp");
} else {
System.out.println("Creating view page " + tableName);
fDir = new File("../../web/WEB-INF/views/" + tableName);
if (!fDir.exists()) fDir.mkdir();
File fView =
new File("../../web/WEB-INF/views/" + tableName + "/view_" + tableName + ".jsp");
Writer outView = new FileWriter(fView);
outView.write(
"<%@ page contentType=\"text/html; charset=UTF-8\" language=\"java\" import=\"java.sql.*,recite18th.library.Db,application.config.Config,recite18th.library.Pagination\" %>");
outView.write("<%@ taglib uri=\"http://java.sun.com/jsp/jstl/core\" prefix=\"c\" %>\n");
outView.write(
"<%@ taglib uri=\"http://java.sun.com/jsp/jstl/functions\" prefix=\"fn\" %>\n");
outView.write("<% int pagenum = 0; %>\n");
// outView.write("<%@ include file=\"/WEB-INF/views/header.jsp\" %>");
outView.write(
"<a href=\"<%=Config.base_url%>index/" + className + "/input/-1\">Tambah Data</a>\n");
outView.write(
"|| <a href=\"<%=Config.base_url%>index/" + className + "/print\">Cetak</a>\n");
outView.write("<table width=\"100%\" id=\"rounded-corner\">\n");
outView.write("<thead>\n");
// iterate all columns : table header
outView.write(" <tr>\n");
outView.write(" <th scope=\"col\" class=\"rounded-company\">No.</th>\n");
resultSet.beforeFirst();
resultSet.next();
// get Primary Key Field Name : often use
String pkFieldName = "";
try {
Class params[] = null;
Method objMethod = cl.getMethod("getPkFieldName", params);
pkFieldName = "" + objMethod.invoke(model);
} catch (Exception ex) {
Logger.getLogger(Model.class.getName()).log(Level.SEVERE, null, ex);
}
// ALL Lower Case
pkFieldName = pkFieldName.toLowerCase();
// customize column view page
for (int i = 1; i <= nColoumn; i++) {
String columnName =
metaColumn.getColumnName(i).toLowerCase(); // Caution : ALL LowerCase
String dataType = metaColumn.getColumnClassName(i);
String thClass = "rounded-q1";
String thTitle = StringUtil.toProperFieldTitle(columnName);
if (TableCustomization.getTable(tableName)
!= null) // there is customization for this table
{
if (TableCustomization.getTable(tableName).get(columnName) != null) {
thTitle = TableCustomization.getTable(tableName).get(columnName) + "";
outView.write(
" <th scope=\"col\" class=\"" + thClass + "\">" + thTitle + "</th>\n");
}
} else { // standard view for this table : hide PK, because mostly is auto increment
if (!metaColumn.isAutoIncrement(i))
outView.write(
" <th scope=\"col\" class=\"" + thClass + "\">" + thTitle + "</th>\n");
}
}
outView.write(" <th scope=\"col\" class=\"rounded-q4\">Aksi</th>\n");
outView.write(" </tr>\n");
outView.write("</thead>\n");
outView.write("<tfoot>\n");
outView.write(" <tr>\n");
outView.write(
" <td colspan=\""
+ (nColoumn + 1)
+ "\" class=\"rounded-foot-left\"><%=Pagination.createLinks(pagenum)%></td>\n");
outView.write(" <td class=\"rounded-foot-right\"> </td>\n");
outView.write(" </tr>\n");
outView.write("</tfoot>\n");
outView.write("<tbody>\n");
outView.write(" <c:forEach items=\"${row}\" var=\"item\" varStatus=\"status\" >\n");
outView.write(" <tr>\n");
outView.write(" <td>${status.count}</td>\n");
// iterate all columns : table data
resultSet.beforeFirst();
resultSet.next();
for (int i = 1; i <= nColoumn; i++) {
String columnName = metaColumn.getColumnName(i);
// if(!columnName.equals(pkFieldName)) //TOFIX : currently, PK Field is not shown
if (TableCustomization.getTable(tableName) != null) {
if (TableCustomization.getTable(tableName).get(columnName) != null) {
outView.write(" <td>${item." + columnName + "}</td>\n");
}
} else {
if (!metaColumn.isAutoIncrement(i))
outView.write(" <td>${item." + columnName + "}</td>\n");
}
}
outView.write(" <td>\n");
outView.write(
" <a href=\"<%=Config.base_url%>index/"
+ className
+ "/input/${item."
+ pkFieldName
+ "}\">Ubah</a>\n");
outView.write(
" <a href=\"<%=Config.base_url%>index/"
+ className
+ "/delete/${item."
+ pkFieldName
+ "}\" onClick=\"return confirm('Apakah Anda yakin?');\">Hapus</a>\n");
outView.write(" </td>\n");
outView.write(" </tr>\n");
outView.write(" </c:forEach>\n");
outView.write("</tbody>\n");
outView.write("</table>\n");
// outView.write("<%@ include file=\"/WEB-INF/views/footer.jsp\" %>");
outView.flush();
outView.close();
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
