2020 12 16

员工培训系统：

package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import bean.job;
import bean.section;
import bean.student;
import bean.teacher;

public class dao {
    public Connection getConnection()
    {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        String url = "jdbc:mysql://localhost:3306/sys?serverTimezone=GMT%2B8";
        String username = "root";
        String password="123456";
        Connection a=null;
        try {
            a = DriverManager.getConnection(url,username,password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return a;
    }
    public boolean checkM(String name,String password) throws SQLException
    {
        Connection coon=getConnection();
        String sql="select* from manager";
        Statement stmt=coon.createStatement();
        ResultSet rs=stmt.executeQuery(sql);
        int TEMP=0;
        while(rs.next())
        {
            if(name.equals(rs.getString("name"))&&password.equals(rs.getString("password")))
                {rs.close();stmt.close();coon.close();return true;}
        }
        rs.close();stmt.close();coon.close();
        return false;
    }
    public boolean checkS(String name,String password) throws SQLException
    {
        Connection coon=getConnection();
        String sql="select* from studentpass";
        Statement stmt=coon.createStatement();
        ResultSet rs=stmt.executeQuery(sql);
        int TEMP=0;
        while(rs.next())
        {
            if(name.equals(rs.getString("SID"))&&password.equals(rs.getString("password")))
                {rs.close();stmt.close();coon.close();return true;}
        }
        rs.close();stmt.close();coon.close();
        return false;
    }
    public boolean checkT(String name,String password) throws SQLException
    {
        Connection coon=getConnection();
        String sql="select* from teacherpass";
        Statement stmt=coon.createStatement();
        ResultSet rs=stmt.executeQuery(sql);
        int TEMP=0;
        while(rs.next())
        {
            if(name.equals(rs.getString("TID"))&&password.equals(rs.getString("password")))
                {TEMP=1;}
        }
        rs.close();stmt.close();coon.close();
        if(TEMP==1) return true;
        else return false;
    }
    public boolean add(String user,String name,String ID,String sex,String job,String section,String password) throws SQLException
    {
        Connection coon=getConnection();
        int row,temp=0;
        String sql,sql1;
        if(user.equals("员工"))
        {
        sql1="insert into studentpass(SID,password)values(?,?)";
        sql="insert into student(SID,Sname,Ssex,Sjob,Ssection)values(?,?,?,?,?)";
        }
        else
        {
            sql1="insert into teacherpass(TID,password)values(?,?)";
            sql="insert into teacher(TID,Tname,Tsex,Tjob,Tsection)values(?,?,?,?,?)";
        }
        PreparedStatement b,c;
        try {
            b = coon.prepareStatement(sql);
            b.setString(1,ID);
            b.setString(2,name);
            b.setString(3,sex);
            b.setString(4,job);
            b.setString(5,section);
            row=b.executeUpdate();
            if(row<0) temp=1;
            b.close();
        } catch (SQLException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
        try {
            c = coon.prepareStatement(sql1);
            c.setString(1,ID);
            c.setString(2,password);
            row=c.executeUpdate();
            if(row<0) temp=1;
            c.close();
        } catch (SQLException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
        coon.close();
        if(temp==0) return true;
        else return false;
    }
    public boolean addclass(String ID,String name,String teacher) throws SQLException
    {
        Connection coon=getConnection();
        int row,temp=0;
        String sql;
        sql="insert into class(ID,name,teacher)values(?,?,?)";
        PreparedStatement b;
        try {
            b = coon.prepareStatement(sql);
            b.setString(1,ID);
            b.setString(2,name);
            b.setString(3,teacher);
            row=b.executeUpdate();
            if(row<0) temp=1;
            b.close();
        } catch (SQLException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
        coon.close();
        if(temp==0) return true;
        else return false;
    }
    public List<student> Mfindstudent(String A,String B) throws SQLException
    {
        List<student> list=new ArrayList<student>();
        Connection coon=getConnection();
        String sql="select * from student";
        Statement stmt=coon.createStatement();
        ResultSet rs=stmt.executeQuery(sql);
        A="S"+A;
        System.out.println(A);
        System.out.println(B);
        while(rs.next())
        {
            System.out.println(rs.getString("Sname"));
            if(B.equals(rs.getString(A)))
            {
            student a=new student();
            a.setSID(rs.getString("SID"));
            a.setSname(rs.getString("Sname"));
            a.setSsex(rs.getString("Ssex"));
            a.setSjob(rs.getString("Sjob"));
            a.setSsection(rs.getString("Ssecion"));
            list.add(a);
            }
        }
        rs.close();
        stmt.close();
        coon.close();
        return list;
    }
    public student Mgetstudent(String ID) throws SQLException
    {
        student a=new student();
        Connection coon=getConnection();
        String sql="select * from student";
        Statement stmt=coon.createStatement();
        ResultSet rs=stmt.executeQuery(sql);
        while(rs.next())
        {
            if(ID.equals(rs.getString("SID")))
            {
            
            a.setSID(rs.getString("SID"));
            a.setSname(rs.getString("Sname"));
            a.setSsex(rs.getString("Ssex"));
            a.setSjob(rs.getString("Sjob"));
            a.setSsection(rs.getString("Ssection"));
            }
        }
        rs.close();
        stmt.close();
        coon.close();
        return a;
    }
    public List<teacher> Mfindteacher(String A,String B) throws SQLException
    {
        List<teacher> list=new ArrayList<teacher>();
        Connection coon=getConnection();
        String sql="select * from teacher";
        Statement stmt=coon.createStatement();
        ResultSet rs=stmt.executeQuery(sql);
        A="T"+A;
        while(rs.next())
        {
            if(B.equals(rs.getString(A)))
            {
            teacher a=new teacher();
            a.setTID(rs.getString("TID"));
            a.setTname(rs.getString("Tname"));
            a.setTsex(rs.getString("Tsex"));
            a.setTjob(rs.getString("Tjob"));
            a.setTsection(rs.getString("Tsection"));
            list.add(a);
            }
        }
        rs.close();
        stmt.close();
        coon.close();
        return list;
    }
    public teacher Mgetteacher(String ID) throws SQLException
    {
        teacher a=new teacher();
        Connection coon=getConnection();
        String sql="select * from teacher";
        Statement stmt=coon.createStatement();
        ResultSet rs=stmt.executeQuery(sql);
        while(rs.next())
        {
            if(ID.equals(rs.getString("TID")))
            {
            a.setTID(rs.getString("TID"));
            a.setTname(rs.getString("Tname"));
            a.setTsex(rs.getString("Tsex"));
            a.setTjob(rs.getString("Tjob"));
            a.setTsection(rs.getString("Tsection"));
            }
        }
        rs.close();
        stmt.close();
        coon.close();
        return a;
    }
    public List<teacher> Mfindteacher1() throws SQLException
    {
        List<teacher> list=new ArrayList<teacher>();
        Connection coon=getConnection();
        String sql="select * from teacher";
        Statement stmt=coon.createStatement();
        ResultSet rs=stmt.executeQuery(sql);
        while(rs.next())
        {
            teacher a=new teacher();
            a.setTID(rs.getString("TID"));
            a.setTname(rs.getString("Tname"));
            a.setTsex(rs.getString("Tsex"));
            a.setTjob(rs.getString("Tjob"));
            a.setTsection(rs.getString("Tsection"));
            list.add(a);
        }
        rs.close();
        stmt.close();
        coon.close();
        return list;
    }
    public boolean set(String user,String ID,String name) throws SQLException
    {
        Connection coon=getConnection();
        int row,temp=0;
        String sql;
        sql="insert into "+user+"(ID,name)values(?,?)";
        PreparedStatement b;
        try {
            b = coon.prepareStatement(sql);
            b.setString(1,ID);
            b.setString(2,name);
            row=b.executeUpdate();
            if(row<0) temp=1;
            b.close();
        } catch (SQLException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
        coon.close();
        if(temp==0) return true;
        else return false;
    }
    public List<section> Msection() throws SQLException
    {
        List<section> list=new ArrayList<section>();
        Connection coon=getConnection();
        String sql="select * from section";
        Statement stmt=coon.createStatement();
        ResultSet rs=stmt.executeQuery(sql);
        while(rs.next())
        {
            section a=new section();
            a.setSID2(rs.getString("ID"));
            a.setSname2(rs.getString("name"));
            list.add(a);
        }
        rs.close();
        stmt.close();
        coon.close();
        return list;
    }
    public List<job> Mjob() throws SQLException
    {
        List<job> list=new ArrayList<job>();
        Connection coon=getConnection();
        String sql="select * from job";
        Statement stmt=coon.createStatement();
        ResultSet rs=stmt.executeQuery(sql);
        while(rs.next())
        {
            job a=new job();
            a.setJID(rs.getString("ID"));
            a.setJname(rs.getString("name"));
            list.add(a);
        }
        rs.close();
        stmt.close();
        coon.close();
        return list;
    }
    public boolean delete(String ID,String user) throws SQLException
    {
        Connection coon=getConnection();
        String sql="delete from "+user+" where ID=?";
        PreparedStatement a1=coon.prepareStatement(sql);
        a1.setString(1,ID);
        a1.executeUpdate();
        coon.close();
        return true;
    }
    public boolean update(String user,String name,String ID) throws SQLException
    {
        Connection coon=getConnection();
        String sql1="update "+user+" set name=? where ID=?";
        PreparedStatement a1=coon.prepareStatement(sql1);
        a1.setString(1,name);
        a1.setString(2,ID);
        a1.executeUpdate();
        a1.close();
        coon.close();
        return true;
    }
    public boolean update2(String user,String ID,String name,String sex,String job,String section) throws SQLException
    {
        Connection coon=getConnection();String sql;
        if(user.equals("student")) sql="update student set Sname=?,Ssex=?,Sjob=?,Ssection=? where SID=?";
        else sql="update teacher set Tname=?,Tsex=?,Tjob=?,Tsection=? where TID=?";
        PreparedStatement a1=coon.prepareStatement(sql);
        a1.setString(1,name);
        a1.setString(2,sex);
        a1.setString(3,job);
        a1.setString(4,section);
        a1.setString(5,ID);
        a1.executeUpdate();
        a1.close();
        coon.close();
        return true;
    }
    public List<section> Mfindsection() throws SQLException
    {
        List<section> list=new ArrayList<section>();
        Connection coon=getConnection();
        String sql="select * from section";
        Statement stmt=coon.createStatement();
        ResultSet rs=stmt.executeQuery(sql);
        while(rs.next())
        {
            section a=new section();
            a.setSID2(rs.getString("ID"));
            a.setSname2(rs.getString("name"));
            list.add(a);
        }
        rs.close();
        stmt.close();
        coon.close();
        return list;
    }
    public List<job> Mfindjob() throws SQLException
    {
        List<job> list=new ArrayList<job>();
        Connection coon=getConnection();
        String sql="select * from job";
        Statement stmt=coon.createStatement();
        ResultSet rs=stmt.executeQuery(sql);
        while(rs.next())
        {
            job a=new job();
            a.setJID(rs.getString("ID"));
            a.setJname(rs.getString("name"));
            list.add(a);
        }
        rs.close();
        stmt.close();
        coon.close();
        return list;
    }

}

package dao;
import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.List;
import bean.job;import bean.section;import bean.student;import bean.teacher;
public class dao {public Connection getConnection(){try {Class.forName("com.mysql.cj.jdbc.Driver");} catch (ClassNotFoundException e) {e.printStackTrace();}String url = "jdbc:mysql://localhost:3306/sys?serverTimezone=GMT%2B8";String username = "root";String password="123456";Connection a=null;try {a = DriverManager.getConnection(url,username,password);} catch (SQLException e) {e.printStackTrace();}return a;}public boolean checkM(String name,String password) throws SQLException{Connection coon=getConnection();String sql="select* from manager";Statement stmt=coon.createStatement();ResultSet rs=stmt.executeQuery(sql);int TEMP=0;while(rs.next()){if(name.equals(rs.getString("name"))&&password.equals(rs.getString("password"))){rs.close();stmt.close();coon.close();return true;}}rs.close();stmt.close();coon.close();return false;}public boolean checkS(String name,String password) throws SQLException{Connection coon=getConnection();String sql="select* from studentpass";Statement stmt=coon.createStatement();ResultSet rs=stmt.executeQuery(sql);int TEMP=0;while(rs.next()){if(name.equals(rs.getString("SID"))&&password.equals(rs.getString("password"))){rs.close();stmt.close();coon.close();return true;}}rs.close();stmt.close();coon.close();return false;}public boolean checkT(String name,String password) throws SQLException{Connection coon=getConnection();String sql="select* from teacherpass";Statement stmt=coon.createStatement();ResultSet rs=stmt.executeQuery(sql);int TEMP=0;while(rs.next()){if(name.equals(rs.getString("TID"))&&password.equals(rs.getString("password"))){TEMP=1;}}rs.close();stmt.close();coon.close();if(TEMP==1) return true;else return false;}public boolean add(String user,String name,String ID,String sex,String job,String section,String password) throws SQLException{Connection coon=getConnection();int row,temp=0;String sql,sql1;if(user.equals("员工")){sql1="insert into studentpass(SID,password)values(?,?)";sql="insert into student(SID,Sname,Ssex,Sjob,Ssection)values(?,?,?,?,?)";}else{sql1="insert into teacherpass(TID,password)values(?,?)";sql="insert into teacher(TID,Tname,Tsex,Tjob,Tsection)values(?,?,?,?,?)";}PreparedStatement b,c;try {b = coon.prepareStatement(sql);b.setString(1,ID);b.setString(2,name);b.setString(3,sex);b.setString(4,job);b.setString(5,section);row=b.executeUpdate();if(row<0) temp=1;b.close();} catch (SQLException e) {// TODO 自动生成的 catch 块e.printStackTrace();}try {c = coon.prepareStatement(sql1);c.setString(1,ID);c.setString(2,password);row=c.executeUpdate();if(row<0) temp=1;c.close();} catch (SQLException e) {// TODO 自动生成的 catch 块e.printStackTrace();}coon.close();if(temp==0) return true;else return false;}public boolean addclass(String ID,String name,String teacher) throws SQLException{Connection coon=getConnection();int row,temp=0;String sql;sql="insert into class(ID,name,teacher)values(?,?,?)";PreparedStatement b;try {b = coon.prepareStatement(sql);b.setString(1,ID);b.setString(2,name);b.setString(3,teacher);row=b.executeUpdate();if(row<0) temp=1;b.close();} catch (SQLException e) {// TODO 自动生成的 catch 块e.printStackTrace();}coon.close();if(temp==0) return true;else return false;}public List<student> Mfindstudent(String A,String B) throws SQLException{List<student> list=new ArrayList<student>();Connection coon=getConnection();String sql="select * from student";Statement stmt=coon.createStatement();ResultSet rs=stmt.executeQuery(sql);A="S"+A;System.out.println(A);System.out.println(B);while(rs.next()){System.out.println(rs.getString("Sname"));if(B.equals(rs.getString(A))){student a=new student();a.setSID(rs.getString("SID"));a.setSname(rs.getString("Sname"));a.setSsex(rs.getString("Ssex"));a.setSjob(rs.getString("Sjob"));a.setSsection(rs.getString("Ssecion"));list.add(a);}}rs.close();stmt.close();coon.close();return list;}public student Mgetstudent(String ID) throws SQLException{student a=new student();Connection coon=getConnection();String sql="select * from student";Statement stmt=coon.createStatement();ResultSet rs=stmt.executeQuery(sql);while(rs.next()){if(ID.equals(rs.getString("SID"))){a.setSID(rs.getString("SID"));a.setSname(rs.getString("Sname"));a.setSsex(rs.getString("Ssex"));a.setSjob(rs.getString("Sjob"));a.setSsection(rs.getString("Ssection"));}}rs.close();stmt.close();coon.close();return a;}public List<teacher> Mfindteacher(String A,String B) throws SQLException{List<teacher> list=new ArrayList<teacher>();Connection coon=getConnection();String sql="select * from teacher";Statement stmt=coon.createStatement();ResultSet rs=stmt.executeQuery(sql);A="T"+A;while(rs.next()){if(B.equals(rs.getString(A))){teacher a=new teacher();a.setTID(rs.getString("TID"));a.setTname(rs.getString("Tname"));a.setTsex(rs.getString("Tsex"));a.setTjob(rs.getString("Tjob"));a.setTsection(rs.getString("Tsection"));list.add(a);}}rs.close();stmt.close();coon.close();return list;}public teacher Mgetteacher(String ID) throws SQLException{teacher a=new teacher();Connection coon=getConnection();String sql="select * from teacher";Statement stmt=coon.createStatement();ResultSet rs=stmt.executeQuery(sql);while(rs.next()){if(ID.equals(rs.getString("TID"))){a.setTID(rs.getString("TID"));a.setTname(rs.getString("Tname"));a.setTsex(rs.getString("Tsex"));a.setTjob(rs.getString("Tjob"));a.setTsection(rs.getString("Tsection"));}}rs.close();stmt.close();coon.close();return a;}public List<teacher> Mfindteacher1() throws SQLException{List<teacher> list=new ArrayList<teacher>();Connection coon=getConnection();String sql="select * from teacher";Statement stmt=coon.createStatement();ResultSet rs=stmt.executeQuery(sql);while(rs.next()){teacher a=new teacher();a.setTID(rs.getString("TID"));a.setTname(rs.getString("Tname"));a.setTsex(rs.getString("Tsex"));a.setTjob(rs.getString("Tjob"));a.setTsection(rs.getString("Tsection"));list.add(a);}rs.close();stmt.close();coon.close();return list;}public boolean set(String user,String ID,String name) throws SQLException{Connection coon=getConnection();int row,temp=0;String sql;sql="insert into "+user+"(ID,name)values(?,?)";PreparedStatement b;try {b = coon.prepareStatement(sql);b.setString(1,ID);b.setString(2,name);row=b.executeUpdate();if(row<0) temp=1;b.close();} catch (SQLException e) {// TODO 自动生成的 catch 块e.printStackTrace();}coon.close();if(temp==0) return true;else return false;}public List<section> Msection() throws SQLException{List<section> list=new ArrayList<section>();Connection coon=getConnection();String sql="select * from section";Statement stmt=coon.createStatement();ResultSet rs=stmt.executeQuery(sql);while(rs.next()){section a=new section();a.setSID2(rs.getString("ID"));a.setSname2(rs.getString("name"));list.add(a);}rs.close();stmt.close();coon.close();return list;}public List<job> Mjob() throws SQLException{List<job> list=new ArrayList<job>();Connection coon=getConnection();String sql="select * from job";Statement stmt=coon.createStatement();ResultSet rs=stmt.executeQuery(sql);while(rs.next()){job a=new job();a.setJID(rs.getString("ID"));a.setJname(rs.getString("name"));list.add(a);}rs.close();stmt.close();coon.close();return list;}public boolean delete(String ID,String user) throws SQLException{Connection coon=getConnection();String sql="delete from "+user+" where ID=?";PreparedStatement a1=coon.prepareStatement(sql);a1.setString(1,ID);a1.executeUpdate();coon.close();return true;}public boolean update(String user,String name,String ID) throws SQLException{Connection coon=getConnection();String sql1="update "+user+" set name=? where ID=?";PreparedStatement a1=coon.prepareStatement(sql1);a1.setString(1,name);a1.setString(2,ID);a1.executeUpdate();a1.close();coon.close();return true;}public boolean update2(String user,String ID,String name,String sex,String job,String section) throws SQLException{Connection coon=getConnection();String sql;if(user.equals("student")) sql="update student set Sname=?,Ssex=?,Sjob=?,Ssection=? where SID=?";else sql="update teacher set Tname=?,Tsex=?,Tjob=?,Tsection=? where TID=?";PreparedStatement a1=coon.prepareStatement(sql);a1.setString(1,name);a1.setString(2,sex);a1.setString(3,job);a1.setString(4,section);a1.setString(5,ID);a1.executeUpdate();a1.close();coon.close();return true;}public List<section> Mfindsection() throws SQLException{List<section> list=new ArrayList<section>();Connection coon=getConnection();String sql="select * from section";Statement stmt=coon.createStatement();ResultSet rs=stmt.executeQuery(sql);while(rs.next()){section a=new section();a.setSID2(rs.getString("ID"));a.setSname2(rs.getString("name"));list.add(a);}rs.close();stmt.close();coon.close();return list;}public List<job> Mfindjob() throws SQLException{List<job> list=new ArrayList<job>();Connection coon=getConnection();String sql="select * from job";Statement stmt=coon.createStatement();ResultSet rs=stmt.executeQuery(sql);while(rs.next()){job a=new job();a.setJID(rs.getString("ID"));a.setJname(rs.getString("name"));list.add(a);}rs.close();stmt.close();coon.close();return list;}
}