数据库基础应用学习

J2EE中的分布式事务：
分布式事务是跨越多个数据源的事务。与通常在单个数据库服务器环境下产生的数据库事务不同，而J2EE的分布式事务能够跨越不同数据库产商的多个数据库服务器。

1、业务层模式：业务层的工作是隔离和封装应用程序的业务逻辑。驻留在表示层的web组件的逻辑可以有效地封装到本层中。业务逻辑一般都包括从从数据库中检索和过滤数据，验证和执行数据库的更新。
 值对象模式：封装拉业务域对象（一个业务对象）的数据成员。并提供拉将信息从业务层传输到表示层的方法。表示层用于缓存值对象。值对象包括代表业务域对象元素的成员和检索这些成员的方法。业务对象的职责是管理持久化对象存储器，因此，必须调用业务对象的方法开改变持久化对象存储器，通过将update方法公开表示给表示层调来实现这个功能。
通过业务对象创建和封装值对象。业务对象组件的职责是实例化值对象然后用正确的值装载值对象属性。
减少数据和应用程序之间的绑定也很重要，因为对于用户而言，希望由其他层来决定如何处理数据。事实上，通过应用数据访问对象设计模式，数据访问对象管理所有关于数据的信息。
数据访问对象封装了类设计中的数据访问细节和一种通用的数据检索方法，既执行查询并且返回Resultset。
使用数据访问对象的事例：
public class GeneralDAO  {

private Connection con;
private PreparedStatement preparedQuery;
private ResultSet             resultSet;
private ResultSetMetaData     resultMD;

private GeneralAggregateVO     gvo = new GeneralAggregateVO();

// the default DataSource name
private static String defaultDS = "movies";    

// -------------------------------------------------
public static void main( String args[] ) {

String     fileName = null;
String     query    = null;
GeneralDAO gd     = null;

try {

     //  // get a local object reference
     //
     //gd = new GeneralDAO();
     gd = new GeneralDAO("knowledgebase");

     //
     // if we didn't get a command line argument, just use the default file name
     //
     if ( args.length == 1 )
        fileName = args[0];
     else
        fileName = "query.sql";

    //
    // get the query
    //
    query = gd.getQuery( fileName );
    if ( query == null ) {
        System.out.println( "No query found. Exiting ... " );
        System.exit( -1 );
     }

    //
    // process the query
    //
    System.out.println("****************************");
    System.out.println("Processing file: " + fileName );
    System.out.println("Processing Query: " + query );
    System.out.println("****************************");

gd.gvo.clear();
gd.executeQuery( query );
gd.gvo.dumpContents();

     //
     // pass the connection into the processQuery method.
     // it is responsible for executing the query
     //
//     ResultSet rs = dq.processQuery( dq.con,  query );
  
     //
     // output results
     //
 //    dq.outputResultSet( rs );
    
}
catch (Exception e) {
     System.out.println("Exception in main: " + e.getMessage() );
     e.printStackTrace();
}
finally {

    try {  
        // close the connection
 if ( gd.con != null )
            gd.con.close();
    }
    catch (SQLException e) {
          e.printStackTrace();
    }
}

}
public void executeQuery( String query ) throws SQLException {

// debug
if ( con == null )
    System.out.println("con is null.");

   //
   // execute the query and store in our local ResultSet
   //
   preparedQuery = con.prepareStatement( query );
   System.out.println("Preparing and executing query ... ");
   if ( preparedQuery != null ) {
       resultSet     = preparedQuery.executeQuery();
       resultMD      = resultSet.getMetaData();
   }
   else {
       System.out.println( "Null PreparedStatement. Quitting ... ");
       System.exit(-1);
   }

   //
   // store the resultSet in our GeneralVO object
   //
   System.out.println( "Loading results ... " );
   loadResults();

}

public void executeQuery() throws SQLException {

   //
   // this asserts that executeQuery( string ) has been called previously
   //
   resultSet     = preparedQuery.executeQuery();
   resultMD      = resultSet.getMetaData();

   // store the resultSet in our GeneralVO object
   System.out.println( "Loading results ... " );
   loadResults();
    
}

public GeneralDAO( String dataSourceName  ) {

try {
     //
     // JNDI startup parameters are stored in the "jndi.properties" file in the
     // classpath.
     //
     InitialContext ctx = new InitialContext( );

     //
     // get the DataSource from the JNDI name server
     //
     DataSource ds = (DataSource) ctx.lookup( dataSourceName );

     //
     // get the connection from the DataSource
     //
     con = ds.getConnection( );
}
catch (NamingException e ) {
      System.out.println( "NamingException in GeneralDAO: " + e.getMessage() );
}
catch (SQLException e) {
      System.out.println( "SQLException in GeneralDAO: " + e.getMessage() );
}

}
// --------------------------------------------------------------

public GeneralDAO() {

     //
     // call constructor with the default DataSource name
     //
     this( defaultDS );

}

// -------------------------------------------------------------------
//public ResultSet processQuery( String query ) {
//    return processQuery( con, query );
//}

public Connection getConnection() {

    return this.con;

}

// -------------------------------------------------------------------
//public ResultSet  processQuery( Connection con, String query ) {
//ResultSet rs = null;
//try {
//Statement stmt = con.createStatement();
//
//rs = stmt.executeQuery( query );
//
//}
//
//catch (SQLException e ) {
// display SQL specific exception information
//     System.out.println("*************************" );
//     System.out.println("SQLException in processQuery: " + e.getMessage() );
//     System.out.println("** SQLState: " + e.getSQLState());
//     System.out.println("** SQL Error Code: " + e.getErrorCode());
//     System.out.println("*************************" );
//     e.printStackTrace();
//}
//finally {
// return rs;
//}
//}

// -------------------------------------------------------------------
//void outputResultSet( ResultSet rs ) {
//
//try {
//    ResultSetMetaData resMetaData = rs.getMetaData();
//   while ( rs.next() ) {
//
//int colCount = resMetaData.getColumnCount();
// System.out.println( "**********************************" );
//       for (int n = 1; n <= colCount; n++ ) {
//             System.out.println( resMetaData.getColumnLabel( n )  +
//                                    ": " + "/t/t" +
//                rs.getString( n ) );                    
//
//           }
//     System.out.println( "**********************************" );
//
//    }
//}
//catch (SQLException e ) {
//    // display SQL specific exception information
//   System.out.println("*************************" );
//  System.out.println("SQLException in outputResultSet: " + e.getMessage() );
// System.out.println("** SQLState: " + e.getSQLState());
//System.out.println("** SQL Error Code: " + e.getErrorCode());
//System.out.println("*************************" );
//e.printStackTrace();
//}
//}

// ------------------------------------------------------------------
// retrieves a query from a given filename
public String getQuery( String fn ) {
String buffer  = null; // data buffer
String retVal  = null; // query string to return

try {
     //
     // create a file reader for the file containing the query
     //
     BufferedReader reader = new BufferedReader ( new FileReader( fn ));

     //
     // start reading the file
     //
     buffer = reader.readLine();
     while ( buffer != null ) {

          if ( retVal == null ) {   // this is the first pass
              retVal = buffer;
           }
          else {
              retVal += buffer;     // append
          }
      //
      // continue reading the file
      //
      buffer = reader.readLine();
     }

    //
    // close the file reader
    //
    reader.close();
}
catch (FileNotFoundException e) {
     System.out.println("FileNotFoundException in getQuery: " + e.getMessage() );
}
catch (IOException e) {
     System.out.println("IOException in getQuery: " + e.getMessage() );
}
finally {
     return retVal;
}

}
// ---------------------------------------------------------------------

// returns the number of rows in a ResultSet
public int getRows( ResultSet rs ) {
int rows = 0;
try {

    while ( rs.next() ) {
            rows++;
    }

}
catch (SQLException e) {
      System.out.println("SQLException in getRows: " + e.getMessage() );
}
finally {
    return rows;
}

}

// set our resultset data to look like the gvo
public void setAggregateVO( ) throws SQLException {

   for ( int n = 0; n

         // move to the row
         gvo.absolute( n ); 

        // updates in this row
       if ( gvo.getUpdateStatus( n ) ) {
      
          // move to the row
          resultSet.absolute( n + 1 );                         

          for ( int z = 0; z

  // update the object
                resultSet.updateObject( (z + 1), gvo.getObject( z ) );

    } 

       }    
   }

}

// load from resultset to aggregate vo
public void loadAggregateVO( GeneralAggregateVO  gvo ) throws SQLException {

   this.gvo = gvo;
   setAggregateVO();   // set our resultset to look like the gvo

}

public void deleteRow( int row ) throws SQLException {

    // delete our current row
    resultSet.absolute( row + 1 );
    resultSet.deleteRow();

}
// ------------------------------------------------------------------------------

public void applyUpdates ( GeneralAggregateVO gvo ) throws SQLException {
//
// apply the gvo before applying the updates, to get the two in synch
// this is needed for RMI access of the gdao

try {

   //
   // load the general aggregate value object passed in
   //
   this.loadAggregateVO( gvo );

   //
   // apply updates
   //
   this.applyUpdates();
}
catch (Exception e)  {
      System.out.println( "Exception in applyUpdates: " + e.getMessage() );
}
}
// apply updates from the gvo
// this currently doesn't require setAggregateVO to be called
// since it calls updateObject for each updated row/column combination
//
public void applyUpdates() throws SQLException {

   for ( int n = 0; n

          // move to the row
   gvo.absolute( n );            // our aggregate VO data
          resultSet.absolute( n + 1 );  // the underlying resultset

         // if this is an insert update, we need to move to an 'insert' row
  if ( gvo.getInsertStatus( n ) ) {   // is this an insert row
      resultSet.moveToInsertRow();
  }

       if ( gvo.getUpdateStatus( n ) ) { // any update in this row ?

           // update only those cols that need updating
    for ( int z = 0; z         if ( gvo.getUpdateStatus( n, z ) ) {   // update status for the row, column
            resultSet.updateObject( (z + 1), gvo.getObject( z ));
               }
    }

  if ( gvo.getInsertStatus( n ) ) {   // this an insert row
      resultSet.insertRow();          // insert the row
  }
         else {                              // this is an update
   resultSet.updateRow();             // update the row
  }
 }
   }

}

// ----------------------------------------------------------------------------
public void loadResults() throws SQLException {

      // by default we use the internal resultset 
      loadResults( resultSet );
}

// ----------------------------------------------------------------------------
// load results from the resultset into the GeneralAggregateVO
public void loadResults( ResultSet rs ) throws SQLException {

      // clear GeneralAggregateVO
      gvo.clear();

     // set the columns in the gvo
     for ( int c = 1; c <= resultMD.getColumnCount(); c++) {

          // ** try getColumnLabel **
          gvo.setColKey( resultMD.getColumnName( c ), (c-1) );
     }

     // for each row
     while ( rs.next() ) {

       for ( int n = 1; n <= resultMD.getColumnCount(); n++ )  {
      
     // apply some general conversion
     // gvo.addObject( JDBCTypeMapper.getColumnDataString( n, rs ) );
     gvo.addObject( JDBCTypeMapper.getColumnData( n, rs ) );
     // gvo.addObject( rs.getObject( n ) );
       }

         // add the row to our aggregate object
         gvo.addRow();

     }

}    // 

public void clearUpdates() {

    // reset update flags
    // does NOT reload data to pre-updates state
    gvo.clearUpdates();
}

public GeneralAggregateVO getGeneralAggregateVO() {

        return gvo;
}

} 

