WT-I Assignment-I

 Assignment 1: TO STUDY HTML, HTML5 & CSS

Set-A

Q1) Create a HTML document to display the following screen.

Q 2. Write a HTML code, which generate the following output

Q3. Write a HTML script to design the following screen

SET B

Q 1. Write the HTML code for generating the form as shown below.

Apply the internal CSS to following form change the font size of the heading to 6pt and change the color to red and also change the background color yellow

Q 2. Write HTML 5 code which generates the following output and display each element of list in different size, color & font. Use external CSS to format the list

Q 3. Create HTML5 page with following specifications

i) Title should be about your City.

ii) Color the background by Pink color.

iii) Place your city name at the top of page in large text and in blue color. iv) Add names of the landmarks in your city, each in different color, style and font

v) Add scrolling text about your City.

vi) Add any image at the bottom. (Use inline CSS to format the web page)

Set C.

Q 1. Design HTML 5 Page Using CSS which display the following Box

( use Box Model Property in CSS)

Q 2Design HTML 5 Page Using CSS Which Display the following Navigation Bar

JAVA-II Assignment-5

 Assignment 5: Spring

Set A

a) Create a Spring core example to display the message “If you can't explain it simply, you don't understand it well enough”.

# hellobeans.java

package springcore_example;

public class hellobean {

    private String message;

    public String getMessage() {

        return message;

    }

    public void setMessage(String message) {

        this.message = message;

    }

    public void sayHello() {

        System.out.println(getMessage());

    }

}

# Main.java

package springcore_example;

import org.springframework.context.ApplicationContext;

import org.springframework.context.support.ClassPathXmlApplicationContext;

public class Main {

    public static void main(String[] args) {

        @SuppressWarnings("resource")

        ApplicationContext context = new 

ClassPathXmlApplicationContext("beans.xml");

        hellobean hello = (hellobean) context.getBean("helloWorld");

        hello.sayHello();

    }

}

# beans.xml

}

<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"

       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

       xsi:schemaLocation="http://www.springframework.org/schema/beans

       http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">

    <bean id="helloWorld" class="springcore_example.hellobean">

        <property name="message" value="If you can't explain it simply, 

you don't understand it well enough"/>

    </bean>

</beans>

}

# beans.xml

}

<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"

       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

       xsi:schemaLocation="http://www.springframework.org/schema/beans

       http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">

    <bean id="helloWorld" class="springcore_example.hellobean">

        <property name="message" value="If you can't explain it simply, 

you don't understand it well enough"/>

    </bean>

</beans>

b) Write a program to display the Current Date using spring.

import java.time.*;

import java.time.format.DateTimeFormatter;

public class CurrentTimeJava8 {

   public static void getCurrentTime(){

        System.out.println("-----Current time of your time zone-----");

        LocalTime time = LocalTime.now();

        System.out.println("Current time of the day: " + time);

    }

    public static void getCurrentTimeWithTimeZone(){

        System.out.println("-----Current time of a different time zone using LocalTime-----");

        ZoneId zoneId = ZoneId.of("America/Los_Angeles");

        LocalTime localTime=LocalTime.now(zoneId);

        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("HH:mm:ss");

        String formattedTime=localTime.format(formatter);

        System.out.println("Current time of the day in Los Angeles: " + formattedTime);

    }

    public static void getCurrentTimeWithOffset(){

        System.out.println("-----Current time of different offset-----");

        ZoneOffset zoneOffset = ZoneOffset.of("-08:00");

        ZoneId zoneId=ZoneId.ofOffset("UTC", zoneOffset);

        LocalTime offsetTime = LocalTime.now(zoneId);

        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("hh:mm a");

        String formattedTime=offsetTime.format(formatter);

        System.out.println("Current time of the day with offset -08:00: " + formattedTime);

    }

}

Set B

a) Design simple student information like Student_id, Student_Name and Student_Age using Spring Framework.

# hellobean.java

package springcore_example;

import java.util.List;

public class hellobean {

    private List<Student> students;

    public List<Student> getStudents() {

        return students;

    }

    public void setStudents(List<Student> students) {

        this.students = students;

    }

