activity13

package jtm.activity13;

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;


public class TeacherManager {

    protected Connection conn;


    public TeacherManager() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost/?autoReconnect=true&useSSL=false","root", "abcd1234");
            conn.setAutoCommit(false);
        } catch (SQLException | ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    /**
     * Returns a Teacher instance represented by the specified ID.
     *
     * @param id
     *            the ID of teacher
     * @return a Teacher object
     * @throws SQLException
     */
    public Teacher findTeacher(int id) {

        Teacher teacher = new Teacher(0, null, null);

        try {
            PreparedStatement statement = conn.prepareStatement("select * from database_activity.Teacher where id = ?");
            statement.setString(1, String.valueOf(id));
            ResultSet rs = statement.executeQuery();
            if (rs.next())teacher = new Teacher(Integer.parseInt(rs.getString("id")), rs.getString("firstname"),rs.getString("lastname"));
        } catch (SQLException e) {}

        return teacher;

    }

    /**
     * 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> teacherList = new ArrayList<Teacher>();

        try {
            PreparedStatement statement = conn.prepareStatement("select * from database_activity.Teacher where firstname like ? and lastname like ?");
            statement.setString(1, "%" + firstName +"%");
            statement.setString(2, "%" + lastName +"%");
            ResultSet rs = statement.executeQuery();
            while (rs.next()){
                Teacher teacher =  new Teacher(Integer.parseInt(rs.getString("id")), rs.getString("firstname"),rs.getString("lastname"));
                teacherList.add(teacher);
            }
        } catch (SQLException e) {}

        return teacherList;

    }

    /**
     * Insert an new teacher (first name and last name) into the repository.
     *
     * @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) {


        if (firstName.length() > 44 || lastName.length() > 44) return false;

        try {
            PreparedStatement statement = conn.prepareStatement("insert into database_activity.Teacher (firstname, lastname) values (?,?)");
            statement.setString(1, firstName);
            statement.setString(2, lastName);
            statement.executeUpdate();
            if (statement.getUpdateCount() >0)conn.commit();
        } catch (SQLException e) {}
        return true;
    }

    /**
     * Insert teacher object into database
     *
     * @param teacher
     * @return true on success, false on error (e.g. non-unique id)
     */
    public boolean insertTeacher(Teacher teacher) {

        int id = teacher.getID();
        Teacher teacher2 = findTeacher(id);
        if (teacher2.getID() != 0) return false;

        String firstName = teacher.getFirstName();
        String lastName = teacher.getLastName();

        try {
            PreparedStatement statement = conn.prepareStatement("insert into database_activity.Teacher values (?,?,?)");
            statement.setString(1, String.valueOf(id));
            statement.setString(2, firstName);
            statement.setString(3, lastName);
            statement.executeUpdate();
            if (statement.getUpdateCount() >0)conn.commit();
        } catch (SQLException e) {}
        return true;
    }

    /**
     * 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) {

        int id = teacher.getID();
        Teacher teacher2 = findTeacher(id);
        if (teacher2.getID() == 0) return false;


        String firstName = teacher.getFirstName();
        String lastName = teacher.getLastName();
        if (teacher2.getFirstName() == firstName && teacher2.getLastName() == lastName) return false;

        try {
            PreparedStatement statement = conn.prepareStatement("update database_activity.Teacher set firstname = ? , lastname = ? where id = ?");
            statement.setString(1, firstName);
            statement.setString(2, lastName);
            statement.setString(3, String.valueOf(id));
            statement.execute();
            if (statement.getUpdateCount() >0)conn.commit();
            statement.close();
        } catch (SQLException e) {}

        return true;
    }

    /**
     * Delete an existing Teacher in the repository with the values represented
     * by the ID.
     *
     * @param id
     *            the ID of teacher.
     * @return true if row was deleted.
     */
    public boolean deleteTeacher(int id) {

        Teacher teacher = findTeacher(id);

        if (teacher.getID() == 0) return false;

        try {
            PreparedStatement statement = conn.prepareStatement("delete from database_activity.Teacher where id= ? ");
            statement.setString(1, String.valueOf(id));
            statement.executeUpdate();
            if (statement.getUpdateCount() >0)conn.commit();
        } catch (SQLException e) {}
        return true;
    }

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