day 25

package week4b;

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

    public static void main(String[] args) {
        Day25B callMe = new Day25B();
        String[] row1 = {"pet d", "204", "Dummy", "filler"};
        String[] row2 = {"pet e", "205", "Dummy", "filler"};
        String[] row3 = {"pet f", "206", "Dummy", "filler"};

        callMe.addThreeRows(row1, row2, row3);
    }

    void addThreeRows(String[] row1, String[]row2, String[]row3){
        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 (? ,? ,? ,? );";
            conn.setAutoCommit(false);
            try {

                PreparedStatement stmt = conn.prepareStatement(sqlQuery);

                stmt.setString(1, row1[0]);
                stmt.setString(2, row1[1]);
                stmt.setString(3, row1[2]);
                stmt.setString(4, row1[3]);

                rowsAffected += stmt.executeUpdate();

                stmt = conn.prepareStatement(sqlQuery);
                stmt.setString(1, row2[0]);
                stmt.setString(2, row2[1]);
                stmt.setString(3, row2[2]);
                stmt.setString(4, row2[3]);
                rowsAffected += stmt.executeUpdate();


                stmt = conn.prepareStatement(sqlQuery);
                stmt.setString(1, row3[0]);
                stmt.setString(2, row3[1]);
                stmt.setString(3, row3[2]);
                stmt.setString(4, row3[3]);
                rowsAffected += stmt.executeUpdate();
            } catch (Exception e) {

            }
            if (rowsAffected == 3) {
                conn.commit();
                System.out.println("3 rows commit");
            }else {
                conn.rollback();
                System.out.println("rollback query");
            }

            conn.close();
        } catch (Exception e) {
            System.out.println("exception triggered");
        } finally {
            System.out.println("rows affected: " + rowsAffected);
        }

    }

    String addFromTable(JTable tblIn) {
        int rowsAffected = 0;
        String resultMsg = "";
        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 (? ,? ,? ,? );";
            conn.setAutoCommit(false);

            PreparedStatement stmt;
            for (int i = 0; i < tblIn.getRowCount(); i++) {
                try{
                    stmt = conn.prepareStatement(sqlQuery);

                    stmt.setObject(1, tblIn.getValueAt(i, 0));
                    stmt.setObject(2, tblIn.getValueAt(i, 1));
                    stmt.setObject(3, tblIn.getValueAt(i, 2));
                    stmt.setObject(4, tblIn.getValueAt(i, 3));

                    rowsAffected += stmt.executeUpdate();
                } catch (Exception e) {
                    System.out.println("ping");
                    System.out.println(e.toString());
                }
            }


            if (rowsAffected == tblIn.getRowCount()) {
                conn.commit();
                System.out.println("table transferred");
                resultMsg = "table transferred";
            }else {
                conn.rollback();
                System.out.println("rollback query");
                resultMsg = "rollback query";
            }
            conn.close();
        } catch (Exception e) {
            System.out.println("exception triggered");
        } finally {
            System.out.println("rows affected: " + rowsAffected);
        }
        return resultMsg;
    }

    // 2 things assuming may account level na gagamitin
    int[] loginCheck(String uName, String pWord) {
        int[] userInfo = {0,0}; // ID, LEVEL
        int rowsAffected = 0;
        boolean canContinue = true;
        try {
            Connection conn = DriverManager.getConnection(address, userName, passWord);
            String sqlQuery = "SELECT COUNT(*) FROM tbl_login "
                    + "WHERE fld_username = ? AND fld_password = ?;";
            conn.setAutoCommit(false);
            PreparedStatement stmt = conn.prepareStatement(sqlQuery);
            stmt.setString(1, uName);
            stmt.setString(2, pWord);
            ResultSet rs = stmt.executeQuery();
            rs.next();
            if (rs.getInt(1) == 1) {
                sqlQuery = "SELECT fld_lid, fld_level FROM tbl_login "
                    + "WHERE fld_username = ? AND fld_password = ?;";
                stmt = conn.prepareStatement(sqlQuery);
                stmt.setString(1, uName);
                stmt.setString(2, pWord);
                ResultSet rs2 = stmt.executeQuery();
                rs2.next();
                userInfo[0] = rs2.getInt(1);
                userInfo[1] = rs2.getInt(2);
            } else {
                canContinue = false;
            }
            if (canContinue) {
                sqlQuery = "UPDATE tbl_login SET fld_last_access = CURRENT_TIMESTAMP()"
                        + " WHERE fld_username = ? AND fld_password = ?;";
                stmt = conn.prepareStatement(sqlQuery);
                stmt.setString(1, uName);
                stmt.setString(2, pWord);

                rowsAffected = stmt.executeUpdate();
                if (rowsAffected != 1) {
                    canContinue = false;
                }
            }

            if (canContinue) {
                conn.commit();
            } else {
                conn.rollback();
            }
            conn.close();
        } catch (Exception e) {
            System.out.println(e.toString());
        }
        return userInfo;
    }
}