@Override
public List<PetType> findPetTypes() throws DataAccessException {
Map<String, Object> params = new HashMap<String, Object>();
return this.namedParameterJdbcTemplate.query(
"SELECT id, name FROM types ORDER BY name",
params,
BeanPropertyRowMapper.newInstance(PetType.class));
}
/**
* 根据主键查询
*
* @param wordId 敏感词ID
* @return SysSensitiveWord
*/
@Override
public SysSensitiveWord findById(Long wordId) {
StringBuilder sql = new StringBuilder();
sql.append(SELECT_SQL);
sql.append(" WHERE _this.`word_id` = ? ");
return jdbcTemplate.queryForObject(
sql.toString(), BeanPropertyRowMapper.newInstance(SysSensitiveWord.class), wordId);
}
/**
* 根据角色查询所有资源
*
* @param roleId 角色ID
* @return
*/
public List<UResource> findByRoleId(Long roleId) {
StringBuilder sql = new StringBuilder();
sql.append(" SELECT ");
sql.append(" " + selectColumn());
sql.append(" FROM u_resource _this ");
sql.append(
" WHERE (select b.id FROM u_role_resource b WHERE _this.id = b.resource_id AND b.role_id = ?) ");
return jdbcTemplate.query(
sql.toString(), BeanPropertyRowMapper.newInstance(UResource.class), roleId);
}
@Override
public Collection<StateEmails> findBySICDesc(String sicdesc) throws DataAccessException {
// TODO Auto-generated method stub
List<StateEmails> emailAk = new ArrayList<>();
// Retrieve the list of all vets.
emailAk.addAll(
this.namedParameterJdbcTemplate.query(
"SELECT * FROM email_ak where rownum < 10",
BeanPropertyRowMapper.newInstance(StateEmails.class)));
return emailAk;
}
/**
* 获取相应的Object
*
* @param sql
* @param className
* @param obj
* @return
*/
public Object getObject(String sql, Class<? extends Serializable> className, Object[] obj) {
Object object = null;
try {
object =
simpleJdbcTemplate.queryForObject(sql, BeanPropertyRowMapper.newInstance(className), obj);
} catch (DataAccessException e) {
log.info(e);
return null;
// throw new DaoException("数据库操作失败！",e);
}
return object;
}
@Override
public StateEmails findByRowNum(Long rownum, String state) throws DataAccessException {
// TODO Auto-generated method stub
StateEmails emailAk;
Map<String, Object> params = new HashMap<>();
params.put("rownum", rownum);
// "SELECT * FROM email_ak where rownum = 1",
emailAk =
this.namedParameterJdbcTemplate.queryForObject(
"SELECT * FROM email_" + state + " where rownum = :rownum",
params,
BeanPropertyRowMapper.newInstance(StateEmails.class));
return emailAk;
}
@Override
public Collection<StateEmails> findByCity(String city) throws DataAccessException {
// TODO Auto-generated method stub
Map<String, Object> params = new HashMap<>();
params.put("city", city);
// "SELECT * FROM email_ak where city = :city",
// "SELECT * FROM email_ak where rownum = 1",
List<StateEmails> emailAk =
this.namedParameterJdbcTemplate.query(
"SELECT * FROM email_ak where rownum = 1",
params,
BeanPropertyRowMapper.newInstance(StateEmails.class));
return emailAk;
}
public User getUser(User user) throws RuntimeException {
logger.info("--> cs.simple.console.service.impl.UserServiceImpl.getUser");
User clone = null;
try {
clone =
jdbcTemplate.queryForObject(
"select * from tb_user where user_id = ?",
new Object[] {user.getUserId()},
BeanPropertyRowMapper.newInstance(User.class));
} catch (Exception e) {
logger.error(ExceptionUtils.getStackTrace(e));
throw new RuntimeException(e);
}
logger.info("<-- cs.simple.console.service.impl.UserServiceImpl.getUser");
return clone;
}
public List<User> queryUser(User user, PagingAndSorting ps) throws RuntimeException {
logger.info("--> cs.simple.console.service.impl.UserServiceImpl.queryUser");
List<User> result = null;
try {
List<Object> args = new ArrayList<Object>();
String viewname = user.getViewname();
StringBuffer b = new StringBuffer();
b.append("select * from tb_user where deleted = ? ");
args.add(Constant.N);
if (StringUtils.hasText(viewname)) {
b.append("and viewname like ? ");
args.add(SQLUtil.likeValue(viewname));
}
String countSQL = SQLUtil.countSQL(b.toString());
int total = jdbcTemplate.queryForObject(countSQL, args.toArray(), Integer.class);
ps.setTotal(total);
String sort = ps.getSort();
String order = ps.getOrder();
if (StringUtils.hasText(sort)) {
b.append("order by ").append(sort).append(" ");
}
if (StringUtils.hasText(order)) {
b.append(order).append(" ");
}
b.append("limit ?,?");
args.add(ps.getOffset());
args.add(ps.getLength());
result =
jdbcTemplate.query(
b.toString(), args.toArray(), BeanPropertyRowMapper.newInstance(User.class));
} catch (Exception e) {
logger.error(ExceptionUtils.getStackTrace(e));
throw new RuntimeException(e);
}
logger.info("<-- cs.simple.console.service.impl.UserServiceImpl.queryUser");
return result;
}
/**
* Dao实现类
*
* <p>Class: admin_resource - 系统资源信息
*
* @since 2015-05-21 12:44:17
*/
@Repository
public class CIP_admin_resourceDaoImpl extends IDaoImpl<CIP_admin_resourcePO>
implements CIP_admin_resourceDao {
private final BeanPropertyRowMapper<CIP_admin_resourceData> dataRM =
BeanPropertyRowMapper.newInstance(CIP_admin_resourceData.class);
public CIP_admin_resourceDaoImpl() {
super();
this.tableName = "cip_admin_resource";
this.tableKeys = new String[] {"resource_id"};
}
@Override
protected String generateWhere(Object... args) {
// TODO Auto-generated method stub
return null;
}
@Override
public List<CIP_admin_resourceData> searchData(CIPPageInfo page, CIPReqCondition[] conditions) {
try {
List<Object> params = new ArrayList<Object>();
String querySql = generateQuerySql(page, conditions, params);
return jdbcTemplate.query(querySql.toString(), params.toArray(), dataRM);
} catch (DataAccessException e) {
throw new CIPDaoException(CIPErrorCode.ERROR_DATABASE_TECH_EXCEPTION);
}
}
private String generateQuerySql(
CIPPageInfo pageUtil, CIPReqCondition[] conditions, List<Object> params) {
StringBuffer countSql =
new StringBuffer().append("SELECT COUNT(1) from ").append(tableName).append("_v");
StringBuffer querySql =
new StringBuffer().append("SELECT * from ").append(tableName).append("_v");
StringBuffer whereSql = new StringBuffer().append(" where ");
if (conditions != null && conditions.length > 0) {
int operator;
for (int i = 0; i < conditions.length; i++) {
operator = conditions[i].getOperator();
if (operator == CIPOperatorType.EQUAL.code) {
whereSql.append(conditions[i].getFieldName()).append(" = ? and ");
params.add(conditions[i].getLowValue());
} else if (operator == CIPOperatorType.GREAT_EQUAL.code) {
whereSql.append(conditions[i].getFieldName()).append(" >= ? and ");
params.add(conditions[i].getLowValue());
} else if (operator == CIPOperatorType.GREAT_THAN.code) {
whereSql.append(conditions[i].getFieldName()).append(" > ? and ");
params.add(conditions[i].getLowValue());
} else if (operator == CIPOperatorType.LESS_EQUAL.code) {
whereSql.append(conditions[i].getFieldName()).append(" <= ? and ");
params.add(conditions[i].getHighValue());
} else if (operator == CIPOperatorType.LESS_THAN.code) {
whereSql.append(conditions[i].getFieldName()).append(" < ? and ");
params.add(conditions[i].getHighValue());
} else if (operator == CIPOperatorType.BETWEEN.code) {
whereSql.append(conditions[i].getFieldName()).append(" BETWEEN ? and ? ");
params.add(conditions[i].getLowValue());
params.add(conditions[i].getHighValue());
} else if (operator == CIPOperatorType.IN.code) {
whereSql.append(conditions[i].getFieldName()).append(" IN ( ");
String[] values = conditions[i].getValues();
for (int j = 0; j < values.length; j++) {
if (j < values.length - 1) whereSql.append("?, ");
else whereSql.append("? ) and ");
params.add(values[j]);
}
}
}
String whereStr = whereSql.substring(0, whereSql.length() - 4);
querySql.append(whereStr);
countSql.append(whereStr);
}
querySql.append(" limit ?,?");
int total = jdbcTemplate.queryForObject(countSql.toString(), params.toArray(), Integer.class);
pageUtil.setTotal(total);
params.add(pageUtil.getStartRecord());
params.add(pageUtil.getRows());
return querySql.toString();
}
private CIP_admin_resourceMapper mapperRM = new CIP_admin_resourceMapper();
public List<Map<String, Object>> exportEntities(
CIPPageInfo pageUtil, CIPReqCondition[] conditions, boolean xFirst) {
List<Object> params = new ArrayList<Object>();
StringBuffer countSql =
new StringBuffer().append("SELECT COUNT(1) from ").append(tableName).append("_v");
StringBuffer querySql =
new StringBuffer().append("SELECT * from ").append(tableName).append("_v");
StringBuffer whereSql = new StringBuffer().append(" where ");
if (conditions != null && conditions.length > 0) {
int operator;
for (int i = 0; i < conditions.length; i++) {
operator = conditions[i].getOperator();
if (operator == CIPOperatorType.EQUAL.code) {
whereSql.append(conditions[i].getFieldName()).append(" = ? and ");
params.add(conditions[i].getLowValue());
} else if (operator == CIPOperatorType.GREAT_EQUAL.code) {
whereSql.append(conditions[i].getFieldName()).append(" >= ? and ");
params.add(conditions[i].getLowValue());
} else if (operator == CIPOperatorType.GREAT_THAN.code) {
whereSql.append(conditions[i].getFieldName()).append(" > ? and ");
params.add(conditions[i].getLowValue());
} else if (operator == CIPOperatorType.LESS_EQUAL.code) {
whereSql.append(conditions[i].getFieldName()).append(" <= ? and ");
params.add(conditions[i].getHighValue());
} else if (operator == CIPOperatorType.LESS_THAN.code) {
whereSql.append(conditions[i].getFieldName()).append(" < ? and ");
params.add(conditions[i].getHighValue());
} else if (operator == CIPOperatorType.BETWEEN.code) {
whereSql.append(conditions[i].getFieldName()).append(" BETWEEN ? and ? ");
params.add(conditions[i].getLowValue());
params.add(conditions[i].getHighValue());
} else if (operator == CIPOperatorType.IN.code) {
whereSql.append(conditions[i].getFieldName()).append(" IN ( ");
String[] values = conditions[i].getValues();
for (int j = 0; j < values.length; j++) {
if (j < values.length - 1) whereSql.append("?, ");
else whereSql.append("? ) and ");
params.add(values[j]);
}
}
}
String whereStr = whereSql.substring(0, whereSql.length() - 4);
querySql.append(whereStr);
countSql.append(whereStr);
}
querySql.append(" limit ?,?");
if (xFirst) {
int total = jdbcTemplate.queryForObject(countSql.toString(), params.toArray(), Integer.class);
pageUtil.setTotal(total);
}
params.add(pageUtil.getStartRecord());
params.add(pageUtil.getRows());
try {
return jdbcTemplate.query(querySql.toString(), params.toArray(), mapperRM);
} catch (DataAccessException e) {
throw new CIPDaoException(CIPErrorCode.ERROR_DATABASE_TECH_EXCEPTION);
}
}
}
/**
* 根据对象查询
*
* @param sysSensitiveWord
* @param orders 排序字段，可多字段排序。格式{{"字段名", "asc"}, {"字段名, "desc"}}
* @param offset 开始索引
* @param rows 条数(从第offset+1条开始，取rows条)
* @return List
*/
@Override
public List<SysSensitiveWord> find(
SysSensitiveWord sysSensitiveWord, String[][] orders, Long offset, Long rows) {
StringBuilder sql = new StringBuilder();
sql.append(SELECT_SQL);
sql.append(" WHERE 1 = 1 ");
List<Object> param = new ArrayList<Object>();
if (sysSensitiveWord != null) {
if (sysSensitiveWord.getWordId() != null) {
sql.append(" AND _this.`word_id` = ?");
param.add(sysSensitiveWord.getWordId());
}
if (sysSensitiveWord.getWord() != null && !"".equals(sysSensitiveWord.getWord())) {
sql.append(" AND _this.`word` = ?");
param.add(sysSensitiveWord.getWord());
}
if (sysSensitiveWord.getMathType() != null && !"".equals(sysSensitiveWord.getMathType())) {
sql.append(" AND _this.`math_type` = ?");
param.add(sysSensitiveWord.getMathType());
}
if (sysSensitiveWord.getCreateDate() != null) {
sql.append(" AND _this.`create_date` = ?");
param.add(sysSensitiveWord.getCreateDate());
}
if (sysSensitiveWord.getUpdateDate() != null) {
sql.append(" AND _this.`update_date` = ?");
param.add(sysSensitiveWord.getUpdateDate());
}
if (sysSensitiveWord.getIsValid() != null && !"".equals(sysSensitiveWord.getIsValid())) {
sql.append(" AND _this.`is_valid` = ?");
param.add(sysSensitiveWord.getIsValid());
}
}
// 排序
if (orders != null && orders.length > 0) {
sql.append(" ORDER BY ");
for (int i = 0; i < orders.length; i++) {
String[] order = orders[i];
if (i != 0) {
sql.append("_this.`").append(order[0]).append("`, ");
}
if (order.length == 1) {
sql.append("_this.`").append(order[0]).append("` ASC ");
} else {
sql.append("_this.`").append(order[0]).append("` ").append(order[1]);
}
}
}
// 分页
if (offset != null && rows != null) {
sql.append(" limit ?,? ");
param.add(offset);
param.add(rows);
} else if (rows != null) {
sql.append(" limit ? ");
param.add(rows);
}
return jdbcTemplate.query(
sql.toString(), param.toArray(), BeanPropertyRowMapper.newInstance(SysSensitiveWord.class));
}
public List<Circle> getAllCircles() {
String sql = "select circle_id as circleId,name from circle";
return this.getJdbcTemplate().query(sql, BeanPropertyRowMapper.newInstance(Circle.class));
}
/** User: gkislin Date: 26.08.2014 */
@Repository
public class JdbcUserRepositoryImpl implements UserRepository {
private static final BeanPropertyRowMapper<User> ROW_MAPPER =
BeanPropertyRowMapper.newInstance(User.class);
@Autowired private JdbcTemplate jdbcTemplate;
@Autowired private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
private SimpleJdbcInsert insertUser;
@Autowired
public JdbcUserRepositoryImpl(DataSource dataSource) {
this.insertUser =
new SimpleJdbcInsert(dataSource).withTableName("USERS").usingGeneratedKeyColumns("id");
}
@Override
public User save(User user) {
MapSqlParameterSource map =
new MapSqlParameterSource()
.addValue("id", user.getId())
.addValue("name", user.getName())
.addValue("email", user.getEmail())
.addValue("password", user.getPassword())
.addValue("registered", user.getRegistered())
.addValue("enabled", user.isEnabled())
.addValue("caloriesPerDay", user.getCaloriesPerDay());
if (user.isNew()) {
Number newKey = insertUser.executeAndReturnKey(map);
user.setId(newKey.intValue());
} else {
namedParameterJdbcTemplate.update(
"UPDATE users SET name=:name, email=:email, password=:password, "
+ "registered=:registered, enabled=:enabled, calories_per_day=:caloriesPerDay WHERE id=:id",
map);
}
return user;
}
@Override
public boolean delete(int id) {
return jdbcTemplate.update("DELETE FROM users WHERE id=?", id) != 0;
}
@Override
public User get(int id) {
List<User> users = jdbcTemplate.query("SELECT * FROM users WHERE id=?", ROW_MAPPER, id);
return DataAccessUtils.singleResult(users);
}
@Override
public User getByEmail(String email) {
return jdbcTemplate.queryForObject("SELECT * FROM users WHERE email=?", ROW_MAPPER, email);
}
@Override
public List<User> getAll() {
return jdbcTemplate.query("SELECT * FROM users ORDER BY name, email", ROW_MAPPER);
}
}