Database programming

Database user interface	The Class Person	The Class Engine
The GUI Interface	Summary	Practice

Exercise

Learning outcomes

On completion of this chapter you will know:

• How to create an ODBC interface to a database
• How to use the Connection, Statement and Resultset objects
• How to process database data using SQL queries
• How to interface a GUI form with the processing

Download pdf version

Introduction

Up to now we have looked at how to store and retrieve data using text files and vectors. Both of those data storage methods, especially the text files, are limited as to the complexity of the data that can be stored. Furthermore the data stored can only be accessed by other Java programmes.

Commercial databases, however, can be used to store and process data themselves and also can be accessed by a variety of other applications. For this reason most organizations store their data in such databases and use programming languages such as Visual Basic and Java if they want to do complex processing on that data.

Here we shall look at how to process data in an Access database. Java itself cannot directly access a database and therefore we need to create an interface between it and the database. Java will then simply talk to the interface and will be completely unaware of the type of database that is at the other end.

Creating an interface to a database

Go to top

The first step in creating an interface is to go to the Control Panel and select Administrative tools. From this we select Data sources (ODBC). Double clicking on it will give us the dialogue box in Figure 17‑1

Figure 17‑1

This box shows us the data sources already available. They are of no use to us and so we need to add our own to the list. To do this we click on the Add button. This gives us the dialogue box in Figure 17‑2.

Figure 17‑2

Here we select the appropriate driver and click on Finish. This, in turn, will give us the dialogue box in Figure 17‑3.

Figure 17‑3

This is where the work is done. We have to supply two data pieces of information: the name of the data source – which will be employees in this case – and the path to the database. In order to supply the latter we click on the button Select. This brings up an Open File dialogue box which allows us the navigate to where the database is stored. Once we have located the database we click on OK. This puts the name and path of the database in the space above the Select button. Once we click on the OK button we will have created an interface called employees to a database called Employees.mdb. that database has a table in it also called employees with fields named ird, name, address and age.

Note: in the above example the names of the interface and the database are the same. This is for consistency purposes only. If we wish the name of the interface can be different to that of the database. Similarly the name of the table in the database can be different from the database name and from the interface name.

The class Person

Go to top

The class Person, whose code is in Listing 17‑1 below is the same class we have used in other examples and is included here only for completion. For this reason no further explanation will be give of this class.

Listing 17‑1

1	package database1;
2	public class Person
3	{
4	private String ird, name, address;
5	private int age;
6	public Person(String i, String n, String a, int ag)
7	{
8	ird = i;
9	name = n;
10	address = a;
11	age = ag;
12	}
13	public String getIrd()
14	{
15	return ird;
16	}
17	public String getName()
18	{
19	return name;
20	}
21	public String getAddress()
22	{
23	return address;
24	}
25	public int getAge()
26	{
27	return age;
28	}
29	}

The class Engine

Go to top

Listing 17‑2