    public void printStudents() {

        for (Student student : students) {

            System.out.println("Student ID is: "+student.getId()+student.getName() + " 

is " + student.getAge() + " years old.");

        }

# hellobean.java

package springcore_example;

import java.util.List;

public class hellobean {

    private List<Student> students;

    public List<Student> getStudents() {

        return students;

    }

    public void setStudents(List<Student> students) {

        this.students = students;

    }

    public void printStudents() {

        for (Student student : students) {

            System.out.println("Student ID is: "+student.getId()+student.getName() + " 

is " + student.getAge() + " years old.");

        }

}

}

# Main.java

package springcore_example;

import org.springframework.context.ApplicationContext;

import org.springframework.context.support.ClassPathXmlApplicationContext;

public class Main {

    public static void main(String[] args) {

        ApplicationContext context = new 

ClassPathXmlApplicationContext("beans.xml");

        hellobean hello = (hellobean) context.getBean("helloWorld");

        hello.printStudents();

    }

}

# Student.java

package springcore_example;

public class Student {

    private int id;

    private String name;

    private int age;

    public int getId() {

        return id;

    }

    public void setId(int id) {

        this.id = id;

    }

    public String getName() {

        return name;

    }

    public void setName(String name) {

this.name = name;

    }

    public int getAge() {

        return age;

    }

    public void setAge(int age) {

        this.age = age;

    }

}

beans.xml

<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"

       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

       xsi:schemaLocation="http://www.springframework.org/schema/beans

       http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">

    <bean id="student1" class="springcore_example.Student">

    <property name="id" value="1"/>

    <property name="name" value="Shahrukh"/>

    <property name="age" value="20"/>

</bean>

<bean id="student2" class="springcore_example.Student">

    <property name="id" value="2"/>

    <property name="name" value="Shubham"/>

    <property name="age" value="22"/>

</bean>

<bean id="student3" class="springcore_example.Student">

    <property name="id" value="3"/>

    <property name="name" value="Rahul"/>

    <property name="age" value="23"/>

</bean>

<bean id="helloWorld" class="springcore_example.hellobean">

    <property name="students">

        <list>

            <ref bean="student1"/>

            <ref bean="student2"/>

                        <ref bean="student3"/>

        </list>

    </property>

</bean>

</beans>

b) Design the Employee login form application using spring form MVC validation.

index.jsp

<a href="empform">Add Employee</a>  

<a href="viewemp">View Employees</a>  

Emp.java

package com.javatpoint.beans;  

public class Emp {  

private int id;  

private String name;  

private float salary;  

private String designation;  

  

public Emp() {}  

  

public Emp(int id, String name, float salary, String designation) {  

    super();  

    this.id = id;  

    this.name = name;  

    this.salary = salary;  

    this.designation = designation;  

}  

  

public int getId() {  

    return id;  

}  

public void setId(int id) {  

    this.id = id;  

}  

public String getName() {  

    return name;  

}  

public void setName(String name) {  

    this.name = name;  

}  

public float getSalary() {  

    return salary;  

}  

public void setSalary(float salary) {  

    this.salary = salary;  

}  

public String getDesignation() {  

    return designation;  

}  

public void setDesignation(String designation) {  

    this.designation = designation;  

}  

  

}  

 EmpController.java

package com.javatpoint.controllers;  

  

import java.util.ArrayList;  

import java.util.List;  

import org.springframework.stereotype.Controller;  

import org.springframework.web.bind.annotation.ModelAttribute;  

import org.springframework.web.bind.annotation.RequestMapping;  

import org.springframework.web.bind.annotation.RequestMethod;  

import org.springframework.web.servlet.ModelAndView;  

import com.javatpoint.beans.Emp;  

@Controller  

public class EmpController {  

  

    @RequestMapping("/empform")  

    public ModelAndView showform(){  

         //command is a reserved request attribute name, now use <form> tag to show object data  

        return new ModelAndView("empform","command",new Emp());  

    }  

    @RequestMapping(value="/save",method = RequestMethod.POST)  

    public ModelAndView save(@ModelAttribute("emp") Emp emp){  

        //write code to save emp object  

        //here, we are displaying emp object to prove emp has data  

        System.out.println(emp.getName()+" "+emp.getSalary()+" "+emp.getDesignation());  

          

        //return new ModelAndView("empform","command",emp);//will display object data  

        return new ModelAndView("redirect:/viewemp");//will redirect to viewemp request mapping  

    }  

      

    @RequestMapping("/viewemp")  

    public ModelAndView viewemp(){  

        //write the code to get all employees from DAO  

        //here, we are writing manual code of list for easy understanding  

        List<Emp> list=new ArrayList<Emp>();  

        list.add(new Emp(1,"rahul",35000f,"S.Engineer"));  

        list.add(new Emp(2,"aditya",25000f,"IT Manager"));  

        list.add(new Emp(3,"sachin",55000f,"Care Taker"));  

          

        return new ModelAndView("viewemp","list",list);  

    }  

}  

web.xml

<?xml version="1.0" encoding="UTF-8"?>  

<web-app version="2.5"   

    xmlns="http://java.sun.com/xml/ns/javaee"   

    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"   

    xsi:schemaLocation="http://java.sun.com/xml/ns/javaee   

    http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">  

 <servlet>  

    <servlet-name>spring</servlet-name>  

    <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>  

    <load-on-startup>1</load-on-startup>  

</servlet>  

<servlet-mapping>  

    <servlet-name>spring</servlet-name>  

    <url-pattern>/</url-pattern>  

</servlet-mapping>  

</web-app>  

spring-servlet.xml

<?xml version="1.0" encoding="UTF-8"?>  

<beans xmlns="http://www.springframework.org/schema/beans"    

    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"    

    xmlns:p="http://www.springframework.org/schema/p"    

    xmlns:context="http://www.springframework.org/schema/context"    

    xsi:schemaLocation="http://www.springframework.org/schema/beans    

http://www.springframework.org/schema/beans/spring-beans-3.0.xsd    

http://www.springframework.org/schema/context    

http://www.springframework.org/schema/context/spring-context-3.0.xsd">    

  

<context:component-scan base-package="com.javatpoint.controllers"></context:component-scan>  

  

<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">  

<property name="prefix" value="/WEB-INF/jsp/"></property>  

<property name="suffix" value=".jsp"></property>  

</bean>  

  

</beans>  

empform.jsp

<%@ taglib uri="http://www.springframework.org/tags/form" prefix="form"%>    

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>    

  

       <form:form method="post" action="save">    

        <table >    

         <tr>    

          <td>Name : </td>   

          <td><form:input path="name"  /></td>  

         </tr>    

         <tr>    

          <td>Salary :</td>    

          <td><form:input path="salary" /></td>  

         </tr>   

         <tr>    

          <td>Designation :</td>    

          <td><form:input path="designation" /></td>  

         </tr>   

         <tr>    

          <td colspan="2"><input type="submit" value="Save" /></td>    

         </tr>    

        </table>    

       </form:form>    

viewemp.jsp

   <%@ taglib uri="http://www.springframework.org/tags/form" prefix="form"%>    

   <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>    

  

  

<table border="2" width="70%" cellpadding="2">  

<tr><th>Id</th><th>Name</th><th>Salary</th><th>Designation</th></tr>  

   <c:forEach var="emp" items="${list}">   

   <tr>  

   <td>${emp.id}</td>  

   <td>${emp.name}</td>  

   <td>${emp.salary}</td>  

   <td>${emp.designation}</td>  

   </tr>  

   </c:forEach>  

   </table>  

JAVA-II Assignmnet-4

 Assignment 4: Servlets and JSP

Set A

a) Design a servlet that provides information about a HTTP request from a client, such as IP address and browser type. The servlet also provides information about the server on which the servlet is running, such as the operating system type, and the names of currently loaded servlets.

index.html

<html>    

<body>        

<form action="lmn" method="get">      

Username:

<input type="text" name="t1">                      

<input type="submit" >        

</form>    </body></html>

web.xml

<web-app>

<servlet>

<servlet-name>abc</servlet-name>

<servlet-class>serverInfo</servlet-class>

</servlet>

<servlet-mapping>

<servlet-name>abc</servlet-name>

<url-pattern>/lmn</url-pattern>

</servlet-mapping>

</web-app>

serverInfo.java

serverInfo.java

import java.io.*;

        import javax.servlet.*;

        import javax.servlet.http.*;

public class serverInfo extends HttpServlet implements Servlet

{

    protected void doGet(HttpServletRequest req,HttpServletResponse res)throws 

IOException,ServletException

    {

        res.setContentType("text/html");

        PrintWriter pw=res.getWriter();

        pw.println("<html><body><h2>Information about Http Request</h2>");

        pw.println("<br>Server Name: "+req.getServerName());

        pw.println("<br>Server Port: "+req.getServerPort());

        pw.println("<br>Ip Address: "+req.getRemoteAddr());

//pw.println("<br>Server Path: "+req.getServerPath());        pw.println("<br>Client Browser: 

"+req.getHeader("User-Agent"));

        pw.println("</body></html>");

        pw.close();

    }

}

//pw.println("<br>Server Path: "+req.getServerPath());        pw.println("<br>Client Browser: 

"+req.getHeader("User-Agent"));

        pw.println("</body></html>");

        pw.close();

    }

}

