Good morning,
I have a code that theoretically seeks me first if the record is already included in the database and if so, I update the data, otherwise it includes it.
However, the data is not updated, nor do I get it back, that is, I understand that I have something wrong with the execution of the "UPDATE". I leave the code:
public Conectate(ArrayList<Item> games, final 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), Pegi_USA VARCHAR (30) , Descripcion_Pegi VARCHAR(500), Codigo_juego VARCHAR (100),"
+ "Lanzamiento VARCHAR (50),"
+ "Argentina VARCHAR (50), Argentina_Oferta VARCHAR (50), Argentina_Gold VARCHAR (50),"
+ "Brasil VARCHAR (50), Brasil_Oferta VARCHAR (50), Brasil_Gold VARCHAR (50),"
+ "Canada VARCHAR (50), Canada_Oferta VARCHAR (50), Canada_Gold VARCHAR (50),"
+ "Colombia VARCHAR (50), Colombia_Oferta VARCHAR (50), Colombia_Gold VARCHAR (50),"
+ "Espanya VARCHAR (50), Espanya_Oferta VARCHAR (50), Espanya_Gold VARCHAR (50),"
+ "USA VARCHAR (50), USA_Oferta VARCHAR (50), USA_Gold VARCHAR (50),"
+ "Hong_Kong VARCHAR (50), Hong_Kong_Oferta VARCHAR (50), Hong_Kong_Gold VARCHAR (50),"
+ "Hungria VARCHAR (50), Hungria_Oferta VARCHAR (50), Hungria_Gold VARCHAR (50),"
+ "Mexico VARCHAR (50), Mexico_Oferta VARCHAR (50), Mexico_Gold VARCHAR (50),"
+ "Rusia VARCHAR (50), Rusia_Oferta VARCHAR (50), Rusia_Gold VARCHAR (50),"
+ "Singapur VARCHAR (50), Singapur_Oferta VARCHAR (50), Singapur_Gold VARCHAR (50),"
+ "Sudafrica VARCHAR (50), Sudafrica_Oferta VARCHAR (50), Sudafrica_Gold VARCHAR (50)"
+ ")");
System.out.println( "Tabla creada!");
for (Item game : games) {
String titulo = game.getName();
boolean isInsert;
try (PreparedStatement ps = con.prepareStatement("SELECT * FROM info_XboxOne WHERE juego = ?")) {
ps.setString(1, titulo);
try (ResultSet rs = ps.executeQuery()) {
isInsert = !rs.next();
}
}
if (isInsert) { //si se cumple esta condicción significa que el juego no está incluido, con lo que lo metemos
try(PreparedStatement ps = con.prepareStatement("INSERT INTO info_XboxOne (Juego, Tipologia, Pertenece, "
+ "Nota, Descripcion_Ingles, Descripcion_Castellano, Pegi,Pegi_USA, Descripcion_Pegi, Codigo_juego,"
+ "Argentina, Argentina_Oferta, Argentina_Gold,"
+ "Brasil, Brasil_Oferta, Brasil_Gold,"
+ "Canada, Canada_Oferta, Canada_Gold,"
+ "Colombia, Colombia_Oferta, Colombia_Gold,"
+ "Espanya, Espanya_Oferta, Espanya_Gold,"
+ "USA, USA_Oferta, USA_Gold,"
+ "Hong_Kong, Hong_Kong_Oferta, Hong_Kong_Gold,"
+ "Hungria, Hungria_Oferta, Hungria_Gold,"
+ "Mexico, Mexico_Oferta, Mexico_Gold,"
+ "Rusia, Rusia_Oferta, Rusia_Gold,"
+ "Singapur, Singapur_Oferta, Singapur_Gold,"
+ "Sudafrica, Sudafrica_Oferta, Sudafrica_Gold"
+ ") VALUES (?,?,?,?,?,?,?,?,?,?"
+ ",?,?,?"
+ ",?,?,?"
+ ",?,?,?"
+ ",?,?,?"
+ ",?,?,?"
+ ",?,?,?"
+ ",?,?,?"
+ ",?,?,?"
+ ",?,?,?"
+ ",?,?,?"
+ ",?,?,?"
+ ",?,?,?"
+ ")")) {
ps.setString(1,titulo);
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(5,game.getValues().get(Constants.DESCRIPCION_INGLES));
ps.setString(6,game.getValues().get(Constants.DESCRIPCION_CASTELLANO));
ps.setString(7,game.getValues().get(Constants.PEGI));
ps.setString(8,game.getValues().get(Constants.PEGI_USA));
ps.setString(9,game.getValues().get(Constants.DESCRIPCION_PEGI));
ps.setString(10,game.getValues().get(Constants.VALUE_MSSTORE_URL));
int contador = 11;
for (Map.Entry<String, String> e: countries.entrySet()) {
ps.setString(contador,game.getValues().get(Constants.PRICE_NORMAL + " "+ e.getKey()));
contador++;
ps.setString(contador,game.getValues().get(Constants.PRICE_OFFER + " "+ e.getKey()));
contador++;
ps.setString(contador,game.getValues().get(Constants.PRICE_GOLD + " "+ e.getKey()));
contador++;
}
ps.executeUpdate();
}
} else {
String query = "UPDATE info_XboxOne SET Tipologia = ?, Pertenece = ?, "
+ "Nota = ?, Descripcion_Ingles = ?, Descripcion_Castellano = ?, "
+ "Pegi = ?, Pegi_USA = ?, Descripcion_Pegi = ?, Codigo_juego = ?,"
+ "Argentina = ?, Argentina_Oferta = ?, Argentina_Gold = ?,"
+ "Brasil = ?, Brasil_Oferta = ?, Brasil_Gold = ?,"
+ "Canada = ?, Canada_Oferta = ?, Canada_Gold = ?,"
+ "Colombia = ?, Colombia_Oferta = ?, Colombia_Gold = ?,"
+ "Espanya = ?, Espanya_Oferta = ?, Espanya_Gold = ?,"
+ "USA = ?, USA_Oferta = ?, USA_Gold = ?,"
+ "Hong_Kong = ?, Hong_Kong_Oferta = ?, Hong_Kong_Gold = ?,"
+ "Hungria = ?, Hungria_Oferta = ?, Hungria_Gold = ?,"
+ "Mexico = ?, Mexico_Oferta = ?, Mexico_Gold = ?,"
+ "Rusia = ?, Rusia_Oferta = ?, Rusia_Gold = ?,"
+ "Singapur = ?, Singapur_Oferta = ?, Singapur_Gold = ?,"
+ "Sudafrica = ?, Sudafrica_Oferta = ?, Sudafrica_Gold = ?"
+ " WHERE juego = ?";
try (PreparedStatement ps = con.prepareStatement(query)) {
ps.setString(1,game.getValues().get(Constants.TIPOLOGIA));
ps.setString(2,game.getValues().get(Constants.PERTENECE));
ps.setString(3,game.getValues().get(Constants.NOTA));
ps.setString(4,game.getValues().get(Constants.DESCRIPCION_INGLES));
ps.setString(5,game.getValues().get(Constants.DESCRIPCION_CASTELLANO));
ps.setString(6,game.getValues().get(Constants.PEGI));
ps.setString(7,game.getValues().get(Constants.PEGI_USA));
ps.setString(8,game.getValues().get(Constants.DESCRIPCION_PEGI));
ps.setString(9,game.getValues().get(Constants.VALUE_MSSTORE_URL));
ps.setString(10,titulo);
int contador = 11;
for (Map.Entry<String, String> e: countries.entrySet()) {
ps.setString(contador,game.getValues().get(Constants.PRICE_NORMAL + " "+ e.getKey()));
contador++;
ps.setString(contador,game.getValues().get(Constants.PRICE_OFFER + " "+ e.getKey()));
contador++;
ps.setString(contador,game.getValues().get(Constants.PRICE_GOLD + " "+ e.getKey()));
contador++;
}
ps.executeUpdate();
}
}
}
} catch (Exception e) {
JOptionPane.showMessageDialog(null, "No se ha podido establecer la conexión con la DB" + e);
e.printStackTrace();
}
}
public String ConvertirObjectToString(Object Obj) {
String Str="";
if(Obj!=null){
Str = Obj.toString();
}
return Str;
}
}
I would appreciate any kind of help.