sql.java

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

/*
 * Sql.java
 *
 * Created on April 20, 2005, 11:35 AM
 */

package jwsgrid.scheduler.priv;

import jwsgrid.scheduler.Scheduler;

import java.sql.*;

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

/**
 *
 * @author sean
 */
public class Sql 
{    
    ////////////////////////////////////////////////////////////////////////////
    // public attributes
    //
    
    public static final String MYSQL_DRV = "com.mysql.jdbc.Driver";
    
    public final static String DB_SCHEMA = "gridsched";
    public final static String DB_USER = "gridsched";
    public final static String LOG_MAXENTRIES_STR = "100";
    public final static int LOG_MAXENTRIES = 100;
    
    public final static String TBL_CFG = "config";
    public final static String CFG_COL_ID = "id";
    public final static String CFG_COL_WSADDR_RESMGR = "resmgr_wsaddr";
        
    public final static String TBL_QUEUE= "sched_queue";  
    public final static String QUEUE_COL_ID = "id";
    public final static String QUEUE_COL_JOB_OWNERID = "jobowner_id";
    public final static String QUEUE_COL_JOBSCHED_ID = "jobsched_id"; 
    public final static String QUEUE_COL_JOB_DESCRIPTIONS = "job_descriptions";
    public final static String QUEUE_COL_JOB_CONSTRAINTS = "job_constraints";
    
    public final static String TBL_DEPLOY = "sched_deploy";
    public final static String DEPLOY_COL_ID = "id";
    public final static String DEPLOY_COL_JOBOWNER_ID = "jobowner_id";
    public final static String DEPLOY_COL_JOBSCHED_ID = "jobsched_id"; 
    public final static String DEPLOY_COL_JOBGROUP_ID = "jobgroup_id";
    public final static String DEPLOY_COL_JOB_NUMBER = "job_number";  
    public final static String DEPLOY_COL_JOBHOST_WSADDR = "jobhost_wsaddr"; 
    public final static String DEPLOY_COL_JOBHOST_JOBID = "jobhost_jobid";
    public final static String DEPLOY_COL_STATUS = "status";
    public final static String DEPLOY_COL_ERRMSG = "error_msg";
 
    ////////////////////////////////////////////////////////////////////////////
    // public classes
    //    
    
    public static class ConfigResult
    {
        private String resMgrWsAddr = null;
        
        public ConfigResult( String resMgrWsAddr ) 
        {
            this.resMgrWsAddr = resMgrWsAddr;
        }
        
        public String getResMgrWsAddr() { return resMgrWsAddr; }
    }
    
    public static class QueueResult
    {
        private String ownerId = null;
        private String schedId = null;
        private String jobDescription = null;
        private String jobConstraints = null;  
        
        public QueueResult(
                String ownerId,
                String schedId,
                String jobDescription,
                String jobConstraints ) 
        {
            this.ownerId = ownerId;
            this.schedId = schedId;
            this.jobDescription = jobDescription;
            this.jobConstraints = jobConstraints;
        }
       
        public String getOwnerId() { return ownerId; }
        public String getSchedId() { return schedId; }
        public String getJobDescriptions() { return jobDescription; }
        public String getJobConstraints() { return jobConstraints; }
    }
    
    public static class DeployResult
    {
        private String ownerId = null;
        private String schedId = null;
        private String groupId = null;
        private int jobNumber = 0;
        private String jobHostWsAddr = null;
        private String jobHostJobId = null;
        private int status = 0;
        private String errMsg = null;
        
        public DeployResult(                
                String ownerId,
                String schedId,
                String groupId,
                int jobNumber,
                String jobHostWsAddr,
                String jobHostJobId,
                int status,
                String errMsg ) 
        {
            this.ownerId = ownerId;
            this.schedId = schedId;
            this.groupId = groupId;
            this.jobNumber = jobNumber;
            this.jobHostWsAddr = jobHostWsAddr;
            this.jobHostJobId = jobHostJobId;
            this.status = status;
            this.errMsg = errMsg;
        }
       
        public int getStatus() { return status; }
        public String getOwnerId() { return ownerId; }
        public String getSchedId() { return schedId; }
        public String getGroupId() { return groupId; }
        public int getJobNumber() { return jobNumber; }
        public String getJobHostWsAddr() { return jobHostWsAddr; }
        public String getJobHostJobId() { return jobHostJobId; }
        public String getErrorMsg() { return errMsg; }
    } 
    
    
    ////////////////////////////////////////////////////////////////////////////
    // private attributes
    //
    
    private static String connStr = null;       
    
    ////////////////////////////////////////////////////////////////////////////
    // public methods
    // 
    
