pratical1.java

import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import java.util.Vector;
import javax.swing.*;
import javax.swing.event.ListSelectionEvent;
import javax.swing.table.DefaultTableModel;



public class pratical1 extends JFrame {


    private static final String DB_URL = "jdbc:mysql://localhost:3306/startersql?useSSL=false&serverTimezone=UTC";
    private static final String DB_USER = "root";          
    private static final String DB_PASSWORD = "yourpass";  

    // UI components
    private final JTextField tfId = new JTextField(10);
    private final JTextField tfName = new JTextField(20);
    private final JTextField tfEmail = new JTextField(20);
    private final JTextField tfGender = new JTextField(10);
    private final JTextField tfDob = new JTextField(10); 
    private final JTextField tfCreatedAt = new JTextField(20);

    private final JButton btnInsert = new JButton("Insert");
    private final JButton btnUpdate = new JButton("Update");
    private final JButton btnDelete = new JButton("Delete");
    private final JButton btnRefresh = new JButton("Refresh");
    private final JButton btnFirst = new JButton("|< First");
    private final JButton btnPrev = new JButton("< Prev");
    private final JButton btnNext = new JButton("Next >");
    private final JButton btnLast = new JButton("Last >|");

    private final DefaultTableModel model = new DefaultTableModel(
            new Object[]{"ID", "Name", "Email", "Gender", "Date of Birth", "Created At"}, 0
    ) {
        @Override
        public boolean isCellEditable(int row, int column) {
            return false;
        }
    };
    private final JTable table = new JTable(model);

    // JDBC objects
    private Connection conn;
    private Statement navStmt;
    private ResultSet navRs;

    public pratical1() {
        super("Practical 1 — Swing + JDBC CRUD (startersql.users)");
        buildUI();
        connect();
        refreshAll(); 

        addWindowListener(new WindowAdapter() {
            @Override
            public void windowClosing(WindowEvent e) {
                cleanup();
                System.exit(0);
            }
        });

        setSize(900, 560);
        setLocationRelativeTo(null);
        setVisible(true);
    }

    private void buildUI() {
        // form panel
        JPanel form = new JPanel(new GridBagLayout());
        GridBagConstraints gc = new GridBagConstraints();
        gc.insets = new Insets(4, 6, 4, 6);
        gc.fill = GridBagConstraints.HORIZONTAL;

        tfId.setEditable(false);
        tfCreatedAt.setEditable(false);

        int r = 0;
        gc.gridx = 0; gc.gridy = r; form.add(new JLabel("ID:"), gc);
        gc.gridx = 1; form.add(tfId, gc);

        gc.gridx = 2; form.add(new JLabel("Name:"), gc);
        gc.gridx = 3; form.add(tfName, gc);

        r++;
        gc.gridx = 0; gc.gridy = r; form.add(new JLabel("Email:"), gc);
        gc.gridx = 1; form.add(tfEmail, gc);

        gc.gridx = 2; form.add(new JLabel("Gender:"), gc);
        gc.gridx = 3; form.add(tfGender, gc);

        r++;
        gc.gridx = 0; gc.gridy = r; form.add(new JLabel("Date of Birth (yyyy-MM-dd):"), gc);
        gc.gridx = 1; form.add(tfDob, gc);

        gc.gridx = 2; form.add(new JLabel("Created At:"), gc);
        gc.gridx = 3; form.add(tfCreatedAt, gc);

        // buttons row
        JPanel bp = new JPanel(new FlowLayout(FlowLayout.LEFT, 6, 0));
        bp.add(btnInsert);
        bp.add(btnUpdate);
        bp.add(btnDelete);
        bp.add(btnRefresh);
        bp.add(new JSeparator(SwingConstants.VERTICAL));
        bp.add(btnFirst);
        bp.add(btnPrev);
        bp.add(btnNext);
        bp.add(btnLast);

        // table
        table.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
        JScrollPane scroll = new JScrollPane(table);

        // layout
        JPanel top = new JPanel(new BorderLayout());
        top.add(form, BorderLayout.CENTER);
        top.add(bp, BorderLayout.SOUTH);

        setLayout(new BorderLayout(6, 6));
        add(top, BorderLayout.NORTH);
        add(scroll, BorderLayout.CENTER);

        // event handlers
        btnInsert.addActionListener(e -> insertRecord());
        btnUpdate.addActionListener(e -> updateRecord());
        btnDelete.addActionListener(e -> deleteRecord());
        btnRefresh.addActionListener(e -> refreshAll());

        btnFirst.addActionListener(e -> navigate("FIRST"));
        btnPrev.addActionListener(e -> navigate("PREV"));
        btnNext.addActionListener(e -> navigate("NEXT"));
        btnLast.addActionListener(e -> navigate("LAST"));

        table.getSelectionModel().addListSelectionListener((ListSelectionEvent e) -> {
            if (!e.getValueIsAdjusting()) {
                int row = table.getSelectedRow();
                if (row >= 0) {
                    tfId.setText(String.valueOf(model.getValueAt(row, 0)));
                    tfName.setText(String.valueOf(model.getValueAt(row, 1)));
                    tfEmail.setText(String.valueOf(model.getValueAt(row, 2)));
                    tfGender.setText(String.valueOf(model.getValueAt(row, 3)));
                    tfDob.setText(String.valueOf(model.getValueAt(row, 4)));
                    tfCreatedAt.setText(String.valueOf(model.getValueAt(row, 5)));
                }
            }
        });
    }

