我一直在遵循本指南进行个人项目，但我遇到了这个奇怪的问题:http://www.tutorialspoint.com/spring/spring_jdbc_example.htm

当使用 spring jdbc 库插入值时，id 的值从 1 跳到 11 再到 21...等等。虽然，当使用简单的插入手动查询数据库时，id 正在按预期工作，即:1,2 ,3,等等...

这是信息:

Spring jdbc 4.2.3 Spring 上下文 4.3.3 mysql-连接器-java 5.1.37

数据库:MySQL 社区服务器 (GPL) 5.5.45-log 托管在 Azure 上，并添加了 ClearDB。

创建表:

CREATE TABLE runners(
   id   INT NOT NULL AUTO_INCREMENT,
   firstName VARCHAR(20) NOT NULL,
   lastName VARCHAR(20) NOT NULL,
   location VARCHAR(20) NOT NULL,
   PRIMARY KEY (ID)
);

当我查询时:SHOW VARIABLES LIKE 'auto_inc%';，我得到: auto_increment_increment 1 auto_increment_offset 1

代码如下:

Runner.java

public class Runner{
    private Integer id;
    private String firstName;
    private String lastName;
    private String location;

    public Integer getId(){
        return this.id;
    }

    public String getFirstName(){
        return this.firstName;
    }

    public String getLastName(){
        return this.lastName;
    }

    public String getLocation(){
        return this.location;
    }

    public void setId(int id){
        this.id = id;
    }

    public void setFirstName(String firstName){
        this.firstName = firstName;
    }

    public void setLastName(String lastName){
        this.lastName = lastName;
    }

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

RunnerDAO.java

public interface RunnerDAO {

       public void setDataSource(DataSource ds);

       public void create(String firstName, String lastName, String location);

       public Runner getRunner(Integer id);

       public List<Runner> listRunners();

       public void delete(Integer id);

       public void update(Integer id, String firstName, String lastName, String location);
}

RunnerJDBCTEmplate.java

public class RunnerJDBCTemplate implements RunnerDAO {
   private DataSource dataSource;
   private JdbcTemplate jdbcTemplateObject;

   public void setDataSource(DataSource dataSource) {
      this.dataSource = dataSource;
      this.jdbcTemplateObject = new JdbcTemplate(dataSource);
   }

   public void create(String firstName, String lastName, String location) {
      String SQL = "insert into runners (firstName, lastName,location) values (?, ?, ?)";

      jdbcTemplateObject.update( SQL, firstName, lastName, location);
      System.out.println("Created Record FirstName = " + firstName + " LastName = " + lastName+ " Location = " + location);
      return;
   }

   public Runner getRunner(Integer id) {
      String SQL = "select * from runners where id = ?";
      Runner runner = jdbcTemplateObject.queryForObject(SQL, new Object[]{id}, new RunnerMapper());
      return runner;
   }

   public List<Runner> listRunners() {
      String SQL = "select * from runners";
      List <Runner> runners = jdbcTemplateObject.query(SQL,new RunnerMapper());
      return runners;
   }

   public void delete(Integer id){
      String SQL = "delete from runners where id = ?";
      jdbcTemplateObject.update(SQL, id);
      System.out.println("Deleted Record with ID = " + id );
      return;
   }

   public void update(Integer id, String firstName, String lastName, String location){
      String SQL = "update runners set firstName = ?,lastName = ?, location = ? where id = ?";
      jdbcTemplateObject.update(SQL, firstName, lastName, location , id);
      System.out.println("Updated Record with ID = " + id );
      return;
   }
}

RunnerMapper.java

public class RunnerMapper implements RowMapper<Runner> {
   public Runner mapRow(ResultSet rs, int rowNum) throws SQLException {
      Runner runner = new Runner();
      runner.setId(rs.getInt("id"));
      runner.setFirstName(rs.getString("firstName"));
      runner.setLastName(rs.getString("lastName"));
      runner.setLocation(rs.getString("location"));
      return runner;
   }
}

当我运行这个 main.java 时:

public static void main(String[] args) throws Exception {
        ApplicationContext context = 
                 new ClassPathXmlApplicationContext("Beans.xml");

          RunnerJDBCTemplate runnerJDBCTemplate = 
          (RunnerJDBCTemplate)context.getBean("RunnerJDBCTemplate");

          System.out.println("------Records Creation--------" );
          runnerJDBCTemplate.create("John", "Doe", "1234 street");
          runnerJDBCTemplate.create("Jane", "Dude", "5678 boulevard");
          runnerJDBCTemplate.create("Johnny", "Duh", "111 avenue");
    }
}

我在我的数据库中得到以下输出

1   John    Doe 1234 street
11  Jane    Dude    5678 boulevard
21  Johnny  Duh 111 avenue

我试过 SET @@auto_increment_increment=1;

同样，手动查询时一切正常。

有人知道吗？谢谢!

编辑

我尝试了其他 mysql 托管，现在它运行良好。似乎 Azure ClearDb Addon 是问题的根源。尽管如此，如果有人有解释，我们将不胜感激。

最佳答案

我相信 Azure 正在使用与 Heroku 相同的 ClearDB。所以这里的这个答案就是最好的解释Heroku MySQL Auto Increment

简而言之，这是一种防止碰撞的ClearDB策略http://www.cleardb.com/developers/help/faq#general_16

关于java - 使用 spring jdbc 时，MySQL auto_increment 10 而不是 1，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/33947711/