CYKABLYETAFSIUAFSIUJGFNSIUFNS

package jtm.activity13;
import java.sql.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

public class TeacherManager {

    protected Connection conn;


    public TeacherManager() {
        try {
            Class.forName("com.mysql.jdbc.Driver"); // Load the driver class.
            conn = DriverManager.getConnection("jdbc:mysql://localhost/?autoReconnect=true&useSSL=false","root", "abcd1234");
            conn.setAutoCommit(false);
        }catch (Exception e){
            e.printStackTrace();
        }

    }

    /**
     * Returns a Teacher instance represented by the specified ID.
     *
     * @param id
     *            the ID of teacher
     * @return a Teacher object
     */
    public Teacher findTeacher(int id) {
        Teacher a = new Teacher (0,null,null);
        Statement st;
        try {
            st = conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM database_activity.Teacher WHERE id = "+id);
            if (rs.next()){

                a.setFirstName(rs.getString(2));
                a.setLastName(rs.getString(3));
                a.setID(id);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return a;
    }

    /**
     * Returns a list of Teacher object that contain the specified first name
     * and last name. This will return an empty List of no match is found.
     *
     * @param firstName
     *            the first name of teacher.
     * @param lastName
     *            the last name of teacher.
     * @return a list of Teacher object.
     */
    public List<Teacher> findTeacher(String firstName, String lastName) {

        List <Teacher> list = new ArrayList<>();
        Statement st;
        try {
            st = conn.createStatement();
            ResultSet rs = st.executeQuery
                    ("SELECT * FROM database_activity.Teacher WHERE firstname LIKE '%"+firstName+"%' AND lastname LIKE '%"+lastName+"%'");
            while (rs.next()){
                list.add(new Teacher (rs.getInt("id"), rs.getString("firstname"), rs.getString("lastname")));

            }
        } catch (SQLException e) {

            e.printStackTrace();
        }
        return list;

    }
        /**
     * Insert an new teacher (first name and last name) into the repository."SELECT * FROM database_activity.Teacher WHERE
     *
     * @param firstName
     *            the first name of teacher
     * @param lastName
     *            the last name of teacher
     * @return true if success, else false.
     */

    public boolean insertTeacher(String firstName, String lastName) {
        Statement st;
        try {
            st = conn.createStatement();
            String sql = "insert into database_activity.Teacher"
                    +"(firstname, lastname) values"
                    +"(?,?)";
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString((1), firstName);
            preparedStatement.setString((2), lastName);
            int rowsAffected = preparedStatement.executeUpdate();

            if (rowsAffected == 1){
                conn.commit();
                return true;
            }else{
                return false;
            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return false;
    }

    /**
     * Insert teacher object into database
     *
     * @param teacher
     * @return true on success, false on error (e.g. non-unique id)
     */
    public boolean insertTeacher(Teacher teacher) {
        Statement st;
        try {
            st = conn.createStatement();
            String sql = "insert into database_activity.Teacher"
                    +"(id,firstname, lastname) values"
                    +"(?,?,?)";
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setInt((1), teacher.getID());
            preparedStatement.setString((2), teacher.getFirstName());
            preparedStatement.setString((3), teacher.getLastName());
            int rowsAffected = preparedStatement.executeUpdate();

            if (rowsAffected == 1){
                conn.commit();
                return true;
            }else{
                return false;
            }

        } catch (SQLException e) {

            e.printStackTrace();
        }
        return false;
    }

    /**
     * Updates an existing Teacher in the repository with the values represented
     * by the Teacher object.
     *
     * @param teacher
     *            a Teacher object, which contain information for updating.
     * @return true if row was updated.
     */
    public boolean updateTeacher(Teacher teacher) {
        boolean status = false;
        Statement st;
        int id = teacher.getID();
        try {
            st = conn.createStatement();
            String name =teacher.getFirstName();
            String surname = teacher.getLastName();
            String sql = "UPDATE database_activity.Teacher SET firstname=?, lastname=? WHERE id="+id;
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString((1), name);
            preparedStatement.setString((2), surname);
            int rowsAffected = preparedStatement.executeUpdate();

            if (rowsAffected == 1){
                status = true;
                conn.commit();
                return true;
            }else{
                return false;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return status;
    }


    public boolean deleteTeacher(int id) {
        boolean status =false;
        Statement st;
        try {
            st = conn.createStatement();
            String sql = "DELETE FROM database_activity.Teacher WHERE id="+id;
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            int rowsAffected = preparedStatement.executeUpdate();
            if (rowsAffected == 1){
                status = true;
                conn.commit();
                return true;
            }else{
                return false;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return status;
    }

    public void closeConnecion() {
        try {
            conn.commit();
            conn.close();
            conn = null;
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}