Шустрый
Профиль
Группа: Участник
Сообщений: 55
Регистрация: 23.12.2004
Репутация: нет
Всего: 1
|
вот выкладываю исправленный-подправленный вариант приложения DB класс| Код |
import javax.swing.*;
import java.*;
import java.util.ArrayList;
import java.util.Vector;
import java.sql.*;
import java.awt.*;
import java.awt.event.*;
public class DB {
private static java.sql.Connection con = null;
private static final String url = "jdbc:microsoft:sqlserver://";
private static final String serverName = "bababab";
private static final String portNumber = "1433";
private static final String databaseName = "всрпвапр";
private static final String userName = "варпвап";
private static final String password = "апропарап";
// Informs the driver to use server a side-cursor, which permits more than one active statement
// on a connection.
private static MyTableModel mtm;
private static final String selectMethod = "cursor";
private static String getConnectionUrl() {
return url + serverName + ":" + portNumber + ";databaseName=" +
databaseName + ";selectMethod=" + selectMethod + ";";
}
public static java.sql.Connection getConnection() {
try{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
con = java.sql.DriverManager.getConnection(getConnectionUrl(),userName,password);
if(con!=null) System.out.println("Connection Successful!");
}
catch(Exception e)
{
e.printStackTrace();
System.out.println("Error Trace in getConnection() : " + e.getMessage());
}
return con;
}
public static Object[][] executeQuery(String query) {
int i = 0;
int j = 0;
int numColumns = 0;
DatabaseMetaData dm = null;
ResultSet rs = null;
ArrayList tableRow = new ArrayList();
ArrayList multiRow = new ArrayList();
Object[][] resultArray2D;
try {
Statement stmt = con.createStatement();
ResultSet result = stmt.executeQuery(query);
ResultSetMetaData rsmd = result.getMetaData();
for (i = 1; i <= rsmd.getColumnCount(); i++) {
tableRow.add(rsmd.getColumnName(i));
numColumns = tableRow.size();
}
multiRow.add(tableRow);
tableRow = new ArrayList();
while (result.next()) {
for (i = 1; i <= rsmd.getColumnCount(); i++) {
tableRow.add(result.getString(i));
}
multiRow.add(tableRow);
tableRow = new ArrayList();
}
stmt.close();
}
catch (SQLException ex) {
System.err.print("SQLException: ");
System.err.println(ex.getMessage());
}
resultArray2D = new Object[multiRow.size() - 1][numColumns];
for (i = 1; i < multiRow.size(); i++) {
tableRow = (ArrayList) multiRow.get(i);
for (j = 0; j < numColumns; j++) {
resultArray2D[i - 1][j] = (Object) tableRow.get(j);
}
tableRow = null;
}
return resultArray2D;
}
public static void closeConnection() {
try {
if (con != null) {
con.close();
}
con = null;
}
catch (Exception e) {
e.printStackTrace();
}
}
// public static Object[][] ExecStoredProc(String myOffice, String myDept, String activeStatus) {
public static MyTableModel ExecStoredProc(String myOffice, String myDept, String activeStatus) {
int i = 0;
int j = 0;
int numColumns = 0;
ArrayList tableRow = new ArrayList();
ArrayList multiRow = new ArrayList();
Object[][] resultArray2D;
CallableStatement cs = null;
try {
cs = con.prepareCall("{CALL SpSelEmployees3(?,?,?,?,?,?,?,?)}");
cs.setString(1, "OfficeName"); // @strOrder char(100) = ' OfficeName, Department, Surname, W_Group, Discipline',
cs.setInt(2, Integer.parseInt(myOffice)); // @office int = -1,
cs.setInt(3, Integer.parseInt(myDept)); // @dept int = -1,
cs.setInt(4, -1); // @position int = -1,
cs.setString(5, ""); // @workgroup char(50) ='',
cs.setString(6, ""); // @discipline char(50) ='',
cs.setInt(7, Integer.parseInt(activeStatus)); // @terminated int = -1
cs.setString(8, ""); // @my_select varchar(1000) = ''
} catch (SQLException e) {
e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
}
ResultSet result = null;
try {
result = cs.executeQuery();
} catch (SQLException e) {
e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
}
mtm = new MyTableModel(con, result);
return mtm;
}
//================================
public static Object[][] ExecQuery_ServicesTree() {
int i = 0;
int j = 0;
int numColumns = 0;
DatabaseMetaData dm = null;
ResultSet rs = null;
ArrayList tableRow = new ArrayList();
ArrayList multiRow = new ArrayList();
Object[][] resultArray2D;
try {
Statement stmt = con.createStatement();
ResultSet result = stmt.executeQuery("select distinct Id_Code, Service_Name from Services order by Service_Name");
ResultSetMetaData rsmd = result.getMetaData();
for (i = 1; i <= rsmd.getColumnCount(); i++) {
tableRow.add(rsmd.getColumnName(i));
numColumns = tableRow.size();
}
multiRow.add(tableRow);
tableRow = new ArrayList();
while (result.next()) {
for (i = 1; i <= rsmd.getColumnCount(); i++) {
tableRow.add(result.getString(i));
}
multiRow.add(tableRow);
tableRow = new ArrayList();
}
stmt.close();
}
catch (SQLException ex) {
System.err.print("SQLException: ");
System.err.println(ex.getMessage());
}
resultArray2D = new Object[multiRow.size() - 1][numColumns];
for (i = 1; i < multiRow.size(); i++) {
tableRow = (ArrayList) multiRow.get(i);
for (j = 0; j < numColumns; j++) {
resultArray2D[i - 1][j] = (Object) tableRow.get(j);
}
tableRow = null;
}
return resultArray2D;
}
public static Object[][] ExecQuery_LangTree() {
int i = 0;
int j = 0;
int numColumns = 0;
DatabaseMetaData dm = null;
ResultSet rs = null;
ArrayList tableRow = new ArrayList();
ArrayList multiRow = new ArrayList();
Object[][] langArray2D;
try {
Statement stmt = con.createStatement();
ResultSet result = stmt.executeQuery("select distinct Id_Code, Language_Name, Id_Employee, EmployeeName, LangLevel from Vw_Lang order by Language_Name");
ResultSetMetaData rsmd = result.getMetaData();
for (i = 1; i <= rsmd.getColumnCount(); i++) {
tableRow.add(rsmd.getColumnName(i));
numColumns = tableRow.size();
}
multiRow.add(tableRow);
tableRow = new ArrayList();
while (result.next()) {
for (i = 1; i <= rsmd.getColumnCount(); i++) {
tableRow.add(result.getString(i));
}
multiRow.add(tableRow);
tableRow = new ArrayList();
}
stmt.close();
}
catch (SQLException ex) {
System.err.print("SQLException: ");
System.err.println(ex.getMessage());
}
langArray2D = new Object[multiRow.size() - 1][numColumns];
for (i = 1; i < multiRow.size(); i++) {
tableRow = (ArrayList) multiRow.get(i);
for (j = 0; j < numColumns; j++) {
langArray2D[i - 1][j] = (Object) tableRow.get(j);
}
tableRow = null;
}
return langArray2D;
}
public static Object[][] ExecQuery_OfficesTree() {
int i = 0;
int j = 0;
int numColumns = 0;
DatabaseMetaData dm = null;
ResultSet rs = null;
ArrayList tableRow = new ArrayList();
ArrayList multiRow = new ArrayList();
Object[][] resultArray2D;
try {
Statement stmt = con.createStatement();
ResultSet result = stmt.executeQuery("select distinct A.Id_Office, B.OfficeName, A.DepartmentCode, C.Department_Name from Employees A left join Offices B on A.Id_Office=B.Id_Code left join Departments C on A.DepartmentCode= C.Id_Code group by A.Id_Office, B.OfficeName, A.DepartmentCode, C.Department_Name order by B.OfficeName, A.Id_Office, C.Department_Name, A.DepartmentCode");
ResultSetMetaData rsmd = result.getMetaData();
for (i = 1; i <= rsmd.getColumnCount(); i++) {
tableRow.add(rsmd.getColumnName(i));
numColumns = tableRow.size();
}
multiRow.add(tableRow);
tableRow = new ArrayList();
while (result.next()) {
for (i = 1; i <= rsmd.getColumnCount(); i++) {
tableRow.add(result.getString(i));
}
multiRow.add(tableRow);
tableRow = new ArrayList();
}
stmt.close();
}
catch (SQLException ex) {
System.err.print("SQLException: ");
System.err.println(ex.getMessage());
}
resultArray2D = new Object[multiRow.size() - 1][numColumns];
for (i = 1; i < multiRow.size(); i++) {
tableRow = (ArrayList) multiRow.get(i);
for (j = 0; j < numColumns; j++) {
resultArray2D[i - 1][j] = (Object) tableRow.get(j);
}
tableRow = null;
}
return resultArray2D;
}
}
|
в MyTableModel класс попробовал приспособить Куртовский пример (пока не работает) работает только Remove в классе formEmployees MyTableModel| Код |
public class MyTableModel extends AbstractTableModel {
int columnCount;
ResultSetMetaData rsMetaData;
Object[] row;
String temp = "";
String[] columnName;
Vector rows = new Vector();
Connection conn;
ResultSet rs;
//вектор видимых столбцов:
Vector visible_columns;
public MyTableModel(Connection conn, ResultSet rs) {
try {
this.conn = conn;
this.rs = rs;
try {
rsMetaData = rs.getMetaData();
} catch (SQLException e) {
e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
}
try {
columnCount = rsMetaData.getColumnCount();
} catch (SQLException e) {
e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
}
columnName = new String[columnCount];
for (int i = 0; i < columnCount; i++) {
try {
columnName[i ] = rsMetaData.getColumnName(i+1).trim();
} catch (SQLException e) {
e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
}
}
try {
while (rs.next()) {
//System.out.println("From cicle!");
row = new Object[columnCount];
for (int y = 1; y <= columnCount; y++) {
row[y - 1] = rs.getString(y);
}
rows.addElement(row);
}
} catch (SQLException e) {
e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
}
} catch (NullPointerException npe) {
}
//MyTableModel {
//здесь мы создаем наш вектор и заполняем его начальными значениями.
visible_columns = new Vector();
for (int i=0; i<columnName.length; i++)
{visible_columns.add(new Integer(i));}
}
public boolean isCellEditable(int row, int column) {
return false;
}
public int getRowCount() {
return rows.size();
}
public int getColumnCount() {
return columnCount;
}
public String getColumnName(int column) {
return (columnName[column]);
}
public Object[] getRow(int index) {
return ((Object[]) rows.elementAt(index));
}
public Object getValueAt(int row, int column) {
return ((Object[]) rows.elementAt(row))[column];
}
public void insertRow(int row, Object rowData) {
rows.insertElementAt(rowData, row);
justifyRows(row, row+1);
fireTableRowsInserted(row, row);
}
private void justifyRows(int from, int to) {
rows.setSize(getRowCount());
}
public void EditRow(int row, Object rowData){
rows.setElementAt(rowData,row);
fireTableRowsUpdated(row,row);
fireTableDataChanged();
}
public void removeRow(int row) {
rows.removeElementAt(row);
rows.setSize(getRowCount());
fireTableRowsDeleted(row, row);
}
public void turnOffColumn(int col) {
Integer intCol = new Integer(col);
int i=visible_columns.indexOf(intCol);
if(i!=-1){
visible_columns.remove(i);
};
fireTableStructureChanged();
}
}
|
formEmployees в следующем ответе ( но не гарантирую, что и сейчас не глюкнет - обрезала нижний кусок - не могут ничего больше вставить - надо чтоб кто-то написал - тогда я вставлю продолжение - или новый топик заведу для продлджения кода класса) для простоты попробую выложить только код того метода RefreshTable (который в принципе все и делает) | Код |
public void RefreshTable() {
MyTableModel mtm = DB.ExecStoredProc(myOffice, myDept, activeStatus); // Call stored procedure SpSelectEmployees3 from SQL SERVER
TableColumnModel tablelcolumnModel = tableEmployees.getColumnModel();
// mtm.turnOffColumn(1);
// mtm.turnOffColumn(2);
tableEmployees = new JTable(mtm); // populating grid with data from result set = stored procedure SpSelectEmployees3
for (int i=tableEmployees.getColumnCount()-1;i>=1;i--)
{
if (tableEmployees.getColumnName(i).equalsIgnoreCase("OutStatus") ||
tableEmployees.getColumnName(i).equalsIgnoreCase("Active_Status") ||
tableEmployees.getColumnName(i).equalsIgnoreCase("Id_Office") ||
tableEmployees.getColumnName(i).equalsIgnoreCase("Id_Dept") ||
tableEmployees.getColumnName(i).equalsIgnoreCase("Aboriginal")){
tableEmployees.removeColumn(tablelcolumnModel.getColumn(i));};
};
tableEmployees.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
tableEmployees.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
if (ALLOW_ROW_SELECTION) { // true by default
ListSelectionModel rowSM = tableEmployees.getSelectionModel();
rowSM.addListSelectionListener(new ListSelectionListener() {
public void valueChanged(ListSelectionEvent e) {
//Ignore extra messages.
if (e.getValueIsAdjusting()) return;
ListSelectionModel lsm = (ListSelectionModel)e.getSource();
if (lsm.isSelectionEmpty()) {
System.out.println("No rows are selected.");
} else {
int selectedRow = lsm.getMinSelectionIndex();
employee_lastname.setText(tableEmployees.getValueAt(selectedRow, 2).toString());
employee_firstname.setText(tableEmployees.getValueAt(selectedRow, 1).toString());
employee_office.setText(tableEmployees.getValueAt(selectedRow, 21).toString());
employee_department.setText(tableEmployees.getValueAt(selectedRow,15).toString());
employee_jobtitle.setText(tableEmployees.getValueAt(selectedRow, 22).toString());
}
}
});
} else {
tableEmployees.setRowSelectionAllowed(false);
}
scrollPane = new JScrollPane(tableEmployees, JScrollPane.VERTICAL_SCROLLBAR_AS_NEEDED, JScrollPane.HORIZONTAL_SCROLLBAR_AS_NEEDED);
// here - activations of two scroll bars
testPanel.removeAll();
testPanel.add(scrollPane, BorderLayout.CENTER);
testPanel.updateUI();
}
|
Это сообщение отредактировал(а) sanik - 6.1.2005, 22:19 |
Новый исправленный вариант GUI, ("Делаем правильный... глюкнулся") 
Загрузка ...
