db.java

servlet做的教职工管理系统

package db;


import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import beans.Cjxx;
import beans.Khxx;
import beans.Users;
import   java.util.Date;   
import   java.util.Calendar;   
  
import   java.text.SimpleDateFormat;  


public class DB {
	private Connection conn;

	private PreparedStatement stmt;
	private PreparedStatement stmt1;
	private PreparedStatement stmt2;

	private ResultSet rs;
	public DB() {
		try {

			Class.forName("org.gjt.mm.mysql.Driver");

			conn = DriverManager
					.getConnection("jdbc:mysql://localhost:3306/jzg?user=root&password=root&useUnicode=true&characterEncoding=gbk");

		} catch (Exception sqle) {

		}
	}
	
	public ArrayList<Users> userDetail(String user)
	{
		ArrayList<Users> a=new ArrayList<Users>();
		try {
			stmt=conn.prepareStatement("select * from user where user=?");
		   stmt.setString(1, user);
		   rs=stmt.executeQuery();
		   while(rs.next())
		   {
			   Users u=new Users();
			   u.setId(rs.getInt(1));
			   u.setGh(rs.getString(2));
			   u.setName(rs.getString(3));
			   u.setRzsj(rs.getString(4));
			   u.setSex(rs.getString(5));
			   u.setCollege(rs.getString(6));
			   u.setPassword(rs.getString(7));
			   u.setUser(rs.getString(8));
			   a.add(u);
		   }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return a;	
	}
	public int changeInfo(Users u)
	{
		int a=0;
		try {
			stmt=conn.prepareStatement("update user set gh=?,name=?,rzsj=?,sex=?,college=? where id=?");
		    stmt.setString(1, u.getGh());
		    stmt.setString(2, u.getName());
		    stmt.setString(3, u.getRzsj());
		    stmt.setString(4, u.getSex());
		    stmt.setString(5, u.getCollege());
		    stmt.setInt(6, u.getId());
		    stmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return a;
	}
	
	public void delete(String user){
		try {
			stmt = conn.prepareStatement("delete from user where user = ?") ;
			stmt.setString(1, user) ;
			stmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
		public void deletekh(String user){
			try {
				stmt = conn.prepareStatement("delete from khxx where user = ?") ;
				stmt.setString(1, user) ;
				stmt.execute();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

}

	public int checkUser(String user,String gh)
	{
		int a=0;
		try {
			stmt=conn.prepareStatement("select user,gh from user where user=? or gh=?");
			stmt.setString(1, user);
			stmt.setString(2, gh) ;
			rs=stmt.executeQuery();
			while(rs.next())
			{
				if(rs.getString("user").equals(user) || rs.getString("gh").equals(gh))
				a=1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return a;
	}
	
	public void addUser(int id, String user, String password, String gh, String college, String rzsj, String name,String sex)
	{
		try {
			stmt = conn.prepareStatement("insert into user(id,gh,name,rzsj,sex,college,password,user) values(?,?,?,?,?,?,?,?)");
			stmt.setInt(1, id) ;
			stmt.setString(2, gh) ;
			stmt.setString(3, name) ;
			stmt.setString(4, rzsj) ;
			stmt.setString(5, sex) ;
			stmt.setString(6, college) ;
			stmt.setString(7, password) ;
			stmt.setString(8, user) ;
			stmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
	}
	
	public void addAdmin(int id, String user, String password)
	{
		try {
			stmt = conn.prepareStatement("insert into admin(id,user,password) values(?,?,?)");
			stmt.setInt(1, id) ;
			stmt.setString(2, user) ;
			stmt.setString(3, password) ;
			stmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
	}
	
	public void InsertKhxx(int id, String user, int cq, int sd, int nl, int jx, int lj)
	{
		try {
			stmt = conn.prepareStatement("insert into khxx(id,user,cq,sd,nl,jx,lj,year,total) values(?,?,?,?,?,?,?,?,?)");
			stmt.setInt(1, id) ;
			stmt.setString(2, user) ;
			stmt.setInt(3, cq) ;
			stmt.setInt(4, sd) ;
			stmt.setInt(5, nl) ;
			stmt.setInt(6, jx) ;
			stmt.setInt(7, lj) ;
			
			Date   now   =   new   Date();   
			  SimpleDateFormat   dateFormat   =   new   SimpleDateFormat("yyyy/MM/dd   HH:mm:ss");//可以方便地修改日期格式   
			  String   hehe   =   dateFormat.format(   now   );   
			  System.out.println(hehe);   
			    
			  Calendar   c   =   Calendar.getInstance();//可以对每个时间域单独修改   
			  int   year   =   c.get(Calendar.YEAR);   
//			  int   month   =   c.get(Calendar.MONTH);   
//			  int   date   =   c.get(Calendar.DATE);   
//			  int   hour   =   c.get(Calendar.HOUR_OF_DAY);   
//			  int   minute   =   c.get(Calendar.MINUTE);   
//			  int   second   =   c.get(Calendar.SECOND);   
			
			stmt.setInt(8, year) ;
			stmt.setInt(9, cq+sd+nl+jx+lj) ;
			stmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
	
	}
	public ArrayList searchUser(String gh , String name , String college ,String rzsj , String sex)
	{
		ArrayList a = new ArrayList();
        try {
			stmt=conn.prepareStatement("select * from user where gh like ? and name like ? and college like ? and rzsj like ? and sex like ? ");
			stmt.setString(1, gh) ;
			stmt.setString(2, name) ;
			stmt.setString(3, college) ;
			stmt.setString(4, rzsj) ;
			stmt.setString(5, sex) ;
			rs=stmt.executeQuery() ;
			while(rs.next())
			{
				Users u = new Users();
				u.setId(rs.getInt("id"));
				u.setUser(rs.getString("user")) ;
				u.setPassword(rs.getString("password")) ;
				u.setName(rs.getString("name")) ;
				u.setCollege(rs.getString("college")) ;
				u.setRzsj(rs.getString("rzsj")) ;
				u.setGh(rs.getString("gh")) ;
				u.setSex(rs.getString("sex")) ;
				a.add(u);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return a;
	}
	
	public ArrayList searchKhxx(String user)
	{
		ArrayList a = new ArrayList();
        try {
			stmt=conn.prepareStatement("select * from khxx where user=?");
			stmt.setString(1, user) ;
	
			rs=stmt.executeQuery() ;
			while(rs.next())
			{
				Khxx u = new Khxx();
				u.setId(rs.getInt("id"));
				u.setUser(rs.getString("user")) ;
				u.setCq(rs.getInt("cq")) ;
				u.setSd(rs.getInt("sd")) ;
				u.setNl(rs.getInt("nl")) ;
				u.setJx(rs.getInt("jx")) ;
				u.setLj(rs.getInt("lj")) ;
				u.setYear(rs.getString("year")) ;
				u.setTotal(rs.getInt("total")) ;
				a.add(u);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return a;
	}
	
	public ArrayList searchCjxx(String user)
	{
		ArrayList a = new ArrayList();
        try {
			stmt=conn.prepareStatement("select * from cjxx where user=?");
			stmt.setString(1, user) ;
			rs=stmt.executeQuery() ;
			while(rs.next())
			{
				Cjxx u = new Cjxx();
				u.setId(rs.getInt("id"));
				u.setUser(rs.getString("user")) ;
				u.setContent(rs.getString("content")) ;
				u.setTime(rs.getString("time")) ;
				a.add(u);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return a;
	}
		// TODO Auto-generated method stub
		public ArrayList<Cjxx> CjDetail(String user)
		{
			ArrayList<Cjxx> a=new ArrayList<Cjxx>();
			try {
				stmt=conn.prepareStatement("select * from cjxx where user=?");
			   stmt.setString(1, user);
			   rs=stmt.executeQuery();
			   while(rs.next())
			   {
				   Cjxx u=new Cjxx();
				   u.setId(rs.getInt(1));
				   u.setUser(rs.getString(2));
				   u.setContent(rs.getString(3));
				   u.setTime(rs.getString(4));
				   a.add(u);
			   }
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return a;	
	}
		public void addCjxx(int id, String user, String content, String time)
		{
			try {
				stmt = conn.prepareStatement("insert into cjxx(id,user,content,time) values(?,?,?,?)");
				stmt.setInt(1, id) ;
				stmt.setString(2, user) ;
				stmt.setString(3, content) ;
				stmt.setString(4, time) ;
				stmt.execute();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}		
		}
		
		public int login(String user,String password)
		{
			int a=0;
			try {
				stmt=conn.prepareStatement("select password from admin where user=?");
				stmt.setString(1, user);
				rs=stmt.executeQuery();
				while(rs.next())
				{
					if(rs.getString("password").equals(password))
					a=1;
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return a;
		}
		
		public int checkAdmin(String user)
		{
			int a=0;
			try {
				stmt=conn.prepareStatement("select user from admin where user=?");
				stmt.setString(1, user);
				rs=stmt.executeQuery();
				while(rs.next())
				{
					if(rs.getString("user").equals(user))
					a=1;
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return a;
		}
		
		
}