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(); } }