Java syntax error

Question

Java syntax error

Navigation

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

1

Good morning,

I have the following code but it does not run after creating the table in the database. I imagine it will be a syntax error in INSERT or in UPDATE :

public class Conectate {
private String driver ="com.mysql.jdbc.Driver";
private String cadenaConexion ="jdbc:mysql://localhost/XboxOne";
private String pass = "";
private String usuario = "root";
public Connection con;

public Conectate(Map<String,  Map<String, Item>> gamesByCountry, Map<String, String> codesByTitle,Map<String, String> countries) {
    try {
        Class.forName(driver);
        con = DriverManager.getConnection(cadenaConexion, usuario, pass);
        System.out.println("¡Conectado!");


        //CREAMOS LA TABLA
        Statement st = con.createStatement();

        st.executeUpdate("CREATE TABLE IF NOT EXISTS info_XboxOne (id INT AUTO_INCREMENT, PRIMARY KEY(id), "
                + "Juego_vinculado VARCHAR(500), Juego VARCHAR(500), Tipologia VARCHAR (500), Pertenece VARCHAR (500), "
                + "Nota VARCHAR (10), Descripcion_Ingles TEXT(4000), Descripcion_Castellano TEXT(4000), Pegi VARCHAR(10), Descripcion_Pegi VARCHAR(200),"
                + "Lanzamiento VARCHAR (50))");

        System.out.println( "Tabla creada!");



        for (String titulo : codesByTitle.keySet()) {     

            ResultSet rs = st.executeQuery("SELECT * FROM info_XboxOne WHERE juego = " + titulo);

            if (!rs.next()) { //si se cumple esta condicción significa que el juego no está incluido, con lo que lo metemos
                PreparedStatement ps = con.prepareStatement("INSERT INTO info_XboxOne (Juego, Tipologia, Pertenece, "
                + "Nota, Descripcion_Ingles, Descripcion_Castellano, Pegi, Descripcion_Pegi, Lanzamiento"
                + ") VALUES (?,?,?,?"
                + ",?,?,?,?,?"
                + ")");

                String code = codesByTitle.get(titulo);

                for (String country : countries.keySet()) {
            Item game = gamesByCountry.get(country).get(code);

                            if (country.equals("Estados Unidos")) {                                  
                                ps.setString(1,titulo);
                            }

            if (game != null) {

                                    if (country.equals("Estados Unidos")) {
                                        ps.setString(2,game.getValues().get(Constants.TIPOLOGIA));
                                        ps.setString(3,game.getValues().get(Constants.PERTENECE));                                            
                                        ps.setString(4,game.getValues().get(Constants.NOTA));
                                        ps.setString(9,game.getValues().get(Constants.FECHA));
                                        ps.setString(5,game.getValues().get(Constants.DESCRIPCION_INGLES));
                                    }

                                    if (country.equals("España")) {
                                        ps.setString(6,game.getValues().get(Constants.DESCRIPCION_CASTELLANO));
                                        ps.setString(7,game.getValues().get(Constants.PEGI));
                                        ps.setString(8,game.getValues().get(Constants.DESCRIPCION_PEGI));
                                    }

            }
                }
                ps.executeUpdate();
            } else { //El juego existe, con lo que actualizamos los datos.
                String query = "UPDATE info_XboxOne SET Tipologia = ?, Pertenece = ?, "
                + "Nota = ?, Descripcion_Ingles = ?, Descripcion_Castellano = ?, "
                + "Pegi = ?, Descripcion_Pegi = ?, Lanzamiento = ? WHERE juego = " + titulo;
                PreparedStatement ps = con.prepareStatement(query);

                String code = codesByTitle.get(titulo);
                for (String country : countries.keySet()) {
        Item game = gamesByCountry.get(country).get(code);

                    if (country.equals("Estados Unidos")) {                                  
                        ps.setString(1,titulo);
                    }

        if (game != null) {

                       if (country.equals("Estados Unidos")) {
                            ps.setString(2,game.getValues().get(Constants.TIPOLOGIA));
                            ps.setString(3,game.getValues().get(Constants.PERTENECE));                                            
                            ps.setString(4,game.getValues().get(Constants.NOTA));
                            ps.setString(9,game.getValues().get(Constants.FECHA));
                            ps.setString(5,game.getValues().get(Constants.DESCRIPCION_INGLES));
                        }

                        if (country.equals("España")) {
                            ps.setString(6,game.getValues().get(Constants.DESCRIPCION_CASTELLANO));
                            ps.setString(7,game.getValues().get(Constants.PEGI));
                            ps.setString(8,game.getValues().get(Constants.DESCRIPCION_PEGI));
                        }

        }
                }
                ps.executeUpdate();
            }                                  

        }




    } catch (Exception e) {
        JOptionPane.showMessageDialog(null, "No se ha podido establecer la conexión con la DB" + e);
    }

}

public String ConvertirObjectToString(Object Obj) {
String Str="";
if(Obj!=null){
    Str = Obj.toString();
}
return Str;
}


}

