Java Code Examples for org.springframework.jdbc.core.BeanPropertyRowMapper

The following examples show how to use org.springframework.jdbc.core.BeanPropertyRowMapper. These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
@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("****************************************************");
	}
}
 
Example 2
@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;
}
 
Example 3
Source Project: audit4j-demo   Source File: JdbcVisitRepositoryImpl.java    License: Apache License 2.0 6 votes vote down vote up
@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;
}
 
Example 4
/**
 * @方法名 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;

}
 
Example 5
@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;
}
 
Example 6
Source Project: FastSQL   Source File: BaseDAO.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * 通过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()返回多条数据");
    }
}
 
Example 7
Source Project: DevOps-for-Web-Development   Source File: JdbcPetRepositoryImpl.java    License: MIT License 5 votes vote down vote up
@Override
public List<PetType> findPetTypes() throws DataAccessException {
    Map<String, Object> params = new HashMap<>();
    return this.namedParameterJdbcTemplate.query(
        "SELECT id, name FROM types ORDER BY name",
        params,
        BeanPropertyRowMapper.newInstance(PetType.class));
}
 
Example 8
Source Project: Demo   Source File: IStudentDaoImpl.java    License: Apache License 2.0 5 votes vote down vote up
@Override
public Student findStudentById(int id) {
    // BeanPropertyRowMapper 使获取的 List 结果列表的数据库字段和实体类自动对应
    List<Student> list = jdbcTemplate.query("select * from student where id = ?", new Object[]{id}, new BeanPropertyRowMapper(Student.class));
    if(list!=null && list.size()>0){
        Student student = list.get(0);
        return student;
    }else{
        return null;
    }
}
 
Example 9
Source Project: Demo   Source File: IStudentDaoImpl.java    License: Apache License 2.0 5 votes vote down vote up
@Override
public List<Student> findStudentList() {
    // 使用Spring的JdbcTemplate查询数据库,获取List结果列表,数据库表字段和实体类自动对应,可以使用BeanPropertyRowMapper
    List<Student> list = jdbcTemplate.query("select * from student", new Object[]{}, new BeanPropertyRowMapper(Student.class));
    if(list!=null && list.size()>0){
        return list;
    }else{
        return null;
    }
}
 
Example 10
/**
 * 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;
}
 
Example 11
Source Project: star-zone   Source File: ChatMsgRepository.java    License: Apache License 2.0 5 votes vote down vote up
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;
}
 
Example 12
Source Project: star-zone   Source File: ConfigEntryRepository.java    License: Apache License 2.0 5 votes vote down vote up
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();
    }
}
 
Example 13
Source Project: star-zone   Source File: UserScoreRepository.java    License: Apache License 2.0 5 votes vote down vote up
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;
}
 
Example 14
Source Project: batch-scheduler   Source File: UserDaoImpl.java    License: MIT License 5 votes vote down vote up
@Override
public UserDetailsEntity findById(String userId) {
    RowMapper<UserDetailsEntity> rowMapper = new BeanPropertyRowMapper<UserDetailsEntity>(UserDetailsEntity.class);

    UserDetailsEntity detailsEntity = jdbcTemplate.queryForObject(sqlText.getSql("sys023"), rowMapper, userId);

    try {
        String domainId = jdbcTemplate.queryForObject("select domain_id from sys_domain_authorization where user_id = ? and default_domain = 1", String.class, userId);
        detailsEntity.setDomainId(domainId);
    } catch (Exception e) {
        e.printStackTrace();
    }

    return detailsEntity;
}
 
Example 15
Source Project: spring-boot   Source File: StoredProcedure2.java    License: MIT License 5 votes vote down vote up
@PostConstruct
public void init() {
    // o_name and O_NAME, same
    jdbcTemplate.setResultsMapCaseInsensitive(true);

    // Convert o_c_book SYS_REFCURSOR to List<Book>
    simpleJdbcCallRefCursor = new SimpleJdbcCall(jdbcTemplate)
            .withProcedureName("get_book_by_name")
            .returningResultSet("o_c_book",
                    BeanPropertyRowMapper.newInstance(Book.class));

}
 
Example 16
/**
 * 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;
}
 
Example 17
Source Project: geode-demo-application   Source File: AlertsDerbyDAO.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * Selects the most recent row from Derby
 * 
 */
public Alert selectMostRecentRow() {
	@SuppressWarnings({ "unchecked", "rawtypes" })
	List<Alert> alert = getJdbcTemplate().query("SELECT * FROM MESSAGE_TABLE ORDER BY id ASC", new BeanPropertyRowMapper(Alert.class));
	return alert.get(0);
	
}
 
Example 18
/**
 * 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;
}
 
Example 19
Source Project: DevOps-for-Web-Development   Source File: JdbcVetRepositoryImpl.java    License: MIT License 5 votes vote down vote up
/**
 * 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;
}
 
Example 20
Source Project: docker-workflow-plugin   Source File: JdbcOwnerRepositoryImpl.java    License: MIT License 5 votes vote down vote up
/**
 * 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;
}
 
Example 21
Source Project: maven-framework-project   Source File: JdbcEmployeeDAOImpl.java    License: MIT License 5 votes vote down vote up
@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;
}
 
Example 22
@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));
}
 
Example 23
Source Project: batch-scheduler   Source File: GroupTaskDaoImpl.java    License: MIT License 5 votes vote down vote up
@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;
}
 
Example 24
Source Project: snakerflow   Source File: SpringJdbcAccess.java    License: Apache License 2.0 5 votes vote down vote up
@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;
	}
}
 
Example 25
/**
 * @方法名 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;
}
 
Example 26
Source Project: spring-boot   Source File: JdbcTemplateUtils.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * 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));
}
 
Example 27
@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));
}
 
Example 28
@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;
    }
}
 
Example 29
@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;
    }
}
 
Example 30
@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;
}