值对象模式的事例：
public interface GenericVO {

// retrieve contents
public Object getObject( int col ); // get the String value at the column offset
public Object getObject( String colName ); // get the String value at the column name position

// set contents
public void setObject( int col, Object obj );
public void setObject( String colName, Object obj );

// add contents
public void addObject( Object obj );

}

public class GeneralAggregateVO implements GenericVO, Serializable {

// internal vectors
private Vector    rows          = new Vector();  // stores and array of currentRecord vectors
private Vector    updateFlags   = new Vector();  // an update flag for each row
private Vector    currentRecord = new Vector();  // the column values (object references) for the current record
private Hashtable updateCols    = new Hashtable(); // update row,cols that have been updated

// current position
private int       currentRow    = 0;

// maintain counts
private int       rowCount      = 0;
private int       columnCount   = 0;

// for column name mapping
private Hashtable colKeys       = new Hashtable();
private Hashtable colNums       = new Hashtable();

public static void main( String args[] ) {

// create an object
GeneralAggregateVO gvo = new GeneralAggregateVO();

System.out.println("Starting .... ");
System.out.println("Vector size : " + gvo.currentRecord.size() );
System.out.println("currentRow: " + gvo.getRowPos() );
System.out.println("rowCount: " + gvo.getRowCount() );
System.out.println("******************************");

// set the column keys for column name lookups
gvo.setColKey( "col1", 0 );
gvo.setColKey( "col2", 1 );
gvo.setColKey( "col3", 2 );

// add some data
gvo.addObject("this");      // 0
gvo.addObject("that");      // 1
gvo.addObject("the other"); // 2

// add current row onto the end
gvo.addRow();

// print it
System.out.println("******************************");
System.out.println("Data added ... " );
System.out.println("currentRow: " + gvo.getRowPos() );
System.out.println("rowCount: " + gvo.getRowCount() );

// add some more data
gvo.addObject("some");      // 0
gvo.addObject("more");      // 1
gvo.addObject("data"); // 2

// add row
gvo.addRow();

System.out.println("******************************");
System.out.println("Data added ... " );
System.out.println("currentRow: " + gvo.getRowPos() );
System.out.println("rowCount: " + gvo.getRowCount() );

// add some more data
gvo.addObject("fred");      // 0
gvo.addObject("is");      // 1
gvo.addObject("not here"); // 2

// add row
gvo.addRow();

System.out.println("******************************");
System.out.println("Data added ... " );
System.out.println("currentRow: " + gvo.getRowPos() );
System.out.println("rowCount: " + gvo.getRowCount() );

// return it
gvo.absolute(0);
System.out.println(" absolute(0) " );
System.out.println("currentRow: " + gvo.getRowPos() );
System.out.println("rowCount: " + gvo.getRowCount() );
System.out.println( "1: " + gvo.getObject(0));
System.out.println( "2: " + gvo.getObject(1));
System.out.println( "3: " + gvo.getObject(2));

gvo.absolute(1);
System.out.println(" absolute(1) " );
System.out.println("currentRow: " + gvo.getRowPos() );
System.out.println("rowCount: " + gvo.getRowCount() );
System.out.println( "1: " + gvo.getObject(0));
System.out.println( "2: " + gvo.getObject(1));
System.out.println( "3: " + gvo.getObject(2));

gvo.absolute(2);
System.out.println(" absolute(2) " );
System.out.println("currentRow: " + gvo.getRowPos() );
System.out.println("rowCount: " + gvo.getRowCount() );
System.out.println( "1: " + gvo.getObject(0));
System.out.println( "2: " + gvo.getObject(1));
System.out.println( "3: " + gvo.getObject(2));

gvo.absolute(0);
System.out.println(" absolute(0) " );
System.out.println("currentRow: " + gvo.getRowPos() );
System.out.println("rowCount: " + gvo.getRowCount() );
System.out.println("getObject('col1'): " + gvo.getObject("col1"));
System.out.println("getObject('col2'): " + gvo.getObject("col2"));
System.out.println("getObject('col3'): " + gvo.getObject("col3"));

gvo.absolute(1);
System.out.println(" absolute(1) " );
System.out.println("currentRow: " + gvo.getRowPos() );
System.out.println("rowCount: " + gvo.getRowCount() );
System.out.println("getObject('col1'): " + gvo.getObject("col1"));
System.out.println("getObject('col2'): " + gvo.getObject("col2"));
System.out.println("getObject('col3'): " + gvo.getObject("col3"));

// change the contents
gvo.absolute(2);
System.out.println(" absolute(2) " );
System.out.println("currentRow: " + gvo.getRowPos() );
System.out.println("rowCount: " + gvo.getRowCount() );
gvo.setObject( "col1", "argghhh");
gvo.setObject( "col2", "oh no mr. bill");
gvo.setObject( "col3", "yechhh");

// print it
gvo.absolute(2);
System.out.println(" absolute(2) " );
System.out.println("currentRow: " + gvo.getRowPos() );
System.out.println("rowCount: " + gvo.getRowCount() );
System.out.println("getObject('col1'): " + gvo.getObject("col1"));
System.out.println("getObject('col2'): " + gvo.getObject("col2"));
System.out.println("getObject('col3'): " + gvo.getObject("col3"));

// deleteRow
gvo.absolute(1);
gvo.deleteRow();
System.out.println(" absolute(1) " );
System.out.println("deleteRow() " );
System.out.println("currentRow: " + gvo.getRowPos() );
System.out.println("rowCount: " + gvo.getRowCount() );
System.out.println("getObject('col1'): " + gvo.getObject("col1"));
System.out.println("getObject('col2'): " + gvo.getObject("col2"));
System.out.println("getObject('col3'): " + gvo.getObject("col3"));

// append a row
gvo.appendRow();
System.out.println( "appended. rowpos: " + gvo.getRowPos() );

gvo.setObject(0,"this");      // 0
gvo.setObject(1,"was");      // 1
gvo.setObject(2,"appended"); // 2

if ( gvo.getInsertStatus( gvo.getRowPos() ) )
    System.out.println("this is an insert row.");
else
    System.out.println("this SHOULD BE an insert row.");

// iterate
gvo.dumpContents();

}

// ------------------------------------------------------------------

// set contents

// set keys for colname to positional mapping
public void setColKey( String name, int pos ) {

   // map column name to position
   colKeys.put( name, new Integer( pos ) );
 
   // reverse map. for lookup position and match to name 
   colNums.put( new Integer( pos ), name );

   columnCount++;

}

public void setObject( int col, Object obj ) {

    currentRecord.set( col, obj );

}

public void setObject( String keyName, Object obj ) {

      int pos  = ((Integer) colKeys.get( keyName )).intValue();
      setObject( pos, obj );

}

// ------------------------------------------------------------------

public void addObject( Object obj ) {

    // will fail if we attempt to go beyond columnCount
    if ( currentRecord.size() <= columnCount ) {

        // add the object
        currentRecord.add( obj );              

    }
    else
        System.out.println("Attempt to get beyond number of columns in VO.");

}
// ------------------------------------------------------------------

// add the current VO to our rows
public void addRow() {

     rows.add( (Object) currentRecord );

     // create a new row
     updateFlags.add( new String( "" ) );     // null for 'no updates'
     currentRecord = new Vector();
     currentRow++;
     rowCount++;

}

// ------------------------------------------------------------------

// clear our current row
public void clearCurrentRow() {
     currentRecord.removeAllElements();
}

// ------------------------------------------------------------------
// delete our current row
public void deleteRow() {
     rows.remove( currentRow );
     currentRow--;
     rowCount--;
}
// ------------------------------------------------------------------

// append a row onto the end of our data set
// that row then becomes the current record
public void appendRow() {

// this is considered an 'insert' row. it will need to be placed into the
// underlying database with an insert operation

// add a 'blank' row that we later update
     // create a new row

     updateFlags.add( new String( "" ) );     // this is an insert row
     currentRecord = new Vector();

     // create empty object references
     for ( int n = 0; n           addObject( "" );
     }

     rows.add( (Object) currentRecord );
     rowCount++;
     currentRow = rowCount - 1;                           // we are pointing at this row - the last row

     updateFlags.set( getRowPos(), new String( "I" )  );  // set the update flag for this row

}

// ------------------------------------------------------------------
// update - set flags to indicate update status 
public void updateObject( int col, Object value ) {
HashSet colSet     = null;
Integer rowPos     = new Integer( getRowPos() ); // current row position

    // set the update flags
    if  ( !( getInsertStatus( getRowPos() )) )   {        // only set this if the 'insert' flag has NOT been set
        updateFlags.set( getRowPos(), new String( "U" ) );  // set the update flag for this row
    }

    if ( updateCols.containsKey( rowPos ) ) {
        colSet = (HashSet) updateCols.get( rowPos ); // get the cols hashtable for this row
        colSet.add( new Integer( col ));  // add this column to the list of updated cols
    }
    else {                                                 // create the cols hashtable for this row
        colSet = new HashSet();
        colSet.add( new Integer( col ));  // add this column to the list of updated cols
        updateCols.put( rowPos, colSet ); // add to the updateCols Hashtable
    }

    // set the object with designated value (update the object)
    setObject( col, value );

}
// ------------------------------------------------------------------
public boolean getInsertStatus( int row ) {

      if ( ( (String) updateFlags.get( row ) ).equals( "I" ) )
         return true;
      else
         return false;
}
// ------------------------------------------------------------------

// get the update status for a specific row and column
public boolean getUpdateStatus( int row, int col ) {
boolean retVal = false;

    // is this an 'insert' column ?
    if   ( ((String) updateFlags.get( row )).toString().equals("I") ) {
         retVal = true;                                  // all columns need 'updating' for inserts
    }

    // is this an 'update' column ? if so, then check the column for updates
    if   ( ((String) updateFlags.get( row )).toString().equals("U") ) {

        // get the Hashset for this row
        HashSet colSet = (HashSet) updateCols.get( new Integer( row ));

 // is this column in the HashSet ?
 retVal = colSet.contains( new Integer( col ) );
    }
 
  return retVal;
}

//public Object getUpdateFlag ( int row ) {
//       return updateFlags.get( row );
//}

// get the update status for the current row
public boolean getUpdateStatus() { // get the update status for the current row

     if ( ((String) updateFlags.get( getRowPos() )) != null ) { // then the 'update' flag has been set
        return true;
     }
     else {
        return false;
     }

}

// get the update status for a specific row
public boolean getUpdateStatus(int row ) { // get the update status for a specific row

     if ( ((String) updateFlags.get( row )) != null ) { // then the 'update' flag has been set
        return true;
     }
     else {
       return false;
     }

}

public int getRowCount() {

   return rowCount;

}

public int getColumnCount() {
  
   return columnCount;
}

public String getColumnName( int col ) {
   
   return (String) colNums.get( new Integer( col ) ); // column name must be String
}

public int getRowPos() {
 
   return currentRow;

}

// get the String value at the column offset
public Object getObject( int col )  {

    return currentRecord.get( col );

}

// ------------------------------------------------------------------
 // get the String value at the column name position
public Object getObject( String colName ) {
  
    int pos = ((Integer) colKeys.get( colName )).intValue();
    return getObject( pos ) ;

}

// ------------------------------------------------------------------
// positioning
// move to the absolute postion
public void absolute( int pos ) {

    currentRow = pos;
    currentRecord = (Vector) rows.get( currentRow );

}

// move to the relative position
public void relative( int pos ) {

    currentRow += pos;
    currentRecord = (Vector) rows.get( currentRow );

}

// ------------------------------------------------------------------

public void dumpContents() {

System.out.println( "*********************************" );
System.out.println("Column Count: " + this.getColumnCount() );
System.out.println("Row Count: " + this.getRowCount() );

    for ( int n = 0; n          this.absolute( n );
         System.out.println( "*********************************" );
         System.out.println( "Row: " + n );
         for ( int z = 0; z               System.out.println( "Column Name: " + this.getColumnName( z ) +
                           " - Column Value: " + this.getObject( z ) );

  }
    }

}
// --------------------------------------------------------------
// clears update flags ... does NOT reset data to pre-update state
public void clearUpdates() {

for ( int n = 0; n      updateFlags.set( n, new String( "" ) );  // set the update flag for this row

}
// --------------------------------------------------------------
// clear internal counters and vectors and allow this object to be reused
public void clear() {

// clear the vectors
rows.removeAllElements();
updateFlags.removeAllElements(); 
currentRecord.removeAllElements();

// need a new object reference for the Hashtables
colKeys    = new Hashtable();
colNums    = new Hashtable();
updateCols = new Hashtable();

currentRow    = 0;
rowCount      = 0;
columnCount   = 0;

}

}