The error it gives me is:

mysql java

asked by JetLagFox 25.02.2017 в 19:12

source

4 answers

Gradually change the opacity of divs using CSS Fatal error: Call to undefined function sqlsrv_connect ()

score 3 · Answer 1

You are hiding information to the exception, so it is difficult to see what query is the error.

Change your code:

    } catch (Exception e) {
        JOptionPane.showMessageDialog(null, "No se ha podido establecer la conexión con la DB" + e);
    }

a

    } catch (Exception e) {
        JOptionPane.showMessageDialog(null, "No se ha podido establecer la conexión con la DB" + e);
        e.printStackTrace();
    }

The complete exception will give you the line number where the query is made that does not work for you.

Checking the syntax of your SQL, everything is fine until you add the value. The syntax error occurs because you compare a field with a literal datum without putting it in quotes, you could construct your SQL for example as:

String sql = String.format("SELECT * FROM info_XboxOne WHERE juego = '%s' ",titulo);

However , this way makes you vulnerable to SQL injection, because it does not protect data that contains "'", so you should prefer what you already did in other parties and use a "prepared statement":

PreparedStatement ps = con.preparedStatement("SELECT * FROM info_XboxOne WHERE juego = ?");
ps.setString(1,titulo);
ResultSet rs = ps.executeQuery();

Since you use pure fields type String , to facilitate your work in case you use many queries, you can declare a method to avoid repeating code:

public ResultSet execPreparedQuery(Connection con, String query, String... data){
    PreparedStatement ps = con.preparedStatement(query);
    for (int i = 0; i < data.length, i++){
        ps.setString(i+1, data[i]);
    }
    return ps.executeQuery();
}

Finally I recommend you not to place as much code inside a block try more on using the very generic Exception to catch errors. As you gave yourself an error message, you lied - it was never a problem to start the connection. It would be better to encapsulate each query in its own block of try ... catch in order to send the query to stdout in case of error:

String sql = "SELECT * FROM info_XboxOne WHERE juego = ?";
ResultSet rs;
try{
    rs = execPreparedQuery(con, sql, titulo);
} catch (SqlException se){
    // puedes imprimir lo que quieres saber sobre lo que pasó
    System.out.println(String.format("Error con consulta: %s , dato %s", sql, titulo));
    se.printStackTrace();
}

Never save on information in a block catch , if you do not know exactly what conditions you get inside and you're sure that you do not need more information about what happened.

score 2 · Answer 2

I think this query does not work well.

("SELECT * FROM info_XboxOne WHERE juego = " + titulo);

I've tried it in a database of my own and it gives an error. Instead you should try something like this:

SELECT * FROM info_XboxOne WHERE juego = ?;

and then st.setString (1, title);

score 1 · Answer 3

You should change the next line

ResultSet rs = st.executeQuery("SELECT * FROM info_XboxOne WHERE juego = " + titulo);

a

ResultSet rs = st.executeQuery("SELECT * FROM info_XboxOne WHERE juego = '" + titulo + "'");

Once you have changed that line you will still have problems because when you are doing the insert, you are leaving unassigned values, for example, what happens if "game" is equal to "null"? Your query would look like this:

INSERT INTO info_XboxOne (Juego, Tipologia, Pertenece, Nota, Descripcion_Ingles, Descripcion_Castellano, Pegi, Descripcion_Pegi, Lanzamiento) VALUES ("Estados Unidos",?,?,?,?,?,?,?,?)

So this is a syntax error in SQL, it would also happen for the UPDATE, remember to assign all the values of "?" in the query otherwise you will have errors when executing the code. If you do not need to fill all the values you should assign at least one empty String.

score 0 · Answer 4

Apparently the problem is that you are using MariaDB and NO mySql

The driver for MariaDB is incorrect should be:

//private String driver ="com.mysql.jdbc.Driver";
private String driver ="org.mariadb.jdbc.Driver";

and the connection String:

   // private String cadenaConexion ="jdbc:mysql://localhost/XboxOne";
  private String cadenaConexion = "jdbc:mariadb://localhost/XboxOne"

I advise you to check the MariaDB documentation