    private void connect() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver"); // optional for modern drivers
            conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
        } catch (Exception ex) {
            showError("Failed to connect to DB", ex);
        }
    }

    private void refreshAll() {
        loadTable();
        openScrollableResultSet();
    }

    private void loadTable() {
        model.setRowCount(0);
        String sql = "SELECT id, name, email, gender, date_of_birth, created_at FROM users ORDER BY id";
        try (PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {

            while (rs.next()) {
                Vector<Object> row = new Vector<>();
                row.add(rs.getInt("id"));
                row.add(rs.getString("name"));
                row.add(rs.getString("email"));
                row.add(rs.getString("gender"));
                row.add(rs.getDate("date_of_birth"));
                row.add(rs.getTimestamp("created_at"));
                model.addRow(row);
            }

            if (model.getRowCount() > 0) {
                table.setRowSelectionInterval(0, 0);
            } else {
                clearForm();
            }
        } catch (SQLException ex) {
            showError("Failed to load table", ex);
        }
    }

    private void openScrollableResultSet() {
        // close previous
        closeNav();
        String sql = "SELECT id, name, email, gender, date_of_birth, created_at FROM users ORDER BY id";
        try {
            navStmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            navRs = navStmt.executeQuery(sql);
            if (navRs.next()) {
                showCurrentFromNav();
            }
        } catch (SQLException ex) {
            showError("Failed to open navigation ResultSet", ex);
        }
    }

    private void showCurrentFromNav() throws SQLException {
        if (navRs == null) return;
        int id = navRs.getInt("id");
        String name = navRs.getString("name");
        String email = navRs.getString("email");
        String gender = navRs.getString("gender");
        Date dob = navRs.getDate("date_of_birth");
        Timestamp created = navRs.getTimestamp("created_at");

        tfId.setText(String.valueOf(id));
        tfName.setText(name);
        tfEmail.setText(email);
        tfGender.setText(gender);
        tfDob.setText(dob != null ? dob.toString() : "");
        tfCreatedAt.setText(created != null ? created.toString() : "");

        // also select in JTable
        int targetRow = -1;
        for (int r = 0; r < model.getRowCount(); r++) {
            Object v = model.getValueAt(r, 0);
            if (v != null && Integer.parseInt(v.toString()) == id) {
                targetRow = r;
                break;
            }
        }
        if (targetRow >= 0) {
            table.setRowSelectionInterval(targetRow, targetRow);
            table.scrollRectToVisible(table.getCellRect(targetRow, 0, true));
        }
    }

    private void navigate(String where) {
        try {
            if (navRs == null) return;
            boolean moved = false;
            switch (where) {
                case "FIRST": moved = navRs.first(); break;
                case "PREV":  moved = navRs.previous(); if (!moved) moved = navRs.first(); break;
                case "NEXT":  moved = navRs.next(); if (!moved) moved = navRs.last(); break;
                case "LAST":  moved = navRs.last(); break;
            }
            if (moved) showCurrentFromNav();
        } catch (SQLException ex) {
            showError("Navigation error", ex);
        }
    }

    private void insertRecord() {
        String name = tfName.getText().trim();
        String email = tfEmail.getText().trim();
        String gender = tfGender.getText().trim();
        String dobStr = tfDob.getText().trim();

        if (name.isEmpty() || email.isEmpty() || dobStr.isEmpty()) {
            JOptionPane.showMessageDialog(this, "Name, Email and Date of Birth are required.");
            return;
        }

        String sql = "INSERT INTO users (name, email, gender, date_of_birth) VALUES (?, ?, ?, ?)";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, name);
            ps.setString(2, email);
            ps.setString(3, gender);
            ps.setDate(4, java.sql.Date.valueOf(dobStr)); // requires yyyy-MM-dd
            ps.executeUpdate();
            refreshAll();
            JOptionPane.showMessageDialog(this, "Inserted successfully.");
        } catch (SQLException ex) {
            showError("Insert failed", ex);
        } catch (IllegalArgumentException ie) {
            JOptionPane.showMessageDialog(this, "Date must be in yyyy-MM-dd format.");
        }
    }

    private void updateRecord() {
        String idStr = tfId.getText().trim();
        if (idStr.isEmpty()) {
            JOptionPane.showMessageDialog(this, "Select a record first.");
            return;
        }

        String name = tfName.getText().trim();
        String email = tfEmail.getText().trim();
        String gender = tfGender.getText().trim();
        String dobStr = tfDob.getText().trim();

        String sql = "UPDATE users SET name=?, email=?, gender=?, date_of_birth=? WHERE id=?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, name);
            ps.setString(2, email);
            ps.setString(3, gender);
            ps.setDate(4, java.sql.Date.valueOf(dobStr));
            ps.setInt(5, Integer.parseInt(idStr));
            int updated = ps.executeUpdate();
            if (updated > 0) {
                refreshAll();
                JOptionPane.showMessageDialog(this, "Updated successfully.");
            } else {
                JOptionPane.showMessageDialog(this, "No record updated (ID not found).");
            }
        } catch (SQLException ex) {
            showError("Update failed", ex);
        } catch (IllegalArgumentException ie) {
            JOptionPane.showMessageDialog(this, "Date must be in yyyy-MM-dd format.");
        }
    }

    private void deleteRecord() {
        String idStr = tfId.getText().trim();
        if (idStr.isEmpty()) {
            JOptionPane.showMessageDialog(this, "Select a record first.");
            return;
        }
        int confirm = JOptionPane.showConfirmDialog(this, "Delete record ID " + idStr + "?", "Confirm Delete", JOptionPane.YES_NO_OPTION);
        if (confirm != JOptionPane.YES_OPTION) return;

        String sql = "DELETE FROM users WHERE id=?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, Integer.parseInt(idStr));
            int deleted = ps.executeUpdate();
            if (deleted > 0) {
                refreshAll();
                JOptionPane.showMessageDialog(this, "Deleted successfully.");
            } else {
                JOptionPane.showMessageDialog(this, "ID not found.");
            }
        } catch (SQLException ex) {
            showError("Delete failed", ex);
        }
    }

    private void clearForm() {
        tfId.setText("");
        tfName.setText("");
        tfEmail.setText("");
        tfGender.setText("");
        tfDob.setText("");
        tfCreatedAt.setText("");
    }

    private void closeNav() {
        try { if (navRs != null) navRs.close(); } catch (Exception ignored) {}
        try { if (navStmt != null) navStmt.close(); } catch (Exception ignored) {}
        navRs = null; navStmt = null;
    }

    private void cleanup() {
        closeNav();
        try { if (conn != null) conn.close(); } catch (Exception ignored) {}
    }

    private void showError(String msg, Exception ex) {
        ex.printStackTrace();
        JOptionPane.showMessageDialog(this, msg + "\n" + ex.getMessage(), "Error", JOptionPane.ERROR_MESSAGE);
    }

    public static void main(String[] args) {
        SwingUtilities.invokeLater(pratical1::new);
    }
}