10 Replies Latest reply: May 31, 2007 12:17 AM by 807606 RSS

    Executing SQL query

    807606
      Here is what I've got:
      try
              {
                  // Load the JDBC driver
                  String driverName = "com.mysql.jdbc.Driver"; // MySQL MM JDBC driver
                  Class.forName (driverName);
                  
                  // Create a connection to the database
                  serverName = "fedex.plastikracing.ne";
                  mydatabase = "web5_db1";
                  url = "jdbc:mysql://" + serverName +  "/" + mydatabase; // a JDBC url
                  username = "web5_u1";
                  password = "password";
                  connection = DriverManager.getConnection (url, username, password);
                  
                  // Create the user account
                  Statement stmt = connection.createStatement ();
                  ResultSet rs;
                  
                  rs = stmt.executeQuery ("INSERT INTO users (name, email, password, work, pos1, sort1, pos2, sort2) VALUES (Tristan Lee, 'tristan@plastikracing.net', 'password', 1, 2, 2, 0, 0)");
              }
              catch (ClassNotFoundException e)
              {
                  // Could not find the database driver
                  JOptionPane.showMessageDialog (CreateAccount.this,"Could not find the database driver.", "Connection Error",
                          JOptionPane.ERROR_MESSAGE);
              }
              catch (SQLException e)
              {
                  // Could not connect to the database
                  JOptionPane.showMessageDialog (CreateAccount.this,"Could not connect to the database: " + serverName + ".", "Connection Error",
                          JOptionPane.ERROR_MESSAGE);
              }
      Everytime it goes to connect, a ClassNotFoundException is thrown and my JOptionPane pops up. In the Runtime window of NetBeans, the MySQL driver is shown (I added it) and it says the driver name is "com.mysql.jdbc.Driver". Why would I be getting this exception? Using NetBeans' database manager, I attempt to connect to my database and it works just fine.
        • 1. Re: Executing SQL query
          807606
          The MySQL driver is not in your runtime classpath. So it cannot be loaded, because the class loader cannot find it.
          • 2. Re: Executing SQL query
            807606
            So would I put the mysql-connector-java-5.0.6-bin.jar file in my /build/classes/ path since that is where my other items such as images are?
            • 3. Re: Executing SQL query
              807606
              So would I put the
              mysql-connector-java-5.0.6-bin.jar file in my
              /build/classes/ path since that is where my
              other items such as images are?
              No I don't think it would go in any place named build...

              How are you running this program? From a jar?
              • 4. Re: Executing SQL query
                807606
                Right now I'm just running the project via NetBeans. If I wanted to though, I could run it using [tristan@tristan ~]$ jdk1.6.0/bin/java -jar "/home/tristan/Java Programs/FedExEvaluations/dist/FedExEvaluations.jar"
                • 5. Re: Executing SQL query
                  807606
                  Well, I had to add the JAR file to the project by adding it as a library. The driver error is now gone. Now it just won't connect. I'll try more later on though.
                  • 6. Re: Executing SQL query
                    807606
                    Well, I had to add the JAR file to the project by
                    adding it as a library. The driver error is now gone.
                    Now it just won't connect. I'll try more later on
                    though.
                    A tip. Either print the stack trace of the exception while you are debugging and/or add the actual message to your message box.

                    Something like appending e.getMessage would be helpful.
                    • 7. Re: Executing SQL query
                      807606
                      Thank you for the tip. It actually led me in the direction of my error.

                      Now I'm getting the "Can not issue data manipulation statements with executequery()" error. From what I've read, it;s because I'm using executeQuery() for an INSERT statement when executeQuery should only be used for a SELECT statement. I tried just execute() and the required type is boolean. How does that work? Wouldn't I need the type to be a string since I've got a statement to execute?
                                  // Create the user account
                                  Statement stmt = connection.createStatement ();
                                  ResultSet rs;
                                  
                                  rs = stmt.executeQuery ("INSERT INTO users (name, email, password, work, pos1, sort1, pos2, sort2) VALUES ('Tristan Lee', 'tristan@plastikracing.net', 'skittles1', 1, 2, 2, 0, 0)");
                      • 8. Re: Executing SQL query
                        807606
                        you mean the return type? boolean is good for that.

                        is there an executeScalar or executeNonQuery? something like that?

                        the return type from an 'insert' should really be an int (number of rows affected) but yeah.
                        • 9. Re: Executing SQL query
                          807606
                          I guess what I needed was this:
                          // Create the user account
                                      Statement stmt = connection.createStatement ();
                                      String sql = "INSERT INTO users (name, email, password, work, pos1, sort1, pos2, sort2) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
                                      PreparedStatement pstmt = connection.prepareStatement (sql);
                                      
                                      // Set the values
                                      pstmt.setString (1, t_name.getText ()); //get the name
                                      pstmt.setString (2, t_email.getText ());
                                      ...
                                      pstmt.executeUpdate ();
                          I think I've got it now. Thank you.

                          Actually, when I am doing is creating an account by having the user enter information and when clicking the button, the values entered will be compiled into a SQL statement that will be executed to create the user account. Would it be good practice to create the account and to ensure that it was created completely do a SELECT statement to pull the information from the database and compare it to what they entered. That way I know for certain that it executed successfully?
                          • 10. Re: Executing SQL query
                            807606
                            I guess what I needed was this:
                            // Create the user account
                            Statement stmt =
                            connection.createStatement ();
                            String sql = "INSERT INTO users (name,
                            email, password, work, pos1, sort1, pos2, sort2)
                            VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
                            PreparedStatement pstmt =
                            connection.prepareStatement (sql);
                            
                            // Set the values
                            pstmt.setString (1, t_name.getText ());
                            //get the name
                            pstmt.setString (2, t_email.getText ());
                            ...
                            pstmt.executeUpdate ();
                            've got it now. Thank you.

                            Actually, when I am doing is creating an account by
                            having the user enter information and when clicking
                            the button, the values entered will be compiled into
                            a SQL statement that will be executed to create the
                            user account. Would it be good practice to create the
                            account and to ensure that it was created completely
                            do a SELECT statement to pull the information from
                            the database and compare it to what they entered.
                            That way I know for certain that it executed
                            successfully?
                            not really no.

                            if the statement succeeds it's successful. just catch an error.