6 Replies Latest reply: Apr 25, 2014 9:05 AM by 896976 RSS

    jdbc connectivity with mysql database

    896976

      I created a database in mysql, but I have problems communicating with the DB in java 

       

      here is the error

      java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/books

      at java.sql.DriverManager.getConnection(DriverManager.java:604)

      at java.sql.DriverManager.getConnection(DriverManager.java:221)

      at displayauthors.DisplayAuthors.main(DisplayAuthors.java:30)

      java.lang.NullPointerException

      at displayauthors.DisplayAuthors.main(DisplayAuthors.java:66)

      BUILD SUCCESSFUL (total time: 1 second)

      ---

      I edited the system variables in windowds

      CLASSPATH

      .;C:\Program Files (x86)\Java\jre7\lib\ext\QTJava.zip

      CLASSPATH1 (notice the 1 at the end, I don't want to override the previous one

      .,C:\Program Files (x86)\MySQL\Connector J 5.1.30\mysql-connector-java-5.1.30-bin.jar

      ---

      The Hostname: localhost   Port: 3306

      Username: root

      password:   Tpswsd77? (changed for thios post)

      Stored Connection: Local instance MySQL56

      connection Method: Standard(TCP/IP)

       

      HERE IS THE CODE 

      public class DisplayAuthors {

         

      // database URL                             

         static final String DATABASE_URL = "jdbc:mysql://localhost:3306/books";

        

         // launch the application

         public static void main( String args[] )

         {

            Connection connection = null; // manages connection

            Statement statement = null; // query statement

            ResultSet resultSet = null; // manages results

         

            // connect to database books and query database

            try

            {

              // establish connection to database                             

               connection = DriverManager.getConnection(

                 // DATABASE_URL, "deitel", "deitel" );

                       //<editor-fold defaultstate="collapsed" desc="comment">

                       DATABASE_URL, "root", "Tpswsd77?" );

               //</editor-fold>

                    // create Statement for querying database

               statement = connection.createStatement();

              

               // query database                                       

               resultSet = statement.executeQuery(           

                  "SELECT authorID, firstName, lastName FROM authors" );

              

               // process query results

               ResultSetMetaData metaData = resultSet.getMetaData();

               int numberOfColumns = metaData.getColumnCount();    

               System.out.println( "Authors Table of Books Database:\n" );

              

               for ( int i = 1; i <= numberOfColumns; i++ )

                  System.out.printf( "%-8s\t", metaData.getColumnName( i ) );

               System.out.println();

              

               while ( resultSet.next() )

               {

                  for ( int i = 1; i <= numberOfColumns; i++ )

                     System.out.printf( "%-8s\t", resultSet.getObject( i ) );

                  System.out.println();

               } // end while

            }  // end try

            catch ( SQLException sqlException )                               

            {                                                                 

               sqlException.printStackTrace();

            } // end catch                                                    

            finally // ensure resultSet, statement and connection are closed

            {                                                            

               try                                                       

               {                                                         

                  resultSet.close();                                     

                  statement.close();                                     

                  connection.close();                                    

               } // end try                                              

               catch ( Exception exception )                             

               {                                                         

                  exception.printStackTrace();                           

               } // end catch                                            

            } // end finally                                             

         } // end main

      } // end class DisplayAuthors

        • 1. Re: jdbc connectivity with mysql database
          gimbal2

          CLASSPATH

          .;C:\Program Files (x86)\Java\jre7\lib\ext\QTJava.zip

          CLASSPATH1 (notice the 1 at the end, I don't want to override the previous one

          .,C:\Program Files (x86)\MySQL\Connector J 5.1.30\mysql-connector-java-5.1.30-bin.jar

           

          Java is not going to look at your CLASSPATH1 variable which is the correct one that you need. As you can see it points to your JDBC driver which the application is reporting cannot be found.

           

          Your CLASSPATH variable as it is now is likely bogus, I am 99.999999% certain that you do not need quicktime. Remove the CLASSPATH variable and rename CLASSPATH1 to CLASSPATH and be done with it. You have backed up your original values in this thread now anyway.

          • 2. Re: jdbc connectivity with mysql database
            896976

            I did this

            Remove the CLASSPATH variable and rename CLASSPATH1 to CLASSPATH

            did not work either

            same error

            run:

            java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/books

            at java.sql.DriverManager.getConnection(DriverManager.java:604)

            at java.sql.DriverManager.getConnection(DriverManager.java:221)

            at displayauthors.DisplayAuthors.main(DisplayAuthors.java:30)

            java.lang.NullPointerException

            at displayauthors.DisplayAuthors.main(DisplayAuthors.java:64)

            BUILD SUCCESSFUL (total time: 0 seconds)

            • 3. Re: jdbc connectivity with mysql database
              rp0428

              I edited the system variables in windowds

              CLASSPATH

              .;C:\Program Files (x86)\Java\jre7\lib\ext\QTJava.zip

              CLASSPATH1 (notice the 1 at the end, I don't want to override the previous one

              .,C:\Program Files (x86)\MySQL\Connector J 5.1.30\mysql-connector-java-5.1.30-bin.jar

              A few facts:

               

              1. You NEED to have the JDBC driver jar on the Java classpath

              2. You don't NEED to have the jar on the Windows classpath

              3. You don't NEED to 'override' the Windows classpath - you can just append the JDBC jar to it

               

              Just use #3 above - edit the Windows CLASSPATH environment variable and add the JDBC jar to it. At it at the start and follow it with a semicolon. Or at it at the end AFTER adding a semicolon.

               

              Or specify the CLASSPATH on the command line when you launch Java. Use the '-cp' or '-classpath <class . . >' options. Just type 'java' at the command line to see the list of parameters that can be used.

              • 4. Re: jdbc connectivity with mysql database
                896976

                Just use #3 above - edit the Windows CLASSPATH environment variable and add the JDBC jar to it. At it at the start and follow it with a semicolon. Or at it at the end AFTER adding a semicolon.

                 

                CLASSPATH

                .;C:\Program Files (x86)\MySQL\Connector J 5.1.30\mysql-connector-java-5.1.30-bin.jar;.;C:\Program Files (x86)\Java\jre7\lib\ext\QTJava.zip

                 

                same error

                run:

                java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/books

                at java.sql.DriverManager.getConnection(DriverManager.java:604)

                at java.sql.DriverManager.getConnection(DriverManager.java:243)

                at displayauthors.DisplayAuthors.main(DisplayAuthors.java:30)

                java.lang.NullPointerException

                at displayauthors.DisplayAuthors.main(DisplayAuthors.java:64)

                BUILD SUCCESSFUL (total time: 0 seconds)

                • 5. Re: jdbc connectivity with mysql database
                  jwenting

                  Seeing as you appear to be running from netbeans, you'd best add the jar file to the project libraries...

                   

                  And NO, you should NEVER put anything in your system classpath. It's extremely bad practice, leads to the Java equivalent of DLL Hell, and of course the moment you distribute the application to any other machine it's going to fail again.

                  Add it to the application specific classpath instead.

                   

                  And avoid putting anything on the classpath that has spaces in the name, it may no longer cause problems but don't count on it working.

                  • 6. Re: jdbc connectivity with mysql database
                    896976

                    Got it that was the problem! Thank you SO much!!