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

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

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

问题

以下是您要翻译的内容:

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

public void getAndInsertData(String nameOfTable) {

    try {

        Class.forName("com.mysql.jdbc.Driver");

        Connection con1 = DriverManager.getConnection(urlDB1, user1, password1);
        Statement s1 = con1.createStatement();

        Connection con2 = DriverManager.getConnection(urlDB2, user2, password2);
        Statement s2 = con2.createStatement();

        ResultSet rs1 = s1.executeQuery("SELECT * FROM " + nameOfTable);

        ResultSetMetaData rsmd1 = rs1.getMetaData();
        int columnCount = rsmd1.getColumnCount();

        for (int column = 1; column <= columnCount; column++) {

            String columnName = rsmd1.getColumnName(column);
            int columnType = rsmd1.getColumnType(column);

            while (rs1.next()) {

                switch (columnType) {

                    case Types.INTEGER:
                    case Types.SMALLINT:
                    case Types.BIGINT:
                    case Types.TINYINT:

                        int integerValue = rs1.getInt(column);

                        String integerQuery = "insert into " + nameOfTable + " (" + columnName + ") VALUES("
                                + integerValue + ");";
                        s2.executeUpdate(integerQuery);

                        break;

                    case Types.VARCHAR:
                    case Types.NVARCHAR:
                    case Types.LONGNVARCHAR:
                    case Types.LONGVARCHAR:

                        String varcharValue = rs1.getString(column);

                        String varcharQuery = "insert into " + nameOfTable + " (" + columnName + ") VALUES("
                                + varcharValue + ");";
                        s2.executeUpdate(varcharQuery);

                    default:
                        System.out.println("Default");
                        break;

                }

            }
        }

    } catch (Exception e) {
        e.printStackTrace();
    }
}
英文:

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?

