groovy代码执行效率
有这么个需求,从一个数据库导表的数据到另一个数据库中,考虑到实现的灵活性,采用了spring支持groovy脚本bean的形式.一个月大概20多万记录,采用纯java实现时:
public class BatchExample {
private static Connection orclConn = null;
private static PreparedStatement orclPs = null;

private static Connection sqlConn = null;
private static PreparedStatement sqlPs = null;
private static ResultSet rs = null;

// 分批条数
private static int preCount = 1000;
// 计数器
private static int count = 0;

private static String insertSQL = "insert into H_InDrugIncome(BusDate,ChargeDate,PatientNo,PatientName,NurseDeptCode,SpecialDeptCode,ICUDeptCode,NarcoDeptCode,OpsDeptCode,MedicalTeamCode,DoctorCode,ItemCode,AmountUnit,Amount,Money,CheckoutCode,VisitNo,ItemNo,execdeptcode) values(?, ?, ?,?, ?, ?,?, ?, ?,?, ?, ?,?, ?, ?,?, ?, ?,?)";
private static String querySQL = "select BusDate,ChargeDate,PatientNo,PatientName,NurseDeptCode,SpecialDeptCode,ICUDeptCode,NarcoDeptCode,OpsDeptCode,MedicalTeamCode,DoctorCode,ItemCode,AmountUnit,Amount,Money,CheckoutCode from H_InDrugIncome where busdate =?";

public static void main(String[] args) throws SQLException {
try {
orclConn = DBUtils.getOrclConn();
orclPs = orclConn.prepareStatement(insertSQL);

sqlConn = DBUtils.getSqlServerConn();
sqlPs = sqlConn.prepareStatement(querySQL);
sqlPs.setFetchSize(100);

long start = System.currentTimeMillis();
orclConn.setAutoCommit(false);

String startDate = "2010.03.01";
int index = 0;

for (int i = 0; i < 31; i++) {
sqlPs.setString(1, AppUtil.addDays(startDate, i));
rs = sqlPs.executeQuery();

while (rs.next()) {
orclPs.setString(1, rs.getString(1));
orclPs.setString(2, rs.getString(2));
orclPs.setString(3, rs.getString(3));
orclPs.setString(4, rs.getString(4));
orclPs.setString(5, rs.getString(5));
orclPs.setString(6, rs.getString(6));

orclPs.setString(7, rs.getString(7));
orclPs.setString(8, rs.getString(8));
orclPs.setString(9, rs.getString(9));
orclPs.setString(10, rs.getString(10));
orclPs.setString(11, rs.getString(11));
orclPs.setString(12, rs.getString(12));

orclPs.setString(13, rs.getString(13));
orclPs.setString(14, rs.getString(14));
orclPs.setFloat(15, rs.getFloat(15));
orclPs.setString(16, rs.getString(16));
orclPs.setString(17, null);
orclPs.setString(18, null);
orclPs.setString(19, null);

orclPs.addBatch();
index++;

if (index == preCount) {
orclPs.executeBatch();
orclConn.commit();

System.out.println("当前进行完毕===>" + (++count) * preCount
+ "条");

index = 0;
}
}
}

orclPs.executeBatch();
orclConn.commit();

long end = System.currentTimeMillis();
System.out.println("数据导入完毕,共导入数据记录数为："
+ (index + (count) * preCount) + "条;所用时间为: "
+ (end - start) + " 毫秒");

} catch (Exception e) {
orclConn.rollback();
System.out.println("数据出错,已进行回滚" + e.getMessage());
throw new RuntimeException();
} finally {

DBUtils.free(null, orclPs, orclConn);
DBUtils.free(rs, sqlPs, sqlConn);
}
}
}
统计时间大概30多秒.
照搬到groovy,需70多秒.

采用groovy闭包:
..
try{

def his = Sql.newInstance(
"jdbc:jtds:sqlserver://192.168.1.148:1433/hcostdata-sl",
"sa",
"123",
"net.sourceforge.jtds.jdbc.Driver" );

def hcost = Sql.newInstance(
"jdbc:jtds:sqlserver://192.168.1.148:1433/hcostdata-sl1",
"sa",
"123",
"net.sourceforge.jtds.jdbc.Driver" );
//hcost.connection.autoCommit = false;

def start = System.currentTimeMillis();

hcost.execute("delete from g_indrugincome2");

//def sql = "insert into g_indrugincome2(busdate) values ($startDate)"
//hcost.execute(sql);

def sql = """
insert into g_indrugincome2(busdate,specialDeptCode,OrgCode,OrgCode1,orgCode2,orgcode3,orgcode4,opsdeptCode,employeeid)
values(?,?,?,?,?,?,?,?,?)"""

def hcostConn = hcost.getConnection()
def hcostStmt = hcostConn.prepareStatement(sql);
hcostConn.setAutoCommit(false)

def alls=[];
def index=0;
his.query("select * from g_indrugincome where busdate>=${startDate} and busdate<=${endDate}"){
rs ->
rs.setFetchSize(fetchSize);
while (rs.next()) {

hcostStmt.setString(1, rs.getString("busDate"));
hcostStmt.setString(2, rs.getString("specialDeptCode"));
hcostStmt.setString(3, rs.getString("OrgCode"));
hcostStmt.setString(4, rs.getString("OrgCode1"));
hcostStmt.setString(5, rs.getString("orgCode2"));
hcostStmt.setString(6, rs.getString("orgcode3"));
hcostStmt.setString(7, rs.getString("orgcode4"));
hcostStmt.setString(8, rs.getString("opsdeptCode"));
hcostStmt.setString(9, rs.getString("employeeid"));
//hcostStmt.executeUpdate();
hcostStmt.addBatch();
////logger.info("importIndrug2 query:" + rs.getString("busDate") + "---" );
index ++;

if((index == records)){
//logger.info("importIndrug3 done:" + index + " records," + (System.currentTimeMillis() - start) + " ms");
hcostStmt.executeBatch();
hcostConn.commit();
index =0;
}
//logger.info("importIndrug3 : records," + rs.getString("busDate") + rs.getString("orgcode"));

}
}
hcostStmt.executeBatch();
hcostConn.commit();

logger.info("importIndrug3 done:" + index + " records," + (System.currentTimeMillis() - start) + " ms");

}catch(Exception dd){
logger.error("importIndrug3 grovvy:" + dd);
System.out.println(dd);
throw new Exception(dd);
}

时间大概3分到4分钟.