Smsjk_siping/src/com/acc/common/jdbc/JdbcTemplateEx.java

package com.acc.common.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.context.ApplicationContext;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.dao.InvalidDataAccessApiUsageException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.ColumnMapRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ParameterDisposer;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.RowMapperResultSetExtractor;
import org.springframework.jdbc.core.SingleColumnRowMapper;
import org.springframework.jdbc.core.SqlProvider;
import org.springframework.jdbc.core.SqlTypeValue;
import org.springframework.jdbc.core.StatementCreatorUtils;
import org.springframework.jdbc.core.support.SqlLobValue;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.jdbc.support.incrementer.AbstractSequenceMaxValueIncrementer;
import org.springframework.util.Assert;

import com.acc.common.jdbc.oracle.OracleTypes;
import com.acc.common.spring.ApplicationFactory;

public class JdbcTemplateEx extends JdbcTemplate
{
	// 1
	private int firstResult;

	// 2
	private int lastResult;

	// 3
	public int getFirstResult()
	{
		return firstResult;
	}

	// 4
	public void setLastResult(int lastResult)
	{
		if (lastResult < 1)
			throw new InvalidDataAccessApiUsageException("lastResult cannot less 1 ");
		this.lastResult = lastResult;
	}

	// 5
	public void setFirstResult(int firstResult)
	{
		if (firstResult < 1)
			throw new InvalidDataAccessApiUsageException("firstResult cannot less 1 ");
		this.firstResult = firstResult;
	}

	// 6
	public int getLastResult()
	{
		return lastResult;
	}

	// 7
	public JdbcTemplateEx()
	{
		super();
		firstResult = 1;
		lastResult = Integer.MAX_VALUE;
	}

	// 8
	public JdbcTemplateEx(DataSource dataSource)
	{

		super(dataSource);
		firstResult = 1;
		lastResult = Integer.MAX_VALUE;
	}

	// 9
	public void insertMap(Map map, String tableName) throws DataAccessException
	{
		String keyStr = "( ";
		String valueStr = "( ";
		Object[] args = new Object[map.size()];
		Iterator it = map.keySet().iterator();
		for (int j = 0; it.hasNext(); j++)
		{
			String key = (String) it.next();
			keyStr = keyStr + key + ",";
			valueStr = valueStr + "?,";
			args[j] = map.get(key);
		}
		keyStr = keyStr.substring(0, keyStr.length() - 1) + " ) ";
		valueStr = valueStr.substring(0, valueStr.length() - 1) + " ) ";
		String insertSql = "insert into " + tableName + " " + keyStr + " values " + valueStr;
		update(insertSql, args);
	}

	// 10

	private void setPrimaryKey(Map map, String tableName, String primaryKeyName)
	{
		Object pkvalue = map.get(primaryKeyName);
		if (pkvalue == null)
			throw new InvalidDataAccessApiUsageException("PrimaryKey '" + primaryKeyName
					+ "' cannot find in map");

		if (pkvalue.toString().equals("-1"))
		{
			IDTableMaxValueIncrementer increamenter = new IDTableMaxValueIncrementer(this
					.getDataSource(), tableName);
			if (pkvalue instanceof Long)
			{
				map.put(primaryKeyName, new Long(increamenter.nextLongValue()));
			}
			else if (pkvalue instanceof Integer)
			{
				map.put(primaryKeyName, new Integer(increamenter.nextIntValue()));
			}
			else if (pkvalue instanceof String)
			{
				map.put(primaryKeyName, increamenter.nextStringValue());
			}
			else
			{
				throw new InvalidDataAccessApiUsageException("PrimaryKey '" + primaryKeyName
						+ "' value can't cast  Long/Integer/String");
			}
		}
		if (pkvalue.toString().equals("-2"))
		{
			AbstractSequenceMaxValueIncrementer incrementer = getSequenceIncrementer(tableName);
			if (incrementer == null) return;
			incrementer.setIncrementerName(incrementer.getIncrementerName() + tableName);
			if (pkvalue instanceof Long)
			{
				map.put(primaryKeyName, new Long(incrementer.nextLongValue()));
			}
			else if (pkvalue instanceof Integer)
			{
				map.put(primaryKeyName, new Integer(incrementer.nextIntValue()));
			}
			else if (pkvalue instanceof String)
			{
				map.put(primaryKeyName, incrementer.nextStringValue());
			}
			else
			{
				throw new InvalidDataAccessApiUsageException("PrimaryKey '" + primaryKeyName
						+ "' value can't cast  Long/Integer/String");
			}
		}
	}