public void getAndInsertData(String nameOfTable) {
try {
Class.forName(&quot;com.mysql.jdbc.Driver&quot;);
Connection con1 = DriverManager.getConnection(urlDB1, user1, password1);
Statement s1 = con1.createStatement();
Connection con2 = DriverManager.getConnection(urlDB2, user2, password2);
Statement s2 = con2.createStatement();
ResultSet rs1 = s1.executeQuery(&quot;SELECT * FROM &quot; + nameOfTable);
ResultSetMetaData rsmd1 = rs1.getMetaData();
int columnCount = rsmd1.getColumnCount();
for (int column = 1; column &lt;= columnCount; column++) {
String columnName = rsmd1.getColumnName(column);
int columnType = rsmd1.getColumnType(column);
while (rs1.next()) {
switch (columnType) {
case Types.INTEGER:
case Types.SMALLINT:
case Types.BIGINT:
case Types.TINYINT:
int integerValue = rs1.getInt(column);
String integerQuery = &quot;insert into &quot; + nameOfTable + &quot; (&quot; + columnName + &quot;) VALUES(&quot;
+ integerValue + &quot;);&quot;;
s2.executeUpdate(integerQuery);
break;
case Types.VARCHAR:
case Types.NVARCHAR:
case Types.LONGNVARCHAR:
case Types.LONGVARCHAR:
String varcharValue = rs1.getString(column);
String varcharQuery = &quot;insert into &quot; + nameOfTable + &quot; (&quot; + columnName + &quot;) VALUES(&quot;
+ varcharValue + &quot;);&quot;;
s2.executeUpdate(varcharQuery);
default:
System.out.println(&quot;Default&quot;);
break;
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
}

答案1

得分: 3

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

英文:

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插入一条记录。

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

ResultSetMetaData rsmd1 = rs1.getMetaData();
int columnCount = rsmd1.getColumnCount();

StringBuffer sql=new StringBuffer("insert into "+nameOfTable+" (");
for (int column = 1; column <= columnCount; column++) {

    String columnName = rsmd1.getColumnName(column);
    if(column>1)
        sql.append(",");
    sql.append(columnName);
}
sql.append(") values (");
for(int i=1;i<=columnCount;i++)
{
    sql.append((i==1?"":",")+"?");
}
sql.append(")");
System.out.println("Prepared SQL:"+sql.toString());
// sql = insert into nameOfTable (col1,col2,col3) values (?,?,?)
PreparedStatement s2= con2.prepareStatement(sql.toString());

while (rs1.next()) {
    s2.clearParameters();
    for (int column = 1; column <= columnCount; column++) {

        int columnType = rsmd1.getColumnType(column);

        switch (columnType) {

            case Types.INTEGER:
            case Types.SMALLINT:
            case Types.BIGINT:
            case Types.TINYINT:
                s2.setInt(column, rs1.getInt(column));
                break;

            case Types.VARCHAR:
            case Types.NVARCHAR:
            case Types.LONGNVARCHAR:
            case Types.LONGVARCHAR:
                s2.setString(column, rs1.getString(column));
                break;
            default:
                System.err.println("Not supported type for column "+column+" with type:"+columnType);
                s2.setNull(column, columnType);
                break;
        }
    } // end of for loop
    // 每条rs1记录执行一次语句
    s2.executeUpdate();
} // 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();

StringBuffer sql=new StringBuffer(&quot;insert into &quot;+nameOfTable+&quot; (&quot;);
for (int column = 1; column &lt;= columnCount; column++) {
String columnName = rsmd1.getColumnName(column);
if(column&gt;1)
sql.append(&quot;,&quot;);
sql.append(columnName);
}
sql.append(&quot;) values (&quot;);
for(int i=1;i&lt;=columnCount;i++)
{
sql.append((i==1?&quot;&quot;:&quot;,&quot;)+ &quot;?&quot;);
}
sql.append(&quot;)&quot;);
System.out.println(&quot;Prepared SQL:&quot;+sql.toString());
// sql = insert into nameOfTable (col1,col2,col3) values (?,?,?)
PreparedStatement s2= con2.prepareStatement(sql.toString());
while (rs1.next()) {
s2.clearParameters();
for (int column = 1; column &lt;= columnCount; column++) {
int columnType = rsmd1.getColumnType(column);
switch (columnType) {
case Types.INTEGER:
case Types.SMALLINT:
case Types.BIGINT:
case Types.TINYINT:
s2.setInt(column, rs1.getInt(column));
break;
case Types.VARCHAR:
case Types.NVARCHAR:
case Types.LONGNVARCHAR:
case Types.LONGVARCHAR:
s2.setString(column, rs1.getString(column));
break;
default:
System.err.println(&quot;Not supported type for column &quot;+column+&quot; with type:&quot;+columnType);
s2.setNull(column, columnType);
break;
}
} // end of for loop
// execute statement once per record in rs1
s2.executeUpdate();
} // end of while

答案3

得分: 1

以下是翻译好的内容:

几个问题:

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

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

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

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

下面是如何实现的:

try (
Connection conSource = DriverManager.getConnection(urlDB1, user1, password1);
Connection conTarget = DriverManager.getConnection(urlDB2, user2, password2);
Statement stmtSource = conSource.createStatement();
ResultSet rsSource = stmtSource.executeQuery("SELECT * FROM " + nameOfTable);
) {
// 构建插入语句
ResultSetMetaData metaData = rsSource.getMetaData();
int columnCount = metaData.getColumnCount();
StringBuilder sql = new StringBuilder("INSERT INTO " + nameOfTable + " (");
for (int column = 1; column <= columnCount; column++) {
if (column != 1)
sql.append(", ");
sql.append(metaData.getColumnName(column));
}
sql.append(") VALUES (");
for (int column = 1; column <= columnCount; column++) {
if (column != 1)
sql.append(", ");
sql.append("?");
}
sql.append(")");
// 复制数据
conTarget.setAutoCommit(false);
try (PreparedStatement stmtTarget = conTarget.prepareStatement(sql.toString())) {
int batchSize = 0;
while (rsSource.next()) {
for (int column = 1; column <= columnCount; column++) {
// 在此处复制行。如果源表和目标表列的类型不兼容,使用 switch 语句控制映射。
// 下面的语句应该适用于大多数类型,所以 switch 语句只需要处理异常情况。
stmtTarget.setObject(column, rsSource.getObject(column), metaData.getColumnType(column));
}
stmtTarget.addBatch();
if (++batchSize == 1000) { // 每插入 1000 行刷新一次以防止内存溢出
stmtTarget.executeBatch();
batchSize = 0;
}
}
if (batchSize != 0)
stmtTarget.executeBatch();
}
conTarget.commit();
}
英文:

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:

try (
Connection conSource = DriverManager.getConnection(urlDB1, user1, password1);
Connection conTarget = DriverManager.getConnection(urlDB2, user2, password2);
Statement stmtSource = conSource.createStatement();
ResultSet rsSource = stmtSource.executeQuery(&quot;SELECT * FROM &quot; + nameOfTable);
) {
// Build insert statement
ResultSetMetaData metaData = rsSource.getMetaData();
int columnCount = metaData.getColumnCount();
StringBuilder sql = new StringBuilder(&quot;INSERT INTO &quot; + nameOfTable + &quot; (&quot;);
for (int column = 1; column &lt;= columnCount; column++) {
if (column != 1)
sql.append(&quot;, &quot;);
sql.append(metaData.getColumnName(column));
}
sql.append(&quot;) VALUES (&quot;);
for (int column = 1; column &lt;= columnCount; column++) {
if (column != 1)
sql.append(&quot;, &quot;);
sql.append(&quot;?&quot;);
}
sql.append(&quot;)&quot;);
// Copy data
conTarget.setAutoCommit(false);
try (PreparedStatement stmtTarget = conTarget.prepareStatement(sql.toString())) {
int batchSize = 0;
while (rsSource.next()) {
for (int column = 1; column &lt;= columnCount; column++) {
// Copy row here. Use switch statement to control the mapping
// if source and target table columns don&#39;t have compatible types.
// The following statement should work for most types, so switch
// statement only needs to cover the exceptions.
stmtTarget.setObject(column, rsSource.getObject(column), metaData.getColumnType(column));
}
stmtTarget.addBatch();
if (++batchSize == 1000) { // Flush every 1000 rows to prevent memory overflow
stmtTarget.executeBatch();
batchSize = 0;
}
}
if (batchSize != 0)
stmtTarget.executeBatch();
}
conTarget.commit();
}

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

发表评论

匿名网友

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

确定