jdbcadapter.java

一个用java写的地震分析软件(无源码)-used to write a seismic analysis software (without source)

    public int getRowCount() {
        return rows.size();
    }

    public Object getValueAt(int aRow, int aColumn) {
	if (rows.get(aRow) == null) System.out.println("JDBCAdapter: getValueAt rows null");
        ArrayList row = (ArrayList)rows.get(aRow);
        return row.get(aColumn);
    }

    public String dbRepresentation(int colIndex, Object value) {
      if (value == null) return "null" ;
      int type = -1;
      if (columnDataArray == null) columnDataArray = getColumnData(colTbls[colIndex]);
      for ( int i=0 ; i < columnDataArray.length; i++) {
	if (columnDataArray[i].columnName.equals(colNames[colIndex]) ) {
	  String typeName  =  columnDataArray[i].typeName;
	  if (typeName.equals("VARCHAR2")) {
	    type = Types.VARCHAR; 
	  }
	  else if (typeName.equals("NUMBER")) {
	    if (columnDataArray[i].decimalDigits == 0)  type = Types.INTEGER; // colScale[colIndex];
	    else  type = Types.DOUBLE;
	  }
	  else if (typeName.equals("FLOAT")) {
	    type =  Types.FLOAT;
	  }
	  else if (typeName.equals("DATE")) {
	    type = Types.DATE;
	  }
	  else if (typeName.equals("TIMESTAMP")) {
	    type = Types.TIMESTAMP;
	  }
	  else if (typeName.equals("OTHER")) {
	    System.out.println("JDBCAdapter dbRepresentation DEBUG at colIndex:" + colIndex + " getType typeName == OTHER");
	    type = Types.OTHER;
	  }
	}
      }
//	int type = colTypes[colIndex];
//	Debug.println("JDBCAdapter: index: " + colIndex + " type:" + type + " class: " + value.getClass().getName());
        switch(type) {
        case Types.DATE:
	case Types.TIME:
	case Types.TIMESTAMP:
//		Debug.println("dbRepresentation date value: " + StringSQL.valueOf(value));
	    String tmp =  StringSQL.valueOf(value);
	    if (tmp.length() > 21) tmp =  tmp.substring(0,20) + "'";
//		Debug.println("dbRep datestring: " + tmp);
	    return tmp;
        case Types.INTEGER:
        case Types.DOUBLE:
        case Types.FLOAT:
	case Types.REAL:
	case Types.NUMERIC:
	case Types.DECIMAL:
        case Types.CHAR:
        case Types.VARCHAR:
        case Types.LONGVARCHAR:
        case Types.OTHER:
//            return value.toString() ;
//		Debug.println("dbRepresentation of value: " + StringSQL.valueOf(value));
            return StringSQL.valueOf(value);
        case Types.BIT:
            return ((Boolean)value).booleanValue() ? "1" : "0";
        default:
            return "'" + value.toString() + "'";
        }
    }

    public void setValueAt(Object value, int rowIndex, int colIndex) {
        try {
//	    Debug.println("JDBCAdapter: setValueAt row,col:" + rowIndex + "," + colIndex);
	    if (rsMD == null) System.out.println("JDBCAdapter: setValueAt rsMD is null");
	    int nullable = rsMD.isNullable(colIndex+1);
	    if (nullable != 1) {
	      if (value == null) {
		System.out.println("JDBCAdapter: setValueAt NULL value not allowed");
		return;
	      }
	      else if (value instanceof String) {
		if ( ((String) value).trim().toUpperCase().equals("NULL")) {
		  System.out.println("JDBCAdapter: setValueAt NULL string value not allowed");
		  return;
		}
	      }
	    }
	}
        catch (SQLException ex) {
            System.err.println("JDBCAdapter: setValueAt rsMD.isNullable SQLException");
	    SQLExceptionHandler.prtSQLException(ex);
        }
	if (updateDBflag) updateRowValueDB(value, rowIndex, colIndex) ;

        ArrayList dataRow = (ArrayList)rows.get(rowIndex);
//        dataRow.set(value, colIndex);
        dataRow.add(colIndex, value);
//	rows.set(dataRow, rowIndex);
	fireTableCellUpdated(rowIndex, colIndex);
    }

