学步园

数据访问对象DAO封装数据库访问细节，提供对数据库中表的粗粒度或者依次一条记录的

访问。值对象封装拉被访问的数据结构，有效地表示拉被访问的记录。
为了更好地模块化，用一个数据库工具类隔离低层数据库访问行为，使获取和更新数据业务

方法不需要负责数据库操作。
DAO的目的就是封装数据库访问和提供为业务对象屏蔽访问细节的方法。这种方法提供了

代码共享，因为各种业务对象之间共享了DAO，所以这种方法还可以本地化对数据访问代

码可能的必要修改。
为了优化性能，在调用查询方法的业务方法使用这些查询方法之前预处理数据库查询，保存

这些预处理的请求，在访问DAO的业务方法请求数据时使用他们。所有的DAO中都包含设

置值对象中DAO值的方法和返回值对象的方法。这样就提供拉一个简便、高效地使用DAO

和数据库交互的方式，使用一个方法设置或者获取DAO值简化了与使用DAO的业务方法之

间的接口，减少了对象间需要的通信。
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.sql.DataSource;
import javax.naming.NamingException;
import javax.naming.InitialContext;
import javax.naming.Context;
import java.io.PrintStream;
import java.io.IOException;

public class DBUtil {

// ** should read this from the jsp page or a properties file **
String dbURL =
          "jdbc:postgresql://localhost:5432/knowledgebase;user=puser;password=puser";

//String dbDriverName = Class.forName("com.informix.jdbc.IfxDriver");
String     dbDriverName = "org.postgresql.Driver";
Connection dbConnection = null;
ResultSet  dbResultSet  = null;
Statement  dbStatement  = null;

String dataSourceName;
InitialContext initContext;

javax.servlet.ServletContext context;

boolean driverLoaded       = false;
boolean Connected          = false;
boolean StatementCreated   = false;

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

// ------ Constructor -------
public DBUtil( ) {

try  {
    //
    // get the default dataSourceName
    //
    //initContext = new InitialContext();
    //dataSourceName = (String) initContext.lookup( "java:comp/env/dataSourceName" );

    getConnected();
}
catch (Exception e) {
      System.out.println("Can't connect to DB in DBUtil constructor. Exception caught:" + e.

getMessage() );
}

}

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

public DBUtil( String dataSourceName ) {

try {

   // set the default dataSourceName
   this.dataSourceName = dataSourceName;
   getConnected();
}
catch (Exception e) {
      System.out.println("Can't connect to DB in DBUtil constructor. Exception caught:" + e.

getMessage() );
}
}

// ---------------------------------------------------
public DBUtil( javax.servlet.ServletContext context ) {

  // save for logging
  this.context = context;

}

// ---------------------------------------------------
void loadDriver()  throws Exception {
     Class.forName( dbDriverName);
}
public void getConnected()  throws Exception {

try {
     //
     // tomcat 4.0.x seems to require this two step process
     //
     InitialContext initContext = new InitialContext( );

     Context context = (Context) initContext.lookup( "java:comp/env" );

     DataSource ds = (DataSource) context.lookup( "jdbc/knowledgebase" );
     dbConnection = ds.getConnection();

     Connected = true;
}
catch (SQLException e) {
     if ( context != null )
         context.log( "SQLException in DBUtil.getConnected: " +  e.getMessage() );
     else
         System.err.println( "SQLException in DBUtil.getConnected: " +  e.getMessage() );

     throw new Exception (  "NamingException in DBUtil.getConnected: " +  e.getMessage() );
}
catch (NamingException e) {
     if ( context != null )
         context.log( "NamingException in DBUtil.getConnected: "   + e.getMessage() );
     else
         System.err.println( "NamingException in DBUtil.getConnected: " +  e.getMessage() );

     throw new Exception (  "NamingException in DBUtil.getConnected: " + e.getMessage() );
}
catch (Exception e) {
     if ( context != null )
         context.log( "NamingException in DBUtil.getConnected: "  + e.getMessage() );
     else
         System.err.println( "NamingException in DBUtil.getConnected: " +  e.getMessage() );

     throw new Exception (  "NamingException in DBUtil.getConnected: "  + e.getMessage() );
}
}
// ----------------------------------------------------------------------

public void createDBStatement() throws Exception {

try {
     if ( !Connected )
         getConnected();

     dbStatement  = dbConnection.createStatement();
}

catch (SQLException e) {
     if ( context != null )
         context.log( "SQLException in DBUtil.getConnected: "  + e.getMessage() );
     else
         System.err.println( "SQLException in DBUtil.getConnected: " +  e.getMessage() );

     throw new Exception (  "NamingException in DBUtil.getConnected: " +   e.getMessage() );
}
catch (NamingException e) {
     if ( context != null )
         context.log( "NamingException in DBUtil.getConnected: "  + e.getMessage() );
     else
         System.err.println( "NamingException in DBUtil.getConnected: " +  e.getMessage() );

     throw new Exception (  "NamingException in DBUtil.getConnected: " +  e.getMessage() );
}
catch (Exception e) {
     if ( context != null )
         context.log( "NamingException in DBUtil.getConnected: "  + e.getMessage() );
     else
         System.err.println( "NamingException in DBUtil.getConnected: " +  e.getMessage() );

     throw new Exception (  "NamingException in DBUtil.getConnected: " +  e.getMessage() );
}}
public PreparedStatement createPreparedStatement( String stmt ) throws Exception {

try {
     if ( !Connected )
         getConnected();
     return dbConnection.prepareStatement( stmt );
}

catch (SQLException e) {
     if ( context != null )
         context.log( "SQLException in DBUtil.getConnected: " + e.getMessage() );
     else
         System.err.println( "SQLException in DBUtil.getConnected: " + e.getMessage() );

     throw new Exception (  "NamingException in DBUtil.getConnected: " + e.getMessage() );
}
catch (NamingException e) {
     if ( context != null )
         context.log( "NamingException in DBUtil.getConnected: "  + e.getMessage() );
     else
         System.err.println( "NamingException in DBUtil.getConnected: " +  e.getMessage() );

     throw new Exception (  "NamingException in DBUtil.getConnected: "   + e.getMessage() );
}
catch (Exception e) {
     if ( context != null )
         context.log( "NamingException in DBUtil.getConnected: "  + e.getMessage() );
     else
         System.err.println( "NamingException in DBUtil.getConnected: " +   e.getMessage() );

     throw new Exception (  "NamingException in DBUtil.getConnected: " + e.getMessage() );

}
}
public ResultSet executePreparedStatement( PreparedStatement pstmt ) throws Exception {

     if ( !Connected )
         getConnected();
     return pstmt.executeQuery();
}
public int executePreparedStmtUpd( PreparedStatement pstmt )  throws Exception{

    if ( !Connected )
        getConnected();
    return pstmt.executeUpdate();

}
public ResultSet executeDBQuery( String query ) throws Exception {
    // make sure we are ready to do this
     if ( !Connected ) 
  getConnected();
   
     if ( !StatementCreated ) {
   createDBStatement();
   StatementCreated = true;
     }
     // execute query
     dbResultSet   = dbStatement.executeQuery( query );

     return dbResultSet;
}
public int executeUpdDBQuery( String query ) throws Exception {

    // make sure we are ready to do this
     if ( !Connected ) 
  getConnected();
   
     if ( !StatementCreated ) {
   createDBStatement();
   StatementCreated = true;
     }
     // execute query
     int retVal   = dbStatement.executeUpdate( query );

      // return number of rows updated
      return retVal;
}
// Bean methods
public ResultSet getdbResultSet() {
 return dbResultSet;
}
public String getdbDriverName() {
 return dbDriverName;
}
public Connection getdbConnection() {
 return dbConnection;
}
public String getDbURL() {
 return dbURL;
}
public Statement getdbStatement() {
 return dbStatement;
}
public void setdbDriverName ( String dbDN ) {
 this.dbDriverName = dbDN;
 driverLoaded = false; // force new driver load
}
public void setdbURL ( String dbURL ) {
 this.dbURL = dbURL;
 Connected = false; // force new connection
}
public void setOutputStream( PrintStream Out ) {
 //this.out = Out;
}
} 

