public void setQuery(String q) {
cache = new Vector();
try {
ResultSet rs = statement.executeQuery(q);
ResultSetMetaData meta = rs.getMetaData();
colCount = meta.getColumnCount();
headers = new String[colCount];
for (int h = 1; h <= colCount; h++) {
headers[h - 1] = meta.getColumnName(h);
}
while (rs.next()) {
String[] record = new String[colCount];
for (int i = 0; i < colCount; i++) {
record[i] = rs.getString(i + 1);
}
cache.addElement(record);
} // while sonu
fireTableChanged(null);
} // try sonu
catch (Exception e) {
cache = new Vector();
e.printStackTrace();
}
} // setQuery sonu
public void actionPerformed(ActionEvent evt) {
// 删除原来的JTable(JTable使用scrollPane来包装)
if (scrollPane != null) {
jf.remove(scrollPane);
}
try (
// 根据用户输入的SQL执行查询
ResultSet rs = stmt.executeQuery(sqlField.getText())) {
// 取出ResultSet的MetaData
ResultSetMetaData rsmd = rs.getMetaData();
Vector<String> columnNames = new Vector<>();
Vector<Vector<String>> data = new Vector<>();
// 把ResultSet的所有列名添加到Vector里
for (int i = 0; i < rsmd.getColumnCount(); i++) {
columnNames.add(rsmd.getColumnName(i + 1));
}
// 把ResultSet的所有记录添加到Vector里
while (rs.next()) {
Vector<String> v = new Vector<>();
for (int i = 0; i < rsmd.getColumnCount(); i++) {
v.add(rs.getString(i + 1));
}
data.add(v);
}
// 创建新的JTable
JTable table = new JTable(data, columnNames);
scrollPane = new JScrollPane(table);
// 添加新的Table
jf.add(scrollPane);
// 更新主窗口
jf.validate();
} catch (Exception e) {
e.printStackTrace();
}
}
public boolean dbOpenList(Connection connection, String sql) {
dbClearList();
this.oldSql = sql;
this.oldConnection = connection;
// apre il resultset da abbinare
ResultSet resu = null;
ResultSetMetaData meta;
try {
stat = connection.createStatement();
resu = stat.executeQuery(sql);
meta = resu.getMetaData();
if (meta.getColumnCount() > 1) {
this.contieneChiavi = true;
}
// righe
while (resu.next()) {
for (int i = 1; i <= meta.getColumnCount(); ++i) {
if (i == 1) {
dbItems.add((Object) resu.getString(i));
lm.addElement((Object) resu.getString(i));
} else if (i == 2) {
dbItemsK.add((Object) resu.getString(i));
// debug
// System.out.println("list:" + String.valueOf(i) + ":" + resu.getString(i));
} else if (i == 3) {
dbItemsK2.add((Object) resu.getString(i));
}
}
}
this.setModel(lm);
// vado al primo
if (dbTextAbbinato != null) {
// debug
// javax.swing.JOptionPane.showMessageDialog(null,this.getKey(0).toString());
dbTextAbbinato.setText(this.getKey(0).toString());
}
return (true);
} catch (Exception e) {
javax.swing.JOptionPane.showMessageDialog(null, e.toString());
e.printStackTrace();
return (false);
} finally {
try {
stat.close();
} catch (Exception e) {
}
try {
resu.close();
} catch (Exception e) {
}
meta = null;
}
}
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);
}
}
