sql.java

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

             Connection conn,
             String ownerId,
             String schedId )
     throws SQLException, Exception 
     {
        Statement stmt = null; 
        String sqlstr = null; 
        
        try 
        {                   
            stmt = conn.createStatement();
            
            sqlstr = 
                    "DELETE FROM " + TBL_QUEUE + 
                    " WHERE " +
                    QUEUE_COL_JOB_OWNERID + " = '" + ownerId + "'" +
                    " AND " +
                    QUEUE_COL_JOBSCHED_ID + " = '" + schedId + "'";
            
            stmt.execute( sqlstr );
            
        } catch ( SQLException sqle ) {
            throw sqle;
            
        } catch ( Exception e ) {
            throw e;
            
        } finally {              
            try 
            {
                if( stmt != null ) 
                {
                    stmt.close();
                    stmt = null;
                }  
                
            } catch ( Exception ex ) {}            
        }          
     }   
     
     public static List<DeployResult> getDeployed( 
             Connection conn, 
             String ownerId )
     throws SQLException, Exception 
     {
        Statement stmt = null; 
        String sqlstr = null; 
        ResultSet rs = null; 
        Vector<DeployResult> list = new Vector();
        
        try 
        {                   
            stmt = conn.createStatement();
            
            sqlstr = 
                    "SELECT * FROM " + TBL_DEPLOY +
                    " WHERE " + 
                    DEPLOY_COL_JOBOWNER_ID + " = '" + ownerId + "'"; 

            if ( stmt.execute( sqlstr ) ) 
            {
                rs = stmt.getResultSet();
                
                while ( rs.next() ) 
                {
                    list.addElement( new DeployResult(
                            rs.getString( DEPLOY_COL_JOBOWNER_ID ),
                            rs.getString( DEPLOY_COL_JOBSCHED_ID ),
                            rs.getString( DEPLOY_COL_JOBGROUP_ID ),
                            rs.getInt( DEPLOY_COL_JOB_NUMBER ),
                            rs.getString( DEPLOY_COL_JOBHOST_WSADDR ),
                            rs.getString( DEPLOY_COL_JOBHOST_JOBID ),
                            rs.getInt( DEPLOY_COL_STATUS ),
                            rs.getString( DEPLOY_COL_ERRMSG ) ) );
                }
            } 
            
            else 
            {
                throw new SQLException( 
                        "SQL SELECT failed on table '" + TBL_DEPLOY + "'" );
            }
            
        } 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 deleteDeployed(
             Connection conn,
             String ownerId,
             String schedId )
     throws SQLException, Exception 
     {
        Statement stmt = null; 
        String sqlstr = null;     
        try 
        {                   
            stmt = conn.createStatement();
            
            sqlstr = 
                    "DELETE FROM " + TBL_DEPLOY + 
                    " WHERE " +
                    DEPLOY_COL_JOBOWNER_ID + " = '" + ownerId + "'" +
                    " AND " +
                    DEPLOY_COL_JOBSCHED_ID + " = '" + schedId + "'";
            
            stmt.execute( sqlstr );
            
        } catch ( SQLException sqle ) {
            throw sqle;
            
        } catch ( Exception e ) {
            throw e;
            
        } finally {              
            try 
            {
                if( stmt != null ) 
                {
                    stmt.close();
                    stmt = null;
                }  
                
            } catch ( Exception ex ) {}            
        }               
     }
     
     public static void insertDeployed(
             Connection conn,
             String ownerId,
             String schedId,
             String groupId,
             int jobNumber,
             String jobHostWsAddr,
             String jobHostJobId,
             int status,
             String errMsg )
     throws SQLException, Exception 
     {
        Statement stmt = null; 
        String sqlstr = null; 
        ResultSet rs = null; 
        
        ownerId = ownerId.replaceAll( "'", "''" );
        jobHostWsAddr = jobHostWsAddr.replaceAll( "'", "''" );
        jobHostJobId = jobHostJobId.replaceAll( "'", "''" ); 
        errMsg = errMsg.replaceAll( "'", "''" ); 

        try 
        {                   
            stmt = conn.createStatement();
            
            sqlstr = 
                    "SELECT * FROM " + TBL_DEPLOY + 
                    " WHERE " +
                    DEPLOY_COL_JOBOWNER_ID + " = '" + ownerId + "'" +
                    " AND " +
                    DEPLOY_COL_JOBSCHED_ID + " = '" + schedId + "'" +
                    " AND " +
                    DEPLOY_COL_JOBGROUP_ID + " = '" + groupId + "'" + 
                    " AND " +
                    DEPLOY_COL_JOB_NUMBER + " = " + jobNumber;
            
            if ( stmt.execute( sqlstr ) )
            {
                rs = stmt.getResultSet();
                
                if ( rs.next() )
                {
                }
                
                else
                {
                    sqlstr = 
                            "INSERT INTO " + TBL_DEPLOY + 
                            " ( " +
                            DEPLOY_COL_JOBOWNER_ID          + "," +
                            DEPLOY_COL_JOBSCHED_ID             + "," +
                            DEPLOY_COL_JOBGROUP_ID          + "," +
                            DEPLOY_COL_JOB_NUMBER           + "," + 
                            DEPLOY_COL_JOBHOST_WSADDR       + "," +
                            DEPLOY_COL_JOBHOST_JOBID        + "," +
                            DEPLOY_COL_STATUS               + "," +
                            DEPLOY_COL_ERRMSG +
                            " ) VALUES ( " +
                            "'" + ownerId + "'"             + "," +
                            "'" + schedId + "'"             + "," +
                            "'" + groupId + "'"             + "," +
                            jobNumber                       + "," +
                            "'" + jobHostWsAddr + "'"       + "," +
                            "'" + jobHostJobId + "'"        + "," +
                            status                          + "," +
                            "'" + errMsg + "'"              +
                            " )";

                    stmt.execute( sqlstr );                    
                }                
            }
            
            else
            {
                throw new SQLException( 
                        "SQL SELECT failed on table '" + TBL_QUEUE + "'" );
            }
                            
        } 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 ) {}            
        }                    
     }
     
     public static void deleteQueue( Connection conn )
     throws SQLException, Exception 
     {
        Statement stmt = null; 
        String sqlstr = null;     
        try 
        {                   
            stmt = conn.createStatement();
            stmt.execute( "DELETE FROM " + TBL_QUEUE );
            
        } catch ( SQLException sqle ) {
            throw sqle;
            
        } catch ( Exception ex ) {
            throw ex;
            
        } finally {              
            try 
            {
                if ( stmt != null ) 
                {
                    stmt.close();
                    stmt = null;
                }  
                
            } catch ( Exception ex ) {}            
        }                      
     }
     
     public static void deleteDeploy( Connection conn )
     throws SQLException, Exception 
     {
        Statement stmt = null; 
        String sqlstr = null;     
        try 
        {                   
            stmt = conn.createStatement();
            stmt.execute( "DELETE FROM " + TBL_DEPLOY );
            
        } catch ( SQLException sqle ) {
            throw sqle;
            
        } catch ( Exception e ) {
            throw e;
            
        } finally {              
            try 
            {
                if( stmt != null ) 
                {
                    stmt.close();
                    stmt = null;
                }  
                
            } catch ( Exception ex ) {}            
        }                      
     }     
     
     public static void beginTransaction( Connection conn )
     throws SQLException, Exception 
     {
        Statement stmt = null; 
        String sqlstr = null; 
        
        try {                   
            stmt = conn.createStatement();
            
            sqlstr = "BEGIN";
            
            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 commitTransaction( Connection conn )
     throws SQLException, Exception 
     {
        Statement stmt = null; 
        String sqlstr = null; 
        
        try {                   
            stmt = conn.createStatement();
            
            sqlstr = "COMMIT";
            
            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 rollbackTransaction( Connection conn )
     throws SQLException, Exception 
     {
        Statement stmt = null; 
        String sqlstr = null; 
        
        try {                   
            stmt = conn.createStatement();
            
            sqlstr = "ROLLBACK";
            
            stmt.execute( sqlstr );
            
        } catch ( SQLException sqle ) {
            throw sqle;
            
        } catch ( Exception e ) {
            throw e;
            
        } finally {              
            try 
            {
                if( stmt != null ) 
                {
                    stmt.close();
                    stmt = null;
                }  
                
            } catch ( Exception ex ) {}            
        }          
     }     
    
    public static Connection getConnection() throws SQLException, Exception 
    {
        Connection conn = null;
        
        if ( connStr == null ) 
        {
            throw new Exception( "connection parameters have not been set" );
        }
        
        try 
        {
            conn = DriverManager.getConnection( connStr ); 
            
        } catch( SQLException sqle ) {
            throw sqle;
        }
        
        return conn;
    }     
    
    /** set connection parameters */
    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 void loadDriver() throws Exception 
    {
        try 
        {
            loadDriver( MYSQL_DRV );
            
        } catch ( Exception e ) {
            throw e;
        }
    }
    
    public static void loadDriver( String drv ) throws Exception 
    {
        if ( drv == null || drv.length() == 0 ) 
        {
            drv = MYSQL_DRV;
        }
    
        try 
        { 
            Class.forName( drv ).newInstance(); 
            
        } catch ( ClassNotFoundException ce ) { 
            throw ce; 
            
        } catch ( InstantiationException ie ) {
            throw ie;
            
        } catch ( IllegalAccessException iae ) {
            throw iae;
        }  
    }      
}