Java中取sql语句中的列

package com.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

public class OracleTest {
 
 /** 连接驱动 */
    private final String oracleDriverName = "oracle.jdbc.driver.OracleDriver";
  
   
//    private final String oracleUrlToConnect ="jdbc:oracle:thin:@192.168.0.36:1521:Test";
    /** 以下使用的Test就是Oracle里的表空间 */
    private final String oracleUrlToConnect =null;
   
    private Connection myConnection = null;
   
   
    /**
     * 加载 jdbc driver
     *
     */
    public OracleTest() {
        try {
            Class.forName(oracleDriverName);
        }catch(ClassNotFoundException ex) {
            System.out.println(getErrorMessage(ex,"======The Driver loaded error,please contact to your Software Designer!======").toString());
        }
    }
  
   
    /** 错误信息 */
    public StringBuffer getErrorMessage(Exception ex,String alarmMessage){
        StringBuffer errorStringBuffer = new StringBuffer();
        errorStringBuffer.append(alarmMessage);
        errorStringBuffer.append(ex.getMessage());
        return errorStringBuffer;
    }
  
   
    /**
     * 连接
     * @return Connection
     */
    public Connection getConnection(String oracleUrlToConnect,String username,String password) {
        try{
//         this.myConnection = DriverManager.getConnection(oracleUrlToConnect,"Fane","201");
            this.myConnection = DriverManager.getConnection(oracleUrlToConnect,username,password);
          
        }catch(Exception ex){
            System.out.println(getErrorMessage(ex,"======Can not get connection,please contact to your Software Designer!======").toString());
        }
      
        return this.myConnection;
      
    }
   
   
  
    /**
     * @param args
     */
    public static void main(String[] args) throws Exception {
     
     /** url */
       String oracleUrlToConnect=null;
       
       /** 用户名 */
     String username=null;
     
     /** 密码 */
     String password=null;
     
     /** sql语句 */
     String sql=null;
     
     
     if(args.length>=4 ){
      oracleUrlToConnect=args[0];
      username=args[1];
      password=args[2];
      sql=args[3];
     }else{
      System.out.println("======args error======");
      
//      oracleUrlToConnect="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8";
//      username="test";
//      password="123456";
//      sql="select * from tbl_child";
      
      return;
     }
 
     System.out.println("======oracleUrlToConnect======"+oracleUrlToConnect);
     System.out.println("======username======"+username);
     System.out.println("======password======"+password);
     System.out.println("======sql======"+sql);
     
        OracleTest myOracleTest = new OracleTest();
       
        Connection myConnection=null;
        PreparedStatement myPreparedStatement=null;
        ResultSet myResultSet=null;
       
        try{
         
            myConnection = myOracleTest.getConnection(oracleUrlToConnect,username,password);

            System.out.println("======Now begin to excute.............======");
      
//            PreparedStatement myPreparedStatement = myConnection.prepareStatement("select area_id, area_name,ip_address,tel,area_type,pc_id from c_area_info");
           
            myPreparedStatement = myConnection.prepareStatement(sql);
           
            myResultSet = myPreparedStatement.executeQuery();
        
          
            ResultSetMetaData rsmd = myResultSet.getMetaData();
         int colCount = rsmd.getColumnCount();
         
         StringBuffer myStringBuffer = new StringBuffer();

      
            while(myResultSet.next()){
             StringBuffer cols =new StringBuffer("cols (");
             StringBuffer values =new StringBuffer(" values(");
             
    for(int i = 1; i <= colCount; i++) {//for循环控制列数
     
     /** 得到当前列的列名 */
     String name = rsmd.getColumnName(i);  
     
     /** 得到当前列的值 */
     String value = myResultSet.getString(i);
     
//     
//     if("null".equals(value)||value==null){
//      value ="";
//     }
//     if("type".equals(name)||"size".equals(name)){
//      name += "1";
//     }

     /** 时间的处理 */
     if(value.indexOf(':')!=-1&&value.indexOf('-')!=-1&&value.startsWith("20")){
      if(value.length()>10){
       value =value.substring(0,10);
      }
      value ="to_date('"+value+"','YYYY-MM-DD')";
      
      values.append(value);
      
      if(i!=colCount)
       values.append(",");
     }else{
      values.append("'");
      values.append(value);
      values.append("'");
      
      if(i!=colCount)
       values.append(",");
     }
     
     cols.append(name);
     
     if(i!=colCount)
      cols.append(",");
         
    }
    
    cols.append(")");
    values.append(")");
    
    
     System.out.println(cols.toString()+values.toString());
            }
                       
//            System.out.println(new String(myStringBuffer.toString().getBytes("ISO-8859-1"),"GBK"));
        }catch(Exception ex){
            System.out.println(myOracleTest.getErrorMessage(ex,"======Application error,please contact to your Software Designer!======").toString());
        }finally{
         
          /** 关闭连接 */
         try{
          if(myConnection!=null)
           myConnection.close();
         }catch(Exception e){
          
         }
         
          try{
           if(myResultSet!=null)
            myResultSet.close();
          }catch(Exception e){
          
         }
   
     try{
          if(myPreparedStatement!=null)
           myPreparedStatement.close();
        }catch(Exception e){
       
      }
        
        }

    }

}

评论

5 楼 louisling 2009-03-11  

晕.....

4 楼 louisling 2009-03-11  

奇怪, 在你这里竟然没有效果.

3 楼 louisling 2009-03-11  

你为什么不把代码放在 Code 标签里面?
放在里面好看多啦.

/**
 * Get disk volume information, each DiskVolumeInfo refer to a disk volume
 */
public static DiskVolumeInfo[] getDiskCapacity() {
    DiskVolume[] a = FileRegistry.getVolumes();
    for (int i = 0; i < volumes.length; i++) {
        volumes[i].setPrefix(a[i].getPrefix());
        volumes[i].setMaxCapacity(a[i].getMaxCapacity());
        volumes[i].setFreeSpace(a[i].getFreeSpace());
    }
    return volumes;
}

select * from dba_tables 
select object_type from all_objects group by object_type

<logger name="za.gov.ecprov.csd" additivity="false">
    <level value="info" />		
    <appender-ref ref="ConsoleAppender" />
</logger>

<root>
    <priority value="info" />
    <appender-ref ref="ConsoleAppender" />
    <appender-ref ref="DefaultLogAppender" />
</root>

我试过可以支持java, xml, sql 语法颜色.

2 楼 louisling 2009-03-11  

你为什么不把代码放在 Code 标签里面?
放在里面好看多啦.

/**
 * Get disk volume information, each DiskVolumeInfo refer to a disk volume
 */
public static DiskVolumeInfo[] getDiskCapacity() {
    DiskVolume[] a = FileRegistry.getVolumes();
    for (int i = 0; i < volumes.length; i++) {
        volumes[i].setPrefix(a[i].getPrefix());
        volumes[i].setMaxCapacity(a[i].getMaxCapacity());
        volumes[i].setFreeSpace(a[i].getFreeSpace());
    }
    return volumes;
}

我试过可以支持java, xml, sql 语法颜色.

1 楼 louisling 2009-03-11  

你为什么不把代码放在