	// 11
	public Object insertMap(Map map, String tableName, String primaryKeyName)
			throws DataAccessException
	{
		LobHelper lobhelper = getLobHelper();
		this.setPrimaryKey(map, tableName, primaryKeyName);
		String keyStr = "( ";
		String valueStr = "( ";
		Object[] args = new Object[map.size()];
		int[] types = new int[map.size()];
		Iterator it = map.keySet().iterator();
		int j = 0;
		while (it.hasNext())
		{
			String key = (String) it.next();
			Object val = map.get(key);
			if (val != null)
			{
				keyStr = keyStr + key + ",";
				valueStr = valueStr + "?,";
				args[j] = lobhelper.getObjectTransLob(val);
				types[j] = OracleTypes.getTypeByObject(args[j]);
				j++;
			}
		}
		keyStr = keyStr.substring(0, keyStr.length() - 1) + " ) ";
		valueStr = valueStr.substring(0, valueStr.length() - 1) + " ) ";
		String insertSql = "insert into " + tableName + " " + keyStr + " values " + valueStr;
		update(insertSql, args, types);
		return map.get(primaryKeyName);
	}

	// 12
	public void updateMap(Map map, String tableName, String primaryKeyName)
			throws DataAccessException
	{
		LobHelper lobhelper = getLobHelper();
		Object pkvalue = map.get(primaryKeyName);
		if (pkvalue == null)
			throw new InvalidDataAccessApiUsageException("Primary Key doesn't exist in Map");
		Object[] args = new Object[map.size()];
		int[] types = new int[map.size()];
		Iterator it = map.keySet().iterator();
		String updateSql = "update " + tableName + " set ";
		int j = 0;
		while (it.hasNext())
		{
			String keyName = (String) it.next();
			if (primaryKeyName.equalsIgnoreCase(keyName)) continue;

			updateSql = updateSql + keyName + " = ?,";
			args[j] = lobhelper.getObjectTransLob(map.get(keyName));
			types[j] = OracleTypes.getTypeByObject(args[j]);
			j++;
		}
		args[map.size() - 1] = pkvalue;
		types[map.size() - 1] = OracleTypes.getTypeByObject(args[map.size() - 1]);
		updateSql = updateSql.substring(0, updateSql.length() - 1) + "  where ";
		updateSql = updateSql + primaryKeyName + " = ? ";
		update(updateSql, args, types);

	}

	// 13
	public long getQueryCount(String sql) throws DataAccessException
	{
		if (sql == null || sql.equals(""))
		{
			return 0;
		}
		String lowercaseSql = sql.toLowerCase();
		int fromLocation = lowercaseSql.indexOf(" from ");
		sql = "select count(*) as count " + sql.substring(fromLocation, sql.length());
		if (lowercaseSql.indexOf("/*not-order*/") == -1)
		{
			lowercaseSql = sql.toLowerCase();
			int orderbyLocation = lowercaseSql.indexOf(" order by");
			if (lowercaseSql.indexOf("order by") != -1)
			{
				sql = sql.substring(0, orderbyLocation);
			}
		}
		return this.queryForLong(sql);
	}

	// 14
	public long getQueryCount(String sql, Object args[]) throws DataAccessException
	{
		if (sql == null || sql.equals(""))
		{
			return 0;
		}
		String lowercaseSql = sql.toLowerCase();
		int fromLocation = lowercaseSql.indexOf(" from ");
		sql = "select count(*) as count " + sql.substring(fromLocation, sql.length());
		lowercaseSql = sql.toLowerCase();
		int orderbyLocation = lowercaseSql.indexOf(" order by");
		if (lowercaseSql.indexOf("order by") != -1)
		{
			sql = sql.substring(0, orderbyLocation);
		}
		return this.queryForLong(sql, args);
	}

