//practical-1 db_connectivity
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class Practical_1 {
    public static void main(String[] args) {
        Connection con = null;

        try {
            // Load JDBC Driver
            Class.forName("com.mysql.cj.jdbc.Driver");

            // Database details
            String url = "jdbc:mysql://localhost:3307/college";
            String user = "root";
            String password = "";

            // Establish connection
            con = DriverManager.getConnection(url, user, password);

            System.out.println("Database Connected Successfully!");

        } catch (ClassNotFoundException e) {
            System.out.println("Error: JDBC Driver not found");
        } catch (SQLException e) {
            System.out.println("Error: Database connection failed");
            System.out.println("Reason: " + e.getMessage());
        } finally {
            try {
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                System.out.println("Error while closing connection");
            }
        }
    }
}


//practical-2 CRUD operations
package practical_2;

import java.sql.*;
import java.util.Scanner;

public class Practical_2 {
    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");

            Connection con = DriverManager.getConnection(
                "jdbc:mysql://localhost:3307/jdbc_demo", "root", "");

            Statement stmt = con.createStatement();
            Scanner sc = new Scanner(System.in);

            // Create table
            String createTable = "CREATE TABLE IF NOT EXISTS student (" +
                    "student_id INT PRIMARY KEY, " +
                    "name VARCHAR(50), " +
                    "course VARCHAR(50), " +
                    "marks INT)";
            stmt.executeUpdate(createTable);

            int choice;

            do {
                System.out.println("\n----- STUDENT MENU -----");
                System.out.println("1. Insert Student");
                System.out.println("2. Display Students");
                System.out.println("3. Update Student");
                System.out.println("4. Delete Student");
                System.out.println("5. Exit");

                System.out.print("Enter your choice: ");
                choice = sc.nextInt();
                sc.nextLine();

                switch (choice) {

                    case 1:
                        System.out.print("Enter ID: ");
                        int id = sc.nextInt(); sc.nextLine();

                        System.out.print("Enter Name: ");
                        String name = sc.nextLine();

                        System.out.print("Enter Course: ");
                        String course = sc.nextLine();

                        System.out.print("Enter Marks: ");
                        int marks = sc.nextInt(); sc.nextLine();

                        String insert = "INSERT INTO student VALUES (" +
                                id + ",'" + name + "','" + course + "'," + marks + ")";
                        stmt.executeUpdate(insert);

                        System.out.println("Inserted successfully");
                        break;

                    case 2:
                        ResultSet rs = stmt.executeQuery("SELECT * FROM student");

                        while (rs.next()) {
                            System.out.println(
                                rs.getInt("student_id") + " " +
                                rs.getString("name") + " " +
                                rs.getString("course") + " " +
                                rs.getInt("marks"));
                        }
                        break;

                    case 3:
                        System.out.print("Enter ID to update: ");
                        int uid = sc.nextInt(); sc.nextLine();

                        System.out.print("New Course: ");
                        String newCourse = sc.nextLine();

                        System.out.print("New Marks: ");
                        int newMarks = sc.nextInt(); sc.nextLine();

                        String update = "UPDATE student SET course='" + newCourse +
                                "', marks=" + newMarks +
                                " WHERE student_id=" + uid;

                        int rowsUpdated = stmt.executeUpdate(update);

                        if (rowsUpdated > 0)
                            System.out.println("Updated successfully");
                        else
                            System.out.println("ID not found");
                        break;

                    case 4:
                        System.out.print("Enter ID to delete: ");
                        int did = sc.nextInt(); sc.nextLine();

                        String delete = "DELETE FROM student WHERE student_id=" + did;

                        int rowsDeleted = stmt.executeUpdate(delete);

                        if (rowsDeleted > 0)
                            System.out.println("Deleted successfully");
                        else
                            System.out.println("ID not found");
                        break;

                    case 5:
                        System.out.println("Exiting...");
                        break;

                    default:
                        System.out.println("Invalid choice");
                }

            } while (choice != 5);

        } catch (Exception e) {
            System.out.println("Error: " + e.getMessage());
        }
    }
}


//practical-3 GUI
//db.java
package db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DB {
    public static Connection getCon() {
        Connection con = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");

            con = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/practical3_db",
                "root",
                ""
            );

            System.out.println("Database Connected Successfully");

        } catch (ClassNotFoundException e) {
            System.out.println("MySQL Driver Not Found");
        } catch (SQLException e) {
            System.out.println("Database Connection Failed");
        }

        return con;
    }
}

//register.java
package gui;

import db.DB;
import java.sql.*;
import javax.swing.*;
import java.awt.event.*;

