genericfactorybase.java

OR Mapping工具

     */

	protected Vector queryAllSorted (DatabaseSession dbSess, SortCriteria sortBy) throws SQLException {
		return querySorted (dbSess, null, sortBy);
	}

	/** A routine to get a objects from a table using the filter and sorted w/ the criteria
     * @param dbSess the current merlin session
     * @param sortBy the sorting criteria
	 * @param query the filter to search with
     *
     * @throws SQLException if a db error occurs
     * @return Vector of results
     */
   	public Vector querySorted (DatabaseSession dbSess, QueryFilter query, SortCriteria sortBy) throws SQLException {
       	Vector result = null;
		String sql = getSelectSql ();//"select * from " + getTableName ();
		QueryResult qr = getResults (dbSess, sql, query, sortBy);
		ResultSet rs = qr.rs;
		result = getCollection (dbSess, rs);
		qr.close ();
		return result;
	}

    /** A routine to get a single object given a filter
     * @param dbSess the current merlin session
     * @param query one which should find 0-1 objects
     *
     * @throws SQLException if a db error occurs or more than 1 object is found on this
     * filter
     * @return Identified
     */

    public Identified getObject (DatabaseSession dbSess, QueryFilter query)
          throws SQLException {

        Identified result = null;
		String sql = getSelectSql ();
		QueryResult qr = getResults (dbSess, sql, query, null);
		ResultSet rs = qr.rs;
        if (rs.next()) {
            result = makeObject (dbSess, rs);
        }
        if (rs.next()) { // extra hit found throw EX
            qr.close();
            throw new AbraSQLException ("ephman.abra.database.morethanone", this.getClass().getName());
        }
		qr.close ();
		return result;
    }


	protected Identified makeObject (DatabaseSession dbSess, ResultSet rs) throws SQLException {
		int oid = rs.getInt(getPrimaryColumn());
		Identified result = dbSess.getItem (this, oid);
		if (result == null) { // retrieve
			result = makeFromResultSet (rs);
			dbSess.putItem(this, result);
			if (dbSess.isLockingMode ()) // fresh copy so is locked..
				result.setLocked ();
			deepRetrieval (dbSess, result);

		} else if (dbSess.isLockingMode ()) { // not null but locking mode
			// check for refresh
			if (!result.isLocked ()) // not locked yet within transaction so check for a refresh
				checkRefresh (dbSess, result, rs);
		}
		return result;
	}

    // Here so that we can override it for query based factories
    protected boolean defaultNeedsWhereLogic () {
        return true;
    }

    protected boolean needsAndBeforeFilter () {
        return false;
    }

	/** get results and set key in first arg.. */
	protected QueryResult getResults (DatabaseSession dbSess, String sql, int key)
		throws SQLException {
		return getResults (dbSess, sql, new PreparedFilter ("", key), null);
	}

	/** a helper routine to build a query and execute */
	protected QueryResult getResults (DatabaseSession dbSess, String sql,
									  QueryFilter filter, SortCriteria sc) throws SQLException {
		return getResults (dbSess, sql, filter, sc, defaultNeedsWhereLogic(), null);
	}

	protected QueryResult getResults (DatabaseSession dbSess, String sql,
									  QueryFilter filter, SortCriteria sc,
									  boolean needsWhereLogic, String tableName)
		throws SQLException {

		Connection conn = ((JDBCDatabaseSession)dbSess).getJdbcConnection ();

		PreparedQuery pq = null;
		if (filter != null) {
			if (tableName == null)
				pq = new PreparedQuery (filter);
			else
				pq = new PreparedQuery (filter, tableName);
			String qs = pq.getSqlString ();
			if (qs.length () > 0) {
				if (!endDates && needsWhereLogic)
					sql += " where ";
                if (needsAndBeforeFilter())
                    sql += " and ";
				sql += qs;
			}
		}
		if (sc != null)
			sql += sc.toString ();
		/* new PMB 6/10/03 -- this allows the pq to deal with wrapper queries like Oracle Limit */
		if (pq != null)
			sql = pq.getWrappedString (sql);

		PreparedStatement stmt = conn.prepareStatement (sql);

		QueryTracer.trace (this, sql, pq);
		if (pq != null)
			pq.setArgs (stmt);

		((JDBCDatabaseSession)dbSess).setCurrentStatement(stmt);
		ResultSet rs = stmt.executeQuery ();
		((JDBCDatabaseSession)dbSess).setCurrentStatement(null);
		return new QueryResult (rs, stmt);
	}


   /** A routine to count the number of rows in a query
     * which returns the count of hits
     *
     * @param dbSess the current merlin session
     * @param filter a QueryFilter
     */

    public int countRows (DatabaseSession dbSess, QueryFilter filter)
          throws SQLException {
		int result = 0;
		String sql = getSelectCountSql();
		QueryResult qr = getResults (dbSess, sql, filter, null);
		ResultSet rs = qr.rs;
		if (rs.next ()) {
			result = rs.getInt (1);
		}
		qr.close (); // close rs and stmt
		return result;
    }
    /** A routine to perform a search given an ordering
     * which returns the Vector of oid hits in a DatabaseCursor object
     * this allows small range retrievals with any type of data abstraction
     *
     * @param dbSess the current merlin session
     * @param sortBy a list of columns on which to sort the data
     */

    public DatabaseCursor cursorQuery (DatabaseSession dbSess, SortCriteria sortBy)
          throws SQLException {
        return cursorQuery (dbSess, null, sortBy);
    }
    /** A routine to perform a search given a filter
     * which returns the Vector of oid hits in a DatabaseCursor object
     * this allows small range retrievals with any type of data abstraction
     *
     * @param dbSess the current merlin session
     * @param query a query filter with any lookup specific criteria
     */

    public DatabaseCursor cursorQuery (DatabaseSession dbSess, QueryFilter query)
          throws SQLException {
        return cursorQuery (dbSess, query, null);
    }

    /** A routine to perform a search given a filter and an ordering
     * which returns the Vector of oid hits in a DatabaseCursor object
     * this allows small range retrievals with any type of data abstraction
     *
     * @param dbSess the current merlin session
     * @param query a query filter with any lookup specific criteria
     * @param sortBy a list of columns on which to sort the data
     */

    public DatabaseCursor cursorQuery (DatabaseSession dbSess, QueryFilter query, SortCriteria sortBy) throws SQLException {
        //String sql = "select " + getPrimaryColumn() + " from " + getTableName();
        String sql = getCursorQuerySql();
		QueryResult qr = getResults (dbSess, sql, query, sortBy);
		ResultSet rs = qr.rs;

        Vector hits = new Vector ();
        int hit_count = 0;
        while (rs.next()) {
            hits.addElement (new Integer (rs.getInt(getPrimaryColumn ())));
            hit_count++;
        }
        // now cleanup
		qr.close ();
        return new DatabaseCursor (this, hits, query, sortBy, hit_count);
    }

    /**
     * Raw query returns DatabaseCursor - to be used in descendant classes
     *
     * @param dbSess
     * @param sql
     * @return
     * @throws SQLException
     */
    protected DatabaseCursor cursorRawQuery (DatabaseSession dbSess, String sql) throws SQLException {
        QueryResult qr = getResults (dbSess, sql, null, null);
        ResultSet rs = qr.rs;
        Vector hits = new Vector ();
        int hit_count = 0;
        while (rs.next()) {
            hits.addElement (new Integer (rs.getInt(getPrimaryColumn ())));
            hit_count++;
        }
        // now cleanup
		qr.close ();
        return new DatabaseCursor (this, hits, null, null, hit_count);
    }


	/**
	 *  Execute a stored proc with some arguments that returns an integer.
	 */
	protected int storedProcCall (DatabaseSession dbSess, String procCall, Vector args) throws SQLException {
		int result = 0;
		Connection conn = getConnection (dbSess);
		CallableStatement cstmt = conn.prepareCall (procCall);
		cstmt.registerOutParameter (1, Types.INTEGER);
		if (args != null) {
			for (int i = 0; i < args.size (); i++) {
				/* Arguments are counted from 1 and the first is the return value */
				cstmt.setObject (i+2, args.elementAt(i));
			}
		}
		((JDBCDatabaseSession)dbSess).setCurrentStatement(cstmt);
		cstmt.execute ();
		((JDBCDatabaseSession)dbSess).setCurrentStatement(null);
		result = cstmt.getInt (1);
		cstmt.close ();
		return result;
	}

    protected DatabaseCursor cursorStoredProcCall (DatabaseSession dbSess,
                                                   String procCall,
                                                   Vector args,
                                                   SortCriteria sort)
            throws SQLException
    {
        DatabaseCursor cursor = null;
        Connection conn = getConnection (dbSess);
        PreparedStatement cstmt = conn.prepareCall(procCall);
        if (args != null) {
            for (int i = 0; i < args.size(); i++) {
                cstmt.setObject(i+1, args.elementAt(i));
            }
        }
    	((JDBCDatabaseSession)dbSess).setCurrentStatement(cstmt);
        ResultSet rs = cstmt.executeQuery();
    	((JDBCDatabaseSession)dbSess).setCurrentStatement(null);
        if (rs.next()) {
            Vector hits = new Vector ();
            int hit_count = 0;
             while (rs.next()) {
                hits.addElement (new Integer (rs.getInt(getPrimaryColumn ())));
                hit_count++;
            }
            cursor = new DatabaseCursor (this, hits, null, null, hit_count);
            cursor.originalSort = sort;
        }
        return cursor;
    }

	// code for refreshing data..
	protected void checkRefresh (DatabaseSession dbSess, Identified item) throws SQLException {
		// check refresh with no rs..  need to make rs..
		if (item instanceof Versioned && !item.isLocked ()) { // versioned object..
			String sql = getSelectSql(getPrimaryColumn ());
			QueryResult qr = getResults (dbSess, sql, item.getOid ());
			ResultSet rs = qr.rs;
			if (rs.next ()) {
				checkRefresh (dbSess, item, rs);
			}
			qr.close ();
		}
	}
	/** routine to check if an object is up to date..
	 * @param dbSess this threads connection
	 * @param item the dbObject in question
	 * @param rs an open ResultSet represneting this object in the db
	 * @throws SQLException on error.
	 */
	protected void checkRefresh (DatabaseSession dbSess, Identified item, ResultSet rs) throws SQLException {
		if (item instanceof Versioned) { // versioned object..
			Versioned v = (Versioned)item;
			if (v.getVersion () != rs.getInt (VERSION_NUMBER) || v.getVersion () == Versioned.INVALID) {
				// out of date..
				refreshFromResultSet (v, rs);
			}
			if (!v.isLocked ()) { // set locked and do deep retrieval..
				v.setLocked ();
				deepRetrieval (dbSess, v); // do an update on all the sub pieces..
			}
		}
	}
