CODEWITHSUNDEEP
javamysqljspservlets
ZZZZZZZZZ
ZZZZZZZZZ

Reputation: 199

Checking duplicate insertions in MySQL

MySQL output has duplicate inputs

I have a problem with MySQL where in duplicate inputs are being inserted to a table that I created, I implemented a Database in a WebApp using JSP and Servlets.

for example when I input the following in a form: Employee Name: Kylo Ren Code: A Sales: 3000

it yields into 3 different rows by only submitting the form once. 

public void computeGross(Connection connection){

    switch(employeeCode){
    case "A":
        setGross(grossA+(getEmployeeSales()*grossSalesA));
        break;
    case "B":
        setGross(grossB+(getEmployeeSales()*grossSalesB));  
        break;
    case "C":
        setGross(grossC+(getEmployeeSales()*grossSalesC));  
        break;
    }   
    insertRecord(connection);
}

//compute for the commission, if sales>2500; commission=7.5% of sales
public void computeCommission(Connection connection){       

    if(employeeSales>2500)
        setCommission(getEmployeeSales()*commissionVal);
    else {
        setCommission(0);
    }

    insertRecord(connection);

}

//compute takehome pay, takehome pay = summation of gross and commission

public void computeTakeHomePay(Connection connection){


    setResult(getGross()+getCommission());

    insertRecord(connection);

}

private void insertRecord(Connection conn){

    try{
        String sql="insert into MachineProblem1(EmployeeName, EmployeeCode, EmployeeSales, EmployeeGross, EmployeeCommission, EmployeeResult)"+ "values(?,?,?,?,?,?)";

        PreparedStatement pstmnt= conn.prepareStatement(sql);
        pstmnt.setString(1, employeeName);
        pstmnt.setString(2, employeeCode);
        pstmnt.setDouble(3, employeeSales);
        pstmnt.setDouble(4, gross);
        pstmnt.setDouble(5, commission);
        pstmnt.setDouble(6, result);

        //now commit to database
        pstmnt.executeUpdate();

    }catch(SQLException sqle){
        sqle.printStackTrace();
    }

}

// get records

public ResultSet getAllRecords(Connection conn){
    ResultSet records = null;
    try{
        String sql="select*from MachineProblem1";

        PreparedStatement pstmnt= conn.prepareStatement(sql);

        records= pstmnt.executeQuery();


    }catch(SQLException sqle){
        sqle.printStackTrace();
    }
    return records;
}

Upvotes: 1

Views: 300

Answers (2)

yash
yash

Reputation: 2291

In insertRecord method, sql query is for insert, so everytime method called,it insert new record.
=========================
Problem is in insertRecord method, you are calling insertRecord method from all the other three methods like computeGross, computeTakeHomePay, computeCommission and inside insertRecord method, problem is in this line:

String sql="insert into MachineProblem1(EmployeeName, EmployeeCode, EmployeeSales, EmployeeGross, EmployeeCommission, EmployeeResult)"+ "values(?,?,?,?,?,?)";

it insert three record in database, because it call this method body three time.

if you want to resolve that, i'll suggest you that create one new method updateRecord in which you'll update the previously inserted record. and change in your code, whichever method you first call, it calls insertRecord method, while other methods call updateRecord method. so, the entry is not inserted three times, it update the previous entry only.

Upvotes: 1

Samuel Kok
Samuel Kok

Reputation: 585

Judging from the result, You should remove the insertRecord(connection) method call from the computation methods and invokeinsertRecord(connection)only after the computations are completed.

Alternatively, you can use an Insert ... On Duplicate Update SQL instead by checking if the key already exist.

MySQL: INSERT ... On Duplicate Update

Upvotes: 1

Related Questions