Learn Fundamentals of Java Programming “Database connection from Java in Java Programming ” Lesson 29

Database connection from Java

We are now ready to write a Java program to connect to a database and execute a SQL query on the database. In our MySQL database, we have already created a database called bookstore and a table called book within it. The columns of this table are (title, author, publisher, genre, and price). We have also inserted 5 rows into the table (Figure 1.11j).

We will now learn how to retrieve data from the MySQL table book in the bookstore database from a Java program.
All the classes that we need are in the java.sql package. So we import the required classes as in Figure 1.11k.

Firstly, to establish a database connection to the MySQL Server, we invoke the getconnection() method of the DriverManager class. This method needs three parameters – URL of the database, username, password to connect to the database.

Each database driver has a different syntax for the URL. The MySQl URL has a hostname, the port, and the database name. In our program we construct a String with hostname as localhost, port number as 3306, and the database name as bookstore.

String dbURL = “jdbc:mysql://localhost:3306/bookstore”;

We also assign the username and password, this has to be the same username and password that is used for starting the MySQL Server.

String username =”root”;
String password = “password”;

Next, we invoke the getconnection() method using the URL, username, and password:

Connection dbCon = DriverManager.getConnection(dbURL, username, password);

Next, we use the Connection object returned by the getconnection() method and invoke the createStatement() method. This method returns a Statement object for sending SQL statements to the database.

Statement stmt = dbCon.createStatement();

Next, we invoke the executeQuery() method of the Statement object to execute an SQL query. This method returns a single ResultSet object. The ResultSet is a table of data returned by a specific SQL statement.

String query =”select * from book”;
ResultSet rs = stmt.executeQuery(query);

Finally, we use the next() method of the ResultSet object to iterate through all the rows of data returned by the query. When there are no more rows left, the next() method will return false. Since we know there are 5 columns in the book table, we use a for loop and the getString() method of the ResultSet object to print all the five columns.

while(rs.next()){
for (int i = 1; <=5; i++) {
System.out.print(rs.getString(i));
System.out.print(” | “);
}
System.out.println( );
}

All the statements described above have to be put in a try catch block to catch Exceptions (of the Exception type SQLException) that can occur while connecting or fetching data from the database.

Figures 1.11k and 1.11l show the complete listing of the MySQL Demo program and its output respectively.

Once you complete running your database program, you can disconnect the MySQL Server from NetBeans. Under the Databases node, right click the MySQL Server at localhost:3306 [root] and select Disconnect.