java - spring jdbctemplate原始无效列类型的类型

Question

Java - Spring JDBC - Oracle

我的 table 看起来像

id, RAW(16)
name, varchar(50)

@Autowired

    private JdbcTemplate jdbcTemplate;

String queryStr = "insert into myt (id, name) values(:id, :name)";

MapSqlParameterSource param = new MapSqlParameterSource();
param.addValue("id", UUID.randomUUID(), Types.Binary);
param.addValue("name", "my name", Types.VARCHAR);

jdbcTemplate.update(queryStr, param);

我收到错误java.sql.SQLException无效的列类型

Answer 1

您使用的 JdbcTemplate 不支持命名参数。有了它你的请求应该是这样的:

private JdbcTemplate jdbcTemplate;

@Autowired
public void setDataSource(DataSource dataSource) {
  jdbcTemplate = new JdbcTemplate(dataSource);
}

public boolean create(Organization org) {
  var query = """
    INSERT INTO organization
      (company_name, year_of_incorporation, postal_code, employee_count, slogan)
    VALUES (?, ?, ?, ?, ?)""";
  
  var args = new Object[] { 
    org.getCompanyName(), org.getYearOfIncorporation(), org.getPostalCode(),
    org.getEmployeeCount(), org.getSlogan()
  };
  
  return jdbcTemplate.update(query, args) == 1;

如果您想使用命名参数，您最好使用NamedParameterJdbcTemplate。有了它你可以做这样的事情:

public Organization getOrganization(Integer id) {
  var query = """
    SELECT id, company_name, year_of_incorporation, postal_code, employee_count, slogan
    FROM organization
    WHERE id = :ID""";
  var params = new MapSqlParameterSource("ID", id);

  return namedParamJdbcTemplate.queryForObject(query, params, new OrganizationRowMapper());
}

或

public boolean create(Organization org) {
  var query = """
    INSERT INTO organization
      (company_name, year_of_incorporation, postal_code, employee_count, slogan)
    VALUES (:companyName, :yearOfIncorporation, :postalCode, :employeeCount, :slogan)""";
  var params = new BeanPropertySqlParameterSource(org);

  return namedParamJdbcTemplate.update(query, params) == 1;
}