更新查询的动态列名

Question

         Connection conn = null;
        PreparedStatement pstmt = null;

        try{
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection("jdbc:mysql://localhost/netbeans","root","");



        Statement st = conn.createStatement();

         ResultSet rs = st.executeQuery("SELECT * FROM colo");

    rs = st.executeQuery("SELECT COUNT(*) FROM colo");
    // get the number of rows from the result set
    rs.next();
    int rowCount = rs.getInt(1); 

     //txt_ans.setText(String.valueOf(rowCount));
     int num_1 =300;
     int num_2 =200;
     int num_3 =300;
     int num_4 =400;
     String value = null;
     int value1 ;

     for(int i=1;i<=rowCount;i++)
     {

        String sql =("SELECT * FROM colo WHERE id = '"+i+"'");

        pstmt = conn.prepareStatement(sql);
      rs = pstmt.executeQuery(sql);
       while(rs.next())
          value = rs.getString("co1");
       //txt_ans.setText(String.valueOf(value));
       String x = "co2";
       if(value.equals("lo1"))
       {
          // value1= 1;
          // txt_ans.setText(String.valueOf(value1));


             String sql1 =("update colo set '"+x+"' = '"+num_1+"' where id = '"+i+"'");
        pstmt = conn.prepareStatement(sql1);
      int r = pstmt.executeUpdate(sql1);


       txt_ans.setText(String.valueOf(r));    

       }
       else if(value.equals("lo2"))
       {
          // value1= 1;
          // txt_ans.setText(String.valueOf(value1));
      String sql1 =("update colo set '"+ x +"' = '"+num_2+"' where id = '"+i+"'");
        pstmt = conn.prepareStatement(sql1);
      int r = pstmt.executeUpdate(sql1);


       txt_ans.setText(String.valueOf(r));    

       }
       else
       {
           value1 = 9009;
           txt_ans.setText(String.valueOf(value1));
       }


     }

Answer 1

问题在于对列名使用单引号，例如'x'，所以只需删除它们，如下所示：

String sql1 =("update colo set " + x + " = ? where id = ?");//no single quote for x
pstmt = conn.prepareStatement(sql1);
pstmt.setString(1, num_1);
pstmt.setString(2, i);
int r = pstmt.executeUpdate(sql1);

此外，始终使用PrepareStatement的SetString等。方法，用于设置推荐的值。

对else if(value.equals（“lo2”））块中的另一个查询也应用相同的概念。