Java Application with connection to a database

Here you learn how to create Java application with connection to MS Access database and MySQL database. You will learn how to create database and table in MS Access, MySQL You will learn how to insert, search and pull a record from a table and display it in Java. You will learn how to use Java JFrame, JPanel, JTextField, JLabel, JButton classes and how lay out different elements on the frame window. Open MS Access and select blank database. See Database design tutorial to learn how to create database in MS Access. Save database as credit.mdb in "C:\java\credit" directory. In MS Access Select queries on the objects panel and click Create query in design view. Select Query window and Show Table window with list of tables display. Close Show Table window. Double click SQL icon on main tool bar. Query1:Select Query window displays. Copy Create table script in the Query1 window and click "!" on main tool bar. credit_cards table will be created. Select tables from object panel and open credit card table in design view. (Right click it and select open in design view) Change cardid data type to Autonumber. Save changes.

create table credit_cards(
cardid int not null PRIMARY Key,
name varchar(50),
type varchar(20),
expired date,
card_num varchar(30),
credit currency,
phone varchar(15),
address varchar(100),
city varchar(20),
state varchar(2),
zip varchar(10)
);

Now you need to create "credit" data source name for your credit database. Read PHP and MS Access. tutorial to learn how to create data source name in ODBC. When data source created, you are ready to write Java code. First, we have to create Java class for connection to MS Access database. Open notepad and copy the following code.

import java.sql.*;

public class MSaccessconn
{
public MSaccessconn()
{

}

public Connection getConnection()
{
Connection conn=null;

// Loading driver
try {
String url = "jdbc:odbc:credit";

Class.forName(
"sun.jdbc.odbc.JdbcOdbcDriver" );

conn = DriverManager.getConnection( url );
}
catch ( ClassNotFoundException cnfex ) {
cnfex.printStackTrace();
}
catch ( SQLException sqlex ) {
sqlex.printStackTrace();
}
catch ( Exception excp ) {
excp.printStackTrace();
}

return conn;

}

Save file as MSaccessconn.java in "C:\java\credit" folder. Sometimes notepad automatically ads txt extension to it files. To prevent it, while saving the file, type file name in quotation marks like this "MSaccessconn.java".

Our class is very simple. It has an empty constructor public MSaccessconn() { } and one method getConnection, that returns connection to database object. Class.forName method loads the database driver. DriverManager.getConnection method created connection to the database. Imported java.sql. package includes all classes used in Msaccessconn class.

In command prompt go to "C:\java\credit" folder and type javac Msaccessconn.java Press enter. If you did not misspell anything MSaccessconn.class file will be created in the same directory. Learn how to set PATH and CLASSPATH environment variables in Start Java tutorial.

Now we ready to create CreditCard class. We need a window to enter or display data from MS Access database. That is why CreditCard class extends JFrame class. JLabels are used for field names and are placed on JPanel (labelPanel). JTextFields are used for data entry and are placed on JPanel (fieldPanel.). Both Jpanels have GridLayout.

10 is number of rows in the grid. 1 is number of column. So, all labels are placed in one column on label panel. All text fields are placed in one column on the field panel. Label and Field panels are placed on DataEntryPanel. All buttons are placed on button panel. Then DataEntryPanel and button panel placed on JFrame. JFrame has BorderLayout layout. DataEntryPanel is placed in center of JFrame, button panel is places at the bottom of it.

CreditCard constructor includes all code for creating window elements and layout them. Also Inside the constructor we creating connection to MS Access

DataEntryPanel implements ActionListener interface to listen to button events. When you implement an interface, you must implement its methods. For ActionListener interface you must implement actionPerformed( ActionEvent event) function. The event.getSource() method return object name that caused an event. In our example it is button click. Depending on what button was clicked, we call saveRecord or searchRecord() function

To insert or retrieve a record we have to create statement and recordset object. Then in while loop we walk through recordset records and retrieve fields' values. Piece of code below shows how to do it

Statement stmt = dbconn.createStatement();

ResultSet rs = stmt.executeQuery( sql );

while ( rs.next() )
{
txtName.setText(rs.getString(2));
txtType.setText(rs.getString(3));
txtExpired.setText(rs.getString(4));
txtNumber.setText(rs.getString(5));
txtCredit.setText(rs.getString(6));
txtPhone.setText(rs.getString(7));
txtAddress.setText(rs.getString(8));
txtCity.setText(rs.getString(9));
txtState.setText(rs.getString(10));
txtZip.setText(rs.getString(11));
}

stmt.close();

Please download CreditCard.java file and save it in C:\java\credit directory. Compile MSaccessconn.java file, then compile CreditCard.java file. Run the application and insert a record. Then search the record by credit card number.

If everything is working well, then try to create the same application with MySQL database. Download mysql connector connectorJ unzip and place connector jar file in a directory.

On my computer it is C:\j2sdk1.4.2_08\lib\mysql-connector-java-3.0.17-ga-bin.jar

Enter the path in AUTOEXEC.bat file or create environment variable. On my PC CLASSPATH in AUTOEXEC.bat file SET CLASSPATH=.;C:\j2sdk1.4.2_08\lib; For windows 2000, XP enter environment variables. Read how to create environment variables

Read MySQL Manual to learn how to created database and table. Download MySQLconn.java file read the code and compile the file. This code example assumes that you run MySQL on the same computer where you run java application. If it is not the case, then you have to edit MySQLconn.java file and enter computer IP address in place of localhoss like this:

conn= DriverManager.getConnection(
"jdbc:mysql://10.100.15.168/credit?user=root&password=aspirin");

If you do not know computer IP address on which MySQL is running, open command prompt on that machine and type:ipconfig

     IP Address. . . . . . . . . . . . : 10.101.15.168
     Subnet Mask . . . . . . . . . . . : 255.255.0.0
     Default Gateway . . . . . . . . . : 10.100.66.10

Then download CreditCardMySQL.java file read it and compile. Run the application. Insert records. Search for records. Have fun.

My eBooks on Amazon.com