Assignment 3 - Database Programming
Set A
a) Create a PROJECT table with fields project_id, Project_name, Project_description,
Project_Status. etc. Insert values in the table. Display all the details of the PROJECT table in
a tabular format on the screen.(using swing).
Code -
import [Link].*;
import [Link].*;
import [Link].*;
class ProjectTableGUI {
public static void main(String[] args) {
JFrame f = new JFrame("Project Table");
String[] columns = {"ID", "Name", "Description", "Status"};
DefaultTableModel model = new DefaultTableModel(columns, 0);
try {
// JDBC connection
[Link]("[Link]");
Connection con = [Link](
"jdbc:mysql://localhost:3306/projectdb", "root", "root");
Statement st = [Link]();
ResultSet rs = [Link]("SELECT * FROM PROJECT");
while ([Link]()) {
int id = [Link]("project_id");
String name = [Link]("project_name");
String desc = [Link]("project_description");
String status = [Link]("project_status");
[Link](new Object[]{id, name, desc, status});
}
[Link]();
} catch (Exception e) {
[Link](e);
}
JTable table = new JTable(model);
JScrollPane sp = new JScrollPane(table);
[Link](sp);
[Link](500, 300);
[Link](true);
}
}
Output -
b) Write a program to display information about the database and list all the tables in the
database. (Use DatabaseMetaData).
Code -
import [Link].*;
class DatabaseInfo {
public static void main(String[] args) {
try {
// Load driver
[Link]("[Link]");
// Connect to database
Connection con = [Link](
"jdbc:mysql://localhost:3306/projectdb", "root", "root");
// Get metadata
DatabaseMetaData dbmd = [Link]();
// Display DB info
[Link]("Database Name: " + [Link]());
[Link]("Database Version: " + [Link]());
[Link]("Driver Name: " + [Link]());
[Link]("Driver Version: " + [Link]());
// Get tables
[Link]("\nTables in Database:");
ResultSet rs = [Link](null, null, "%", new String[]{"TABLE"});
while ([Link]()) {
[Link]([Link]("TABLE_NAME"));
}
[Link]();
} catch (Exception e) {
[Link](e);
}
}
}
Output -
c) Write a program to display information about all columns in the DONAR table using
ResultSetMetaData
Code -
import [Link].*;
class ColumnInfo {
public static void main(String[] args) {
try {
[Link]("[Link]");
Connection con = [Link](
"jdbc:mysql://localhost:3306/projectdb", "root", "root");
Statement st = [Link]();
ResultSet rs = [Link]("SELECT * FROM DONAR");
// Get metadata
ResultSetMetaData rsmd = [Link]();
int count = [Link]();
[Link]("Column Information:\n");
for (int i = 1; i <= count; i++) {
[Link]("Column Name: " + [Link](i));
[Link]("Column Type: " + [Link](i));
[Link]("Column Size: " + [Link](i));
[Link]("-------------------------");
}
[Link]();
} catch (Exception e) {
[Link](e);
}
}
}
Output -
Set B
a) Create a MOBILE table with fields Model_Number, Model_Name, Model_Color,
Sim_Type, NetworkType, BatteryCapacity, InternalStorage, RAM and ProcessorType. Insert
values in the table. Write a menu driven program to pass the input using Command line
argument to perform the following operations on the MOBILE table.
1. Insert 2. Modify 3. Delete 4. Search 5. View All 6. Exit
Code -
import [Link].*;
import [Link].*;
class MobileCRUD {
public static void main(String[] args) {
Scanner sc = new Scanner([Link]);
try {
[Link]("[Link]");
Connection con = [Link](
"jdbc:mysql://localhost:3306/projectdb", "root", "root");
int choice;
do {
[Link]("\[Link]\[Link]\[Link]\[Link]\[Link] All\[Link]");
[Link]("Enter choice: ");
choice = [Link]();
switch(choice) {
case 1: // Insert
[Link]("Model Number: ");
int num = [Link]();
[Link]();
[Link]("Model Name: ");
String name = [Link]();
[Link]("Color: ");
String color = [Link]();
[Link]("Sim Type: ");
String sim = [Link]();
[Link]("Network: ");
String net = [Link]();
[Link]("Battery: ");
int battery = [Link]();
[Link]("Storage: ");
int storage = [Link]();
[Link]("RAM: ");
int ram = [Link]();
[Link]();
[Link]("Processor: ");
String proc = [Link]();
PreparedStatement ps = [Link](
"INSERT INTO MOBILE VALUES(?,?,?,?,?,?,?,?,?)");
[Link](1, num);
[Link](2, name);
[Link](3, color);
[Link](4, sim);
[Link](5, net);
[Link](6, battery);
[Link](7, storage);
[Link](8, ram);
[Link](9, proc);
[Link]();
[Link]("Inserted!");
break;
case 2: // Modify
[Link]("Enter Model Number to update: ");
int id = [Link]();
[Link]();
[Link]("New Name: ");
String newName = [Link]();
PreparedStatement ps2 = [Link](
"UPDATE MOBILE SET Model_Name=? WHERE Model_Number=?");
[Link](1, newName);
[Link](2, id);
[Link]();
[Link]("Updated!");
break;
case 3: // Delete
[Link]("Enter Model Number to delete: ");
int del = [Link]();
PreparedStatement ps3 = [Link](
"DELETE FROM MOBILE WHERE Model_Number=?");
[Link](1, del);
[Link]();
[Link]("Deleted!");
break;
case 4: // Search
[Link]("Enter Model Number: ");
int search = [Link]();
PreparedStatement ps4 = [Link](
"SELECT * FROM MOBILE WHERE Model_Number=?");
[Link](1, search);
ResultSet rs = [Link]();
while([Link]()) {
[Link]([Link](1) + " " + [Link](2));
}
break;
case 5: // View All
Statement st = [Link]();
ResultSet rs2 = [Link]("SELECT * FROM MOBILE");
while([Link]()) {
[Link]([Link](1) + " " + [Link](2));
}
break;
} while(choice != 6);
[Link]();
} catch(Exception e) {
[Link](e);
}
}
}
Output -
b) Design a following Registration form and raise an appropriate exception if invalid
information is entered like Birth Year ‘0000’
Code -
import [Link].*;
import [Link].*;
import [Link].*;
class CowinForm {
public static void main(String[] args) {
JFrame f = new JFrame("Co-WIN Registration");
JLabel title = new JLabel("Co-WIN Registration");
[Link](150, 10, 200, 30);
[Link](new Font("Arial", [Link], 16));
JLabel adhar = new JLabel("AdharCard No:");
[Link](50, 50, 120, 20);
JTextField t1 = new JTextField();
[Link](180, 50, 150, 20);
JLabel year = new JLabel("Birth Year:");
[Link](50, 80, 120, 20);
JTextField t2 = new JTextField();
[Link](180, 80, 150, 20);
JLabel mobile = new JLabel("Mobile No:");
[Link](50, 110, 120, 20);
JTextField t3 = new JTextField();
[Link](180, 110, 150, 20);
JLabel age = new JLabel("Age Group:");
[Link](50, 140, 120, 20);
JRadioButton r1 = new JRadioButton("18 & above");
[Link](180, 140, 100, 20);
JRadioButton r2 = new JRadioButton("45 & above");
[Link](280, 140, 100, 20);
ButtonGroup bg1 = new ButtonGroup();
[Link](r1);
[Link](r2);
JLabel vaccine = new JLabel("Vaccines:");
[Link](50, 170, 120, 20);
JRadioButton v1 = new JRadioButton("Covishield");
[Link](180, 170, 100, 20);
JRadioButton v2 = new JRadioButton("Covaxin");
[Link](280, 170, 100, 20);
JRadioButton v3 = new JRadioButton("Sputnik V");
[Link](380, 170, 100, 20);
ButtonGroup bg2 = new ButtonGroup();
[Link](v1);
[Link](v2);
[Link](v3);
JButton add = new JButton("ADD");
[Link](50, 220, 100, 30);
// VALIDATION
[Link](e -> {
try {
String birthYear = [Link]();
if ([Link]("0000")) {
throw new Exception("Invalid Birth Year!");
}
[Link](f, "Registration Successful!");
} catch (Exception ex) {
[Link](f, [Link]());
}
});
[Link](title);
[Link](adhar);
[Link](t1);
[Link](year);
[Link](t2);
[Link](mobile);
[Link](t3);
[Link](age);
[Link](r1);
[Link](r2);
[Link](vaccine);
[Link](v1);
[Link](v2);
[Link](v3);
[Link](add);
[Link](550, 350);
[Link](null);
[Link](true);
}
}
Output -
Set C
a) Create tables : Course (courseid, coursename, courseinstructor) and Student (studentid,
studentname, studentclass). Course and Student have a many to many relationship. Create
a GUI based system for performing the following operations on the tables: Course : Add
Course, View All students of a specific course Student : Add Student, Delete Student, View
All students, Search student.
Code -
import [Link].*;
import [Link].*;
import [Link].*;
class CourseStudentGUI {
static Connection con;
public static void main(String[] args) {
try {
[Link]("[Link]");
con = [Link](
"jdbc:mysql://localhost:3306/projectdb", "root", "root");
} catch (Exception e) {
[Link](e);
}
JFrame f = new JFrame("Course-Student System");
JButton addCourse = new JButton("Add Course");
[Link](50, 50, 150, 30);
JButton addStudent = new JButton("Add Student");
[Link](220, 50, 150, 30);
JButton viewStudents = new JButton("View Students");
[Link](50, 100, 150, 30);
JButton deleteStudent = new JButton("Delete Student");
[Link](220, 100, 150, 30);
// ADD COURSE
[Link](e -> {
try {
int id = [Link]([Link]("Course ID"));
String name = [Link]("Course Name");
String inst = [Link]("Instructor");
PreparedStatement ps = [Link](
"INSERT INTO Course VALUES (?, ?, ?)");
[Link](1, id);
[Link](2, name);
[Link](3, inst);
[Link]();
[Link](f, "Course Added");
} catch (Exception ex) {
[Link](ex);
}
});
// ADD STUDENT
[Link](e -> {
try {
int id = [Link]([Link]("Student ID"));
String name = [Link]("Student Name");
String cls = [Link]("Class");
PreparedStatement ps = [Link](
"INSERT INTO Student VALUES (?, ?, ?)");
[Link](1, id);
[Link](2, name);
[Link](3, cls);
[Link]();
[Link](f, "Student Added");
} catch (Exception ex) {
[Link](ex);
}
});
// VIEW ALL STUDENTS
[Link](e -> {
try {
Statement st = [Link]();
ResultSet rs = [Link]("SELECT * FROM Student");
String data = "";
while ([Link]()) {
data += [Link](1) + " " + [Link](2) + "\n";
}
[Link](f, data);
} catch (Exception ex) {
[Link](ex);
}
});
// DELETE STUDENT
[Link](e -> {
try {
int id = [Link]([Link]("Enter Student ID"));
PreparedStatement ps = [Link](
"DELETE FROM Student WHERE studentid=?");
[Link](1, id);
[Link]();
[Link](f, "Deleted");
} catch (Exception ex) {
[Link](ex);
}
});
[Link](addCourse);
[Link](addStudent);
[Link](viewStudents);
[Link](deleteStudent);
[Link](450, 250);
[Link](null);
[Link](true);
}
}
Output -
b) Create the following tables and relations, for an INVESTMENT firm EMP(empid
,empname, empaddress, empcontact, empage) INVESTOR(invno, invname , invdate,
invamt) An employee may invest in one or more investments, hence he can be an investor.
But an investor need not be an employee of the firm. Insert sufficient number of records in
the relations / tables with appropriate values.
i. Display the List the distinct names of person who are either employees, or investors or
both.
ii. List the names of employees who are not investors
Code -
To create Table -
CREATE TABLE EMP (
empid INT PRIMARY KEY,
empname VARCHAR(50),
empaddress VARCHAR(100),
empcontact VARCHAR(15),
empage INT
);
CREATE TABLE INVESTOR (
invno INT PRIMARY KEY,
invname VARCHAR(50),
invdate DATE,
invamt INT
);
To Insert Data
INSERT INTO EMP VALUES
(1, 'Anu', 'Pune', '9876543210', 22),
(2, 'Rahul', 'Mumbai', '9876543211', 30),
(3, 'Sneha', 'Delhi', '9876543212', 28);
INSERT INTO INVESTOR VALUES
(101, 'Anu', '2025-01-10', 50000),
(102, 'Amit', '2025-02-15', 70000),
(103, 'Sneha', '2025-03-20', 60000);
i) DISTINCT names
SELECT empname AS name FROM EMP
UNION
SELECT invname FROM INVESTOR;
ii) Employees who are NOT investors
SELECT empname
FROM EMP
WHERE empname NOT IN (
SELECT invname FROM INVESTOR
);
Output -