I have a small code in java that before entering information in the database, search through a code if the record is already included in the database or not. If it is, it updates it and inserts it.
boolean isInsert;
boolean precio_modificado = false;
try (PreparedStatement ps = con_actu.prepareStatement("SELECT * FROM info_XboxOne WHERE Codigo_juego = ?")) {
ps.setString(1, code);
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_actu.prepareStatement("INSERT INTO info_XboxOne (Juego, DWG, juegos_vault, EA_Access, spotlight, fecha_spotlight, Tipologia, Pertenece, "
+ "Nota, Descripcion_Ingles, Descripcion_Castellano, Pegi,Pegi_USA, Descripcion_Pegi, Codigo_juego, Lanzamiento, URL, URL_imagen,"
+ "Argentina, Argentina_Oferta, Argentina_Gold, Argentina_Access,"
+ "Australia, Australia_Oferta, Australia_Gold, Australia_Access,"
+ "Brasil, Brasil_Oferta, Brasil_Gold, Brasil_Access,"
+ "Canada, Canada_Oferta, Canada_Gold, Canada_Access,"
+ "Chile, Chile_Oferta, Chile_Gold, Chile_Access,"
+ "China, China_Oferta, China_Gold, China_Access,"
+ "Colombia, Colombia_Oferta, Colombia_Gold, Colombia_Access,"
+ "Espanya, Espanya_Oferta, Espanya_Gold, Espanya_Access,"
+ "Hong_Kong, Hong_Kong_Oferta, Hong_Kong_Gold, Hong_Kong_Access,"
+ "Hungria, Hungria_Oferta, Hungria_Gold, Hungria_Access,"
+ "India, India_Oferta, India_Gold, India_Access,"
+ "Inglaterra, Inglaterra_Oferta, Inglaterra_Gold, Inglaterra_Access,"
+ "Japon, Japon_Oferta, Japon_Gold, Japon_Access,"
+ "Korea, Korea_Oferta, Korea_Gold, Korea_Access,"
+ "Mexico, Mexico_Oferta, Mexico_Gold, Mexico_Access,"
+ "Rusia, Rusia_Oferta, Rusia_Gold, Rusia_Access,"
+ "Singapur, Singapur_Oferta, Singapur_Gold, Singapur_Access,"
+ "Sudafrica, Sudafrica_Oferta, Sudafrica_Gold, Sudafrica_Access,"
+ "Taiwan, Taiwan_Oferta, Taiwan_Gold, Taiwan_Access,"
+ "USA, USA_Oferta, USA_Gold, USA_Access"
+ ") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?"
+ ",?,?,?,?"
+ ",?,?,?,?"
+ ",?,?,?,?"
+ ",?,?,?,?"
+ ",?,?,?,?"
+ ",?,?,?,?"
+ ",?,?,?,?"
+ ",?,?,?,?"
+ ",?,?,?,?"
+ ",?,?,?,?"
+ ",?,?,?,?"
+ ",?,?,?,?"
+ ",?,?,?,?"
+ ",?,?,?,?"
+ ",?,?,?,?"
+ ",?,?,?,?"
+ ",?,?,?,?"
+ ",?,?,?,?"
+ ",?,?,?,?"
+ ",?,?,?,?"
+ ")")) {
ps.setString(1,titulo);
ps.setString(2,game.getValues().get(Constants.GOLD));
ps.setString(3,game.getValues().get(Constants.THE_VAULT));
ps.setString(4,game.getValues().get(Constants.EA_ACCESS));
ps.setString(5,game.getValues().get(Constants.OFERTA));
ps.setString(6,game.getValues().get(Constants.FECHA_SPOTLIGHT));
ps.setString(7,game.getValues().get(Constants.TIPOLOGIA));
ps.setString(8,game.getValues().get(Constants.PERTENECE));
ps.setString(9,game.getValues().get(Constants.NOTA));
ps.setString(10,game.getValues().get(Constants.DESCRIPCION_INGLES));
ps.setString(11,game.getValues().get(Constants.DESCRIPCION_CASTELLANO));
ps.setString(12,game.getValues().get(Constants.PEGI));
ps.setString(13,game.getValues().get(Constants.PEGI_USA));
ps.setString(14,game.getValues().get(Constants.DESCRIPCION_PEGI));
ps.setString(15,game.getValues().get(Constants.VALUE_MSSTORE_URL));
ps.setString(16,game.getValues().get(Constants.FECHA));
ps.setString(17,game.getValues().get(Constants.URL_JUEGO));
ps.setString(18,game.getValues().get(Constants.URL_IMAGEN));
int contador = 19;
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.setString(contador,game.getValues().get(Constants.PRICE_ACCESS + " "+ e.getKey()));
contador++;
}
ps.executeUpdate();
}
} else {
titulo = game.getName();
String query = "UPDATE info_XboxOne SET "
+ "Nota = ?, Descripcion_Ingles = ?, Descripcion_Castellano = ?, "
+ "Pegi = ?, Pegi_USA = ?, Descripcion_Pegi = ?, juego = ?, Lanzamiento = ?, URL = ?, URL_imagen = ?,"
+ "Argentina = ?, Argentina_Oferta = ?, Argentina_Gold = ?, Argentina_Access = ?,"
+ "Australia = ?, Australia_Oferta = ?, Australia_Gold = ?, Australia_Access = ?,"
+ "Brasil = ?, Brasil_Oferta = ?, Brasil_Gold = ?, Brasil_Access = ?,"
+ "Canada = ?, Canada_Oferta = ?, Canada_Gold = ?, Canada_Access = ?,"
+ "Chile = ?, Chile_Oferta = ?, Chile_Gold = ?, Chile_Access = ?,"
+ "China = ?, China_Oferta = ?, China_Gold = ?, China_Access = ?,"
+ "Colombia = ?, Colombia_Oferta = ?, Colombia_Gold = ?, Colombia_Access = ?,"
+ "Espanya = ?, Espanya_Oferta = ?, Espanya_Gold = ?, Espanya_Access = ?,"
+ "Hong_Kong = ?, Hong_Kong_Oferta = ?, Hong_Kong_Gold = ?, Hong_Kong_Access = ?,"
+ "Hungria = ?, Hungria_Oferta = ?, Hungria_Gold = ?, Hungria_Access = ?,"
+ "India = ?, India_Oferta = ?, India_Gold = ?, India_Access = ?,"
+ "Inglaterra = ?, Inglaterra_Oferta = ?, Inglaterra_Gold = ?, Inglaterra_Access = ?,"
+ "Japon = ?, Japon_Oferta = ?, Japon_Gold = ?, Japon_Access = ?,"
+ "Korea = ?, Korea_Oferta = ?, Korea_Gold = ?, Korea_Access = ?,"
+ "Mexico = ?, Mexico_Oferta = ?, Mexico_Gold = ?, Mexico_Access = ?,"
+ "Rusia = ?, Rusia_Oferta = ?, Rusia_Gold = ?, Rusia_Access = ?,"
+ "Singapur = ?, Singapur_Oferta = ?, Singapur_Gold = ?, Singapur_Access = ?,"
+ "Sudafrica = ?, Sudafrica_Oferta = ?, Sudafrica_Gold = ?, Sudafrica_Access = ?,"
+ "Taiwan = ?, Taiwan_Oferta = ?, Taiwan_Gold = ?, Taiwan_Access = ?,"
+ "USA = ?, USA_Oferta = ?, USA_Gold = ?, USA_Access = ?,"
+ "DWG = ?, juegos_vault = ?, EA_Access = ?, spotlight = ?, fecha_spotlight = ?"
+ " WHERE Codigo_juego = ?";
try (PreparedStatement ps = con_actu.prepareStatement(query)) {
ps.setString(1,game.getValues().get(Constants.NOTA));
ps.setString(2,game.getValues().get(Constants.DESCRIPCION_INGLES));
ps.setString(3,game.getValues().get(Constants.DESCRIPCION_CASTELLANO));
ps.setString(4,game.getValues().get(Constants.PEGI));
ps.setString(5,game.getValues().get(Constants.PEGI_USA));
ps.setString(6,game.getValues().get(Constants.DESCRIPCION_PEGI));
ps.setString(7,titulo);
ps.setString(8,game.getValues().get(Constants.FECHA));
ps.setString(9,game.getValues().get(Constants.URL_JUEGO));
ps.setString(10,game.getValues().get(Constants.URL_IMAGEN));
ps.setString(96,game.getValues().get(Constants.VALUE_MSSTORE_URL));
ps.setString(91,game.getValues().get(Constants.GOLD));
ps.setString(92,game.getValues().get(Constants.THE_VAULT));
ps.setString(93,game.getValues().get(Constants.EA_ACCESS));
ps.setString(94,game.getValues().get(Constants.OFERTA));
ps.setString(95,game.getValues().get(Constants.FECHA_SPOTLIGHT));
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.setString(contador,game.getValues().get(Constants.PRICE_ACCESS + " "+ e.getKey()));
contador++;
}
ps.executeUpdate();
}
}
In theory it usually works well, but sometimes it does not. When the registration is already included, instead of updating me, I insert it. The funny thing is that if I run the code again to check the same record, the second time it does NOT insert it, it updates it. The only thing that I have noticed is that the code of the URL that I enter manually is in uppercase, and that the code of the database is in lowercase.
I do not know if that can cause the search to return a negative result. Otherwise you could add the following line before doing the search:
code.toLowerCase;
What I do not understand is how it can happen to me from time to time, and when it happens, if I run the code again with the same URL, it updates it instead of inserting it.