Results 1 to 6 of 6
  1. #1
    alexander.s is offline Member
    Join Date
    Aug 2008
    Posts
    8
    Rep Power
    0

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

    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!

    Java Code:
    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;
        }

  2. #2
    PhHein's Avatar
    PhHein is offline Senior Member
    Join Date
    Apr 2009
    Location
    Germany
    Posts
    1,430
    Rep Power
    6

    Default

    Where do you commit your statements?
    Math problems? Call 1-800-[(10x)(13i)^2]-[sin(xy)/2.362x]
    The Ubiquitous Newbie Tips

  3. #3
    alexander.s is offline Member
    Join Date
    Aug 2008
    Posts
    8
    Rep Power
    0

    Default

    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.

  4. #4
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    8

    Default

    Quote Originally Posted by alexander.s View Post
    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).

  5. #5
    alexander.s is offline Member
    Join Date
    Aug 2008
    Posts
    8
    Rep Power
    0

    Default

    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 :)

  6. #6
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,450
    Rep Power
    18

    Default

    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).

Similar Threads

  1. Query in database
    By anki1234 in forum JDBC
    Replies: 3
    Last Post: 08-20-2008, 05:45 PM
  2. Read a Public Key from a USP stick
    By shauom in forum New To Java
    Replies: 2
    Last Post: 08-06-2008, 07:57 AM
  3. How to query data from database using SSL
    By mano in forum New To Java
    Replies: 0
    Last Post: 08-02-2007, 05:30 PM
  4. Displaying different columns with database query
    By jennybgh in forum JavaServer Pages (JSP) and JSTL
    Replies: 0
    Last Post: 08-02-2007, 05:04 PM
  5. Replies: 3
    Last Post: 07-30-2007, 07:11 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •