Library Management System Project with Java Swing MySQL with Netbeans IDE

This is a sample java project written in Java & MySQL. So, you need following requirement to continue.

Java
MySQL
JDBC Connector for Mysql
Netbeans IDE
MySQL Workbench (Optional)

System Requirements

Administrators login
Administrator can add,edit,delete,view,search & issue books to the users
Administrator can register,edit,delete,view & search users.

Entity Model - Using MySQL Workbench

Database Setup

Now let's login to MySQL server and create database "library_db".

CREATE DATABASE library_db ;

USE library_db

CREATE TABLE `login` (
  `id` int(11) NOT NULL,
  `user_name` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `book` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `book_code` varchar(60) DEFAULT NULL,
  `category` varchar(255) DEFAULT NULL,
  `author` varchar(255) DEFAULT NULL,
  `price` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `identity_card_number` varchar(60) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `mobile` varchar(255) DEFAULT NULL,
  `gender` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `issue` (
  `id` int(11) NOT NULL,
  `book_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `login_id` int(11) NOT NULL,
  `issued_datetime` varchar(255) DEFAULT NULL,
  `period` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_issue_book1_idx` (`book_id`),
  KEY `fk_issue_login1_idx` (`login_id`),
  KEY `fk_issue_user1_idx` (`user_id`),
  CONSTRAINT `fk_issue_book1` FOREIGN KEY (`book_id`) REFERENCES `book` (`id`),
  CONSTRAINT `fk_issue_login1` FOREIGN KEY (`login_id`) REFERENCES `login` (`id`),
  CONSTRAINT `fk_issue_user1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB;

CREATE TABLE `return` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fine` decimal(10,2) DEFAULT NULL,
  `issue_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_return_issue1_idx` (`issue_id`),
  CONSTRAINT `fk_return_issue1` FOREIGN KEY (`issue_id`) REFERENCES `issue` (`id`)
) ENGINE=InnoDB;

This project consist with following parts.

Setup Library Project in Netbeans
Create MySQL database connection
User Login (Administrators)
Administrator Panel - Add,Edit,Delete,View,Search & Issue Books

Setup Library Project in Netbeans

Open Netbeans and create project called "Library". (File->New Project->Java Application)

Create MySQL database connection

To separate SQL command from GUI we create service package in the project and add DBService class into it and it look like follows

In my code following attribute are use to make the db connection.

Database: library_db
MySQL User Name : root
MySQL Password : root

DBService.java

package util;

package service;

import java.sql.*;

public class DBService {

    private static Connection conn = null;

    public static Connection connect() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        //System.out.println("Loaded driver");
        conn = DriverManager.getConnection("jdbc:mysql://localhost/library_db?user=root&password=root");
        //System.out.println("Connected to MySQL");
        return conn;
    }
}

Add JDBC Connector to the project class path.

First download JDBC Connector for MySQL from here according to your platform. In this project i'am using mysql-connector-java-8.0.16.jar file.
Then right click on Libraries Folder of the project and select Add JAR/Folder option. From the file browser window find and add downloaded jar to project class path.

Find more details about Add JDBC Connector to the project class path from here.

User Login - Admin

For this we need to create swing login form and add action listener to the login button.
In here we are creating package library under library project and add UserLogin class with login() method as following.

UserLogin.java

package library;

import java.awt.event.*;
import javax.swing.*;
import service.DBService;

public class UserLogin {

    public static void login() {

        JFrame frame = new JFrame("User Login");//JFrame
        frame.setSize(400, 200);//600 width and 500 height  
        frame.setLayout(null);//using no layout managers 
        JLabel lblUserName, lblPassword;
        lblUserName = new JLabel("Username");
        lblUserName.setBounds(30, 15, 100, 30);

        lblPassword = new JLabel("Password");
        lblPassword.setBounds(30, 50, 100, 30);

        JTextField txtUserName = new JTextField();
        txtUserName.setBounds(110, 15, 200, 30);
        txtUserName.setText("admin");

        JPasswordField txtPassword = new JPasswordField();
        txtPassword.setBounds(110, 50, 200, 30);
        txtPassword.setText("abc123");

        JButton btnLogin = new JButton("Login");
        btnLogin.setBounds(130, 90, 80, 25); 
        
        frame.add(lblUserName);
        frame.add(txtUserName);
        frame.add(lblPassword);
        frame.add(txtPassword);
        frame.add(btnLogin);

        btnLogin.addActionListener(new ActionListener() {  //Perform action

            public void actionPerformed(ActionEvent e) {

                String username = txtUserName.getText();
                String password = txtPassword.getText();

                //username password validation
                if (username.equals("")) //If username is empty
                {
                    JOptionPane.showMessageDialog(null, "Username cannot be empty");
                } else if (password.equals("")) //If password is empty
                {
                    JOptionPane.showMessageDialog(null, "Password cannot be empty");
                } else {
                    try {
                        //check user exist in the database
                        boolean isValidUser = DBService.validUser(username, password);
                        if (isValidUser) {
                            frame.dispose();
                            AdminDashboard ad = new AdminDashboard();
                            ad.show();
                        } else {
                            JOptionPane.showMessageDialog(frame, "Username/Password not match", "Login Failed", JOptionPane.ERROR_MESSAGE);

                        }
                    } catch (Exception ex) {
                        JOptionPane.showMessageDialog(frame, ex.getMessage(), "Error", JOptionPane.ERROR_MESSAGE);
                    }
                }
            }
        });

        frame.setVisible(true);
        frame.setLocationRelativeTo(null);
        frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

    }

}

In Line 55 DBService.validUser(username, password); method to check username & password exist in the database. Update DBService class as follows.

DBService.java

package service;

import java.sql.*;
import javax.swing.table.*;
import model.*;
import util.*;

public class DBService {

    private static Connection conn = null;
    private static PreparedStatement pstmt = null;
    private static ResultSet rs = null;
    private static String query;

    public static Connection connect() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        //System.out.println("Loaded driver");
        conn = DriverManager.getConnection("jdbc:mysql://localhost/library_db?user=root&password=root");
        //System.out.println("Connected to MySQL");
        return conn;
    }

    public static boolean validUser(String username, String password) throws ClassNotFoundException, SQLException {
        query = "SELECT * FROM LOGIN WHERE USER_NAME=? AND PASSWORD=?";
        pstmt = connect().prepareStatement(query);
        pstmt.setString(1, username);
        pstmt.setString(2, password);
        rs = pstmt.executeQuery();
        boolean isValidUser = rs.next();

        rs.close();
        pstmt.close();
        conn.close();

        return isValidUser;

    }
}

In Line 56 to 59 of UserLogin.java we close the User Login frame and opens AdminDashboard frame for valid users.

Tech Show 86

Search This Blog