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).
Database pgsql file
-- create table project(p_id int primary key, p_name char(34),p_description char(56),p_status char(34));
-- INSERT INTO project VALUES(1,'abc','java project','completed');
-- INSERT INTO project VALUES(2,'kpl','php project','completed');
-- INSERT INTO project VALUES(3,'lmn','c project','completed');
-- INSERT INTO project VALUES(4,'xyz','ruby project','completed');
SELECT * FROM project;
-- delete FROM project where p_id >=7;
import java.sql.*;
import java.awt.event.*;
import javax.swing.*;
import java.awt.*;
import javax.swing.table.DefaultTableModel;
public class StudentprojectDisplay extends JFrame implements ActionListener
{
Connection con;
ResultSet rs;
Statement st;
static JTable table;
String[] columnNames={"p_id","p_name","p_description","p_status"};
JFrame frm;
JPanel p1;
String p_id = "",p_name = "",p_description = "",p_status = "";
JTextField txtid,txtname,txtdesc,textstatus;
JButton b1,b2,b3,b4;
StudentprojectDisplay(){
setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
setTitle("STUDENT PROJECT INFO");
p1=new JPanel();
p1.setLayout(new GridLayout(5,5,10,10));
Label l1 = new Label("P-ID");
p1.add(l1);
txtid = new JTextField(20);
p1.add(txtid);
Label l2 = new Label("P-NAME");
p1.add(l2);
txtname = new JTextField(20);
p1.add(txtname);
Label l3 = new Label("P-DESCRIPTION");
p1.add(l3);
txtdesc = new JTextField(20);
p1.add(txtdesc);
Label l4 = new Label("P-STATUS");
p1.add(l4);
textstatus = new JTextField(20);
p1.add(textstatus);
b1 = new JButton("Display");
p1.add(b1);
b1.addActionListener(this);
add(p1,BorderLayout.WEST);
setVisible(true);
setSize(400,400);
}//ProjectDetails()
public void actionPerformed(ActionEvent ae)
{
if(ae.getSource()==b1)//display
{
frm = new JFrame("Display");
frm.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
frm.setLayout(new BorderLayout());
DefaultTableModel model = new DefaultTableModel();
model.setColumnIdentifiers(columnNames);
table = new JTable();
table.setModel(model);
JScrollPane scroll = new JScrollPane(table);
try{
Class.forName("org.postgresql.Driver");
// Use database name & password according to your "dbname","pass"
con = DriverManager.getConnection("jdbc:postgresql://localhost/postgres", "postgres", "dsk");
st = con.createStatement();
rs = st.executeQuery("select * from project");
while(rs.next()){
p_id = rs.getString(1);
p_name = rs.getString(2);
p_description = rs.getString(3);
p_status = rs.getString(4);
// This all coloumn names are taken from project table.
model.addRow(new Object[]{p_id,p_name,p_description,p_status});
}//while
frm.add(scroll);
frm.setVisible(true);
frm.setSize(400,400);
}//try
catch(Exception e){
JOptionPane.showMessageDialog(null,e,"Error",JOptionPane.ERROR_MESSAGE);
}
}//if
}
public static void main(String args[])
{
new StudentprojectDisplay();
}//main
}//class
b) Write a program to display information about the database and list all the tables in the database. (Use DatabaseMetaData).
import java.sql.*;
public class Metadata {
public static void main(String[] args) {
try {
// load a driver
Class.forName("org.postgresql.Driver");
// Establish Connection
Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost/postgres", "postgres", "dsk");
DatabaseMetaData dbmd = conn.getMetaData();
System.out.println("\t-----------------------------------------------------------------------");
System.out.println("\t\tDriver Name : " + dbmd.getDriverName());
System.out.println("\t\tDriver Version : " + dbmd.getDriverVersion());
System.out.println("\t\tUserName : " + dbmd.getUserName());
System.out.println("\t\tDatabase Product Name : " + dbmd.getDatabaseProductName());
System.out.println("\t\tDatabase Product Version : " + dbmd.getDatabaseProductVersion());
System.out.println("\t---------------------------------------------------------------------");
String table[] = { "TABLE" };
ResultSet rs = dbmd.getTables(null, null, null, table);
System.out.println("\t\tTable Names:");
while (rs.next()) {
System.out.println(rs.getString("TABLE_NAME"));
}
rs.close();
conn.close();
} // try
catch (Exception e) {
System.out.println(e);
} // catch
}// main
}// metadata
c) Write a program to display information about all columns in the DONAR table using ResultSetMetaData.
for database material use here donor.pgsql file
-- create table donor(did int, dname char(22),daddr varchar(22));
-- insert into donor VALUES(1,'AAA','zzz');
-- insert into donor VALUES(2,'BBB','yyy');
-- insert into donor VALUES(3,'CCC','xxx');
-- insert into donor VALUES(4,'DDD','www');
SELECT * from donor;
program:-
import java.sql.*;
public class DONOR {
public static void main(String[] args) {
try {
// load a driver
Class.forName("org.postgresql.Driver");
// Establish Connection
Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost/postgres", "postgres", "dsk");
Statement stmt = null;
stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from donor");
ResultSetMetaData rsmd = rs.getMetaData();
System.out.println("\t-------------------------------------------------");
int count = rsmd.getColumnCount();
System.out.println("\t No. of Columns: " + rsmd.getColumnCount());
System.out.println("\t-------------------------------------------------");
for (int i = 1; i <= count; i++)
{
System.out.println("\t\tColumn No : " + i);
System.out.println("\t\tColumn Name : " + rsmd.getColumnName(i));
System.out.println("\t\tColumn Type : " + rsmd.getColumnTypeName(i));
System.out.println("\t\tColumn Display Size : " + rsmd.getColumnDisplaySize(i));
System.out.println();
} // for
System.out.println("\t--------------------------------------------------");
rs.close();
stmt.close();
conn.close();
} // try
catch (Exception e) {
System.out.println(e);
} // catch
}
}
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 MOBILE table.
1. Insert 2. Modify 3. Delete 4. Search 5. View All 6. Exit
PGSQL File :
create table mobile(mno int ,name char(20),color char(20),sim char(20), Battery int, internal int , ram int , pr char(25));
select * from mobile;
Program:-
import java.sql.*;
import java.util.*;
public class mobile {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstmt = null;
Statement stmt = null;
ResultSet rs = null;
try {
// load a driver
Class.forName("org.postgresql.Driver");
// Establish Connection
conn = DriverManager.getConnection("jdbc:postgresql://localhost/postgres", "postgres", "dsk");
Scanner sc = new Scanner(System.in);
System.out.println("\n\tMobile Information\n");
do {
System.out.println("\n\t1.Insert \n\t2.Modify\n\t3.Delete\n\t4.Search \n\t5.View All\n\t6.Exit\n");
System.out.println("Enter Your Choice: ");
int ch = sc.nextInt();
switch (ch) {
case 1:
pstmt = conn.prepareStatement("insert into mobile values(?,?,?,?,?,?,?,?)");
System.out.println("Enter Model_Number: ");
int mno = sc.nextInt();
pstmt.setInt(1, mno);
sc.nextLine();
System.out.println("Enter Model_Name: ");
String name = sc.nextLine();
pstmt.setString(2, name);
System.out.println("Enter Model_Color: ");
String color = sc.nextLine();
pstmt.setString(3, color);
System.out.println("Enter Sim_Type: ");
String sim = sc.nextLine();
pstmt.setString(4, sim);
System.out.println("Enter Battery Capacity: ");
int Battery = sc.nextInt();
pstmt.setInt(5, Battery);
System.out.println("Enter Internal Storage In GB: ");
int internal = sc.nextInt();
pstmt.setInt(6, internal);
System.out.println("Enter RAM In GB: ");
int ram = sc.nextInt();
pstmt.setInt(7, ram);
sc.nextLine();
System.out.println("Enter Processor_Type: ");
String pr = sc.nextLine();
pstmt.setString(8, pr);
int result = pstmt.executeUpdate();
System.out.println(result + " Record Inserted\n");
break;
case 2:
String SQL = "update mobile set name=? where mno=?";
pstmt = conn.prepareStatement(SQL);
System.out.println("Enter Model No for Update Record: ");
int no = sc.nextInt();
pstmt.setInt(2, no);
sc.nextLine();
System.out.println("Enter Updated Model name: ");
String mname = sc.nextLine();
pstmt.setString(1, mname);
int result2 = pstmt.executeUpdate();
System.out.println(result2 + " Record Updated\n");
break;
case 3:
pstmt = conn.prepareStatement("delete from mobile where mno=?");
System.out.println("Enter Model No for Delete Record: ");
int model = sc.nextInt();
pstmt.setInt(1, model);
int result3 = pstmt.executeUpdate();
System.out.println(result3 + " Record Deleted\n");
break;
case 4:
pstmt = conn.prepareStatement("select * from mobile where mno=?");
System.out.println("Enter Model No for serach Record: ");
int m = sc.nextInt();
pstmt.setInt(1, m);
rs = pstmt.executeQuery();
System.out.println("\n------------------------------------------------------------------------------------------------------------------");
while (rs.next()) {
System.out.println(rs.getInt(1) + "\t" + rs.getString(2) + "\t" + rs.getString(3) + "\t"
+ rs.getString(4) + "\t" + rs.getInt(5) + "\t" + rs.getInt(6) + "\t" + rs.getInt(7)
+ "\t" + rs.getString(8));
}
System.out.println("------------------------------------------------------------------------------------------------------------------");
break;
case 5:
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from mobile");
System.out.println("\n------------------------------------------------------------------------------------------------------------------");
while (rs.next()) {
System.out.println(rs.getInt(1) + "\t" + rs.getString(2) + "\t" + rs.getString(3) + "\t"
+ rs.getString(4) + "\t" + rs.getInt(5) + "\t" + rs.getInt(6) + "\t" + rs.getInt(7)
+ "\t" + rs.getString(8));
}
System.out.println("------------------------------------------------------------------------------------------------------------------");
break;
case 6:
System.exit(1);
rs.close();
stmt.close();
pstmt.close();
conn.close();
sc.close();
}
} while (true);
} // try
catch (Exception e) {
System.out.println(e);
} // catch
}// main
}// class
b) Design a following Registration form and raise an appropriate exception if invalid information is entered like Birth Year ‘0000’
PGSQL File :-
create table cowin(adharno char(22) PRIMARY KEY,year int,phNo char(15),age char(30),hospital char(30),vaccine char(33),timestamp char(44));
select * from cowin;
Program:-
import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
class InvalidBirthDateException extends Exception {
String msg = "Invalid Date Exception\n";
public String toString() {
return msg;
}
}
public class Cowin extends JFrame implements ActionListener {
JTextField adhar, byear, phone, hosp;
JPanel p1, p2, p3, p4;
JButton add, update, delete, view, search;
JRadioButton r1, r2, r3, r4, r5, r6, r7, r8;
ButtonGroup bg,bg1,bg2;
JComboBox hos;
String s[] = { "Tambe Hospital", "Daima Hospital", "Nighute Hospital" };
Cowin() {
setTitle("Cowin Registration");
setSize(800, 600);
setLayout(new GridLayout(8, 2, 40, 40));
setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
JLabel adharno = new JLabel("Adhar Card Number: ");
add(adharno);
adhar = new JTextField(10);
add(adhar);
JLabel Byear = new JLabel("Birth Year: ");
add(Byear);
byear = new JTextField(10);
add(byear);
JLabel phoneNo = new JLabel("Mobile Number: ");
add(phoneNo);
phone = new JTextField(10);
add(phone);
// Age Radio Button
p1 = new JPanel();
p1.setLayout(new FlowLayout());
JLabel Age = new JLabel("Age Group : ");
add(Age);
r1 = new JRadioButton("18 & above");
p1.add(r1);
// to get the value of radio button
r1.setActionCommand("18 & above");
r2 = new JRadioButton("45 & above");
r2.setActionCommand("45 & above");
p1.add(r2);
add(p1);
JLabel hospital = new JLabel("Select Hospital: ");
add(hospital);
hos = new JComboBox(s);
add(hos);
// Vaccines Radio Button
p2 = new JPanel();
p2.setLayout(new FlowLayout());
JLabel Vaccines = new JLabel("Vaccines : : ");
add(Vaccines);
r3 = new JRadioButton("Covishield");
p2.add(r3);
r3.setActionCommand("Covishield");
r4 = new JRadioButton("Covaxin");
p2.add(r4);
r4.setActionCommand("Covaxin");
r5 = new JRadioButton("Sputnik V");
p2.add(r5);
r5.setActionCommand("SputnikV");
add(p2);
// TimeSlot Radio Button
p3 = new JPanel();
p3.setLayout(new FlowLayout());
JLabel Time = new JLabel("Time Slot :: ");
add(Time);
r6 = new JRadioButton("Morning");
p3.add(r6);
r6.setActionCommand("Morning");
r7 = new JRadioButton("Afternoon");
p3.add(r7);
r7.setActionCommand("Afternoon");
r8 = new JRadioButton("Evening");
p3.add(r8);
r8.setActionCommand("Evening");
add(p3);
// Button
p4 = new JPanel();
p4.setLayout(new FlowLayout());
add = new JButton("Add");
p4.add(add);
update = new JButton("Update");
p4.add(update);
delete = new JButton("Delete");
p4.add(delete);
view = new JButton("View");
p4.add(view);
search = new JButton("Search");
p4.add(search);
add(p4);
add.addActionListener(this);
bg = new ButtonGroup();
bg.add(r1);
bg.add(r2);
bg1 = new ButtonGroup();
bg1.add(r4);
bg1.add(r3);
bg1.add(r5);
bg2 = new ButtonGroup();
bg2.add(r6);
bg2.add(r7);
bg2.add(r8);
setVisible(true);
}
public void actionPerformed(ActionEvent ae) {
if (ae.getSource() == add) {
String adharno = (adhar.getText());
int year = Integer.parseInt(byear.getText());
String phNo = (phone.getText());
String hospital = (String) (hos.getSelectedItem());
String age=bg.getSelection().getActionCommand();
String vaccine=bg1.getSelection().getActionCommand();
String timestamp=bg2.getSelection().getActionCommand();
try {
if (year == 0000) {
throw new InvalidBirthDateException();
} else {
Connection conn = null;
PreparedStatement pstmt = null;
try {
// load a driver
Class.forName("org.postgresql.Driver");
// Establish Connection
// Use database name & password according to your "dbname","pass"
conn = DriverManager.getConnection("jdbc:postgresql://localhost/postgres", "postgres", "dsk");
pstmt = conn.prepareStatement("insert into cowin values(?,?,?,?,?,?,?)");
pstmt.setString(1, adharno);
pstmt.setInt(2, year);
pstmt.setString(3, phNo);
pstmt.setString(4, hospital);
pstmt.setString(5, age);
pstmt.setString(6, vaccine);
pstmt.setString(7, timestamp);
int result = pstmt.executeUpdate();
if (result == 1) {
JOptionPane.showMessageDialog(null, "Succesfully Inserted", hospital,
JOptionPane.INFORMATION_MESSAGE);
}
pstmt.close();
conn.close();
} // try
catch (Exception e) {
JOptionPane.showMessageDialog(null, e, "ERROR OCCURED", JOptionPane.ERROR_MESSAGE);
} // catch
}
} catch (InvalidBirthDateException e) {
JOptionPane.showMessageDialog(null, e, "ERROR OCCURED", JOptionPane.ERROR_MESSAGE);
}
}
}
public static void main(String[] args) {
new Cowin();
}// MAIN
}// CLASS
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.
PGSQL file :-
create table student1(s_id int ,s_name char(30) primary key,s_class char(20));
insert into student1 values(1,'abc','tybcs');
insert into student1 values(2,'xyz','sybcs');
insert into student1 values(3,'pqr','fybcs');
insert into student1 values(4,'lmn','tybcs');
insert into student1 values(56,'Prasad','TyBcs');
insert into student1 values(77,'Swayam','TYBCS');
select * from student1;
create table course(c_id int ,c_name char(30) primary key, c_inst char(30));
insert into course values(1,'java','Gite Madam');
insert into course values(2,'ruby','Kawde sir');
insert into course values(3,'python','Thorat Madam');
insert into course values(4,'c','Sharma Madam');
insert into course values(5,'php','Kawde sir');
insert into course values(6,'mysql','Vikhe Madam');
select * from course;
create table SC(s_name char(30),foreign key(s_name) references student1(s_name),c_name char(30),foreign key(c_name) references course(c_name));
insert into SC values('abc','java');
insert into SC values('xyz','java');
insert into SC values('pqr','php');
insert into SC values('lmn','c');
insert into SC values('Swayam','ruby');
insert into SC values('Prasad','php');
insert into SC values('Swayam','c');
insert into SC values('Prasad','ruby');
SELECT * from SC;
select s_name from SC where c_name = 'c';
PROGRAM:-
import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import javax.swing.table.*;
public class Student_Course extends JFrame implements ActionListener {
JButton addstd, Delete, View, Search, addcourse, course;
JPanel p1, p2;
Connection con;
ResultSet rs;
Statement st;
PreparedStatement pstmt = null;
AddStudent stdobj;
Addcourse cobj;
static JTable table;
String s_id = "", s_name = "", s_class = "";
String st_name = "";
String[] columnNames = { "s_id ", "s_name ", "s_class " };
String[] SC_columns = { "st_name "};
JFrame frm,frmdisp;
Student_Course() {
setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
setTitle("STUDENT INFO");
setLayout(new GridLayout(4, 4, 40, 20));
setVisible(true);
setSize(400, 400);
p1 = new JPanel();
p1.setLayout(new FlowLayout());
JLabel std = new JLabel("Student : ");
add(std);
addstd = new JButton("Add");
p1.add(addstd);
Delete = new JButton("Delete");
p1.add(Delete);
View = new JButton("View All");
p1.add(View);
Search = new JButton("Search");
p1.add(Search);
add(p1);
p2 = new JPanel();
p2.setLayout(new FlowLayout());
JLabel sub = new JLabel("Course : ");
add(sub);
addcourse = new JButton("Add");
p2.add(addcourse);
course = new JButton("View ");
p2.add(course);
add(p2);
// adding action listener to all buttons
addstd.addActionListener(this);
addcourse.addActionListener(this);
Delete.addActionListener(this);
View.addActionListener(this);
Search.addActionListener(this);
course.addActionListener(this);
}
public void actionPerformed(ActionEvent ae) {
try {
Class.forName("org.postgresql.Driver");
// Use database name & password according to your "dbname","pass"
con = DriverManager.getConnection("jdbc:postgresql://localhost/postgres", "postgres", "dsk");
if (ae.getSource() == View) {
frm = new JFrame("Student DISPLAY");
setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
setLayout(new BorderLayout());
DefaultTableModel model = new DefaultTableModel();
model.setColumnIdentifiers(columnNames);
table = new JTable();
table.setModel(model);
table.setAutoResizeMode(JTable.AUTO_RESIZE_ALL_COLUMNS);
table.setFillsViewportHeight(true);
JScrollPane scroll = new JScrollPane(table);
scroll.setHorizontalScrollBarPolicy(JScrollPane.HORIZONTAL_SCROLLBAR_AS_NEEDED);
scroll.setVerticalScrollBarPolicy(JScrollPane.VERTICAL_SCROLLBAR_AS_NEEDED);
st = con.createStatement();
rs = st.executeQuery("select * from student1");
while (rs.next()) {
s_id = rs.getString(1);
s_name = rs.getString(2);
s_class = rs.getString(3);
// This all coloumn names are taken from project table.
model.addRow(new Object[] { s_id, s_name, s_class });
} // while
frm.add(scroll);
frm.setVisible(true);
frm.setSize(400, 400);
} // view
if (ae.getSource() == addstd) {
stdobj = new AddStudent();
} // adding student record
if (ae.getSource() == Search) {
String s1 = JOptionPane.showInputDialog(null, "Enter Student Name");
pstmt = con.prepareStatement("select * from student1 where s_name=?");
pstmt.setString(1, s1);
rs = pstmt.executeQuery();
while (rs.next()) {
String result = rs.getInt(1) + "\t" + rs.getString(2) + "\t" + rs.getString(3);
JOptionPane.showMessageDialog(null, result);
}
}
if (ae.getSource() == Delete) {
String s1 = JOptionPane.showInputDialog(null, "Enter Student Name ");
pstmt = con.prepareStatement("delete from student1 where s_name=?");
pstmt.setString(1, s1);
pstmt.executeUpdate();
JOptionPane.showMessageDialog(null, "Record deleted");
}
if (ae.getSource() == addcourse) {
cobj = new Addcourse();
} // adding course record
if (ae.getSource() == course) {
String s1 = JOptionPane.showInputDialog(null, "Enter course Name");
frmdisp = new JFrame("Student course DISPLAY");
setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
setLayout(new BorderLayout());
DefaultTableModel model = new DefaultTableModel();
model.setColumnIdentifiers(SC_columns);
table = new JTable();
table.setModel(model);
table.setAutoResizeMode(JTable.AUTO_RESIZE_ALL_COLUMNS);
table.setFillsViewportHeight(true);
JScrollPane scroll = new JScrollPane(table);
scroll.setHorizontalScrollBarPolicy(JScrollPane.HORIZONTAL_SCROLLBAR_AS_NEEDED);
scroll.setVerticalScrollBarPolicy(JScrollPane.VERTICAL_SCROLLBAR_AS_NEEDED);
pstmt = con.prepareStatement(
"select s_name from SC where c_name = ?");
pstmt.setString(1, s1);
rs = pstmt.executeQuery();
while (rs.next()) {
st_name = rs.getString(1);
// This all coloumn names are taken from sc table.
model.addRow(new Object[] { st_name});
} // while
frmdisp.add(scroll);
frmdisp.setVisible(true);
frmdisp.setSize(400, 400);
}
} // try
catch (Exception e) {
JOptionPane.showMessageDialog(null, e, "Error", JOptionPane.ERROR_MESSAGE);
}
}
public static void main(String[] args) {
new Student_Course();
}
}// class
class AddStudent extends JFrame implements ActionListener {
JTextField txtroll, txtname, txtclass;
JButton btnadd, btnclear;
AddStudent() {
setTitle("Student Information");
setSize(400, 500);
setVisible(true);
setLayout(new GridLayout(4, 2, 40, 40));
setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
JLabel rollno = new JLabel("Enter Roll Number: ");
add(rollno);
txtroll = new JTextField(10);
add(txtroll);
JLabel stdname = new JLabel("Enter Student Name: ");
add(stdname);
txtname = new JTextField(10);
add(txtname);
JLabel classname = new JLabel("Enter Student Class: ");
add(classname);
txtclass = new JTextField(10);
add(txtclass);
btnadd = new JButton("Submit");
add(btnadd);
btnadd.addActionListener(this);
btnclear = new JButton("Clear");
add(btnclear);
btnclear.addActionListener(this);
}// constructor
public void actionPerformed(ActionEvent ae) {
String str = ae.getActionCommand();
int rn = Integer.parseInt(txtroll.getText());
String name = (txtname.getText());
String cname = (txtclass.getText());
Connection conn = null;
PreparedStatement pstmt = null;
try {
// load a driver
Class.forName("org.postgresql.Driver");
// Establish Connection
conn = DriverManager.getConnection("jdbc:postgresql://localhost/postgres", "postgres", "dsk");
if (str.equals("Submit")) {
pstmt = conn.prepareStatement("insert into student1 values(?,?,?)");
pstmt.setInt(1, rn);
pstmt.setString(2, name);
pstmt.setString(3, cname);
int result = pstmt.executeUpdate();
System.out.println(result + " Record Inserted");
txtroll.setText("");
txtname.setText("");
txtclass.setText("");
}
pstmt.close();
conn.close();
} // try
catch (Exception e) {
System.out.println(e);
} // catch
}
}
class Addcourse extends JFrame implements ActionListener {
JTextField txtcname, txtins, txtcno;
JButton btnadd, btnclear;
Addcourse() {
setTitle("course Information");
setSize(400, 500);
setVisible(true);
setLayout(new GridLayout(4, 2, 40, 40));
setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
JLabel cno = new JLabel("Enter Course Number: ");
add(cno);
txtcno = new JTextField(10);
add(txtcno);
JLabel cname1 = new JLabel("Enter course Name: ");
add(cname1);
txtcname = new JTextField(10);
add(txtcname);
JLabel ins = new JLabel("Enter course Instructor: ");
add(ins);
txtins = new JTextField(10);
add(txtins);
btnadd = new JButton("Submit");
add(btnadd);
btnadd.addActionListener(this);
btnclear = new JButton("Clear");
add(btnclear);
btnclear.addActionListener(this);
}// constructor
public void actionPerformed(ActionEvent ae) {
String str = ae.getActionCommand();
int cn = Integer.parseInt(txtcno.getText());
String name = (txtcname.getText());
String course = (txtins.getText());
Connection conn = null;
PreparedStatement pstmt = null;
try {
// load a driver
Class.forName("org.postgresql.Driver");
// Establish Connection
conn = DriverManager.getConnection("jdbc:postgresql://localhost/postgres", "postgres", "dsk");
if (str.equals("Submit")) {
pstmt = conn.prepareStatement("insert into course values(?,?,?)");
pstmt.setInt(1, cn);
pstmt.setString(2, name);
pstmt.setString(3, course);
int result = pstmt.executeUpdate();
System.out.println(result + " Record Inserted");
txtcno.setText("");
txtcname.setText("");
txtins.setText("");
}
pstmt.close();
conn.close();
} // try
catch (Exception e) {
System.out.println(e);
} // catch
}
}
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
PGSQL FILE :-
-- create table employee(empid int primary key, empname char(20),empaddress char(30),empcontact bigint ,empage int);
-- create table investor(invno int primary key,invname char(20),invdate varchar(20),invamt int,empid int,foreign key(empid) references employee(empid));
INSERT INTO employee VALUES(1,'Jay','Nagpur',9876787656,34);
INSERT INTO employee VALUES(2,'Aijay','Kolhapur',9876787656,24);
INSERT INTO employee VALUES(3,'Vijay','Rajapur',9876789876,56);
INSERT INTO employee VALUES(4,'Sanjay','Dapur',9876123656,51);
INSERT INTO employee VALUES(5,'Rajesh','Sangamner',9876134656,21);
SELECT * FROM employee;
INSERT INTO investor VALUES(1,'Jay','11/11/2018',34567,1);
INSERT INTO investor VALUES(2,'Aijay','11/12/2019',45643,2);
INSERT INTO investor VALUES(3,'Vijay','11/9/2020',54321,3);
INSERT INTO investor VALUES(4,'Sanjay','11/8/2021',23452,4);
INSERT INTO investor VALUES(5,'Bhavesh','11/3/2022',34532,NULL);
SELECT * FROM investor;
SELECT empname from employee UNION SELECT invname FROM investor;
SELECT empname from employee EXCEPT SELECT invname FROM investor;
PROGRAM:-
import java.sql.*;
public class Employee {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
// load a driver
Class.forName("org.postgresql.Driver");
// Establish Connection
conn = DriverManager.getConnection("jdbc:postgresql://localhost/postgres", "postgres", "dsk");
// create statement and execute queries
stmt = conn.createStatement();
System.out.println("\n---------------");
System.out.println("Employees");
System.out.println("---------------");
rs = stmt.executeQuery("select empname from employee ");
while (rs.next()) {
System.out.println(rs.getString("empname"));
} // while
System.out.println("---------------");
System.out.println("\n---------------");
System.out.println("Investors");
System.out.println("---------------");
rs = stmt.executeQuery("select invname from investor ");
while (rs.next()) {
System.out.println(rs.getString("invname"));
} // while
System.out.println("---------------");
System.out.println(
"\n------------------------------------------------------------------------------------------");
System.out.println(
"Display the List the distinct names of person who are either employees or investor or both");
System.out.println(
"------------------------------------------------------------------------------------------");
rs = stmt.executeQuery("select empname from employee union select invname from investor");
while (rs.next()) {
System.out.println(rs.getString("empname"));
} // while
System.out.println("---------------");
System.out.println("\n--------------------------------------------------");
System.out.println("List the names of Employees who are not Investors");
System.out.println("--------------------------------------------------");
rs = stmt.executeQuery("select empname from employee except select invname from investor");
while (rs.next()) {
System.out.println(rs.getString("empname"));
} // while
System.out.println("---------------");
// close resultset,stmt & connection Object
rs.close();
stmt.close();
conn.close();
} // try
catch (Exception e) {
System.out.println(e);
} // catch
}// main
}// class
No comments:
Post a Comment