1	package database1;
2	import java.sql.*;
3	public class Engine
4	{
5	private Connection connection;
6	private Statement statement,browseStatement;
7	private ResultSet resultset;
8	String url = "jdbc:odbc:Employees";
9	String selectSql = "select * from employees";
10	public Engine()
11	{
12	try
13	{
14	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
15	connection = DriverManager.getConnection(url);
16	browseStatement= connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
17	resultset = browseStatement.executeQuery(selectSql);
18	}
19	catch(Exception e)
20	{
21	System.out.println(e.toString());
22	}
23	}
24	public ResultSet first()
25	{
26	try
27	{
28	resultset.first();
29	return resultset;
30	}
31	catch(Exception e)
32	{
33	System.out.println(e.toString());
34	return null;
35	}
36	}
37	public ResultSet next()
38	{
39	try
40	{
41	resultset.next();
42	return resultset;
43	}
44	catch(Exception e)
45	{
46	return null;
47	}
48	}
49	public ResultSet last()
50	{
51	try
52	{
53	resultset.last();
54	return resultset;
55	}
56	catch(Exception e)
57	{
58	return null;
59	}
60	}
61	public ResultSet previous()
62	{
63	try
64	{
65	resultset.previous();
66	return resultset;
67	}
68	catch(Exception e)
69	{
70	return null;
71	}
72	}
73	public boolean add(Person p)
74	{
75	try
76	{
77	statement = connection.createStatement();
78	String sql = "Insert into employees (ird, name, address, age) values ('";
79	sql = sql.concat(p.getIrd());
80	sql = sql.concat("','");
81	sql = sql.concat(p.getName());
82	sql = sql.concat("','");
83	sql = sql.concat(p.getAddress());
84	sql = sql.concat("',");
85	sql = sql.concat(Integer.toString(p.getAge()));
86	sql = sql.concat(")");
87	System.out.println(sql);
88	statement.executeUpdate(sql);
89	resultset = browseStatement.executeQuery(selectSql);
90	return true;
91	}
92	catch(Exception e)
93	{
94	System.out.println(e.toString());
95	return false;
96	}
97	}
98	public boolean update(Person p)
99	{
100	try
101	{
102	statement = connection.createStatement();
103	String sql = "Update employees set name = '";
104	sql = sql.concat(p.getName());
105	sql = sql.concat("', Address = '");
106	sql = sql.concat(p.getAddress());
107	sql = sql.concat("', age =");
108	sql = sql.concat(Integer.toString(p.getAge()));
109	sql = sql.concat(" where ird ='");
110	sql = sql.concat(p.getIrd());
111	sql = sql.concat("'");
112	System.out.println(sql);
113	statement.executeUpdate(sql);
114	resultset = browseStatement.executeQuery(selectSql);
115	return true;
116	}
117	catch(Exception e)
118	{
119	System.out.println(e.toString());
120	return false;
121	}
122	}
123	public boolean delete(Person p)
124	{
125	try
126	{
127	statement = connection.createStatement();
128	String sql = "delete from employees where ird = '";
129	sql = sql.concat(p.getIrd());
130	sql = sql.concat("'");
131	System.out.println(sql);
132	statement.executeUpdate(sql);
133	resultset = browseStatement.executeQuery(selectSql);
134	return true;
135	}
136	catch(Exception e)
137	{
138	System.out.println(e.toString());
139	return false;
140	}
141	}
142	}

This class contains code that allows us to

• Open a database through a connection object
• Retrieve data from it
• Browse the data, i.e. move forwards or backwards through the list
• Add extra records to the database
• Modify or delete existing records

All of this is possible through predefined classes in Java. Those are Connection, Statement and Resultset.

The diagram above illustrates the concepts behind those three objects. The connection object forms a bridge between the Java programme and the database interface. If we want data to go over this bridge we cannot tell the connection object itself to do it directly. Instead we create a statement object to carry the data. The statement is analgous to a vehicle that can travel over this bridge. It carries data and/or commands from the programme to the database in order to:

1. add new records as in line 88
2. modify or update existing data as in line 113
3. delete an existing record as in line 132

It can also carry data from the database to the programme as in line 17.

The final object we look at is the Resultset object. This is not used to add records, modify or delete them. It is used only to store data retrieved from the database, and its structure is that of a flat file. Unlike a flat file, whose structure is fixed, the Resultset is dynamic and can accommodate itself to the structure of the data that the statement object returns to it.

Now let us look at the methods for accessing the database and processing its data.

Opening the database in the constructor

In the constructor of the class, which spans lines 10 – 23, opens the database and retrieves its data into a Resultset object so that the contents of that object can be browsed by the user. As well as the classes mentioned above, the constructor uses two String constants declared in the top part of the class – url and selectSql. It also uses the DriverManager class from the package java.sql.

At line 15 a Connection object is created using the getConnection() method from the DriverManager class. At line 16 a Statement object is created using the createStatement() method of the Connection object that was created by the line above. At line 17 a Resultset object is created by the executeQuery() method of the statement object that we created at line 16. This method passes the SQL statement select * from employees – the value of the String constant selectSql into the database via the Connection object. There the SQL statement is executed and the result is passed back to the Java programme, where it is put into a Resultset object and a pointer to that object is stored in the variable resultset.

We shall not be doing any processing on the Resultset object we have created in the constructor other than browse its contents. For this we have the four methods first, last, next and previous. Each of those methods have identical structure and therefore we shall only look at one of them – the method first.

This method spans the lines 24 – 36. Its body forms a try..catch construct. The reason for this is that when browsing a resultset we need to be aware of the following:

• The resultset may be empty
• We may be using the next method on a resultset when we are already on the last record
• We may be using the previous method on a resultset when we are already at the beginning

If any of the above occur an exception is thrown by Java. For this reason all access to the resultset must be inside a try block.

