Cannot insert data in MySQL using java

Hi everyone,

I wanted to add/update data from java, but I think I have mistakes in the code cause the only thing it is doing is creating the table.

What I want to do is to verify first if the game is already in the database, if not add the game, and if it already is in the database just update the information.

The code I am using:

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;
}

    
}

perhaps not the right time to mention this, but MySQL provides INSERT...ON DUPLICATE KEY UPDATE syntax so that you can accomplish this with a single statement

Oh, it sounds interesting, how does it work?

well, you use an INSERT statement, with the ON DUPLICATE KEY UPDATE option

documentation is in da manual here – https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

I´m not sure if it is going to work in this case. As all the examples I am reading, the duplicate character is the main key, but in my case I have an ID, which is the primary key, and the name of the game, which is just VARCHAR.

Can I do something like this?

INSERT INTO tabla (Juego, Tipologia, Pertenece, Nota, Descripcion_Ingles, Descripcion_Castellano, Pegi, Descripcion_Pegi, Lanzamiento) VALUES (?,?,?,?,?,?,?,?,?)
ON DUPLICATE KEY UPDATE Tipologia=VALUES(Tipologia), Pertenece =VALUES(Pertenece), Nota=VALUES(Nota)...

yes, that’s how you do it

most important – you need to have a UNIQUE key (one or more columns) defined for the table, which is the basis upon which a duplicate is detected

presumably the name of the game is your unique key?

Not really, I have created a column named ID and is the one which is the key, maybe I should change, delete the ID and use the name of the game as primary…but even it is very strange to happen, there could be two games with the same name, that is why I am using ID column as primary.

so how do you decide if a new insert is a duplicate or not? based on which columns?

You are right. But can happen, I haven’t encounter that situation till now because I have been introducing all the data manually.

so what are your criteria for determining if a new row is a duplicate or not?

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.