在 SQL 表中插入行的问题，一些列插入了 NULL 值。

huangapple
go评论112阅读模式
英文:

Inserting rows in sql table problem, some columns inserted NULL value

问题

以下是您要翻译的内容：

我有2个具有多个表的数据库。所有的表都具有相同的语法。这里我有一个以表名作为参数的方法。我尝试插入的表有3列（整数，varchar，整数）。问题是，只有第一行被插入，第2行和第3行为空，我不知道问题是什么。有任何建议吗？

  1. public void getAndInsertData(String nameOfTable) {
  3.     try {
  5.         Class.forName("com.mysql.jdbc.Driver");
  7.         Connection con1 = DriverManager.getConnection(urlDB1, user1, password1);
  8.         Statement s1 = con1.createStatement();
  10.         Connection con2 = DriverManager.getConnection(urlDB2, user2, password2);
  11.         Statement s2 = con2.createStatement();
  13.         ResultSet rs1 = s1.executeQuery("SELECT * FROM " + nameOfTable);
  15.         ResultSetMetaData rsmd1 = rs1.getMetaData();
  16.         int columnCount = rsmd1.getColumnCount();
  18.         for (int column = 1; column <= columnCount; column++) {
  20.             String columnName = rsmd1.getColumnName(column);
  21.             int columnType = rsmd1.getColumnType(column);
  23.             while (rs1.next()) {
  25.                 switch (columnType) {
  27.                     case Types.INTEGER:
  28.                     case Types.SMALLINT:
  29.                     case Types.BIGINT:
  30.                     case Types.TINYINT:
  32.                         int integerValue = rs1.getInt(column);
  34.                         String integerQuery = "insert into " + nameOfTable + " (" + columnName + ") VALUES("
  35.                                 + integerValue + ");";
  36.                         s2.executeUpdate(integerQuery);
  38.                         break;
  40.                     case Types.VARCHAR:
  41.                     case Types.NVARCHAR:
  42.                     case Types.LONGNVARCHAR:
  43.                     case Types.LONGVARCHAR:
  45.                         String varcharValue = rs1.getString(column);
  47.                         String varcharQuery = "insert into " + nameOfTable + " (" + columnName + ") VALUES("
  48.                                 + varcharValue + ");";
  49.                         s2.executeUpdate(varcharQuery);
  51.                     default:
  52.                         System.out.println("Default");
  53.                         break;
  55.                 }
  57.             }
  58.         }
  60.     } catch (Exception e) {
  61.         e.printStackTrace();
  62.     }
  63. }
英文:

I have 2 DB with multiple tables. All tables have the same syntax. Here I have a method that takes name of the table as an argument. The table that I try to insert is with 3 columns (int, varchar, int). The problem is, only the first row is inserted, and the 2 and 3 row is NULL, I don't know what is the problem. Any suggestions, please?

  1. public void getAndInsertData(String nameOfTable) {
  2. try {
  3. Class.forName(&quot;com.mysql.jdbc.Driver&quot;);
  4. Connection con1 = DriverManager.getConnection(urlDB1, user1, password1);
  5. Statement s1 = con1.createStatement();
  6. Connection con2 = DriverManager.getConnection(urlDB2, user2, password2);
  7. Statement s2 = con2.createStatement();
  8. ResultSet rs1 = s1.executeQuery(&quot;SELECT * FROM &quot; + nameOfTable);
  9. ResultSetMetaData rsmd1 = rs1.getMetaData();
  10. int columnCount = rsmd1.getColumnCount();
  11. for (int column = 1; column &lt;= columnCount; column++) {
  12. String columnName = rsmd1.getColumnName(column);
  13. int columnType = rsmd1.getColumnType(column);
  14. while (rs1.next()) {
  15. switch (columnType) {
  16. case Types.INTEGER:
  17. case Types.SMALLINT:
  18. case Types.BIGINT:
  19. case Types.TINYINT:
  20. int integerValue = rs1.getInt(column);
  21. String integerQuery = &quot;insert into &quot; + nameOfTable + &quot; (&quot; + columnName + &quot;) VALUES(&quot;
  22. + integerValue + &quot;);&quot;;
  23. s2.executeUpdate(integerQuery);
  24. break;
  25. case Types.VARCHAR:
  26. case Types.NVARCHAR:
  27. case Types.LONGNVARCHAR:
  28. case Types.LONGVARCHAR:
  29. String varcharValue = rs1.getString(column);
  30. String varcharQuery = &quot;insert into &quot; + nameOfTable + &quot; (&quot; + columnName + &quot;) VALUES(&quot;
  31. + varcharValue + &quot;);&quot;;
  32. s2.executeUpdate(varcharQuery);
  33. default:
  34. System.out.println(&quot;Default&quot;);
  35. break;
  36. }
  37. }
  38. }
  39. } catch (Exception e) {
  40. e.printStackTrace();
  41. }
  42. }

答案1

得分: 3