// UPDATE OF DATABASE 
    public int updateRowValueDB(Object value, int rowIndex, int colIndex) {
//	if (value == null) Debug.println ("updateRowValueDB value == null");
//	else Debug.println("updateRowValueDB value:" + value.getClass().getName() + " : " + value.toString());
	int rval = -1;
	String tableName;
        try {
	    if (queryTables.length == 1) {
		tableName = queryTables[0];
                if (tableName.length() == 0) {
		    System.out.println("JDBCAdapter: updateRowValueDB tableName empty string.");
		    return rval;
		}
            }
	    else {
		tableName = rsMD.getTableName(colIndex+1); // data may be from more than one table 
		if (tableName == null) {
		    System.out.println("JDBCAdapter: updateRowValueDB rsMD.getTableName returned null.");
		    return rval;
		}
	    }
//	    Debug.println("updateRowValueDB at getPrimaryKeys");
	    String [] pkeys = getPrimaryKeys(tableName);
            String columnName = getColumnName(colIndex);
            String query =
                "UPDATE " + tableName +
                " SET " + columnName + " = " +
		dbRepresentation(colIndex, value) +
                "  WHERE  " ;
	    int ikeycol;
            for (int ikey = 0; ikey < pkeys.length; ikey++) {
                if (ikey > 0) query = query + " AND ";
		ikeycol = findColumn(pkeys[ikey]);
                query = query + pkeys[ikey] + " = " +
                    dbRepresentation(ikeycol,getValueAt(rowIndex, ikeycol)); 
            }
	    if (conn == null) {
		System.out.println("JDBCAdapter: updateRowValueDB: connection null");
		return rval;
            }
	    else {
		if (! lastUpdateQuery.equals(query)) {
                    System.out.println("JDBCAdapter: updateRowValueDB: Old value:" + dbRepresentation(colIndex,getValueAt(rowIndex, colIndex))); 
		    System.out.println("JDBCAdapter: updateRowDB SQL statement:\n" + query);
		    try {
			if (lockstmt == null) lockstmt = conn.createStatement();
			lockstmt.execute(strLock1 + tableName + strLock2);
		    }
		    catch (SQLException ex) {
			System.err.println("JDBCAdapter: updateRowDB execute lock statement SQLException");
			SQLExceptionHandler.prtSQLException(ex);
			return rval;
		    }
		    if (updstmt == null) updstmt = conn.createStatement();
		    rval = updstmt.executeUpdate(query);
		    conn.commit();
//		    Debug.println("JDBCAdapter: updateRowDB executeUpdate after commit");
		    lastUpdateQuery = query;
		}
	    }
	}
        catch (SQLException ex) {
            System.err.println("JDBCAdapter: updateRowDB executeUpdate statement SQLException");
	    SQLExceptionHandler.prtSQLException(ex);
        }
	return rval;
    }
// end of UPDATE 

    public int addRow(int rowIndex) {
    	ArrayList newRow = (ArrayList) makeNullRow();
	rows.add(rowIndex, newRow);
//	int rowIndex = rows.size() - 1;
	fireTableRowsInserted(rowIndex,rowIndex); 
	return rows.size();
    }
    
    public int addRow(int rowIndex, Object [] values) {
	if (values.length != getColumnCount()) {
	    System.err.println("JDBCAdapter: addRow values length not equal to getColumnCount.");
	    return -1;
	}
/* must have correspondence between column names and values expected
	for (int i = 0; i < getColumnCount(); i++) {
	    if (values[i] != null)  {
		if (! values[i].getClass().getName().equals(getColumnClass(i).getName()) ) {
		    System.err.println("JDBCAdapter: addRow mismatched class for column:" + i +
			"  ColumnClass:" + getColumnClass(i).getName() + " ValueClass:" + values[i].getClass().getName());
		    return -1;
		}
	    }
	    else {
// or leave as null object - noop
	    } 
	}
*/
    	ArrayList newRow = (ArrayList) makeNewRow(values);
	rows.add(rowIndex, newRow);
//	int rowIndex = rows.size() - 1;
	fireTableRowsInserted(rowIndex,rowIndex); 
	return rows.size();
    }
    
    List makeNewRow(Object[] values) {
	ArrayList newRow = new ArrayList(64);
	for (int i = 0; i < getColumnCount(); i++) {
		newRow.add(values[i]);
	}
	return newRow;
    }
    
    List makeNullRow() {
	ArrayList nullRow = new ArrayList(64);
	Object nullObject = null;
//	Class className;
	for (int i = 0; i < getColumnCount(); i++) {
	    nullRow.add(nullObject);
	}
	return nullRow;
    }

// DELETE row from TableModel and DB
    public int deleteRow(int rowIndex) {
	int rval = -1;
  	if (rowIndex > -1 && rowIndex < rows.size()) {
	    if (insertedRowIndex != rowIndex) {
		rval = deleteRowFromDB(rowIndex);
		if (rval > 0) {
//		    Debug.println("JDBCAdapter: deleteRowFromDB dropped " + rval + " rows from database");
		}
		else {
		    System.err.println("JDBCAdapter: delrow unable to drop table row at index " + rowIndex + " from database");
		}
	    }
	    rows.remove(rowIndex);
	    fireTableRowsDeleted(rowIndex,rowIndex);
//	    Debug.println("JDBCAdapter: delrow dropped row " + rowIndex + " from table");
	    if (insertedRowIndex == rowIndex) {
		insertDBflag = false;
		insertedRowIndex = -1;
	    }
	}
	return rval;
    }
    
