Java SqlParameterSource類代碼示例

import org.springframework.jdbc.core.namedparam.SqlParameterSource; //導入依賴的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.SqlParameterSource; //導入依賴的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.SqlParameterSource; //導入依賴的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.SqlParameterSource; //導入依賴的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.SqlParameterSource; //導入依賴的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.SqlParameterSource; //導入依賴的package包/類
/**
 * Executes a given procedure against the datasource. 
 * @param procedureName The name of the procedure to execute.
 * @param parameters The parameters for the procedure.
 * @return The Map of returned values from the procedure.
 */
public Map<String, ?> executeProcedure(String procedureName, Map<String, ?> parameters)
{
    SimpleJdbcCall call = new SimpleJdbcCall(this.datasource).withSchemaName("lodbot").withProcedureName(procedureName);
    SqlParameterSource callParameters = new MapSqlParameterSource(parameters);
    return call.execute(callParameters);
}
 

import org.springframework.jdbc.core.namedparam.SqlParameterSource; //導入依賴的package包/類
public E selectOneWhere(String sqlCondition, SqlParameterSource parameterSource) {
    //sql
    String sql = "SELECT * FROM " + tableName + " WHERE " + sqlCondition;

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


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

import org.springframework.jdbc.core.namedparam.SqlParameterSource; //導入依賴的package包/類
private SqlParameterSource getSqlParameterByModel(User user) {

		// Unable to handle List<String> or Array
		// BeanPropertySqlParameterSource

		MapSqlParameterSource paramSource = new MapSqlParameterSource();
		paramSource.addValue("id", user.getId());
		paramSource.addValue("name", user.getName());
		paramSource.addValue("email", user.getEmail());
		paramSource.addValue("login", user.getLogin());
		paramSource.addValue("address", user.getAddress());
		paramSource.addValue("password", user.getPassword());
		paramSource.addValue("newsletter", user.isNewsletter());

		// join String
		paramSource.addValue("framework", convertListToDelimitedString(user.getFramework()));
		paramSource.addValue("sex", user.getSex());
		paramSource.addValue("number", user.getNumber());
		paramSource.addValue("country", user.getCountry());
		paramSource.addValue("skill", convertListToDelimitedString(user.getSkill()));

		return paramSource;
	}
 

import org.springframework.jdbc.core.namedparam.SqlParameterSource; //導入依賴的package包/類
private String queryToCSV(final SqlParameterSource queryParams, final String sql) {
    final int bufferSize = 16384;

    try (final StringWriter writer = new StringWriter(bufferSize);
            final CSVWriter csvWriter = new CSVWriter(writer, ';')) {

        jdbcTemplate.query(sql, queryParams, resultSet -> {
            try {
                csvWriter.writeAll(resultSet, true);
                csvWriter.flush();

                return null;

            } catch (IOException ioe) {
                throw new RuntimeException(ioe);
            }
        });

        return writer.getBuffer().toString();
    } catch (final IOException e) {
        throw new RuntimeException(e);
    }
}
 

import org.springframework.jdbc.core.namedparam.SqlParameterSource; //導入依賴的package包/類
@Override
@Transactional
public TaskExecution merge (TaskExecution aTaskExecution) {
  TaskExecution current = jdbc.queryForObject("select * from task_execution where id = :id for update", Collections.singletonMap("id", aTaskExecution.getId()),this::jobTaskRowMappper);
  SimpleTaskExecution merged = SimpleTaskExecution.createForUpdate(aTaskExecution);  
  if(current.getStatus().isTerminated() && aTaskExecution.getStatus() == TaskStatus.STARTED) {
    merged = SimpleTaskExecution.createForUpdate(current);
    merged.setStartTime(aTaskExecution.getStartTime());
  }
  else if (aTaskExecution.getStatus().isTerminated() && current.getStatus() == TaskStatus.STARTED) {
    merged.setStartTime(current.getStartTime());      
  }
  SqlParameterSource sqlParameterSource = createSqlParameterSource(merged);
  jdbc.update("update task_execution set serialized_execution=:serializedExecution,status=:status,start_time=:startTime,end_time=:endTime where id = :id ", sqlParameterSource);
  return merged;
}
 

import org.springframework.jdbc.core.namedparam.SqlParameterSource; //導入依賴的package包/類
/**
 * Put a key/value pair in the database
 *
 * @param key
 * @param value
 */
public void put(String key, String value) {
    String sql = String.format("select rap_insert_%s(:key, :content);", tableName);
    PGobject valueJson = new PGobject();
    valueJson.setType("jsonb");
    try {
        valueJson.setValue(value);
    } catch (SQLException e) {
        log.error(ExceptionToString.format(e));
        valueJson = null;
    }
    if (valueJson != null) {
        SqlParameterSource paramSource = new MapSqlParameterSource("key", key).addValue("content", valueJson);
        RowMapper<Boolean> rowMapper = new RowMapper<Boolean>() {
            @Override
            public Boolean mapRow(ResultSet rs, int rowNum) throws SQLException {
                return true;
            }
        };
        namedJdbcTemplate.query(sql, paramSource, rowMapper);
    }
}
 

import org.springframework.jdbc.core.namedparam.SqlParameterSource; //導入依賴的package包/類
public List<RaptureFolderInfo> getChildren(String prefix) {
    if (!prefix.startsWith("/")) {
        prefix = "/" + prefix;
    }
    String sql = String.format("SELECT SUB, N FROM %s WHERE ID=:prefix;", folderTable);
    SqlParameterSource paramSource = new MapSqlParameterSource("prefix", prefix);
    RowMapper<RaptureFolderInfo> rowMapper = new RowMapper<RaptureFolderInfo>() {
        @Override
        public RaptureFolderInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
            String value = rs.getString(1);
            String nature = rs.getObject(2).toString();
            RaptureFolderInfo info = new RaptureFolderInfo();
            info.setName(value);
            info.setFolder(nature.equals("folder"));
            return info;

        }
    };
    return namedJdbcTemplate.query(sql, paramSource, rowMapper);
}
 

