员工签到系统mysql_签到系统.MySQL/JDBC

//完成连接数据库操作，生成并返回Statement

publicStatement getStatement() {try{

Class.forName(DRIVER);

con=DriverManager.getConnection(URL, USERNAME, PASSWORD);

stmt=con.createStatement();returnstmt;

}catch(Exception e) {

e.printStackTrace();return null;

}

}//检查用户名&密码

public booleancheckUser(String username, String password) {try{

String loginQuery= "SELECT * FROM users WHERE username = ‘"

+ username + "‘ AND password = ‘" + password + "‘";

stmt=getStatement();

rs=stmt.executeQuery(loginQuery);if(rs.next()) {return true;

}else{return false;

}

}catch(Exception ex) {

ex.printStackTrace();return false;

}

}//添加签到记录

public voidcheckin(String username) {try{//调用MySQL的CURDATE()函数，自动获取当前日期

String addCheckin = "INSERT INTO checkin VALUES (‘" +username+ "‘, CURDATE())";

stmt=getStatement();

stmt.executeUpdate(addCheckin);

}catch(Exception ex) {

ex.printStackTrace();

}

}//查询签到年(本年)

publicResultSet queryCURYear(String username) {try{

String queryCURMonth= "SELECT YEAR(CURDATE()) AS currentYear";

stmt=getStatement();

rs=stmt.executeQuery(queryCURMonth);returnrs;

}catch(Exception ex) {

ex.printStackTrace();return null;

}

}//查询签到月(当前月)

publicResultSet queryCURMonth(String username) {try{

String queryCURMonth= "SELECT MONTH(CURDATE()) AS currentMonth";

stmt=getStatement();

rs=stmt.executeQuery(queryCURMonth);returnrs;

}catch(Exception ex) {

ex.printStackTrace();return null;

}

}//查询签到月(当前月)的第一天属于星期几

publicResultSet queryStartDayOfWeekInCURMonth() {try{//语句含义：星期几(日期减法(CURDATE() - 日期(CURDATE()) - 1天))

String queryStartDayOfWeekInCURMonth = "SELECT DAYOFWEEK(DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) - 1 DAY)) "

+ "AS startDayOfWeekInCURMonth";

stmt=getStatement();

rs=stmt.executeQuery(queryStartDayOfWeekInCURMonth);returnrs;

}catch(Exception ex) {

ex.printStackTrace();return null;

}

}//查询签到月(当前月)的总天数

publicResultSet queryTotalNumberOfDaysInCURMonth() {try{

String queryTotalNumberOfDaysInCURMonth= "SELECT DAY(LAST_DAY(CURDATE())) "

+ "AS totalNumberOfDaysInCURMonth";

stmt=getStatement();

rs=stmt.executeQuery(queryTotalNumberOfDaysInCURMonth);returnrs;

}catch(Exception ex) {

ex.printStackTrace();return null;

}

}//获取用户在当前月的签到天数

publicResultSet queryCheckinDaysInCURMonth(String username) {try{//自行判断当前月份，并获取本月的签到记录(日期)

String queryCheckinDaysInCURMonth = "SELECT DISTINCT DATE(checkindays) "

+ "AS checkinDaysInCURMonth "

+ "FROM checkin "

+ "WHERE YEAR(checkindays) = YEAR(CURDATE()) "

+ "AND MONTH(checkindays) = MONTH(CURDATE()) "

+ "AND username = ‘" + username + "‘ "

+ "ORDER BY checkindays;";

stmt=getStatement();

rs=stmt.executeQuery(queryCheckinDaysInCURMonth);returnrs;

}catch(Exception ex) {

ex.printStackTrace();return null;

}

}