Java BeanPropertySqlParameterSource类代码示例

import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource; //导入依赖的package包/类
@Override
public Object add(Object object) {
	SqlModel<Object> sqlModel = sqlBuilder.insertSelectiveSql(object);
	checkSqlModel(sqlModel);
	
	SqlParameterSource paramSource =  new BeanPropertySqlParameterSource(object);
	KeyHolder generatedKeyHolder =  new  GeneratedKeyHolder();
	namedPjdbcTemplate.update(sqlModel.getSql(), paramSource, generatedKeyHolder);
	Number num = generatedKeyHolder.getKey();
	
	String[] primaryKeys = sqlModel.getPrimaryKeys();
	if(primaryKeys != null && primaryKeys.length > 0){
		BeanWrapper beanWrapper = PropertyAccessorFactory.forBeanPropertyAccess(object);
		beanWrapper.setPropertyValue(primaryKeys[0], num);
	}
	
	return object;
}
 

import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource; //导入依赖的package包/类
@Override
public int[] batchUpdate(Collection<?> objects) {
	SqlParameterSource[] batchArgs = new BeanPropertySqlParameterSource[objects.size()];
	String sql = null;
	int index = 0;
	for (Object object : objects) {
		if(index == 0 ){
			//XXX: thinking 共享第一实体的sql
			SqlModel<Object> sqlModel = sqlBuilder.updateSql(object);
			sql = sqlModel.getSql();
		}
		batchArgs[index] = new BeanPropertySqlParameterSource(object);
		
		index ++;
	}
	int[] rows = namedPjdbcTemplate.batchUpdate(sql, batchArgs);
	return rows;
}
 

import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource; //导入依赖的package包/类
@Test
public void testInsert() {
    GeneratedAlwaysRecord record = new GeneratedAlwaysRecord();
    record.setId(100);
    record.setFirstName("Bob");
    record.setLastName("Jones");
    
    InsertStatementProvider<GeneratedAlwaysRecord> insertStatement = insert(record)
            .into(generatedAlways)
            .map(id).toProperty("id")
            .map(firstName).toProperty("firstName")
            .map(lastName).toProperty("lastName")
            .build()
            .render(RenderingStrategy.SPRING_NAMED_PARAMETER);
    
    SqlParameterSource parameterSource = new BeanPropertySqlParameterSource(insertStatement.getRecord());
    KeyHolder keyHolder = new GeneratedKeyHolder();
    
    int rows = template.update(insertStatement.getInsertStatement(), parameterSource, keyHolder);
    String generatedKey = (String) keyHolder.getKeys().get("FULL_NAME");
    
    assertThat(rows).isEqualTo(1);
    assertThat(generatedKey).isEqualTo("Bob Jones");
    
}
 

import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource; //导入依赖的package包/类
public int addChat(Chat chat) throws Exception {
	List<String> fields = Arrays.asList(
		SADisplayConstants.CHAT_ID,	SADisplayConstants.COLLAB_ROOM_ID, SADisplayConstants.USER_ORG_ID,
		SADisplayConstants.CREATED, SADisplayConstants.SEQ_NUM, SADisplayConstants.MESSAGE);
		
	QueryModel model = QueryManager.createQuery(SADisplayConstants.CHAT_TABLE).insertInto(fields);
	
	//generate a chatid if not set
	if (chat.getChatid() <= 0) {
		QueryModel idModel = QueryManager.createQuery("chat_seq").selectNextVal();
		int id = this.template.queryForObject(idModel.toString(), new MapSqlParameterSource(), Integer.class);
		chat.setChatid(id);
	}
	
	try {
		this.template.update(model.toString(), new BeanPropertySqlParameterSource(chat));
		return chat.getChatid();
	} catch(Exception e) {
		throw new Exception("Unhandled exception while persisting Chat entity:", e);
	}
}
 

import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource; //导入依赖的package包/类
@PUT
@Path("/{employeeId}")
public Employee editEmployee(final Employee employee, @PathParam("employeeId") Integer employeeId) {

	String sql = "update EMPLOYEE set name=:name, address=:address where id=:id";

	SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(employee);

	this.namedParameterJdbcTemplate.update(sql, namedParameters);

	sql = "select * from EMPLOYEE where id=:employeeId";
	SqlParameterSource namedParameters2 = new MapSqlParameterSource("employeeId", employeeId);
	return namedParameterJdbcTemplate.query(sql, namedParameters2, new EmployeeMapper()).get(0);
}
 

