在带有Connector / J的MySQL中将getGeneratedKeys与批处理插入一起使用

25-02-28 13

在本文中，我们将详细介绍在带有Connector/J的MySQL中将getGeneratedKeys与批处理插入一起使用的各个方面，同时，我们也将为您带来关于.MySQLNonTransientCon

在本文中，我们将详细介绍在带有Connector / J的MySQL中将getGeneratedKeys与批处理插入一起使用的各个方面，同时，我们也将为您带来关于.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 5 times. Giving up.、com.google.common.collect.testing.DerivedCollectionGenerators.MapEntrySetGenerator的实例源码、getGeneratedKeys自动获取主键的方法、Java Statement.getGeneratedKeys()方法：获取自动生成的键的有用知识。

本文目录一览：

在带有Connector / J的MySQL中将getGeneratedKeys与批处理插入一起使用
.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 5 times. Giving up.
com.google.common.collect.testing.DerivedCollectionGenerators.MapEntrySetGenerator的实例源码
getGeneratedKeys自动获取主键的方法
Java Statement.getGeneratedKeys()方法：获取自动生成的键

在带有Connector / J的MySQL中将getGeneratedKeys与批处理插入一起使用

使用Connector /
J，我想在主表中进行批量插入，然后在明细表中进行批量插入（PreparedStatement.executeBatch()两者均适用）。我在网上找不到太多信息，因此我正在寻找有经验的人的任何反馈。

我可以 Statement.getGeneratedKeys()用来获取主表中新插入的行的ID，以便可以将它们用作详细信息插入中的外键吗？
如果不是每个查询都导致插入（例如有一个insert ignore或insert ... on duplicate key update查询）怎么办？我是否会Statement.getGeneratedKeys()为每条语句排一行，还是只为新条语句排？
Statement.getGeneratedKeys()插入的主记录之一存在错误并且在连接字符串中continueBatchOnError设置为时，将返回什么 true？
Connector / J版本5.0.x与5.5.x之间的相关行为是否存在差异？MySQL 5.0和5.1呢？
还有其他我应该注意的问题或陷阱吗？
有一个更好的方法吗？

答案1

小编典典

好吧，我进行了一些测试。使用Connector / J 5.1和MySQL 5.1.42，我观察到以下几点：

Statement.getGeneratedKeys() 可以预期插入
如果插入或更新了一行（返回的更新计数数组executeBatch()返回‘1’或‘2’），Statement.getGeneratedKeys()则将具有该行的键。如果该行未修改（insert ignore或者insert ... on duplicate key update导致无操作，则executeBatch()返回3），则没有键。
返回的ResultSet getGeneratedKeys将具有（2）中成功插入的行的条目。失败的插入将不会生成密钥行（更新计数值为Statement.EXECUTE_FAILED）
？
小心使用rewriteBatchedStatementsJDBC连接字符串。如果将其设置为true，则任何失败都将导致重写的“块”中的每一行都被视为失败。解决此问题的一种方法是遍历失败的行，然后不进行批处理而重试它们。
？

.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 5 times. Giving up.

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 5 times. Giving up.
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:408)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	at com.mysql.jdbc.Util.getInstance(Util.java:386)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
	at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2385)
	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2306)
	at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:834)
	at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
	at sun.reflect.GeneratedConstructorAccessor4.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:408)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:416)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:346)
	at com.mysql.jdbc.jdbc2.optional.MysqlDataSource.getConnection(MysqlDataSource.java:443)
	at com.mysql.jdbc.jdbc2.optional.MysqlDataSource.getConnection(MysqlDataSource.java:141)
	at com.mysql.jdbc.jdbc2.optional.MysqlDataSource.getConnection(MysqlDataSource.java:111)
	at cn.qjj.mysql.MySQLPool.getNewConnection(MySQLPool.java:72)
	at cn.qjj.mysql.MySQLPool.init(MySQLPool.java:63)
	at cn.qjj.mysql.MySQLPool.<init>(MySQLPool.java:32)
	at cn.qjj.mysql.MySQLPool.getInstance(MySQLPool.java:39)
	at cn.qjj.mysql.SqlUtils.<clinit>(SqlUtils.java:33)
	at cn.qjj.solr.MsgUtil.main(MsgUtil.java:31)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection,  message from server: "Too many connections"
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:408)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	at com.mysql.jdbc.Util.getInstance(Util.java:386)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
	at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1112)
	at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2483)
	at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2324)
	... 18 more
