How to insert auto increment values to mysql from java

Question

     try
    {
        String ptype = null;
        if(b1.isSelected())
        {
            ptype="cash";
        }
        else if(b2.isSelected())
        {
            ptype="cred";
        }    


        String rID=ridt.getText();
        String cID=cidt.getText();
        double Rcharge=Double.parseDouble(rct.getText());
        double Mcharge=Double.parseDouble(mct.getText());
        double total=Double.parseDouble(tott.getText());
        double sum=Double.parseDouble(casht.getText());
        double change=Double.parseDouble(balt.getText());


        String sql0 ="INSERT INTO transactions(transaction_id,reservation_id,cus_id,room_charges,meal_charges,total_amount,cash,change,payment_type,emp_id)values('"+?+"','"+rID+"','"+cID+"','"+Rcharge+"','"+Mcharge+"','"+total+"','"+sum+"','"+change+"','"+ ptype+"','e010')";
        pst=conn.prepareStatement(sql0);
        pst.execute();

    }
    catch(NumberFormatException | SQLException e)
    {
        System.out.println(e);
    }

In the above sql statement transaction_id is an auto increment field. How do I insert that value to the table? If I left that value in the code will it be added to the table at the database?

**Auto** means it increments automatically in some specific scenario, Then why are you willing to violate it's scenario by inserting your custom id ? — Shree Krishna, Apr 03 '16 at 04:34
Take a look at [getGeneratedKeys()](http://docs.oracle.com/javase/8/docs/api/java/sql/Statement.html#getGeneratedKeys--). — Sandeep Chatterjee, Apr 03 '16 at 04:49

score 0 · Answer 1 · answered Apr 03 '16 at 04:40

0

auto increment means that field automatically gets incremented as records are inserted to the table. If you want to custom input ids to the field transaction_id change its field type to int,tinyintor whatever depending on the type of passing values.

answered Apr 03 '16 at 04:40

Kasun Siyambalapitiya

3,956
8
38
58

score 0 · Answer 2 · edited May 23 '17 at 11:44

If the field is mapped as auto_increment then it should not be passed in the query , let it happen at the database level.
Also if you are using the preparedStatment use it properly.
In preparedStatement don't pass the dynamic value directly in the query, if you want to do that use create statement.
PreparedStatments is better in terms of security, check this for reference Difference between Statement and PreparedStatement
Pls check the modified code, I removed the autogenrated field and also made the query as it should be for preparedSatements

        String rID=ridt.getText();
        String cID=cidt.getText();
        double Rcharge=Double.parseDouble(rct.getText());
        double Mcharge=Double.parseDouble(mct.getText());
        double total=Double.parseDouble(tott.getText());
        double sum=Double.parseDouble(casht.getText());
        double change=Double.parseDouble(balt.getText());


        String sql0 ="INSERT INTO transactions(reservation_id,cus_id,room_charges,meal_charges,total_amount,cash,change,payment_type,emp_id)values(?,?,?,?,?,?,?,?,?)";

        preparedStatement.setString(1, "Test");
        preparedStatement.setInt(1, 44);
        // repeat the above two based on your requirement and datbase field


        pst=conn.prepareStatement(sql0);
        pst.execute();

How to insert auto increment values to mysql from java

2 Answers2