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