    public static String createTableInitScript() 
    {
        String initScript = "";

        initScript += 
                "CREATE TABLE `" + DB_SCHEMA + "`.`" + TBL_CFG + "` (\n" +
                "`" + CFG_COL_ID + "` INT  NOT NULL AUTO_INCREMENT,\n" +
                "`" + CFG_COL_WSADDR_RESMGR + "` VARCHAR(255)  NOT NULL,\n" +
                "PRIMARY KEY(`" + CFG_COL_ID + "`)\n" +
                ")\n" +
                "TYPE = MYISAM;\n";      

        initScript += 
                "CREATE TABLE `" + DB_SCHEMA + "`.`" + TBL_QUEUE + "` (\n" +
                "`" + QUEUE_COL_ID + "` INT  NOT NULL AUTO_INCREMENT,\n" +
                "`" + QUEUE_COL_JOB_OWNERID + "` VARCHAR(255)  NOT NULL,\n" +
                "`" + QUEUE_COL_JOBSCHED_ID + "` VARCHAR(255)  NOT NULL,\n" +
                "`" + QUEUE_COL_JOB_DESCRIPTIONS + "` TEXT NOT NULL,\n" + 
                "`" + QUEUE_COL_JOB_CONSTRAINTS + "` TEXT NOT NULL,\n" +
                "PRIMARY KEY(`" + QUEUE_COL_ID + "`)\n" +
                ")\n" +
                "TYPE = MYISAM;\n";                 

        initScript += 
                "CREATE TABLE `" + DB_SCHEMA + "`.`" + TBL_DEPLOY + "` (\n" +
                "`" + DEPLOY_COL_ID + "` INT  NOT NULL AUTO_INCREMENT,\n" +
                "`" + DEPLOY_COL_JOBOWNER_ID + "` VARCHAR(255)  NOT NULL,\n" +  
                "`" + DEPLOY_COL_JOBSCHED_ID + "` VARCHAR(255)  NOT NULL,\n" +
                "`" + DEPLOY_COL_JOBGROUP_ID + "` VARCHAR(255),\n" +
                "`" + DEPLOY_COL_JOB_NUMBER + "` INT DEFAULT 0,\n" +  
                "`" + DEPLOY_COL_JOBHOST_WSADDR + "` VARCHAR(255),\n" +
                "`" + DEPLOY_COL_JOBHOST_JOBID + "` VARCHAR(255),\n" +
                "`" + DEPLOY_COL_STATUS + "` INT DEFAULT 0,\n" + 
                "`" + DEPLOY_COL_ERRMSG + "` VARCHAR(255),\n" +
                "PRIMARY KEY(`" + DEPLOY_COL_ID + "`)\n" +
                ")\n" +
                "TYPE = MYISAM;\n";    
              
        return initScript;
    }   
    
    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 Exception( 
                        "SELECT * failed on table '" + TBL_CFG + "'" );
            }
            
            rs = stmt.getResultSet();
            
            if ( rs.next() ) 
            {
                result = new ConfigResult(
                        rs.getString( CFG_COL_WSADDR_RESMGR ) );                
            }  
            
        } 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 ) {}            
        }  
        
        return result;
    }  
    
    public static void setConfig( Connection conn, String resMgrWsAddr ) 
    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 Exception( 
                        "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_RESMGR +
                        " ) VALUES ( " +
                        "'" + resMgrWsAddr + "'" + 
                        " )";  
            } 
            
            else 
            {
                sqlstr = 
                        "UPDATE " + TBL_CFG + " SET " +
                        CFG_COL_WSADDR_RESMGR + " = '" + resMgrWsAddr + "'" + 
                        " 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 List<QueueResult> getQueued( 
             Connection conn, 
             String ownerId )
     throws SQLException, Exception 
     {
        Statement stmt = null; 
        String sqlstr = null; 
        ResultSet rs = null; 
        Vector<QueueResult> list = new Vector();
        
        try 
        {                   
            stmt = conn.createStatement();
            
            sqlstr = "SELECT * FROM " + TBL_QUEUE;

            if ( ownerId != null )
            {
                sqlstr += 
                        " WHERE " + QUEUE_COL_JOB_OWNERID +
                        " = '" + ownerId + "'"; 
            } 
            
            if ( stmt.execute( sqlstr ) ) 
            {
                rs = stmt.getResultSet();
                
                while ( rs.next() ) 
                {
                    list.addElement( 
                            new QueueResult(
                            rs.getString( QUEUE_COL_JOB_OWNERID ),
                            rs.getString( QUEUE_COL_JOBSCHED_ID ),
                            rs.getString( QUEUE_COL_JOB_DESCRIPTIONS ),
                            rs.getString( QUEUE_COL_JOB_CONSTRAINTS ) ) );
                }
            } 
            
            else 
            {
                throw new SQLException( 
                        "SQL SELECT failed on table '" + TBL_QUEUE + "'" );
            }
            
        } 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 insertQueued( 
             Connection conn,
             String ownerId,
             String schedId,
             String jobDescriptions,
             String jobConstraints )
     throws SQLException, Exception 
     {
        Statement stmt = null; 
        String sqlstr = null; 
        ResultSet rs = null; 
        int rowCount = 0;
        
        ownerId = ownerId.replaceAll( "'", "''" );
        jobDescriptions = jobDescriptions.replaceAll( "'", "''" );
        jobConstraints = jobConstraints.replaceAll( "'", "''" );       
        
        try 
        {                   
            stmt = conn.createStatement();     
            
            sqlstr = 
                    "SELECT * FROM " + TBL_QUEUE + " WHERE " +
                    QUEUE_COL_JOB_OWNERID + " = '" + ownerId + "'" +
                    " AND " +
                    QUEUE_COL_JOBSCHED_ID + " = '" + schedId + "'";
            
            if ( stmt.execute( sqlstr ) ) 
            {
                rs = stmt.getResultSet();
                
                if ( rs.next() ) 
                {

                }
                
                else
                {
                    sqlstr = 
                            "INSERT INTO " + TBL_QUEUE + 
                            " ( " +
                            QUEUE_COL_JOB_OWNERID           + "," +
                            QUEUE_COL_JOBSCHED_ID              + "," +
                            QUEUE_COL_JOB_DESCRIPTIONS      + "," +
                            QUEUE_COL_JOB_CONSTRAINTS       +
                            " ) VALUES ( " +
                            "'" + ownerId + "'"             + "," +
                            "'" + schedId + "'"             + "," +
                            "'" + jobDescriptions + "'"     + "," +
                            "'" + jobConstraints + "'"      + 
                            " )";

                    stmt.execute( sqlstr );                    
                }
                
            } 
            
            else 
            {
                throw new SQLException( 
                        "SQL SELECT failed on table '" + TBL_QUEUE + "'" );
            }

        } 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 deleteQueued(