import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource; //导入依赖的package包/类
/**
 * 根据主键创建查询条件
 *
 * @param pathBase Querydsl query type
 * @param pk       主键
 * @return 查询条件
 */
public static QueryExample createExampleByPk(RelationalPathBase<?> pathBase, Object pk) {
    int numOfPk = pathBase.getPrimaryKey().getLocalColumns().size();
    Validate.isTrue(numOfPk > 0, "primaryKey not exists");
    QueryExample example = QueryExample.newInstance();
    if (numOfPk == 1) {
        example.equalsTo(pathBase.getPrimaryKey().getLocalColumns()
                .get(0).getMetadata().getName(), pk);
    } else {
        SqlParameterSource source = new BeanPropertySqlParameterSource(pk);
        for (Path<?> path : pathBase.getPrimaryKey().getLocalColumns()) {
            String name = path.getMetadata().getName();
            String humpName = humpName(name);
            example.equalsTo(humpName, source.getValue(name));
        }
    }
    return example;
}
 

import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource; //导入依赖的package包/类
private String runCommand(StepContext steoContext, String sqlCommand) throws Exception {

		String msg = "";
		if (sqlCommand.trim().toUpperCase().startsWith("SELECT")) {
			logger.debug("Executing: " + sqlCommand);
			List<Map<String, Object>> result = jdbcTemplate.queryForList(sqlCommand,
					new BeanPropertySqlParameterSource(steoContext));
			msg = "Result: " + result;
			logger.debug(msg);
		} else {
			logger.debug("Updating : " + sqlCommand);
			int updated = jdbcTemplate.update(sqlCommand, new BeanPropertySqlParameterSource(steoContext));
			msg = "Updated: " + updated + " rows";
			logger.debug(msg);
		}

		return msg;
	}
 

import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource; //导入依赖的package包/类
@Override
@Transactional
public String create(Alert alert) {
    log.debug("Inserting alert into SQL backend: " + alert);
    checkArgument(StringUtils.isBlank(alert.getId()), "alert id cannot be already set");

    String id = UUID.randomUUID().toString();
    alert.setId(id);
    String sql = "INSERT INTO Alert (id, created_by, message, priority, user_id,  acknowledged, created_date) " +
            "VALUES (:id, :createdBy, :message, :priority, :userId, :acknowledged, :createdDate)";

    BeanPropertySqlParameterSource source = new BeanPropertySqlParameterSource(alert);
    int results = this.namedJdbcTemplate.update(sql, source);
    log.debug("Got: " + results + " results");

    return id;
}
 

import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource; //导入依赖的package包/类
@Override
@Transactional(readOnly = true)
public List<Alert> readPage(PagingCriteria criteria) {
    log.debug("reading page with criteria: " + criteria);
    if (criteria == null || criteria.getLimit() == null || criteria.getStart() == null) {
        log.warn("criteria invalid - reading all instead of subset");
        return readAll();
    }
    String sql = "SELECT LIMIT :start :limit * FROM Alert ORDER BY created_date ASC";
    BeanPropertySqlParameterSource source = new BeanPropertySqlParameterSource(criteria);

    List<Alert> alerts = this.namedJdbcTemplate.query(sql, source, new AlertRowMapper());
    log.debug(alerts.size() + " alerts returned using criteria: " + criteria);

    return alerts;
}
 

import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource; //导入依赖的package包/类
@Override
@Transactional
public String create(Document document) {
    log.debug("Inserting Document into SQL backend: " + document);
    checkArgument(StringUtils.isBlank(document.getId()), "Document id cannot be already set");

    String id = UUID.randomUUID().toString();
    document.setId(id);
    String sql = "INSERT INTO Document (id, author, title, content, summary, group_id, state_, created_date) " +
            "VALUES (:id, :author, :title, :content, :summary, :groupId, :state, :createdDate)";

    BeanPropertySqlParameterSource source = new BeanPropertySqlParameterSource(document);
    int results = this.namedJdbcTemplate.update(sql, source);
    log.debug("Got: " + results + " results");
    document.setId(id);
    return id;
}
 

