sql.java

这是一个基于计算网格的web service。它用java编写。一旦安装完成

    throws SQLException, Exception
    {
        Statement stmt = null; 
        ResultSet rs = null;
        String sqlstr = null; 
        
        try 
        {
            stmt = conn.createStatement();
            sqlstr = "SELECT * FROM " + TBL_DYN_INFO;
            
            if ( !stmt.execute( sqlstr ) ) 
            {
                throw new SQLException( 
                        "SELECT * failed on table '" + TBL_DYN_INFO + "'" );
            }
            
            rs = stmt.getResultSet();
            
            if ( rs.next() )
            {
                sqlstr = 
                        "UPDATE " + TBL_DYN_INFO + " SET " +
                        DYNINFO_COL_INFO + " = '" + xmlDoc + "'" +
                        " WHERE " + 
                        CFG_COL_ID + " = " + rs.getInt( OUTPUT_COL_ID );                                 
            }
            
            else
            {
                sqlstr = 
                        "INSERT INTO " + TBL_DYN_INFO + " ( " +
                        DYNINFO_COL_INFO +
                        " ) VALUES ( " +
                        "'" + xmlDoc + "'" + 
                        " )";  
            }
            
            stmt.execute( sqlstr );
            
        } catch ( SQLException sqle ) {
            throw sqle;
            
        } catch ( Exception ex ) {
            throw ex;
            
        } finally {
            try 
            {
                if( rs != null ) {
                    rs.close();
                    rs = null;
                }
                
            } catch ( Exception ex ) {}
                
            try 
            {
                if( stmt != null ) {
                    stmt.close();
                    stmt = null;
                } 
                
            } catch ( Exception ex ) {}            
        }         
    }
    
    public static ConfigResult getConfig( Connection conn ) 
    throws SQLException, Exception
    {
        ConfigResult result = null;
        Statement stmt = null; 
        ResultSet rs = null;
        String sqlstr = null; 
        
        try 
        {
            stmt = conn.createStatement();
            sqlstr = "SELECT * FROM " + TBL_CFG;
            
            if( !stmt.execute( sqlstr ) ) 
            {
                throw new SQLException( 
                        "SELECT * failed on table '" + TBL_CFG + "'" );
            }
            
            rs = stmt.getResultSet();
            
            if ( rs.next() ) 
            {
                result = new ConfigResult(
                        rs.getString( CFG_COL_WSADDR_JOBHOST ),
                        rs.getString( CFG_COL_CLUSTERID ),
                        rs.getString( CFG_COL_WSADDR_RESMGR ),
                        rs.getInt( CFG_COL_JOBHIST_MAXHRS ) );                
            }  
            
        } catch ( SQLException sqle ) {
            throw sqle;
            
        } catch ( Exception ex ) {
            throw ex;
            
        } finally {
            try 
            {
                if( rs != null ) 
                {
                    rs.close();
                    rs = null;
                }
                
            } catch ( Exception e ) {}
                
            try 
            {
                if( stmt != null ) 
                {
                    stmt.close();
                    stmt = null;
                }  
                
            } catch ( Exception e ) {}            
        }  
        
        return result;
    }
    
    public static void setConfig( 
            Connection conn,
            String jobHostWsAddr,
            String clusterId,
            String resMgrWsAddr,
            int jobHistMaxHrs ) 
    throws SQLException, Exception 
    {
        Statement stmt = null; 
        ResultSet rs = null;
        String sqlstr = null; 
        int rowCount = 0;
        int id = 0;
        
        try 
        {
            stmt = conn.createStatement();
            sqlstr = "SELECT * FROM " + TBL_CFG;
            
            if ( !stmt.execute( sqlstr ) ) 
            {
                throw new SQLException( 
                        "SELECT * failed on table '" + TBL_CFG + "'" );
            }
            
            rs = stmt.getResultSet();

            while ( rs.next() ) 
            {
                id = rs.getInt( CFG_COL_ID );
                rowCount++;
            }            
            
            if ( rowCount == 0 ) 
            {
                sqlstr = 
                        "INSERT INTO " + TBL_CFG + " ( " +
                        CFG_COL_WSADDR_JOBHOST  + "," +
                        CFG_COL_CLUSTERID       + "," +
                        CFG_COL_WSADDR_RESMGR   + "," +
                        CFG_COL_JOBHIST_MAXHRS  +
                        " ) VALUES ( " +
                        "'" + jobHostWsAddr + "'" +     "," +
                        "'" + clusterId + "'" +     "," +
                        "'" + resMgrWsAddr + "'" +      "," +
                        jobHistMaxHrs +
                        " )"; 
            } 
            
            else 
            {
                sqlstr = 
                        "UPDATE " + TBL_CFG + " SET " +
                        CFG_COL_WSADDR_JOBHOST + " = '" + jobHostWsAddr + "'"   + "," +
                        CFG_COL_CLUSTERID + " = '" + clusterId + "'"            + "," +
                        CFG_COL_WSADDR_RESMGR + " = '" + resMgrWsAddr + "'"     + "," +
                        CFG_COL_JOBHIST_MAXHRS + " = " + jobHistMaxHrs + 
                        " WHERE " + CFG_COL_ID + " = " + id;                
            }
            
            stmt.execute( sqlstr );
            
        } catch ( SQLException sqle ) {
            throw sqle;
            
        } catch ( Exception ex ) {
            throw ex;
            
        } finally {
            try 
            {
                if( rs != null ) {
                    rs.close();
                    rs = null;
                }
                
            } catch ( Exception ex ) {}
                
            try 
            {
                if( stmt != null ) {
                    stmt.close();
                    stmt = null;
                } 
                
            } catch ( Exception ex ) {}            
        } 
    }
    
    public static void moveJobToHistoryTable( Connection conn, String jobId )
        throws SQLException, Exception 
    {
        Statement stmt = null; 
        String sqlstr = null; 
        ResultSet rs = null;

        try
        {
            stmt = conn.createStatement();
            
            sqlstr = 
                "SELECT * FROM " + TBL_JOBS_CURR + " WHERE " +
                JOB_COL_JOBID + " = " + "'" + jobId + "'";
            
            if ( stmt.execute( sqlstr ) ) 
            {
                rs = stmt.getResultSet();        
                
                if ( rs.next() ) 
                {
                    sqlstr = 
                            "INSERT INTO " + TBL_JOBS_HIST +
                            " ( " +
                            JOB_COL_OWNERID +       "," +
                            JOB_COL_OWNEREMAIL +    "," +
                            JOB_COL_JOBID +         "," +
                            JOB_COL_JOBSTATUS +     "," +
                            JOB_COL_JOBDESCR +      "," +
                            JOB_COL_JOBINDEX +      "," +
                            JOB_COL_EXITVAL +    "," +
                            JOB_COL_EXITMSG +       "," +
                            JOB_COL_TS_SUBMIT +     "," +
                            JOB_COL_TS_START +    "," +
                            JOB_COL_TS_COMPLETE + 
                            " ) VALUES ( " +
                            "'" + rs.getString( JOB_COL_OWNERID ) + "'"     + "," +
                            "'" + rs.getString( JOB_COL_OWNEREMAIL ) + "'"  + "," +
                            "'" + rs.getString( JOB_COL_JOBID ) + "'"       + "," +
                            rs.getInt( JOB_COL_JOBSTATUS )                  + "," +
                            "'" + rs.getString( JOB_COL_JOBDESCR )  + "'"   + "," +
                            rs.getInt( JOB_COL_JOBINDEX )                   + "," +
                            rs.getInt( JOB_COL_EXITVAL )                    + "," +
                            "'" + rs.getString( JOB_COL_EXITMSG ) + "'"     + "," +
                            "'" + rs.getString( JOB_COL_TS_SUBMIT ) + "'"   + "," +
                            "'" + rs.getString( JOB_COL_TS_START ) + "'"    + "," +
                            "'" + rs.getString( JOB_COL_TS_COMPLETE ) + "'" +
                            " ) ";

                    stmt.execute( sqlstr );
                    
                    sqlstr = 
                            "DELETE FROM " + TBL_JOBS_CURR + " WHERE " +
                            JOB_COL_JOBID + " = " + "'" + jobId + "'";
                    
                    stmt.execute( sqlstr ); 
                } 
                
                else 
                {
                    throw new SQLException( 
                            "a job with id '" + jobId + "' was not found" );
                } 
            } 
            
            else 
            {
                throw new SQLException( 
                        "SQL SELECT failed on table " + 
                        "'" + TBL_JOBS_CURR + "'" );                
            }
             
        } catch ( SQLException sqle ) {
            throw sqle;
            
        } catch ( Exception e ) {
            throw e;
            
        } finally {
            try 
            {
                if( rs != null ) 
                {
                    rs.close();
                    rs = null;
                }
                
            } catch ( Exception e ) {}            
            
            try 
            {
                if( stmt != null ) 
                {
                    stmt.close();
                    stmt = null;
                }  
                
            } catch ( Exception e ) {}            
        }         
    }
    
    public static void setJobExitValue( 
            Connection conn, 
            String jobId, 
            int value )
            throws SQLException, Exception 
    {
        Statement stmt = null; 
        String sqlstr = null; 
        ResultSet rs = null;
        Timestamp started = null;

        try {
            stmt = conn.createStatement();
            
            sqlstr = 
                "SELECT * FROM " + TBL_JOBS_CURR + " WHERE " +
                JOB_COL_JOBID + " = " + "'" + jobId + "'";
            
            if ( stmt.execute( sqlstr ) ) {
                rs = stmt.getResultSet();
                
                if ( rs.next() ) {
                    sqlstr = 
                            "UPDATE " + TBL_JOBS_CURR + " SET " +
                            JOB_COL_EXITVAL + " = " + value + 
                            " WHERE " + JOB_COL_JOBID + " = " +
                            "'" + jobId + "'";

                    stmt.execute( sqlstr );

                    if ( stmt.getUpdateCount() == 0 ) {
                        throw new SQLException( 
                                "SQL UPDATE failed on table " +
                                "'" + TBL_JOBS_CURR + "'" );  
                    }   
                    
                } else {
                    throw new SQLException( 
                            "a job with id '" + jobId + "' was not found" );
                } 
                
            } else {
                throw new SQLException( 
                        "SQL SELECT failed on table " + 
                        "'" + TBL_JOBS_CURR + "'" );                
            }