Day 20 Connection

package week3;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class Day20C {
    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        String fName, lName, jobTitle, gender;
        int age, deptID;

        System.out.print("Enter first name: ");
        fName = sc.nextLine();
        System.out.print("Enter last name: ");
        lName = sc.nextLine();
        System.out.print("Enter Age: ");
        age = sc.nextInt();
        sc.nextLine();
        System.out.print("Enter job title: ");
        jobTitle = sc.nextLine();
        System.out.print("Enter Department ID: ");
        deptID = sc.nextInt();
        sc.nextLine();
        System.out.print("Enter gender(M/F): ");
        gender = sc.nextLine();

        addNewRowTest(fName, lName, age, jobTitle, deptID, gender);

    }

    static void addNewRowTest(String fName, String lName, int age, String jobTitle, int deptID, String gender) {
        String connectionString = "jdbc:mysql://localhost:3306/db_mng_b11";
        String userName = "mngb11";
        String passWord = "2023";
        try {
            Connection conn = DriverManager.getConnection(connectionString, userName, passWord);
            Statement stmt = conn.createStatement();
            String sqlQuery = "INSERT INTO tbl_employee(fld_fname, fld_lname, fld_age"
                    + ", fld_position, fld_did, fld_gender)"
                    + " VALUES ('" + fName + "','" + lName + "','" + age + "'"
                    + ",'" + jobTitle + "','" + deptID + "','" + gender + "');";

            System.out.println(sqlQuery);
            int rowAffected = stmt.executeUpdate(sqlQuery);
            System.out.println(rowAffected + " row(s) affected");
            conn.close();
        }   catch (SQLException ex) {
            System.out.println("error: " + ex.toString());
        }
    }
}

//--- table query------------------
CREATE TABLE tbl_employee
(fld_fname VARCHAR(50)
, fld_lname VARCHAR(50)
, fld_age INT
, fld_position VARCHAR(50)
, fld_did INT
, fld_gender VARCHAR(1)
);

// --- EXECUTE UPDATE INSERT INTO ---------------------------
package week3;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

public class Day20D {
    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        String fName = "", lName = "", jobTitle = "", gender = "";
        int age = 0, deptID = 0;
        boolean canContinue = true;

        try {
            System.out.print("Enter first name: ");
            fName = sc.nextLine();
            System.out.print("Enter last name: ");
            lName = sc.nextLine();
            System.out.print("Enter Age: ");
            age = sc.nextInt();
            sc.nextLine();
            System.out.print("Enter job title: ");
            jobTitle = sc.nextLine();
            System.out.print("Enter Department ID: ");
            deptID = sc.nextInt();
            sc.nextLine();
            System.out.print("Enter gender(M/F): ");
            gender = sc.nextLine();
        } catch (Exception e) {
            System.out.println("One of user input is invalid");
            canContinue = false;
        }
        if (canContinue) {
            int result = addNewRow(fName, lName, age, jobTitle, deptID, gender);
            System.out.println(result + " row(s) affected");
        }
    }

    static int addNewRow(String fName, String lName, int age, String jobTitle, int deptID, String gender) {
        String connectionString = "jdbc:mysql://localhost:3306/db_mng_b11";
        String userName = "mngb11";
        String passWord = "2023";
        int rowAffected = 0;
        try {
            Connection conn = DriverManager.getConnection(connectionString, userName, passWord);

            String sqlQuery = "INSERT INTO tbl_employee "
                    + "(fld_fname, fld_lname, fld_age"
                    + ", fld_position, fld_did, fld_gender)"
                    + " VALUES ( ?, ?, ?, ?, ?, ?);";
            PreparedStatement stmt = conn.prepareStatement(sqlQuery);
            stmt.setString(1, fName);
            stmt.setString(2, lName);
            stmt.setInt(3, age);
            stmt.setString(4, jobTitle);
            stmt.setInt(5, deptID);
            stmt.setString(6, gender);

//            System.out.println(sqlQuery); // nandito lang to pang test
            rowAffected = stmt.executeUpdate();
            conn.close();
        }   catch (SQLException ex) {
            System.out.println("error: " + ex.toString());
        }
        return rowAffected;
    }
}

// --- EXECUTE QUERY SELECT---------------------------
package week3;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Day20E {
    public static void main(String[] args) {
        getAllRows();
    }

    static void getAllRows() {
        String connectionString = "jdbc:mysql://localhost:3306/db_mng_b11";
        String userName = "mngb11";
        String passWord = "2023";
        try {
            Connection conn = DriverManager.getConnection(connectionString, userName, passWord);

            String sqlQuery = "SELECT * FROM tbl_employee;";
            PreparedStatement stmt = conn.prepareStatement(sqlQuery);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                System.out.print(rs.getString(1) + " | ");
                System.out.print(rs.getString(2) + " | ");
                System.out.println(rs.getString(3));
            }
            System.out.println(rs);
//            System.out.println(sqlQuery); // nandito lang to pang test

            conn.close();
        }   catch (SQLException ex) {
            System.out.println("error: " + ex.toString());
        }
    }
}