JDBC : Menu Driven Application

Menu Driven application in Java to use various JDBC operation. In this example DB operation is managed by a class called “clsDB.java”, the Student data is managed by a class called “Student.java”. The “Main.java” file consist of Main function as well as menu to perform various operation related to student.

To connect with MySQL database, one need the valid driver and URL string. Below is the user defined class that connect with the MySQL database and also manages the other operation such as related to it.

Below is the list of methods that handles various database operation.

• Execute SQL
• Defines and returns the Result set related to a query
• Add, Update, Delete and Display the “Student” class details

Field Name	Data type, size	Constraint
Sno	int (3)	Primary Key
Sname	text(30)

create table stud (sno int(3) primary key, sname text(30))

clsDB.java

import java.sql.*;

// Developed by Ravi R. Oza
// Jamnagar, Gujarat
// raviroza.wordpress.com
public class clsDB {
	Connection cn;
	Statement st;
	String url = "jdbc:mysql://localhost:3306/rro", username = "root", password = "";

	public clsDB() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			cn = DriverManager.getConnection(url, username, password);
		} catch (Exception err) {
			System.out.println("Error : " + err);
		}
	}

	public void myExecuteQry(String s) {
		try {
			st = cn.createStatement();
			st.executeUpdate(s);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public ResultSet getResultSet(String s) {
		ResultSet r;
		try {
			st = cn.createStatement();
			r = st.executeQuery(s);
			return r;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public void addStudent(Student stud) {
		String q = "insert into stud (sno,sname) values (" + stud.getSno() + ",'" + stud.getSname() + "')";
		myExecuteQry(q);
	}

	public void updateStudent(Student stud) {
		String q = "update stud set sname = '" + stud.getSname() + "' where sno = " + stud.getSno() + "";
		myExecuteQry(q);
	}

	public void deleteStudent(Student stud) {
		String q = "delete from stud where sno = " + stud.getSno() + "";
		myExecuteQry(q);
	}

	public void displayStudent() throws SQLException {
		ResultSet r = getResultSet("Select * from stud");
		System.out.println("No.\t\t\tName");
		while (r.next()) {
			System.out.print(r.getInt(1) + "\t\t\t" + r.getString(2) + "\n");
		}
	}
}

Student.java

import java.util.Scanner;

public class Student 
{
	private int sno;
	private String sname;	
	Scanner sc = new Scanner(System.in);
	
	public void readSno()
	{
		System.out.print("Enter stud no : ");  
		sno = sc.nextInt();
	}
	public void readSname()
	{
		System.out.print("Enter stud name : ");  
		sname = sc.next();
	}
	
	
	public int getSno() {
		return sno;
	}
	public void setSno(int sno) {
		this.sno = sno;
	}
	public String getSname() {
		return sname;
	}
	public void setSname(String sname) {
		this.sname = sname;
	}
	
}

Main.java

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

public class Main
{
	
	public static void main(String[] args) throws SQLException
	{
		Scanner sc = new Scanner(System.in);
		clsDB db = new clsDB();
		
		int ch=0;
		Student stud = new Student();
		
		while(ch!=5)
		{		
			System.out.println("Main Menu for Student Data Entry");
			System.out.println("1. Add student details");
			System.out.println("2. Update student details");
			System.out.println("3. Delete student details");
			System.out.println("4. Display all the student details");
			
			System.out.println("5. Exit");

			System.out.print("Enter your choice : ");
			ch = sc.nextInt();
			
			if(ch == 1)
			{
				System.out.println("Adding student details");
				System.out.print("Enter stud no : ");  
					stud.setSno(sc.nextInt());
				System.out.print("Enter stud name : ");
					stud.setSname(sc.next());
					
				db.myExecuteQry("insert into stud (sno,sname) values ("+stud.getSno()+",'"+stud.getSname()+"')");
				System.out.println("Record added successfully...");				
			}
			else if(ch == 2)
			{
				System.out.println("Updating student details");
				System.out.print("Enter stud no to be updated : ");
				stud.setSno(sc.nextInt());
				System.out.print("Enter new name for student : ");
				stud.setSname(sc.next());
				db.myExecuteQry("update stud set sname = '"+stud.getSname()+"' where sno = "+stud.getSno()+"");
				System.out.println("Record updated successfully...");				
			}
			else if(ch == 3)
			{
				System.out.println("Deleting student details");
				System.out.print("Enter stud no to be deleted : ");
				stud.setSno(sc.nextInt());				
				db.myExecuteQry("delete from stud where sno = "+stud.getSno()+"");
				System.out.println("Record deleted successfully...");				
			}
			else if(ch == 4)
			{
				System.out.println("Displaying student details");
				ResultSet r = db.getResultSet("Select * from stud");
				System.out.println("No.\t\t\tName");
				while(r.next())
				{
					System.out.print(r.getInt(1)+"\t\t\t"+r.getString(2)+"\n");
				}
			}
			else if(ch==5)
			{
				System.exit(0);
			}			
		}		
	}
}

JDBCUsingPOJO.java

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

public class JDBCUsingPOJO 
{	
	public static void main(String[] args) throws SQLException
	{
		Scanner sc = new Scanner(System.in);
		int ch=0;
		clsDB 	db 		= new clsDB();			
		Student stud 	= new Student();
		
		while(ch!=5)
		{		
			System.out.println("Main Menu for Student Data Entry");
			System.out.println("1. Add student details");
			System.out.println("2. Update student details");
			System.out.println("3. Delete student details");
			System.out.println("4. Display all the student details");
			
			System.out.println("5. Exit");

			System.out.print("Enter your choice : ");
			ch = sc.nextInt();
			
			if(ch == 1)
			{				
				System.out.println("adding student : ");
				stud.readSno();
				stud.readSname();
				db.addStudent(stud);
				System.out.println("Record added successfully...");				
			}
			else if(ch == 2)
			{				
				System.out.println("updating student : ");
				stud.readSno();
				stud.readSname();				
				db.updateStudent(stud);
				System.out.println("Record updated successfully...");				
			}
			else if(ch == 3)
			{				
				System.out.print("Deleting  student: ");
				stud.readSno();				
				db.deleteStudent(stud);
				System.out.println("Record deleted successfully...");				
			}
			else if(ch == 4)
			{
				System.out.println("Displaying student details");
				db.displayStudent();
			}
			else if(ch==5)
			{
				System.exit(0);
			}			
		}		
	}
}