I can not update a field in my MySql database from Java

Question

I can not update a field in my MySql database from Java

Navigation

#1 by (1 votes)
#2 by (1 votes)
#3 by (0 votes)

1

Hi, I'm having a problem when updating a field in my database, it does not throw me any errors, it just does not update the field in the database.

I have the Product Class and a constructor that is as follows:

public Producto(String strNombreProd, double fltPrecio, double fltPrecioMayor, int intCantidad, String strUbicacion) {
    this.strNombreProd = strNombreProd;
    this.fltPrecio = fltPrecio;
    this.fltPrecioMayor = fltPrecioMayor;
    this.intCantidad = intCantidad;
    this.strUbicacion = strUbicacion;
}

And in my interface I have an event that when I click on an icon I can change the name of that product:

private void btnActualizaNombreMouseClicked(java.awt.event.MouseEvent evt) {                                                
    try {
        String nombre = JOptionPane.showInputDialog(null, "Ingrese el nuevo nombre de:\n" + txtNombre.getText());
        txtNombre.setText(nombre);
        producto = new Producto(txtNombre.getText(), Double.parseDouble(txtPrecio.getText()), Double.parseDouble(txtPrecioM.getText()), Integer.parseInt(txtCantidad.getText()), txtUbicacion.getText());
        material.UpdateProducto(producto);
        updateTabla();
    } catch (NullPointerException | NumberFormatException e) {
        e.printStackTrace();
    }
}

And I forgot this is the method where I connect to the database:

public void UpdateProducto(Producto producto){
    try {
        con = DriverManager.getConnection("jdbc:mysql://localhost:3306/empresa", "root", "pass");
        String Sentencia = "UPDATE producto SET Nombre_Producto = ?,Precio = ?, Precio_Mayor = ?, Cantidad = ?, Ubicacion = ? WHERE Nombre_Producto = ?";
        ps = con.prepareStatement(Sentencia);
        ps.setString(1, producto.getStrNombreProd());
        ps.setDouble(2, producto.getFltPrecio());
        ps.setDouble(3, producto.getFltPrecioMayor());
        ps.setInt(4, producto.getIntCantidad());
        ps.setString(5, producto.getStrUbicacion());
        ps.setString(6, producto.getStrNombreProd());
        ps.executeUpdate();
    } catch (SQLException ex) {
        ex.printStackTrace();
    } finally {
        try {
            ps.close();
            con.close();
        } catch (SQLException ex) {
            Logger.getLogger(DATMaterial.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

I only have the error when updating the name_product field but when I update other fields, it updates them without problems

mysql java

asked by Roberth Torres 29.12.2017 в 16:41

source

3 answers

1

You must use your priary_key in the WHERE clause according to what you indicate in your CREATE is 'Code', not the name of the product. It is not recommended to use fields other than the primary_key to reference queries, if they can modify those fields.

Change your sentence to this:

 String Sentencia = "UPDATE producto SET Nombre_Producto = ?,Precio = ?, Precio_Mayor = ?, Cantidad = ?, Ubicacion = ? WHERE Codigo  = ?";

Then change the string you set in that position:

public void UpdateProducto(Producto producto){
    try { 
         //... tu codigo
        ps.setString(6, producto.getCodigoProd());
        ps.executeUpdate();
         //.. resto del codigo

answered by 29.12.2017 в 17:19

0

Maybe because you are using Product_Name as if it were your primary key. Better to add a product_id (unique value) ...

"UPDATE product SET Product_name =?, Price =?, Price_Mayor =?, Quantity =?, Location =? WHERE Product_id =?";

try again

answered by 29.12.2017 в 17:08

Extract random elements from an array in php Error "INCORRECT SYNTAX NEAR '@nombretbl'" trying to query with dynamic table name

score 1 · Accepted Answer

As they say the previous answer you should not use a field other than the primary key to perform an update to a specific record, on the other hand when you modify the name does not update because the new name is not in the table yet: Think in this:

You want to change the name from product 1 to product 2 so the query that you do has in the clause where ... WHERE Nombre_Producto = 'producto 2' for obvious reasons you will not find anything that update.

I recommend two things:

Add to the method that saves the following modification:

int cantRegActualizados = ps.executeUpdate(); if(cantRegActualizados == 0) { JOptionPane.showMessageDialog(null, "alert",String.format( "No se ha actualizado el producto. El producto %s no existe",producto.getStrNombreProd()), JOptionPane.ERROR_MESSAGE); }

If you put a little logic to the message you will notice the error immediately.

That you use the debug to see the data that you are sending to the query and thus be able to find this type of errors, if you do not know how to debug here I leave a good link How to debug in netbeans