// DELETE row from DB
    public int deleteRowFromDB(int rowIndex) {
	int rval = -1;
	if (queryTables.length != 1) {
	  System.err.println("JDBCAdapter: deleteRowFromDB SQL query includes columns from more than 1 table.");
	  return rval;
	}
        try {
	    String [] pkeys = getPrimaryKeys(queryTables[0]);
            String query =
                "DELETE FROM " + queryTables[0] + " WHERE " ;
	    int ikeycol;
            for (int ikey = 0; ikey < pkeys.length; ikey++) {
                if (ikey > 0) query = query + " AND ";
		ikeycol = findColumn(pkeys[ikey]);
                query = query + pkeys[ikey] + " = " +
                    dbRepresentation(ikeycol, getValueAt(rowIndex, ikeycol));
            }
	    System.out.println("JDBCAdapter: deleteRowFromDB SQL statement:\n" + query);
	    try {
		if (lockstmt == null) lockstmt = conn.createStatement();
		lockstmt.execute(strLock1 + queryTables[0] + strLock2);
	    }
	    catch (SQLException ex) {
		System.err.println("JDBCAdapter: updateRowDB execute lock statement SQLException");
		SQLExceptionHandler.prtSQLException(ex);
		return rval;
	    }
	    if (updstmt == null) updstmt = conn.createStatement();
	    rval = updstmt.executeUpdate(query);
	    conn.commit();
	}
        catch (SQLException ex) {
            System.err.println("JDBCAdapter: deleteRowFromDB SQL executeUpdate statement SQLException");
	    SQLExceptionHandler.prtSQLException(ex);
        }
	return rval;
    }

// INSERT table row into DB
    public int insertIntoDB(JTable tbl) {
	if (! (tbl.getModel() instanceof JDBCAdapter) ) return -1;
	int rowIndex = tbl.getSelectedRow();
	int rval = insertIntoDB(rowIndex);
//	if (rval > 0) tbl.clearSelection();
	return rval;
    }

    public int insertIntoDB () {
	int rval = insertIntoDB(insertedRowIndex);
        if (rval > 0) insertedRowIndex = -1;
	return rval;
    }

    public int insertIntoDB (int rowid) {
	int rval = -1;

	if (queryTables.length != 1) {
	  System.err.println("JDBCAdapter: insertIntoDB: Only allowing inserts for single table, not join views.");
//	  java.awt.Toolkit.getDefaultToolkit().beep();
	  return rval;
	}

//	int rowid = insertedRowIndex;

	if (rowid < 0) {
	  System.err.println("JDBCAdapter: insertIntoDB: Invalid row index; index < 0.");
	  return rval;
	}
	else if (rowid > getRowCount()) {
	  System.err.println("JDBCAdapter: insertIntoDB: Invalid row index; index greater than table row count.");
	  return rval;
	}
	else {
	  StringBuffer strSQL = new StringBuffer(255);
	  strSQL.append("INSERT INTO " + queryTables[0].toString() + "(" );
	  for (int i = 0; i < getColumnCount(); i++) {
	    strSQL.append(colNames[i]);
	    strSQL.append(", ");
	  }
	  strSQL.delete(strSQL.length()-2, strSQL.length());
	  strSQL.append(") VALUES (" );
	  for (int i = 0; i < getColumnCount(); i++) {
//	    strSQL.append(StringSQL.valueOf(getValueAt(rowid,i))); 
	    if (colNames[i].equalsIgnoreCase("LDDATE")) strSQL.append("SYSDATE");
            else strSQL.append(dbRepresentation(i, getValueAt(rowid, i)));

	    strSQL.append(", ");
	  }
	  strSQL.delete(strSQL.length()-2, strSQL.length());
	  strSQL.append(")");
	  System.out.println("JDBCAdapter: insertToDB SQL statement:\n" + strSQL);

	  try {
	    try {
	      if (lockstmt == null) lockstmt = conn.createStatement();
	      lockstmt.execute(strLock1 + queryTables[0] + strLock2);
	    }
	    catch (SQLException ex) {
	      System.err.println("JDBCAdapter: updateRowDB execute lock statement SQLException");
	      SQLExceptionHandler.prtSQLException(ex);
	      return rval;
	    }
	    if (updstmt == null) updstmt = conn.createStatement();
	    rval = updstmt.executeUpdate(strSQL.toString());
	    conn.commit();
	  }
          catch (SQLException ex) {
	    System.err.println("JDBCAdapter: insertToDB executeUpdate SQLException");
	    SQLExceptionHandler.prtSQLException(ex);
          }
	  return rval;
	}
    }
}