import java.util.Vector;
import java.util.Collection;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import db.DBUtil;

public class CategoriesDAO {
// private members
private String category;
private String description;

// hold query strings
private String insertStmtStr;
private String updateStmtStr;
private String deleteStmtStr;
private String selectStmtStr;
private String selectCategoryListStr;

// hold prepared statements
private PreparedStatement insertStmt;
private PreparedStatement updateStmt;
private PreparedStatement deleteStmt;
private PreparedStatement selectStmt;
private PreparedStatement selectCategoryListStmt;

DBUtil dbutil;

// getXXXX methods
public String getCategory() {
   return this.category;
}

public String getDescription() {
    return this.description;
}

// setXXXX methods
public void setCategory( String category ) {
   this.category = category;
}

public void setDescription( String description ) {
   this.description = description;
}

// convenience methods
public void createPreparedStatements() {
try {

insertStmtStr = "insert into categories( category, description) values (?,?)";
insertStmt = dbutil.createPreparedStatement( insertStmtStr );   
updateStmtStr = "update categories set description = ? where category = ? ";
updateStmt = dbutil.createPreparedStatement( updateStmtStr );
deleteStmtStr = "delete from categories where category = ?";
deleteStmt = dbutil.createPreparedStatement( deleteStmtStr );
selectStmtStr = "select category, description from categories where category = ?";
selectStmt = dbutil.createPreparedStatement( selectStmtStr );
selectCategoryListStr = "select category, description from categories";
selectCategoryListStmt = dbutil.createPreparedStatement( selectCategoryListStr );

}
catch( Exception e) {

  System.out.println("Exception in categoriesDAO.CreatePreparedStatement(): " + e.

getMessage() );}
}

public void updateDAO() throws SQLException {

    updateStmt.setString(1, getDescription() );
    updateStmt.setString(2, getCategory() );
    updateStmt.executeUpdate();

}

public void insertDAO() throws SQLException {
     insertStmt.setString(1, getCategory() );
     insertStmt.setString(2, getDescription() );

     insertStmt.executeUpdate();}

public void deleteDAO() throws SQLException {
      deleteStmt.setString(1, getCategory() );
      deleteStmt.executeUpdate();

}
public void loadDAO( String category )  throws SQLException {

 selectStmt.setString(1, category );

 ResultSet rs = selectStmt.executeQuery();

if ( rs.next() ) {
 setCategory( rs.getString(1) );
 setDescription( rs.getString(2) );
}
}

// load values from ValueObject
public void loadDAO( CategoriesVO vo ) {

setCategory( vo.getCategory() );
setDescription( vo.getDescription() );

}

// set ValueObject with internal member values
public void setVO( CategoriesVO vo ) {

vo.setCategory( this.getCategory() );
vo.setDescription( this.getDescription() );
}

public Collection getCategoryList() throws SQLException {
Vector v = new Vector();
CategoriesVO vo = null;

ResultSet rs = selectCategoryListStmt.executeQuery();

while ( rs.next() ) {
      vo = new CategoriesVO();
      vo.setCategory( rs.getString(1) );
      vo.setDescription( rs.getString(2) );
      v.add( vo );
}
return v;
}

// constructor
public CategoriesDAO() {
     // create db wrapper
     dbutil = new DBUtil();
     // create SQL prepared statements
     createPreparedStatements();
     }
}