sqlwriter.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.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

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.IoUtils;
/**
 * @author zosatapo
 *
 * To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Generation - Code and Comments
 */
public class SQLWriter implements Writer
{
	private Schema schema;
	private Connection conn;
	private PreparedStatement stmt;
	public SQLWriter(Schema schema, Connection conn)
	{
		this.schema = schema;
		this.conn = conn;
	}
  
	public void write(Record record) throws CoreException
	{
		try
		{
			if (stmt == null)
			{
				stmt = conn.prepareStatement(generateSQL(record));
			}
      
		  //System.err.println("[generateSQL]"+generateSQL(record));
			
      PreparedStatement pstmt = (PreparedStatement) stmt;
			setParameter(pstmt, record);
			pstmt.execute();
		}
		catch (SQLException sqlex)
		{
      System.err.println("[SQLWriter write]{" + record + "}");
      throw new CoreException(sqlex);
		}
    
		//System.err.println("[Write]{" + record + "}");
	}
  
	public void close() throws CoreException
	{
		try
		{
			if (stmt != null)
			{
				PreparedStatement pstmt = (PreparedStatement) stmt;
				pstmt.close();
			}
		}
		catch (SQLException sqlex)
		{
			throw new CoreException(sqlex);
		}
	}

	public Schema getSchema()
	{
		return schema;
	}
  
	//----------------------------------------
	private String generateSQL(Record record)
	{
		int colTotal = schema.getColumnCount();
		StringBuffer columns = new StringBuffer();
		StringBuffer values = new StringBuffer();
		Column column = null;
		String colName = null;
		Type colType = null;
		Cell cell = null;
    
		for (int i = 0; i < colTotal; ++i)
		{
			column = schema.getColumn(i);
			    
      if(!column.isNull())
      {
        colName = column.getName();
  			columns.append(colName + ",");
  			values.append("?,");
      }
		}
    
    String col=columns.toString();
    String val=values.toString();
    
    int chopIndex=col.lastIndexOf(",");
    if(chopIndex>0)
    {
      col=col.substring(0,chopIndex);
    }

    chopIndex=val.lastIndexOf(",");
    if(chopIndex>0)
    {
      val=val.substring(0,chopIndex);
    }
    
		return "insert into "
			+ schema.getTableName()
			+ "("
			+ col
			+ ") values("
			+ val
			+ ")";
	}
	private void setParameter(PreparedStatement pstmt, Record record)
	throws SQLException, CoreException
	{
		int cellSize = record.getCellCount();
    int paramIndex=1;
    
		for (int i = 0; i < cellSize; ++i)
		{
			Cell cell = record.getCell(i);
             
      //有些列可能不需要导入数据库      
      if(!cell.isNull())
      {
        IoUtils.writeCell(pstmt, cell,paramIndex);
        ++paramIndex;
      }
    }
	}
}