b) Write a Program to make use of following JSP implicit objects:

i. out: To display current Date and Time.

ii. request: To get header information.

iii. response: To Add Cookie

iv. config: get the parameters value defined in <init-param>

v. application: get the parameter value defined in <context-param>

vi. session: Display Current Session ID

vii. pageContext: To set and get the attributes.

viii. page: get the name of Generated Servlet

index.jsp

<%@ page language="java"%>

<%@ page import="java.util.Date" %>

<!DOCTYPE html>

<head>

<title>Index JSP Page</title>

</head>

<body>

<%-- out object example --%>

<table border="1"; style="background-color:#ffffcc; width:30%" >

<caption><h3>JSP Implicit object</h3></caption>

<tr><td><b>Current Time</b></td>

<td><% out.print(new Date()); %></td></tr>

<%-- response object example --%>

<%response.addCookie(new Cookie("Test","Value")); %>

<%-- application object example --%>

<tr><td><b>User context param value</b></td>

<td><%=application.getInitParameter("User") %></td></tr>

<%-- session object example --%>

<tr><td><b>User Session ID</b></td>

<td><%=session.getId() %></td></tr>

<%-- pageContext object example --%>

<% pageContext.setAttribute("Test", "Test Value"); %>

<tr><td><b>PageContext attribute</b></td>