The first action inside this block is to call the first method of the Resultset object. If the resultset is not empty then the method will point to the first record and store the pointer to it in the pointer resultset. This value is then returned at line 29.

If the resultset is empty then actioning first will cause an exception to be thrown and control will pass to line 33 where the cause of the exception is printed on the monitor. Line 34 will return a null value to indicate an unsuccessful attempt to access the resultset.

Adding a new record to the database

The method add, which spans lines 73-97, controls adding a new record to the database. It is passed a pointer to an object of the class Person. This object contains the values that we wish to store in the database. The body of the method is a try..catch construct because the programme is accessing a database whose layout, location and existence is beyond its control.

Java programmes add data to a database by creating an SQL statement and executing that statement within the database itself via a Statement object. Thus in line 77 we create a Statement object in the same way as we created it in the constructor. In lines 78 – 86 we build up an SQL statement by concatenating data and punctuation marks on to a String object. The value of the object is initialized to Insert into employees (ird, name, address, age) values ('. Supposing that the values for ird, name, address and age were “123456”, “John Smith”, “Port Moresby” and 32 then when line 86 had finished execution the value of the String object would be Insert into employees (ird, name, address, age) values ('123456’,’John Smith’,’Port Moresby’,22). This statement is executed in the database at line 88. At line 89 a query is run on the database so that the object resultset is updated with the new record that’s been added. Line 90 returns a value of true to indicate successful completion of the method’s tasks.

Updating an existing record

In order to update the contents of an existing record we use the update method which spans lines 98 – 122.its logic and layout is identical to that for adding a record except that

• It uses an update query instead of an insert query
• The ird number is not updated.

Deleting a record

Again this method is similar to its two predecessors except that it uses a delete query and that it uses the ird number to search for the record to delete.

The GUI interface

Go to top

Most of the code below is concerned with creating the frame shown in Figure 17‑4 below. That frame’s text boxes will be either used to display values in the fields of tables in the database or else used to supply data to the methods of an object of the class Engine that would either add a new record to the database or modify or delete and existing one. For this reason we need to:

declare a pointer to an object of the class Engine somewhere in the programme and create an object of the same class. This is done in line 85.

Declare a pointer to an object of the class Resultset so that data received from the methods first, last, next and previous. This is done in the argument of the method showData().

Listing 17‑3

