Database Porgramming : Creating a simple phone book using Java & a database ( HSQLDB )
[info] You to have Java and Eclipse Configured and Installed. If not please install the JDK and download Eclipse[/info] In one of the last tutorial, we have created a simple phonebook system using a text file and Java Programming. For more details, click here.
In this tutorial, we will be creating the same project but with the integration of a simple database management system called ( HSQLDB ). The program will be a textual interactive application without a graphical interface for the sake of simplicity ! We will be having three main sections for writing the software:
- Interaction with the user.
- Database Initialisation.
- Phonebook operations ( Add, delete, search …)
1. Interaction with the user
Before, we begin! let’s write a simple skeleton for the program to interact with the user !
1) Create a project using Eclipse ( or other IDE ), let’s call it : MyPhoneBook
2) Create a Class and call it : PhoneBook
[info] Click here for how to create a project using Eclipse[/info]
import java.io.*;
import java.sql.*;
import java.util.*;
public class PhoneBook {
public static String help_msg= "Press: H Help - A Add contact - S Search - Q Exit :";
public static void main(String[] args) {
System.out.println("\n\n***** Welcome to MyPhone Book *****\n\n");
Scanner s=new Scanner(System.in);
for(;;){
System.out.print("[Main Menu] "+help_msg+"\n:");
String command=s.nextLine().trim();
if (command.equalsIgnoreCase("H")){
System.out.println(help_msg);
}else if (command.equalsIgnoreCase("A")){
System.out.print("Type in contact details in the format: name,lastname,phone\n:");
}else if (command.equalsIgnoreCase("S")){
System.out.print("Type in the name you are searching for :\n:");
}else if (command.equalsIgnoreCase("Q")){
System.out.println("Good Bye User....");
System.exit(0);
}else{
System.out.print("Unknown command ! Try again \n:");
}
}
}
}
The above is a simple skeleton for interacting with the user asking for command to be typed.
2. Database Initialisation
1) Let’s modify the code above to include a few lines to connect to the DBMS:
import java.io.*;
import java.sql.*;
import java.util.*;
public class PhoneBook {
public static String help_msg= "Press: H Help - A Add contact - S Search - Q Exit :";
public static void main(String[] args) {
try{
Connection con=null;
Class.forName("org.hsqldb.jdbc.JDBCDriver");
con=DriverManager.getConnection("jdbc:hsqldb:mydb2","SA","");
con.createStatement().executeUpdate("SET DATABASE SQL SYNTAX MYS TRUE");
con.createStatement().executeUpdate("create table IF NOT EXISTS contacts ("+
"id int auto_increment primary key," +
"name varchar(45)," +
"lname varchar(45),"+
"phone varchar(45))");
System.out.println("\n\n***** Welcome to MyPhone Book *****\n\n");
Scanner s=new Scanner(System.in);
for(;;){
System.out.print("[Main Menu] "+help_msg+"\n:");
String command=s.nextLine().trim();
if (command.equalsIgnoreCase("H")){
System.out.println(help_msg);
}else if (command.equalsIgnoreCase("A")){
System.out.print("Type in contact details in the format: name,lastname,phone\n:");
}else if (command.equalsIgnoreCase("S")){
System.out.print("Type in the name you are searching for :\n:");
}else if (command.equalsIgnoreCase("Q")){
System.out.println("Good Bye User....");
System.exit(0);
}else{
System.out.print("Unknown command ! Try again \n:");
}
}
}catch(Exception e){
e.printStackTrace();
}
}
}
Explanation:
- Connection con=null; Creates an null conncetion object
- Class.forName(“org.hsqldb.jdbc.JDBCDriver”); : loads the JDBC driver for the DBMS server into the runtime.
- con=DriverManager.getConnection(“jdbc:hsqldb:mydb2″,”SA”,””); connects to the DBMS using username SA, empty password. The name of the database is mydb2
- con.createStatement().executeUpdate(“SET DATABASE SQL SYNTAX MYS TRUE”); Executes an SQL query : To set the syntax to MySQL
- con.createStatement().executeUpdate(“…. : Executes an SQL query : Creating a Table…
[info]We use con.createStatement().executeUpdate(“…“); for SQL queries that do not return results like : insert, update, delete, alter…[/info]
3. Phonebook Operations
Let’s try this new code :
import java.io.*;
import java.sql.*;
import java.util.*;
public class PhoneBook {
public static String help_msg= "Press: H Help - A Add contact - S Search - Q Exit :";
public static void main(String[] args) {
Connection con=null;
try{
Class.forName("org.hsqldb.jdbc.JDBCDriver");
con=DriverManager.getConnection("jdbc:hsqldb:mydb7","SA","");
con.createStatement().executeUpdate("SET DATABASE SQL SYNTAX MYS TRUE");
con.createStatement().executeUpdate("create table IF NOT EXISTS contacts ("+
"id int auto_increment primary key," +
"name varchar(45)," +
"lname varchar(45),"+
"phone varchar(45))");
System.out.println("\n\n***** Welcome to MyPhone Book *****\n\n");
Scanner s=new Scanner(System.in);
String command="";
for(;;){
System.out.print("[Main Menu] "+help_msg+"\n:");
command=s.next().trim();
if (command.trim().isEmpty()){continue;}
if (command.equalsIgnoreCase("H")){
System.out.println(help_msg);
/*
*
*Adding Contacts
*
*/
}else if (command.equalsIgnoreCase("A")){
System.out.print("Type in contact details in the format: name,lastname,phone\n:");
for(;;){
String data=s.next().trim();
String [] temp=data.split(",");
if (temp.length!=3){
System.out.println("Error, the insertion format should be in the format: firstname,lastname,phone :");
continue;
}
con.createStatement().executeUpdate("insert into contacts (name,lname,phone) values" +
"('"+temp[0]+"','"+temp[1]+"','"+temp[2]+"')");
break;
}
/*
*
*Searching for Contacts
*
*/
}else if (command.equalsIgnoreCase("S")){
System.out.print("Type in the name you are searching for :\n:");
String data=s.next().trim();
String q="select * from contacts where name like '%"+data+"%' or lname like '%"+data+"%'";
PreparedStatement pst=con.prepareStatement(q);
pst.clearParameters();
ResultSet rs=pst.executeQuery();
System.out.println("******** Results *********");
System.out.println("ID\t\tName\t\tLast Name\t\tPhone");
while(rs.next()){
System.out.println(rs.getString(1)+"\t\t"+rs.getString(2)+"\t\t"+rs.getString(3)+"\t\t"+rs.getString(4));
}
/*
*
*Quiting the system
*
*/
}else if (command.equalsIgnoreCase("Q")){
Statement st = con.createStatement();
st.execute("SHUTDOWN");
con.close();
System.out.println("Good Bye User....");
System.exit(0);
}else{
System.out.print("Unknown command ! Try again: ["+command+"]\n");
}
}
}catch(Exception e){
e.printStackTrace();
}
}
}
Run your Application now:
you will get the following error :
To fix it, click Run -> Run Configurations ..
Make sure of selecting the main class name: if it does not show, double click on Java Application
Click on : ClassPath
Click on the name of your project : PhoneDirectory
Click on Add External JARS, and look for your your HSQLDB.jar file
[info]In case you don’t have the HSGLDB,You can download the DBMS : HSQLDB from here [/info]
Click Apply, — > Run
in case you get the following error regarding Failure of Lock Acquisition.
Click on ht (X X) symbol shown below to fix it, then run again.
About Author
by Imed Bouchrika
Dr. Imed Bouchrika received his PhD degrees in Electronics and Computer Science from the University of Southampton in United Kingdom in 2008. He has been in the industry as a software developer for over a decade primarily designing and developing interactive web applications. At the same time, he worked as a research fellow at the Image Processing research group at the University of Southampton investigating the potential use of gait as new biometric for forensic and security applications.
Leave a Reply
-
Motivations of why to Learn Databases
-
How to Convert ER Diagram to Relational Database
-
List of Recommended Websites to Learn Databases
-
BaseX : Tutorial for using an XML Native Database Management System
-
xPath Processing for XML documents using Java
-
Making Connection to Oracle Database System with Java
){kind=link}
i have a problem when i try and enter a new name how are u supposed to make a new entry i keep getting the error message