Calling a java method in Mysql query-java

Question

i have searched on the internet on how i can get values from a method called in mysql query, whereby it only returns identical values for all rows in a column,what can i do so that i get the respective values in each row when the method is called.I need help please.My method for getting results is as follows:

public final void getResults() {
   // from = (String) empol.getSelectedItem();
    resultsTable=new JTable();
    model=new DefaultTableModel();
    int i;
    int count;
    String a[];
    String header[] =  { "Name","Date","Basic","Commision","Allowances","NSSF","Deductions","PAYE","Gross","Net"};   //Table Header Values, change, as your wish
    count = header.length;
    for(i = 0; i < count; i++) {
        model.addColumn(header[i]);
    }
    resultsTable.setModel(model);                             
    docwin.add(resultsTable.getTableHeader(),BorderLayout.NORTH);

    a = new String[count];
    try {
        dbconn.connect();
        st = dbconn.conn.createStatement();
        SQL = "select money.Name, money.Date, money.earnings,money.commision,(SELECT SUM(Amount) "
                + "  FROM allowances where allowances.Name=money.Name) as Allowance,(select nssf_amount from nhif where "
                +"nhif.Name=money.Name ) as NSSF, (SELECT SUM(Amount) "
                + " FROM deductions where deductions.Name=money.Name ) as deductions,'"+getTax1()+"' as Paye, "
                + " (SELECT (earnings +commision + (SELECT SUM(Amount) "
                + "  FROM allowances where allowances.Name=money.Name))) as GrossPay,"
                + "(SELECT ((SELECT (earnings +commision + (SELECT SUM(Amount) "
                + "  FROM allowances where allowances.Name=money.Name)))) - "
                + "((SELECT SUM(Amount)"
                + " FROM deductions where deductions.Name=money.Name )+(SELECT nhif_amount FROM nhif where nhif.Name=money.Name )+ "
                + "(earnings * 0.16)+(select nssf_amount from nhif where "
                +"nhif.Name=money.Name ) ) ) as NetPay "
                + "from money Group by Name order by money.monid ";
        rs = st.executeQuery(SQL);
         while (rs.next()){
            for(i = 0; i < count; i++){
                a[i] = rs.getString(i+1);
            }
            model.addRow(a);                //Adding the row in table model
            resultsTable.setModel(model);             // set the model in jtable
        }

        pane = new JScrollPane(resultsTable);
        docwin.add(pane);
    } catch (SQLException ex) {
        JOptionPane.showMessageDialog(null, ex);
    }
}

the method that is invoked is this:

public String getTax1() throws SQLException {

    String result="";
    String s = "select (money.earnings + money.commision+(select sum(Amount) from allowances where allowances.Name=money.Name)) as Total from money ";
    Statement sts;
    ResultSet rst;
    sts = dbconn.conn.createStatement();
    rst = sts.executeQuery(s);
    while (rst.next()) {
        result = rst.getString("Total");
    }
    // return result;
    int gross= Integer.parseInt(result);

    final double ftx1 = 10165;
    final double ftx2 = 9576;
    final double ftx3 = 9576;
    final double ftx4 = 9576;
    final double ftx5 = 0;
    // the percentage of fedral tax
    final double taxper1 = 0.10;
    final double taxper2 = 0.15;
    final double taxper3 = 0.20;
    final double taxper4 = 0.25;
    final double taxper5 = 0.30;
    Double relief=1162.0;
    // double theincome = in.nextDouble();
    //1st cut
    double thing1 = Math.min(ftx1, gross);
    double taxd1 = thing1 * taxper1;
    double thinga = Math.max(gross - ftx1, 0);
    //2nd cut
    double thing2 = Math.min(ftx2, thinga);
    double taxd2 = thing2 * taxper2;
    double thingb = Math.max(thinga - ftx2, 0);
    //3rd cut
    double thing3 = Math.min(ftx3, thingb);
    double taxd3 = thing3 * taxper3;
    double thingc = Math.max(thingb - ftx3, 0);
    //4th cut
    double thing4 = Math.min(ftx4, thingc);
    double taxd4 = thing4 * taxper4;
    double thingd = Math.max(thingc - ftx4, 0);
    //5th cut
    double thing5 = Math.max(ftx5, thingd);
    double taxd5 = thing5 * taxper5;
    //total federal tax-tft
    double tft = taxd1 + taxd2 + taxd3 + taxd4 + taxd5;

    double tax=tft-relief;
    netTax1=Double.toString(tax);

    return netTax1;
}

All it does is that it gives one identical PAYE value for all employees,what can i do to make it show respective tax values in the jtable? kindly help

So basically you want to display return value of getTax1 against each employee on the JTable? And looking at the method getTax1 it is going to return same value no matter what employee we are interested in - correct? — ring bearer
– ring bearer, Commented Jun 28, 2015 at 17:20
So what is the error your are getting or is it working? is st.executeQuery throwing SQLException? — ring bearer
– ring bearer, Commented Jun 28, 2015 at 17:50
@ring,i am not getting any error,all it does it picks the value for the first employee and puts it in all other employees.i want it get tax and show for each and every employee in each respective row in the PAYE column — kazisto
– kazisto, Commented Jun 28, 2015 at 18:03
yes i want it to show the return value against each employee @ring — kazisto
– kazisto, Commented Jun 28, 2015 at 18:08

ring bearer · Accepted Answer · 2015-06-28 18:12:05Z

1

Your requirement is to have different getTax1() value returned per employee. That can not be achieved by adding getTax1 call to the SQL. What you have essentially done is added a constant value in the SELECT clause and by that you ensured for each row the query returns there will always be a constant value added to the field list, which is the return value of getTax1() call.

In order to get it working as you wish you need to either: Add the expression needed to compute PAYE per employee (join using some ID that makes sense to your application) or do a computation per employee record using following steps.

In getResults method :

Execute query to fetch all the details except `PAYE` field that you want to compute. 
For each employee in the result    
    Call `getTax1` with employee ID as parameter to get corresponding value
    Add return value to the array that makes the table model. 
Done

You need to change getTax1 logic to accept an Id or other fields that lets you compute require value per employee.

answered Jun 28, 2015 at 18:12

ring bearer

20.9k8 gold badges62 silver badges73 bronze badges

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

5 Comments

kazisto Over a year ago

,pardon a bit,how can i call getax1 with id,please dont mind...if possible,asample

ring bearer Over a year ago

@kazisto : Per your comment, the getTax1 would change per employee. That means the query inside getTax1 should contain some conditions specific to an employee. Currently the query in getTax1 is kind of static - it is going to return same value no matter what. So you need to re-design that query. At that point, it will become clear to you what values you need to pass as dynamic values to the query. They should be part of your method signature. HTH

kazisto Over a year ago

which condition shall i use sir?I have tried ".....where allowances.Name=money.Name" but it shows the same thing....

ring bearer Over a year ago

@kazisto :) I can not advise on this because I do not know anything about your data model.

kazisto Over a year ago

just stranded in my project...help please

Collectives™ on Stack Overflow

Calling a java method in Mysql query-java

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related