java.lang.NullPointerException
	at java.util.concurrent.ConcurrentHashMap.putVal(ConcurrentHashMap.java:1011)
	at java.util.concurrent.ConcurrentHashMap.put(ConcurrentHashMap.java:1006)
	at cn.qjj.mysql.MySQLPool.init(MySQLPool.java:63)
	at cn.qjj.mysql.MySQLPool.<init>(MySQLPool.java:32)
	at cn.qjj.mysql.MySQLPool.getInstance(MySQLPool.java:39)
	at cn.qjj.mysql.SqlUtils.<clinit>(SqlUtils.java:33)
	at cn.qjj.solr.MsgUtil.main(MsgUtil.java:31)

com.google.common.collect.testing.DerivedCollectionGenerators.MapEntrySetGenerator的实例源码

项目：guava-mock 文件：MapTestSuiteBuilder.java

@Override
protected List<TestSuite> createDerivedSuites(
    FeatureSpecificTestSuiteBuilder<
            ?,? extends OnesizeTestContainerGenerator<Map<K,V>,Map.Entry<K,V>>>
        parentBuilder) {
  // Todo: Once invariant support is added,supply invariants to each of the
  // derived suites,to check that mutations to the derived collections are
  // reflected in the underlying map.

  List<TestSuite> derivedSuites = super.createDerivedSuites(parentBuilder);

  if (parentBuilder.getFeatures().contains(CollectionFeature.SERIALIZABLE)) {
    derivedSuites.add(
        MapTestSuiteBuilder.using(
                new ReserializedMapGenerator<K,V>(parentBuilder.getSubjectGenerator()))
            .withFeatures(computeReserializedMapFeatures(parentBuilder.getFeatures()))
            .named(parentBuilder.getName() + " reserialized")
            .suppressing(parentBuilder.getSuppressedTests())
            .createTestSuite());
  }

  derivedSuites.add(
      createDerivedEntrySetSuite(
              new MapEntrySetGenerator<K,V>(parentBuilder.getSubjectGenerator()))
          .withFeatures(computeEntrySetFeatures(parentBuilder.getFeatures()))
          .named(parentBuilder.getName() + " entrySet")
          .suppressing(parentBuilder.getSuppressedTests())
          .createTestSuite());

  derivedSuites.add(
      createDerivedKeySetSuite(keySetGenerator(parentBuilder.getSubjectGenerator()))
          .withFeatures(computeKeySetFeatures(parentBuilder.getFeatures()))
          .named(parentBuilder.getName() + " keys")
          .suppressing(parentBuilder.getSuppressedTests())
          .createTestSuite());

  derivedSuites.add(
      createDerivedValueCollectionSuite(
              new MapValueCollectionGenerator<K,V>(parentBuilder.getSubjectGenerator()))
          .named(parentBuilder.getName() + " values")
          .withFeatures(computeValuesCollectionFeatures(parentBuilder.getFeatures()))
          .suppressing(parentBuilder.getSuppressedTests())
          .createTestSuite());

  return derivedSuites;
}

项目：googles-monorepo-demo 文件：MapTestSuiteBuilder.java

@Override
protected List<TestSuite> createDerivedSuites(
    FeatureSpecificTestSuiteBuilder<
            ?,V>(parentBuilder.getSubjectGenerator()))
          .named(parentBuilder.getName() + " values")
          .withFeatures(computeValuesCollectionFeatures(parentBuilder.getFeatures()))
          .suppressing(parentBuilder.getSuppressedTests())
          .createTestSuite());

  return derivedSuites;
}

项目：guava-libraries 文件：MapTestSuiteBuilder.java

@Override
protected List<TestSuite> createDerivedSuites(
    FeatureSpecificTestSuiteBuilder<
        ?,V>>>
    parentBuilder) {
  // Todo: Once invariant support is added,to check that mutations to the derived collections are
  // reflected in the underlying map.

  List<TestSuite> derivedSuites = super.createDerivedSuites(parentBuilder);

  if (parentBuilder.getFeatures().contains(CollectionFeature.SERIALIZABLE)) {
    derivedSuites.add(MapTestSuiteBuilder.using(
            new ReserializedMapGenerator<K,V>(parentBuilder.getSubjectGenerator()))
        .withFeatures(computeReserializedMapFeatures(parentBuilder.getFeatures()))
        .named(parentBuilder.getName() + " reserialized")
        .suppressing(parentBuilder.getSuppressedTests())
        .createTestSuite());
  }

  derivedSuites.add(createDerivedEntrySetSuite(
          new MapEntrySetGenerator<K,V>(parentBuilder.getSubjectGenerator()))
      .withFeatures(computeEntrySetFeatures(parentBuilder.getFeatures()))
      .named(parentBuilder.getName() + " entrySet")
      .suppressing(parentBuilder.getSuppressedTests())
      .createTestSuite());

  derivedSuites.add(createDerivedKeySetSuite(
          keySetGenerator(parentBuilder.getSubjectGenerator()))
      .withFeatures(computeKeySetFeatures(parentBuilder.getFeatures()))
      .named(parentBuilder.getName() + " keys")
      .suppressing(parentBuilder.getSuppressedTests())
      .createTestSuite());

  derivedSuites.add(createDerivedValueCollectionSuite(
          new MapValueCollectionGenerator<K,V>(
              parentBuilder.getSubjectGenerator()))
      .named(parentBuilder.getName() + " values")
      .withFeatures(computeValuesCollectionFeatures(
          parentBuilder.getFeatures()))
      .suppressing(parentBuilder.getSuppressedTests())
      .createTestSuite());

  return derivedSuites;
}

