SimpleJdbcInsert mixes column names from different databases in MySQL

Question

I have recently upgraded spring framework from 5.0.3 to 5.3.12 and mysql from 5.7 to 8.

I am adding new columns in DB and corresponding fields in model class. but it is unable to store the value for newly added fields in DB. I have debugged the program the values are patched in model class but after the simplejdbcinsert api being called the values doesnt persist in DB.

the code is like this -

DAO call -

      logger.debug("Party Insert: " + logStr);
      ID = new SimpleJdbcInsert(dataSource).withTableName("TBL_PARTY")
      .usingGeneratedKeyColumns("Id").executeAndReturnKey(sqlParams).intValue();

model class -

public class Party extends GenericEntity{


private String partyType, name, partyCode, PANNumber;
    private String registrationId;
private String WebsiteAddress;
private boolean isActive = true;    
private String externalID;
private double openingBalance, closingBalance;

// new columns - fields
private String temp;
private int tempInt;
private byte tempTyniInt;
private boolean tempBol;


public String getTemp() {
    return temp;
}

public void setTemp(String temp) {
    this.temp = temp;
}

public String getWebsiteAddress() {
    return WebsiteAddress;
}
public void setWebsiteAddress(String websiteAddress) {
    WebsiteAddress = websiteAddress;
}
public String getPartyCode() {
    return partyCode;
}
public void setPartyCode(String partyCode) {
    this.partyCode = partyCode;
}
public String getPartyType() {
    return partyType;
}
public void setPartyType(String partyType) {
    this.partyType = partyType;
}
public String getName() {
    return name;
}
public void setName(String name) {
    this.name = name;
}

public String getRegistrationId() {
    return registrationId;
}
public void setRegistrationId(String registrationId) {
    this.registrationId = registrationId;
}
public boolean equals(Object obj) {
    
    return ReflectionUtil.compareObjects(this, obj);
}

public String getExternalID() {
    return externalID;
}
public void setExternalID(String externalID) {
    this.externalID = externalID;
}
public double getOpeningBalance() {
    return openingBalance;
}
public void setOpeningBalance(double openingBalance) {
    this.openingBalance = openingBalance;
}
public double getClosingBalance() {
    return closingBalance;
}
public void setClosingBalance(double closingBalance) {
    this.closingBalance = closingBalance;
}
public String toString()
{
    return StringUtil.objectToString(this).concat(super.toString());
}
public String getPANNumber() {
    return PANNumber;
}
public void setPANNumber(String pANNumber) {
    PANNumber = pANNumber;
}
public boolean getIsActive() {
    return isActive;
}
public void setIsActive(boolean isActive) {
    this.isActive = isActive;
}

public int getTempInt() {
    return tempInt;
}

public void setTempInt(int tempInt) {
    this.tempInt = tempInt;
}

public int getTempTyniInt() {
    return tempTyniInt;
}

public void setTempTyniInt(byte tempTyniInt) {
    this.tempTyniInt = tempTyniInt;
}

public boolean getTempBol() {
    return tempBol;
}

public void setTempBol(boolean tempBol) {
    this.tempBol = tempBol;
}

the new columns(fields) are - temp, tempInt, tempTyniInt, tempBol.

FYI - after the up-gradation we have changed the DB collation for all the tables from latin1_swedish_ci to utf8mb4_unicode_ci & charset from latin1 to utf8mb4

Answer 1

found the solution from the source - https://github.com/spring-projects/spring-framework/issues/22015

someone commented -

The default value of property nullCatalogMeansCurrent has been changed in mysql-driver 5.x and 8.x. In 5.x, the default value is true, and in 8.x false, so in 5.x DatabaseMetaData.getTables will return tables exactly from 'example1', and in 8.x DatabaseMetaData.getTables will return tables not only from 'example1' but from all databases, that's why the SQL will be changed to 'INSERT INTO persons (lastname) VALUES (?)'. Workaround, add nullCatalogMeansCurrent=true to conn url when using mysql-driver 8.x, all tests pass successfully.