Good morning,
I have a java program that updates data or inserts data into a table within a database. The program worked perfectly until today, in the table I had created by default through the java program, I added new columns to update them manually. When I run the program again, it gave me the following error: Field 'anywhere' doesn't have a default value , which curiously is the first new column I have included. What I do not understand is, why do I have to define a value for the column anywhere , just like I suppose it will happen to me with the other columns that I have included manually, if in the java program I do not want to update that column? I leave the code:
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) {
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), "
+ "anywhere tinyint(1), black tinyint(1), black tinyint(1), fecha_spotlight TIMESTAMP, DWG tinyint(1), GWG tinyint(1),"
+ "desde timestamp, hasta timestamp, juegos_vault tinyint(1),"
+ "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), URL VARCHAR (400), URL_imagen VARCHAR (5000),"
+ "Argentina VARCHAR (50), Argentina_Oferta VARCHAR (50), Argentina_Gold VARCHAR (50), Argentina_Access VARCHAR (50),"
+ "Australia VARCHAR (50), Australia_Oferta VARCHAR (50), Australia_Gold VARCHAR (50), Australia_Access VARCHAR (50),"
+ "Brasil VARCHAR (50), Brasil_Oferta VARCHAR (50), Brasil_Gold VARCHAR (50), Brasil_Access VARCHAR (50),"
+ "Canada VARCHAR (50), Canada_Oferta VARCHAR (50), Canada_Gold VARCHAR (50), Canada_Access VARCHAR (50),"
+ "Chile VARCHAR (50), Chile_Oferta VARCHAR (50), Chile_Gold VARCHAR (50), Chile_Access VARCHAR (50),"
+ "China VARCHAR (50), China_Oferta VARCHAR (50), China_Gold VARCHAR (50), China_Access VARCHAR (50),"
+ "Colombia VARCHAR (50), Colombia_Oferta VARCHAR (50), Colombia_Gold VARCHAR (50), Colombia_Access VARCHAR (50),"
+ "Espanya VARCHAR (50), Espanya_Oferta VARCHAR (50), Espanya_Gold VARCHAR (50), Espanya_Access VARCHAR (50),"
+ "USA VARCHAR (50), USA_Oferta VARCHAR (50), USA_Gold VARCHAR (50), USA_Access VARCHAR (50),"
+ "Hong_Kong VARCHAR (50), Hong_Kong_Oferta VARCHAR (50), Hong_Kong_Gold VARCHAR (50), Hong_Kong_Access VARCHAR (50),"
+ "Hungria VARCHAR (50), Hungria_Oferta VARCHAR (50), Hungria_Gold VARCHAR (50), Hungria_Access VARCHAR (50),"
+ "India VARCHAR (50), India_Oferta VARCHAR (50), India_Gold VARCHAR (50), India_Access VARCHAR (50),"
+ "Inglaterra VARCHAR (50), Inglaterra_Oferta VARCHAR (50), Inglaterra_Gold VARCHAR (50), Inglaterra_Access VARCHAR (50),"
+ "Japon VARCHAR (50), Japon_Oferta VARCHAR (50), Japon_Gold VARCHAR (50), Japon_Access VARCHAR (50),"
+ "Korea VARCHAR (50), Korea_Oferta VARCHAR (50), Korea_Gold VARCHAR (50), Korea_Access VARCHAR (50),"
+ "Mexico VARCHAR (50), Mexico_Oferta VARCHAR (50), Mexico_Gold VARCHAR (50), Mexico_Access VARCHAR (50),"
+ "Rusia VARCHAR (50), Rusia_Oferta VARCHAR (50), Rusia_Gold VARCHAR (50), Rusia_Access VARCHAR (50),"
+ "Singapur VARCHAR (50), Singapur_Oferta VARCHAR (50), Singapur_Gold VARCHAR (50), Singapur_Access VARCHAR (50),"
+ "Sudafrica VARCHAR (50), Sudafrica_Oferta VARCHAR (50), Sudafrica_Gold VARCHAR (50), Sudafrica_Access VARCHAR (50),"
+ "Taiwan VARCHAR (50), Taiwan_Oferta VARCHAR (50), Taiwan_Gold VARCHAR (50), Taiwan_Access 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, 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,"
+ "USA, USA_Oferta, USA_Gold, USA_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"
+ ") 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));
ps.setString(11,game.getValues().get(Constants.URL_JUEGO));
ps.setString(12,game.getValues().get(Constants.URL_IMAGEN));
int contador = 13;
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(); //aquí es donde me muestra el error Field 'anywhere' doesn't have a default value
}
} else {
String query = "UPDATE info_XboxOne SET Tipologia = ?, Pertenece = ?, "
+ "Nota = ?, Descripcion_Ingles = ?, Descripcion_Castellano = ?, "
+ "Pegi = ?, Pegi_USA = ?, Descripcion_Pegi = ?, juego = ?, 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 = ?,"
+ "USA = ?, USA_Oferta = ?, USA_Gold = ?, USA_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 = ?"
+ " WHERE Codigo_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,titulo);
ps.setString(10,game.getValues().get(Constants.URL_JUEGO));
ps.setString(11,game.getValues().get(Constants.URL_IMAGEN));
ps.setString(92,game.getValues().get(Constants.VALUE_MSSTORE_URL));
int contador = 12;
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();
}
}
}
} 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 have put it in the code with a comment, but to make it easier to see the line that marks the error is the following: ps.executeUpdate();
The structure of the table is as follows, although I show the first columns since they are almost 100 in total:
I also leave all the errors you throw at me:
The only thing that occurs to me is to also define a value for the new columns that I have added, but I do not understand why it asks for a value when I understand that I do not have to introduce it with the execution of the program.