Insert data from java in MySQL

1

Good morning,

I have looked at other alternatives of questions that have been asked here without being able to obtain a solution. My doubt comes when inserting data from an array. In the java program itself, a table is created (if it does not exist) and then it goes to enter all the data stored in the array, but it does not insert anything.

The code I have:

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

public Conectate(String[][] listaEmpresaA) {
    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 Xbox_One (id INT AUTO_INCREMENT, "
                + "PRIMARY KEY(id), Juego VARCHAR(500), URL VARCHAR (200), Lanzamiento VARCHAR (50), Descripcion TEXT(5000),"
                + "Pegi VARCHAR(10), Descripcion_Pegi VARCHAR (200), nota FLOAT(10,1),"
                + "USA VARCHAR (100), USA_Gold VARCHAR (100), USA_sin_Gold VARCHAR (100), USA_EA VARCHAR (100),"
                + "ARG VARCHAR (100), ARG_Gold VARCHAR (100), ARG_sin_Gold VARCHAR (100), ARG_EA VARCHAR (100),"
                + "AUS VARCHAR (100), AUS_Gold VARCHAR (100), AUS_sin_Gold VARCHAR (100), AUS_EA VARCHAR (100),"
                + "BRA VARCHAR (100), BRA_Gold VARCHAR (100), BRA_sin_Gold VARCHAR (100), BRA_EA VARCHAR (100),"
                + "CAN VARCHAR (100), CAN_Gold VARCHAR (100), CAN_sin_Gold VARCHAR (100), CAN_EA VARCHAR (100),"
                + "SIN VARCHAR (100), SIN_Gold VARCHAR (100), SIN_sin_Gold VARCHAR (100), SIN_EA VARCHAR (100),"
                + "ESP VARCHAR (100), ESP_Gold VARCHAR (100), ESP_sin_Gold VARCHAR (100), ESP_EA VARCHAR (100),"
                + "HK VARCHAR (100), HK_Gold VARCHAR (100), HK_sin_Gold VARCHAR (100), HK_EA VARCHAR (100),"
                + "MEX VARCHAR (100), MEX_Gold VARCHAR (100), MEX_sin_Gold VARCHAR (100), MEX_EA VARCHAR (100),"
                + "COL VARCHAR (100), COL_Gold VARCHAR (100), COL_sin_Gold VARCHAR (100), COL_EA VARCHAR (100),"
                + "HUN VARCHAR (100), HUN_Gold VARCHAR (100), HUN_sin_Gold VARCHAR (100), HUN_EA VARCHAR (100),"
                + "SUD VARCHAR (100), SUD_Gold VARCHAR (100), SUD_sin_Gold VARCHAR (100), SUD_EA VARCHAR (100),"
                + "RUS VARCHAR (100), RUS_Gold VARCHAR (100), RUS_sin_Gold VARCHAR (100), RUS_EA VARCHAR (100),"
                + "CHI VARCHAR (100), CHI_Gold VARCHAR (100), CHI_sin_Gold VARCHAR (100), CHI_EA VARCHAR (100),"
                + "IND VARCHAR (100), IND_Gold VARCHAR (100), IND_sin_Gold VARCHAR (100), IND_EA VARCHAR (100),"
                + "JP VARCHAR (100), JP_Gold VARCHAR (100), JP_sin_Gold VARCHAR (100), JP_EA VARCHAR (100),"
                + "KOR VARCHAR (100), KOR_Gold VARCHAR (100), KOR_sin_Gold VARCHAR (100), KOR_EA VARCHAR (100),"
                + "TAI VARCHAR (100), TAI_Gold VARCHAR (100), TAI_sin_Gold VARCHAR (100), TAI_EA VARCHAR (100),"
                + "UK VARCHAR (100), UK_Gold VARCHAR (100), UK_sin_Gold VARCHAR (100), UK_EA VARCHAR (100))"
                );

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


        PreparedStatement ps = con.prepareStatement("INSERT INTO           Xbox_One (Juego, URL, Publicado, Lanzamiento, Descripcion, Pegi, Descripcion_Pegi, nota, "
                                + "USA, USA_Gold, USA_sin_Gold, USA_EA,"
                                + "ARG, ARG_Gold, ARG_sin_Gold, ARG_EA,"
                                + "AUS, AUS_Gold, AUS_sin_Gold, AUS_EA,"
                                + "BRA, BRA_Gold, BRA_sin_Gold, BRA_EA,"
                                + "CAN, CAN_Gold, CAN_sin_Gold, CAN_EA,"
                                + "SIN, SIN_Gold, SIN_sin_Gold, SIN_EA,"
                                + "ESP, ESP_Gold, ESP_sin_Gold, ESP_EA,"
                                + "HK, HK_Gold, HK_sin_Gold, HK_EA,"
                                + "MEX, MEX_Gold, MEX_sin_Gold, MEX_EA,"
                                + "COL, COL_Gold, COL_sin_Gold, COL_EA,"
                                + "HUN, HUN_Gold, HUN_sin_Gold, HUN_EA,"
                                + "SUD, SUD_Gold, SUD_sin_Gold, SUD_EA,"
                                + "RUS, RUS_Gold, RUS_sin_Gold, RUS_EA,"
                                + "CHI, CHI_Gold, CHI_sin_Gold, CHI_EA,"
                                + "CHINA, CHINA_Gold, CHINA_sin_Gold, CHINA_EA,"
                                + "IND, IND_Gold, IND_sin_Gold, IND_EA,"
                                + "JP, JP_Gold, JP_sin_Gold, JP_EA,"
                                + "KOR, KOR_Gold, KOR_sin_Gold, KOR_EA,"
                                + "TAI, TAI_Gold, TAI_sin_Gold, TAI_EA,"
                                + "UK, UK_Gold, UK_sin_Gold, UK_EA)"
                                + ") VALUES (?,?,?,?,?,?,?,?"
                                + ",?,?,?,?"
     + ",?,?,?,?"
     + ",?,?,?,?"
     + ",?,?,?,?"
     + ",?,?,?,?"
     + ",?,?,?,?"
     + ",?,?,?,?"
     + ",?,?,?,?"
     + ",?,?,?,?"
     + ",?,?,?,?"
     + ",?,?,?,?"
     + ",?,?,?,?"
     + ",?,?,?,?"
     + ",?,?,?,?"
     + ",?,?,?,?"
     + ",?,?,?,?"
     + ",?,?,?,?"
     + ",?,?,?,?"
     + ",?,?,?,?"
     + ",?,?,?,?"
                                + ")");


        for(String[] array : listaEmpresaA){
            for(int i = 0; i < 95; ++i){ //Careful to an             arrayoutofbounds exception, but every parameter MUST BE SET
                ps.setString(i+2, array[i]); //Can't be null, if so, must use setNull(i+1);
            }
        ps.executeUpdate();
        }







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

}

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


}

I would appreciate any kind of help. I have managed to insert loose data by putting them by hand but in this case not.

Thanks in advance.

    
asked by JetLagFox 20.01.2017 в 22:27
source

1 answer

0

You never set the value for the first parameter of PreparedStatement . Also, if I count well, you have 22 * 4 = 88 values to put in ps , yes to code would make more sense if it looked like this:

    for(String[] array : listaEmpresaA){
        for(int i = 0; i < 88; i++){
            // Careful to avoid ArrayIndexOutOfBoundsException
            if (array[i]!=null || i >= array.length){
                ps.setString(i+1, array[i]);
            } else {
                ps.setNull(i+1);
            }
        //Can't be null, if so, must use setNull(i+1);
        }
    ps.executeUpdate();
    }

Someone who wants to save you headaches gave you comments in English, I took the liberty of fishing for you.

    
answered by 21.01.2017 в 01:03