concretedataaccessor.java

前一段时间看《数据访问模式》时

/**
 *@version 1.0
 *@author cbf107
 *@link http://www.cbf107.com
*/
package com.cbf107.www;

/*
数据访问器
*/
import java.sql.*;
import java.util.*;


public class ConcreteDataAccessor implements DataAccessor {
	
	   private Conn con;
	
	   private Connection accountingConnection;
	   private Connection payrollConnection;
	   private Connection otherConnection; 
	   
//
	   public ConcreteDataAccessor() throws DataException {
	      try{
	      	 con=new Conn();
	      	 
	         accountingConnection  =  DriverManager.getConnection(con.sConnStr);
	         payrollConnection     =  DriverManager.getConnection(con.sConnStr);
	         otherConnection       =  DriverManager.getConnection(con.sConnStr);
	      }
	      catch(SQLException e){
	         throw new DataException("不能建立使用数据访问器",e);
	      }
	   }
//根据条件读取数据返回List
       public List read(
			         String table, 
			         String[] columns,
					 Row selectionRow,
			         String[] sortColumns,
					 String order
			        ) throws DataException {
	      try{
	        StringBuffer buffer = new StringBuffer();
	        buffer.append(" SELECT ");
	         
	        if (columns !=null){
	          for(int i=0;i<columns.length;++i){
	            if(i>0)
	              buffer.append(",");
	            buffer.append(columns[i]);
	          }
	        }
	        else
	          buffer.append(" * ");

	        buffer.append(" FROM ");
	        buffer.append(resolveQualifiedTable(table));
	      
	        if (selectionRow!=null){
	          buffer.append(generateWhereClause(selectionRow));
	        }

	        if (sortColumns!=null){
	          buffer.append(" ORDER BY ");
	          for(int i=0;i<sortColumns.length;++i){
	            if (i>0)
	               buffer.append(",");
	            buffer.append(sortColumns[i]);
	            buffer.append(" ");
	            buffer.append(order);
	            buffer.append(" ");
	          }
	        }
	      
	        Connection connection=resolveConnection(table);
	        synchronized(connection){
	          Statement statement = connection.createStatement();
	          System.out.println(buffer.toString());
	          ResultSet resultSet = statement.executeQuery(buffer.toString());
	          
	          ResultSetMetaData rsmd=resultSet.getMetaData();
	          int columnCount = rsmd.getColumnCount();
	                 
	          List resultRows = new LinkedList();
	          while(resultSet.next()){
	            Row resultRow = new Row ();
	            for(int i=1;i<=columnCount;++i){
	             resultRow.addColumn(rsmd.getColumnName(i),resultSet.getObject(i));
	            }
	            resultRows.add(resultRow);
	          }
	          resultSet.close();
	          statement.close();
	          return resultRows;
	        } 
	      } 
	      catch(SQLException e){
	        throw new DataException("不能够读取表"+table,e);	
	      }
	}
    
//插入数据
	   public void insert(
			           String table,
			           List rows
					   ) throws DataException {
		try{
			
		    for(Iterator i=rows.iterator();i.hasNext();){
		       Row row=(Row) i.next();	
		       StringBuffer buffer=new StringBuffer();
		       buffer.append("INSERT INTO ");
		       buffer.append(resolveQualifiedTable(table));
		       
		       
		       
		       buffer.append("(");
		       boolean firstColumn=true;
		       for(Iterator j=row.columns();j.hasNext();){
		         if (!firstColumn)
		         	buffer.append(", ");
		         else
		         	firstColumn=false;
		            buffer.append(j.next());
		       }
		       
		       buffer.append(") VALUES (");
		       firstColumn=true;
		       		       
		       for(Iterator j=row.columns();j.hasNext();){
		           if (!firstColumn)
		           	 buffer.append(", ");
		           else
		           	 firstColumn=false;
		           
		           String column=(String)j.next();
		           Object columnValue=row.getColumnValue(column);
		           buffer.append(generateLiteralValue(columnValue));	          
		       }
		       
		       buffer.append(")");
		         
		       Connection connection=resolveConnection(table);
		       synchronized(connection){
		          Statement statement=connection.createStatement();
		          System.out.println(buffer.toString());
		          statement.executeUpdate(buffer.toString());
		          statement.close();
		       }
		    }	
		}
		catch(SQLException e){
			  throw new DataException("不能将数据够插入该表"+table,e);	
			}
	}
//修改数据
	   public void update(
			           String table, 
					   Row selectionRow, 
					   Row updateRow
					   ) throws DataException {
		try{
			
			
			StringBuffer buffer=new StringBuffer();
			
			buffer.append("UPDATE ");
			buffer.append(resolveQualifiedTable(table));	
			buffer.append(" SET ");
			
			
			
		    boolean firstColumn=true;
		    for(Iterator i=updateRow.columns();i.hasNext();){
		    	if (!firstColumn)
		    		buffer.append(", ");
		    	else
		    		firstColumn=false;
		    	String column=(String)i.next();
		    	buffer.append(column);
		    	buffer.append(" = ");
		    	Object columnValue=updateRow.getColumnValue(column);
		    	buffer.append(generateLiteralValue(columnValue));
		    }
		    
		    if (selectionRow != null){
		    	buffer.append(generateWhereClause(selectionRow));
		    }
		    
		    Connection connection = resolveConnection(table);
		    synchronized(connection){
		        Statement statement=connection.createStatement();
		        System.out.println(buffer.toString());
		        statement.executeUpdate(buffer.toString());
		        statement.close();
		    }
		}
		catch(SQLException e){
		  throw new DataException("不能修改该表"+table,e);
		}
	}
//删除数据
	   public void delete(
			           String table, 
					   Row selectionRow)throws DataException {
		try{
		  StringBuffer buffer=new StringBuffer();
		  buffer.append("DELETE FROM ");
		  buffer.append(resolveQualifiedTable(table));
		  
		  if (selectionRow!=null){
		  	buffer.append(generateWhereClause(selectionRow));
		  }
		  
		  Connection connection=resolveConnection(table);
		  synchronized(connection){
		    Statement statement=connection.createStatement();
		   // System.out.println(buffer.toString());
		    statement.executeUpdate(buffer.toString());
		    statement.close();
		  }
		}
		catch(SQLException e){
		  throw new DataException("不能删除该表数据"+table,e);	
		}

	}
//	
	   private Connection resolveConnection(String table){
		if (table.startsWith("A"))
			return accountingConnection;
		else 
		  if (table.startsWith("P"))
			return payrollConnection;
		  else 
		    return otherConnection;
	}
	
	   private String resolveQualifiedTable(String table) {
		if (table.startsWith("A"))
			return "ACCTDATA."+table;
	    else 
	      if (table.startsWith("P"))
	      	return "PAYROLL."+table;
	      else
	      	return table;
	}

	   private String generateLiteralValue(Object literalValue) {
		StringBuffer buffer=new StringBuffer();
		if(!(literalValue instanceof Number))
			buffer.append("'");
		buffer.append(literalValue);
		if (!(literalValue instanceof Number))
			buffer.append("'");
		return buffer.toString();
	}
	   
	   private String generateWhereClause(Row selectionRow) {
	    StringBuffer buffer=new StringBuffer();
	    buffer.append(" WHERE ");
	    boolean firstColumn=true;
	    for(Iterator i=selectionRow.columns();i.hasNext();){
	      if (!firstColumn)
	      	buffer.append(" AND ");
	      else
	      	firstColumn=false;
	    String column=(String)i.next();
	    buffer.append(column);
	    buffer.append(" = ");
	    Object columnValue=selectionRow.getColumnValue(column);
	    buffer.append(generateLiteralValue(columnValue));
	    }
	    return buffer.toString();
    }

}