databasetoolsdaoimpl.java

自动导入文件到数据库,用于那些无法实时入库,定点时间入库的需求

package com.onet.autobill.dao.impl;

import java.sql.CallableStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.onet.autobill.dao.DataBaseToolsDao;

public class DataBaseToolsDaoImpl extends JdbcDaoSupport implements DataBaseToolsDao {
	
	private SimpleDateFormat simpledateformat= new SimpleDateFormat ("yyyyMMdd");

	@Override
	public synchronized String getTableName(final int type, final int logcode, final int isReply,
			final String dateStr) {
		String sql = "{? = CALL UP_GetTableName(?, ?, ?, ?, ?)}";
		String tablename ="" ;
		try {
			tablename =  (String)getJdbcTemplate().execute(
					sql,
					new CallableStatementCallback() {
						public Object doInCallableStatement(CallableStatement cs)
								throws SQLException, DataAccessException {
							// 初始化存储过程的人口参数
							cs.registerOutParameter(1, Types.INTEGER);
							cs.setInt(2, logcode);
							cs.setInt(3, type);
							cs.setInt(4, isReply);
							cs.setString(5, dateStr);
							cs.registerOutParameter(6, Types.VARCHAR);
							//执行存储过程
							cs.execute();

							int errcode = cs.getInt(1);
							if(errcode==-1){
								return "";
							}else{
								return cs.getString(6);
							}
							
						}
					});
		} catch (Exception e) {
			logger.error("获取表名失败!,参数为:type:"+type+";logcode:"+logcode+";isReply:"+isReply+";dateStr:"+dateStr, e);
		}
		return tablename;
	}

	@Override
	public int importBillFileToDB(final String fileName, final String tableName) {
		logger.debug("文件"+fileName+"导入表"+tableName);
//		return 0;
		if (fileName == null || tableName == null || tableName.equals("")
				|| fileName.equals("")) {
			logger.error("文件名或表名为空!");
			return 1;// 只要不返回0就行
		}
		int errcode = 0;
		String sql = "{CALL UP_IMPORTDATAAUTO(?,?,?)}";
		try {
			errcode = (Integer)getJdbcTemplate().execute(
					sql,
					new CallableStatementCallback() {
						public Object doInCallableStatement(CallableStatement cs)
								throws SQLException, DataAccessException {
							// 初始化存储过程的人口参数
							cs.setString(1, fileName);
							cs.setString(2, tableName);
							// 注册返回值
							cs.registerOutParameter(3, Types.INTEGER);
							//执行存储过程
							cs.execute();

							return cs.getInt(3);
							
						}
					});
		} catch (Exception e) {
			logger.error("向表"+tableName+"导入"+fileName+"文本数据失败!", e);
			errcode = 1;
		}
		return errcode;
	}

	@Override
	public boolean updateSubmitStatusAndProductId(final Date date) {
		logger.info("开始更新下行状态报告及产品.");
//		return true;
		String sql = "{CALL ONET_PAutoBill_UpdateProductIdAndStatus(?)}";
		try {
			getJdbcTemplate().execute(sql, 					new CallableStatementCallback() {
						public Object doInCallableStatement(CallableStatement cs)
								throws SQLException, DataAccessException {
							// 初始化存储过程的人口参数

							cs.setString(1,simpledateformat.format(date));
							//执行存储过程
							cs.execute();
								return "";
						}
					});
			logger.info("更新下行状态报告及产品成功.");
			return true;
		} catch (Exception e) {
			logger.error("更新下行状态报告及产品失败!", e);
			return false;
		}
		
	}

	@Override
	public boolean autoTestStat(final Date date) {
		logger.info("开始执行自动巡检统计作业.");
//		return true;
		String sql = "{CALL ONET_PStaticExtDateStat}";
		try {
			getJdbcTemplate().execute(sql, 					new CallableStatementCallback() {
						public Object doInCallableStatement(CallableStatement cs)
								throws SQLException, DataAccessException {
							// 初始化存储过程的人口参数

//							cs.setString(1,simpledateformat.format(date));
							//执行存储过程
							cs.execute();
								return "";
						}
					});
			logger.info("执行自动巡检统计作业成功.");
			return true;
		} catch (Exception e) {
			logger.error("执行自动巡检统计作业失败!", e);
			return false;
		}
	}

	@Override
	public boolean updateDeliverInfo(final Date date) {
		logger.info("开始更新上行信息");
		String sql = "{CALL ONET_PAutoBill_UPDateMoInfo(?)}";
		try {
			getJdbcTemplate().execute(sql, 					new CallableStatementCallback() {
						public Object doInCallableStatement(CallableStatement cs)
								throws SQLException, DataAccessException {
							// 初始化存储过程的人口参数

							cs.setString(1,simpledateformat.format(date));
							//执行存储过程
							cs.execute();
							return "";
						}
					});
			logger.info("开始更新上行信息成功.");
			return true;
		} catch (Exception e) {
			logger.error("开始更新上行信息失败!", e);
			return false;
		}
	}

}