import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import java.lang.StringBuffer;
import java.io.IOException;
import java.io.*;
//******************
import java.sql.*; //allows sql calls to the database
public class AddressBookDataBase extends JFrame
{
private DataPanel myDataPanel;
private Connection dbconn;
private static int numPeople=0;
private static String info;
private static JTextArea txtInfo=new JTextArea( 8, 40 ); //needs to be here to speak
//across classes
public AddressBookDataBase()
{
super("This is my Student Records which calls a database, Student");
GridLayout myGridLayout= new GridLayout(3,1); //allows 3 panels
Container p = getContentPane();
myDataPanel=new DataPanel();
p.add(myDataPanel);
myDataPanel.setLayout(myGridLayout);
//*********************************
try
{
Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
dbconn = DriverManager.getConnection("jdbc:odbc:databaseName",
"servernameForOracle","password");
info="Connection successful\n";
}
catch ( ClassNotFoundException cnfex )
{
cnfex.printStackTrace();
info=info+"Connection unsuccessful\n" + cnfex.toString();
}
catch ( SQLException sqlex )
{
sqlex.printStackTrace();
info=info+"Connection unsuccessful\n" +sqlex.toString();
}
catch ( Exception excp )
{
excp.printStackTrace();
info=info+excp.toString();
}
//**********************************
txtInfo.setText(info); //sets connection information
setSize(500,290);
setVisible(true);
}
public static void main(String args[])
{
AddressBookDataBase myAddressBookDataBase= new AddressBookDataBase();
myAddressBookDataBase.addWindowListener
(
new WindowAdapter()
{
public void windowClosing(WindowEvent e)
{
System.exit(0);
}
}
);
}
//*******************************
class DataPanel extends JPanel implements ActionListener
{
JLabel lblIDCap= new JLabel("Record Number");
JLabel lblLast=new JLabel("ROLL NO");
JLabel lblFirst=new JLabel("First Name");
JLabel lblPhone=new JLabel("AGE");
//JTextArea txtInfo=new JTextArea();
JLabel lblID=new JLabel(" "); //10 spaces
JTextField txtLast=new JTextField(10);
JTextField txtFirst=new JTextField(10);
JTextField txtPhone=new JTextField(10);
JButton btnAdd=new JButton("Add Record");
JButton btnFind=new JButton("Find Record");
JButton btnDelete=new JButton("Delete Record");
JButton btnUpdate=new JButton("Update Record"); //**
JButton btnClear=new JButton("Clear");
JButton btnExit=new JButton("Exit");
public DataPanel()
{
JPanel myPanel = new JPanel();
JPanel myPanel2 = new JPanel();
JPanel myPanel3 =new JPanel(); //**
myPanel.setLayout(new GridLayout (4,2));
myPanel2.setLayout(new GridLayout (2,3));
myPanel3.setLayout(new GridLayout(1,1));
add(myPanel);
add(myPanel2);
add(myPanel3); //**
myPanel.add(lblIDCap);
myPanel.add(lblID);
myPanel.add(lblLast);
myPanel.add(txtLast);
myPanel.add(lblFirst);
myPanel.add(txtFirst);
myPanel.add(lblPhone);
myPanel.add(txtPhone);
myPanel2.add(btnAdd);
myPanel2.add(btnFind);
myPanel2.add(btnDelete);
myPanel2.add(btnUpdate);
myPanel2.add(btnClear);
myPanel2.add(btnExit);
myPanel3.add( new JScrollPane(txtInfo)); //**
//puts txtInfo on application and allows it to scroll
btnAdd.addActionListener(this);
btnFind.addActionListener(this);
btnUpdate.addActionListener(this);
btnClear.addActionListener(this);
btnExit.addActionListener(this);
btnDelete.addActionListener(this);
}
public void actionPerformed(ActionEvent event)
{
String ID=""; //must initialize to ""
String Last="";
String First="";
String Phone="";
Object source=event.getSource();
ID=lblID.getText().trim();
lblID.setText(ID);
Last=txtLast.getText().trim();
txtLast.setText(Last);
First=txtFirst.getText().trim();
txtFirst.setText(First);
Phone=txtPhone.getText().trim();
txtPhone.setText(Phone);
if (source.equals(btnAdd))
{
//********************************
try {
Statement statement = dbconn.createStatement();
if ( !Last.equals( "" ) &&
!First.equals( "" ) &&
!Phone.equals("") )
{
String temp = "INSERT INTO stu (" +
"Rollno, name, " +
"age" +
") VALUES (" +
Last + ", '" +
First + "', '" +
Phone + "')";
txtInfo.append( "\nInserting: " +
dbconn.nativeSQL( temp ) + "\n" );
int result = statement.executeUpdate( temp );
if ( result == 1 )
{ //does a query to see if insertion successful
txtInfo.append("\nInsertion successful\n");
String query="";
try
{
query = "SELECT * FROM stu WHERE name='" +
First + "' " ;
ResultSet rs = statement.executeQuery( query );
rs.next();
lblID.setText(String.valueOf(rs.getInt(1)));
}
catch ( SQLException sqlex )
{
txtInfo.append("error :-"+ sqlex.toString() );
}
}
else
{
txtInfo.append( "\nInsertion failed\n" );
txtFirst.setText( "" );
txtLast.setText( "" );
txtPhone.setText( "" );
}
}
else
txtInfo.append( "\nEnter Rollno, first, and " +
"Age, then press Add\n" );
statement.close();
}
catch ( SQLException sqlex )
{
txtInfo.append( sqlex.toString() );
txtFirst.setText("Entry already exists -- reenter");
}
}
//****************************
if (source.equals(btnFind))
{
try
{
if ( !Last.equals(""))
{
Statement statement =dbconn.createStatement();
String query = "SELECT * FROM stu " +
"WHERE rollno = " +
Last + " ";
txtInfo.append( "\nSending query: " +
dbconn.nativeSQL( query ) + "\n" );
ResultSet rs = statement.executeQuery( query );
display( rs );
statement.close();
}
else
txtLast.setText("Enter Roll No"+
" then press Find" );
}
catch ( SQLException sqlex )
{
txtInfo.append( sqlex.toString() + sqlex.getMessage() );
}
}
//******************************************
if (source.equals(btnUpdate))
{
try
{
Statement statement = dbconn.createStatement();
if ( ! lblID.getText().equals(""))
{
String temp = "UPDATE stu SET " +
"name='" + txtFirst.getText() +
"', age=" + txtPhone.getText() +
" WHERE rollno=" + lblID.getText();
txtInfo.append( "\nSending update: " +
dbconn.nativeSQL( temp ) + "\n" );
int result = statement.executeUpdate( temp );
if ( result == 1 )
txtInfo.append( "\nUpdate successful\n" );
else {
txtInfo.append( "\nUpdate failed\n" );
txtFirst.setText( "" );
txtLast.setText( "" );
txtPhone.setText( "" );
}
statement.close();
}
else
txtInfo.append( "\nYou may only update an " +
"existing record. Use Find to " +
"\nlocate the record, then " +
"modify the information and " +
"\npress Update.\n" );
}
catch ( SQLException sqlex ) {
txtInfo.append( sqlex.toString() );
}
}
//********************************************
if (source.equals(btnDelete))
{
try
{
Statement statement = dbconn.createStatement();
if ( ! lblID.getText().equals(""))
{
System.out.print(lblID.getText());
String temp = "DELETE from stu " +
" WHERE rollno=" + lblID.getText();
txtInfo.append( "\nDeleting Record: " +
dbconn.nativeSQL( temp ) + "\n" );
int result = statement.executeUpdate( temp );
if ( result == 1 )
{
txtInfo.append( "\nDeletion successful\n" );
}
else
{
txtInfo.append( "\nDeletion failed\n" );
txtFirst.setText( "" );
txtLast.setText( "" );
txtPhone.setText( "" );
}
statement.close();
}
else
txtInfo.append( "\nYou may only delete an " +
"existing record. Use Find to " +
"\nlocate the record, then " +
"press delete.\n" );
}
catch ( SQLException sqlex )
{
txtInfo.append( sqlex.toString() );
}
}
//********************************************
if (source.equals(btnClear))
{
txtLast.setText("");
txtFirst.setText("");
txtPhone.setText("");
lblID.setText("");
}
//********************************************
if (source.equals(btnExit))
{
System.exit(0);
}
}
//********************************************
public void display( ResultSet rs )
{
try
{
rs.next();
txtLast.setText("");
txtFirst.setText("");
txtPhone.setText("");
lblID.setText("");
int recordNumber = rs.getInt( 1 );
if ( recordNumber != 0 )
{
lblID.setText( String.valueOf(recordNumber) );
txtFirst.setText( rs.getString( 2 ) );
txtPhone.setText( rs.getString( 3 ) );
txtLast.setText( rs.getString( 1 ) );
}
else
{
txtInfo.append( "\nNo record found\n" );
}
}
catch ( SQLException sqlex )
{
txtInfo.append( "\n*** Student Details Not In Database ***\n" );
}
} }
}