import org.springframework.jdbc.core.namedparam.SqlParameterSource; //導入依賴的package包/類
public static boolean tableExists(NamedParameterJdbcTemplate namedParameterJdbcTemplate, String tableName) throws PostgresException {
    String sql = "SELECT EXISTS (\n"
            + "    SELECT 1 \n"
            + "    FROM   pg_catalog.pg_class c\n"
            + "    JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
            + "    WHERE  n.nspname = 'public'\n"
            + "    AND    c.relname = :table_name\n"
            + ")";

    SqlParameterSource paramSource = new MapSqlParameterSource("table_name", tableName);
    try {
        return namedParameterJdbcTemplate.queryForObject(sql, paramSource, Boolean.class);
    } catch (DataAccessException e) {
        throw new PostgresException(String.format("Error while checking if table [%s] exists", tableName), e);
    }
}
 

import org.springframework.jdbc.core.namedparam.SqlParameterSource; //導入依賴的package包/類
private void insertValue(NamedParameterJdbcTemplate jdbcTemplate, int i) throws SQLException {
    String value = String.format("{\"id\": "
            + "\"%s\", \"max\": 2, \"message\": \"what upz\", \"lastSeen\": 12345, \"progress\": 1}", i);
    PGobject valueJson = new PGobject();
    valueJson.setType("jsonb");
    valueJson.setValue(value);
    SqlParameterSource params = new MapSqlParameterSource().addValue("keyIn", "key" + i).addValue("contentIn", valueJson);
    jdbcTemplate.update("INSERT INTO activity\n"
            + "VALUES(:keyIn, :contentIn, now());\n", params);
}
 

import org.springframework.jdbc.core.namedparam.SqlParameterSource; //導入依賴的package包/類
/**
 * Exemple de requ�te conditionnelle param�tr�e
 */
public List<User> findUsersByExample(final User example) {
    StringBuilder sb = new StringBuilder("select * from users where 1=1 ");

    if (example.getId() != 0) {
        sb.append(" and id = :idParam ");
    }
    if (!StringUtils.isEmpty(example.getName())) {
        sb.append(" and name = :userName ");
    }
    if (!StringUtils.isEmpty(example.getMail())) {
        sb.append(" and email = :userEmail ");
    }

    SqlParameterSource params = new MapSqlParameterSource()
            .addValue("idParam", example.getId())
            .addValue("userName", example.getName())
            .addValue("userEmail", example.getMail());

    return namedParameterJdbcTemplate.query(sb.toString(), params, new UserRowMapper());
}