sql.java

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

/*
 * java
 *
 * Created on April 10, 2005, 12:32 PM
 */

package jwsgrid.jobhost.priv;

import jwsgrid.jobhost.*;

import java.sql.*;

import java.util.Vector;
import java.util.List;

/**
 *
 * @author sean
 */
public class Sql 
{   
    ////////////////////////////////////////////////////////////////////////////
    // public attributes
    //    
    
    public static final String DB_SCHEMA = "gridjobhost";
    public static final String DB_USER = "gridjobhost";
    
    public final static String LOG_MAXENTRIES_STR = "100";
    public final static int LOG_MAXENTRIES = 100;    
            
    public static final String TBL_CFG = "config";  
    public static final String TBL_JOBS_CURR = "jobs_current";
    public static final String TBL_JOBS_HIST = "jobs_history";
    public static final String TBL_JOBS_OUTPUT = "jobs_output";
    public static final String TBL_DYN_INFO = "dynamic_info";
    
    ////////////////////////////////////////////////////////////////////////////
    // public classes
    //      
    
    public static class ConfigResult 
    {       
        private String jobHostWsAddr = null;
        private String clusterId = null;
        private String resMgrWsAddr = null;
        private int jobHistMaxHrs = 0;
        
        public ConfigResult( 
                String jobHostWsAddr, 
                String clusterId,
                String resMgrWsAddr,
                int jobHistMaxHrs ) 
        {
            this.jobHostWsAddr = jobHostWsAddr;
            this.clusterId = clusterId;
            this.resMgrWsAddr = resMgrWsAddr;
            this.jobHistMaxHrs = jobHistMaxHrs;
        }
        
        public String getJobHostWsAddr() { return jobHostWsAddr; }
        public String getClusterId() { return clusterId; }
        public String getResMgrWsAddr() { return resMgrWsAddr; }
        public int getJobHistMaxHrs() { return jobHistMaxHrs; }
    }
    
    public static class JobResult 
    {        
        private String ownerId = null;
        private String ownerEmail = null;
        private String jobId = null;
        private String jobDescr = null;
        private int jobIndex = 0;
        private int jobStatus = 0;
        private int exitValue = 0;
        private String exitMessage = null;
        private boolean startJob = false;
        private String submitTs = null;
        private String startTs = null;
        private String completeTs = null;
        
        public JobResult( 
                String ownerId, 
                String ownerEmail,
                String jobId,
                String jobDescr,
                int jobIndex,
                int jobStatus,
                int startJob,
                int exitValue,
                String exitMessage,
                String submitTs,
                String startTs,
                String completeTs ) 
        {
            this.ownerId = ownerId;
            this.ownerEmail = ownerEmail;
            this.jobId = jobId;
            this.jobDescr = jobDescr;
            this.jobIndex = jobIndex;
            this.jobStatus = jobStatus;
            
            if ( startJob != 0 ) 
            {
                this.startJob = true;
            } 
            
            else 
            {
                this.startJob = false;
            }
            
            this.exitValue = exitValue;
            this.exitMessage = exitMessage;
            this.submitTs = submitTs;
            this.startTs = startTs;
            this.completeTs = completeTs;
        }
                
        public String getOwnerId() { return ownerId; }
        public String getOwnerEmail() { return ownerEmail; }
        public String getJobId() { return jobId; }
        public String getJobDescr() { return jobDescr; }
        public int getJobIndex() { return jobIndex; }
        public int getJobStatus() { return jobStatus; }
        public boolean startJob() { return startJob; }
        public int getExitValue() { return exitValue; }
        public String getExitMessage() { return exitMessage; }
        public String getSubmitTs() { return submitTs; }
        public String getStartTs() { return startTs; }
        public String getCompleteTs() { return completeTs; }
    };    
    
    ////////////////////////////////////////////////////////////////////////////
    // private attributes
    //      
    
    private static final String MYSQL_DRV = "com.mysql.jdbc.Driver";
    
    private static final String CFG_COL_ID = "id"; 
    private static final String CFG_COL_CLUSTERID = "cluster_id";
    private static final String CFG_COL_WSADDR_JOBHOST = "wsaddr_jobhost";
    private static final String CFG_COL_WSADDR_RESMGR = "wsaddr_resmgr";
    private static final String CFG_COL_JOBHIST_MAXHRS = "jobhist_maxhrs";    
    