import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource; //导入依赖的package包/类
@Override
@Transactional(readOnly = true)
public List<Document> readPage(PagingCriteria criteria) {
    log.debug("reading page with criteria: " + criteria);
    if (criteria == null || criteria.getLimit() == null || criteria.getStart() == null) {
        log.warn("criteria invalid - reading all instead of subset");
        return readAll();
    }
    String sql = "SELECT LIMIT :start :limit * FROM Document ORDER BY created_date ASC";
    BeanPropertySqlParameterSource source = new BeanPropertySqlParameterSource(criteria);

    List<Document> documents = this.namedJdbcTemplate.query(sql, source, new DocumentRowMapper());
    log.debug(documents.size() + " Documents returned using criteria: " + criteria);

    return documents;
}
 

import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource; //导入依赖的package包/类
public <T> int delete(T t) throws DataAccessException {
    StringBuilder sql = new StringBuilder();
    sql.append(DELETE).append(SPACE).append(FROM).append(SPACE).append(t.getClass().getSimpleName().toLowerCase()).append(SPACE);
    sql.append(WHERE).append(SPACE).append(IDENTICAL).append(SPACE);
    PropertyDescriptor[] pds = BeanUtils.getPropertyDescriptors(t.getClass());
    BeanWrapper beanWrapper = PropertyAccessorFactory.forBeanPropertyAccess(t);
    for (int i = 0; i < pds.length; i++) {
        PropertyDescriptor pd = pds[i];
        String name = pd.getName();
        if (!CLASS.equals(name) && beanWrapper.isReadableProperty(name) && beanWrapper.getPropertyValue(name) != null) {
            sql.append(AND).append(SPACE).append(CamelCaseUtils.underscoreName(name)).append(SPACE);
            sql.append(EQUAL).append(SPACE).append(COLON).append(name).append(SPACE);
        }
    }
    return namedJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(t));
}
 

import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource; //导入依赖的package包/类
@Override
public Long insertUser(User u) {
	StringBuffer sql = new StringBuffer("");
	sql.append(" insert into user ");
	sql.append(" ( username, password ) ");
	sql.append(" values ( ");
	sql.append("   :username ");
	sql.append(" , :password ");
	sql.append(" ) ");
	
	SqlParameterSource paramSource = new BeanPropertySqlParameterSource(u);
	KeyHolder generatedKeyHolder = new GeneratedKeyHolder();
	int rows = getDao().update(sql.toString(), paramSource, generatedKeyHolder);
	
	if(rows > 0 ){
		Long userId = generatedKeyHolder.getKey().longValue();
		u.setId(userId);
	}
	
	return u.getId();
}
 

import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource; //导入依赖的package包/类
@Override
	public List<User> listUser(User query) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" select * ").append("\n");
		sql.append(" from user ").append("\n");
		sql.append(" where 1=1 ").append("\n");
		
		if(null != query.getId()){
			sql.append(" and id = :id  ").append("\n");
		}
		sql.append(" order by id desc ").append("\n");
		
		SqlParameterSource paramSource = new BeanPropertySqlParameterSource(query);
		
//		RowMapper<User> rowMapper = ParameterizedBeanPropertyRowMapper.newInstance(User.class);
		
	    BeanPropertyRowMapper<User> rowMapper = new BeanPropertyRowMapper<User>(User.class);
		
		return getDao().query(sql.toString(), paramSource, rowMapper);

	}
 

import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource; //导入依赖的package包/类
@Override
public void save(Owner owner) throws DataAccessException {
    BeanPropertySqlParameterSource parameterSource = new BeanPropertySqlParameterSource(owner);
    if (owner.isNew()) {
        Number newKey = this.insertOwner.executeAndReturnKey(parameterSource);
        owner.setId(newKey.intValue());
    } else {
        this.namedParameterJdbcTemplate.update(
            "UPDATE owners SET first_name=:firstName, last_name=:lastName, address=:address, " +
                "city=:city, telephone=:telephone WHERE id=:id",
            parameterSource);
    }
}