<td>{Name="Test",Value="<%=pageContext.getAttribute("Test") %>"}</td></tr>

<%-- page object example --%>

<tr><td><b>Generated Servlet Name</b>:</td>

<td><%=page.getClass().getName() %></td></tr>

<%-- request object example --%>

<tr><td><b>Request User-Agent</b></td>

<td> <%=request.getHeader("User-Agent") %></td></tr>

</table>

</body>

</html>

<tr><td><b>Generated Servlet Name</b>:</td>

<td><%=page.getClass().getName() %></td></tr>

<%-- request object example --%>

<tr><td><b>Request User-Agent</b></td>

<td> <%=request.getHeader("User-Agent") %></td></tr>

</table>

</body>

</html>

web.xml

<web-app>

<welcome-file-list>

,,,,,,,,<welcome-file>index.jsp</welcome-file>

,,,,</welcome-file-list>,,

,,,,<context-param>

,,,,,,,,<param-name>User</param-name>

,,,,,,,,<param-value>Archana</param-value>

,,,,</context-param>

,,,,<servlet>

,,,,,,,,<servlet-name>xyz</servlet-name>

,,,,,,,,<jsp-file>/index.jsp</jsp-file>

,,,,</servlet>

,,,,<servlet-mapping>

,,,,,,,,<servlet-name>xyz</servlet-name>

,,,,,,,,<url-pattern>/test</url-pattern>

,,,,</servlet-mapping>

</web-app>

web.xml

<web-app>

<welcome-file-list>

,,,,,,,,<welcome-file>index.jsp</welcome-file>

,,,,</welcome-file-list>,,

,,,,<context-param>

,,,,,,,,<param-name>User</param-name>

,,,,,,,,<param-value>Archana</param-value>

,,,,</context-param>

,,,,<servlet>

,,,,,,,,<servlet-name>xyz</servlet-name>

,,,,,,,,<jsp-file>/index.jsp</jsp-file>

,,,,</servlet>

,,,,<servlet-mapping>

,,,,,,,,<servlet-name>xyz</servlet-name>

,,,,,,,,<url-pattern>/test</url-pattern>

,,,,</servlet-mapping>

</web-app>

c) Write a program to create a Online Book purchase. User must be login and then purchase the book. Each page should have a page total. The last page should display a total book and bill, which consists of a page total of what ever the purchase has been done and print the total. (Use HttpSession)

Set B

a) Design an HTML page which passes customer number to a search servlet. The servlet searches for the customer number in a database (customer table) and returns customer details if found the number otherwise display error message.

PGSQL FIle :-

create table customer(name char(20),address char(20),id int);

select * from customer;

import java.io.*;

import javax.servlet.*;

import javax.servlet.http.*;

import java.sql.*;

import java.util.*;

public class customer extends HttpServlet {

