Update Embedded Database - Need a second set of eyes on code

[mikewyatt]

I am trying to add /edit records in an embedded database.
I have written some code that I think might work, but I would like a second set of eyes to look at to see if it is coded correctly.....

 private void Save_Item___MouseClicked(java.awt.event.MouseEvent evt) {
    if ("Equipment - Mundane".equals(jComboBox1.getSelectedItem().toString())) {
    try {
        Connection conn = DriverManager.getConnection("jdbc:derby:Databases/Equipment_Mundane");
        Statement stmt = conn.createStatement();
        String sql = "INSERT INTO EQUIPMENT_MUNDANE (ITEM_NAME, COST, COIN, WEIGHT) "
              + "VALUES ("
              + "Item_Name.getText(),  "
              + "Cost.getText(), "
              + "Coin.getSelectedValue, "
              + "Weight.getText())";
        String sql1 = "UPDATE EQUIPMENT_MUNDANE SET ITEM_NAME = Item_Name.getText() "
              + "WHERE ITEM_NAME = Item_Name.getText() AND"
              + "String COST = Cost.getText() AND "
              + "String COIN = Coin.getSelectedValue.toString() AND "
              + "String WEIGHT = Weight.getText()";
    if (New_Item.isSelected())
        stmt.executeUpdate(sql);
    if (Existing_Item.isSelected())
        stmt.executeUpdate(sql1);

} catch (SQLException ex) {
    Logger.getLogger(Edit_Equip_Databases.class.getName()).log(Level.SEVERE, null, ex);

There are no apparent syntax errors.
The varibles Item_Name, Cost, and Weight are jtextfields, Coin is a jcombobox, and New_Item & Existing_Item are radio buttons.

the statement sql is to add a new record to the database
while sql1 is to change/edit values of an existing record.
I may add a delete record option to the mix.

I would prefer that new records be inserted in alphabetically order per Item_Name, but I know that this code will not do that.

So if you guys will take a look at this and make any suggestions, I would appriciate it.... Dont want to screw up my database accidently.

[JamesCherrill]

The best way to test code is to test it ;)
If you haven't got a disposable re-usable re-creatable test database then now's the time to create one. (Write a script to create and populate it, so you can restore/reset it to a known state before each test run)

Apart from that...
You create a new database connection (expensive!) every time. If you are going to run this frequently then its probably better to create just one connection and re-use it. Ditto prepared SQL statements
You insert the user's text into your SQL statements without any validation at all. Suppose the user has a quote character in their text. Suppose they are smart enough to have text that terminates your SQL command and does something malicious? Much better to get the text from the input fields and perform some minimal validation before allowing it near your database.

[mikewyatt]

I got the code that saves new records to the database and I thought I would share it.

    private void Save_Item___MouseClicked(java.awt.event.MouseEvent evt) {
        Connection conn;
        if ("Equipment - Mundane".equals(jComboBox1.getSelectedItem().toString())) {
            String Temp_Item_Name = ("" + Item_Name.getText().replaceAll("'", "`"));
            String Temp_Cost = ("" + Cost.getText().replaceAll("'", "`"));
            String Temp_Coin = "" + Coin.getSelectedItem().toString();
            String Temp_Weight = ("" + Weight.getText().replaceAll("'", "`"));
            try {
                conn = DriverManager.getConnection("jdbc:derby:Databases/Equipment_Mundane");
                Statement statement = conn.createStatement(
                    ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
                ResultSet rs = statement.executeQuery("select * from EQUIPMENT_MUNDANE");
                rs.moveToInsertRow();
                rs.updateString("ITEM_NAME", Temp_Item_Name);
                rs.updateString("COST", Temp_Cost);
                rs.updateString("COIN", Temp_Coin);
                rs.updateString("WEIGHT", Temp_Weight);
                rs.insertRow();
                statement.close();
                rs.close();
                System.out.println("Record Stored.");
            } catch (SQLException ex) {
                Logger.getLogger(Edit_Equip_Databases.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

and thank you James for the hint of invalid characters being entered and causing errors... such as the ' character hence the replaceAll at the start of the routine.

Now If I can figure out how to either sort the database after entering a new record or inserting the record in its proper alphanumeric position at creation time I will be happy....

thanks again.

[JamesCherrill]

I'm no SQL expert, but it's my understanding that SQL tables are sets, with no physical or intrinsic ordering that you can use or rely on. You can only specify a sort order when retrieving records. Maybe someone in our database forum can be more helpful.

[mikewyatt]

That is what I am thinking with my research....

Throughout my program, I select the record that I need through a drop down (Jcombobox). While this will require more coding, I will probably after loading all primary names into the combo box, to do a bubble sort on that and then when the item desired is selected, search and load the record from the database that I need into memory.

BTW.... thanks again James for all your help.