org.springframework.jdbc.core.BeanPropertyRowMapper Java Examples

@Override
public void run(String... strings) throws Exception {
	log.info("============================================================");
	log.info("Query the write through table");
	log.info("============================================================");
	String sql = "SELECT * FROM message_table";
	List<Message> messages = jdbcTemplate.query(sql,new BeanPropertyRowMapper<Message>(Message.class));
	if (messages == null) {
		log.info("The results from the DB were null: " + sql);
	}
	if (messages.isEmpty()) {
		log.info("There were no records in the DB with query: " + sql);
	} else {
		log.info("****************************************************");
		log.info("Here are the results:");
		log.info("****************************************************");
		for (Message message : messages) {
			log.info(message.toString());
		}
		log.info("****************************************************");
	}
}
 

@Override
public List<Visit> findByPetId(Integer petId) {
    final List<Visit> visits = this.jdbcTemplate.query(
            "SELECT id, visit_date, description FROM visits WHERE pet_id=?",
            new BeanPropertyRowMapper<Visit>() {
                @Override
                public Visit mapRow(ResultSet rs, int row) throws SQLException {
                    Visit visit = new Visit();
                    visit.setId(rs.getInt("id"));
                    Date visitDate = rs.getDate("visit_date");
                    visit.setDate(new DateTime(visitDate));
                    visit.setDescription(rs.getString("description"));
                    return visit;
                }
            },
            petId);
    return visits;
}
 

/**
 * @方法名 findAllTypeByUser
 * @功能 TODO(这里用一句话描述这个方法的作用)
 * @参数 @param username
 * @参数 @return
 * @返回 List<SysDbmsTabsTypeInfo>
 * @author Administrator
 * @throws
 */
public List<SysDbmsTabsTypeInfo> findAllTypeByUser(String username) {
	
	StringBuilder stringBuilder = new StringBuilder();
	stringBuilder.append("select * from sys_dbms_tabs_type_info t ");
	stringBuilder.append(" where t.uuid in (");
	stringBuilder.append(" select a.type_uuid from sys_dbms_tabs_info a ");
	stringBuilder.append("  where a.uuid in ( ");
	stringBuilder.append("   select b.tabs_id from sys_roles_tabs_info b ");
	stringBuilder.append("    where b.role_id in (");
	stringBuilder.append("     select c.roles_id from sys_user_roles_info c");
	stringBuilder.append("      where c.user_id in ( ");
	stringBuilder.append("       select d.uuid from sys_user_base_info d");
	stringBuilder.append("        where d.user_name = '" + username + "'");
	stringBuilder.append("      ) and c.checked = 1");
	stringBuilder.append("    ) ");
	stringBuilder.append("  ) and a.delete_flag = 0");
	stringBuilder.append(" ) and t.delete_flag = 0");
	stringBuilder.append(" order by t.type_order");
	NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(jdbcTemplate);
	List<SysDbmsTabsTypeInfo> list = template.query(stringBuilder.toString(), new BeanPropertyRowMapper<>(SysDbmsTabsTypeInfo.class));
	return list;

}
 

@Override
public List<Folder> findDeleted(long userId, Integer maxHits) {
	List<Folder> results = new ArrayList<Folder>();
	try {
		String query = "select ld_id, ld_name, ld_lastmodified from ld_folder where ld_deleted=1 and ld_deleteuserid = "
				+ userId;

		@SuppressWarnings("rawtypes")
		RowMapper mapper = new BeanPropertyRowMapper() {
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				Folder fld = new Folder();
				fld.setId(rs.getLong(1));
				fld.setName(rs.getString(2));
				fld.setLastModified(rs.getTimestamp(3));
				return fld;
			}
		};

		results = (List<Folder>) query(query, null, mapper, maxHits);
	} catch (Exception e) {
		log.error(e.getMessage());
	}

	return results;
}
 

/**
 * 通过where条件查找一条记录
 * 查找姓名为1年龄大于23的记录  selectOneWhere("name=? and age 大于号 ?", "wang",23)
 *
 * @param sqlCondition name=:1 and age=:2
 * @param values       "wang",23
 */