1	package database1;
2	import java.awt.*;
3	import java.sql.ResultSet;
4	import javax.swing.*;
5	import java.awt.event.ActionEvent;
6	import java.awt.event.ActionListener;
7
8	public class Frame1 extends JFrame {
9	public Frame1() {
10	try {
11	jbInit();
12	} catch (Exception exception) {
13	exception.printStackTrace();
14	}
15	}
16	private void jbInit() throws Exception {
17	getContentPane().setLayout(null);
18	irdField.setBounds(new Rectangle(220, 24, 113, 21));
19	nameField.setBounds(new Rectangle(220, 54, 113, 21));
20	addressField.setBounds(new Rectangle(220, 84, 113, 21));
21	ageField.setBounds(new Rectangle(220, 114, 113, 21));
22	addButton.setBounds(new Rectangle(78, 149, 112, 21));
23	addButton.setText("Add");
24	addButton.addActionListener(new Frame1_addButton_actionAdapter(this));
25	modifyButton.setBounds(new Rectangle(78, 179, 112, 21));
26	modifyButton.setText("Modify");
27	modifyButton.addActionListener(new Frame1_modifyButton_actionAdapter(this));
28	deleteButton.setBounds(new Rectangle(78, 209, 112, 21));
29	deleteButton.setText("Delete");
30	deleteButton.addActionListener(new Frame1_deleteButton_actionAdapter(this));
31	firstButton.setBounds(new Rectangle(220, 149, 112, 21));
32	firstButton.setText("First");
33	firstButton.addActionListener(new Frame1_firstButton_actionAdapter(this));
34	nextButton.setBounds(new Rectangle(220, 179, 112, 21));
35	nextButton.setText("Next");
36	nextButton.addActionListener(new Frame1_nextButton_actionAdapter(this));
37	lastButton.setBounds(new Rectangle(220, 209, 112, 21));
38	lastButton.setText("Last");
39	lastButton.addActionListener(new Frame1_lastButton_actionAdapter(this));
40	previousButton.setBounds(new Rectangle(220, 239, 112, 21));
41	previousButton.setText("Previous");
42	previousButton.addActionListener(new Frame1_previousButton_actionAdapter(this));
43
44	jLabel1.setText("Ird");
45	jLabel1.setBounds(new Rectangle(76, 26, 116, 21));
46	jLabel2.setText("Name");
47	jLabel2.setBounds(new Rectangle(76, 52, 102, 24));
48	jLabel3.setText("Address");
49	jLabel3.setBounds(new Rectangle(75, 85, 116, 24));
50	jLabel4.setText("Age");
51	jLabel4.setBounds(new Rectangle(75, 118, 131, 20));
52	this.getContentPane().add(irdField);
53	this.getContentPane().add(nameField);
54	this.getContentPane().add(addressField);
55	this.getContentPane().add(ageField);
56	this.getContentPane().add(addButton);
57	this.getContentPane().add(modifyButton);
58	this.getContentPane().add(deleteButton);
59	this.getContentPane().add(jButton4);
60	this.getContentPane().add(firstButton);
61	this.getContentPane().add(nextButton);
62	this.getContentPane().add(lastButton);
63	this.getContentPane().add(previousButton);
64	this.getContentPane().add(jLabel1);
65	this.getContentPane().add(jLabel2);
66	this.getContentPane().add(jLabel3);
67	this.getContentPane().add(jLabel4);
68	}
69	JTextField irdField = new JTextField();
70	JTextField nameField = new JTextField();
71	JTextField addressField = new JTextField();
72	JTextField ageField = new JTextField();
73	JButton addButton = new JButton();
74	JButton modifyButton = new JButton();
75	JButton deleteButton = new JButton();
76	JButton jButton4 = new JButton();
77	JButton firstButton = new JButton();
78	JButton nextButton = new JButton();
79	JButton lastButton = new JButton();
80	JButton previousButton = new JButton();
81	JLabel jLabel1 = new JLabel();
82	JLabel jLabel2 = new JLabel();
83	JLabel jLabel3 = new JLabel();
84	JLabel jLabel4 = new JLabel();
85	Engine engine = new Engine();
86	public void addButton_actionPerformed(ActionEvent e) {
87	Person p = new Person(irdField.getText(),nameField.getText(),addressField.getText(),Integer.parseInt(ageField.getText().trim()));
88
89
90	if(engine.add(p))
91	this.setTitle("New record added");
92	else
93	this.setTitle("Could not add a new record");
94	}
95	public void modifyButton_actionPerformed(ActionEvent e) {
96	Person p = new Person(irdField.getText(),nameField.getText(), addressField.getText(),Integer.parseInt(ageField.getText().trim()));
97
98
99	if(engine.update(p))
100	this.setTitle("Record modified");
101	else
102	this.setTitle("Could not modify record");
103	}
104	public void deleteButton_actionPerformed(ActionEvent e) {
105	Person p = newPerson(irdField.getText(),nameField.getText(),addressField.getText(),Integer.parseInt(ageField.getText().trim()));
106
107
108	if(engine.delete(p))
109	this.setTitle("Record deleted");
110	else
111	this.setTitle("Could not delete record");
112	}
113	public void firstButton_actionPerformed(ActionEvent e) {
114	showData(engine.first());
115	}
116	private void showData(ResultSet r)
117	{
118	if(r != null)
119	{
120	try
121	{
122	irdField.setText(r.getString(1));
123	nameField.setText(r.getString(2));
124	addressField.setText(r.getString(3));
125	ageField.setText(r.getString(4));
126	}
127	catch(Exception e){}
128	}
129	}
130	public void nextButton_actionPerformed(ActionEvent e) {
131	showData(engine.next());
132	}
133	public void lastButton_actionPerformed(ActionEvent e) {
134	showData(engine.last());
135	}
136
137	public void previousButton_actionPerformed(ActionEvent e) {
138	showData(engine.previous());
139	}
140	}

Figure 17‑4

