Showing posts with label crud jdbc. Show all posts
Showing posts with label crud jdbc. Show all posts

Friday, August 18, 2017

Student Information System in Java JDBC

In this article I would like to share with you Student Information System in Java Using JDBC ( Java Database Connectivity). It will add, edit, delete and view student records. The records is being stored in MySQL Database. I hope you will learn something in this sample database application that I wrote using Java and MySQL.  I am using NetBeans 8.1 as my text editor in writing this program.

I hope you will find my work useful and beneficial. If you have some questions about programming, about my work please send mu an email at jakerpomperada@gmail.comand jakerpomperada@yahoo.com. People here in the Philippines can contact me at  my mobile number 09173084360.

Thank you very much and Happy Programming.













Sample Program Output


Complete Program Listing


DBConfiguration.java

package studentinfosystem;

import java.sql.*;

public class DBConfiguration {
    // database URL                              

    static final String DATABASE_URL = "jdbc:mysql://localhost/student";
    Connection connection = null;
    Statement statement = null; 
    ResultSet resultSet = null; 

    public DBConfiguration() throws SQLException {
        // establish connection to database  
        try {
            connection = DriverManager.getConnection(DATABASE_URL, "root", "");

        } catch (SQLException ex) {
            System.out.println("The following error has occured: " + ex.getMessage());
        }
    }

    public void DisconnectFromDB() {

        try {
            resultSet.close();
            statement.close();
            connection.close();
        } // end try                                               
        catch (Exception ex) {
            System.out.println("The following error has occured: " + ex.getMessage());
        } // end catch  
    }

    public ResultSet ReadRecords(String sql_stmt) {
        try {

            statement = connection.createStatement();
                                    
            resultSet = statement.executeQuery(sql_stmt);

            return resultSet;

        } 
        catch (SQLException ex) {
            System.out.println("The following error has occured: " + ex.getMessage());
        }                                                    

        return resultSet;
    }

    public void ExecuteSQLStatement(String sql_stmt) {
        try {
            statement = connection.createStatement();
                                    
            statement.executeUpdate(sql_stmt);
        } 
        catch (SQLException ex) {
            System.out.println("The following error has occured: " + ex.getMessage());
        }
    }
}


StudentInfoSystem.java

package studentinfosystem;

import java.sql.SQLException;
import java.util.Scanner;

public class StudentInfoSystem {

    public static void main(String[] args) throws SQLException {
        DisplayMenu();
    }

    public static void DisplayMenu() throws SQLException {
        Scanner userInput = new Scanner(System.in);
        String READ_MENU;

        System.out.println("**************************************************");
        System.out.println("|   Student Information System in Java JDBC      |");
System.out.println("|    Written By: Mr. Jake R.Pomperada, MAED-IT   |");
        System.out.println("**************************************************");
        System.out.println("|                                                |");
        System.out.println("|        1. ADD RECORDS IN THE DATABASE          |");
        System.out.println("|        2. VIEW RECORDS IN THE DATABASE         |");
        System.out.println("|        3. EDIT RECORDS IN THE DATABASE         |");
        System.out.println("|        4. DELETE RECORDS IN THE DATABASE       |");
        System.out.println("|        5. QUIT DATABASE APPLICATION            |");
        System.out.println("**************************************************");

        System.out.print("Select Your Choice :=> ");

        READ_MENU = userInput.next();

        switch (READ_MENU) {
            case "1":
                Add add = new Add();
                break;
            case "2":
                View view = new View();
                break;
            case "3":
                Edit edit = new Edit();
                break;
            case "4":
                Delete delete = new Delete();
                break;
            case "5":
                System.exit(0);
                break;
            default:
                System.out.println("Invalid selection");
                break; 
        }
    }
}


Add.java

package studentinfosystem;

import java.sql.SQLException;
import java.util.Scanner;

public class Add {