public E selectOneWhere(String sqlCondition, Object... values) {
    //sql
    String sql = "SELECT " + columns + " FROM " + tableName + " WHERE " + sqlCondition;

    List<E> dataList = getSqlFactory().createSQL().useSql(sql)
            .varParameter(values)
            .queryList(new BeanPropertyRowMapper<>(entityClass));

    if (dataList.isEmpty()) {
        return null;
    } else if (dataList.size() == 1) {
        return dataList.get(0);
    } else {
        log.error(tableName + "#findOneWhere()返回多条数据");
        throw new RuntimeException(tableName + "#findOneWhere()返回多条数据");
    }
}
 

@RequestMapping(method = RequestMethod.GET)
public @ResponseBody Result onRootAccess() {
    log.info("Creating table");

    jdbcTemplate.execute("DROP TABLE IF EXISTS contacts");
    jdbcTemplate.execute("CREATE TABLE contacts(" + "id SERIAL, first_name VARCHAR(255), last_name VARCHAR(255))");

    log.info("Inserting data in table");
    jdbcTemplate.update("INSERT INTO contacts(first_name, last_name) VALUES ('John','Wright')");

    log.info("Retrieving data from table");
    final List<Contact> contacts = jdbcTemplate.query("SELECT id, first_name, last_name FROM contacts",
            new BeanPropertyRowMapper<Contact>(Contact.class));

    Result result = new Result();
    result.setStatus("Successfully connected to PostgreSQL. Retrieved data from contacts table");
    result.setContacts(contacts);
    return result;
}
 

@SuppressWarnings("unchecked")
public <T> T queryObject(Class<T> clazz, String sql, Object... args) {
	if(log.isDebugEnabled()) {
		log.debug("查询单条数据=\n" + sql);
	}
	try {
		return (T)template.queryForObject(sql, args, new BeanPropertyRowMapper(clazz));
	} catch(Exception e) {
		log.error("查询单条数据=\n" + e.getMessage());
		return null;
	}
}
 

/**
 * Loads {@link Owner Owners} from the data store by last name, returning all owners whose last name <i>starts</i> with
 * the given name; also loads the {@link Pet Pets} and {@link Visit Visits} for the corresponding owners, if not
 * already loaded.
 */
@Override
public Collection<Owner> findByLastName(String lastName) throws DataAccessException {
    Map<String, Object> params = new HashMap<>();
    params.put("lastName", lastName + "%");
    List<Owner> owners = this.namedParameterJdbcTemplate.query(
        "SELECT id, first_name, last_name, address, city, telephone FROM owners WHERE last_name like :lastName",
        params,
        BeanPropertyRowMapper.newInstance(Owner.class)
    );
    loadOwnersPetsAndVisits(owners);
    return owners;
}
 

public String get(String key) {
    String sql = "SELECT " + COLUMNS + " FROM " + TABLE_NAME
            + " WHERE config_key=:configKey LIMIT 1 ";
    Map<String, Object> param = new HashMap<String, Object>();
    param.put("configKey", key);
    List<ConfigEntry> configEntryList = namedParameterJdbcTemplate.query(sql, param,
            new BeanPropertyRowMapper<>(ConfigEntry.class));
    if (configEntryList == null || configEntryList.size() == 0) {
        return null;
    } else {
        return configEntryList.get(0).getConfigValue();
    }
}
 

public List<ChatUser> recentUser1(long userId) {
    String sql = "SELECT sender_id user_id, MAX(create_time) create_time " +
            "FROM " + TABLE_NAME + " WHERE receiver_id =:userId " +
            "GROUP BY sender_id";
    Map<String, Object> parameter = new HashMap<>();
    parameter.put("userId", userId);
    List<ChatUser> list = namedParameterJdbcTemplate.query(sql, parameter, new BeanPropertyRowMapper<>(ChatUser.class));
    return list;
}
 

public UserScore findOne(long userId) {
    String sql = " SELECT * FROM " + TABLE_NAME + " WHERE user_id=:userId ";
    Map<String, Object> parameter = new HashMap<String, Object>();
    parameter.put("userId", userId);
    UserScore userScore = null;

    try {
        userScore = namedParameterJdbcTemplate.queryForObject(sql, parameter,
                new BeanPropertyRowMapper<>(UserScore.class));
    } catch (EmptyResultDataAccessException e) {
        userScore = null;
    }
    return userScore;
}
 

