/*** @ClassName: JdbcUtil
* @Description: 封装测试常用的数据库操作**/
public class JdbcUtil {
private static final Loglog = LogFactory.getLog(JdbcUtil.class);
/**
* 向指定表中插入数据
*
* @param jdbcTemplate
* 包含数据源信息的NamedParameterJdbcTemplate
* @param tableName
* @param columnNameValuePairs
*/
public static int insertData(NamedParameterJdbcTemplate jdbcTemplate,
String tableName, Map columnNameValuePairs) {
Assert.notNull(jdbcTemplate, "jdbcTemplate must not be null");
Assert.notNull(tableName, "tableName must not be null");
Assert.notEmpty(columnNameValuePairs,
"columnNameValuePairs must not be empty");
StringBuffer preSql =new StringBuffer("INSERT INTO " + tableName+"(");
StringBuffer afterSql =new StringBuffer(" VALUES(");
StringBuffer afterSqlToLog =new StringBuffer(" VALUES(");
Set columnNames = columnNameValuePairs.keySet();
for (String columnName : columnNames) {
preSql.append(columnName+",");
afterSql.append(":"+columnName+",");
afterSqlToLog.append(columnNameValuePairs.get(columnName)+",");
}
preSql.deleteCharAt(preSql.toString().length()-1).append(") ");
afterSql.deleteCharAt(afterSql.toString().length()-1).append(")");
afterSqlToLog.deleteCharAt(afterSqlToLog.toString().length()-1).append(")");
//待执行的sql
String sql = preSql.toString()+afterSql.toString();
String sqlToLog = preSql.toString()+afterSqlToLog.toString();
log.info(sqlToLog);
return jdbcTemplate.update(sql,columnNameValuePairs);
}
/**
* 根据id删除表（表必须有id这个字段）中数据
*
* @param jdbcTemplate
* 包含数据源信息的NamedParameterJdbcTemplate
* @param tableName
* @param id
*/
public static int deleteDataById(NamedParameterJdbcTemplate jdbcTemplate,
String tableName, Object idValue) {
Map columnNameValuePairs =new HashMap();
columnNameValuePairs.put("id", idValue);
return deleteData(jdbcTemplate, tableName, columnNameValuePairs);
}
/**
* 根据主键删除表中数据
*
* @param jdbcTemplate
* 包含数据源信息的NamedParameterJdbcTemplate
* @param tableName
* 表名
* @param primaryKeyName
* 主键字段名
* @param primaryKeyValue
*/
public static int deleteDataByPrimaryKey(NamedParameterJdbcTemplate jdbcTemplate,
String tableName, String primaryKeyName, Object primaryKeyValue) {
Map columnNameValuePairs =new HashMap();
columnNameValuePairs.put(primaryKeyName, primaryKeyValue);
return deleteData(jdbcTemplate, tableName, columnNameValuePairs);
}
/**
* 根据数据库表字段名称和值删除
*
* @param jdbcTemplate
* @param tableName
* @param columnNameValuePairs
* @return
*/
public static int deleteData(NamedParameterJdbcTemplate jdbcTemplate,
String tableName, Map columnNameValuePairs) {
Assert.notNull(jdbcTemplate, "jdbcTemplate must not be null");
Assert.notNull(tableName, "tableName must not be null");
Assert.notEmpty(columnNameValuePairs,
"columnNameValuePairs must not be empty");
StringBuffer sql =new StringBuffer("DELETE FROM " + tableName
+" WHERE 1=1 ");
StringBuffer sqlToLog =new StringBuffer(sql);
Set columnNames = columnNameValuePairs.keySet();
for (String columnName : columnNames) {
sql.append(" AND " + columnName +"=:" + columnName);
sqlToLog.append(" AND " + columnName +"='"
+ columnNameValuePairs.get(columnName) +"'");
}
log.info(sqlToLog);
return jdbcTemplate.update(sql.toString(), columnNameValuePairs);
}
/**
* 根据主键查询表（表必须有id这个字段）中数据
*
* @param jdbcTemplate
* 包含数据源信息的NamedParameterJdbcTemplate
* @param tableName
* 表名
* @param idValue
* id值
* @return
*/
public static List>queryDataById(
NamedParameterJdbcTemplate jdbcTemplate, String tableName, Object idValue) {
Map columnNameValuePairs =new HashMap();
columnNameValuePairs.put("id", idValue);
return queryData(jdbcTemplate, tableName, columnNameValuePairs);
}
/**
* 根据主键查询表中数据
*
* @param jdbcTemplate
* 包含数据源信息的NamedParameterJdbcTemplate
* @param tableName
* 表名
* @param primaryKeyName
* 主键字段名
* @param primaryKeyValue
* 主键值
* @return
*/
public static List>queryDataByPrimaryKey(
NamedParameterJdbcTemplate jdbcTemplate, String tableName,
String primaryKeyName, Object primaryKeyValue) {
Assert.notNull(jdbcTemplate, "jdbcTemplate must not be null");
Assert.notNull(tableName, "tableName must not be null");
Assert.notNull(primaryKeyName, "primaryKeyName must not be null");
Map columnNameValuePairs =new HashMap();
columnNameValuePairs.put(primaryKeyName, primaryKeyValue);
return queryData(jdbcTemplate, tableName, columnNameValuePairs);
}
/**
* 根据数据库表字段名称和值查询
*
* @param jdbcTemplate
* @param tableName
* @param columnNameValuePairs
* @return
*/
public static List>queryData(
NamedParameterJdbcTemplate jdbcTemplate, String tableName,
Map columnNameValuePairs) {
Assert.notNull(jdbcTemplate, "jdbcTemplate must not be null");
Assert.notNull(tableName, "tableName must not be null");
Assert.notEmpty(columnNameValuePairs,
"columnNameValuePairs must not be empty");
StringBuffer sql =new StringBuffer("SELECT * FROM " + tableName
+" WHERE 1=1 ");
StringBuffer sqlToLog =new StringBuffer(sql);
Set columnNames = columnNameValuePairs.keySet();
for (String columnName : columnNames) {
sql.append(" AND " + columnName +"=:" + columnName);
sqlToLog.append(" AND " + columnName +"='"
+ columnNameValuePairs.get(columnName) +"'");
}
log.info(sqlToLog);
return jdbcTemplate.queryForList(sql.toString(), columnNameValuePairs);
}
/**
* 根据主键判断表（表必须有id这个字段）中是否存在数据
*
* @param jdbcTemplate
* @param tableName
* @param idValue
* @return
*/
public static boolean isExistsInDb(NamedParameterJdbcTemplate jdbcTemplate,
String tableName, Object idValue) {
return queryDataById(jdbcTemplate, tableName, idValue).size() >0;
}
/**
* 根据主键判断表中是否存在数据
*
* @param jdbcTemplate
* @param tableName
* @param primaryKeyName
* @param primaryKeyValue
* @return
*/
public static boolean isExistsInDb(NamedParameterJdbcTemplate jdbcTemplate,
String tableName, String primaryKeyName,Object primaryKeyValue) {
return queryDataByPrimaryKey(jdbcTemplate, tableName, primaryKeyName,
primaryKeyValue).size() >0;
}
/**
*
* @param jdbcTemplate
* @param tableName
* @param columnNameValuePairs
* @return
*/
public static boolean isExistsInDb(NamedParameterJdbcTemplate jdbcTemplate,
String tableName, Map columnNameValuePairs) {
return queryData(jdbcTemplate, tableName, columnNameValuePairs).size() >0;
}
/**
* 组装Oracle分页查询语句
*
* @param sql
* 原始sql
* @param start
* 起始行
* @param max
* 最大记录数
* @return
*/
public static StringgetPagingSqlForOracle(String sql, int start, int max) {
StringBuffer pagingSelect =new StringBuffer();
pagingSelect
.append("SELECT * FROM (SELECT row_.*,rownum rownum_ FROM( ");
pagingSelect.append(sql);
pagingSelect.append(" ) row_ WHERE rownum <= " + (start + max)
+") WHERE rownum_ > " + start);
return pagingSelect.toString();
}
}