public class Register extends JFrame {
    JTextField txtName, txtEmail, txtPhone;
    JButton btnSave;

    public Register() {
        setTitle("Registration Form");
        setSize(350, 250);
        setLayout(null);
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

        JLabel l1 = new JLabel("Name:");
        l1.setBounds(30, 30, 100, 25);
        add(l1);

        txtName = new JTextField();
        txtName.setBounds(120, 30, 150, 25);
        add(txtName);

        JLabel l2 = new JLabel("Email:");
        l2.setBounds(30, 70, 100, 25);
        add(l2);

        txtEmail = new JTextField();
        txtEmail.setBounds(120, 70, 150, 25);
        add(txtEmail);

        JLabel l3 = new JLabel("Phone:");
        l3.setBounds(30, 110, 100, 25);
        add(l3);

        txtPhone = new JTextField();
        txtPhone.setBounds(120, 110, 150, 25);
        add(txtPhone);

        btnSave = new JButton("Save");
        btnSave.setBounds(120, 160, 80, 30);
        add(btnSave);

        btnSave.addActionListener(e -> saveData());

        setVisible(true);
    }

    void saveData() {
        Connection con = DB.getCon();

        if (con == null) {
            JOptionPane.showMessageDialog(this, "Database Not Connected");
            return;
        }

        try {
            String sql = "INSERT INTO user (name, email_id, phone_number) VALUES (?, ?, ?)";
            PreparedStatement ps = con.prepareStatement(sql);

            ps.setString(1, txtName.getText());
            ps.setString(2, txtEmail.getText());
            ps.setString(3, txtPhone.getText());

            ps.executeUpdate();

            JOptionPane.showMessageDialog(this, "Data Saved Successfully");

            new ViewData();
            this.dispose();

        } catch (SQLException e) {
            JOptionPane.showMessageDialog(this, "Insert Failed");
        }
    }

    public static void main(String[] args) {
        new Register();
    }
}
//view data
package gui;

import db.DB;
import java.sql.*;
import javax.swing.*;
import javax.swing.table.DefaultTableModel;

public class ViewData extends JFrame {
    JTable table;

    public ViewData() {
        setTitle("User Data");
        setSize(500, 300);
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

        table = new JTable();
        DefaultTableModel model = new DefaultTableModel();

        model.addColumn("ID");
        model.addColumn("Name");
        model.addColumn("Email");
        model.addColumn("Phone");

        table.setModel(model);

        Connection con = DB.getCon();

        try {
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM user");

            while (rs.next()) {
                model.addRow(new Object[]{
                    rs.getInt("id"),
                    rs.getString("name"),
                    rs.getString("email_id"),
                    rs.getString("phone_number")
                });
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        add(new JScrollPane(table));
        setVisible(true);
    }
}


//practical-4 authentication
index.html
<!DOCTYPE html>
<html>
<head>
    <title>User Login</title>
</head>
<body>
    <h2>User Authentication</h2>
    <form action="LoginServlet" method="post">
        Username: <input type="text" name="username"><br><br>
        Password: <input type="password" name="password"><br><br>
        <input type="submit" value="Login">
    </form>
</body>
</html>
//loginservlet.java
import java.io.*;
import java.sql.*;
import java.util.Enumeration;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.WebServlet;

@WebServlet("/LoginServlet")
public class LoginServlet extends HttpServlet {

    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        response.setContentType("text/html");
        PrintWriter out = response.getWriter();

        String username = request.getParameter("username");
        String password = request.getParameter("password");

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");

            Connection con = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/practical_4", "root", "");

            PreparedStatement ps = con.prepareStatement(
                "SELECT * FROM user WHERE username=? AND password=?");

            ps.setString(1, username);
            ps.setString(2, password);

            ResultSet rs = ps.executeQuery();

            if (rs.next())
                out.println("<h2>Login Successful</h2>");
            else
                out.println("<h2>Invalid Credentials</h2>");

            // Request headers
            Enumeration<String> headers = request.getHeaderNames();
            while (headers.hasMoreElements()) {
                String h = headers.nextElement();
                out.println(h + ": " + request.getHeader(h) + "<br>");
            }

            con.close();

        } catch (Exception e) {
            out.println("Error: " + e.getMessage());
        }
    }
}


//practical-5 student result
//studentservlet.java
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.WebServlet;

@WebServlet("/StudentServlet")
public class StudentServlet extends HttpServlet {

    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        response.setContentType("text/html");
        PrintWriter out = response.getWriter();

        String name = request.getParameter("name");
        String id = request.getParameter("id");
        String dept = request.getParameter("dept");

        HttpSession session = request.getSession();
        session.setAttribute("name", name);
        session.setAttribute("id", id);
        session.setAttribute("dept", dept);

