Day 22 Methods

package week4;

import java.sql.*;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;
public class Day22Methods {
    private String address = "jdbc:mysql://localhost:3306/db_jtvi_b8_23";
    private String userName = "jtvi23b8"; //default "root"
    private String passWord = "demo";

    int addNewRow(String name, double price, String mfg, String catName) {
        int rowsAffected = 0;
        try {
            Connection conn = DriverManager.getConnection(address, userName, passWord);
            String sqlQuery = "INSERT INTO tbl_price_list (fld_pname ,fld_price"
                    + " ,fld_manufacturer ,fld_cat_name)"
                    + " VALUES (? ,? ,? ,? );";

            PreparedStatement stmt = conn.prepareStatement(sqlQuery);
            stmt.setString(1, name);
            stmt.setDouble(2, price);
            stmt.setString(3, mfg);
            stmt.setString(4, catName);
            rowsAffected = stmt.executeUpdate();

            conn.close();
        } catch (Exception e) {
            rowsAffected = -1;
            System.out.println(e.toString());
        }
        return rowsAffected;
    }

    void getAllRows(JTable tblReference) { // pang jFramee form
        DefaultTableModel tableModel = (DefaultTableModel) tblReference.getModel();
        tableModel.setRowCount(0);
        try {
            Connection conn = DriverManager.getConnection(address, userName, passWord);
            String sqlQuery = "SELECT fld_pid ,fld_pname ,fld_price "
                    + ",fld_manufacturer ,fld_cat_name FROM tbl_price_list;";
            PreparedStatement stmt = conn.prepareStatement(sqlQuery);

            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                Object[] newRow = {
                    rs.getInt(1)
                    , rs.getString(2)
                    , rs.getDouble(3)
                    , rs.getString(4)
                    , rs.getString(5)
                };
                tableModel.addRow(newRow);
            }

            conn.close();
        } catch (Exception e) {
            System.out.println(e.toString());
        }
    }

    void printAllRows() { // pang command line na result
        try {
            Connection conn = DriverManager.getConnection(address, userName, passWord);
            String sqlQuery = "SELECT fld_pid ,fld_pname ,fld_price "
                    + ",fld_manufacturer ,fld_cat_name FROM tbl_price_list;";

            PreparedStatement stmt = conn.prepareStatement(sqlQuery);

            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                System.out.printf("%-2d | %-35s | %-8.2f | %-35s | %-20s%n"
                    , rs.getInt(1)
                    , rs.getString(2)
                    , rs.getString(3)
                    , rs.getString(4)
                    , rs.getString(5)
                );
            }

            System.out.println( rs );
        } catch (Exception e) {
            System.out.println(e.toString());
        }
    }
}

//CREATE TABLE tbl_price_list (
//    fld_pid INT NOT NULL AUTO_INCREMENT PRIMARY KEY
//    ,fld_pname VARCHAR (35) NOT NULL
//    ,fld_price DECIMAL (20, 4) NOT NULL
//    ,fld_manufacturer VARCHAR (35) NOT NULL
//    ,fld_cat_name VARCHAR (20) NOT NULL
//);