你的integerQuery和varcharQuery都会向数据库表中插入一条记录，其中一个列填充了值，而其他列则为空。因为你只为一个列提供了值。

英文:

Your integerQuery and varcharQuery both insert into datebase table a record with one filled column and blank other columns. Because you provide value to one column only.

答案2

得分: 1

正如The Impaler已经提到的，您的循环放置位置不正确。
对于rs1的每条记录，您希望使用s2插入一条记录。

您可以首先使用元数据构建一个预处理语句，然后注入这些值：

  1. ResultSetMetaData rsmd1 = rs1.getMetaData();
  2. int columnCount = rsmd1.getColumnCount();
  4. StringBuffer sql=new StringBuffer("insert into "+nameOfTable+" (");
  5. for (int column = 1; column <= columnCount; column++) {
  7.     String columnName = rsmd1.getColumnName(column);
  8.     if(column>1)
  9.         sql.append(",");
  10.     sql.append(columnName);
  11. }
  12. sql.append(") values (");
  13. for(int i=1;i<=columnCount;i++)
  14. {
  15.     sql.append((i==1?"":",")+"?");
  16. }
  17. sql.append(")");
  18. System.out.println("Prepared SQL:"+sql.toString());
  19. // sql = insert into nameOfTable (col1,col2,col3) values (?,?,?)
  20. PreparedStatement s2= con2.prepareStatement(sql.toString());
  22. while (rs1.next()) {
  23.     s2.clearParameters();
  24.     for (int column = 1; column <= columnCount; column++) {
  26.         int columnType = rsmd1.getColumnType(column);
  28.         switch (columnType) {
  30.             case Types.INTEGER:
  31.             case Types.SMALLINT:
  32.             case Types.BIGINT:
  33.             case Types.TINYINT:
  34.                 s2.setInt(column, rs1.getInt(column));
  35.                 break;
  37.             case Types.VARCHAR:
  38.             case Types.NVARCHAR:
  39.             case Types.LONGNVARCHAR:
  40.             case Types.LONGVARCHAR:
  41.                 s2.setString(column, rs1.getString(column));
  42.                 break;
  43.             default:
  44.                 System.err.println("Not supported type for column "+column+" with type:"+columnType);
  45.                 s2.setNull(column, columnType);
  46.                 break;
  47.         }
  48.     } // end of for loop
  49.     // 每条rs1记录执行一次语句
  50.     s2.executeUpdate();
  51. } // end of while
英文:

As The Impaler already mentioned, your loops are at the wrong place.
For every record of rs1, you want to insert one record using s2.

You can build a prepared statement first using the metadata and then inject the values:
ResultSetMetaData rsmd1 = rs1.getMetaData();
int columnCount = rsmd1.getColumnCount();

  1. StringBuffer sql=new StringBuffer(&quot;insert into &quot;+nameOfTable+&quot; (&quot;);
  2. for (int column = 1; column &lt;= columnCount; column++) {
  3. String columnName = rsmd1.getColumnName(column);
  4. if(column&gt;1)
  5. sql.append(&quot;,&quot;);
  6. sql.append(columnName);
  7. }
  8. sql.append(&quot;) values (&quot;);
  9. for(int i=1;i&lt;=columnCount;i++)
  10. {
  11. sql.append((i==1?&quot;&quot;:&quot;,&quot;)+ &quot;?&quot;);
  12. }
  13. sql.append(&quot;)&quot;);
  14. System.out.println(&quot;Prepared SQL:&quot;+sql.toString());
  15. // sql = insert into nameOfTable (col1,col2,col3) values (?,?,?)
  16. PreparedStatement s2= con2.prepareStatement(sql.toString());
  17. while (rs1.next()) {
  18. s2.clearParameters();
  19. for (int column = 1; column &lt;= columnCount; column++) {
  20. int columnType = rsmd1.getColumnType(column);
  21. switch (columnType) {
  22. case Types.INTEGER:
  23. case Types.SMALLINT:
  24. case Types.BIGINT:
  25. case Types.TINYINT:
  26. s2.setInt(column, rs1.getInt(column));
  27. break;
  28. case Types.VARCHAR:
  29. case Types.NVARCHAR:
  30. case Types.LONGNVARCHAR:
  31. case Types.LONGVARCHAR:
  32. s2.setString(column, rs1.getString(column));
  33. break;
  34. default:
  35. System.err.println(&quot;Not supported type for column &quot;+column+&quot; with type:&quot;+columnType);
  36. s2.setNull(column, columnType);
  37. break;
  38. }
  39. } // end of for loop
  40. // execute statement once per record in rs1
  41. s2.executeUpdate();
  42. } // end of while

答案3

得分: 1

以下是翻译好的内容：

几个问题：

  • 使用 try-with-resources 来确保 JDBC 资源被正确清理。

  • 不需要 switch 语句，因为实际上我们不需要知道列的类型。如果使用 getObject() 和 setObject()，JDBC 驱动程序会处理这个问题。

  • 从源表只执行一次 INSERT 操作来处理每一行。

  • 当插入大量记录时，可以使用批处理来提高性能。

