package link;
import java.sql.*;
import java.util.*;
import org.apache.jasper.tagplugins.jstl.core.Out;
public class MyConnection
{
final String URL="jdbc:mysql://localhost:3306/test?&useSSL=false&serverTimezone=UTC";
final String Name="root";
final String Password="woshinidie668";
private static String name;
private static String password;
private static String tpassword;
private static String cname;
private static int number;
private static String sname;
private static String ID;
private static String sex;
private static String course;
private static String subject;
private static String title;
private static String Tname;
private static String CID;
private static int cnumber;
private Connection con;
private PreparedStatement stmt;
private ResultSet rs;
private PreparedStatement stmt1;
private ResultSet rs1;
private static String spassword;
boolean a;
public static String getCID()
{
return CID;
}
public static void setCID(String cID)
{
CID = cID;
}
public static String getName()
{
return name;
}
public static void setName(String name)
{
MyConnection.name = name;
}
public static String getPassword()
{
return password;
}
public static void setPassword(String password)
{
MyConnection.password = password;
}
public static int getNumber()
{
return number;
}
public static void setNumber(int number)
{
MyConnection.number = number;
}
public static String getCname()
{
return cname;
}
public static void setCname(String name)
{
cname = name;
}
public static String getTpassword()
{
return tpassword;
}
public static void setTpassword(String tpassword)
{
MyConnection.tpassword = tpassword;
}
public static String getSname()
{
return sname;
}
public static void setSname(String sname)
{
MyConnection.sname = sname;
}
public static String getID()
{
return ID;
}
public static void setID(String iD)
{
ID = iD;
}
public static String getSex()
{
return sex;
}
public static void setSex(String sex)
{
MyConnection.sex = sex;
}
public static String getCourse()
{
return course;
}
public static void setCourse(String class1)
{
course = class1;
}
public static String getSubject()
{
return subject;
}
public static void setSubject(String subject)
{
MyConnection.subject = subject;
}
public static int getCnumber()
{
return cnumber;
}
public static void setCnumber(int cnumber)
{
MyConnection.cnumber = cnumber;
}
public static String getTname()
{
return Tname;
}
public static void setTname(String tname)
{
Tname = tname;
}
public static String getTitle()
{
return title;
}
public static void setTitle(String title)
{
MyConnection.title = title;
}
public String getSpassword()
{
return spassword;
}
public void setSpassword(String spassword)
{
this.spassword = spassword;
}
public boolean isA()
{
return a;
}
public void setA(boolean a)
{
this.a = a;
}
public Connection getConnection() throws Exception
{
if(con == null)
{
// 指出连接数据库所需要的驱动程序
Class.forName("com.mysql.cj.jdbc.Driver");
System.out.println("数据库连接成功！");
// 建立与数据库之间的连接
con = DriverManager.getConnection(URL,Name,Password);
System.out.println("数据库连接成功！");
}
return con;
}
public void TnameFind(Connection conn) throws SQLException
{
String sql1="select * from imteacher where 教师工号=?";
stmt=conn.prepareStatement(sql1);
stmt.setString(1,getID( ));
rs=stmt.executeQuery( );
while(rs.next())
{
setTname(rs.getString("教师姓名"));
}
}
public void SnameFind(Connection conn) throws SQLException
{
String sql1="select * from imstudent where 学号=?";
stmt=conn.prepareStatement(sql1);
stmt.setString(1,getID( ));
rs=stmt.executeQuery( );
while(rs.next())
{
setSname(rs.getString("姓名"));
}
}
public boolean FindName(Connection conn) throws SQLException
{
String sql1="select * from management where 姓名=?";
stmt=conn.prepareStatement(sql1);
stmt.setString(1,getName( ));
rs=stmt.executeQuery( );
while(rs.next( ))
{
setA(true);
if(rs.getString("密码").equals(getPassword()))
{
return true;
}
else
{
return false;
}
}
return false;
}
public void addStudent(Connection conn)
{
String sql="insert into imstudent (学号,姓名,性别,班级,专业) values (?,?,?,?,?)";
try {
stmt=conn.prepareStatement(sql);
stmt.setString(1,getID( ));
stmt.setString(2,getSname( ));
stmt.setString(3,getSex( ));
stmt.setString(4,getCourse( ));
stmt.setString(5,getSubject( ));
stmt.execute( );
}
catch (SQLException e)
{
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
public void teacherAdd(Connection conn) throws SQLException
{
String sql="insert into teacher (教师工号,密码) values (?,?)";
stmt=conn.prepareStatement(sql);
stmt.setString(1,getID( ));
stmt.setString(2,getTpassword( ));
stmt.execute();
}
public void studentAdd(Connection conn) throws SQLException
{
String sql="insert into student (学号,密码) values (?,?)";
stmt=conn.prepareStatement(sql);
stmt.setString(1,getID( ));
stmt.setString(2,getSpassword( ));
stmt.execute();
}
public void addTeacher(Connection conn)
{
String sql="insert into imteacher (教师工号,教师姓名,教师性别,教师所在学院,职称) values (?,?,?,?,?)";
try {
stmt=conn.prepareStatement(sql);
stmt.setString(1,getID( ));
stmt.setString(2,getTname( ));
stmt.setString(3,getSex( ));
stmt.setString(4,getCourse( ));
stmt.setString(5,getSubject( ));
stmt.execute( );
}
catch (SQLException e)
{
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
public void courseAdd(Connection conn)
{
String sql="insert into course (课程编号,课程名称,人数上限,任课教师,选课人数) values (?,?,?,?)";
try {
stmt=conn.prepareStatement(sql);
stmt.setString(1,getID( ));
stmt.setString(2,getCname( ));
stmt.setInt(3,getNumber( ));
stmt.setString(4,getTname( ));
stmt.setInt(5, 0);
stmt.execute( );
}
catch (SQLException e)
{
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
public boolean showCourse(Connection conn) throws SQLException
{
setA(false);
String sql1="select * from course where 课程名称=?";
stmt=conn.prepareStatement(sql1);
stmt.setString(1, getName( ));
rs=stmt.executeQuery( );
if(rs.next( ))
{
if(rs.getInt("选课人数")>=rs.getInt("人数上限"))
{
return false;
}
else
{
setA(true);
setCID(rs.getString("课程编号"));
setName(rs.getString("课程名称"));
setNumber(rs.getInt("人数上限"));
setTname(rs.getString("任课教师"));
setCnumber(rs.getInt("选课人数"));
return true;
}
}
return true;
}
public void PasswordTset(PreparedStatement stmt) throws SQLException
{
stmt.setString(1,getTpassword( ));
stmt.setString(2,getID( ));
stmt.executeUpdate( );
}
public void PasswordSset(PreparedStatement stmt) throws SQLException
{
stmt.setString(1,getSpassword( ));
stmt.setString(2,getID( ));
stmt.executeUpdate( );
}
public boolean FindSname(Connection conn) throws SQLException
{
String sql1="select * from student where 学号=?";
String sql="Update student set 密码=? where 学号=?";
stmt=conn.prepareStatement(sql1);
stmt.setString(1,getID( ));
rs=stmt.executeQuery( );
while(rs.next( ))
{
if(rs.getString("密码").equals(""))
{
stmt=conn.prepareStatement(sql);
PasswordSset(stmt);
break;
}
}
rs.close( );
stmt.close( );
stmt1=conn.prepareStatement(sql1);
stmt1.setString(1,getID( ));
rs1=stmt1.executeQuery( );
while(rs1.next( ))
{
setA(true);
if(rs1.getString("密码").equals(getSpassword()))
{
return true;
}
else
{
return false;
}
}
return false;
}
public boolean FindTname(Connection conn) throws SQLException
{
String sql1="select * from teacher where 教师工号=?";
String sql="Update teacher set 密码=? where 教师工号=?";
stmt=conn.prepareStatement(sql1);
stmt.setString(1,getID( ));
rs=stmt.executeQuery( );
while(rs.next( ))
{
if(rs.getString("密码").equals(""))
{
stmt=conn.prepareStatement(sql);
PasswordTset(stmt);
break;
}
}
rs.close( );
stmt.close( );
stmt1=conn.prepareStatement(sql1);
stmt1.setString(1,getID( ));
rs1=stmt1.executeQuery( );
while(rs1.next( ))
{
setA(true);
if(rs1.getString("密码").equals(getTpassword()))
{
return true;
}
else
{
return false;
}
}
return false;
}
public void SearchTname(Connection conn) throws SQLException
{
String sql1="select * from imteacher where 教师姓名=?";
stmt=conn.prepareStatement(sql1);
stmt.setString(1,getTname( ));
rs=stmt.executeQuery( );
while(rs.next( ))
{
setID(rs.getString("教师工号"));
setTname(rs.getString("教师姓名"));
setSex(rs.getString("教师性别"));
setCourse(rs.getString("教师所在学院"));
setSubject(rs.getString("职称"));
}
}
public void SearchSname(Connection conn) throws SQLException
{
String sql1="select * from imstudent where 姓名=?";
stmt=conn.prepareStatement(sql1);
stmt.setString(1,getSname( ));
rs=stmt.executeQuery( );
while(rs.next( ))
{
setID(rs.getString("学号"));
setTname(rs.getString("姓名"));
setSex(rs.getString("性别"));
setCourse(rs.getString("班级"));
setSubject(rs.getString("专业"));
}
}
public void UpdateTeacher(Connection conn) throws SQLException
{
String sql1="Update imteacher set 教师所在学院=?,职称=? where 教师工号=?";
stmt=conn.prepareStatement(sql1);
stmt.setString(1, getCourse( ));
stmt.setString(2, getSubject( ));
stmt.setString(3, getID( ));
stmt.execute( );
}
public void UpdateStudent(Connection conn) throws SQLException
{
String sql1="Update imstudent set 班级=?,专业=? where 学号=?";
stmt=conn.prepareStatement(sql1);
stmt.setString(1, getCourse( ));
stmt.setString(2, getSubject( ));
stmt.setString(3, getID( ));
stmt.execute( );
}
public void UpdateCourse(Connection conn) throws SQLException
{
int a;
String sql="Update course set 选课人数=? where 课程名称=?";
String sql1="select * from course where 课程名称=?";
stmt=conn.prepareStatement(sql1);
stmt.setString(1, getName( ));
rs=stmt.executeQuery( );
while(rs.next( ))
{
a=rs.getInt("选课人数");
a++;
setNumber(a);
stmt1=conn.prepareStatement(sql);
stmt1.setInt(1,a);
stmt1.setString(2, getName( ));
stmt1.execute( );
}
}
public void AddImcourse(Connection conn) throws SQLException
{
String sql="select * from imstudent where 学号=?";
String sql1="insert into imcourse (选课学生,学号,性别,班级,专业,课程) values(?,?,?,?,?,?)";
stmt=conn.prepareStatement(sql);
stmt.setString(1, getID( ));
rs=stmt.executeQuery( );
while(rs.next( ))
{
setSname(rs.getString("姓名"));
setSex(rs.getString("性别"));
setCourse(rs.getString("班级"));
setSubject(rs.getString("专业"));
stmt1=conn.prepareStatement(sql1);
stmt1.setString(1, getSname( ));
stmt1.setString(2, getID( ));
stmt1.setString(3, getSex( ));
stmt1.setString(4, getCourse( ));
stmt1.setString(5, getSubject( ));
stmt1.setString(6, getName( ));
stmt1.execute( );
}
}
public void ShowTeacher(Connection conn) throws SQLException
{
String sql="select * from imteacher where 教师姓名=?";
stmt=conn.prepareStatement(sql);
stmt.setString(1, getTname( ));
rs=stmt.executeQuery( );
while(rs.next( ))
{
setID(rs.getString("教师工号"));
setSex(rs.getString("教师性别"));
setCourse(rs.getString("教师所在学院"));
setSubject(rs.getString("职称"));
}
}
public boolean Judgement(Connection conn) throws SQLException
{
int i=0;
String sql="select * from imcourse where 学号=?";
stmt=conn.prepareStatement(sql);
stmt.setString(1, getID( ));
rs=stmt.executeQuery( );
while(rs.next( ))
{
if(rs.getString("课程").equals(getName( )))
{
i++;
}
}
if(i<1)return true;
else return false;
}
public void close()
{
try
{
rs.close();
}
catch(Exception e){}
try
{
stmt.close();
}catch(Exception e){}
try
{
con.close();
}catch(Exception e){}
}
}