Advanced Java programming Lab Manual

#imports
import java.sql.*; // For database operations
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane; // For displaying dialog boxes
import javax.swing.table.DefaultTableModel;
import java.io.*; // For file operations and exceptions
import com.itextpdf.text.*; // For PDF document creation
import com.itextpdf.text.pdf.*; // For PDF table and writer

// inside product Form constructor.

connect();
LoadProductNo();
Fetch();

// After product Form

private Connection con;

private PreparedStatement pst;
private ResultSet rs;

#1 Create connection
// after product form

public void connect() {

String url = "jdbc:mysql://localhost/product-crud";
String user = "root";
String password = "";

try {
Class.forName("com.mysql.cj.jdbc.Driver"); // Use the newer driver class name
con = DriverManager.getConnection(url, user, password);
System.out.println("Connection established successfully.");
} catch (ClassNotFoundException e) {
Logger.getLogger(productForm.class.getName()).log(Level.SEVERE, "MySQL JDBC
Driver not found.", e);
} catch (SQLException e) {
Logger.getLogger(productForm.class.getName()).log(Level.SEVERE, "Connection
failed.", e);
}
}

#2 insert code
// double click create button and inside action performed.

if(txtName.getText().isEmpty()){
JOptionPane.showMessageDialog(this,"Product Name is required !!!");
} else if(txtPrice.getText().isEmpty()){
JOptionPane.showMessageDialog(this,"Product Price is required !!!");
} else if(txtQty.getText().isEmpty()){
JOptionPane.showMessageDialog(this,"Product Qty is required !!!");
} else {
try {

1
// Retrieve input values
String name = txtName.getText().trim();
String price = txtPrice.getText().trim();
String qty = txtQty.getText().trim();

// Prepare SQL statement

String sql = "INSERT INTO product (name, price, qty) VALUES (?, ?, ?)";
pst = con.prepareStatement(sql);

// Set parameters
pst.setString(1, name);
pst.setString(2, price);
pst.setString(3, qty);

// Execute the update

int rowsAffected = pst.executeUpdate();

// Check result and show message

if (rowsAffected == 1) {
JOptionPane.showMessageDialog(this, "Record added successfully !!!");
// Clear the input fields
txtName.setText("");
txtPrice.setText("");
txtQty.setText("");
LoadProductNo();
Fetch();
} else {
JOptionPane.showMessageDialog(this, "Failed to save record !!!");
}
} catch (SQLException ex) {
Logger.getLogger(productForm.class.getName()).log(Level.SEVERE, "Database
error:", ex);
JOptionPane.showMessageDialog(this, "An error occurred while saving the
record.");
}
}
#3 Load Product No
//After public void connection

public void LoadProductNo(){

try {

pst = con.prepareStatement("SELECT id FROM product");

rs = pst.executeQuery();
txtpid.removeAllItems();

while(rs.next()){
txtpid.addItem(rs.getString(1));
}
} catch (SQLException ex) {
Logger.getLogger(productForm.class.getName()).log(Level.SEVERE, "Database
error:", ex);
}
}

#4 Search code
// inside Search button event action performed

try {

String pid = txtpid.getSelectedItem().toString();

2
pst = con.prepareStatement("SELECT * FROM product WHERE id=?");
pst.setString(1,pid);
rs = pst.executeQuery();

if(rs.next()==true){
txtName.setText(rs.getString(2));
txtPrice.setText(rs.getString(3));
txtQty.setText(rs.getString(4));
}else{
JOptionPane.showMessageDialog(this,"No record found !!!");
}
} catch (SQLException ex) {
Logger.getLogger(productForm.class.getName()).log(Level.SEVERE,null, ex);
}

#5 Update Product
// inside update button action performed

try {
String name = txtName.getText();
String price = txtPrice.getText();
String qty = txtQty.getText();
String pid = txtpid.getSelectedItem().toString();

pst = con.prepareStatement("UPDATE product SET name=?,price=?,qty=? WHERE id =

?");

pst.setString(1,name);
pst.setString(2,price);
pst.setString(3,qty);
pst.setString(4,pid);
int k=pst.executeUpdate();

if(k==1){
JOptionPane.showMessageDialog(this,"Record updated Successfully !!!");

txtName.setText("");
txtPrice.setText("");
txtQty.setText("");
txtName.requestFocus();
LoadProductNo();
}
} catch (SQLException ex){
Logger.getLogger(productForm.class.getName()).log(Level.SEVERE,null, ex);
}

#6 Delete Product
// inside delete button action performed

try {
String pid = txtpid.getSelectedItem().toString();
pst = con.prepareStatement("DELETE FROM product WHERE id=?");
pst.setString(1, pid);
int k = pst.executeUpdate();
if (k == 1) {
JOptionPane.showMessageDialog(this, "Record has been deleted successfully!");
txtName.setText("");

3
txtPrice.setText("");
txtQty.setText("");
txtName.requestFocus();
LoadProductNo();
} else {
JOptionPane.showMessageDialog(this, "Failed to delete record!");
}
} catch (SQLException ex) {
Logger.getLogger(productForm.class.getName()).log(Level.SEVERE, null, ex);
}

#7 Display Product List from Database to

productDisplayTable.
// right click on table and go to table content to change column name.
//Call Fetch(); inside Add Update Delete.

private void Fetch() {

try {
pst = con.prepareStatement("SELECT id, name, price, qty FROM product");
rs = pst.executeQuery();
DefaultTableModel df = (DefaultTableModel) productDisplayTable.getModel();
df.setRowCount(0);

while (rs.next()) {
Object[] rowData = {
rs.getString("id"),
rs.getString("name"),
rs.getString("price"),
rs.getString("qty")
};
df.addRow(rowData);
}
} catch (SQLException ex) {
Logger.getLogger(productForm.class.getName()).log(Level.SEVERE, null, ex);
}
}

#8 Export Product List to Excel File

// inside btnExcel

String filename = "E:\\ExcelFile.csv";

try (FileWriter fw = new FileWriter(filename);

PreparedStatement pst = con.prepareStatement("SELECT * FROM product");
ResultSet rs = pst.executeQuery()) {

4
while (rs.next()) {
fw.append(rs.getString(1)).append(',')
.append(rs.getString(2)).append(',')
.append(rs.getString(3)).append(',')
.append(rs.getString(4)).append('\n');
}
JOptionPane.showMessageDialog(this, "File exported successfully !!!");
} catch (IOException | SQLException ex) {
Logger.getLogger(productForm.class.getName()).log(Level.SEVERE, null,
ex);
}

# 9 Login Form
// create LoginForm like product form by right click inside default
//package.
// to make login form comes as first change inside main class of product
//form productForm().setVisible(true);to loginForm().setVisible(true);
// copy connection and import code from product form
// create users table with id,username and password.
Imports
import java.sql.*; // For database operations
import java.util.logging.*; // For logging
import javax.swing.*; // For Swing components like JOptionPane

// inside btnLogin

String username = txtUserName.getText();

String password = new String(txtPassword.getPassword());

try (PreparedStatement pst = con.prepareStatement("SELECT * FROM users

WHERE username = ? AND password = ?")) {
pst.setString(1, username);
pst.setString(2, password);

try (ResultSet rs = pst.executeQuery()) {

if (rs.next()) {
new productForm().setVisible(true);

5
} else {
JOptionPane.showMessageDialog(null, "Username or password is incorrect
!!!");
txtUserName.setText("");
txtPassword.setText("");
}
}
} catch (SQLException ex) {
Logger.getLogger(loginForm.class.getName()).log(Level.SEVERE, null,
ex);
}

// inside btnCancel

//System.exit(0);
txtUserName.setText("");
txtPassword.setText("");