SQL query won't stick in the database :confused:

[alexander.s]

Hi!

I'm using Netbeans 6.8 and my MySQL connector is working fine. And when I do create my tables and consequently read them it seems to be working like a charm, however, when I try to read them again they're not there? I check manually in the MySQL command prompt and the tables are there, but they're empty ? :confused:

So what I've done is I delete the tables and create them every single time I need to make a query, this works since my database is tiny. But I really would like to get this working how it's supposed to.

Thank you in advance!

public ArrayList<String> getQuery(String query){
         Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        String url = "jdbc:mysql://localhost:3306/";
        String db = "databasename";
        String driver = "com.mysql.jdbc.Driver";
        String user = "username";
        String pass = "password";

        try {
            Class.forName(driver);
            con = DriverManager.getConnection(url + db, user, pass);
            con.setAutoCommit(false);// Disables auto-commit.

            st = con.createStatement();

            st.executeUpdate("DROP table components");
            st.executeUpdate("DROP table accessories");
            st.executeUpdate("create table components(objekt varchar(50),childID int not null)");
            st.executeUpdate("create table accessories(ID int not null auto_increment primary key,child varchar(50))");
            for(n=1;n<=12;n++){
                st.executeUpdate("INSERT INTO accessories VALUES('"+n+"','"+accessories[n-1]+"')");
            }
            st.executeUpdate("INSERT INTO components VALUES('Dörr','1')");
            st.executeUpdate("INSERT INTO components VALUES('Dörr','2')");
            st.executeUpdate("INSERT INTO components VALUES('Dörr','3')");
            st.executeUpdate("INSERT INTO components VALUES('Dörr','4')");
            st.executeUpdate("INSERT INTO components VALUES('Dörr','5')");
            st.executeUpdate("INSERT INTO components VALUES('Fönster','6')");
            st.executeUpdate("INSERT INTO components VALUES('Fönster','7')");
            st.executeUpdate("INSERT INTO components VALUES('Fönster','1')");
            st.executeUpdate("INSERT INTO components VALUES('Fönster','2')");
            st.executeUpdate("INSERT INTO components VALUES('Fönster','8')");
            st.executeUpdate("INSERT INTO components VALUES('Fönster','9')");
            st.executeUpdate("INSERT INTO components VALUES('Vägg','8')");
            st.executeUpdate("INSERT INTO components VALUES('Vägg','10')");
            st.executeUpdate("INSERT INTO components VALUES('Vägg','4')");
            st.executeUpdate("INSERT INTO components VALUES('Tak','11')");
            st.executeUpdate("INSERT INTO components VALUES('Tak','4')");
            st.executeUpdate("INSERT INTO components VALUES('Tak','8')");
            st.executeUpdate("INSERT INTO components VALUES('Golv','8')");
            st.executeUpdate("INSERT INTO components VALUES('Golv','11')");
            st.executeUpdate("INSERT INTO components VALUES('Golv','12')");
            st.executeBatch();


            String q = "SELECT child FROM accessories INNER JOIN components ON components.objekt='"+query+"' AND components.childID=accessories.ID";
            rs = st.executeQuery(q);
            while (rs.next()) {
                 answer.add(rs.getString(1).toString());
            }
            rs.close();
            st.close();
            con.close();
           
        } catch (Exception e) {
            e.printStackTrace();
        }
         return answer;
    }

[PhHein]

Where do you commit your statements?

[alexander.s]

I'm sorry I think you're going to have to elaborate. I commit my SQL statements from that method "getQuery", I also opened the MySQL command prompt and wrote select * from components for example. I don't think I understand your question at all, sorry.

[masijade]

I'm sorry I think you're going to have to elaborate. I commit my SQL statements from that method "getQuery", I also opened the MySQL command prompt and wrote select * from components for example. I don't think I understand your question at all, sorry.

No, you execute them there, you do not commit them there. You never call commit() on the connection and you never check whether "autocommit" is true or not, so again, where are you commiting your actions.

Edit: And close your resources in a Finally block unless you want to eventually sabotage your programs ability to use the database (and possibly anyone else, as well).

[alexander.s]

You were absolutely correct, I did not commit at all. I didn't know I had to, but now I do :)

I've added a con.commit() and moved the close() of my resources as you call it into a finally block.

Thanks a lot :)

[Tolls]

Make sure you close the resources in reverse order (rs, st then con), and each in their own try/catch block (not one block for all three).