    public void doGet(HttpServletRequest request, HttpServletResponse responce) throws IOException, ServletException {

        responce.setContentType("text/html");

        PrintWriter out = responce.getWriter();

        try {

            Scanner sc = new Scanner(System.in);

            Connection con = null;

            Statement st = null;

            ResultSet rs = null;

            // load driver

            Class.forName("org.postgresql.Driver");

            // establish a conn

            con = DriverManager.getConnection("jdbc:postgresql://localhost/bcs", "postgres", "");

            int cnum = Integer.parseInt(request.getParameter("num"));

            st = con.createStatement();

            rs = st.executeQuery("select * from cust where id=" + cnum);

            while (rs.next()) {

                out.println(rs.getString(1) + " " + rs.getString(2) + " " + rs.getInt(3));

                out.println("<br>");

            }

        } catch (Exception e) {

            out.println(e);

        }

        out.close();

    }

}

Customer.jsp

<html>

  <body>

    <title>Customer Table</title>

    <form method="get" action="http://localhost:8080/servlets/customer">

      Enter Cust No:<input type="text" name="num" />

      <br />

      <input type="submit" name="submit" value="submit" />

    </form>

  </body>

</html>

b) Design an HTML page containing option buttons (Maths, Physics, Chemistry and Biology) and buttons submit and reset. When the user clicks submit, the server responds by adding a cookie containing the selected subject and sends a message back to the client. Program should not allow duplicate cookies to be written.

B2.jsp

<html>

  <head>

    <title>cookie</title>

  </head>

  <body>

    <form method="get" action="http://localhost:8080/Varient/B2">

      <input type="checkbox" name="sub" value="phy" />Physics<br />

      <input type="checkbox" name="sub" value="chem" />Chemistry<br />

      <input type="checkbox" name="sub" value="bio" />Bio<br />

      <input type="checkbox" name="sub" value="math" />Math <br />

      <br />

      <input type="Submit" value="sub" />

      <input type="Reset" value="clear" />

    </form>

  </body>

</html>

B2.java

import java.io.*;

import javax.servlet.*;

import javax.servlet.http.*;

import java.util.*;

public class B2 extends HttpServlet {

    public void doGet(HttpServletRequest req, HttpServletResponse res) throws IOException, ServletException {

        res.setContentType("text/html");

        PrintWriter out = res.getWriter();

        String allsub = "";

        String lang[] = req.getParameterValues("sub");

        for (int i = 0; i < lang.length; i++) {

            allsub = allsub + lang[i];

        }

        Cookie c1 = new Cookie("sub1", allsub);

        res.addCookie(c1);

        out.println("cookie added with value: " + allsub);

        out.println("<br>");

        out.close();

    }

}

c) Write a JSP program to display the details of PATIENT (PatientNo, PatientName, PatientAddress, Patientage,PatientDiease) in tabular form on browser

<!DOCTYPE html>

<html>

<body>

    <%@ page import="java.sql.*;" %>

        <%! int pno,age; 

        String pname,address,diease; %>

            <% try {

                Class.forName("org.postgresql.Driver"); 

                Connection con = DriverManager.getConnection("jdbc:postgresql:postgres","postgres", " ");

                Statement st=cn.createStatement();

                ResultSetrs=st.executeQuery("select * from Patient");

                 %>

                <table border="1" width="40%">

                    <tr>

                        <td>Patient No</td>

                        <td>Name</td>

                        <td>Age</td>

                        <td>Address</td

                        <td>Diease</td>

                    </tr>

                    <% while(rs.next()) { %>

                        <tr>

                            <td>

                                <%= rs.getInt("pno") %>

                            </td>

                            <td>

                                <%= rs.getString("pname") %>

                            </td>

                            <%= rs.getInt("age") %>

                             </td>

                            

                            <td>

                                <%= rs.getString("address") %>                          

                             <td>

                                <%= rs.getString("diease") %>

                             </td>

                             

                            </tr>

                        <% 

                        }

                         cn.close(); 

                        }

                        catch(Exception e) {

                         out.println(e); 

                        }

                         %>

</body>

</html>

Set C

a) Create a JSP page for an online multiple choice test. The questions are randomly selected from a database and displayed on the screen. The choices are displayed using radio buttons. When the user clicks on next, the next question is displayed.When the user clicks on submit, display the total score on the screen.

b) Consider the following entities and their relationships Movie (movie_no, movie_name, release_year) Actor(actor_no, name) Relationship between movie and actor is many – many with attribute rate in Rs. Create a RDB in 3 NF answer the following: 

a) Accept an actor name and display all movie names in which he has acted along with his name on top. b) Accept a movie name and list all actors in that movie along with the movie name on top.

JAVA-II Assignment-3

 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