private static String getSql(final String sql, String sqlPre)
{
String maxRow = FileManager.getESBProperty("MAX_ROW");
if ((maxRow != null) && (!"".equals(maxRow)))
{
int pos = sql.indexOf("order by");
if (pos == -1)
{
pos = sql.indexOf("ORDER BY");
if (pos == -1)
pos = sql.indexOf("group by");
if (pos == -1)
pos = sql.indexOf("ORDER BY");
}
if (pos > 0)
{
sqlPre = sql.substring(0, pos - 1) + " and rownum < " + maxRow
+ " " + sql.substring(pos);
}
}
return sqlPre;
}
/*
* 简单的通用数据库Insert/Update方法,提供给非标准JAVA客户端使用(入参只支持String)
*
* @param sql sql语句,参数用?
*
* @param param 参数值,参数值的顺利必须与sql语句中的参数(?)匹配(使用||分隔的字符串)
*
* @return void
*/
public static void execSQL(String sql, String param)
{
String[] tmp = param.split("\\|\\|");
ArrayList<String> paramList = new ArrayList<String>();
for (int i = 0; i < tmp.length; i++)
{
paramList.add(tmp[i]);
}
execSQL(sql, paramList);
}
/*
* 简单的通用数据库Insert/Update方法
*
* @param sql sql语句,参数用?
*
* @param param 参数值,参数值的顺利必须与sql语句中的参数(?)匹配
*
* @return void
*/
public static void execSQL(final String sql, List<String> param)
{
logger.info("Get connection begin ...");
Connection conn = getConnection();
logger.info("Get connection Successful.");
PreparedStatement ps = null;
try
{
logger.info("Prepare sql statement:[" + sql + "]");
ps = conn.prepareStatement(sql);
logger.info("Prepare sql parameters:" + param.toString());
for (int i = 0; i < param.size(); i++)
{
ps.setString(i + 1, (String) param.get(i));
}
logger.info("Execute sql begin ...");
ps.execute();
logger.info("Execute sql Successful.");
}
catch (Exception e)
{
logger.error("DataBase execute exception! Message:" + e.toString()
+ "\n\t\tSqlText: " + sql + "\n\t\tparam: "
+ param.toString());
throw new BaseException(e);
}
finally
{
if (null != ps)
{
try
{
ps.close();
logger.info("Close PreparedStatement Successful.");
}
catch (SQLException e)
{
logger.error("Close PreparedStatement error! Message:"
+ e.toString());
throw new BaseException(e);
}
}
if (null != conn)
{
try
{
conn.close();
logger.info("Close Connection Successful.");
}
catch (SQLException e)
{
logger.error("Close connection error! message:"
+ e.toString());
throw new BaseException(e);
}
}
}
}
{
String maxRow = FileManager.getESBProperty("MAX_ROW");
if ((maxRow != null) && (!"".equals(maxRow)))
{
int pos = sql.indexOf("order by");
if (pos == -1)
{
pos = sql.indexOf("ORDER BY");
if (pos == -1)
pos = sql.indexOf("group by");
if (pos == -1)
pos = sql.indexOf("ORDER BY");
}
if (pos > 0)
{
sqlPre = sql.substring(0, pos - 1) + " and rownum < " + maxRow
+ " " + sql.substring(pos);
}
}
return sqlPre;
}
/*
* 简单的通用数据库Insert/Update方法,提供给非标准JAVA客户端使用(入参只支持String)
*
* @param sql sql语句,参数用?
*
* @param param 参数值,参数值的顺利必须与sql语句中的参数(?)匹配(使用||分隔的字符串)
*
* @return void
*/
public static void execSQL(String sql, String param)
{
String[] tmp = param.split("\\|\\|");
ArrayList<String> paramList = new ArrayList<String>();
for (int i = 0; i < tmp.length; i++)
{
paramList.add(tmp[i]);
}
execSQL(sql, paramList);
}
/*
* 简单的通用数据库Insert/Update方法
*
* @param sql sql语句,参数用?
*
* @param param 参数值,参数值的顺利必须与sql语句中的参数(?)匹配
*
* @return void
*/
public static void execSQL(final String sql, List<String> param)
{
logger.info("Get connection begin ...");
Connection conn = getConnection();
logger.info("Get connection Successful.");
PreparedStatement ps = null;
try
{
logger.info("Prepare sql statement:[" + sql + "]");
ps = conn.prepareStatement(sql);
logger.info("Prepare sql parameters:" + param.toString());
for (int i = 0; i < param.size(); i++)
{
ps.setString(i + 1, (String) param.get(i));
}
logger.info("Execute sql begin ...");
ps.execute();
logger.info("Execute sql Successful.");
}
catch (Exception e)
{
logger.error("DataBase execute exception! Message:" + e.toString()
+ "\n\t\tSqlText: " + sql + "\n\t\tparam: "
+ param.toString());
throw new BaseException(e);
}
finally
{
if (null != ps)
{
try
{
ps.close();
logger.info("Close PreparedStatement Successful.");
}
catch (SQLException e)
{
logger.error("Close PreparedStatement error! Message:"
+ e.toString());
throw new BaseException(e);
}
}
if (null != conn)
{
try
{
conn.close();
logger.info("Close Connection Successful.");
}
catch (SQLException e)
{
logger.error("Close connection error! message:"
+ e.toString());
throw new BaseException(e);
}
}
}
}