Solved

Simple Java Program to Acess books database.

Posted on 2009-05-05
12
400 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
ID: 24306556
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
ID: 24306575
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
ID: 24306983
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
ID: 24307045
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
ID: 24307055
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
ID: 24307174
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 12

Expert Comment

by:Gibu George
ID: 24307387
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
ID: 24307655
Got my question answered. Thanks Gibu
0
 
LVL 12

Accepted Solution

by:
Gibu George earned 125 total points
ID: 24308521
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
ID: 24308530
also deleting will not help others who are searching related solutions.
0
 
LVL 12

Expert Comment

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

Author Comment

by:durrr
ID: 24309854
I did, thanks for letting me know.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql query Help 12 53
Help with SQL field formatting 3 20
how to add new optional parameter to JSP 1 29
t-sql split string into multiple rows 7 39
Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
The viewer will learn how to implement Singleton Design Pattern in Java.

895 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

16 Experts available now in Live!

Get 1:1 Help Now