    Add() throws SQLException {
        Scanner userInput = new Scanner(System.in);
        System.out.println("You selected option 1: Add Database Record: ");
        String name;
        System.out.print("Enter Student Name : ");
        name = userInput.nextLine();

        String course;
        System.out.print("Enter Course : ");
        course = userInput.nextLine();

        String school;
        System.out.print("Enter School : ");
        school = userInput.nextLine();
        String address;
        System.out.print("Enter Adress :  ");
        address = userInput.nextLine();
String mobile;
        System.out.print("Enter Mobile : ");
        mobile = userInput.nextLine();
       
        String email;
        System.out.print("Enter Email : ");
        email = userInput.nextLine();
       
  
        DBConfiguration dbUtilities = new DBConfiguration();
                 
       String sql_stmt = "INSERT INTO records (name,course,school,address,mobile,email) VALUES ('" + name + "','" + course + "','" + school + "','" + address + "','" + mobile + "','" + email + "')";
              
        dbUtilities.ExecuteSQLStatement(sql_stmt);
       
        StudentInfoSystem.DisplayMenu();
    }
}


Edit.java


/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package studentinfosystem;

/**
 *
 * @author jake.r.pomperada
 */

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Scanner;

public class Edit {

    Edit() throws SQLException {
        Scanner userInput = new Scanner(System.in);
        System.out.println("You selected option 3: Update database record: ");

        String student_id;
        System.out.print("Enter Student ID to edit : ");
        student_id = userInput.nextLine();
        
        DisplayRecord(student_id);

        String name;
        System.out.print("Enter Student Name : ");
        name = userInput.nextLine();

        String course;
        System.out.print("Enter Course : ");
        course = userInput.nextLine();

        String school;
        System.out.print("Enter School : ");
        school = userInput.nextLine();
        String address;
        System.out.print("Enter Adress :  ");
        address = userInput.nextLine();
String mobile;
        System.out.print("Enter Mobile : ");
        mobile = userInput.nextLine();
       
        String email;
        System.out.print("Enter Email : ");
        email = userInput.nextLine();
             
        DBConfiguration dbUtilities = new DBConfiguration();

        String sql_stmt = "UPDATE records SET name = '" + name + "',course = '" + course + "',school = '" + school + "',address = '" + address + "',mobile = '" + mobile+ "',email = '" + email + "' WHERE id = " + student_id;

        dbUtilities.ExecuteSQLStatement(sql_stmt);

        System.out.println("The Record has successfully being updated.");

        StudentInfoSystem.DisplayMenu();
    }

    private void DisplayRecord(String student_id) throws SQLException {
        try {
            DBConfiguration dbUtilities = new DBConfiguration();
            String sql_stmt = "SELECT id, name, course, school, address, mobile,email FROM records WHERE id = " + student_id;
            ResultSet resultSet = dbUtilities.ReadRecords(sql_stmt);

            if (resultSet.next()) {

                ResultSetMetaData metaData = resultSet.getMetaData();
                int numberOfColumns = metaData.getColumnCount();
                System.out.print("\n\n");
                System.out.print("DATABASE RECORD LISTING");
                System.out.print("\n\n");

                for (int i = 1; i <= numberOfColumns; i++) {
                    System.out.printf("%-21s", metaData.getColumnName(i));
                }
                System.out.println();

                do {
                    for (int i = 1; i <= numberOfColumns; i++) {
                        System.out.printf("%-21s", resultSet.getObject(i));
                    }
                    System.out.println();
                } while (resultSet.next());

                System.out.println();

            } else {
                System.out.println("No database records found.");
            }

            //close db connection
            dbUtilities.DisconnectFromDB();
        } catch (SQLException ex) {
            System.out.println("The following error has occured: " + ex.getMessage());
        }
    }
}


Delete.java



/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package studentinfosystem;

/**
 *
 * @author jake.r.pomperada
 */

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Scanner;

public class Delete {

    Delete() throws SQLException {
        Scanner userInput = new Scanner(System.in);
        System.out.println("You selected option 4: Delete database record : ");

        String student_id;
        System.out.println("Enter Student ID to be deleted : ");
        student_id = userInput.nextLine();

        
        DisplayRecord(student_id);

        String confirm_delete;
        System.out.println("Enter Y to confirm deletion : ");
        confirm_delete = userInput.next();

        if ("Y".equals(confirm_delete) || "y".equals(confirm_delete)) {
            DBConfiguration dbUtilities = new DBConfiguration();

            String sql_stmt = "DELETE FROM records WHERE id = " + student_id;

            dbUtilities.ExecuteSQLStatement(sql_stmt);
            
            System.out.println("The Record has successfully being deleted.");
        }

         StudentInfoSystem.DisplayMenu();
    }