下面是如何实现的：

  1. try (
  2. Connection conSource = DriverManager.getConnection(urlDB1, user1, password1);
  3. Connection conTarget = DriverManager.getConnection(urlDB2, user2, password2);
  4. Statement stmtSource = conSource.createStatement();
  5. ResultSet rsSource = stmtSource.executeQuery("SELECT * FROM " + nameOfTable);
  6. ) {
  7. // 构建插入语句
  8. ResultSetMetaData metaData = rsSource.getMetaData();
  9. int columnCount = metaData.getColumnCount();
  10. StringBuilder sql = new StringBuilder("INSERT INTO " + nameOfTable + " (");
  11. for (int column = 1; column <= columnCount; column++) {
  12. if (column != 1)
  13. sql.append(", ");
  14. sql.append(metaData.getColumnName(column));
  15. }
  16. sql.append(") VALUES (");
  17. for (int column = 1; column <= columnCount; column++) {
  18. if (column != 1)
  19. sql.append(", ");
  20. sql.append("?");
  21. }
  22. sql.append(")");
  23. // 复制数据
  24. conTarget.setAutoCommit(false);
  25. try (PreparedStatement stmtTarget = conTarget.prepareStatement(sql.toString())) {
  26. int batchSize = 0;
  27. while (rsSource.next()) {
  28. for (int column = 1; column <= columnCount; column++) {
  29. // 在此处复制行。如果源表和目标表列的类型不兼容，使用 switch 语句控制映射。
  30. // 下面的语句应该适用于大多数类型，所以 switch 语句只需要处理异常情况。
  31. stmtTarget.setObject(column, rsSource.getObject(column), metaData.getColumnType(column));
  32. }
  33. stmtTarget.addBatch();
  34. if (++batchSize == 1000) { // 每插入 1000 行刷新一次以防止内存溢出
  35. stmtTarget.executeBatch();
  36. batchSize = 0;
  37. }
  38. }
  39. if (batchSize != 0)
  40. stmtTarget.executeBatch();
  41. }
  42. conTarget.commit();
  43. }
英文:

A few issues:

  • Use try-with-resources to make sure the JDBC resources are cleaned up correctly.

  • No need for a switch statement, because we don't actually need to know the types of the columns. The JDBC driver will handle that if you use getObject() and setObject().

  • Only execute one INSERT per row from the source table.

  • Use batching when inserting a lot of records, for better performance.

Here is how to do it:

  1. try (
  2. Connection conSource = DriverManager.getConnection(urlDB1, user1, password1);
  3. Connection conTarget = DriverManager.getConnection(urlDB2, user2, password2);
  4. Statement stmtSource = conSource.createStatement();
  5. ResultSet rsSource = stmtSource.executeQuery(&quot;SELECT * FROM &quot; + nameOfTable);
  6. ) {
  7. // Build insert statement
  8. ResultSetMetaData metaData = rsSource.getMetaData();
  9. int columnCount = metaData.getColumnCount();
  10. StringBuilder sql = new StringBuilder(&quot;INSERT INTO &quot; + nameOfTable + &quot; (&quot;);
  11. for (int column = 1; column &lt;= columnCount; column++) {
  12. if (column != 1)
  13. sql.append(&quot;, &quot;);
  14. sql.append(metaData.getColumnName(column));
  15. }
  16. sql.append(&quot;) VALUES (&quot;);
  17. for (int column = 1; column &lt;= columnCount; column++) {
  18. if (column != 1)
  19. sql.append(&quot;, &quot;);
  20. sql.append(&quot;?&quot;);
  21. }
  22. sql.append(&quot;)&quot;);
  23. // Copy data
  24. conTarget.setAutoCommit(false);
  25. try (PreparedStatement stmtTarget = conTarget.prepareStatement(sql.toString())) {
  26. int batchSize = 0;
  27. while (rsSource.next()) {
  28. for (int column = 1; column &lt;= columnCount; column++) {
  29. // Copy row here. Use switch statement to control the mapping
  30. // if source and target table columns don&#39;t have compatible types.
  31. // The following statement should work for most types, so switch
  32. // statement only needs to cover the exceptions.
  33. stmtTarget.setObject(column, rsSource.getObject(column), metaData.getColumnType(column));
  34. }
  35. stmtTarget.addBatch();
  36. if (++batchSize == 1000) { // Flush every 1000 rows to prevent memory overflow
  37. stmtTarget.executeBatch();
  38. batchSize = 0;
  39. }
  40. }
  41. if (batchSize != 0)
  42. stmtTarget.executeBatch();
  43. }
  44. conTarget.commit();
  45. }

huangapple
  • 本文由 发表于 2020年9月4日 21:33:58
  • 转载请务必保留本文链接：https://go.coder-hub.com/63742250.html
  • java
  • mysql
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定