java - 如何使用该列中数值的平均值更新特定列的空单元格？ (Mysql使用java)

Question

我想取列中所有数值的平均值，并用计算出的平均值更新该列中的空单元格，但是在更新空单元格期间遇到问题...请给我建议...以下是我的代码。 ..

/*---------------- Function to apply Mean Imputation Algorithm on saved data in Database -----------------*/
public void CalcMean(String tableName) {

        Statement stmt = null;
        String sql = null;
        ResultSet rs = null;

        try {

            setConnection();
            //connection.setAutoCommit(false);
            stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);

            sql = "SELECT * FROM " + tableName;
            System.out.println("sql :" + sql);
            rs = stmt.executeQuery(sql);
            CachedRowSet rowset = new CachedRowSetImpl();
            rowset.populate(rs);

            while(rowset.next()){

                ResultSetMetaData rsmd = rowset.getMetaData();
                int numOfCols = rsmd.getColumnCount();//get total number of columns from database

                    for(int i = 1; i <= numOfCols; i++){
                        if (rowset.getString(i)== null || rowset.getString(i).equals("*") || rowset.getString(i).equals("?"))
                        {
                            System.out.println("was NULL"+i);// Database contains NULL
                            String nullCellValue = rowset.getString(i);// get the designated cell's value
                            System.out.println(nullCellValue);

                        }
                        else
                        {
                            System.out.println("not NULL"+i);// Database does not contain NULL
                            String cellValue = rowset.getString(i);// get the designated cell's value

                            System.out.println(cellValue);

                            /*----- check if the value in cell is Numerical or Categorical [0-9]+-----*/
                            String regex = "[0-9.]+";

                                if(cellValue.matches(regex)){
                                    System.out.println("Value is numerical");

                                    String colName = rsmd.getColumnName(i);
                                    System.out.println(colName);

                                    CalcNumericValMean(colName , tableName);

                            }
                            else{
                                System.out.println("Value is categorical");
                            }
                        }
                    }
            }

            //Clean-up environment
            rs.close();
            stmt.close();
            //connection.commit();
            closeConnection();

        }/*catch (SQLException ex) {
            try {
                connection.rollback();
                System.out.println("RollBack performed");
                closeConnection();
                } catch (Exception e1) {
                System.out.println("RollBack failed");
                }
            } */
        catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

}
// Function to Calculate Mean of Numeric Values 
public void CalcNumericValMean(String colName , String tableName){

    Statement st = null;
    String sql = null;
    String average = null;

    try{

        setConnection();
        //connection.setAutoCommit(false);
        st = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_UPDATABLE);

            sql = "SELECT AVG( " + colName + " ) FROM " + tableName;
            System.out.println("sql :" + sql);

            ResultSet rset = st.executeQuery(sql);
            CachedRowSet rowset = new CachedRowSetImpl();
            rowset.populate(rset);

            int i = 1;
            while(rowset.next()) {  // check if a result was returned
                  average = rowset.getString(i); // get your result
                  System.out.println("average is : " + average);
                  i++;
                }

        //Clean-up environment
        rset.close();
        st.close(); 
        //connection.commit();
        closeConnection();

    }
    catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

}

Answer 1

也许你应该尝试使用表模型来实现？ 。表模型覆盖包含 public void setValueAt(Object value, int row, int col) ，您可以对其进行 ovveride 并在表的特定列上进行数学计算