/**
 * Loads {@link Owner Owners} from the data store by last name, returning all owners whose last name <i>starts</i> with
 * the given name; also loads the {@link Pet Pets} and {@link Visit Visits} for the corresponding owners, if not
 * already loaded.
 */
@Override
public Collection<Owner> findByLastName(String lastName) throws DataAccessException {
    Map<String, Object> params = new HashMap<String, Object>();
    params.put("lastName", lastName + "%");
    List<Owner> owners = this.namedParameterJdbcTemplate.query(
            "SELECT id, first_name, last_name, address, city, telephone FROM owners WHERE last_name like :lastName",
            params,
            BeanPropertyRowMapper.newInstance(Owner.class)
    );
    loadOwnersPetsAndVisits(owners);
    return owners;
}
 

@SuppressWarnings({ "unchecked", "rawtypes" })
public Employee findById(int id){
	 
	String sql = "SELECT * FROM EMPLOYEE WHERE ID = ?";

	jdbcTemplate = new JdbcTemplate(dataSource);
	Employee employee = (Employee) jdbcTemplate.queryForObject(
			sql, new Object[] { id }, new BeanPropertyRowMapper(Employee.class));
 
	return employee;
}
 

/**
 * 通用根据主键查询单条记录
 *
 * @param pk 主键
 * @return 单条记录
 */
public T findOneById(P pk) {
	String tableName = getTableName();
	String sql = StrUtil.format("SELECT * FROM {table} where id = ?", Dict.create().set("table", tableName));
	RowMapper<T> rowMapper = new BeanPropertyRowMapper<>(clazz);
	log.debug("【执行SQL】SQL：{}", sql);
	log.debug("【执行SQL】参数：{}", JSONUtil.toJsonStr(pk));
	return jdbcTemplate.queryForObject(sql, new Object[]{pk}, rowMapper);
}
 

@Override
public List<Document> findDeleted(long userId, Integer maxHits) {
	List<Document> results = new ArrayList<Document>();
	try {
		String query = "select ld_id, ld_lastmodified, ld_filename, ld_customid, ld_tenantid, ld_folderid from ld_document where ld_deleted=1 and ld_deleteuserid = "
				+ userId + " order by ld_lastmodified desc";

		@SuppressWarnings("rawtypes")
		RowMapper docMapper = new BeanPropertyRowMapper() {
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				Document doc = new Document();
				doc.setId(rs.getLong(1));
				doc.setLastModified(rs.getTimestamp(2));
				doc.setFileName(rs.getString(3));
				doc.setCustomId(rs.getString(4));
				doc.setTenantId(rs.getLong(5));

				Folder folder = new Folder();
				folder.setId(rs.getLong(6));
				doc.setFolder(folder);

				return doc;
			}
		};

		results = (List<Document>) query(query, null, docMapper, maxHits);

	} catch (Throwable e) {
		log.error(e.getMessage());
	}

	return results;
}
 

/**
 * 通用根据主键查询单条记录
 *
 * @param pk 主键
 * @return 单条记录
 */
public T findOneById(P pk) {
	String tableName = getTableName();
	String sql = StrUtil.format("SELECT * FROM {table} where id = ?", Dict.create().set("table", tableName));
	RowMapper<T> rowMapper = new BeanPropertyRowMapper<>(clazz);
	log.debug("【执行SQL】SQL：{}", sql);
	log.debug("【执行SQL】参数：{}", JSONUtil.toJsonStr(pk));
	return jdbcTemplate.queryForObject(sql, new Object[]{pk}, rowMapper);
}
 

@Override
public List<User> list() {
    Map params = new HashMap();
    params.put("tableName", "User");

    String sqlStatement = getSqlStatement(FreemarkHelper.TMPL_SQL_SELECT, params);
    return jdbcTemplate.query(sqlStatement, new BeanPropertyRowMapper(User.class));
}
 

@Override
public List<GroupTaskEntity> getJobList(String groupId) {
    RowMapper<GroupTaskEntity> rowMapper = new BeanPropertyRowMapper<>(GroupTaskEntity.class);
    List<GroupTaskEntity> list = jdbcTemplate.query(batchSqlText.getSql("sys_rdbms_133"), rowMapper, groupId);
    logger.debug("group id is:{}", groupId);
    return list;
}
 

