java如何在循环中相应地将数据存储在数据库列中

Question

我在vector表中有一个项目列表，但项目大小可能不同，如何根据项目编号将这些项目相应地存储在数据库列中。示例：我有一个带有[apple,red,24]的列表项。我想使用循环将该项相应地存储在数据库列value1= apple、value2=red、value3=24中？下面是当前的示例代码和数据库列

数据库设计实例

值1 |值2 |值3 |值4 |值5

jsp文件

    for(int i =0; i <2; i++)
        {

            Vector vTables = (Vector)session.getAttribute("SES_VALUE"+i); 
            //first vector list [[apple,red,5],[orange,orange,3]]
            //second vector list [[apple,red,5,big],[orange,orange,4,small]]

                for(int k = 0; k < vTables.size(); k++) 
                { 

                    Vector vTemp2           = (Vector) vTables.elementAt(i); 
                   //first loop vTemp2 list [[apple,red,5],[orange,orange,3]] 

                        DB_BS.makeConnection();
                        iRowAffected = DB_BS.insert_table_value(vTemp2);
                        DB_BS.takeDown();   
                }

        }

java文件--我被这部分卡住了

public int insert_table_value(Vector vtTable)throws Exception 
{
    //vtTable item will be  [[apple,red,5],[orange,orange,3]] 

    for(int k = 1; k <= vtTable.size(); k++) 
    { 
        //in this sValue will get each items from the vtTable
        String sValue           = (String) vtTable.elementAt(k); 

       //how to write an sql query at here??  
       //what i plan to do is, if sValue have 3 item, it will store the value in database per columns accodingly
        String myQuery = "INSERT INTO TB_BS_VALUE ( ) VALUES ( )"; 

        pstmt2 = new PreparedStatementLogable(myConn, myQuery);
        pstmt2.setString(k, sValue);

        myQuery = pstmt2.toString();
        pstmt = myConn.prepareStatement(myQuery);
        RowsAffected = pstmt.executeUpdate();

        if (RowsAffected > 0) 
            insertSQLLog("SQL", myQuery, "", "", "", "");
    }
    return RowsAffected;
}

Answer 1

String sValue是一个字符串，所以第一件事应该是将其拆分（根据您的评论，按“，”分隔）

String[] result = sValue.split(",");

那你就可以

String columns = "";
String values = "";

for (int i = 0; i <= result.size(); i++) {
   columns += "VALUE" + (i+1) +",";
   values+="?,";
}

// get rid of trailing ,
columns = b.replace(columns.lastIndexOf(","), columns.lastIndexOf(",") + 1,""); 
values = b.replace(values.lastIndexOf(","), values.lastIndexOf(",") + 1,"");

String myQuery = "INSERT INTO TB_BS_VALUE ("+columns+") VALUES ("+values+")"; 

pstmt = new PreparedStatementLogable(myConn, myQuery);

for (int i = 0; i <= result.size(); i++) {
    pstmt.setString(i, result[i]);
}

两个for循环可能会被折叠成一个，不确定这个想法，并且无法尝试

注意

RowsAffected = pstmt.executeUpdate(); 

不应该编译，应该

int rowsAffected = pstmt.executeUpdate();