项目：guava 文件：MapTestSuiteBuilder.java

@Override
protected List<TestSuite> createDerivedSuites(
    FeatureSpecificTestSuiteBuilder<
            ?,Entry<K,V>(parentBuilder.getSubjectGenerator()))
          .named(parentBuilder.getName() + " values")
          .withFeatures(computeValuesCollectionFeatures(parentBuilder.getFeatures()))
          .suppressing(parentBuilder.getSuppressedTests())
          .createTestSuite());

  return derivedSuites;
}

getGeneratedKeys自动获取主键的方法

public class Demo
{

public static void main(String[] args) {

try {
String sql="insert into person values(?,?)";
Class.forName("com.microsoft.sqlserver.jdbc.sqlServerDriver");
Connection conn=DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=pubs","sa","");

//需要增加一个Statement.RETURN_GENERATED_KEYS这样的参数
java.sql.PreparedStatement p=conn.PreparedStatement(sql,RETURN_GENERATED_KEYS);
p.setobject(1,"zengguofeef");

//p.setobject(2,new java.sql.Date(System.currentTimeMillis()));

//必须这样设置,System.currentTimeMillis()表示当前系统时间的毫秒数,Timestamp()方法会根据这个毫秒数设置成当前时间
p.setTimestamp(2,new java.sql.Timestamp(System.currentTimeMillis()));
p.executeUpdate();

//通过这个方法获取getGeneratedKeys();
ResultSet rs=p.getGeneratedKeys();
rs.next();
int num=rs.getInt(1);
System.out.println("自动增长列为:"+num);
} catch (ClassNotFoundException e) {
e.printstacktrace();
} catch (sqlException e) {
e.printstacktrace();
}
}

public static void main(String[] args)
{

//在sql语句后面加上一个select scope_identity()
String sql="insert into person values(?,?) select scope_identity()";
try {
Class.forName("com.microsoft.jdbc.sqlserver.sqlServerDriver");
Connection conn=DriverManager.getConnection("jdbc:microsoft:sql);p.setobject(1,"zengguofeef");p.setTimestamp(2,new java.sql.Timestamp(System.currentTimeMillis()));//这里使用的是executeQuery()方法,而不是executeUpdate()ResultSet rs=p.executeQuery();rs.next();int num=rs.getInt(1);System.out.println("自动增长列为:"+num);conn.close();} catch (ClassNotFoundException e) {e.printstacktrace();} catch (sqlException e) {e.printstacktrace();}}}

Java Statement.getGeneratedKeys()方法：获取自动生成的键

Java Statement.getGeneratedKeys() 方法获取由于执行此 Statement 对象而创建的所有自动生成的键。如果此 Statement 对象没有生成任何键，则返回空的 ResultSet 对象。

语法：

getGeneratedKeys()

注意：如果未指定表示自动生成键的列，则 JDBC 驱动程序实现将确定最能表示自动生成键的列。

示例

本示例通过 Statement 的 getGeneratedKeys 方法获取执行此 Statement 对象而创建的所有自动生成的键。

Connection conn = ……  //省略部分代码
Statement stmt = conn.createStatement();  //获取Statement
ResultSet rs = stmt.getGeneratedKeys();

今天关于在带有Connector / J的MySQL中将getGeneratedKeys与批处理插入一起使用的分享就到这里，希望大家有所收获，若想了解更多关于.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 5 times. Giving up.、com.google.common.collect.testing.DerivedCollectionGenerators.MapEntrySetGenerator的实例源码、getGeneratedKeys自动获取主键的方法、Java Statement.getGeneratedKeys()方法：获取自动生成的键等相关知识，可以在本站进行查询。

本文标签：