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 */;