/**
 * Refresh the cache of Vets that the ClinicService is holding.
 */
@Override
public Collection<Vet> findAll() throws DataAccessException {
    List<Vet> vets = new ArrayList<>();
    // Retrieve the list of all vets.
    vets.addAll(this.jdbcTemplate.query(
        "SELECT id, first_name, last_name FROM vets ORDER BY last_name,first_name",
        BeanPropertyRowMapper.newInstance(Vet.class)));

    // Retrieve the list of all possible specialties.
    final List<Specialty> specialties = this.jdbcTemplate.query(
        "SELECT id, name FROM specialties",
        BeanPropertyRowMapper.newInstance(Specialty.class));

    // Build each vet's list of specialties.
    for (Vet vet : vets) {
        final List<Integer> vetSpecialtiesIds = this.jdbcTemplate.query(
            "SELECT specialty_id FROM vet_specialties WHERE vet_id=?",
            new BeanPropertyRowMapper<Integer>() {
                @Override
                public Integer mapRow(ResultSet rs, int row) throws SQLException {
                    return rs.getInt(1);
                }
            },
            vet.getId());
        for (int specialtyId : vetSpecialtiesIds) {
            Specialty specialty = EntityUtils.getById(specialties, Specialty.class, specialtyId);
            vet.addSpecialty(specialty);
        }
    }
    return vets;
}
 

@Test
public void testInsertion() {
	Payload sent = new Payload("hello", 42);
	channels.input().send(MessageBuilder.withPayload(sent).build());
	Payload result = jdbcOperations.query("select a, b from foobar", new BeanPropertyRowMapper<>(Payload.class)).get(0);
	Assert.assertThat(result, Matchers.samePropertyValuesAs(sent));
}
 

/**
 * Fetch a sut node info records matching the given test ID
 * @param testId the test ID
 * @return A list of records
 * @throws DataNotFoundException if no records are found that match the query
 */
public List<SutNodeInfo> fetchByTest(int testId) throws DataNotFoundException {
    return runQueryMany("SELECT * FROM sut_node_info sn,test_sut_node_link tsnl " +
                    "WHERE sn.sut_node_id = tsnl.sut_node_id AND tsnl.test_id = ?",
            new BeanPropertyRowMapper<>(SutNodeInfo.class),
            testId);
}
 

/**
 * @方法名 findAllBySysTableInfoAndUsername
 * @功能 TODO(这里用一句话描述这个方法的作用)
 * @参数 @param sysDbmsTabsInfo
 * @参数 @return
 * @返回 List<SysDbmsTabsInfo>
 * @author Administrator
 * @throws
 */
public List<SysDbmsTabsInfo> findAllBySysTableInfoAndUsername(SysDbmsTabsInfo sysDbmsTabsInfo) {
	// 多条件时循环查询并找出userindex都有的表
	StringBuilder stringBuilder = new StringBuilder();
	stringBuilder.append("select distinct * from sys_dbms_tabs_info a ");
	stringBuilder.append("  where a.uuid in ( ");
	stringBuilder.append("   select b.tabs_id from sys_roles_tabs_info b ");
	stringBuilder.append("    where b.role_id in (");
	stringBuilder.append("     select c.roles_id from sys_user_roles_info c");
	stringBuilder.append("      where c.user_id in ( ");
	stringBuilder.append("       select d.uuid from sys_user_base_info d");
	stringBuilder.append("        where d.user_name = '" + sysDbmsTabsInfo.getCreateUser() + "'");
	stringBuilder.append("      ) and c.checked = 1");
	stringBuilder.append("    ) ");
	stringBuilder.append("  ) and a.delete_flag = 0");
	if (sysDbmsTabsInfo.getTypeUuid() != null && !"".equals(sysDbmsTabsInfo.getTypeUuid())) {
		stringBuilder.append("  and a.type_uuid = '" + sysDbmsTabsInfo.getTypeUuid() + "' ");
	}
	
	if (sysDbmsTabsInfo.getJdbcUuid() != null && !"".equals(sysDbmsTabsInfo.getJdbcUuid())) {
		stringBuilder.append("  and a.jdbc_uuid = '" + sysDbmsTabsInfo.getJdbcUuid() + "' ");
	}
	stringBuilder.append("  order by a.tabs_order ");
	NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(jdbcTemplate);
	List<SysDbmsTabsInfo> tabsList = template.query(stringBuilder.toString(), new BeanPropertyRowMapper<>(SysDbmsTabsInfo.class));
	// 多条件查询
	return tabsList;
}
 

