We help IT Professionals succeed at work.

Simple Java Program to Acess books database.

durrr
durrr asked
on
435 Views
Last Modified: 2013-11-23
Write a simple java program to access the books database and display all the contents in the authors table.(Install MySql and the MySql-JDBC connector)
Heres the code I have but its for DIFFERENT TABLES. can you please correct the code for  program to access the books database and display all the contents in the authors table
1	// Fig. 25.25: DisplayAuthors.java
	2	// Displaying the contents of the authors table.
	3	import java.sql.Connection;
	4	import java.sql.Statement;
	5	import java.sql.DriverManager;
	6	import java.sql.ResultSet;
	7	import java.sql.ResultSetMetaData;
	8	import java.sql.SQLException;
	9	
	10	public class DisplayAuthors 
	11	{
	12	   // JDBC driver name and database URL                              
	13	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";        
	14	   static final String DATABASE_URL = "jdbc:mysql://localhost/books";
	15	   
	16	   // launch the application
	17	   public static void main( String args[] )
	18	   {
	19	      Connection connection = null; // manages connection
	20	      Statement statement = null; // query statement
	21	    
	22	      // connect to database books and query database
	23	      try 
	24	      {
	25	         Class.forName( JDBC_DRIVER ); // load database driver class
	26	
	27	         // establish connection to database                              
	28	         connection =                                                     
	29	            DriverManager.getConnection( DATABASE_URL, "jhtp6", "jhtp6" );
	30	
	31	         // create Statement for querying database
	32	         statement = connection.createStatement();
	33	         
	34	         // query database                                        
	35	         ResultSet resultSet = statement.executeQuery(            
	36	            "SELECT authorID, firstName, lastName FROM authors" );
	37	         
	38	         // process query results
	39	         ResultSetMetaData metaData = resultSet.getMetaData();
	40	         int numberOfColumns = metaData.getColumnCount();     
	41	         System.out.println( "Authors Table of Books Database:" );
	42	         
	43	         for ( int i = 1; i <= numberOfColumns; i++ )
	44	            System.out.printf( "%-8s\t", metaData.getColumnName( i ) );
	45	         System.out.println();
	46	         
	47	         while ( resultSet.next() ) 
	48	         {
	49	            for ( int i = 1; i <= numberOfColumns; i++ )
	50	               System.out.printf( "%-8s\t", resultSet.getObject( i ) );
	51	            System.out.println();
	52	         } // end while
	53	      }  // end try
	54	      catch ( SQLException sqlException )                                
	55	      {                                                                  
	56	         sqlException.printStackTrace();
	57	         System.exit( 1 );                                               
	58	      } // end catch

Open in new window

Comment
Watch Question

Gibu GeorgeChief Technology Officer

Commented:
This code looks ok.

>Heres the code I have but its for DIFFERENT TABLES
What does this mean?
Gibu GeorgeChief Technology Officer

Commented:
One thing once the statement and rs are used close it. do that in the finally block and similarly once you are done with the connection object close that also

Author

Commented:
I just got the Code from Deitel and Deitel Text Book but I was NOT sure if this code is right one for QUESTION i have posed. Can you please recheck if the answer to my question is the code I have incorporated. I thought  that the code is attached is for different query with different connection. Can you please re-check it thoroughly and tell me if this is the right code for my question. Thanks
Gibu GeorgeChief Technology Officer

Commented:
The code which you have posted connects to a mysql db, gets the authorid, firstname,lastname from the authors table (all the rows). and dynamically goes through the result set and prints the authorid, firstname and last name row by row.

If you are having different tables, and connection details you just need to change the query. and the connection details like the url , user name, password.
Gibu GeorgeChief Technology Officer

Commented:
if you want all the columns of the authors table
change the query to select * from authors,  this also will work

Author

Commented:
Can you please Change it and post the updated Code, I dont wanna mess it up. Please bear with my ignorance, I am very new to all this programming. Please make the changes and attach the file. Thank you Gibu.
Gibu GeorgeChief Technology Officer

Commented:
please find the attached code snippet, i hope the tables name is authors
// 25.25: 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 {
	// JDBC driver name and database URL
	static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
	static final String DATABASE_URL = "jdbc:mysql://localhost/books";
 
	// launch the application
	public static void main(String args[]) {
		Connection connection = null; // manages connection
		Statement statement = null; // query statement
 
		// connect to database books and query database
		try {
			Class.forName(JDBC_DRIVER); // load database driver class
 
			// establish connection to database
			connection = DriverManager.getConnection(DATABASE_URL, "jhtp6",
					"jhtp6");
 
			// create Statement for querying database
			statement = connection.createStatement();
 
			// query database
			ResultSet resultSet = statement
					.executeQuery("SELECT * FROM authors");
 
			// process query results
			ResultSetMetaData metaData = resultSet.getMetaData();
			int numberOfColumns = metaData.getColumnCount();
			System.out.println("Authors Table of Books Database:");
 
			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();
			System.exit(1);
		} // end catch
		catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(connection!=null){
				try {
					connection.close();
				} catch (SQLException e) {
					connection=null;
				}
			}
		}
	}
}

Open in new window

Author

Commented:
Got my question answered. Thanks Gibu
Chief Technology Officer
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Gibu GeorgeChief Technology Officer

Commented:
also deleting will not help others who are searching related solutions.
Gibu GeorgeChief Technology Officer

Commented:
So please cancel the delete request and accept my comment as the solution

Author

Commented:
I did, thanks for letting me know.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.