ERROR insert JDBC data MYSQL Java

2

I've been trying to insert all the data I get through the scanner into the database all day yesterday and I get an error, I can not find the solution anywhere.

I get this error message:

  

Exception in thread "main"   com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an   error in your SQL syntax; check the manual that corresponds to your   MariaDB server version for the right syntax to use near '?,?,?,?)' At   line 1 at   sun.reflect.NativeConstructorAccessorImpl.newInstance0 (Native Method)   at   sun.reflect.NativeConstructorAccessorImpl.newInstance (NativeConstructorAccessorImpl.java:62)   at   sun.reflect.DelegatingConstructorAccessorImpl.newInstance (DelegatingConstructorAccessorImpl.java:45)   at java.lang.reflect.Constructor.newInstance (Constructor.java:423) at   com.mysql.jdbc.Util.handleNewInstance (Util.java:411) at   com.mysql.jdbc.Util.getInstance (Util.java:386) at   com.mysql.jdbc.SQLError.createSQLException (SQLError.java:1053) at   com.mysql.jdbc.MysqlIO.checkErrorPacket (MysqlIO.java:4120) at   com.mysql.jdbc.MysqlIO.checkErrorPacket (MysqlIO.java:4052) at   com.mysql.jdbc.MysqlIO.sendCommand (MysqlIO.java:2503) at   com.mysql.jdbc.MysqlIO.sqlQueryDirect (MysqlIO.java:2664) at   com.mysql.jdbc.ConnectionImpl.execSQL (ConnectionImpl.java:2788) at   com.mysql.jdbc.ConnectionImpl.execSQL (ConnectionImpl.java:2738) at   com.mysql.jdbc.StatementImpl.execute (StatementImpl.java:899) at   com.mysql.jdbc.StatementImpl.execute (StatementImpl.java:737) at   inventory.SwitchInt.RegistrarProduct (SwitchInt.java:59) at   inventory.Inventory.main (Inventory.java:31)

I have changed the syntax more than 20 times literally, but I can not. In class we are used to doing postgre and it is different, I searched for the syntax of mysql and they said it was the one I have, but it does not work, I leave the code below.

Conexion Class

public class Conexion {

Connection conexion = null;
Statement comando = null;
ResultSet registro;

public Connection MySQLConnect() {

    try {
        Class.forName("com.mysql.jdbc.Driver");
        String servidor = "jdbc:mysql://localhost:3306/inventario";
        String usuario = "root";
        String pass = "";
        conexion = DriverManager.getConnection(servidor, usuario, pass);

    } catch (ClassNotFoundException ex) {
        JOptionPane.showMessageDialog(null, ex, "Error en la conexión a la base de datos: " + ex.getMessage(), JOptionPane.ERROR_MESSAGE);
        conexion = null;
    } catch (SQLException ex) {
        JOptionPane.showMessageDialog(null, ex, "Error en la conexión a la base de datos: " + ex.getMessage(), JOptionPane.ERROR_MESSAGE);
        conexion = null;
    } catch (Exception ex) {
        JOptionPane.showMessageDialog(null, ex, "Error en la conexión a la base de datos: " + ex.getMessage(), JOptionPane.ERROR_MESSAGE);
        conexion = null;
    } finally {
        JOptionPane.showMessageDialog(null, "Conexion establecida correctamente.");
        return conexion;
    }
}

PreparedStatement prepareStatement(String insert_into_usuarioNombreApellidosEdadCor) {
    throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
}
}

Here I have the error, in this class.

public class SwitchInt {

**Conexion db = new Conexion();
Connection cn = db.MySQLConnect();**


static String nombre;
static double precio;
static int opcion, stock, codigo;
Producto p;
Vector<Producto> productos = new Vector<Producto>();

public void Intro() {
    System.out.print("\nElija un número:\n 1. Registrar producto\n 2. Vender producto\n 3. Buscar producto\n 4. Calcular precio medio\n 5. Salir \n\n ");
}

public void RegistrarProducto() throws SQLException {
    System.out.print("\n Ingrese un código del producto: \n");
    codigo = entrada.nextInt();
    System.out.print("Nombre: ");
    nombre = entrada.next();
    System.out.print("Precio: ");
    precio = entrada.nextDouble();
    System.out.print("Stock: ");
    stock = entrada.nextInt();
    p = busqueda(codigo, productos);

    if (p == null) {
        productos.addElement(new Producto(codigo, nombre, precio, stock));

        **String sql = "INSERT INTO productos (ID, Nombre, Precio, Stock) VALUES(?,?,?,?)";

        PreparedStatement pst;
        pst = cn.prepareStatement(sql);
        pst.setInt(1, codigo);
        pst.setString(2, nombre);
        pst.setDouble(3, precio);
        pst.setInt(4, stock);
        pst.execute(sql);**

        System.out.print("Producto agregado\n");
    } else {
        System.out.print("ya existe este producto");
    }
}

I appreciate a little help, thank you.

    
asked by Manuee 21.03.2018 в 13:07
source

1 answer

5

When using prepared queries, which is correct in this case 1 , the method that executes the statement is executeUpdate , not execute .

  

executeUpdate

     

Executes the SQL statement on this PreparedStatement object, which   must be a declaration of SQL Data Manipulation Language (DML), as    INSERT , UPDATE or DELETE ; or a SQL statement that does not return   nothing, like a DDL instruction.

In addition, the SQL statement was already passed here: pst = cn.prepareStatement(sql); , then it just remains to execute, you do not have to pass again the variable sql in executeUpdate .

If you write your code like that, it should work:

    String sql = "INSERT INTO productos (ID, Nombre, Precio, Stock) VALUES(?,?,?,?)";

    PreparedStatement pst;
    pst = cn.prepareStatement(sql);
    pst.setInt(1, codigo);
    pst.setString(2, nombre);
    pst.setDouble(3, precio);
    pst.setInt(4, stock);
    pst.executeUpdate();

Notes:

1 When external data intervenes in a query, always apply the principle of prepared queries , otherwise our code would be vulnerable to a serious security risk known as SQL injection , through which a malicious user could take control not only from our database, but of the entire operating system .

    
answered by 21.03.2018 / 13:43
source