	// 16
	public List queryEx(String sql, RowMapper rowMapper) throws DataAccessException
	{
		return (List) this.query(sql, new RowCallbackHandlerResultSetExtractor(rowMapper));

	}

	// 17
	protected Object queryEx(PreparedStatementCreator psc, final PreparedStatementSetter pss,
			final ResultSetExtractor rse) throws DataAccessException
	{
		return super.query(psc, pss, rse);
	}

	// 18
	public Object queryEx(PreparedStatementCreator psc, ResultSetExtractor rse)
			throws DataAccessException
	{
		return queryEx(psc, null, rse);
	}

	// 19
	public Object queryEx(String sql, PreparedStatementSetter pss, final ResultSetExtractor rse)
			throws DataAccessException
	{
		if (sql == null)
		{
			throw new InvalidDataAccessApiUsageException("SQL may not be null");
		}
		return queryEx(new SimplePreparedStatementCreator(sql), pss, rse);
	}

	// 20
	public Object query(String sql, Object[] args, int[] argTypes, ResultSetExtractor rse)
			throws DataAccessException
	{
		return query(sql, new ArgTypePreparedStatementSetter(args, argTypes), rse);
	}

	// 21
	public Object query(String sql, Object[] args, ResultSetExtractor rse)
			throws DataAccessException
	{
		return query(sql, new ArgPreparedStatementSetter(args), rse);
	}

	// 25
	public List queryEx(String sql, Object[] args, ResultSetExtractor rch)
			throws DataAccessException
	{
		return (List) queryEx(sql, new ArgPreparedStatementSetter(args), rch);
	}

	// 26
	public List query(PreparedStatementCreator psc, RowMapper rowMapper) throws DataAccessException
	{
		return (List) queryEx(psc, new RowMapperResultSetExtractor(rowMapper));
	}

	// 29
	public List queryEx(String sql, Object[] args, RowMapper rowMapper) throws DataAccessException
	{
		return queryEx(sql, args, new RowCallbackHandlerResultSetExtractor(rowMapper));
	}

	// 30
	private static class SimplePreparedStatementCreator implements PreparedStatementCreator,
			SqlProvider
	{

		private final String sql;

		public SimplePreparedStatementCreator(String sql)
		{
			this.sql = sql;
		}

		public PreparedStatement createPreparedStatement(Connection con) throws SQLException
		{
			return con.prepareStatement(this.sql);
		}

		public String getSql()
		{
			return sql;
		}
	}

	// 31
	private static class ArgPreparedStatementSetter implements PreparedStatementSetter,
			ParameterDisposer
	{

		private final Object[] args;

		public ArgPreparedStatementSetter(Object[] args)
		{
			this.args = args;
		}

		public void setValues(PreparedStatement ps) throws SQLException
		{
			if (this.args == null) return;
			for (int i = 0; i < this.args.length; i++)
			{
				if (args[i] instanceof SqlLobValue)
				{
					StatementCreatorUtils.setParameterValue(ps, i + 1, Types.CLOB, null,
							this.args[i]);
				}
				else
				{
					StatementCreatorUtils.setParameterValue(ps, i + 1, SqlTypeValue.TYPE_UNKNOWN,
							null, this.args[i]);
				}
			}

		}

		public void cleanupParameters()
		{
			StatementCreatorUtils.cleanupParameters(this.args);
		}
	}

	// 32
	private static class ArgTypePreparedStatementSetter implements PreparedStatementSetter,
			ParameterDisposer
	{

		private final Object[] args;

		private final int[] argTypes;

		public ArgTypePreparedStatementSetter(Object[] args, int[] argTypes)
		{
			if ((args != null && argTypes == null) || (args == null && argTypes != null)
					|| (args != null && args.length != argTypes.length))
			{
				throw new InvalidDataAccessApiUsageException(
						"args and argTypes parameters must match");
			}
			this.args = args;
			this.argTypes = argTypes;
		}

		public void setValues(PreparedStatement ps) throws SQLException
		{
			if (this.args == null) return;
			for (int i = 0; i < this.args.length; i++)
			{
				StatementCreatorUtils.setParameterValue(ps, i + 1, this.argTypes[i], null,
						this.args[i]);
			}

		}

		public void cleanupParameters()
		{
			StatementCreatorUtils.cleanupParameters(this.args);
		}
	}