@Override
public <E> List<E> search(String sql, List<Object> values, Class<E> e) {
	if (StringUtils.isEmpty(sql))
		return new ArrayList<E>();
	if (values == null)
		values = new ArrayList<Object>();
	logger.info("sql : " + sql + " values:" + values);
	return this.getJdbcTemplate().query(sql, values.toArray(),
			new BeanPropertyRowMapper<E>(e));
}
 

public String get(String key) {
    String sql = "SELECT " + COLUMNS + " FROM " + TABLE_NAME
            + " WHERE config_key=:configKey LIMIT 1 ";
    Map<String, Object> param = new HashMap<String, Object>();
    param.put("configKey", key);
    List<ConfigEntry> configEntryList = namedParameterJdbcTemplate.query(sql, param,
            new BeanPropertyRowMapper<>(ConfigEntry.class));
    if (configEntryList == null || configEntryList.size() == 0) {
        return null;
    } else {
        return configEntryList.get(0).getConfigValue();
    }
}
 

/**
 * BeanPropertyRowMapper 包装
 */
public static <T> PageBean<T> queryPageByBeanMppaerNativeSqlString(final JdbcTemplate jdbcTemplate, final SqlUtils.Dialect dbDialect,
                                                                   final String queryNativeSql, Object[] queryArgs,
                                                                   final String countNativeSql, Object[] countArgs,
                                                                   int currentPageNo, int pageSize,
                                                                   Class<T> resultClass) {
    return queryPageByNativeSqlString(jdbcTemplate, dbDialect,
            queryNativeSql, queryArgs,
            countNativeSql, countArgs,
            currentPageNo, pageSize, new BeanPropertyRowMapper(resultClass));
}
 

@Override
public List<Department> findDepartmentsOfCompany(Company company) {
    String query = "SELECT pid, company_pid, name" +
            "           FROM department " +
            "           WHERE company_pid = :pid" +
            "           ORDER BY pid";

    MapSqlParameterSource params = new MapSqlParameterSource()
            .addValue("pid", company.getPid());

    return jdbcTemplate.query(query, params, BeanPropertyRowMapper.newInstance(Department.class));
}
 

@Override
public User queryByName(String username) {
    try {
        return jdbcTemplate.queryForObject("SELECT * FROM user WHERE username = ?",
            new BeanPropertyRowMapper<>(User.class), username);
    } catch (EmptyResultDataAccessException e) {
        return null;
    }
}
 

@Override
public User queryByName(String name) {

    try {
        User user = jdbcTemplate.queryForObject("SELECT * FROM user WHERE name = ?",
            new BeanPropertyRowMapper<>(User.class), name);
        log.info("[Query] name = {}, result = {}", name, user);
        return user;
    } catch (EmptyResultDataAccessException e) {
        return null;
    }
}
 

@Override
public List<T> search(String sql, List<Object> values) {
	if (StringUtils.isEmpty(sql))
		return new ArrayList<T>();
	if (values == null)
		values = new ArrayList<Object>();
	if (log.isDebugEnabled()) {
		log.debug("sql : " + sql + " values:" + values);
	}
	List<T> list = this.getJdbcTemplate().query(sql, values.toArray(),
			new BeanPropertyRowMapper<T>(this.persistentClass));
	return list == null ? new ArrayList<T>() : list;
}
 

public List<Operation> findAllByApiId(Long id) {
	StringBuilder sql = new StringBuilder(170);
	sql.append("SELECT PATH ");
	sql.append("from OPERATIONS OP ");
	sql.append("INNER JOIN RESOURCES RES ON OP.RESOURCE_ID = RES.ID ");
	sql.append("INNER JOIN APIS API ON RES.API_ID = API.ID ");
	sql.append("WHERE API.ID = ? ");

	return jdbcTemplate.query(sql.toString(), new Object[] { id },
			new BeanPropertyRowMapper<Operation>(Operation.class));
}