• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 698
  • Last Modified:

JAVA JDBC

I am having problems connecting to the database with this java program.  I do not know how to connect to the localhost which is on my computer.  Can you help me get connected to the JDBC with this program.  The program compiles but I get a message saying that I not connected to the Driver.  If possible can you provide me with some details as to connecting to the local host.  Also, this is my CLASSPATH .;C:\mysql-connector-java-5.1.18\mysql-connector-java-5.1.18-bin.jar  
is it right or am I completely off the mark?
Books.sql
DisplayAuthors.java
0
assaultkitty
Asked:
assaultkitty
  • 10
  • 3
1 Solution
 
for_yanCommented:

this is an example of working mySQL connection:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class TestMySql {

     public static void main(String[] args) {
  System.out.println("MySQL Connect Example.");
  Connection conn = null;
  String url = "jdbc:mysql://localhost:3306/";
  String dbName = "mysql";
  String driver = "com.mysql.jdbc.Driver";
  String userName = "user";
  String password = "password";
  try {
  Class.forName(driver).newInstance();
  conn = DriverManager.getConnection(url+dbName,userName,password);
      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery("select * from java_mysql.test_table");
      while(rs.next()){
          String s = rs.getString("a");
          float f = rs.getFloat("b");
          System.out.println("a: " + s + " b: " + f);
       
      }


  System.out.println("Connected to the database");
  conn.close();
  System.out.println("Disconnected from database");
  } catch (Exception e) {
  e.printStackTrace();
  }
  }

    
}

Open in new window

0
 
for_yanCommented:
You may need to ad the port
jdbc:mysql://localhost:3306/books

3306 is default, unless they changed it on installation 3306 should work
0
 
for_yanCommented:
try this command:
String driver = "com.mysql.jdbc.Driver";

Class.forName(driver).newInstance();
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
for_yanCommented:
try this and post what is the error

// Fig. 28.23: DisplayAuthors.java
// Displaying the contents of the authors table.
import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

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  
         String driver = "com.mysql.jdbc.Driver";

         Class.forName(driver).newInstance();

         connection = DriverManager.getConnection( 
			 DATABASE_URL, "deitel", "deitel" );

         // 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



/**************************************************************************
 * (C) Copyright 1992-2010 by Deitel & Associates, Inc. and               *
 * Pearson Education, Inc. All Rights Reserved.                           *
 *                                                                        *
 * DISCLAIMER: The authors and publisher of this book have used their     *
 * best efforts in preparing the book. These efforts include the          *
 * development, research, and testing of the theories and programs        *
 * to determine their effectiveness. The authors and publisher make       *
 * no warranty of any kind, expressed or implied, with regard to these    *
 * programs or to the documentation contained in these books. The authors *
 * and publisher shall not be liable in any event for incidental or       *
 * consequential damages in connection with, or arising out of, the       *
 * furnishing, performance, or use of these programs.                     *
 *************************************************************************/

 

Open in new window

0
 
assaultkittyAuthor Commented:
This is the error message! now!
 

----jGRASP exec: javac -g DisplayAuthors.java

DisplayAuthors.java:28: unreported exception java.lang.ClassNotFoundException; must be caught or declared to be thrown
         Class.forName(driver).newInstance();
                      ^
DisplayAuthors.java:28: unreported exception java.lang.InstantiationException; must be caught or declared to be thrown
         Class.forName(driver).newInstance();
                                          ^
2 errors

 ----jGRASP wedge2: exit code for process is 1.
 ----jGRASP: operation complete.
0
 
for_yanCommented:
Are you sure that you have the driver in the CLASSPATH ?
In jGRASP mysql-connector-java-5.1.18-bin.jar    should probably be added
to dependencies
0
 
for_yanCommented:
no , actually it is compiler error - you should put it inside try/catch loop
0
 
for_yanCommented:
change it this way - catch any Exception, not just SQLException

// Fig. 28.23: DisplayAuthors.java
// Displaying the contents of the authors table.
import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

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  
         String driver = "com.mysql.jdbc.Driver";

         Class.forName(driver).newInstance();

         connection = DriverManager.getConnection( 
			 DATABASE_URL, "deitel", "deitel" );

         // 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 ( Exception ex1 )                                
      {                                                                  
         ex1.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



/**************************************************************************
 * (C) Copyright 1992-2010 by Deitel & Associates, Inc. and               *
 * Pearson Education, Inc. All Rights Reserved.                           *
 *                                                                        *
 * DISCLAIMER: The authors and publisher of this book have used their     *
 * best efforts in preparing the book. These efforts include the          *
 * development, research, and testing of the theories and programs        *
 * to determine their effectiveness. The authors and publisher make       *
 * no warranty of any kind, expressed or implied, with regard to these    *
 * programs or to the documentation contained in these books. The authors *
 * and publisher shall not be liable in any event for incidental or       *
 * consequential damages in connection with, or arising out of, the       *
 * furnishing, performance, or use of these programs.                     *
 *************************************************************************/

 

Open in new window

0
 
assaultkittyAuthor Commented:
This is my classpath .;C:\mysql-connector-java-5.1.18\mysql-connector-java-5.1.18-bin.jar

0
 
for_yanCommented:
no, look at my later post - this error was not about classpath - it was compiler arror about not catching possible ClassNot Found exception
- see my previous post
0
 
for_yanCommented:
In case of classpath problems Class.forName() will throw runtime exception; this was just compiler - change as I posted
0
 
assaultkittyAuthor Commented:
I the correct ouput


 ----jGRASP exec: java DisplayAuthors

Authors Table of Books Database:

AuthorID      FirstName      LastName      
1             Harvey        Deitel        
2             Paul          Deitel        
3             Andrew        Goldberg      
4             David         Choffnes      

 ----jGRASP: operation complete.
 Can you explain what you actually did? You are a genius!!!!
0
 
for_yanCommented:
Oh, I just took template on how to connect to MySQL (see I posted it in the first posting)
and modified your code accodring to it.
You actually had almost everything right, we just added the default port for MySQL 3306
and also added
Class.forName("com.mysql.jdbc.Driver");
This is necessary for DriverManager class which looks for suitable Driver class among those available
when it executes its getConnection method.

This is a standard pattern to establish JDBC connection with any database,
only driver name and format of URL string ("jdbc:mysql://localhost:3306/books" in this case)
will be different.



0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now