	// 33
	private class RowCallbackHandlerResultSetExtractor<T> implements ResultSetExtractor<List<T>>
	{

		private final RowMapper<T> rowMapper;

		private final int rowsExpected;

		/**
		 * Create a new RowMapperResultSetExtractor.
		 * 
		 * @param rowMapper the RowMapper which creates an object for each row
		 */
		public RowCallbackHandlerResultSetExtractor(RowMapper<T> rowMapper)
		{
			this(rowMapper, 0);
		}

		/**
		 * Create a new RowMapperResultSetExtractor.
		 * 
		 * @param rowMapper the RowMapper which creates an object for each row
		 * @param rowsExpected the number of expected rows (just used for optimized collection
		 *            handling)
		 */
		public RowCallbackHandlerResultSetExtractor(RowMapper<T> rowMapper, int rowsExpected)
		{
			Assert.notNull(rowMapper, "RowMapper is required");
			this.rowMapper = rowMapper;
			this.rowsExpected = rowsExpected;
		}

		public List<T> extractData(ResultSet rs) throws SQLException
		{
			List<T> results = (this.rowsExpected > 0 ? new ArrayList<T>(this.rowsExpected)
					: new ArrayList<T>());
			int rowNum = 0;
			boolean isnext = true;

			int start = JdbcTemplateEx.this.getFirstResult();
			int end = JdbcTemplateEx.this.getLastResult();

			if (start > end)
				throw new SQLException(
						"JdbcTemplateEx Start postion greator than JdbcTemplateEx end");
			for (int i = 1; i <= start; i++)
			{
				isnext = rs.next();
			}
			for (int i = start; i <= end && isnext; i++)
			{
				int x = 1;
				results.add(this.rowMapper.mapRow(rs, x++));
				isnext = rs.next();
			}
			return results;
		}

	}

	// 34
	public List queryForList(String sql, Class elementType) throws DataAccessException
	{
		return queryEx(sql, new SingleColumnRowMapper(elementType));
	}

	// 35
	public List queryForList(String sql) throws DataAccessException
	{
		return queryEx(sql, this.getColumnMapRowMapperEx());
	}

	// 37
	public List queryForList(String sql, final Object[] args, Class elementType)
			throws DataAccessException
	{
		return queryEx(sql, args, new SingleColumnRowMapper(elementType));
	}

	// 39
	public List queryForList(String sql, final Object[] args) throws DataAccessException
	{
		return queryEx(sql, args, this.getColumnMapRowMapperEx());
	}

	// 39
	public List queryForList(String sql, final String arg) throws DataAccessException
	{
		return queryEx(sql, new Object[] { arg }, this.getColumnMapRowMapperEx());

	}

	// 40
	public Map queryForMap(String sql, Object[] args, int[] argTypes) throws DataAccessException
	{
		try
		{
			return (Map) queryForObject(sql, args, argTypes, this.getColumnMapRowMapperEx());
		}
		catch (IncorrectResultSizeDataAccessException e)
		{
			return new HashMap();
		}
	}

	// 41
	public Map queryForMap(String sql) throws DataAccessException
	{
		try
		{
			return (Map) queryForObject(sql, this.getColumnMapRowMapperEx());
		}
		catch (IncorrectResultSizeDataAccessException e)
		{
			return new HashMap();
		}
	}

	// 42
	public Map queryForMap(String sql, Object[] args) throws DataAccessException
	{
		try
		{
			return (Map) queryForObject(sql, args, this.getColumnMapRowMapperEx());
		}
		catch (IncorrectResultSizeDataAccessException e)
		{
			return new HashMap();
		}
	}

