sqlreader.java

JAVA EXCEL操作API

/*
 * Created on 2004-3-22
 *
 * To change the template for this generated file go to
 * Window - Preferences - Java - Code Generation - Code and Comments
 */
package com.zosatapo.xls.io;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import com.zosatapo.xls.core.Cell;
import com.zosatapo.xls.core.Column;
import com.zosatapo.xls.core.CoreException;
import com.zosatapo.xls.core.Record;
import com.zosatapo.xls.core.Schema;
import com.zosatapo.xls.core.Type;
import com.zosatapo.xls.util.ConnUtils;
import com.zosatapo.xls.util.IoUtils;

/**
 * @author Administrator
 *
 * To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Generation - Code and Comments
 */
public class SQLReader implements  Reader
{
  private Schema schema;
  private ResultSet rs;
  
  private ResultSetMetaData metadata;
  
  private int rowCount;
  private Record record;
  
  public SQLReader(Schema schema,ResultSet rs)
  {
    this.schema=schema;
    this.rs=rs;
    
    this.rowCount=0;
  }
  
  public boolean hasNext()throws CoreException
  {
    try
    {
      while(rowCount<schema.getStartRow())
      {
        System.err.println("[Skip Record] row="+rowCount);
        if(!rs.next())
        {
          return false;
        }
        
        ++rowCount;
      }
      
      if(rowCount>schema.getEndtRow())
      {
        return false;
      }
      return rs.next();
    }
    catch(SQLException sqlex)
    {
      throw new CoreException(sqlex);
    }
  }
  
  public Record next()throws CoreException
  {
    if(record==null)
    {
      record=new Record(schema,rowCount); 
    }
    else
    {
      record.setRowIndex(rowCount);
      record.clearCells();
    }
    
    try
    {
      if(metadata==null)
      {
        metadata=rs.getMetaData();
      }
      
      int colTotal=metadata.getColumnCount();  
      Type srcType=null;
      Type dstType=null;
      Column column=null;
      
      Cell cellObject=null;
         
      for(int i=1;i<=colTotal && (i<=schema.getColumnCount());++i)
      {
        column=schema.getColumn(i-1);
        
        if(column.isNull())
        {
          record.addCell(Cell.NULL_CELL);
        }
        else
        {
          srcType=column.getInType();
          dstType=column.getType();
        
          if(srcType==null)
          {
            srcType=dstType;
          }
          if(column.useDefault())
          {
            //使用缺省值就不需要再次从数据源中读取
            cellObject=new Cell(schema,rowCount,i-1,column.getDefaultValue());          
          }
          else
          {
            cellObject=new Cell(schema,rowCount,i-1,IoUtils.readCell(rs,i,srcType,dstType));          
          } 
          
          record.addCell(cellObject);
         
        }
      }
    }
    catch(SQLException sqlex)
    {
      throw new CoreException(sqlex);
    }
    
    ++rowCount;
    
    System.err.println("[Read]{"+record+"}");
    
    return record;
  }
  
  public void close()throws CoreException{}
  
  public Schema getSchema()
  {
    return schema;
  }
  
  public static void main(String args[])
  throws Exception
  {
    POIFSFileSystem fs =new POIFSFileSystem(new FileInputStream(".\\conf\\in.xls"));
    HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs);
    HSSFSheet        sheet  = hssfworkbook.getSheetAt(0);  
    
    Schema schema=new Schema();   
    schema.open(); 
    
    Connection conn=ConnUtils.getConnection(schema.getStoreConfig());
    Statement stmt=conn.createStatement();
    ResultSet rs=stmt.executeQuery("select dat_birthday,vc_family,vc_name from test");
    SQLReader reader=new SQLReader(schema,rs); 
    
    while(reader.hasNext())
    {
      reader.next();
    }
    
    conn.close();
  }
}