package com.beans;
import java.io.Serializable;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
@SuppressWarnings("serial")
public class PoJo implements Serializable {
private static final Logger logger = LoggerFactory.getLogger(PoJo.class);
JdbcTemplate jdbcTemplate;
public JdbcTemplate getJdbcTemplate() {
return jdbcTemplate;
}
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
String _tableName() {
return "base_teacher";
}
StringBuilder _SqlSave(Serializable entity) {
StringBuilder _save = new StringBuilder();
try {
boolean flag_value = false;
Field[] fields = entity.getClass().getDeclaredFields();
for (Field field : fields) {
if ("INSTANCE".equals(field.getName())) {
continue;
}
String getFn = "get" + field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1);
Method method = entity.getClass().getMethod(getFn);
Object obj = method.invoke(entity);
if (obj != null) {
flag_value = true;
_save.append(field.getName()).append(",");
}
}
if (!flag_value)
return _save;
_save.insert(0, String.format("insert into %s (", _tableName()));
_save.deleteCharAt(_save.length() - 1).append(")");
// 拼接值
_save.append("values").append("(");
for (Field field : fields) {
if ("INSTANCE".equals(field.getName())) {
continue;
}
String getFn = "get" + field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1);
Method method = entity.getClass().getMethod(getFn);
Object obj = method.invoke(entity);
if (obj != null) {
_save.append("?,");
}
}
_save.deleteCharAt(_save.length() - 1).append(")");
} catch (SecurityException e) {
logger.info("异常信息{}",e);
} catch (NoSuchMethodException e) {
logger.info("异常信息{}",e);
} catch (IllegalAccessException e) {
logger.info("异常信息{}",e);
} catch (IllegalArgumentException e) {
logger.info("异常信息{}",e);
} catch (InvocationTargetException e) {
logger.info("异常信息{}",e);
}
return _save;
}
StringBuilder _SqlUpdate(Serializable entity) {
StringBuilder _update = new StringBuilder();
try {
_update.append("update ").append(_tableName()).append(" set ");
Field[] fields = entity.getClass().getDeclaredFields();
for (Field field : fields) {
if ("INSTANCE".equals(field.getName())) {
continue;
}
if ("id".equals(field.getName().toLowerCase())) {
continue;
}
String getFn = "get" + field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1);
Method method = entity.getClass().getMethod(getFn);
Object obj = method.invoke(entity);
if (obj != null) {
_update.append(field.getName()).append(" = ? ,");
}
}
_update.deleteCharAt(_update.length() - 1).append(" where id = ?");
} catch (SecurityException e) {
logger.info("异常信息{}",e);
} catch (NoSuchMethodException e) {
logger.info("异常信息{}",e);
} catch (IllegalAccessException e) {
logger.info("异常信息{}",e);
} catch (IllegalArgumentException e) {
logger.info("异常信息{}",e);
} catch (InvocationTargetException e) {
logger.info("异常信息{}",e);
}
return _update;
}
StringBuilder _SqlDel(Serializable entity) {
StringBuilder _del = new StringBuilder();
try {
_del.append("delete ").append(_tableName()).append(" where ");
Field[] fields = entity.getClass().getDeclaredFields();
for (Field field : fields) {
if ("INSTANCE".equals(field.getName())) {
continue;
}
String getFn = "get" + field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1);
Method method = entity.getClass().getMethod(getFn);
Object obj = method.invoke(entity);
if (obj != null) {
_del.append(field.getName()).append("=? and ");
}
}
_del.delete(_del.length() - 4,_del.length());
} catch (SecurityException e) {
logger.info("异常信息{}",e);
} catch (NoSuchMethodException e) {
logger.info("异常信息{}",e);
} catch (IllegalAccessException e) {
logger.info("异常信息{}",e);
} catch (IllegalArgumentException e) {
logger.info("异常信息{}",e);
} catch (InvocationTargetException e) {
logger.info("异常信息{}",e);
}
return _del;
}
StringBuilder _SqlWhere(Serializable entity) {
StringBuilder _where = new StringBuilder();
try {
Field[] fields = entity.getClass().getDeclaredFields();
for (Field field : fields) {
if ("INSTANCE".equals(field.getName())) {
continue;
}
String getFn = "get" + field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1);
Method method = entity.getClass().getMethod(getFn);
Object obj = method.invoke(entity);
if (obj != null) {
_where.append(field.getName()).append("=? and ");
}
}
if (_where.length() > 5) {
_where.delete(_where.length() - 4,_where.length());
}
} catch (SecurityException e) {
logger.info("异常信息{}",e);
} catch (NoSuchMethodException e) {
logger.info("异常信息{}",e);
} catch (IllegalAccessException e) {
logger.info("异常信息{}",e);
} catch (IllegalArgumentException e) {
logger.info("异常信息{}",e);
} catch (InvocationTargetException e) {
logger.info("异常信息{}",e);
}
return _where;
}
StringBuilder _SqlWhere(Map<String, Object> param) {
StringBuilder _where = new StringBuilder();
try {
for(String key:param.keySet())
{
_where.append(key).append(" = ? and ");
}
if (_where.length() > 5) {
_where.delete(_where.length() - 4,_where.length());
}
} catch (SecurityException e) {
logger.info("异常信息{}",e);
}
return _where;
}
Object[] _Values(Serializable entity) {
try {
List<Object> values = new LinkedList<Object>();
Field[] fields = entity.getClass().getDeclaredFields();
for (Field field : fields) {
if ("INSTANCE".equals(field.getName())) {
continue;
}
String getFn = "get" + field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1);
Method method = entity.getClass().getMethod(getFn);
Object obj = method.invoke(entity);
if (obj != null) {
values.add(obj);
}
}
return values.toArray();
} catch (SecurityException e) {
logger.info("异常信息{}",e);
} catch (NoSuchMethodException e) {
logger.info("异常信息{}",e);
} catch (IllegalAccessException e) {
logger.info("异常信息{}",e);
} catch (IllegalArgumentException e) {
logger.info("异常信息{}",e);
} catch (InvocationTargetException e) {
logger.info("异常信息{}",e);
}
return null;
}
List<Object> _LValues(Serializable entity) {
List<Object> values = new LinkedList<Object>();
try {
Field[] fields = entity.getClass().getDeclaredFields();
for (Field field : fields) {
if ("INSTANCE".equals(field.getName())) {
continue;
}
String getFn = "get" + field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1);
Method method = entity.getClass().getMethod(getFn);
Object obj = method.invoke(entity);
if (obj != null) {
values.add(obj);
}
}
} catch (SecurityException e) {
logger.info("异常信息{}",e);
} catch (NoSuchMethodException e) {
logger.info("异常信息{}",e);
} catch (IllegalAccessException e) {
logger.info("异常信息{}",e);
} catch (IllegalArgumentException e) {
logger.info("异常信息{}",e);
} catch (InvocationTargetException e) {
logger.info("异常信息{}",e);
}
return values;
}
Object[] _Values(Map<String, Object> param) {
try {
List<Object> values = new LinkedList<Object>();
for (String key : param.keySet()) {
if ("INSTANCE".equals(key.toUpperCase())) {
continue;
}
values.add(param.get(key));
}
return values.toArray();
} catch (SecurityException e) {
logger.info("异常信息{}",e);
}
return null;
}
List<Object> _LValues(Map<String, Object> param) {
List<Object> values = new LinkedList<Object>();
try {
for (String key : param.keySet()) {
if ("INSTANCE".equals(key.toUpperCase())) {
continue;
}
values.add(param.get(key));
}
} catch (SecurityException e) {
logger.info("异常信息{}",e);
}
return values;
}
void _setIValues(Serializable entity, PreparedStatement ps) {
try {
Field[] fields = entity.getClass().getDeclaredFields();
int i = 1;
for (Field field : fields) {
if ("INSTANCE".equals(field.getName())) {
continue;
}
String getFn = "get" + field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1);
Method method = entity.getClass().getMethod(getFn);
Object obj = method.invoke(entity);
if (obj != null) {
try {
ps.setObject(i, obj);
} catch (SQLException e) {
logger.info("异常信息{}",e);
continue;
}
i++;
}
}
} catch (SecurityException e) {
logger.info("异常信息{}",e);
} catch (NoSuchMethodException e) {
logger.info("异常信息{}",e);
} catch (IllegalAccessException e) {
logger.info("异常信息{}",e);
} catch (IllegalArgumentException e) {
logger.info("异常信息{}",e);
} catch (InvocationTargetException e) {
logger.info("异常信息{}",e);
}
}
void _setUValues(Serializable entity, PreparedStatement ps) {
try {
Field[] fields = entity.getClass().getDeclaredFields();
int i = 1;
for (Field field : fields) {
if ("INSTANCE".equals(field.getName())) {
continue;
}
if ("id".equals(field.getName().toLowerCase())) {
continue;
}
String getFn = "get" + field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1);
Method method = entity.getClass().getMethod(getFn);
Object obj = method.invoke(entity);
if (obj != null) {
try {
ps.setObject(i, obj);
} catch (SQLException e) {
logger.info("异常信息{}",e);
continue;
}
i++;
}
}
Method method = entity.getClass().getMethod("getId");
Object obj = method.invoke(entity);
ps.setObject(i, obj);
} catch (SecurityException e) {
logger.info("异常信息{}",e);
} catch (NoSuchMethodException e) {
logger.info("异常信息{}",e);
} catch (IllegalAccessException e) {
logger.info("异常信息{}",e);
} catch (IllegalArgumentException e) {
logger.info("异常信息{}",e);
} catch (InvocationTargetException e) {
logger.info("异常信息{}",e);
} catch (SQLException e) {
logger.info("异常信息{}",e);
}
}
List<Object[]> _ListValues(List<?> lst_vo) {
List<Object[]> lst_orgs = new ArrayList<Object[]>();
for (Object entity : lst_vo) {
try {
List<Object> values = new LinkedList<Object>();
Field[] fields = entity.getClass().getDeclaredFields();
for (Field field : fields) {
if ("INSTANCE".equals(field.getName())) {
continue;
}
String getFn = "get" + field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1);
Method method = entity.getClass().getMethod(getFn);
Object obj = method.invoke(entity);
if (obj != null) {
values.add(obj);
}
}
lst_orgs.add(values.toArray());
} catch (SecurityException e) {
logger.info("异常信息{}",e);
} catch (NoSuchMethodException e) {
logger.info("异常信息{}",e);
} catch (IllegalAccessException e) {
logger.info("异常信息{}",e);
} catch (IllegalArgumentException e) {
logger.info("异常信息{}",e);
} catch (InvocationTargetException e) {
logger.info("异常信息{}",e);
}
}
System.out.println(Arrays.toString(lst_orgs.toArray()));
return lst_orgs;
}
Object[] _UpdateValues(Serializable entity) {
try {
List<Object> values = new LinkedList<Object>();
Field[] fields = entity.getClass().getDeclaredFields();
for (Field field : fields) {
if ("INSTANCE".equals(field.getName())) {
continue;
}
if ("id".equals(field.getName().toLowerCase())) {
continue;
}
String getFn = "get" + field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1);
Method method = entity.getClass().getMethod(getFn);
Object obj = method.invoke(entity);
if (obj != null) {
values.add(obj);
}
}
Method method = entity.getClass().getMethod("getId");
Object obj = method.invoke(entity);
values.add(obj);
return values.toArray();
} catch (SecurityException e) {
logger.info("异常信息{}",e);
} catch (NoSuchMethodException e) {
logger.info("异常信息{}",e);
} catch (IllegalAccessException e) {
logger.info("异常信息{}",e);
} catch (IllegalArgumentException e) {
logger.info("异常信息{}",e);
} catch (InvocationTargetException e) {
logger.info("异常信息{}",e);
}
return null;
}
List<Object[]> _UpdateListValues(List<?> lst_vo) {
List<Object[]> lst_orgs = new ArrayList<Object[]>();
for (Object entity : lst_vo) {
try {
List<Object> values = new LinkedList<Object>();
Field[] fields = entity.getClass().getDeclaredFields();
for (Field field : fields) {
if ("INSTANCE".equals(field.getName())) {
continue;
}
if ("id".equals(field.getName().toLowerCase())) {
continue;
}
String getFn = "get" + field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1);
Method method = entity.getClass().getMethod(getFn);
Object obj = method.invoke(entity);
if (obj != null) {
values.add(obj);
}
}
Method method = entity.getClass().getMethod("getId");
Object obj = method.invoke(entity);
values.add(obj);
lst_orgs.add(values.toArray());
} catch (SecurityException e) {
logger.info("异常信息{}",e);
} catch (NoSuchMethodException e) {
logger.info("异常信息{}",e);
} catch (IllegalAccessException e) {
logger.info("异常信息{}",e);
} catch (IllegalArgumentException e) {
logger.info("异常信息{}",e);
} catch (InvocationTargetException e) {
logger.info("异常信息{}",e);
}
}
return lst_orgs;
}
Object[] _DelValues(Serializable entity) {
try {
List<Object> values = new LinkedList<Object>();
Field[] fields = entity.getClass().getDeclaredFields();
for (Field field : fields) {
if ("INSTANCE".equals(field.getName())) {
continue;
}
String getFn = "get" + field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1);
Method method = entity.getClass().getMethod(getFn);
Object obj = method.invoke(entity);
if (obj != null) {
values.add(obj);
}
}
return values.toArray();
} catch (SecurityException e) {
logger.info("异常信息{}",e);
} catch (NoSuchMethodException e) {
logger.info("异常信息{}",e);
} catch (IllegalAccessException e) {
logger.info("异常信息{}",e);
} catch (IllegalArgumentException e) {
logger.info("异常信息{}",e);
} catch (InvocationTargetException e) {
logger.info("异常信息{}",e);
}
return null;
}
public Map<String, Object> _queryMapOneBySql(String sql) {
try {
if (sql.indexOf("rownum") == -1) {
if (sql.indexOf("where") == -1) {
sql += " where ";
}
sql += " rownum = 1 ";
}
logger.info("执行SQL[{}]",sql);
return jdbcTemplate.queryForMap(sql);
} catch (DataAccessException e) {
logger.info("异常信息{}",e);
}
return new HashMap<>();
}
public List<Map<String, Object>> _queryMapListBySql(String sql) {
try {
logger.info("执行SQL[{}]",sql);
return jdbcTemplate.queryForList(sql);
} catch (DataAccessException e) {
logger.info("异常信息{}",e);
}
return new ArrayList<>();
}
public List<Map<String, Object>> _queryPagingListMapBySql(String sql, int start, int end) {
try {
String pgSQL = "SELECT * FROM (SELECT a.*, ROWNUM rn FROM (%s) a WHERE ROWNUM <= %d) WHERE rn >= %d";
pgSQL = String.format(pgSQL, sql, end, start);
// Sql转大写使用
logger.info("执行SQL[{}]",pgSQL.toUpperCase());
return jdbcTemplate.queryForList(pgSQL.toUpperCase());
} catch (DataAccessException e) {
logger.info("异常信息{}",e);
}
return new ArrayList<>();
}
public int _statBySql(String sql) {
try {
String statSql = "SELECT count(*) FROM (%s) ";
statSql = String.format(statSql, sql);
// Sql转大写使用
logger.info("执行SQL[{}]",statSql);
return jdbcTemplate.queryForObject(statSql, Integer.class);
} catch (DataAccessException e) {
logger.info("异常信息{}",e);
}
return 0;
}
public <T> int Save() {
try {
logger.info("执行SQL[{}]",_SqlSave(this).toString());
return jdbcTemplate.update(_SqlSave(this).toString(), _Values(this));
} catch (DataAccessException e) {
logger.info("异常信息{}",e);
}
return 0;
}
public <T> int[] Saves(final List<? extends Serializable> lst_vo) {
try {
BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() {
public int getBatchSize() {
return lst_vo.size();
}
public void setValues(PreparedStatement ps, int i) {
try {
_setIValues(lst_vo.get(i), ps);
} catch (Exception e) {
logger.info("异常信息{}",e);
}
}
};
logger.info("执行SQL[{}]",_SqlSave(lst_vo.get(0)).toString());
return jdbcTemplate.batchUpdate(_SqlSave(lst_vo.get(0)).toString(),setter);
} catch (DataAccessException e) {
logger.info("异常信息{}",e);
}
return null;
}
public <T> int Update() {
try {
logger.info("执行SQL[{}]",_SqlUpdate(this).toString());
return jdbcTemplate.update(_SqlUpdate(this).toString(), _UpdateValues(this));
} catch (DataAccessException e) {
logger.info("异常信息{}",e);
}
return 0;
}
public <T> int Del() {
try {
logger.info("执行SQL[{}]",_SqlDel(this).toString());
return jdbcTemplate.update(_SqlDel(this).toString(), _DelValues(this));
} catch (DataAccessException e) {
logger.info("异常信息{}",e);
}
return 0;
}
public <T> int[] BatDel(final List<? extends Serializable> lst_vo) {
try {
BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() {
public int getBatchSize() {
return lst_vo.size();
}
public void setValues(PreparedStatement ps, int i) {
try {
_setIValues(lst_vo.get(i), ps);
} catch (Exception e) {
logger.info("异常信息{}",e);
}
}
};
logger.info("执行批量SQL[{}]",_SqlDel(lst_vo.get(0)).toString());
return jdbcTemplate.batchUpdate(_SqlDel(lst_vo.get(0)).toString(),setter);
} catch (DataAccessException e) {
logger.info("异常信息{}",e);
}
return null;
}
public <T> int DelById(String id) {
try {
String sql = String.format("delete from %s where id = ? ",_tableName());
logger.info("执行删除SQL[{}],参数id[{}]",sql,id);
return jdbcTemplate.update(sql,id);
} catch (DataAccessException e) {
logger.info("异常信息{}",e);
}
return 0;
}
public <T> int DelById(int id) {
try {
String sql = String.format("delete from %s where id = ? ",_tableName());
logger.info("执行删除SQL[{}],参数id[{}]",sql,id);
return jdbcTemplate.update(sql,id);
} catch (DataAccessException e) {
logger.info("异常信息{}",e);
}
return 0;
}
public <T> int DelById(Integer id) {
try {
String sql = String.format("delete from %s where id = ? ",_tableName());
logger.info("执行删除SQL[{}]",sql);
return jdbcTemplate.update(sql,id);
} catch (DataAccessException e) {
logger.info("异常信息{}",e);
}
return 0;
}
public <T> int[] Update(final List<? extends Serializable> lst_vo) {
try {
BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() {
public int getBatchSize() {
return lst_vo.size();
}
public void setValues(PreparedStatement ps, int i) {
try {
_setUValues(lst_vo.get(i), ps);
} catch (Exception e) {
logger.info("异常信息{}",e);
}
}
};
logger.info("执行SQL[{}]",_SqlUpdate(lst_vo.get(0)).toString());
return jdbcTemplate.batchUpdate(_SqlUpdate(lst_vo.get(0)).toString(),setter);
} catch (DataAccessException e) {
logger.info("异常信息{}",e);
}
return null;
}
public <T> int SaveAutoKey_int() {
return _saveAutoKey(this.getClass()).intValue();
}
public <T> long SaveAutoKey_long() {
return _saveAutoKey(this.getClass()).longValue();
}
public <T> double SaveAutoKey_double() {
return _saveAutoKey(this.getClass()).doubleValue();
}
public <T> float SaveAutoKey_float() {
return _saveAutoKey(this.getClass()).floatValue();
}
public <T> Number _saveAutoKey(final Class<T> beanClass) {
KeyHolder keyHolder = new GeneratedKeyHolder();
try {
jdbcTemplate.update(new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
PreparedStatement ps = jdbcTemplate.getDataSource().getConnection()
.prepareStatement(_SqlSave(beanClass).toString());
_setIValues(beanClass, ps);
return ps;
}
}, keyHolder);
} catch (DataAccessException e) {
logger.info("异常信息{}",e);
}
return keyHolder.getKey();
}
public Map<String, Object> GetMapById(Object id){
try {
String sql = String.format("select * from %s where id=? and rownum = 1",_tableName());
logger.info("执行SQL[{}]",sql);
List<Map<String, Object>> results = jdbcTemplate.queryForList(sql,id);
return (results == null || results.size() == 0) ?null:results.get(0);
} catch (DataAccessException e) {
logger.info("异常信息{}",e);
}
return null;
}
public Map<String, Object> GetMap(){
try {
String where = _SqlWhere(this).toString();
String sql = "";
List<Map<String, Object>> results = null;
if(StringUtils.isNotBlank(where) && StringUtils.isNotEmpty(where)){
sql = String.format("select * from %s where %s and rownum = 1",_tableName(),where);
logger.info("执行SQL[{}]",sql);
results = jdbcTemplate.queryForList(sql,_Values(this));
}else{
sql = String.format("select * from %s where rownum = 1",_tableName());
logger.info("执行SQL[{}]",sql);
results = jdbcTemplate.queryForList(sql);
}
return (results == null || results.size() == 0) ?null:results.get(0);
} catch (DataAccessException e) {
logger.info("异常信息{}",e);
}
return null;
}
public Map<String, Object> GetMapByMap(Map<String, Object> param){
try {
String where = _SqlWhere(param).toString();
String sql = "";
List<Map<String, Object>> results = null;
if(StringUtils.isBlank(where) || StringUtils.isEmpty(where)){
sql = String.format("select * from %s where rownum = 1",_tableName());
logger.info("执行SQL[{}]",sql);
results = jdbcTemplate.queryForList(sql);
}else{
sql = String.format("select * from %s where %s and rownum = 1",_tableName(),_SqlWhere(param));
logger.info("执行SQL[{}]",sql);
results = jdbcTemplate.queryForList(sql,_Values(param));
}
return (results == null || results.size() == 0) ?null:results.get(0);
} catch (DataAccessException e) {
logger.info("异常信息{}",e);
}
return null;
}
public List<Map<String, Object>> GetListMap(){
try {
String where = _SqlWhere(this).toString();
String sql = "";
List<Map<String, Object>> results = null;
if(StringUtils.isBlank(where) || StringUtils.isEmpty(where)){
sql = String.format("select * from %s ",_tableName());
logger.info("执行SQL[{}]",sql);
results = jdbcTemplate.queryForList(sql);
}else{
sql = String.format("select * from %s where %s ",_tableName(),_SqlWhere(this));
logger.info("执行SQL[{}]",sql);
results = jdbcTemplate.queryForList(sql,_Values(this));
}
return (results != null && results.size() == 0) ?null:results;
} catch (DataAccessException e) {
logger.info("异常信息{}",e);
}
return null;
}
public List<Map<String, Object>> GetListMap(int size){
try {
String where = _SqlWhere(this).toString();
String sql = "";
List<Map<String, Object>> results = null;
if(StringUtils.isBlank(where) || StringUtils.isEmpty(where)){
sql = String.format("select * from %s where rownum <= ?",_tableName());
results = jdbcTemplate.queryForList(sql,size);
}else{
sql = String.format("select * from %s where %s ",_tableName(),_SqlWhere(this)+" and rownum <= ?");
logger.info("执行SQL[{}]",sql);
List<Object> args = new ArrayList<Object>();
args.addAll(_LValues(this));
args.add(size);
results = jdbcTemplate.queryForList(sql,args.toArray());
}
return (results != null && results.size() == 0) ?null:results;
} catch (DataAccessException e) {
logger.info("异常信息{}",e);
}
return null;
}
public List<Map<String, Object>> GetListMap(Map<String, Object> param){
try {
String where = _SqlWhere(param).toString();
String sql = "";
List<Map<String, Object>> results = null;
if(StringUtils.isBlank(where) || StringUtils.isEmpty(where)){
sql = String.format("select * from %s ",_tableName());
logger.info("执行SQL[{}]",sql);
results = jdbcTemplate.queryForList(sql);
}else{
sql = String.format("select * from %s where %s ",_tableName(),_SqlWhere(param));
logger.info("执行SQL[{}]",sql);
results = jdbcTemplate.queryForList(sql,_Values(param));
}
return (results != null && results.size() == 0) ?null:results;
} catch (DataAccessException e) {
logger.info("异常信息{}",e);
}
return null;
}
public List<Map<String, Object>> GetListMap(Map<String, Object> param,int size){
try {
String where = _SqlWhere(param).toString();
String sql = "";
List<Map<String, Object>> results = null;
if(StringUtils.isBlank(where) || StringUtils.isEmpty(where)){
sql = String.format("select * from %s where rownum <= ?",_tableName());
logger.info("执行SQL[{}]",sql);
results = jdbcTemplate.queryForList(sql,size);
}else{
sql = String.format("select * from %s where %s ",_tableName(),_SqlWhere(param).append(" and rownum <= ?"));
logger.info("执行SQL[{}]",sql);
List<Object> args = new ArrayList<Object>();
args.addAll(_LValues(param));
args.add(size);
results = jdbcTemplate.queryForList(sql,args.toArray());
}
return (results != null && results.size() == 0) ?null:results;
} catch (DataAccessException e) {
logger.info("异常信息{}",e);
}
return null;
}
public List<Map<String, Object>> pageListMap(int currentPage,int pageSize){
if(currentPage <= 0) currentPage = 1;
if(pageSize <= 0) pageSize = 5;
int start = (currentPage-1)*pageSize;
int end = currentPage*pageSize;
String sql = String.format("select * from %s ",_tableName());
String where = _SqlWhere(this).toString();
if(StringUtils.isNotBlank(where) && StringUtils.isNotEmpty(where)){
sql += " where "+where;
}
String pgSQL="SELECT * FROM (SELECT a.*, ROWNUM rn FROM (%s) a WHERE ROWNUM <= %d) WHERE rn >= %d";
pgSQL = String.format(pgSQL, sql,end,start);
logger.info("执行SQL[{}]",pgSQL);
try {
List<Map<String, Object>> results = null;
if(StringUtils.isNotBlank(where) && StringUtils.isNotEmpty(where)){
results = jdbcTemplate.queryForList(pgSQL.toUpperCase(),_Values(this));
}else{
results = jdbcTemplate.queryForList(pgSQL.toUpperCase());
}
return (results != null && results.size() == 0) ?null:results;
} catch (DataAccessException e) {
logger.info("异常信息{}",e);
}
return null;
}
public Integer pageListCount(){
String sql = String.format("select count(*) from %s ",_tableName());
String where = _SqlWhere(this).toString();
if(StringUtils.isNotBlank(where) && StringUtils.isNotEmpty(where)){
sql += " where "+where;
}
logger.info("执行SQL[{}]",sql);
try {
if(StringUtils.isNotBlank(where) && StringUtils.isNotEmpty(where)){
return jdbcTemplate.queryForObject(sql,_Values(this), Integer.class);
}else{
return jdbcTemplate.queryForObject(sql, Integer.class);
}
} catch (DataAccessException e) {
logger.info("异常信息{}",e);
}
return 0;
}
public Integer pageListCount(Map<String, Object> param){
String sql = String.format("select count(*) from %s ",_tableName());
String where = _SqlWhere(param).toString();
if(StringUtils.isNotBlank(where) && StringUtils.isNotEmpty(where)){
sql += " where "+where;
}
logger.info("执行SQL[{}]",sql);
try {
if(StringUtils.isNotBlank(where) && StringUtils.isNotEmpty(where)){
return jdbcTemplate.queryForObject(sql,_Values(param), Integer.class);
}else{
return jdbcTemplate.queryForObject(sql, Integer.class);
}
} catch (DataAccessException e) {
logger.info("异常信息{}",e);
}
return 0;
}
public List<Map<String, Object>> pageListMap(Map<String, Object> param,int currentPage,int pageSize){
if(currentPage <= 0) currentPage = 1;
if(pageSize <= 0) pageSize = 5;
int start = (currentPage-1)*pageSize;
int end = currentPage*pageSize;
String sql = String.format("select * from %s ",_tableName());
String where = _SqlWhere(param).toString();
if(StringUtils.isNotBlank(where) && StringUtils.isNotEmpty(where)){
sql += " where "+where;
}
String pgSQL="SELECT * FROM (SELECT a.*, ROWNUM rn FROM (%s) a WHERE ROWNUM <= %d) WHERE rn >= %d";
pgSQL = String.format(pgSQL, sql,end,start);
logger.info("执行SQL[{}]",pgSQL);
try {
List<Map<String, Object>> results = null;
if(StringUtils.isNotBlank(where) && StringUtils.isNotEmpty(where)){
results = jdbcTemplate.queryForList(pgSQL.toUpperCase(),_Values(param));
}else{
results = jdbcTemplate.queryForList(pgSQL.toUpperCase());
}
return (results != null && results.size() == 0) ?null:results;
} catch (DataAccessException e) {
logger.info("异常信息{}",e);
}
return null;
}
}
<dependencies>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.29</version>
</dependency>
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.2.0.4</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>3.2.16.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>3.2.16.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>3.2.16.RELEASE</version>
</dependency>
<dependency>
<groupId>commons-lang</groupId>
<artifactId>commons-lang</artifactId>
<version>2.6</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
<!-- Logging -->
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.6.6</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>jcl-over-slf4j</artifactId>
<version>1.6.6</version>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.6.6</version>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>3.0.1</version>
<scope>provided</scope>
</dependency>
</dependencies>