	public Map queryForMap(String sql, String arg) throws DataAccessException
	{
		try
		{
			return (Map) queryForObject(sql, new Object[] { arg }, this.getColumnMapRowMapperEx());
		}
		catch (IncorrectResultSizeDataAccessException e)
		{
			return new HashMap();
		}
	}

	// 43
	public String queryForString(String sql) throws DataAccessException
	{
		try
		{
			return (String) queryForObject(sql, String.class);
		}
		catch (IncorrectResultSizeDataAccessException e)
		{
			return "";
		}
	}

	// 44
	public String queryForString(String sql, Object args[]) throws DataAccessException
	{
		try
		{
			return (String) queryForObject(sql, args, String.class);
		}
		catch (IncorrectResultSizeDataAccessException e)
		{
			return "";
		}

	}

	// 45
	public int update(String sql, final Object[] args) throws DataAccessException
	{
		return update(sql, new ArgPreparedStatementSetter(args));
	}

	// 45
	public int update(String sql, final Object[] args, int[] types) throws DataAccessException
	{
		return super.update(sql, args, types);
	}

	// 46
	public int[] batchUpdate(String sql, final BatchPreparedStatementSetter pss)
			throws DataAccessException
	{
		if (logger.isDebugEnabled())
		{
			logger.debug("Executing SQL batch update [" + sql + "]");
		}
		return (int[]) execute(sql, new PreparedStatementCallback()
		{
			public Object doInPreparedStatement(PreparedStatement ps) throws SQLException
			{
				try
				{
					int batchSize = pss.getBatchSize();
					if (JdbcUtils.supportsBatchUpdates(ps.getConnection()))
					{
						for (int i = 0; i < batchSize; i++)
						{
							pss.setValues(ps, i);
							ps.addBatch();
						}
						return ps.executeBatch();
					}
					else
					{
						int[] rowsAffected = new int[batchSize];
						for (int i = 0; i < batchSize; i++)
						{
							pss.setValues(ps, i);
							rowsAffected[i] = ps.executeUpdate();
						}
						return rowsAffected;
					}
				}
				finally
				{
					if (pss instanceof ParameterDisposer)
					{
						((ParameterDisposer) pss).cleanupParameters();
					}
				}
			}
		});
	}

	// 47
	public boolean hasResult(String sql) throws DataAccessException
	{
		this.setFirstResult(1);
		this.setLastResult(1);
		List list = queryForList(sql);
		this.setFirstResult(1);
		this.setLastResult(Integer.MAX_VALUE);
		if (list == null) return false;
		if (list.size() == 0) return false;
		return true;
	}

	// 48
	public boolean hasResult(String sql, Object args[]) throws DataAccessException
	{
		this.setFirstResult(1);
		this.setLastResult(1);
		List list = queryForList(sql, args);
		this.setFirstResult(1);
		this.setLastResult(Integer.MAX_VALUE);
		if (list == null) return false;
		if (list.size() == 0) return false;
		return true;
	}

	// 49
	private LobHelper getLobHelper()
	{
		ApplicationContext ctx = ApplicationFactory.getContext();
		if (ctx.containsBeanDefinition("lobHelper"))
		{
			return (LobHelper) ctx.getBean("lobHelper");
		}
		return null;
	}

	// 50
	private RowMapper getColumnMapRowMapperEx()
	{
		ApplicationContext ctx = ApplicationFactory.getContext();
		if (ctx.containsBeanDefinition("dataTypeMapping"))
		{
			return (RowMapper) ctx.getBean("dataTypeMapping");
		}
		return new ColumnMapRowMapper();
	}

	// 51
	private AbstractSequenceMaxValueIncrementer getSequenceIncrementer(String tableName)
	{
		ApplicationContext ctx = ApplicationFactory.getContext();
		if (ctx.containsBeanDefinition("SequenceIncrementer"))
		{
			AbstractSequenceMaxValueIncrementer sequenceIncrementer = (AbstractSequenceMaxValueIncrementer) ctx
					.getBean("SequenceIncrementer");
			sequenceIncrementer.setDataSource(this.getDataSource());
			return sequenceIncrementer;
		}
		return null;
	}
}