@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;
 }
Example #6
0
  /**
   * 获取相应的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;
  }
Example #9
0
 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;
 }
Example #10
0
  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));
 }
Example #16
0
/** 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);
  }
}