        out.println("<h3>Welcome, " + name + "</h3>");
        out.println("<form action='MarksServlet' method='post'>");

        for (int i = 1; i <= 6; i++) {
            out.println("Subject " + i + ": <input type='number' name='m" + i + "'><br><br>");
        }

        out.println("<input type='submit' value='Generate Result'>");
        out.println("</form>");
    }
}
//marksServlet.java
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.WebServlet;

@WebServlet("/MarksServlet")
public class MarksServlet extends HttpServlet {

    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        response.setContentType("text/html");
        PrintWriter out = response.getWriter();

        HttpSession session = request.getSession();

        String name = (String) session.getAttribute("name");
        String id = (String) session.getAttribute("id");
        String dept = (String) session.getAttribute("dept");

        int m1 = Integer.parseInt(request.getParameter("m1"));
        int m2 = Integer.parseInt(request.getParameter("m2"));
        int m3 = Integer.parseInt(request.getParameter("m3"));
        int m4 = Integer.parseInt(request.getParameter("m4"));
        int m5 = Integer.parseInt(request.getParameter("m5"));
        int m6 = Integer.parseInt(request.getParameter("m6"));

        boolean pass = m1>=35 && m2>=35 && m3>=35 &&
                       m4>=35 && m5>=35 && m6>=35;

        out.println("<h2>Result</h2>");
        out.println("Name: " + name + "<br>");
        out.println("ID: " + id + "<br>");
        out.println("Department: " + dept + "<br><br>");

        if (pass)
            out.println("<h2 style='color:green'>PASS</h2>");
        else
            out.println("<h2 style='color:red'>FAIL</h2>");

        session.invalidate();
    }
}


//practical-6 request redirection
//index.html
<!DOCTYPE html>
<html>
<head>
    <title>Request Redirection</title>
</head>
<body>
    <h2>Servlet Request Redirection</h2>
    <form action="RedirectServlet" method="post">
        Enter URL:
        <input type="text" name="location" placeholder="https://www.google.com">
        <br><br>
        <input type="submit" value="Go">
    </form>
</body>
</html>
//redirectServlet.java
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.WebServlet;

@WebServlet("/RedirectServlet")
public class RedirectServlet extends HttpServlet {

    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        String url = request.getParameter("location");

        if (url != null && !url.isEmpty()) {
            response.sendRedirect(url);
        } else {
            response.setContentType("text/html");
            PrintWriter out = response.getWriter();
            out.println("<h3>Please enter a valid URL</h3>");
        }
    }
}


//practical-7 JSP user authentication
//login.jsp
<%@ page language="java" %>
<html>
<head>
    <title>User Login</title>
</head>
<body>
<h2>User Authentication - JSP</h2>

<form action="authenticate.jsp" method="post">
    Username: <input type="text" name="username"><br><br>
    Password: <input type="password" name="password"><br><br>
    <input type="submit" value="Login">
</form>

</body>
</html>
//authenticate.jsp
<%@ page import="java.sql.*" %>

<%
    String user = request.getParameter("username");
    String pass = request.getParameter("password");

    boolean valid = false;

    try {
        Class.forName("com.mysql.cj.jdbc.Driver");

        Connection con = DriverManager.getConnection(
            "jdbc:mysql://localhost:3307/jsp_db",
            "root", ""
        );

        PreparedStatement ps = con.prepareStatement(
            "SELECT * FROM users WHERE username=? AND password=?"
        );

        ps.setString(1, user);
        ps.setString(2, pass);

        ResultSet rs = ps.executeQuery();

        if (rs.next()) {
            valid = true;
        }

        rs.close();
        ps.close();
        con.close();

    } catch (Exception e) {
        out.println("Error: " + e);
    }

    if (valid) {
%>
        <h2 style="color:green">Login Successful</h2>
        <h3>Welcome, <%= user %></h3>
<%
    } else {
%>
        <h2 style="color:red">Invalid Username or Password</h2>
<%
    }
%>


//practical-8 jsp bank account
//AccountBean.java
package bean;

import java.io.Serializable;

public class AccountBean implements Serializable {

    private int accountNo;
    private String name;
    private double balance;

    // Constructor
    public AccountBean() {
        balance = 0.0;
    }

    // Getters & Setters
    public int getAccountNo() {
        return accountNo;
    }

