java - 插入 "Duplicate entry ... for key PRIMARY"和 "Krakow"时为 "Kraków"

Question

我有一个实体 LocationsCoordinates 用于在具有唯一位置和国家(制作复合主键)的数据库中保存位置坐标，其定义如下:

@Entity
@Table(name = "LOCATIONS_COORDINATES")
@IdClass(LocationsCoordinatesPK.class)
public class LocationsCoordinates  implements Serializable {

    private static final long serialVersionUID = -4580217081464519853L;

    @Id
    @Column(name = "LOCATION")
    String location;

    @Id
    @Column(name = "COUNTRY")
    String country;

    @Column(name = "LATITUDE")
    Double latitude;

    @Column(name = "LONGITUDE")
    Double longitude;

    public String getLocation() {
        return location;
    }

    public void setLocation(String location) {
        this.location = location;
    }

    public String getCountry() {
        return country;
    }

    public void setCountry(String country) {
        this.country = country;
    }

    public Double getLatitude() {
        return latitude;
    }

    public void setLatitude(Double latitude) {
        this.latitude = latitude;
    }

    public Double getLongitude() {
        return longitude;
    }

    public void setLongitude(Double longitude) {
        this.longitude = longitude;
    }

}

--

public class LocationsCoordinatesPK implements Serializable 
{   
    private static final long serialVersionUID = -4675306358956719620L;

    String location;

    String country;

    public String getLocation() {
        return location;
    }

    public void setLocation(String location) {
        this.location = location;
    }

    public String getCountry() {
        return country;
    }

    public void setCountry(String country) {
        this.country = country;
    }
}

例如，在 jpa 中批量插入 utf-8 字符集中带有坐标的位置

public class SampleDAO {

    protected EntityManager entityManager;

    @PersistenceContext
    public void setEntityManager(EntityManager entityManager) {
        this. entityManager = entityManager;
    }

public void saveCoordinates(){
ArrayList<LocationsCoordinates> coordinates = new ArrayList<LocationsCoordinates>();

LocationsCoordinates coordinate = new LocationsCoordinates();
coordinate.setLocation("Krakow");
coordinate.setCountry("Poland");
coordinate.setLatitude(25.25817);
coordinate.setLongitude(55.30472);
coordinates.add(coordinate);

coordinate = new LocationsCoordinates();
coordinate.setLocation("Kraków");
coordinate.setCountry("Poland");
coordinate.setLatitude(25.25817);
coordinate.setLongitude(55.30472);
coordinates.add(coordinate);
save(coordinates);
}

@Transactional
public <LocationsCoordinates> List<LocationsCoordinates > save(List<LocationsCoordinates > list) {
    List<LocationsCoordinates > returnList = new ArrayList<LocationsCoordinates >();
        for(LocationsCoordinates  ob : list){
            returnList.add(entityManager.merge(ob));
        }
        return returnList;
    }
}
}

在调用保存函数时，出现以下异常

2013-10-09 19:42:41,892 [ERROR ][pool-1-thread-1] JDBCExceptionReporter - Duplicate entry 'Poland-Kraków' for key 'PRIMARY'
2013-10-09 19:42:41,984 [ERROR ][pool-1-thread-1] TaskUtils$LoggingErrorHandler - Unexpected error occurred in scheduled task.
org.springframework.dao.DataIntegrityViolationException: could not insert: [com.reppify.core.dto.LocationsCoordinates]; SQL [insert into LOCATIONS_COORDINATES (LATITUDE, LONGITUDE, COUNTRY, LOCATION) values (?, ?, ?, ?)]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not insert: [com.reppify.core.dto.LocationsCoordinates]
    at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:643)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:104)
    at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:516)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:754)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:723)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:392)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:120)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
    at $Proxy28.save(Unknown Source)
    at com.reppify.accenture.scoring.ScoreConnections.scoreConnections(ScoreConnections.java:308)
    at com.reppify.parser.job.accenture.JobLoader.synchronizeJob(JobLoader.java:155)
    at com.reppify.parser.job.accenture.JobLoader.startJobLoader(JobLoader.java:51)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:64)
    at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:53)
    at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:81)
    at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
    at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source)
    at java.util.concurrent.FutureTask.run(Unknown Source)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(Unknown Source)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)
Caused by: org.hibernate.exception.ConstraintViolationException: could not insert: [com.reppify.core.dto.LocationsCoordinates]
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:96)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2454)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2874)
    at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:79)
    at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:273)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:265)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:184)
    at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
    at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:51)
    at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1216)
    at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:383)
    at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:133)
    at org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:76)
    at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:512)
    ... 25 more
Caused by: java.sql.BatchUpdateException: Duplicate entry 'Poland-Kraków' for key 'PRIMARY'
    at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2024)
    at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1449)
    at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
    at org.hibernate.jdbc.BatchingBatcher.addToBatch(BatchingBatcher.java:56)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2434)
    ... 37 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'Poland-Kraków' for key 'PRIMARY'
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:407)
    at com.mysql.jdbc.Util.getInstance(Util.java:382)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1039)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3603)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3535)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1989)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2150)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2626)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2415)
    at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1976)
    ... 41 more 

因为 "Krakow" 和 "Kraków" 是不同的字符串值，所以它们不被认为是重复的，但正如我们在异常中看到的那样，它们被认为是相同的值.请帮忙。

Answer 1

尝试使用 utf8_general_ciutf8_bin 作为 MySQL 列的排序规则。

这好像不是Java的问题，而是MySQL的问题。

查找有关排序规则的更多信息 here

这是正确的CREATE TABLE

CREATE TABLE `locations_coordinates` (
  `COUNTRY` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `LOCATION` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `LATITUDE` double DEFAULT NULL,
  `LONGITUDE` double DEFAULT NULL,
  PRIMARY KEY (`COUNTRY`,`LOCATION`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;