Solved

Simple Java Program to Acess books database.

Posted on 2009-05-05
12
399 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

0
Comment
Question by:durrr
  • 8
  • 4
12 Comments
 
LVL 12

Expert Comment

by:Gibu George
Comment Utility
This code looks ok.

>Heres the code I have but its for DIFFERENT TABLES
What does this mean?
0
 
LVL 12

Expert Comment

by:Gibu George
Comment Utility
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
0
 

Author Comment

by:durrr
Comment Utility
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
0
 
LVL 12

Expert Comment

by:Gibu George
Comment Utility
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.
0
 
LVL 12

Expert Comment

by:Gibu George
Comment Utility
if you want all the columns of the authors table
change the query to select * from authors,  this also will work
0
 

Author Comment

by:durrr
Comment Utility
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.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 12

Expert Comment

by:Gibu George
Comment Utility
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

0
 

Author Comment

by:durrr
Comment Utility
Got my question answered. Thanks Gibu
0
 
LVL 12

Accepted Solution

by:
Gibu George earned 125 total points
Comment Utility
Hi durr,

This is not the way to accept the solution, need to click accept the solution I have given closing this way

0
 
LVL 12

Expert Comment

by:Gibu George
Comment Utility
also deleting will not help others who are searching related solutions.
0
 
LVL 12

Expert Comment

by:Gibu George
Comment Utility
So please cancel the delete request and accept my comment as the solution
0
 

Author Comment

by:durrr
Comment Utility
I did, thanks for letting me know.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now