PROJECT
PROJECT
PROJECT
Introduction
This software project is developed to automate the functionalities of a
functionaries.
is tied with the database for easy access and interface to the database.
This software, being simple in design and working, does not require
During coding and design of the software Project, Java NetBeans IDE, a
1
(GUI) based integrated platform and coding simplicity. As a back-end a
In its current scope, the software enables user to retrieve and update
the information from centralized database designed with MySQL . This
software does not require much training time of the users due to
limited functionality and simplicity.
2
During the development of Public Library Information System project,
Java NetBeans IDE, a powerful, open source event-driven form-based
development environment is used for modular design and future
expandability of the system.
3. Some application area like accounting of books and fines etc. are
not implemented in the project. It facilitates librarian to record
and update only transaction record.
3
3. Theoretical Background
To find and retrieve just the data that meets conditions you specify,
including data from multiple tables, create a query. A query can also
update or delete multiple records at the same time, and perform built-
in or custom calculations on your data.
4
Role of RDBMS Application Program:
A computer database works as a electronic filing system, which has a
large number of ways of cross-referencing, and this allows the user
many different ways in which to re-organize and retrieve data. A
database can handle business inventory, accounting and filing and use
the information in its files to prepare summaries, estimates and other
reports. The management of data in a database system is done by
means of a general-purpose software package called a Database
Management System (DBMS). Some commercially available DBMS are
MS SQL Server, MS ACCESS, INGRES, ORACLE, and Sybase. A database
management system, therefore, is a combination of hardware and
software that can be used to set up and monitor a database, and can
manage the updating and retrieval of database that has been stored in
it. Most of the database management systems have the following
capabilities:
Creating of a table, addition, deletion, modification of records.
Retrieving data collectively or selectively.
The data stored can be sorted or indexed at the user's discretion
and direction.
Various reports can be produced from the system. These may be
either standardized report or that may be specifically generated
according to specific user definition.
Mathematical functions can be performed and the data stored in
the database can be manipulated with these functions to perform
the desired calculations.
5
To maintain data integrity and database use.
8
3.3 What is NetBeans IDE ?
9
first sponsored open source project, one in which Sun would be paying
for the site and handling the infrastructure.
Features of NetBeans
NetBeans IDE 6.9 introduces the JavaFX Composer, support for JavaFX
SDK 1.3, OSGi interoperability, support for the PHP Zend framework
and Ruby on Rails 3.0, and more.
10
4. Problem Definition & Analysis
The hardest part of building a software system is deciding precisely
what to build. No other part of the conceptual work is so difficult as
establishing the detailed technical requirement. Defining and applying
good, complete requirements are hard to work, and success in this
endeavor has eluded many of us. Yet, we continue to make progress.
Problem definition describes the What of a system, not How . The
quality of a software product is only as good as the process that creates
it. Problem definition is one of the most crucial steps in this creation
process. Without defining a problem, developers do not know what to
build, customers do not know what to expect, and there is no way to
validate that the built system satisfies the requirement.
Problem definition and Analysis is the activity that encompasses
learning about the problem to be solved, understanding the needs of
customer and users, trying to find out who the user really is, and
understanding all the constraints on the solution. It includes all
activities related to the following:
Identification and documentation of customer’s or user’s needs.
Creation of a document that describes the external behavior and
the association constraints that will satisfies those needs.
Analysis and validation of the requirements documents to ensure
consistency, completeness, and feasibility
Evolution of needs.
After the analysis of the functioning of a Public Library system, the
proposed System is expected to do the following: -
To provide a user friendly, Graphical User Interface (GUI) based
integrated and centralized environment for computerized Public
Library System.
11
The proposed system should maintain all the records and
transactions, and should generate the required reports and
information when required.
12
5. System Implementation
13
6. System Design & Development
Table: Publisher
Column Name Type Size
Pub_ID (Primary Key) Integer 4
Pub_Name Varchar 30
Pub_Address Varchar 40
Pub_Phone1 Varchar 12
Pub_Phone2 Varchar 12
Status Char 1
Table: Lib
Column Name Type Size
ACC_No (Primary Key) Integer 4
Acq_No Integer 4
BTitle Varchar 30
Author1 Varchar 30
Author2 Varchar 30
14
Pub_ID (Foreign Key-Reference Publisher) Integer 4
Price Float(8,2) 8
PDate Date
Pages Integer 4
Edition Integer 4
Status Char 1
Table: Member
Column Name Type Size
Memb_No (Primary Key) Integer 4
Memb_Name Varchar 30
Memb_Add Varchar 40
Memb_Phone Varchar 12
MDate Date
MEDate Date
MFee Float(8,2) 4
Mem_Status Char 1
Mem_Issue Char 1
Table: MIssue
Column Name Type Size
ACC_No Integer 4
Acq_No Integer 4
Memb_No Integer 4
IDate Date 10
RDate Date 10
Table: TIssue
Column Name Type Size
ACC_No Integer 4
Memb_No Integer 4
IDate Date 10
RDate Date 10
15
6.2 Menu Design:
JSS Infoware gateway comprises the following options, organized in a
user friendly way. The menu system divided in Menu Bars, each having
a pull down menus containing options for a specific task.
16
Deletion of MembDelUI.java
Deletion Member
records.
Viewing of MembNavUI.java
Record
publisher
Navigation
records.
Available Book Report of ABList.java
List Available books.
Issued Book Report of Issued IBList.java
4. Reports
List books.
Report of MList.java
Members List
members.
Close the --
5. Quit Application
Application.
Frame: MainUI.java
19
private void mnuMListActionPerformed(java.awt.event.ActionEvent
evt) {
new MListUI().setVisible(true);
}
private void mnuLibEditActionPerformed(java.awt.event.ActionEvent
evt) {
// TODO add your handling code here:
new LibEditUI().setVisible(true);
}
private void
jMenuItem1ActionPerformed(java.awt.event.ActionEvent evt) {
// TODO add your handling code here:
new LibDelUI().setVisible(true);
}
/** * @param args the command line arguments */
public static void main(String args[]) {
java.awt.EventQueue.invokeLater(new Runnable() {
public void run() {
new MainUI().setVisible(true);
}
});
}
Frame: PubUI.java
20
Coding of pubUI.java
import java.sql.*;
import javax.swing.JOptionPane;
private void cmdExitActionPerformed(java.awt.event.ActionEvent
evt) {
this.setVisible(false);
new MainUI().setVisible(true);
}
private void formWindowGainedFocus(java.awt.event.WindowEvent
evt) {
txtPno.setEditable(false);
// Deactivate the Save button when form loads
cmdSave.setEnabled(false);
}
private void cmdNewActionPerformed(java.awt.event.ActionEvent
evt) {
// Activate the Save button when New button clicked
cmdSave.setEnabled(true);
try {
21
Class.forName("com.mysql.jdbc.Driver");
Connection con = (Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
Statement stmt = null;
ResultSet rs = null; // ResultSet for publisher table.
String SQL = "SELECT * FROM publisher";
stmt = con.createStatement(); // Connection string for
ResultSet - rs.
rs = stmt.executeQuery(SQL);
int pno = 1;
int PID=0;
while (rs.next()) {
PID = rs.getInt("pub_id");
pno++;
}
PID++;
pno = PID;
txtPno.setText(Integer.toString(pno));
txtPName.setFocusable(true);
con.close();
rs.close();
stmt.close();
} catch (Exception e) {
JOptionPane.showMessageDialog(this,e.getMessage());
e.printStackTrace();
}
}
private void cmdSaveActionPerformed(java.awt.event.ActionEvent
evt) {
try {
22
Class.forName("com.mysql.jdbc.Driver");
Connection con = (Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
Statement stmt = null;
ResultSet rs = null;
String SQL = "SELECT * FROM publisher";
stmt = con.createStatement();
rs = stmt.executeQuery(SQL);
int Pno = Integer.parseInt(txtPno.getText());
String PName = txtPName.getText();
String PAdd = txtPAdd.getText();
String Pph1 = txtPh1.getText();
String Pph2 = txtPh2.getText();
char PStatus = 'Y';
int code = JOptionPane.showConfirmDialog(this, "Are you sure
to add?", "Confirmation Dialog Box",
JOptionPane.YES_NO_CANCEL_OPTION,
JOptionPane.INFORMATION_MESSAGE);
if (code == JOptionPane.YES_OPTION) {
String strSQL = "INSERT INTO Publisher(pub_id, pub_name,
pub_address, pub_phone1, pub_phone2, status) VALUES ("+(Pno)+",
'"+(PName)+"', '"+(PAdd)+"', '"+(Pph1)+"', '"+(Pph2)+"', '"+(PStatus)
+"')";
int rowsEffected = stmt.executeUpdate(strSQL);
JOptionPane.showMessageDialog(this, "Record added
successfully into Publisher table");
}
con.close();
stmt.close();
rs.close();
cmdSave.setEnabled(false);
23
} catch (Exception e) {
JOptionPane.showMessageDialog(this, e.getMessage());
}
}
private void cmdClearActionPerformed(java.awt.event.ActionEvent
evt) {
txtPno.setText("");
txtPName.setText("");
txtPAdd.setText("");
txtPh1.setText("");
txtPh2.setText("");
cmdSave.setEnabled(false);
}
private void cmdEditActionPerformed(java.awt.event.ActionEvent
evt) {
this.setVisible(false);
new PubEditUI().setVisible(true);
}
/** * @param args the command line arguments */
/*public static void main(String args[]) {
java.awt.EventQueue.invokeLater(new Runnable() {
public void run() {
new pubUI().setVisible(true);
new pubUI().setSize(500, 500);
}
});
}*/
}
Frame: PubEditUI.java
24
Coding for PubEditUI.java
/* * PubEditUI.java **/
import java.sql.*;
import javax.swing.JOptionPane;
import javax.swing.DefaultListModel;
public class PubEditUI extends javax.swing.JFrame {
/** Creates new form PubEditUI */
public PubEditUI() {
initComponents();
}
private void formWindowGainedFocus(java.awt.event.WindowEvent
evt) {
txtPno.setEditable(false);
// Creating a ListModel object dModel to perform
DefaultListModel
// method operations
DefaultListModel dModel = (DefaultListModel) jList1.getModel();
// Method to add elements into jList1 control
dModel.clear();
try {
25
Class.forName("com.mysql.jdbc.Driver");
Connection con = (Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
Statement stmt = null;
ResultSet rs = null;
String SQL = "SELECT * FROM Publisher";
stmt = con.createStatement();
rs = stmt.executeQuery(SQL);
while (rs.next()) {
String Pno = rs.getString("pub_id");
String PName = rs.getString("pub_name");
// To make the publisher no. as 4 digit because we will extract
4 digit from list value
// in mouse click event.
if (Pno.length() < 4)
{
int x = Pno.length();
int nl = 4 - x;
while (nl > 0){
Pno = Pno + " ";
nl--;
}
}
dModel.addElement(Pno + "- " + PName);
}
jList1.setModel(dModel);
con.close();
} catch (Exception e) {
JOptionPane.showMessageDialog(this,e.getMessage());
e.printStackTrace();
26
}
}
private void jList1MouseClicked(java.awt.event.MouseEvent evt) {
// getSelectedValue() method extracts the current cursor location
value into a variable
String MPub = (String) jList1.getSelectedValue();
// Extract the first 4 characters as publisher ID into a variable
String PubN =MPub.trim().substring(0, 3);
String query = "SELECT * FROM Publisher WHERE pub_Id = " +
PubN + ";";
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con = (Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
// Create SQL statement and execute query.
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(query);
if (rs.next()) {
//PubN = rs.getString("pub_no");
String PubName = rs.getString("pub_name");
String PubAdd = rs.getString("pub_address");
String PubPh1 = rs.getString("pub_phone1");
String PubPh2 = rs.getString("pub_phone2");
String PStatus = rs.getString("status");
// Displaying the contents in respective text boxes.
txtPno.setText(PubN);
txtPName.setText(PubName);
txtPAdd.setText(PubAdd);
txtPh1.setText(PubPh1);
txtPh2.setText(PubPh2);
txtStatus.setText(PStatus);
27
txtPno.setEditable(false);
// Close the operational object for Student
con.close();
stmt.close();
rs.close();
} else {
JOptionPane.showMessageDialog(null, "Record does not
found in Publisher table");
}
} catch (Exception e) {
JOptionPane.showMessageDialog(this, e.getMessage());
}
}
private void cmdExitActionPerformed(java.awt.event.ActionEvent
evt) {
this.setVisible(false);
//new MainUI().setVisible(true);
}
private void
cmdUpdateActionPerformed(java.awt.event.ActionEvent evt) {
try {
// Connect to MySQL database
// Don't forget to import the two packages
// import java.sql.*;
// import javax.swing.JOptionPane;
Class.forName("com.mysql.jdbc.Driver");
Connection con = (Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
Statement stmt = null;
ResultSet rs = null;
28
String SQL = "SELECT * FROM Publisher";
stmt = con.createStatement();
rs = stmt.executeQuery(SQL);
int pno = Integer.parseInt(txtPno.getText().trim());
String PubName = txtPName.getText();
String PubAdd = txtPAdd.getText();
String PubPh1 = txtPh1.getText();
String PubPh2 = txtPh2.getText();
String PStatus = txtStatus.getText();
String strSQL = "Update publisher set pub_name ='"+(PubName)
+"',pub_address = '"+(PubAdd)+"', pub_phone1 = '"+(PubPh1)+"',
pub_phone2 = '"+(PubPh2)+"', status = '"+(PStatus)+"' where pub_id = "
+ (pno);
int rowsEffected = stmt.executeUpdate(strSQL);
if (rowsEffected == 0)
JOptionPane.showMessageDialog(this, "Record does not
exists");
else
JOptionPane.showMessageDialog(this,"Record modified");
con.close();
stmt.close();
rs.close();
} catch (Exception e) {
JOptionPane.showMessageDialog(this, e.getMessage());
}
}
/** * @param args the command line arguments */
/*public static void main(String args[]) {
java.awt.EventQueue.invokeLater(new Runnable() {
public void run() {
new PubEditUI().setVisible(true);
}
29
});
}*/
}
30
Frame: PubDelUI.java
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
stmt = con.createStatement();
rs = stmt.executeQuery(SQL);
while (rs.next()) {
String Pno = rs.getString("pub_id");
String PName = rs.getString("pub_name");
// To make the publisher no. as 4 digit because we will extract
4 digit from list value
// in mouse click event.
if (Pno.length() < 4)
{
int x = Pno.length();
int nl = 4 - x;
while (nl > 0){
Pno = Pno + " ";
nl--;
}
}
dModel.addElement(Pno + "- " + PName);
}
jList1.setModel(dModel);
con.close();
} catch (Exception e) {
JOptionPane.showMessageDialog(this,e.getMessage());
e.printStackTrace();
}
}
35
/** * @param args the command line arguments */
/*public static void main(String args[]) {
java.awt.EventQueue.invokeLater(new Runnable() {
public void run() {
new PubDelUI().setVisible(true);
}
});
}*/
}
Frame: PubNavUI.java
Coding of PubNavUI.java
/* PubNavUI.java */
import java.sql.*;
import javax.swing.JOptionPane;
public class PubNavUI extends javax.swing.JFrame {
/** Creates new form PubNavUI */
public PubNavUI() {
36
initComponents();
}
// Global variables
Statement stmt = null;
ResultSet rs = null;
String SQL = "SELECT * FROM publisher";
public void disable_textfields() {
txtPno.setEditable(false);
txtPName.setEditable(false);
txtPAdd.setEditable(false);
txtPh1.setEditable(false);
txtPh2.setEditable(false);
txtStatus.setEditable(false);
}
private void cmdFirstActionPerformed(java.awt.event.ActionEvent
evt) {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con = (Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
if (rs.first()) {
String PubN = rs.getString("pub_id");
String PubName = rs.getString("pub_name");
String PubAdd = rs.getString("pub_address");
String PubPh1 = rs.getString("pub_phone1");
String PubPh2 = rs.getString("pub_phone2");
String PStatus = rs.getString("status");
// Displaying the contents in respective text boxes.
txtPno.setText(PubN);
txtPName.setText(PubName);
37
txtPAdd.setText(PubAdd);
txtPh1.setText(PubPh1);
txtPh2.setText(PubPh2);
txtStatus.setText(PStatus);
cmdFirst.setEnabled(false);
cmdNext.setEnabled(true);
cmdPrev.setEnabled(false);
cmdLast.setEnabled(true);
} else {
cmdFirst.setEnabled(false);
cmdNext.setEnabled(false);
cmdPrev.setEnabled(false);
cmdLast.setEnabled(false);
JOptionPane.showMessageDialog(this, "Rhere is no record in
table", "Student",0);
}
} catch (Exception e) {
JOptionPane.showMessageDialog(this, e.getMessage());
}
}
private void cmdNextActionPerformed(java.awt.event.ActionEvent
evt) {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con = (Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
if (rs.next()) {
String PubN = rs.getString("pub_id");
String PubName = rs.getString("pub_name");
String PubAdd = rs.getString("pub_address");
38
String PubPh1 = rs.getString("pub_phone1");
String PubPh2 = rs.getString("pub_phone2");
String PStatus = rs.getString("status");
// Displaying the contents in respective text boxes.
txtPno.setText(PubN);
txtPName.setText(PubName);
txtPAdd.setText(PubAdd);
txtPh1.setText(PubPh1);
txtPh2.setText(PubPh2);
txtStatus.setText(PStatus);
cmdFirst.setEnabled(true);
cmdNext.setEnabled(true);
cmdPrev.setEnabled(true);
cmdLast.setEnabled(true);
} else {
cmdNext.setEnabled(false);
JOptionPane.showMessageDialog(this, "You are at last record
position", "Student",0);
}
} catch (Exception e) {
JOptionPane.showMessageDialog(this, e.getMessage());
}
}
private void cmdExitActionPerformed(java.awt.event.ActionEvent
evt) {
this.setVisible(false);
}
private void cmdPrevActionPerformed(java.awt.event.ActionEvent
evt) {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con = (Connection)
39
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
if (rs.previous()) {
String PubN = rs.getString("pub_id");
String PubName = rs.getString("pub_name");
String PubAdd = rs.getString("pub_address");
String PubPh1 = rs.getString("pub_phone1");
String PubPh2 = rs.getString("pub_phone2");
String PStatus = rs.getString("status");
// Displaying the contents in respective text boxes.
txtPno.setText(PubN);
txtPName.setText(PubName);
txtPAdd.setText(PubAdd);
txtPh1.setText(PubPh1);
txtPh2.setText(PubPh2);
txtStatus.setText(PStatus);
cmdFirst.setEnabled(true);
cmdNext.setEnabled(true);
cmdPrev.setEnabled(true);
cmdLast.setEnabled(true);
} else {
cmdPrev.setEnabled(false);
JOptionPane.showMessageDialog(this, "You are at first
position", "Student",0);
}
} catch (Exception e) {
JOptionPane.showMessageDialog(this, e.getMessage());
}
}
private void cmdLastActionPerformed(java.awt.event.ActionEvent
evt) {
40
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con = (Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
if (rs.last()) {
String PubN = rs.getString("pub_id");
String PubName = rs.getString("pub_name");
String PubAdd = rs.getString("pub_address");
String PubPh1 = rs.getString("pub_phone1");
String PubPh2 = rs.getString("pub_phone2");
String PStatus = rs.getString("status");
// Displaying the contents in respective text boxes.
txtPno.setText(PubN);
txtPName.setText(PubName);
txtPAdd.setText(PubAdd);
txtPh1.setText(PubPh1);
txtPh2.setText(PubPh2);
txtStatus.setText(PStatus);
cmdFirst.setEnabled(true);
cmdNext.setEnabled(false);
cmdPrev.setEnabled(true);
cmdLast.setEnabled(false);
} else {
JOptionPane.showMessageDialog(this, "You are already at last
record", "Student",0);
}
} catch (Exception e) {
JOptionPane.showMessageDialog(this, e.getMessage());
}
}
41
private void formWindowGainedFocus(java.awt.event.WindowEvent
evt) {
disable_textfields();
try {
// Connect to MySQL database
// Don't forget to import the two packages
// import java.sql.*;
// import javax.swing.JOptionPane;
Class.forName("com.mysql.jdbc.Driver");
Connection con = (Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
stmt = con.createStatement();
rs = stmt.executeQuery(SQL);
if (rs.first())
{
String PubN = rs.getString("pub_id");
String PubName = rs.getString("pub_name");
String PubAdd = rs.getString("pub_address");
String PubPh1 = rs.getString("pub_phone1");
String PubPh2 = rs.getString("pub_phone2");
String PStatus = rs.getString("status");
// Displaying the contents in respective text boxes.
txtPno.setText(PubN);
txtPName.setText(PubName);
txtPAdd.setText(PubAdd);
txtPh1.setText(PubPh1);
txtPh2.setText(PubPh2);
txtStatus.setText(PStatus);
cmdFirst.setEnabled(false);
cmdNext.setEnabled(true);
42
cmdPrev.setEnabled(false);
cmdLast.setEnabled(true);
}
else
{
cmdFirst.setEnabled(false);
cmdNext.setEnabled(false);
cmdPrev.setEnabled(false);
cmdLast.setEnabled(false);
JOptionPane.showMessageDialog(this, "Rhere is no record in
table", "Student",0);
}
} catch (Exception e) {
JOptionPane.showMessageDialog(this, e.getMessage());
}
}
/** * @param args the command line arguments */
public static void main(String args[]) {
java.awt.EventQueue.invokeLater(new Runnable() {
public void run() {
new PubNavUI().setVisible(true);
}
});
}
}
Frame: LibUI.java
43
Coding for LibUI.java
/* * LibUI.java **/
import java.sql.*;
import javax.swing.JOptionPane;
import javax.swing.DefaultComboBoxModel;
public class LibUI extends javax.swing.JFrame {
/** Creates new form LibUI */
public LibUI() {
initComponents();
}
Statement stmt = null;
ResultSet rs = null;
String SQL = "SELECT * FROM Lib";
// for table Publisher
Statement stmt1 = null;
ResultSet rs1 = null;
String SQL1 = "SELECT * FROM publisher";
44
private void cmdExitActionPerformed(java.awt.event.ActionEvent
evt) {
this.setVisible(false);
}
private void cmdEditActionPerformed(java.awt.event.ActionEvent
evt) {
this.setVisible(false);
new LibEditUI().setVisible(true);
}
private void cmdSaveActionPerformed(java.awt.event.ActionEvent
evt) {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con = (Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
stmt = con.createStatement();
rs = stmt.executeQuery(SQL);
int Acno = Integer.parseInt(txtAcno.getText().trim());
String bTitle = txtBTitle.getText();
String Auth1 = txtAuth1.getText();
String Auth2 = txtAuth2.getText();
// Creating a String object pName
String pName = (String) jComboBox1.getSelectedItem();
// Extract the first 4 characters as publisher number into a
variable
String PubNo = String.valueOf(pName.substring(0, 3).trim());
int pno = Integer.parseInt(PubNo);
jLabel10.setText(""+pno);
float Price = Float.parseFloat(txtPrice.getText());
int pages = Integer.parseInt(txtPages.getText());
45
int edition = Integer.parseInt(txtEdition.getText());
String Pdate = txtPDate.getText();
char BStatus = 'Y';
int code = JOptionPane.showConfirmDialog(this, "Are you sure
to add?", "Confirmation Dialog Box",
JOptionPane.YES_NO_CANCEL_OPTION,
JOptionPane.INFORMATION_MESSAGE);
if (code == JOptionPane.YES_OPTION) {
String strSQL = "INSERT INTO Lib(acc_no, btitle, author1,
author2, pub_id, price, pdate, pages, edition, status) VALUES ("+(Acno)
+", '"+(bTitle)+"', '"+(Auth1)+"', '"+(Auth2)+"', "+(pno)+", "+(Price)+", '"+
(Pdate)+"', "+(pages)+", "+(edition)+", '"+(BStatus)+"')";
int rowsEffected = stmt.executeUpdate(strSQL);
JOptionPane.showMessageDialog(this, "Record added
successfully into Lib table");
}
cmdSave.setEnabled(false);
} catch (Exception e) {
JOptionPane.showMessageDialog(this, e.getMessage());
}
}
private void cmdNewActionPerformed(java.awt.event.ActionEvent
evt) {
// Activate the Save button when New button pressed
cmdSave.setEnabled(true);
DefaultComboBoxModel cModel = (DefaultComboBoxModel)
jComboBox1.getModel();
cModel.removeAllElements();
txtStatus.setEditable(false);
// Activate the Save button when New button clicked
cmdSave.setEnabled(true);
try {
46
Class.forName("com.mysql.jdbc.Driver");
Connection con = (Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
stmt = con.createStatement(); // Connection string for
ResultSet - rs.
rs = stmt.executeQuery(SQL);
int acno=0, ACNO = 0;
while (rs.next()) {
ACNO = rs.getInt("acc_no");
}
ACNO++;
acno = ACNO;
txtAcno.setText(Integer.toString(acno));
stmt1 = con.createStatement(); // To list publishers in
JComboBox1 component
rs1 = stmt1.executeQuery(SQL1);
while (rs1.next()) {
String pubno = rs1.getString("pub_id");
String pubName = rs1.getString("pub_name");
// To make the publisher no. as 4 digit because we will extract
4 digit from list value
// in mouse click event
txtStatus.setText("Y"); // Because it is a new book, when it will
be issue,
// it's status will be N.
if (pubno.length() < 4)
{
int x = pubno.length();
int nl = 4 - x;
while (nl > 0){
47
pubno = pubno + " ";
nl--;
}
}
cModel.addElement(pubno + "- " + pubName);
}
} catch (Exception e) {
JOptionPane.showMessageDialog(this,e.getMessage());
e.printStackTrace();
}
}
private void cmdClearActionPerformed(java.awt.event.ActionEvent
evt) {
DefaultComboBoxModel cModel = (DefaultComboBoxModel)
jComboBox1.getModel();
txtAcno.setText("");
txtBTitle.setText("");
txtAuth1.setText("");
txtAuth2.setText("");
cModel.removeAllElements();
txtPrice.setText("");
txtPages.setText("");
txtEdition.setText("");
txtPDate.setText("");
txtStatus.setText("");
cmdSave.setEnabled(false);
}
private void formWindowGainedFocus(java.awt.event.WindowEvent
evt) {
txtAcno.setEditable(false);
// Deactivate the Save button when form loads
cmdSave.setEnabled(false);
48
}
private void cmdDeleteActionPerformed(java.awt.event.ActionEvent
evt) {
this.setVisible(false);
new LibDelUI().setVisible(true);
}
/** * @param args the command line arguments */
public static void main(String args[]) {
java.awt.EventQueue.invokeLater(new Runnable() {
public void run() {
new LibUI().setVisible(true);
}
});
}
}
Frame: LibEditUI.java
49
Coding for LibEditUI.java
/* * LibEditUI.java **/
import java.sql.*;
import javax.swing.JOptionPane;
import javax.swing.DefaultListModel;
import javax.swing.DefaultComboBoxModel;
public class LibEditUI extends javax.swing.JFrame {
/** Creates new form LibEditUI */
public LibEditUI() {
initComponents();
}
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
stmt = con.createStatement();
rs = stmt.executeQuery(SQL);
int Acno = Integer.parseInt(txtAcno.getText().trim());
String bTitle = txtBTitle.getText();
50
String Auth1 = txtAuth1.getText();
String Auth2 = txtAuth2.getText();
// Creating a String object pName
String pName = (String) jComboBox1.getSelectedItem();
// Extract the first 4 characters as publisher number into a
variable
String NPubNo = String.valueOf(pName.substring(0, 3).trim());
int Npno = Integer.parseInt(NPubNo);
int PID = Integer.parseInt(txtPID.getText());
if (Npno != PID)
PID = Npno;
float Price = Float.parseFloat(txtPrice.getText());
int pages = Integer.parseInt(txtPages.getText());
int edition = Integer.parseInt(txtEdition.getText());
String Pdate = txtPDate.getText();
String BStatus = txtStatus.getText();
String strSQL = "Update Lib set btitle ='"+(bTitle)+"', author1 =
'"+(Auth1)+"', author2 = '"+(Auth2)+"', pub_id = "+(PID)+", price = "+
(Price)+", pdate = '"+(Pdate)+"', pages = "+(pages)+ ", edition = "+
(edition)+", status = '"+(BStatus)+"' where acc_no = " + (Acno);
int rowsEffected = stmt.executeUpdate(strSQL);
if (rowsEffected == 0)
JOptionPane.showMessageDialog(this, "Record does not
exists");
else
JOptionPane.showMessageDialog(this,"Record modified");
} catch (Exception e) {
JOptionPane.showMessageDialog(this, e.getMessage());
}
}
private void formWindowGainedFocus(java.awt.event.WindowEvent
evt) {
51
txtAcno.setEditable(false);
txtPID.setEditable(false);
txtStatus.setEditable(false);
// Creating a ListModel object dModel to perform
DefaultListModel
// method operations
DefaultListModel dModel = (DefaultListModel) jList1.getModel();
// Method to add elements into jList1 control
dModel.clear();
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con = (Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
stmt = con.createStatement();
rs = stmt.executeQuery(SQL);
while (rs.next()) {
String Ano = rs.getString("acc_no");
String BTitle = rs.getString("btitle");
// To make the Accession no. as 4 digit because we will extract
4 digit from list value
// in mouse click event.
if (Ano.length() < 4)
{
int x = Ano.length();
int nl = 4 - x;
while (nl > 0){
Ano = Ano + " ";
nl--;
}
}
52
dModel.addElement(Ano + "- " + BTitle);
}
jList1.setModel(dModel);
// To list the publisher id and name into JComboBox1.
DefaultComboBoxModel cModel = (DefaultComboBoxModel)
jComboBox1.getModel();
cModel.removeAllElements();
stmt1 = con.createStatement(); // To list publishers in
JComboBox1 component
rs1 = stmt1.executeQuery(SQL1);
while (rs1.next()) {
String pubno = rs1.getString("pub_id");
String pubName = rs1.getString("pub_name");
// To make the publisher no. as 4 digit because we will extract
4 digit from list value
// in mouse click event
txtStatus.setText("Y"); // Because it is a new book, when it will
be issue,
// it's status will be N.
if (pubno.length() < 4)
{
int x = pubno.length();
int nl = 4 - x;
while (nl > 0){
pubno = pubno + " ";
nl--;
}
}
cModel.addElement(pubno + "- " + pubName);
}
} catch (Exception e) {
JOptionPane.showMessageDialog(this,e.getMessage());
53
e.printStackTrace();
}
}
private void cmdExitActionPerformed(java.awt.event.ActionEvent
evt) {
this.setVisible(false);
}
private void jList1MouseClicked(java.awt.event.MouseEvent evt) {
// getSelectedValue() method extracts the current cursor location
value into a variable
String MBook = (String) jList1.getSelectedValue();
// Extract the first 4 characters as Accession number into a variable
String Acno =MBook.trim().substring(0, 3);
String query = "SELECT * FROM Lib WHERE acc_no = " + Acno + ";";
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con = (Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
// Create SQL statement and execute query.
stmt = con.createStatement();
rs = stmt.executeQuery(query);
if (rs.next()) {
String bTitle = rs.getString("btitle");
String Auth1 = rs.getString("author1");
String Auth2 = rs.getString("author2");
String PID = rs.getString("pub_id");
String Price = rs.getString("price");
String PDate = rs.getString("pdate");
String pages = rs.getString("pages");
String edition = rs.getString("edition");
String PStatus = rs.getString("status");
54
// Displaying the contents in respective text boxes.
txtAcno.setText(Acno);
txtBTitle.setText(bTitle);
txtAuth1.setText(Auth1);
txtAuth2.setText(Auth2);
txtPID.setText(PID);
txtPrice.setText(Price);
txtPDate.setText(PDate);
txtPages.setText(pages);
txtEdition.setText(edition);
txtStatus.setText(PStatus);
// Close the operational object for Student
con.close();
stmt.close();
rs.close();
} else {
JOptionPane.showMessageDialog(null, "Record does not
found in Student table");
}
} catch (Exception e) {
JOptionPane.showMessageDialog(this, e.getMessage());
}
}
55
}*/
}
Frame: LibDelUI.java
Coding of LibDelUI.Java
/* * LibDelUI.java **/
import java.sql.*;
import javax.swing.JOptionPane;
import javax.swing.DefaultListModel;
public class LibDelUI extends javax.swing.JFrame {
/** Creates new form LibDelUI */
public LibDelUI() {
initComponents();
}
Statement stmt = null;
ResultSet rs = null;
String SQL = "SELECT * FROM Lib";
56
private void cmdDeleteActionPerformed(java.awt.event.ActionEvent
evt) {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con = (Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
stmt = con.createStatement();
rs = stmt.executeQuery(SQL);
int Ano = Integer.parseInt(txtAcno.getText().trim());
// Steps to confirm deletion
int opt = JOptionPane.showConfirmDialog(null, "Are you sure to
delete this record ?");
if (opt == JOptionPane.YES_OPTION)
{
try {
if (txtStatus.getText().contains("Y"))
{
String strSQL = "Delete from Lib where acc_no = " +
(Ano);
int rowsEffected = stmt.executeUpdate(strSQL);
if (rowsEffected == 0)
JOptionPane.showMessageDialog(this, "Record does
not exists");
else
{
JOptionPane.showMessageDialog(this,"Record
Deleted");
}
}
else
57
JOptionPane.showMessageDialog(this,"Record cannot be
deleted, because status is 'N'");
} catch (Exception e) {
JOptionPane.showMessageDialog(null, "Unable to delete");
}
}
con.close();
stmt.close();
rs.close();
} catch (Exception e) {
JOptionPane.showMessageDialog(this, e.getMessage());
}
}
private void cmdExitActionPerformed(java.awt.event.ActionEvent
evt) {
this.setVisible(false);
}
private void jList1MouseClicked(java.awt.event.MouseEvent evt) {
// getSelectedValue() method extracts the current cursor location
value into a variable
String MBook = (String) jList1.getSelectedValue();
// Extract the first 4 characters as Accession number into a variable
String Acno =MBook.trim().substring(0, 3);
String query = "SELECT * FROM Lib WHERE acc_no = " + Acno + ";";
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con = (Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
// Create SQL statement and execute query.
stmt = con.createStatement();
rs = stmt.executeQuery(query);
58
if (rs.next()) {
String bTitle = rs.getString("btitle");
String Auth1 = rs.getString("author1");
String Auth2 = rs.getString("author2");
String PID = rs.getString("pub_id");
String Price = rs.getString("price");
String PDate = rs.getString("pdate");
String pages = rs.getString("pages");
String edition = rs.getString("edition");
String PStatus = rs.getString("status");
// Displaying the contents in respective text boxes.
txtAcno.setText(Acno);
txtBTitle.setText(bTitle);
txtAuth1.setText(Auth1);
txtAuth2.setText(Auth2);
txtPID.setText(PID);
txtPrice.setText(Price);
txtPDate.setText(PDate);
txtPages.setText(pages);
txtEdition.setText(edition);
txtStatus.setText(PStatus);
// Close the operational object for Student
con.close();
stmt.close();
rs.close();
} else {
JOptionPane.showMessageDialog(null, "Record does not
found in Student table");
}
} catch (Exception e) {
JOptionPane.showMessageDialog(this, e.getMessage());
59
}
}
private void formWindowGainedFocus(java.awt.event.WindowEvent
evt) {
txtAcno.setEditable(false);
txtPID.setEditable(false);
txtStatus.setEditable(false);
// Creating a ListModel object dModel to perform
DefaultListModel
// method operations
DefaultListModel dModel = (DefaultListModel) jList1.getModel();
// Method to add elements into jList1 control
dModel.clear();
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con = (Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
stmt = con.createStatement();
rs = stmt.executeQuery(SQL);
while (rs.next()) {
String Ano = rs.getString("acc_no");
String BTitle = rs.getString("btitle");
// To make the Accession no. as 4 digit because we will extract
4 digit from list value
// in mouse click event.
if (Ano.length() < 4)
{
int x = Ano.length();
int nl = 4 - x;
while (nl > 0){
60
Ano = Ano + " ";
nl--;
}
}
dModel.addElement(Ano + "- " + BTitle);
}
jList1.setModel(dModel);
} catch (Exception e) {
JOptionPane.showMessageDialog(this,e.getMessage());
e.printStackTrace();
}
}
/** * @param args the command line arguments */
public static void main(String args[]) {
java.awt.EventQueue.invokeLater(new Runnable() {
public void run() {
new LibDelUI().setVisible(true);
}
});
}
}
Frame: IssueUI.java
61
Coding for IssueUI.java
/* * IssueUI.java **/
import java.sql.*;
import javax.swing.JOptionPane;
import javax.swing.DefaultListModel;
public class IssueUI extends javax.swing.JFrame {
/** Creates new form IssueUI */
public IssueUI() {
initComponents();
Statement stmt = null;
ResultSet rs = null;
char st1 = 'N';
char ms = 'Y';
String SQL = "SELECT * FROM Member WHERE mem_issue = '" + st1 +
"' and mem_status ='" + ms + "'";
Statement stmt1 = null;
62
ResultSet rs1 = null;
char st = 'Y'; // Lib table
String SQL1 = "SELECT * FROM Lib WHERE status = '" + st + "';";
64
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
// Listing Members
stmt = con.createStatement();
rs = stmt.executeQuery(SQL);
while (rs.next()) {
String Mno = rs.getString("memb_no");
String MName = rs.getString("memb_name");
// To make the Member no. as 4 digit because we will extract
4 digit from list value
// in mouse click event.
if (Mno.length() < 4)
{
int x = Mno.length();
int nl = 4 - x;
while (nl > 0){
Mno = Mno + " ";
nl--;
}
}
dModel.addElement(Mno + "- " + MName);
}
jList1.setModel(dModel);
// cModel to perform DefaultListModel for Library
// method operations
DefaultListModel cModel = (DefaultListModel) jList2.getModel();
// Method to add elements into jList1 control for Library
cModel.clear();
// Listing books
stmt1 = con.createStatement();
rs1 = stmt1.executeQuery(SQL1);
65
while (rs1.next()) {
String Ano = rs1.getString("acc_no");
String BTitle = rs1.getString("btitle");
// To make the Accession no. as 4 digit because we will extract
4 digit from list value
// in mouse click event.
if (Ano.length() < 4)
{
int x = Ano.length();
int nl = 4 - x;
while (nl > 0){
Ano = Ano + " ";
nl--;
}
}
cModel.addElement(Ano + "- " + BTitle);
}
jList2.setModel(cModel);
} catch (Exception e) {
JOptionPane.showMessageDialog(this,e.getMessage());
e.printStackTrace();
}
}
private void cmdExitActionPerformed(java.awt.event.ActionEvent
evt) {
this.setVisible(false);
}
private void jList2MouseClicked(java.awt.event.MouseEvent evt) {
// getSelectedValue() method extracts the current cursor location
value into a variable
String MBook = (String) jList2.getSelectedValue();
66
// Extract the first 4 characters as Accession number into a variable
String Acno =MBook.trim().substring(0, 3);
String query = "SELECT * FROM Lib WHERE acc_no = " + Acno + ";";
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con = (Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
// Create SQL statement and execute query.
stmt1 = con.createStatement();
rs1 = stmt1.executeQuery(query);
if (rs1.next()) {
String bTitle = rs1.getString("btitle");
String Auth1 = rs1.getString("author1");
String PID = rs1.getString("pub_id");
String PStatus = rs1.getString("status");
// Displaying the contents in respective text boxes.
txtAcno.setText(Acno);
txtBTitle.setText(bTitle);
txtAuth1.setText(Auth1);
txtPID.setText(PID);
txtStatus.setText(PStatus);
} else {
JOptionPane.showMessageDialog(null, "Record does not
found in Libdent table");
}
} catch (Exception e) {
JOptionPane.showMessageDialog(this, e.getMessage());
}
}
private void cmdIssueActionPerformed(java.awt.event.ActionEvent
evt) {
67
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con = (Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
// Missue table
stmt2 = con.createStatement();
rs2 = stmt.executeQuery(SQL);
// Tissue table
stmt3 = con.createStatement();
rs3 = stmt.executeQuery(SQL);
int Acno = Integer.parseInt(txtAcno.getText().trim());
int Mno = Integer.parseInt(txtMNo.getText().trim());
String idt = txtIDate.getText();
String rat = idt; // Temporarily assigned for testing purpose
int code = JOptionPane.showConfirmDialog(this, "Are you sure
to add?", "Confirmation Dialog Box",
JOptionPane.YES_NO_CANCEL_OPTION,
JOptionPane.INFORMATION_MESSAGE);
if (code == JOptionPane.YES_OPTION) {
// Record updated into Missue and Tissue tables
String strSQL = "INSERT INTO Missue(acc_no, memb_no, idate,
rdate) VALUES ("+(Acno)+", "+(Mno)+", '"+(idt)+"', '"+(rat)+"')";
String strSQL1 = "INSERT INTO Tissue(acc_no, memb_no,
idate, rdate) VALUES ("+(Acno)+", "+(Mno)+", '"+(idt)+"', '"+(rat)+"')";
stmt2.executeUpdate(strSQL);
stmt3.executeUpdate(strSQL1);
// Change the status as library book issued
char sta = 'N'; // Lib table
String strSQLa = "Update Lib set status ='"+(sta)+"' where
acc_no = " + (Acno);
68
stmt1.executeUpdate(strSQLa);
// Change the status as Member has a book
char stb = 'Y'; // Member table
String strSQLb = "Update Member set mem_issue ='"+(stb)+"'
where memb_no = " + (Mno);
stmt.executeUpdate(strSQLb);
JOptionPane.showMessageDialog(this, "Record update
successfully");
}
} catch (Exception e) {
JOptionPane.showMessageDialog(this, e.getMessage());
}
}
/** * @param args the command line arguments */
public static void main(String args[]) {
java.awt.EventQueue.invokeLater(new Runnable() {
public void run() {
new IssueUI().setVisible(true);
}
});
}
Frame: ReturnUI.java
69
Coding for ReturnUI.java
/* * ReturnUI.java **/
import java.sql.*;
import javax.swing.JOptionPane;
import javax.swing.DefaultListModel;
public class ReturnUI extends javax.swing.JFrame {
/** Creates new form ReturnUI */
public ReturnUI() {
initComponents();
}
Statement stmt = null;
ResultSet rs = null;
char st1 = 'Y';
String SQL = "SELECT * FROM Member WHERE mem_issue = '" + st1 +
"';";
Statement stmt1 = null;
ResultSet rs1 = null;
char st = 'Y'; // Lib table
String SQL1 = "SELECT * FROM Lib WHERE status = '" + st + "';";
Statement stmt2 = null;
70
ResultSet rs2 = null;
String SQL2 = "SELECT * FROM Missue";
Statement stmt3 = null;
ResultSet rs3 = null;
String SQL3 = "SELECT * FROM Tissue";
private void jList1MouseClicked(java.awt.event.MouseEvent evt) {
// getSelectedValue() method extracts the current cursor location
value into a variable
String MembNo = (String) jList1.getSelectedValue();
// Extract the first 4 characters as Member No into a variable
String Mno =MembNo.trim().substring(0, 3);
String query = "SELECT * FROM Member WHERE memb_no = " +
(Mno) + ";";
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con = (Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
// Create SQL statement and execute query.
stmt = con.createStatement();
rs = stmt.executeQuery(query);
if (rs.next()) {
String MName = rs.getString("memb_name");
String MDate = rs.getString("mdate");
String MStatus = rs.getString("mem_status");
// Displaying the contents in respective text boxes.
txtMNo.setText(Mno);
txtMName.setText(MName);
txtMDate.setText(MDate);
txtMStatus.setText(MStatus);
} else {
71
JOptionPane.showMessageDialog(null, "Record does not
found in Member table");
}
// Extrating the Acc_no from Tissue table to find book details in
Lib table
String query1 = "SELECT * FROM Tissue WHERE memb_no = " +
(Mno) + ";";
// Create SQL statement and execute query.
stmt3 = con.createStatement();
rs3 = stmt3.executeQuery(query1);
int ACno = 0;
if (rs3.next()) {
ACno = rs3.getInt("acc_no");
txtAcno.setText(String.valueOf(ACno));
}
// Extrating the Library details for Acc_no from Lib table
String query2 = "SELECT * FROM Lib WHERE acc_no = " + (ACno)
+ ";";
// Create SQL statement and execute query.
stmt1 = con.createStatement();
rs1 = stmt1.executeQuery(query2);
String BTitle, Auth;
if (rs1.next()) {
BTitle = rs1.getString("btitle");
Auth = rs1.getString("author1");
txtBTitle.setText(BTitle);
txtAuth.setText(Auth);
jLabel9.setText("Issued");
}
} catch (Exception e) {
JOptionPane.showMessageDialog(this, e.getMessage());
}
72
}
private void formWindowGainedFocus(java.awt.event.WindowEvent
evt) {
txtMNo.setEditable(false);
txtMName.setEditable(false);
txtMDate.setEditable(false);
txtMStatus.setEditable(false);
txtAcno.setEditable(false);
txtBTitle.setEditable(false);
txtAuth.setEditable(false);
// dModel to perform DefaultListModel for Member
// method operations
DefaultListModel dModel = (DefaultListModel) jList1.getModel();
// Method to add elements into jList1 control for member
dModel.clear();
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con = (Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
// Listing Members
stmt = con.createStatement();
rs = stmt.executeQuery(SQL);
while (rs.next()) {
String Mno = rs.getString("memb_no");
String MName = rs.getString("memb_name");
// To make the Member no. as 4 digit because we will extract
4 digit from list value
// in mouse click event.
if (Mno.length() < 4)
{
73
int x = Mno.length();
int nl = 4 - x;
while (nl > 0){
Mno = Mno + " ";
nl--;
}
}
dModel.addElement(Mno + "- " + MName);
}
jList1.setModel(dModel);
} catch (Exception e) {
JOptionPane.showMessageDialog(this,e.getMessage());
e.printStackTrace();
}
}
private void cmdExitActionPerformed(java.awt.event.ActionEvent
evt) {
this.setVisible(false);
}
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
// Missue table
int Mno = 0, Acno = 0;
Mno = Integer.parseInt(txtMNo.getText().trim());
Acno = Integer.parseInt(txtAcno.getText().trim());
74
// Steps to confirm return
int opt = JOptionPane.showConfirmDialog(null, "Are you sure to
return this book ?");
if (opt == JOptionPane.YES_OPTION)
{
String strSQLr = "Delete from Tissue where memb_no = " +
(Mno);
stmt3.executeUpdate(strSQLr);
// Change the status as library book issued
char sta = 'Y'; // Lib table
String strSQLa = "Update Lib set status ='"+(sta)+"' where
acc_no = " + (Acno);
stmt1.executeUpdate(strSQLa);
// Change the status as Member has a book
char stb = 'N'; // Member table
String strSQLb = "Update Member set mem_issue ='"+(stb)+"'
where memb_no = " + (Mno);
stmt.executeUpdate(strSQLb);
JOptionPane.showMessageDialog(this, "Thanks for returning
book");
}
} catch (Exception e) {
JOptionPane.showMessageDialog(this, e.getMessage());
}
}
/** * @param args the command line arguments */
public static void main(String args[]) {
java.awt.EventQueue.invokeLater(new Runnable() {
public void run() {
new ReturnUI().setVisible(true);
}
});
75
}
}
76
Frame: MembUI.java
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
stmt = con.createStatement();
rs = stmt.executeQuery(SQL);
int Mno = Integer.parseInt(txtMNo.getText());
String MName = txtMName.getText();
String MAdd = txtMAdd.getText();
String MPh1 = txtMPh.getText();
String MDate = txtMDate.getText();
String MEDate = txtMDate.getText(); // This is temporarily
assigned same as membership date
double MFee = Double.parseDouble(txtMFee.getText());
char MStatus = 'Y'; // Member status is Y
78
char MIssue = 'N'; // Book issue status is N
int code = JOptionPane.showConfirmDialog(this, "Are you sure
to add?", "Confirmation Dialog Box",
JOptionPane.YES_NO_CANCEL_OPTION,
JOptionPane.INFORMATION_MESSAGE);
if (code == JOptionPane.YES_OPTION) {
String strSQL = "INSERT INTO Member(memb_no,
memb_name, memb_add, memb_phone, mdate, medate, mfee,
mem_status, mem_issue) VALUES ("+(Mno)+", '"+(MName)+"', '"+
(MAdd)+"', '"+(MPh1)+"', '"+(MDate)+"', '"+(MEDate)+"', "+(MFee)+",
'"+(MStatus)+"', '"+(MIssue)+"')";
int rowsEffected = stmt.executeUpdate(strSQL);
JOptionPane.showMessageDialog(this, "Record added
successfully into Member table");
}
cmdSave.setEnabled(false);
} catch (Exception e) {
JOptionPane.showMessageDialog(this, e.getMessage());
}
}
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
79
stmt = con.createStatement(); // Connection string for
ResultSet - rs.
rs = stmt.executeQuery(SQL);
int MNO=0, Mno = 0;
while (rs.next()) {
MNO = rs.getInt("memb_no");
}
MNO++;
Mno = MNO;
txtMNo.setText(Integer.toString(Mno));
txtMName.setFocusable(true);
} catch (Exception e) {
JOptionPane.showMessageDialog(this,e.getMessage());
e.printStackTrace();
}
}
private void formWindowGainedFocus(java.awt.event.WindowEvent
evt) {
txtMNo.setEditable(false);
txtMStatus.setEditable(false);
// Deactivate the Save button when form loads
cmdSave.setEnabled(false);
}
private void cmdDelActionPerformed(java.awt.event.ActionEvent
evt) {
this.setVisible(false);
new MembDelUI().setVisible(true);
}
/** * @param args the command line arguments */
public static void main(String args[]) {
java.awt.EventQueue.invokeLater(new Runnable() {
public void run() {
80
new MembUI().setVisible(true);
}
});
}
}
Frame: MembEditUI.java
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
stmt = con.createStatement();
rs = stmt.executeQuery(SQL);
int Mno = Integer.parseInt(txtMNo.getText().trim());
String MName = txtMName.getText();
String MAdd = txtMAdd.getText();
String MPh1 = txtMPh.getText();
String MDate = txtMDate.getText();
String MEDate = txtMDate.getText(); // This is temporarily
assigned same as membership date
double MFee = Double.parseDouble(txtMFee.getText());
char MStatus = 'Y'; // Member status is Y
String strSQL = "Update Member set memb_name ='"+(MName)
+"', memb_add = '"+(MAdd)+"', memb_phone = '"+(MPh1)+"', mdate =
'"+(MDate)+"', mfee = "+(MFee)+" where memb_no = " + (Mno);
int rowsEffected = stmt.executeUpdate(strSQL);
if (rowsEffected == 0)
JOptionPane.showMessageDialog(this, "Record does not
exists");
else
82
JOptionPane.showMessageDialog(this,"Record modified");
} catch (Exception e) {
JOptionPane.showMessageDialog(this, e.getMessage());
}
}
private void jList1MouseClicked(java.awt.event.MouseEvent evt) {
// getSelectedValue() method extracts the current cursor location
value into a variable
String MembNo = (String) jList1.getSelectedValue();
// Extract the first 4 characters as Member No into a variable
String Mno =MembNo.trim().substring(0, 3);
String query = "SELECT * FROM Member WHERE memb_no = " +
(Mno) + ";";
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con = (Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
// Create SQL statement and execute query.
stmt = con.createStatement();
rs = stmt.executeQuery(query);
if (rs.next()) {
String MName = rs.getString("memb_name");
String MAdd = rs.getString("memb_add");
String MPh1 = rs.getString("memb_phone");
String MDate = rs.getString("mdate");
String MEDate = rs.getString("medate");
String MFee = rs.getString("mfee");
String MStatus = rs.getString("mem_status");
String MIssue = rs.getString("mem_issue");
// Displaying the contents in respective text boxes.
txtMNo.setText(Mno);
83
txtMName.setText(MName);
txtMAdd.setText(MAdd);
txtMPh.setText(MPh1);
txtMDate.setText(MDate);
txtMFee.setText(MFee);
txtMStatus.setText(MStatus);
txtMStatus.setEditable(false);
} else {
JOptionPane.showMessageDialog(null, "Record does not
found in Member table");
}
} catch (Exception e) {
JOptionPane.showMessageDialog(this, e.getMessage());
}
}
private void formWindowGainedFocus(java.awt.event.WindowEvent
evt) {
txtMNo.setEditable(false);
txtMStatus.setEditable(false);
// Creating a ListModel object dModel to perform
DefaultListModel
// method operations
DefaultListModel dModel = (DefaultListModel) jList1.getModel();
// Method to add elements into jList1 control
dModel.clear();
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con = (Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
stmt = con.createStatement();
84
rs = stmt.executeQuery(SQL);
while (rs.next()) {
String Mno = rs.getString("memb_no");
String MName = rs.getString("memb_name");
// To make the Member no. as 4 digit because we will extract
4 digit from list value
// in mouse click event.
if (Mno.length() < 4)
{
int x = Mno.length();
int nl = 4 - x;
while (nl > 0){
Mno = Mno + " ";
nl--;
}
}
dModel.addElement(Mno + "- " + MName);
}
jList1.setModel(dModel);
} catch (Exception e) {
JOptionPane.showMessageDialog(this,e.getMessage());
e.printStackTrace();
}
}
/** * @param args the command line arguments */
public static void main(String args[]) {
java.awt.EventQueue.invokeLater(new Runnable() {
public void run() {
new MembEditUI().setVisible(true);
}
});
85
}
}
Frame: MembDelUI.java
86
// getSelectedValue() method extracts the current cursor location
value into a variable
String MembNo = (String) jList1.getSelectedValue();
// Extract the first 4 characters as Member No into a variable
String Mno =MembNo.trim().substring(0, 3);
String query = "SELECT * FROM Member WHERE memb_no = " +
(Mno) + ";";
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con = (Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
// Create SQL statement and execute query.
stmt = con.createStatement();
rs = stmt.executeQuery(query);
if (rs.next()) {
String MName = rs.getString("memb_name");
String MAdd = rs.getString("memb_add");
String MPh1 = rs.getString("memb_phone");
String MDate = rs.getString("mdate");
String MEDate = rs.getString("medate");
String MFee = rs.getString("mfee");
String MStatus = rs.getString("mem_status");
String MIssue = rs.getString("mem_issue");
// Displaying the contents in respective text boxes.
txtMNo.setText(Mno);
txtMName.setText(MName);
txtMAdd.setText(MAdd);
txtMPh.setText(MPh1);
txtMDate.setText(MDate);
txtMFee.setText(MFee);
txtMStatus.setText(MStatus);
87
txtMStatus.setEditable(false);
} else {
JOptionPane.showMessageDialog(null, "Record does not
found in Member table");
}
} catch (Exception e) {
JOptionPane.showMessageDialog(this, e.getMessage());
}
}
private void cmdExit1ActionPerformed(java.awt.event.ActionEvent
evt) {
this.setVisible(false);
//new MainUI().setVisible(true);
}
private void cmdDeleteActionPerformed(java.awt.event.ActionEvent
evt) {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con = (Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
stmt = con.createStatement();
rs = stmt.executeQuery(SQL);
int Mno = Integer.parseInt(txtMNo.getText().trim());
// Steps to confirm deletion
int opt = JOptionPane.showConfirmDialog(null, "Are you sure to
delete this record ?");
if (opt == JOptionPane.YES_OPTION) {
try {
char stb = 'N'; // Member table
88
String strSQL = "Update Member set mem_status ='"+(stb)
+"' where memb_no = " + (Mno);
int rowsEffected = stmt.executeUpdate(strSQL);
if (rowsEffected == 0)
JOptionPane.showMessageDialog(this, "Record does not
exists");
else {
JOptionPane.showMessageDialog(this,"Record Deleted");
// Text boxes cleared
txtMNo.setText("");
txtMName.setText("");
txtMAdd.setText("");
txtMPh.setText("");
txtMDate.setText("");
txtMFee.setText("");
txtMStatus.setText("");
}
} catch (Exception e) {
JOptionPane.showMessageDialog(null, "Unable to delete");
}
}
} catch (Exception e) {
JOptionPane.showMessageDialog(this, e.getMessage());
}
}
private void formWindowGainedFocus(java.awt.event.WindowEvent
evt) {
txtMNo.setEditable(false);
txtMName.setEditable(false);
txtMAdd.setEditable(false);
txtMPh.setEditable(false);
txtMDate.setEditable(false);
89
txtMFee.setEditable(false);
txtMStatus.setEditable(false);
// Creating a ListModel object dModel to perform
DefaultListModel
// method operations
DefaultListModel dModel = (DefaultListModel) jList1.getModel();
// Method to add elements into jList1 control
dModel.clear();
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con = (Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
stmt = con.createStatement();
rs = stmt.executeQuery(SQL);
while (rs.next()) {
String Mno = rs.getString("memb_no");
String MName = rs.getString("memb_name");
// To make the Member no. as 4 digit because we will extract
4 digit from list value
// in mouse click event.
if (Mno.length() < 4)
{
int x = Mno.length();
int nl = 4 - x;
while (nl > 0){
Mno = Mno + " ";
nl--;
}
}
dModel.addElement(Mno + "- " + MName);
90
}
jList1.setModel(dModel);
} catch (Exception e) {
JOptionPane.showMessageDialog(this,e.getMessage());
e.printStackTrace();
}
}
/** * @param args the command line arguments */
public static void main(String args[]) {
java.awt.EventQueue.invokeLater(new Runnable() {
public void run() {
new MembDelUI().setVisible(true);
}
});
}
}
Frame: MembNavUI.java
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
if (rs.previous()) {
String Mno = rs.getString("memb_no");
92
String MName = rs.getString("memb_name");
String MAdd = rs.getString("memb_add");
String MPh1 = rs.getString("memb_phone");
String MDate = rs.getString("mdate");
String MEDate = rs.getString("medate");
String MFee = rs.getString("mfee");
String MStatus = rs.getString("mem_status");
String MIssue = rs.getString("mem_issue");
// Displaying the contents in respective text boxes.
txtMNo.setText(Mno);
txtMName.setText(MName);
txtMAdd.setText(MAdd);
txtMPh.setText(MPh1);
txtMDate.setText(MDate);
txtMFee.setText(MFee);
txtMStatus.setText(MStatus);
txtMIssue.setText(MIssue);
cmdFirst.setEnabled(true);
cmdNext.setEnabled(true);
cmdPrev.setEnabled(true);
cmdLast.setEnabled(true);
} else {
cmdPrev.setEnabled(false);
JOptionPane.showMessageDialog(this, "You are at first
position", "Student",0);
}
} catch (Exception e) {
JOptionPane.showMessageDialog(this, e.getMessage());
}
}
private void cmdLastActionPerformed(java.awt.event.ActionEvent
evt) {
93
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con = (Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
if (rs.last()) {
String Mno = rs.getString("memb_no");
String MName = rs.getString("memb_name");
String MAdd = rs.getString("memb_add");
String MPh1 = rs.getString("memb_phone");
String MDate = rs.getString("mdate");
String MEDate = rs.getString("medate");
String MFee = rs.getString("mfee");
String MStatus = rs.getString("mem_status");
String MIssue = rs.getString("mem_issue");
// Displaying the contents in respective text boxes.
txtMNo.setText(Mno);
txtMName.setText(MName);
txtMAdd.setText(MAdd);
txtMPh.setText(MPh1);
txtMDate.setText(MDate);
txtMFee.setText(MFee);
txtMStatus.setText(MStatus);
txtMIssue.setText(MIssue);
cmdFirst.setEnabled(true);
cmdNext.setEnabled(false);
cmdPrev.setEnabled(true);
cmdLast.setEnabled(false);
} else {
JOptionPane.showMessageDialog(this, "You are already at last
record", "Student",0);
94
}
} catch (Exception e) {
JOptionPane.showMessageDialog(this, e.getMessage());
}
}
private void cmdNextActionPerformed(java.awt.event.ActionEvent
evt) {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con = (Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
if (rs.next()) {
String Mno = rs.getString("memb_no");
String MName = rs.getString("memb_name");
String MAdd = rs.getString("memb_add");
String MPh1 = rs.getString("memb_phone");
String MDate = rs.getString("mdate");
String MEDate = rs.getString("medate");
String MFee = rs.getString("mfee");
String MStatus = rs.getString("mem_status");
String MIssue = rs.getString("mem_issue");
// Displaying the contents in respective text boxes.
txtMNo.setText(Mno);
txtMName.setText(MName);
txtMAdd.setText(MAdd);
txtMPh.setText(MPh1);
txtMDate.setText(MDate);
txtMFee.setText(MFee);
txtMStatus.setText(MStatus);
txtMIssue.setText(MIssue);
95
cmdFirst.setEnabled(true);
cmdNext.setEnabled(true);
cmdPrev.setEnabled(true);
cmdLast.setEnabled(true);
} else {
cmdNext.setEnabled(false);
JOptionPane.showMessageDialog(this, "You are at last record
position", "Student",0);
}
} catch (Exception e) {
JOptionPane.showMessageDialog(this, e.getMessage());
}
}
private void cmdExitActionPerformed(java.awt.event.ActionEvent
evt) {
this.setVisible(false);
}
private void cmdFirstActionPerformed(java.awt.event.ActionEvent
evt) {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con = (Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
if (rs.first()) {
String Mno = rs.getString("memb_no");
String MName = rs.getString("memb_name");
String MAdd = rs.getString("memb_add");
String MPh1 = rs.getString("memb_phone");
String MDate = rs.getString("mdate");
String MEDate = rs.getString("medate");
96
String MFee = rs.getString("mfee");
String MStatus = rs.getString("mem_status");
String MIssue = rs.getString("mem_issue");
// Displaying the contents in respective text boxes.
txtMNo.setText(Mno);
txtMName.setText(MName);
txtMAdd.setText(MAdd);
txtMPh.setText(MPh1);
txtMDate.setText(MDate);
txtMFee.setText(MFee);
txtMStatus.setText(MStatus);
txtMIssue.setText(MIssue);
cmdFirst.setEnabled(false);
cmdNext.setEnabled(true);
cmdPrev.setEnabled(false);
cmdLast.setEnabled(true);
} else {
cmdFirst.setEnabled(false);
cmdNext.setEnabled(false);
cmdPrev.setEnabled(false);
cmdLast.setEnabled(false);
JOptionPane.showMessageDialog(this, "Rhere is no record in
table", "Student",0);
}
} catch (Exception e) {
JOptionPane.showMessageDialog(this, e.getMessage());
}
}
private void formWindowGainedFocus(java.awt.event.WindowEvent
evt) {
disable_textfields();
try {
97
Class.forName("com.mysql.jdbc.Driver");
Connection con = (Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
stmt = con.createStatement();
rs = stmt.executeQuery(SQL);
if (rs.first())
{
String Mno = rs.getString("memb_no");
String MName = rs.getString("memb_name");
String MAdd = rs.getString("memb_add");
String MPh1 = rs.getString("memb_phone");
String MDate = rs.getString("mdate");
String MEDate = rs.getString("medate");
String MFee = rs.getString("mfee");
String MStatus = rs.getString("mem_status");
String MIssue = rs.getString("mem_issue");
// Displaying the contents in respective text boxes.
txtMNo.setText(Mno);
txtMName.setText(MName);
txtMAdd.setText(MAdd);
txtMPh.setText(MPh1);
txtMDate.setText(MDate);
txtMFee.setText(MFee);
txtMStatus.setText(MStatus);
txtMIssue.setText(MIssue);
cmdFirst.setEnabled(false);
cmdNext.setEnabled(true);
cmdPrev.setEnabled(false);
cmdLast.setEnabled(true);
}
98
else
{
cmdFirst.setEnabled(false);
cmdNext.setEnabled(false);
cmdPrev.setEnabled(false);
cmdLast.setEnabled(false);
JOptionPane.showMessageDialog(this, "Rhere is no record in
table", "Student",0);
}
} catch (Exception e) {
JOptionPane.showMessageDialog(this, e.getMessage());
}
}
/** * @param args the command line arguments */
public static void main(String args[]) {
java.awt.EventQueue.invokeLater(new Runnable() {
public void run() {
new MembNavUI().setVisible(true);
}
});
}
}
99
Frame: ABListUI.java
100
for (int i = 0; i < rows; i++) {
model.removeRow(0);
}
}
// SQL Query
char st = 'Y'; // Lib table
String query = "SELECT * FROM Lib WHERE status = '" + st + "';";
try {
// Connect to MySQL database
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con = (Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
// Create SQL statement and execute query.
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(query);
102
Connection con = (Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
// Create SQL statement and execute query.
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(query);
// Iterate through the result and display on screen
while (rs.next()) {
String Acno = rs.getString("acc_no");
String bTitle = rs.getString("btitle");
String Auth1 = rs.getString("author1");
String Price = rs.getString("price");
String edition = rs.getString("edition");
//System.out.println(Acno + "|" + bTitle + "|" + Auth1 + "|" +
Price + "|" + edition);
Dmodel.addRow(new Object[] {Acno, bTitle, Auth1, Price,
edition});
}
}
catch (Exception e) {
JOptionPane.showMessageDialog(this, e.getMessage());
}
}
/** * @param args the command line arguments */
public static void main(String args[]) {
java.awt.EventQueue.invokeLater(new Runnable() {
public void run() {
new ABListUI().setVisible(true);
}
});
}
}
103
Frame: IBListUI.java
Frame: MListUI.java
107
Connection con = (Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/Library","r
oot","raj");
// Create SQL statement and execute query.
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(query);
// Iterate through the result and display on screen
while (rs.next()) {
String Mno = rs.getString("memb_no");
String MName = rs.getString("memb_name");
String MAdd = rs.getString("memb_add");
String MPh1 = rs.getString("memb_phone");
String MDate = rs.getString("mdate");
//System.out.println(Mno + "|" + MName + "|" + MAdd + "|"
+ MPh1 + "|" + MDate);
model.addRow(new Object[] {Mno, MName, MAdd, MPh1,
MDate});
}
}
catch (Exception e) {
JOptionPane.showMessageDialog(this, e.getMessage());
}
}
109
7. User Manual
Software Requirement-
Windows 2000/XP OS is desirable.
NetBeans Ver 5.1 or higher should be installed with JDK and JVM.
MySQL Ver 6.1 with Library Database must be present at
machine.
Database Installation
Note: The PC must have MySQL server with user (root) and password
(raj) . If root password is any other password, it can be changed by
running MySQL Server Instance Configure Wizard.
110
Provide current password of root and new password as “raj” , this will
change the root password.
111
Publisher:
This menu item gives options to insert, delete, update and access the
Publisher record.
Book:
This menu gives options to Insert, delete, modify and delete the Books
record. Also the Books can be Issued and Received to/from members.
Members:
This menu item gives options to insert, delete, update and access the
Member’s record.
Report:
This menu provides option to get reports of Issued books, Available
books and Members list.
112
8. References
In order to work on this project titled -LibSys – Public Library
Management System, the following books and literature are refered
by me during the various phases of development of the project.
(4) http://www.netbeans.org/
113