public void updateDb(Connection con) { try (Statement statement = con.createStatement(); ResultSet resultSet = statement.executeQuery("SELECT * FROM contact where firstname = \"Michael\"")) { resultSet.absolute(1); // 1 zmienic na dokładny wiersz w ktorym jest michael - metadata? resultSet.updateString("phoneNumber", "+009392032302"); resultSet.updateRow(); System.out.println(" After the update"); System.out.println("ID \tfName \tsurname \temail \t\tphoneNo"); resultSet.beforeFirst(); while (resultSet.next()) { System.out.println( resultSet.getInt("id") + "\t" + resultSet.getString("name") + "t\"" + resultSet.getString("surname") + "t\"" + resultSet.getString("email") + "t\"" + resultSet.getString("phoneNumber")); } } catch (SQLException e) { e.printStackTrace(); } }
/** * Affiche le resultSet en System.out (utile pour le DEBUG) * * @param rs ResultSet à afficher */ public void print_resultSet(ResultSet rs) { if (rs != null) { ResultSetMetaData md = null; int nombreColumns = 0; try { md = rs.getMetaData(); nombreColumns = md.getColumnCount(); String[] nomColumns = new String[nombreColumns]; for (int i = 1; i < (nombreColumns + 1); i++) { nomColumns[i - 1] = md.getColumnName(i); } int i = 1; while (rs.next()) { System.out.print("DEBUG : Row " + i + " : "); for (String s : nomColumns) { System.out.print(rs.getString(s) + " "); } i++; System.out.println(); } rs.beforeFirst(); } catch (SQLException ex) { Logger.getLogger(Request_MySQL.class.getName()).log(Level.SEVERE, null, ex); } } }
private void Baru() { btnSave.setText("Save"); txtNip.requestFocus(); txtNip.setText(""); try { Class.forName(KoneksiDatabase.driver); java.sql.Connection c = DriverManager.getConnection( KoneksiDatabase.database, KoneksiDatabase.user, KoneksiDatabase.pass); Statement s = c.createStatement(); String sql = "select * from absensi_lembur"; ResultSet rs = s.executeQuery(sql); final String[] headers = { "Kd Absen", "NIP", "Tgl Absen", "Masuk", "Pulang", "Hari", "Tipe Hari", "Terlambat", "Lembur", "Tipe Lembur", "Tot Lembur", "Tunj Makan", "Tunj Transport" }; rs.last(); int n = rs.getRow(); Object[][] data = new Object[n][13]; int p = 0; rs.beforeFirst(); while (rs.next()) { data[p][0] = rs.getString(1); data[p][1] = rs.getString(2); data[p][2] = rs.getString(3); data[p][3] = rs.getString(4); data[p][4] = rs.getString(5); data[p][5] = rs.getString(6); data[p][6] = rs.getString(7); data[p][7] = rs.getString(8); data[p][8] = rs.getString(9); data[p][9] = rs.getString(10); data[p][10] = rs.getString(11); data[p][11] = rs.getString(12); data[p][12] = rs.getString(13); p++; } tblLembur.setModel(new DefaultTableModel(data, headers)); tblLembur.setAlignmentX(CENTER_ALIGNMENT); } catch (Exception e) { JOptionPane.showMessageDialog( null, "Gagal Koneksi, Ada Kesalahan.", "Warning", JOptionPane.WARNING_MESSAGE); } }
public int taille_resultSet(ResultSet rs) throws SQLException { int taille = 0; while (rs.next()) { taille++; } rs.beforeFirst(); return taille; }
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 readData() { try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); con = DriverManager.getConnection( "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=" + filep); stmnt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); rst = con.getMetaData().getTables(null, null, "%", null); rst.next(); String SheetName = rst.getString(3); String query = "Select distinct * from [" + SheetName + "]"; rs = stmnt.executeQuery(query); if (rs != null) { rs.last(); RowCount = rs.getRow(); rs.beforeFirst(); // stmnt.close(); rst.close(); if (RowCount > 0) { registno = new String[RowCount]; name = new String[RowCount]; kdno = new int[RowCount]; kcno = new double[RowCount]; ccno = new double[RowCount]; seat = new double[RowCount]; i = 0; while (rs.next() && (rs.getString(1) != null)) { // rs.next(); i++; try { registno[i] = rs.getString(1); name[i] = rs.getString(2); kdno[i] = Integer.parseInt(rs.getString(3)); kcno[i] = Double.parseDouble(rs.getString(4)); ccno[i] = Double.parseDouble(rs.getString(5)); seat[i] = Double.parseDouble(rs.getString(6)); System.out.println(i); } catch (Exception e) { e.printStackTrace(); } } } } rs.close(); con.close(); } catch (Exception e) { // System.out.println("fail to get student connection"); } }
private int getRowCount(ResultSet rs) { int count = 0; try { rs.absolute(-1); count = rs.getRow(); rs.beforeFirst(); } catch (SQLException e) { printSQLException(e); } return count; }
private boolean executeQuery() { boolean returnVaue = true; int beginRow = 0; boolean moveIF = true; String lineOne = ""; ResultSet rst = null; ResultSetMetaData rstMetaData = null; int columnCount = 0; String temp = ""; rowNum = 0; if (querySQL == "") { writeLog("??????????"); quickField = ""; quickValue = ""; quickRow = -1; return false; } quickRow = -1; try { if (isProcedure) { rst = cstmt.executeQuery(); } else { if (stmt == null) init2(); rst = stmt.executeQuery(querySQL); } } catch (Exception e) { writeLog( (new StringBuilder("??��???????")) .append(e.toString()) .append(",sql=") .append(querySQL) .toString()); try { if (isProcedure && cstmt != null) cstmt.close(); else if (stmt != null) stmt.close(); } catch (SQLException sqlexception) { } returnVaue = false; return returnVaue; } try { rst.last(); totalRowCount = rst.getRow(); rst.beforeFirst(); } catch (Exception ex) { totalRowCount = 0; writeLog((new StringBuilder("??��???????")).append(ex.toString()).toString()); return returnVaue; } pageCount = BigInteger.valueOf(totalRowCount).divide(BigInteger.valueOf(perPage)).intValue(); if (BigInteger.valueOf(totalRowCount).mod(BigInteger.valueOf(perPage)).intValue() > 0) pageCount++; if (pageCount == 0) nextPage = 0; else if (nextPage > pageCount) nextPage = 0; beginRow = nextPage * perPage; beginRow++; curPage = nextPage; if (pageCount <= 1) { ifNext = false; nextPage = 0; } else if (nextPage > pageCount) { ifNext = false; nextPage = 0; } else { ifNext = true; } if (nextPage < 0) nextPage = 0; if (quickField != null && quickField.compareTo("") != 0 && pageCount != 0 && quickValue != null && quickValue.compareTo("") != 0) try { int colIndex = 0; String quickFieldName = ""; String quickFieldType = ""; String quickFieldTmp[] = (String[]) null; boolean haveFound = false; int findRow = 0; quickFieldTmp = StringUtil.split(quickField, ","); if (quickFieldTmp == null) throw new Exception((new StringBuilder("??????��??")).append(quickField).toString()); quickFieldName = quickFieldTmp[0]; quickFieldType = quickFieldTmp[1]; colIndex = rst.findColumn(quickFieldName); rst.beforeFirst(); while (!haveFound && rst.next()) { String tmpValue = ""; if (rst.getObject(colIndex) != null) { tmpValue = rst.getObject(colIndex).toString(); if (quickFieldType.compareToIgnoreCase("date") == 0) { int indexPos = -1; tmpValue = StringUtil.mk_date(tmpValue, 2); indexPos = tmpValue.indexOf(quickValue); if (indexPos == -1) { haveFound = false; } else { haveFound = true; findRow = rst.getRow(); } } else if (quickFieldType.compareToIgnoreCase("long") == 0) { int indexPos = -1; indexPos = tmpValue.compareTo(quickValue); if (indexPos == 0) { haveFound = true; findRow = rst.getRow(); } else { haveFound = false; } } else if (quickFieldType.compareToIgnoreCase("char") == 0) { int indexPos = -1; indexPos = tmpValue.indexOf(quickValue); if (indexPos == -1) { haveFound = false; } else { haveFound = true; findRow = rst.getRow(); } } } } if (findRow > 0) { int pageTmp = 0; int modTmp = 0; pageTmp = BigInteger.valueOf(findRow).divide(BigInteger.valueOf(perPage)).intValue(); modTmp = BigInteger.valueOf(findRow).mod(BigInteger.valueOf(perPage)).intValue(); if (modTmp == 0) { nextPage = pageTmp - 1; quickRow = perPage; } else { nextPage = pageTmp; quickRow = modTmp; } beginRow = nextPage * perPage; beginRow++; curPage = nextPage; if (nextPage >= pageCount - 1) { ifNext = false; nextPage = 0; } else { ifNext = true; nextPage = nextPage + 1; } } } catch (Exception exx) { writeLog(exx.toString()); quickRow = -1; } quickField = ""; quickValue = ""; try { rstMetaData = rst.getMetaData(); } catch (Exception e) { writeLog((new StringBuilder("???????")).append(e.toString()).toString()); returnVaue = false; } try { columnCount = rstMetaData.getColumnCount(); } catch (Exception e) { writeLog((new StringBuilder("??��??")).append(e.toString()).toString()); returnVaue = false; } Object object = null; for (int i = 1; i <= columnCount; i++) try { fieldVector.add(rstMetaData.getColumnName(i)); } catch (Exception e) { writeLog( (new StringBuilder("Exception while getColumnName?G")).append(e.toString()).toString()); } try { if (pageCount > 0) { try { rst.absolute(beginRow); moveIF = true; } catch (Exception exx) { moveIF = false; } if (moveIF) { for (int i = 1; i <= columnCount; i++) { try { object = rst.getObject(i); if (object == null) temp = ""; else temp = object.toString(); } catch (Exception e) { temp = ""; } if (temp == null) temp = ""; lineOne = (new StringBuilder(String.valueOf(lineOne))).append(temp).toString(); valueVector.add(temp); } if (lineOne.equals("")) { valueVector = new ArrayList(); rowNum = 0; } else { rowNum = 1; } for (int i = 1; i < perPage; i++) if (rst.next()) { rowNum++; for (int k = 1; k <= columnCount; k++) { try { if (rst.getObject(k) == null) temp = ""; else temp = rst.getObject(k).toString(); } catch (Exception e) { temp = ""; } if (temp == null) temp = ""; valueVector.add(temp); } } } } } catch (Exception e) { writeLog((new StringBuilder("??????")).append(e.toString()).toString()); returnVaue = false; } try { rst.close(); } catch (Exception exception) { } try { if (stmt != null) stmt.close(); } catch (Exception exception1) { } if (!returnVaue) resetBean(); colCount = columnCount; close(); return returnVaue; }
public void copyDbData(String dbin, String dbout) throws SQLException { // Do some connections and collect statements Connection inConn = makeConnection(dbin); Statement inSt = inConn.createStatement(); DatabaseMetaData dbmeta = inConn.getMetaData(); Connection outConn = makeConnection(dbout); Statement outSt = outConn.createStatement(); ResultSet tables = getTables(inConn); forEach( tables, rs -> { try { // Okay, we'v got our table name String tableName = rs.getString("TABLE_NAME"); // PreparedStatement st = DriverManager.getConnection(addr, user, // passwd).prepareStatement(format("CREATE TABLE %s.%s (LIKE %s.%s)", dbout, tableName, // dbin, tableName)); // st.execute(); // st.close(); // Lets fetch all data from it ResultSet data = inSt.executeQuery(format("SELECT * FROM \"%s\"", tableName)); // Lets get table schema from metadata and generate creation operator ResultSetMetaData datamd = data.getMetaData(); int columnCount = datamd.getColumnCount(); StringBuilder sb = new StringBuilder(); sb.append("CREATE TABLE ").append(tableName).append(" ("); for (int i = 1; i <= columnCount; i++) { if (i > 1) sb.append(", "); sb.append(datamd.getColumnName(i)).append(" ").append(datamd.getColumnTypeName(i)); if (datamd.isNullable(i) == ResultSetMetaData.columnNoNulls) sb.append(" NOT NULL"); // Tons of parameters could be parsed here except of references and keys // if (precision != 0) { // sb.append("(").append(precision).append(")"); // } switch (datamd.getColumnTypeName(i)) { case "character": case "time": case "": sb.append("(").append(datamd.getPrecision(i)).append(")"); } } sb.append(");"); // Looks good, lets create this in our outer database LOGGER.info("Execute: '{}'", sb.toString()); outSt.execute(sb.toString()); // Setting up primary keys Map<String, StringBuilder> primaryKeys = new HashMap<>(); ResultSet primaryKeysRs = dbmeta.getPrimaryKeys(null, null, tableName); forEach( primaryKeysRs, pkRs -> { try { String columName = pkRs.getString("COLUMN_NAME"); String pkName = pkRs.getString("PK_NAME"); if (!primaryKeys.containsKey(pkName)) { primaryKeys.put(pkName, new StringBuilder(columName)); } else { primaryKeys.get(pkName).append(", ").append(columName); } } catch (SQLException e) { // Should never be reached e.printStackTrace(); } }); primaryKeys .entrySet() .forEach( entry -> { String pkName = entry.getKey(); String pk = entry.getValue().toString(); String query = format("ALTER TABLE %s ADD PRIMARY KEY (%s)", tableName, pk); try { LOGGER.info("Execute: '{}'", query); outSt.executeUpdate(query); } catch (SQLException e) { LOGGER.error("Setting '{}' as primary key in '{}' failed", pk, tableName); e.printStackTrace(); } }); // Generate insertion statement StringBuilder prepareQuery = new StringBuilder("INSERT INTO "); prepareQuery.append(tableName).append(" VALUES ("); for (int i = 0; i < columnCount; i++) { if (i > 0) prepareQuery.append(", "); prepareQuery.append("?"); } prepareQuery.append(");"); LOGGER.info("Prepare statement with: {}", prepareQuery); PreparedStatement pst = outConn.prepareStatement(prepareQuery.toString()); // And put all our's data in it forEach( data, dataRs -> { try { for (int i = 1; i <= columnCount; i++) { pst.setObject(i, dataRs.getObject(i)); } LOGGER.info("Execute: '{}'", pst.toString()); pst.execute(); } catch (SQLException e) { e.printStackTrace(); } /* try { StringBuilder insert = new StringBuilder(); insert.append("INSERT INTO \"").append(tableName).append("\" VALUES ("); for (int i = 1; i <= columnCount; i++) { if (i > 1) insert.append(", "); insert.append(dataRs.getString(i)); } insert.append(");"); // Finally put insert it in table // Timestamps would be inserted incorrect LOGGER.info("Execute: '{}'", insert.toString()); outSt.executeUpdate(insert.toString()); } catch (SQLException e) { LOGGER.error("Failed to insert into '{}'", tableName); } */ }); } catch (SQLException e) { LOGGER.error("Failed to create table"); e.printStackTrace(); } }); // Lets deal up with foreign keys tables.beforeFirst(); forEach( tables, rs -> { try { String tableName = rs.getString("TABLE_NAME"); ResultSet foreignKeysRs = dbmeta.getImportedKeys(null, null, tableName); Map<String, String> pkTables = new HashMap<>(); Map<String, StringBuilder> pkKeys = new HashMap<>(); Map<String, StringBuilder> fkKeys = new HashMap<>(); forEach( foreignKeysRs, keyRs -> { try { String pkTable = keyRs.getString("PKTABLE_NAME"); String pkColumn = keyRs.getString("PKCOLUMN_NAME"); String fkColumn = keyRs.getString("FKCOLUMN_NAME"); String fkName = keyRs.getString("FK_NAME"); if (!pkTables.containsKey(fkName)) { pkTables.put(fkName, pkTable); pkKeys.put(fkName, new StringBuilder(pkColumn)); fkKeys.put(fkName, new StringBuilder(fkColumn)); } else { pkKeys.get(fkName).append(", ").append(pkColumn); fkKeys.get(fkName).append(", ").append(fkColumn); } } catch (SQLException e) { e.printStackTrace(); // Should newer be reached } }); pkTables .entrySet() .forEach( entry -> { String keyName = entry.getKey(); String extTableName = entry.getValue(); String alterFK = format( "ALTER TABLE %s ADD FOREIGN KEY (%s) REFERENCES %s(%s);", tableName, // keyName, fkKeys.get(keyName), extTableName, pkKeys.get(keyName)); LOGGER.info("Execute: '{}'", alterFK); try { outSt.executeUpdate(alterFK); } catch (SQLException e) { e.printStackTrace(); } }); } catch (SQLException e) { e.printStackTrace(); } }); }
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 void setContent(String cat) { cat = cat.trim(); selectAllCB.setVisible(false); selectAllCB.setSelected(false); deleteBut.setVisible(false); restoreBut.setVisible(false); refreshBut.setVisible(true); Object columns[] = null; int count = 0; switch (cat) { case "Inbox": columns = new Object[] {"", "From", "Date", "Subject", "Content"}; count = Database.getCount("Inbox"); workingSet = db.getData("SELECT * FROM messages WHERE tag='inbox' ORDER BY msg_id desc"); ; break; case "SentMail": columns = new Object[] {"", "To", "Date", "Subject", "Content"}; count = Database.getCount("Sentmail"); workingSet = db.getData("SELECT * FROM messages WHERE tag='sentmail' ORDER BY msg_id desc"); break; case "Draft": columns = new Object[] {"", "To", "Date", "Subject", "Content"}; count = Database.getCount("Draft"); workingSet = db.getData("SELECT * FROM messages WHERE tag='draft' ORDER BY msg_id desc"); break; case "Outbox": columns = new Object[] {"", "To", "Date", "Subject", "Content"}; count = Database.getCount("Outbox"); workingSet = db.getData("SELECT * FROM messages WHERE tag='outbox' ORDER BY msg_id desc"); break; case "Trash": // restoreBut.setVisible(true); columns = new Object[] {"", "To/From", "Date", "Subject", "Content"}; count = Database.getCount("Trash"); workingSet = db.getData( "SELECT * FROM messages,trash WHERE messages.tag='trash' and messages.msg_id=trash.msg_id ORDER BY deleted_at desc"); break; default: System.out.println("in default case"); } if (count > 0) { selectAllCB.setVisible(true); rows = new Object[count][]; msgID = new int[count]; try { workingSet.beforeFirst(); for (int i = 0; i < count && workingSet.next(); i++) { msgID[i] = workingSet.getInt(1); rows[i] = new Object[] { false, workingSet.getString(2), workingSet.getDate(3), workingSet.getString(4), workingSet.getString(5) }; } } catch (SQLException sqlExc) { JOptionPane.showMessageDialog(null, sqlExc, "EXCEPTION", JOptionPane.ERROR_MESSAGE); sqlExc.printStackTrace(); } tableModel = new MyDefaultTableModel(rows, columns); table = new JTable(tableModel); table.getSelectionModel().addListSelectionListener(this); table.addMouseListener(this); table.getTableHeader().setOpaque(true); table.getTableHeader().setReorderingAllowed(false); // table.getTableHeader().setBackground(Color.blue); table.getTableHeader().setForeground(Color.blue); // table.setRowSelectionAllowed(false); // table.setColumnSelectionAllowed(false); table.setFont(new Font(Font.SANS_SERIF, Font.PLAIN, 14)); table.setRowHeight(20); table.setFillsViewportHeight(true); TableColumn column = null; for (int i = 0; i < 5; i++) { column = table.getColumnModel().getColumn(i); if (i == 0) { column.setPreferredWidth(6); } else if (i == 3) { column.setPreferredWidth(250); } else if (i == 4) { column.setPreferredWidth(450); } else { column.setPreferredWidth(40); } } table.setAutoResizeMode(JTable.AUTO_RESIZE_SUBSEQUENT_COLUMNS); remove(contentPan); contentPan = new JScrollPane(table); contentPan.setBackground(Color.orange); contentPan.setOpaque(true); contentPan.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0)); add(contentPan, "Center"); Home.home.homeFrame.setVisible(true); } else { JPanel centPan = new JPanel(new GridBagLayout()); centPan.setBackground(new Color(52, 86, 70)); JLabel label = new JLabel("No Messages In This Category"); label.setFont(new Font(Font.SANS_SERIF, Font.BOLD, 22)); label.setForeground(Color.orange); centPan.add(label); remove(contentPan); contentPan = new JScrollPane(centPan); contentPan.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0)); add(contentPan, "Center"); contentPan.repaint(); } }
public static void main(String args[]) { try { String url; if (args.length == 0) url = "jdbc:virtuoso://localhost:1111"; else url = args[0]; Class.forName("virtuoso.jdbc3.Driver"); System.out.println("--------------------- Test of scrollable cursor -------------------"); System.out.print("Establish connection at " + url); Connection connection = DriverManager.getConnection(url, "dba", "dba"); if (connection instanceof virtuoso.jdbc3.VirtuosoConnection) System.out.println(" PASSED"); else { System.out.println(" FAILED"); System.exit(-1); } System.out.print("Create a Statement class attached to this connection"); Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); if (stmt instanceof virtuoso.jdbc3.VirtuosoStatement) System.out.println(" PASSED"); else { System.out.println(" FAILED"); System.exit(-1); } try { stmt.executeUpdate("drop table EX..DEMO"); } catch (Exception e) { } System.out.print("Execute CREATE TABLE"); if (stmt.executeUpdate("create table EX..DEMO (ID integer,FILLER integer,primary key(ID))") == 0) System.out.println(" PASSED"); else { System.out.println(" FAILED"); System.exit(-1); } System.out.print("Create a PStatement class attached to this connection"); PreparedStatement pstmt = connection.prepareStatement("INSERT INTO EX..DEMO(ID,FILLER) VALUES (?,?)"); System.out.println(" PASSED"); System.out.print("Execute INSERT INTO"); for (int i = 0; i < 100; i++) { pstmt.setInt(1, i); pstmt.setInt(2, i); if (pstmt.executeUpdate() != 1) { System.out.println(" FAILED"); System.exit(-1); } } System.out.println(" PASSED"); pstmt.close(); System.out.print("Execute SELECT"); stmt.setMaxRows(100); stmt.setFetchSize(10); stmt.execute("SELECT * from EX..DEMO"); System.out.println(" PASSED"); System.out.print("Get the result set"); ResultSet rs = stmt.getResultSet(); if (rs instanceof virtuoso.jdbc3.VirtuosoResultSet) { System.out.println(" PASSED"); } else { System.out.println(" FAILED"); System.exit(-1); } System.out.print("Execute the resultset.beforeFirst()"); rs.beforeFirst(); System.out.println(" PASSED"); System.out.print("Execute the resultset.next()"); for (int i = 0; i < 100; i++) { rs.next(); if (rs.getInt(2) != i) { System.out.println(" FAILED"); System.exit(-1); } } System.out.println(" PASSED"); System.out.print("Execute the resultset.afterLast()"); rs.afterLast(); System.out.println(" PASSED"); System.out.print("Execute the resultset.previous()"); for (int i = 99; i >= 0; i--) { rs.previous(); if (rs.getInt(2) != i) { System.out.println(" FAILED"); System.exit(-1); } } System.out.println(" PASSED"); System.out.print("Execute the resultset.first()"); rs.first(); System.out.println(" PASSED"); System.out.print("Execute the resultset.next()"); for (int i = 0; i < 100; i++) { if (rs.getInt(2) != i) { System.out.println(" FAILED"); System.exit(-1); } rs.next(); } System.out.println(" PASSED"); System.out.print("Execute the resultset.last()"); rs.last(); System.out.println(" PASSED"); System.out.print("Execute the resultset.previous()"); for (int i = 99; i >= 0; i--) { if (rs.getInt(2) != i) { System.out.println(" FAILED"); System.exit(-1); } rs.previous(); } System.out.println(" PASSED"); System.out.print("Execute the resultset.absolute(>0)"); for (int i = 0; i != 100; i++) { rs.absolute(i + 1); if (rs.getInt(2) != i) { System.out.println(" FAILED"); System.exit(-1); } } System.out.println(" PASSED"); System.out.print("Execute the resultset.absolute(<0)"); for (int i = -1, j = 99; i != -101; i--, j--) { rs.absolute(i); if (rs.getInt(2) != j) { System.out.println(" FAILED"); System.exit(-1); } } System.out.println(" PASSED"); System.out.print("Execute the resultset.absolute(50)"); rs.absolute(50); System.out.println(" PASSED"); System.out.print("Execute the resultset.relative(>0)"); for (int i = 50; i != 90; i++) { if (rs.getInt(2) != i - 1) { System.out.println(" FAILED"); System.exit(-1); } rs.relative(1); } System.out.println(" PASSED"); System.out.print("Execute the resultset.absolute(50)"); rs.absolute(50); System.out.println(" PASSED"); System.out.print("Execute the resultset.relative(<0)"); for (int i = 50; i != 10; i--) { if (rs.getInt(2) != i - 1) { System.out.println(" FAILED"); System.exit(-1); } rs.relative(-1); } System.out.println(" PASSED"); System.out.print("Execute the resultset.first()"); rs.first(); System.out.println(" PASSED"); System.out.print("Update rows in the table"); for (int i = 0; i != 2; i++) { rs.updateInt("FILLER", i * 2); rs.updateRow(); rs.refreshRow(); if (rs.getInt(2) != i * 2) { System.out.println(" FAILED"); System.exit(-1); } rs.next(); } System.out.println(" PASSED"); System.out.print("Execute DELETE"); pstmt = connection.prepareStatement("DELETE FROM EX..DEMO WHERE ID=?"); for (int i = 0; i < 100; i++) { pstmt.setInt(1, i); if (pstmt.executeUpdate() != 1) { System.out.println(" FAILED"); System.exit(-1); } } System.out.println(" PASSED"); pstmt.close(); stmt.close(); stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); System.out.print("Execute DROP TABLE"); if (stmt.executeUpdate("DROP TABLE EX..DEMO") == 0) System.out.println(" PASSED"); else { System.out.println(" FAILED"); System.exit(-1); } System.out.print("Close statement at " + url); stmt.close(); System.out.println(" PASSED"); System.out.print("Close connection at " + url); connection.close(); System.out.println(" PASSED"); System.out.println("-------------------------------------------------------------------"); System.exit(0); } catch (Exception e) { System.out.println(" FAILED"); e.printStackTrace(); System.exit(-1); } }
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(); } }
public RAJ5(String sAno) { int files; int cols; String query11 = "drop table if exists tempAJ5 "; String query22 = "drop table if exists tempAJ5a "; String query1 = "create table tempAJ5 "; query1 += "select ano,descripcion as oficina,materia,sum(num) as num "; query1 += "from asist_juridica,oficinas "; query1 += "where (ano=\"" + sAno + "\") and (asist_juridica.oficina=oficinas.codigo) "; query1 += "group by materia,oficina "; query1 += "order by materia"; String query2 = "CREATE TABLE `tempAJ5a` ( "; query2 += "`ano` char(4) default '0', "; query2 += "`materia` char(15) default '0', "; query2 += "`of_benassal` int(3) unsigned default '0', "; query2 += "`of_montanejos` int(3) unsigned default '0', "; query2 += "`of_morella` int(3) unsigned default '0', "; query2 += "`of_onda` int(3) unsigned default '0', "; query2 += "`of_segorbe` int(3) unsigned default '0', "; query2 += "`of_traiguera` int(3) unsigned default '0'"; query2 += ") "; String query3 = "Select * from tipo_asist_jur"; String query4 = "select * from tempAJ5"; try { Connection con = this.getConnection(); Statement sentencia11 = con.createStatement(); sentencia11.execute(query11); sentencia11.close(); Statement sentencia22 = con.createStatement(); sentencia22.execute(query22); sentencia22.close(); Statement sentencia1 = con.createStatement(); sentencia1.execute(query1); sentencia1.close(); Statement sentencia2 = con.createStatement(); sentencia2.execute(query2); sentencia2.close(); Statement sentencia3 = con.createStatement(); ResultSet rs3 = sentencia3.executeQuery(query3); Statement sentencia4 = con.createStatement(); ResultSet rs4 = sentencia4.executeQuery(query4); String query5 = ""; String materia = ""; int quantitat, quantitat1, quantitat2, quantitat3, quantitat4, quantitat5, quantitat6; while (rs3.next()) { materia = rs3.getString("abreviatura"); quantitat = 0; quantitat1 = 0; quantitat2 = 0; quantitat3 = 0; quantitat4 = 0; quantitat5 = 0; quantitat6 = 0; while (rs4.next()) { if (materia.equals(rs4.getString("materia"))) { quantitat = rs4.getInt("num"); if (rs4.getString("oficina").equals("OF. BENASAL")) { quantitat1 = quantitat; } else if (rs4.getString("oficina").equals("OF. MONTANEJOS")) { quantitat2 = quantitat; } else if (rs4.getString("oficina").equals("OF. MORELLA")) { quantitat3 = quantitat; } else if (rs4.getString("oficina").equals("OF. ONDA")) { quantitat4 = quantitat; } else if (rs4.getString("oficina").equals("OF. SEGORBE")) { quantitat5 = quantitat; } else if (rs4.getString("oficina").equals("OF. TRAIGUERA")) { quantitat6 = quantitat; } } } query5 = "insert into tempAJ5a values (\"" + sAno + "\",\"" + materia + "\"," + quantitat1 + "," + quantitat2 + "," + quantitat3 + "," + quantitat4 + "," + quantitat5 + "," + quantitat6 + ")"; Statement sentencia5 = con.createStatement(); sentencia5.execute(query5); sentencia5.close(); rs4.beforeFirst(); } sentencia3.close(); sentencia4.close(); // select final per a formar la matriu AbstractTableModel String query6 = "select *,sum(of_traiguera+of_benassal+of_morella+of_onda+of_segorbe+of_montanejos)as total "; query6 += "from tempAJ5a "; query6 += "group by materia"; Statement sentencia6 = con.createStatement(); ResultSet rs6 = sentencia6.executeQuery(query6); ResultSetMetaData rsmd6 = rs6.getMetaData(); files = 0; while (rs6.next()) { files++; } cols = rsmd6.getColumnCount(); f = new Object[files][cols]; c = new Object[cols]; for (int i = 0; i < cols; i++) { c[i] = rsmd6.getColumnName(i + 1); } int x = 0; rs6.beforeFirst(); while (rs6.next()) { for (int y = 0; y < cols; y++) { if ((y >= 2) && (y <= 8)) { f[x][y] = new Integer(rs6.getString(y + 1)); } else { f[x][y] = rs6.getString(y + 1); } } x++; } sentencia6.close(); con.close(); } catch (SQLException e) { System.err.println("Error en la base de dades: " + e); } }