    private static final String JOB_COL_ID = "id";
    private static final String JOB_COL_OWNERID = "owner_id";
    private static final String JOB_COL_OWNEREMAIL = "owner_email";
    private static final String JOB_COL_JOBID = "job_id";
    private static final String JOB_COL_JOBDESCR = "job_descr";
    private static final String JOB_COL_JOBINDEX = "job_index";
    private static final String JOB_COL_JOBSTATUS = "job_status"; 
    private static final String JOB_COL_STARTJOB = "start_job";
    private static final String JOB_COL_EXITVAL = "exit_value";
    private static final String JOB_COL_EXITMSG = "exit_message";
    private static final String JOB_COL_TS_SUBMIT = "ts_submit";
    private static final String JOB_COL_TS_START = "ts_start";
    private static final String JOB_COL_TS_COMPLETE = "ts_complete"; 
    
    private static final String DYNINFO_COL_ID = "id";
    private static final String DYNINFO_COL_INFO = "info_doc";
    
    public static final String OUTPUT_COL_ID = "id";
    public static final String OUTPUT_COL_JOBID = "job_id";
    public static final String OUTPUT_COL_FILEPATH = "file_path";
    
    public static final int START_JOB_FLAG = 1;
    
    private static String connStr = "";
    
    ////////////////////////////////////////////////////////////////////////////
    // public methods
    //
    
    public static String createTableInitScript() 
    {
        String initScript = 
                "CREATE TABLE `" + DB_SCHEMA + "`.`" + TBL_JOBS_CURR + "` (\n" +
                "`" + JOB_COL_ID + "` INT  NOT NULL AUTO_INCREMENT,\n" +                
                "`" + JOB_COL_OWNERID + "` VARCHAR(255)  NOT NULL,\n" +
                "`" + JOB_COL_JOBID + "` VARCHAR(255)  NOT NULL,\n" +
                "`" + JOB_COL_JOBSTATUS + "` INT  NOT NULL,\n" +
                "`" + JOB_COL_JOBDESCR + "` TEXT NOT NULL,\n" +
                "`" + JOB_COL_JOBINDEX + "` INT NOT NULL,\n" +
                "`" + JOB_COL_EXITVAL + "` INT NOT NULL DEFAULT 0,\n" +
                "`" + JOB_COL_EXITMSG + "` VARCHAR(255),\n" +
                "`" + JOB_COL_OWNEREMAIL + "` VARCHAR(255),\n" +
                "`" + JOB_COL_STARTJOB + "` INT NOT NULL DEFAULT 0,\n" +
                "`" + JOB_COL_TS_SUBMIT + "` VARCHAR(32),\n" +
                "`" + JOB_COL_TS_START + "` VARCHAR(32),\n" +
                "`" + JOB_COL_TS_COMPLETE + "` VARCHAR(32),\n" +
                "PRIMARY KEY(`" + Sql.JOB_COL_ID + "`)\n" +
                ")\n" +
                "TYPE = MYISAM;\n";  
        
        initScript += 
                "CREATE TABLE `" + DB_SCHEMA + "`.`" + TBL_JOBS_HIST + "` (\n" +
                "`" + JOB_COL_ID + "` INT  NOT NULL AUTO_INCREMENT,\n" +                
                "`" + JOB_COL_OWNERID + "` VARCHAR(255)  NOT NULL,\n" +
                "`" + JOB_COL_JOBID + "` VARCHAR(255)  NOT NULL,\n" +
                "`" + JOB_COL_JOBSTATUS + "` INT  NOT NULL,\n" +
                "`" + JOB_COL_JOBDESCR + "` TEXT NOT NULL,\n" +
                "`" + JOB_COL_JOBINDEX + "` INT  NOT NULL,\n" +  
                "`" + JOB_COL_EXITVAL + "` INT NOT NULL DEFAULT 0,\n" +
                "`" + JOB_COL_EXITMSG + "` VARCHAR(255),\n" +   
                "`" + JOB_COL_OWNEREMAIL + "` VARCHAR(255),\n" +
                "`" + JOB_COL_STARTJOB + "` INT NOT NULL DEFAULT 0,\n" +
                "`" + JOB_COL_TS_SUBMIT + "` VARCHAR(32),\n" +
                "`" + JOB_COL_TS_START + "` VARCHAR(32),\n" +
                "`" + JOB_COL_TS_COMPLETE + "` VARCHAR(32),\n" +
                "PRIMARY KEY(`" + Sql.JOB_COL_ID + "`)\n" +
                ")\n" +
                "TYPE = MYISAM;\n";  
        
        initScript +=
                "CREATE TABLE `" + DB_SCHEMA + "`.`" + TBL_CFG + "` (\n" +
                "`" + CFG_COL_ID + "` INT NOT NULL AUTO_INCREMENT,\n" +                
                "`" + CFG_COL_WSADDR_JOBHOST + "` VARCHAR(255) NOT NULL,\n" +
                "`" + CFG_COL_CLUSTERID + "` VARCHAR(255) NOT NULL,\n" +
                "`" + CFG_COL_WSADDR_RESMGR + "` VARCHAR(255) NOT NULL,\n" +
                "`" + CFG_COL_JOBHIST_MAXHRS + "` INT NOT NULL DEFAULT 0,\n" +
                "PRIMARY KEY(`" + Sql.JOB_COL_ID + "`)\n" +
                ")\n" +
                "TYPE = MYISAM;\n";   
        
        initScript +=
                "CREATE TABLE `" + DB_SCHEMA + "`.`" + TBL_JOBS_OUTPUT + "` (\n" +
                "`" + OUTPUT_COL_ID + "` INT(11)  NOT NULL AUTO_INCREMENT,\n" +
                "`" + OUTPUT_COL_JOBID + "` VARCHAR(255)  NOT NULL,\n" +
                "`" + OUTPUT_COL_FILEPATH + "` VARCHAR(255)  NOT NULL,\n" +
                "PRIMARY KEY(`" + OUTPUT_COL_ID + "`)\n" +
                ")\n" +
                "TYPE = MYISAM;\n"; 
        
        initScript +=
                "CREATE TABLE `" + DB_SCHEMA + "`.`" + TBL_DYN_INFO + "` (\n" +
                "`" + DYNINFO_COL_ID + "` INT(11)  NOT NULL AUTO_INCREMENT,\n" +
                "`" + DYNINFO_COL_INFO + "` TEXT  NOT NULL,\n" +
                "PRIMARY KEY(`" + DYNINFO_COL_ID + "`)\n" +
                ")\n" +
                "TYPE = MYISAM;\n";
        
        
        return initScript;
    }
    