    private void DisplayRecord(String student_id) throws SQLException {
        try {
            DBConfiguration dbUtilities = new DBConfiguration();
            String sql_stmt = "SELECT id, name, course, school, address, mobile,email FROM records WHERE id = " + student_id;
            
ResultSet resultSet = dbUtilities.ReadRecords(sql_stmt);
            
            if (resultSet.next()) {

                ResultSetMetaData metaData = resultSet.getMetaData();
                int numberOfColumns = metaData.getColumnCount();
                System.out.print("\n\n");
                System.out.print("DATABASE RECORD LISTING");
                System.out.print("\n\n");

                for (int i = 1; i <= numberOfColumns; i++) {
                    System.out.printf("%-21s", metaData.getColumnName(i));
                }
                System.out.println();

                do {
                    for (int i = 1; i <= numberOfColumns; i++) {
                        System.out.printf("%-21s", resultSet.getObject(i));
                    }
                    System.out.println();
                } while (resultSet.next());

                System.out.println();

            } else {
                System.out.println("No database records foundn");
            }

            //close db connection
            dbUtilities.DisconnectFromDB();
        } catch (SQLException ex) {
            System.out.println("The following error has occured: " + ex.getMessage());
        } 
    }
}


View.java

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package studentinfosystem;

/**
 *
 * @author jake.r.pomperada
 */

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

public class View {

    public View() throws SQLException {
        System.out.println("You selected option 2: View Database Record");
        DisplayResults();
    }

    private void DisplayResults() throws SQLException {
        try {
            DBConfiguration dbUtilities = new DBConfiguration();

            String sql_stmt = "SELECT id, name, course, school, address, mobile, email FROM records";
            ResultSet resultSet = dbUtilities.ReadRecords(sql_stmt);

            // process query results
            if (resultSet.next()) {

                ResultSetMetaData metaData = resultSet.getMetaData();
                int numberOfColumns = metaData.getColumnCount();
                System.out.print("\n\n");
                System.out.print("DATABASE RECORD LISTING");
                System.out.print("\n\n");

                for (int i = 1; i <= numberOfColumns; i++) {
                    System.out.printf("%-23s", metaData.getColumnName(i));
                }
                System.out.println();

                do {
                    for (int i = 1; i <= numberOfColumns; i++) {
                        System.out.printf("%-23s", resultSet.getObject(i));
                    }
                    System.out.println();
                } while (resultSet.next());
                
                System.out.println();

            } else {
                System.out.println("No database records found.");
            }

            //close db connection
            dbUtilities.DisconnectFromDB();
        } catch (SQLException ex) {
            System.out.println("The following error has occured: " + ex.getMessage());
        } finally {
            StudentInfoSystem.DisplayMenu();
        }
    }
}


records.sql


-- phpMyAdmin SQL Dump
-- version 4.6.5.2
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Aug 09, 2017 at 11:24 AM
-- Server version: 10.1.21-MariaDB
-- PHP Version: 5.6.30

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `student`
--

-- --------------------------------------------------------

--
-- Table structure for table `records`
--

CREATE TABLE `records` (
  `id` int(11) NOT NULL,
  `name` varchar(200) NOT NULL,
  `course` varchar(200) NOT NULL,
  `school` varchar(200) NOT NULL,
  `address` varchar(200) NOT NULL,
  `mobile` varchar(15) NOT NULL,
  `email` varchar(200) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `records`
--

INSERT INTO `records` (`id`, `name`, `course`, `school`, `address`, `mobile`, `email`) VALUES
(1, 'Jacob Samuel Pomperada', 'BS COE', 'USLS', 'Eroreco,Bacolod City', '09173084360', 'jacob_samuel@gmail.com'),
(2, 'Julianna Rae Pomperada', 'BS Math', 'LCC', 'Eroreco, Bacolod City', '09173084360', 'iya@yahoo.com'),
(3, 'Allie Pomperada', 'BS CHE', 'UNO-R', 'Eroreco, Bacolod City', '09173084360', 'allie@hotmail.com'),
(5, 'Jake R. Pomperada', 'BS ComSci', 'UNO-R', 'Eroreco, Bacolod City', '09173084360', 'jakerpomperada@gmail.com');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `records`
--
ALTER TABLE `records`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `records`
--
ALTER TABLE `records`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;