Let's break down why you're seeing this confusing error even though your placeholders and value count seem to match, and fix the issues in your code:

Key Issues in Your Code #

1. Hardcoded Parameter Index in setNull
   Look at this line in your integer type handling:

   ps.setNull((1 +1), Types.INTEGER);
   

   You're hardcoding the parameter index to 2 instead of using i + 1! This means whenever you hit an integer NULL value, you're overwriting the 2nd parameter instead of the current column's parameter. For rows where the 8th column is NULL, this parameter never gets set properly—hence the error.

2. Missing Parameter Clearing Between Rows
   You're reusing the same PreparedStatement for all rows, but you never clear the parameters from the previous row. Even if you fix the index issue, leftover parameters from prior rows can cause unexpected behavior or missing parameter errors. Always call ps.clearParameters() before setting parameters for a new row.

3. Potential Mismatch Between columnNames and columnTypes
   You generate placeholders based on columnNames.size() but use columnTypes to determine data types. If these two lists don't have the same length, you'll end up setting fewer parameters than placeholders, which would also trigger this error. Add a check to ensure they match.

Fixed Code Example #

Here's your code with the above issues resolved:

import java.sql.Types;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Collections;
import java.util.List;

public void insertData(String tableName, List<String> rows) { 
    List<String> columnNames = null; 
    try { 
        // Get column names and types for the specified table
        columnNames = getColumns(tableName, "dbo"); 
        // Add check for columnTypes and columnNames length match
        if (columnTypes == null || columnNames.size() != columnTypes.size()) {
            System.err.println("Column names and types count mismatch!");
            return;
        }
    } catch(SQLException se) { 
        System.out.println(se.getMessage()); 
        return; // Exit if we can't get column info
    } 

    // Build insert columns and placeholders with cleaner code
    String insertColumns = String.join(", ", columnNames);
    String insertValues = String.join(", ", Collections.nCopies(columnNames.size(), "?"));
    String insertSql = "INSERT INTO dbo." + tableName + " (" + insertColumns + ") values(" + insertValues + ")"; 
    System.out.println(insertSql); 

    // Use try-with-resources to auto-close PreparedStatement
    try (PreparedStatement ps = conn.prepareStatement(insertSql)) { 
        int index = 0; 
        for(String row : rows) { 
            index++; 
            System.out.println(index + ": " + row); 
            String[] items = row.split(","); 

            // Clear parameters from previous row before setting new ones
            ps.clearParameters();

            // Skip rows that don't match column count
            if (items.length != columnNames.size()) {
                System.err.println("Row " + index + " has mismatched column count! Skipping.");
                continue;
            }

            for(int i = 0; i < items.length; i++) { 
                String value = items[i];
                String type = columnTypes.get(i);
                int paramIndex = i + 1;

                if(StringUtils.isInteger(type)){ 
                    if("NULL".equalsIgnoreCase(value)){ 
                        ps.setNull(paramIndex, Types.INTEGER); 
                    } else { 
                        ps.setInt(paramIndex, Integer.parseInt(value)); 
                    } 
                } else if(StringUtils.isVarchar(type)) { 
                    if("NULL".equalsIgnoreCase(value)){ 
                        ps.setNull(paramIndex, Types.VARCHAR); 
                    } else { 
                        ps.setString(paramIndex, value); 
                    } 
                } else if(StringUtils.isDatetime(type)){ 
                    // Fix millisecond pattern from .sss to .SSS
                    SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS"); 
                    java.util.Date date = null; 
                    try { 
                        date = formatter.parse(value); 
                    } catch (ParseException e) { 
                        e.printStackTrace(); 
                    } 
                    if(date == null){ 
                        ps.setNull(paramIndex, Types.TIMESTAMP); 
                    } else { 
                        ps.setTimestamp(paramIndex, new java.sql.Timestamp(date.getTime())); 
                    } 
                }
            } 
            ps.executeUpdate(); // Insert the current row
        } 
    } catch(SQLException se) { 
        System.out.println(se.getMessage()); 
    } 
}

Additional Notes #

• Replaced manual string concatenation with String.join() for cleaner, more maintainable code.
• Used try-with-resources for PreparedStatement to ensure it's properly closed without a messy finally block.
• Fixed the date format pattern (.sss targets seconds, .SSS is correct for milliseconds).
• Added checks for mismatched column counts to catch bad rows early and avoid further errors.
• For production use, avoid split(",") for CSV data—use a dedicated parser like Apache Commons CSV to handle values with commas or quotes correctly.

内容的提问来源于stack exchange，提问作者gazinbendigo