In Listing 17.3 all of the code down as far as line 84 is generated by the JDK itself and simply creates a form as shown in Figure 17.4. Obviously rather than writing all of this code you build up a form and add the controls shown above to it, and allow your Java Development kit to build the code behind the scenes.
Our first modification to this code occurs at line 85 where we create an object of the class Engine. Of course creating an object of the class causes the constructor to run which means that a connection is created to access the database and the contents of the table is employees is put into a resultset object by the select query. Consequently we are ready to start processing the database as soon as the form opens.
Notice that the names on the buttons in Figure 17.4 are the same as the names of the methods of the class Engine. Consequently the actionPerformed event of each of those buttons simply calls one of those methods.
The code for the add buttons spans lines 86 – 94. The code here is quite similar to the equivalent code for the add button of our vector example in the previous chapter. At line 87 an object of the class Person is created and data extracted from the form’s fields is passed to its constructor. At line 90 the add method of the Engine object is called. If this is successful and returns a true value then “Record modified” is put on the window title, otherwise if for any reason the record could not be added then “could not add a new record” is put in the same window.
The code for the modify button spans lines 95 – 103. The structure and logic of this is identical to that of the add button and therefore we shall not discuss it further. The same applies to the code attached to the delete button which spans lines 104 – 112.
The code attached to the button first spans lines 113 – 115. It simply calls the method first of the Engine object. This method returns a pointer to a resultset object which is then passed to the showData method.
The code attached to the last, next and previous buttons work in exactly the same manner.
The method showData is similar to the same method in the vector example apart from the code inside the try block. Remember that the argument r is a pointer to a resultset object. Since the resultset in out case retrieved data from a database table that had the fields ird, name, address and age in it, the resultset will have four fields. To retrieve the ird out of this we use getString(1) i.e. get the string data in the first field. Using a different index number we get data out of the other fields in a similar manner. The data extracted from each field is stored in the appropriate text field on the form.
Below is the code that starts off our application. As there is noting new in it we shall not discuss it further.

1	package database1;
2	public class Untitled1
3	{
4	public static void main(String[] args)
5	{
6	Frame1 f = new Frame1();
7	f.setSize(500,500);
8	f.setVisible(true);
9	}
10	}

Summary

Go to top
Java cannot directly access a database and thus we have to first create an interface between the two. Java talks to the interface, which in turn passes the commands to the database and retrieves data back from it. Java, however, is unaware of what kind of database is behind the interface.
To process the database we use to major classes, Connection and Statement. The connection forms the bridge between the Java programme and the interface while the Statement carries SQL codes through that connection. Of those SQL statements only the Select statement returns a value back to the Java programme. What is returned is a resultset.

Practice

Go to top
Create a database called employees.mdb. Inside this database create a table called employees. This table will have four fields: ird, name, address, age. The first three fields will be of type text and the last will be of type integer.
Create an ODBC interface for this database.
Create a new project and copy the classes Person and Engine into it. Next create a form either by copying the code above or else creating the form using the graphical interface. Either way insert the appropriate code into the actionPerformed event of the buttons. Finally create a class with a method main in it that creates an object of the form class.

Exercise

Go to top
Exercise 17.1

Modify exercise 16.1 so that the data is saved to a database. For this you need to:

• Create a database with a table in it called Sales. This table will have fields for all of the data items that is involved in the sales: Name, address, lawnmower code, amount sold, price before gst, gst, price including gst and total charged to customer.
• Next create a class called SalesEngine.
• This will have a constructor that opens the database and reads the current contents into a resultset.
• An add method will receive an object of the class Sales as an argument, constructs an insert query from all of the data in the object and stores all of that data to the database.
• A modify method will modify an existing record that is being displayed on the form. It will work like the add method in that it will recieve an object of the class Sales as an argument and will create a modify query based on the data retrieved from the Sales object and modify the appropriate record in the database.

• A delete method will also receive an object of Sales as an argument, construct a delete query and use this to delete a record from the database.
• The methods first, last next and previous will allow the user to navigate the resultset.

• You can use Listing 17.2 as a model for this class. Other than the fact that they will be processing different sets of data the two classes will be identical.

• Next modify the form you created for exercise 16.1 as follows:
• The code attached to the actionPerformed event of the Add, Modify and Delete buttons will read data from the Name, address, lawnmower code, amount sold text boxes of the form, create an object of the class Sales from this data and then call the appropriate method of the Sales class and pass the object to that method as an argument.

• Now modify the code attached to the buttons First, Last, Next and Previous so that when they call the method showData they they will pass a Resultset pointer that has been returned by the first, last, next or previous method of the SalesEngine object.

• Finally modify the method showData so that its argument is a Resltset pointer and its body extracts data from a Resultset object and not from a Vector.

• Use Listing 17.3 as a template for how to do this.