@Test @SuppressWarnings({"unchecked", "rawtypes"}) public void testOverridingDifferentClassDefinedForMapping() { BeanPropertyRowMapper mapper = new BeanPropertyRowMapper(Person.class); thrown.expect(InvalidDataAccessApiUsageException.class); mapper.setMappedClass(Long.class); }
@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; }
/** * Static factory method to create a new BeanPropertyRowMapper (with the mapped class specified * only once). * * @param mappedClass the class that each row should be mapped to */ public static <T> BeanPropertyRowMapper<T> newInstance(Class<T> mappedClass) { BeanPropertyRowMapper<T> newInstance = new BeanPropertyRowMapper<T>(); newInstance.setMappedClass(mappedClass); return newInstance; }
/** * 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); } } }
@Test public void testOverridingSameClassDefinedForMapping() { BeanPropertyRowMapper<Person> mapper = new BeanPropertyRowMapper<>(Person.class); mapper.setMappedClass(Person.class); }
/** * 根据对象查询 * * @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); } }