java insert data into Mysql database

Question

I translate the function to the flowing application The application use PreparedStatement pstmt to insert String array of two dimention,when i make it start ,problems appeared like the photo

import java.sql.PreparedStatement;

public static void main(String[] args){

java.sql.Connection conn = java.sql.DriverManager.getConnection(
        url, user, password);

the String array to write into databace.

String[][] sheetContent={{"100007"," 钢笔", "200", "xxx"},{"100020", "鞋子","700", "AAA"}};
int rows=2;
int columns = 4;
String sql = "INSERT INTO product(编号,商品名,单价,提供商) VALUES(?,?,?,?)";
PreparedStatement pstmt = conn.prepareStatement(sql); 
        for(int r=0;r<rows;r++){
            for(int c=0;c<columns;c++){
                pstmt.setString(c, sheetContent[r][c]);
            }
        }
pstmt.executeUpdate();

enter image description here

Indexes to setString() is 1-based, not 0-based. Read the javadoc. — Andreas
– Andreas, Commented May 6, 2016 at 1:25

Dinesh Thalke · Accepted Answer · 2016-05-06 06:28:09Z

1

you should start Index of preparedStatement from 1 , what you are doing is starting from 0.

index = must starts with index integer 1.

pstmt.setString(index, sheetContent[r][c]);

example: pstmt.setString(1, sheetContent[r][c]); pstmt.setString(2, sheetContent[r][c]);

answered May 6, 2016 at 6:28

Dinesh Thalke

1761 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Biegeleisen · Accepted Answer · 2016-05-06 01:30:18Z

1

You need to be clever with how you use your column indices:

PreparedStatement pstmt = conn.prepareStatement(sql); 
for (int r=0; r < rows; r++) {
    for (int c=0; c < columns; c++) {
        int index = 1 + (r * columns) + c;
        pstmt.setString(index, sheetContent[r][c]);
    }
}
pstmt.executeUpdate();

answered May 6, 2016 at 1:30

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

5 Comments

Tasos P. Over a year ago

Why would (r * columns) be required? It would increase the parameter index based on row index; this doesn't seem correct.

Tasos P. Over a year ago

Despite my comment in regards to correctness, I believe that the OP should suggest an edit to Tim's answer which pointed to the correct direction instead of posting his own answer. I would upvote Tim's corrected answer.

Tim Biegeleisen Over a year ago

@Cascader Thanks for defending me buddy. The idea behind my answer is to translate two dimensions (row, column) into a single dimension index which the prepared statement requires. This is all there is to it.

Tasos P. Over a year ago

Your idea is clear and certainly in the right direction. However, the statement has a finite number of parameters (i.e. 4) but should the code run against 50 rows, the index would grow to a maximum of 1 + (49 *4) + 3 (since r would reach 49 and c would reach 3, according to for limits). This would result in error while executing / parsing the statement.

Tim Biegeleisen Over a year ago

He should ensure that the columns exist first. This does not make my answer invalid.

Collectives™ on Stack Overflow

java insert data into Mysql database

2 Answers 2

Comments

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Related