sql.java

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

            stmt = conn.createStatement();
            
            sqlstr = "SELECT * FROM " + TBL_JOBS_HIST;
            
            if( stmt.execute( sqlstr ) ) {
                rs = stmt.getResultSet();
                
                while ( rs.next() ) {
                     list.add( new JobResult( 
                            rs.getString( JOB_COL_OWNERID ),
                            rs.getString( JOB_COL_OWNEREMAIL ),
                            rs.getString( JOB_COL_JOBID ),
                            rs.getString( JOB_COL_JOBDESCR ),
                            rs.getInt( JOB_COL_JOBINDEX ),
                            rs.getInt( JOB_COL_JOBSTATUS ),
                            rs.getInt( JOB_COL_STARTJOB ),
                            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 ) ) );     
                }                
                
            } 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 ) {}            
        }

        return list;
    } 
    
    public static void setCurrJobStart( Connection conn, String jobId )
    throws SQLException, Exception {
        Statement stmt = null; 
        ResultSet rs = null; 
        String sqlstr = 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_STARTJOB + " = " + START_JOB_FLAG +
                            " 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 + "'" );
            }
            
        } 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 updateCurrJobStatus( 
            Connection conn, 
            String jobId,
            int status ) 
            throws SQLException, Exception {
        Statement stmt = null; 
        ResultSet rs = null; 
        String sqlstr = 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_JOBSTATUS + " = " + status +
                            " 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 + "'" );
            }
            
        } 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 List<String> getJobOutputFiles( Connection conn, String jobId ) 
    throws SQLException, Exception {
        Statement stmt = null; 
        ResultSet rs = null;
        String sqlstr = null; 
        Vector<String> list = new Vector();
        
        try {
            stmt = conn.createStatement();
            
            sqlstr = 
                "SELECT * FROM " + TBL_JOBS_OUTPUT + " WHERE " +
                OUTPUT_COL_JOBID + " = '" + jobId + "'"; 
            
            if ( stmt.execute( sqlstr ) ) {
                rs = stmt.getResultSet();
                
                while ( rs.next() ) {
                    list.add( rs.getString( OUTPUT_COL_FILEPATH ) );  
                }                
            }
            
            else {
                throw new SQLException( 
                        "SQL SELECT failed on table '" + TBL_JOBS_OUTPUT + "'" );                
            }
            
        } 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 ) {}            
        }  
        
        return list;
    }  
    
    public static void addJobOuputFile( 
            Connection conn,
            String jobId,
            String relativeFilePath ) throws SQLException, Exception {
        Statement stmt = null; 
        ResultSet rs = null;
        String sqlstr = null; 
        
        try {
            stmt = conn.createStatement();
            
            sqlstr = 
                "INSERT INTO " + TBL_JOBS_OUTPUT + " ( " +
                OUTPUT_COL_JOBID + "," +
                OUTPUT_COL_FILEPATH +
                " ) VALUES ( " +
                "'" + jobId + "'" + ", " +
                "'" + relativeFilePath + "'" +
                " )";
            
            stmt.execute( sqlstr );
            
        } 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 removeJobOuputFiles( Connection conn, String jobId ) 
    throws SQLException, Exception 
    {
        Statement stmt = null;      
        try 
        {
            stmt = conn.createStatement();
            
            String sqlstr = 
                    "DELETE FROM " + TBL_JOBS_OUTPUT + " WHERE " +
                    OUTPUT_COL_JOBID + " = " + "'" + jobId  + "'";

            stmt.execute( sqlstr );
            
        } catch ( SQLException sqle ) {
            throw sqle;
            
        } catch ( Exception e ) {
            throw e;
            
        } finally {
            try {
                if( stmt != null ) {
                    stmt.close();
                    stmt = null;
                }                              
            } catch ( Exception e ) {}            
        } 
    } 
    
    public static void deleteAllData( Connection conn ) 
    throws SQLException, Exception 
    {
        Statement stmt = null;        
        try 
        {
            stmt = conn.createStatement();

            stmt.execute( "DELETE FROM " + TBL_JOBS_CURR );
            stmt.execute( "DELETE FROM " + TBL_JOBS_HIST );
            stmt.execute( "DELETE FROM " + TBL_JOBS_OUTPUT );
            stmt.execute( "DELETE FROM " + TBL_DYN_INFO );
            
        } catch ( SQLException sqle ) {
            throw sqle;
            
        } catch ( Exception e ) {
            throw e;
            
        } finally { 
            try {
                if( stmt != null ) {
                    stmt.close();
                    stmt = null;
                }                              
            } catch ( Exception e ) {}            
        } 
    } 
    
    /*public static void main (String[] args ) {
        Connection conn = null;
        
        try {
            loadDriver();
            setConnectionParams("localhost", "3306", "test" );
            conn = Sql.getConnection();
            
            
        } catch ( Exception e ) {
            System.out.println( e.getMessage() );
        } finally {
            try {
                if( conn != null ) {
                    conn.close();
                    conn = null;
                }                              
            } catch ( Exception e ) {}               
        }
    }*/
}