// code for clob storage..

    // internal function called by putNew and update to wrap rs call
    private void updateClobs (DatabaseSession dbSess, Identified item) throws SQLException {
        Connection conn = this.getConnection(dbSess);
        String sql = "select * from " + getTableName () + " where (" + getPrimaryColumn() + "=?) for update";
		QueryResult qr = getResults (dbSess, sql, item.getOid());
		ResultSet rs = qr.rs;
        if (!rs.next()) {
			qr.close ();
            throw new AbraSQLException ("ephman.abra.database.noclob", this.getTableName());
		}
        setClobs (dbSess, rs, item);
		qr.close();
    }

	protected int getLastId (DatabaseSession dbSess, PreparedStatement stmt) throws SQLException {
		if (stmt instanceof CallableStatement) {
			int result = ((CallableStatement)stmt).getInt (1); // return val
			//stmt.close ();
			return result;
		} else { // just dynamic
			//stmt.close ();
			return getLastId (dbSess);
		}
	}
    // override if you need to set clobs

    protected void setClobs (DatabaseSession dbSess, ResultSet rs, Identified item) throws SQLException {
    }

    /**  sub factory overrides returning true or false
    */
    protected abstract boolean hasClobs ();


	/** need specific db type libraries to do see oracle/FactoryBase */
	protected abstract void setClob (DatabaseSession dbSess, ResultSet rs, String columnName, String value) throws SQLException;

	/* Get the OID of the last object created */
	protected abstract int getLastId (DatabaseSession dbSess) throws SQLException;

	protected String getSelectSql(String columnName){

		return " select * from " + getTableName() + " where " + columnName + "=?";
	}

	protected String getSelectSql() {
		return "select * from " + getTableName() ;
	}

	protected String getCursorQuerySql () {
		return "select " + this.getPrimaryColumn() + " from " + getTableName();
	}

	protected String getSelectCountSql() {
		return "select count(*) from " + getTableName() ;
	}

    protected String getDeleteSql (QueryFilter filter) {
        return "delete from " + this.getTableName() + " where " + filter.toString();
    }
	public static final String VERSION_NUMBER = "version_number";


	protected String getBooleanAsString (Boolean b) {
		return b == null ? " " : (b.booleanValue () ? "T" : "F");
	}

	protected Boolean getStringAsBoolean (String s) {
		return s != null && s.equals ("T") ? Boolean.TRUE :
			s != null && s.equals ("F") ?Boolean.FALSE : null;
	}

	protected boolean endDates = false;

	void trace (String text) { QueryTracer.trace(this, text); }

	class QueryResult {
		ResultSet rs;
		Statement stmt;
		QueryResult (ResultSet rs, Statement stmt) {
			this.rs = rs; this.stmt = stmt;
		}

		void close () throws SQLException {
			rs.close ();
			stmt.close ();
			trace("finished");
		}
	}
}