    public void setAccountNo(int accountNo) {
        this.accountNo = accountNo;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public double getBalance() {
        return balance;
    }

    // Business Methods
    public void deposit(double amount) {
        balance += amount;
    }

    public boolean withdraw(double amount) {
        if (amount <= balance) {
            balance -= amount;
            return true;
        }
        return false;
    }
}

//account.jsp
<%@ page import="bean.AccountBean" %>
<jsp:useBean id="acc" class="bean.AccountBean" scope="session" />

<html>
<head>
    <title>Bank Account</title>
</head>

<body>

<h2>Bank Account Operations</h2>

<form method="post">
    Account No: <input type="text" name="ano"><br><br>
    Name: <input type="text" name="name"><br><br>
    Amount: <input type="text" name="amount"><br><br>

    <input type="submit" name="action" value="Deposit">
    <input type="submit" name="action" value="Withdraw">
</form>

<%
    String action = request.getParameter("action");

    if (action != null) {
        int ano = Integer.parseInt(request.getParameter("ano"));
        String name = request.getParameter("name");
        double amount = Double.parseDouble(request.getParameter("amount"));

        acc.setAccountNo(ano);
        acc.setName(name);

        if (action.equals("Deposit")) {
            acc.deposit(amount);
        } else if (action.equals("Withdraw")) {
            boolean success = acc.withdraw(amount);
            if (!success) {
                out.println("<p style='color:red'>Insufficient Balance</p>");
            }
        }
    }
%>

<h3>Account Information</h3>
Account No: <%= acc.getAccountNo() %><br>
Name: <%= acc.getName() %><br>
Balance: <%= acc.getBalance() %><br>

</body>
</html>


//practical-9 JSP custom tag
//sort.tag
<%@ tag body-content="empty" %>
<%@ attribute name="numbers" required="true" %>

<%
    String input = numbers;
    String[] arr = input.split(",");

    int[] nums = new int[arr.length];

    for(int i = 0; i < arr.length; i++){
        nums[i] = Integer.parseInt(arr[i].trim());
    }

    // Sorting (Ascending)
    for(int i = 0; i < nums.length; i++){
        for(int j = i + 1; j < nums.length; j++){
            if(nums[i] > nums[j]){
                int temp = nums[i];
                nums[i] = nums[j];
                nums[j] = temp;
            }
        }
    }
%>

<h3>Sorted Numbers:</h3>

<%
    for(int n : nums){
        out.print(n + " ");
    }
%>

//index.jsp
<%@ page contentType="text/html" pageEncoding="UTF-8" %>
<%@ taglib prefix="st" tagdir="/WEB-INF/tags" %>

<html>
<head>
    <title>Custom Tag Sorting</title>
</head>

<body>

<h2>Sort 10 Numbers (Custom JSP Tag)</h2>

<form method="post">
    Enter numbers (comma separated): <br><br>
    <input type="text" name="nums" size="40" required>
    <br><br>
    <input type="submit" value="Sort">
</form>

<br>

<%
    String numbers = request.getParameter("nums");
    if(numbers != null){
%>
        <st:sort numbers="<%=numbers%>" />
<%
    }
%>

</body>
</html>

//web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
         version="3.1">

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

</web-app>


//practical-10 jsp application context
//error.jsp
<html>
<head>
    <title>Limit Exceeded</title>
</head>

<body>
<h1 style="color:red;">
Maximum 3 Users Allowed!
Please try again later.
</h1>
</body>
</html>

//index.jsp
<%@ page contentType="text/html" pageEncoding="UTF-8"%>

<%
    Integer users = (Integer)application.getAttribute("users");

    if(users > 3){
        response.sendRedirect("error.jsp");
        return;
    }
%>

<html>
<head>
    <title>Home Page</title>
</head>

<body>
<h2>Welcome User!</h2>
<h3>Active Users: <%= users %></h3>
</body>
</html>

//userListener.java
package listener;

import javax.servlet.ServletContext;
import javax.servlet.http.HttpSessionEvent;
import javax.servlet.http.HttpSessionListener;

public class UserListener implements HttpSessionListener {

    public void sessionCreated(HttpSessionEvent se) {
        ServletContext context = se.getSession().getServletContext();

        Integer userCount = (Integer) context.getAttribute("users");

        if(userCount == null)
            userCount = 0;

        userCount++;

        context.setAttribute("users", userCount);

        System.out.println("User Entered: " + userCount);
    }

    public void sessionDestroyed(HttpSessionEvent se) {
        ServletContext context = se.getSession().getServletContext();

        Integer userCount = (Integer) context.getAttribute("users");

        if(userCount == null)
            userCount = 0;

        userCount--;

        context.setAttribute("users", userCount);

        System.out.println("User Left: " + userCount);
    }
}

//web.xml
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
         version="3.1">

    <listener>
        <listener-class>listener.UserListener</listener-class>
    </listener>

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

</web-app>