I defined an entity class:
class Clazz{
private long id;
private String cityHash;
public Clazz(){}
public Clazz(String cityHash){
this.cityHash = cityHash
}
}
mysql: table city_hash
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`city_hash` bigint(20) unsigned NOT NULL COMMENT'Google city hash64',
I use SimpleJdbcInsert to insert data:
private SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(getJdbcTemplate()).withTableName("city_hash").usingGeneratedKeyColumns("id");
Clazz clazz = new Clazz("12040412458563268888")
Number newId = simpleJdbcInsert.executeAndReturnKey(clazz);
Exception:
Caused by: org.springframework.dao.TransientDataAccessResourceException: PreparedStatementCallback; SQL [];
Cannot convert class java.lang.String to SQL type requested due to java.lang.NumberFormatException- For input string: "12040412458563268888";
nested exception is java. sql.SQLException: Cannot convert class java.lang.String to SQL type requested due to java.lang.NumberFormatException-For input string: "12040412458563268888"
I encountered this exception in spring 3+, it can insert data in spring 2.5.
I think for database bigint(20) unsigned should not be automatically mapped as Long in java
Source code: (fun: AbstractJdbcInsert.executeInsertAndReturnKeyHolderInternal(List)) 3+
if (this.tableMetaDataContext.isGetGeneratedKeysSupported()) {
getJdbcTemplate().update(
new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
PreparedStatement ps = prepareStatementForGeneratedKeys(con);
setParameterValues(ps, values, getInsertTypes());
return ps;
}
},
keyHolder);
}
2.5
if (this.tableMetaDataContext.isGetGeneratedKeysSupported()) {
this.jdbcTemplate.update(new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
PreparedStatement ps = AbstractJdbcInsert.this.prepareStatementForGeneratedKeys(con);
AbstractJdbcInsert.this.setParameterValues(ps, values, (int[])null);
return ps;
}
}, keyHolder);
return keyHolder;
}
Comment From: rstoyanchev
Version 3+ is unclear. If you mean 3.x, that branch is old and unsupported. For a list of currently supported versions, see the support page. If you could, please confirm the actual version you have tried this with?
Comment From: EwainCai
@rstoyanchev Yes, I tested it on spring 3.2.8, spring 4.2.8, spring 5.2.19 All versions after spring3+ have this issue.
Comment From: EwainCai
spring 5.2.19
if (this.tableMetaDataContext.isGetGeneratedKeysSupported()) {
this.getJdbcTemplate().update((con) -> {
PreparedStatement ps = this.prepareStatementForGeneratedKeys(con);
this.setParameterValues(ps, values, this.getInsertTypes());
return ps;
}, keyHolder);
}
Comment From: EwainCai
org.springframework.jdbc.core.StatementCreatorUtils:
private static void setValue(PreparedStatement ps, int paramIndex, int sqlType,
@Nullable String typeName, @Nullable Integer scale, Object inValue) throws SQLException {
if (inValue instanceof SqlTypeValue) {
((SqlTypeValue) inValue).setTypeValue(ps, paramIndex, sqlType, typeName);
}
else if (inValue instanceof SqlValue) {
((SqlValue) inValue).setValue(ps, paramIndex);
}
else if (sqlType == Types.VARCHAR || sqlType == Types.LONGVARCHAR ) {
ps.setString(paramIndex, inValue.toString());
}
else if (sqlType == Types.NVARCHAR || sqlType == Types.LONGNVARCHAR) {
ps.setNString(paramIndex, inValue.toString());
}
else if ((sqlType == Types.CLOB || sqlType == Types.NCLOB) && isStringValue(inValue.getClass())) {
String strVal = inValue.toString();
if (strVal.length() > 4000) {
// Necessary for older Oracle drivers, in particular when running against an Oracle 10 database.
// Should also work fine against other drivers/databases since it uses standard JDBC 4.0 API.
if (sqlType == Types.NCLOB) {
ps.setNClob(paramIndex, new StringReader(strVal), strVal.length());
}
else {
ps.setClob(paramIndex, new StringReader(strVal), strVal.length());
}
}
else {
// Fallback: setString or setNString binding
if (sqlType == Types.NCLOB) {
ps.setNString(paramIndex, strVal);
}
else {
ps.setString(paramIndex, strVal);
}
}
}
else if (sqlType == Types.DECIMAL || sqlType == Types.NUMERIC) {
if (inValue instanceof BigDecimal) {
ps.setBigDecimal(paramIndex, (BigDecimal) inValue);
}
else if (scale != null) {
ps.setObject(paramIndex, inValue, sqlType, scale);
}
else {
ps.setObject(paramIndex, inValue, sqlType);
}
}
else if (sqlType == Types.BOOLEAN) {
if (inValue instanceof Boolean) {
ps.setBoolean(paramIndex, (Boolean) inValue);
}
else {
ps.setObject(paramIndex, inValue, Types.BOOLEAN);
}
}
else if (sqlType == Types.DATE) {
if (inValue instanceof java.util.Date) {
if (inValue instanceof java.sql.Date) {
ps.setDate(paramIndex, (java.sql.Date) inValue);
}
else {
ps.setDate(paramIndex, new java.sql.Date(((java.util.Date) inValue).getTime()));
}
}
else if (inValue instanceof Calendar) {
Calendar cal = (Calendar) inValue;
ps.setDate(paramIndex, new java.sql.Date(cal.getTime().getTime()), cal);
}
else {
ps.setObject(paramIndex, inValue, Types.DATE);
}
}
else if (sqlType == Types.TIME) {
if (inValue instanceof java.util.Date) {
if (inValue instanceof java.sql.Time) {
ps.setTime(paramIndex, (java.sql.Time) inValue);
}
else {
ps.setTime(paramIndex, new java.sql.Time(((java.util.Date) inValue).getTime()));
}
}
else if (inValue instanceof Calendar) {
Calendar cal = (Calendar) inValue;
ps.setTime(paramIndex, new java.sql.Time(cal.getTime().getTime()), cal);
}
else {
ps.setObject(paramIndex, inValue, Types.TIME);
}
}
else if (sqlType == Types.TIMESTAMP) {
if (inValue instanceof java.util.Date) {
if (inValue instanceof java.sql.Timestamp) {
ps.setTimestamp(paramIndex, (java.sql.Timestamp) inValue);
}
else {
ps.setTimestamp(paramIndex, new java.sql.Timestamp(((java.util.Date) inValue).getTime()));
}
}
else if (inValue instanceof Calendar) {
Calendar cal = (Calendar) inValue;
ps.setTimestamp(paramIndex, new java.sql.Timestamp(cal.getTime().getTime()), cal);
}
else {
ps.setObject(paramIndex, inValue, Types.TIMESTAMP);
}
}
else if (sqlType == SqlTypeValue.TYPE_UNKNOWN || (sqlType == Types.OTHER &&
"Oracle".equals(ps.getConnection().getMetaData().getDatabaseProductName()))) {
if (isStringValue(inValue.getClass())) {
ps.setString(paramIndex, inValue.toString());
}
else if (isDateValue(inValue.getClass())) {
ps.setTimestamp(paramIndex, new java.sql.Timestamp(((java.util.Date) inValue).getTime()));
}
else if (inValue instanceof Calendar) {
Calendar cal = (Calendar) inValue;
ps.setTimestamp(paramIndex, new java.sql.Timestamp(cal.getTime().getTime()), cal);
}
else {
// Fall back to generic setObject call without SQL type specified.
ps.setObject(paramIndex, inValue);
}
}
else {
// Fall back to generic setObject call with SQL type specified.
ps.setObject(paramIndex, inValue, sqlType);
}
}
org.springframework.jdbc.core.metadata.GenericTableMetaDataProvider
private void processTableColumns(DatabaseMetaData databaseMetaData, TableMetaData tmd) {
ResultSet tableColumns = null;
String metaDataCatalogName = metaDataCatalogNameToUse(tmd.getCatalogName());
String metaDataSchemaName = metaDataSchemaNameToUse(tmd.getSchemaName());
String metaDataTableName = tableNameToUse(tmd.getTableName());
if (logger.isDebugEnabled()) {
logger.debug("Retrieving meta-data for " + metaDataCatalogName + '/' +
metaDataSchemaName + '/' + metaDataTableName);
}
try {
tableColumns = databaseMetaData.getColumns(
metaDataCatalogName, metaDataSchemaName, metaDataTableName, null);
while (tableColumns.next()) {
String columnName = tableColumns.getString("COLUMN_NAME");
int dataType = tableColumns.getInt("DATA_TYPE");
if (dataType == Types.DECIMAL) {
String typeName = tableColumns.getString("TYPE_NAME");
int decimalDigits = tableColumns.getInt("DECIMAL_DIGITS");
// Override a DECIMAL data type for no-decimal numerics
// (this is for better Oracle support where there have been issues
// using DECIMAL for certain inserts (see SPR-6912))
if ("NUMBER".equals(typeName) && decimalDigits == 0) {
dataType = Types.NUMERIC;
if (logger.isDebugEnabled()) {
logger.debug("Overriding meta-data: " + columnName + " now NUMERIC instead of DECIMAL");
}
}
}
boolean nullable = tableColumns.getBoolean("NULLABLE");
TableParameterMetaData meta = new TableParameterMetaData(columnName, dataType, nullable);
this.tableParameterMetaData.add(meta);
if (logger.isDebugEnabled()) {
logger.debug("Retrieved meta-data: '" + meta.getParameterName() + "', sqlType=" +
meta.getSqlType() + ", nullable=" + meta.isNullable());
}
}
}
catch (SQLException ex) {
if (logger.isWarnEnabled()) {
logger.warn("Error while retrieving meta-data for table columns. " +
"Consider specifying explicit column names -- for example, via SimpleJdbcInsert#usingColumns().",
ex);
}
// Clear the metadata so that we don't retain a partial list of column names
this.tableParameterMetaData.clear();
}
finally {
JdbcUtils.closeResultSet(tableColumns);
}
}
Maybe we not only need to get the 'DATA_TYPE', but also adapt the java types(java.sql.Types) according to the column length!
Comment From: EwainCai
Can someone please reply to this issue?
Comment From: sbrannen
Hi @EwainCai,
What database (and version) are you testing against?
In addition, SimpleJdbcInsert does not have an executeAndReturnKey(...) method that accepts an Object. So it appears that your Number newId = simpleJdbcInsert.executeAndReturnKey(clazz); example would not compile.
If you can provide us a minimal sample application that demonstrates the behavior you are experiencing (preferably via a GitHub repository or zip file that we can download and experiment with), that would significantly increase the likelihood that the team can investigate further.
Thanks
Comment From: spring-projects-issues
If you would like us to look at this issue, please provide the requested information. If the information is not provided within the next 7 days this issue will be closed.
Comment From: spring-projects-issues
Closing due to lack of requested feedback. If you would like us to look at this issue, please provide the requested information and we will re-open the issue.
Comment From: EwainCai
Hi @EwainCai,
What database (and version) are you testing against?
In addition,
SimpleJdbcInsertdoes not have anexecuteAndReturnKey(...)method that accepts anObject. So it appears that yourNumber newId = simpleJdbcInsert.executeAndReturnKey(clazz);example would not compile.If you can provide us a minimal sample application that demonstrates the behavior you are experiencing (preferably via a GitHub repository or zip file that we can download and experiment with), that would significantly increase the likelihood that the team can investigate further.
Thanks
@sbrannen Sorry for providing wrong information.
The example below is the method I actually use.
spring-jdbc/src/main/java/org/springframework/jdbc/core/simple/SimpleJdbcInsert.java
@Override
public Number executeAndReturnKey(Map<String, ?> args) {
return doExecuteAndReturnKey(args);
}
private SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(getJdbcTemplate())
.withTableName("city_hash")
.usingGeneratedKeyColumns("id");
Map<String, String> map = new HashMap<>();
map.put("city_hash", "12040412458563268888");
Number newId = simpleJdbcInsert.executeAndReturnKey(map);
Comment From: snicoll
If you can provide us a minimal sample application that demonstrates the behavior you are experiencing (preferably via a GitHub repository or zip file that we can download and experiment with), that would significantly increase the likelihood that the team can investigate further.
Can you please provide that as I am still not sure what database you're using and we can't justify trying to guess what it is.
Comment From: spring-projects-issues
If you would like us to look at this issue, please provide the requested information. If the information is not provided within the next 7 days this issue will be closed.
Comment From: spring-projects-issues
Closing due to lack of requested feedback. If you would like us to look at this issue, please provide the requested information and we will re-open the issue.