    public static void loadDriver() throws Exception 
    {
        try 
        {
            Class.forName( MYSQL_DRV ).newInstance();
            
        } catch ( Exception ex ) {
            throw ex;
        }
    }

    public static void loadDriver( String drv ) throws Exception 
    {
        try 
        { 
            // The newInstance() call is a work around for some 
            // broken Java implementations
            Class.forName( drv ).newInstance(); 
            
        } catch ( Exception ex ) { 
            throw ex; 
        }        
    }  
    
    public static void setConnectionParams(
            String host,
            String port,
            String pass ) 
    {
        
        if ( host.startsWith( "http://") ) 
        {
            String tmp = "http://";
            host = host.substring( tmp.length(), host.length() );
        }
        
        connStr =  "jdbc:mysql:/";
        connStr += "/" + host;
        connStr += ":" + port;
        connStr += "/" + DB_SCHEMA ;     
        connStr += "?user=" + DB_USER;
        connStr += "&password=" + pass;           
    }
    
    public static Connection getConnection() throws SQLException, Exception 
    {
        Connection conn = null;
        
        if ( connStr.length() == 0 ) 
        {
            throw new SQLException( "connection parameters have not been set" );
        }
        
        try 
        {
            conn = DriverManager.getConnection( connStr ); 
            
        } catch( SQLException sqle ) {
            throw sqle;
        }
        
        return conn;
    }     
    
    public static String getDynamicInfoXmlDoc( Connection conn )
    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() )
            {
                return rs.getString( DYNINFO_COL_INFO );                
            }
            
            return null;
            
        } 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 setDynamicInfoXmlDoc( Connection conn, String xmlDoc )