db.java

MIS管理系统

package database;

import java.sql.*;//为了能使用数据库，我们加载了数据库包
import java.util.ArrayList;
import java.io.IOException;
import java.io.PrintWriter;



import bean.*;
import login.*;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

public class DB {
	private Connection conn=null;// 用来连接数据库的“数据库连接对象”
	private PreparedStatement stmt=null;// 数据库操作对象
	private ResultSet rs=null;// 结果集
	public DB() {
		try {

			conn = DBPool.getPool().getConnection();// 使用连接池创建连接
		} catch (Exception e) {

		}
		
	}
public boolean check(Users u)
{
	boolean checkuser=false;//登陆失败。。
try {
	stmt=conn.prepareStatement("select password from users where username=?");
	stmt.setString(1,u.getUsername());
	rs=stmt.executeQuery();
	if (rs.next())
	{
		
		String realpassword=rs.getString("password");
		if (realpassword.equals(u.getPassword()))
		checkuser=true;
		
	}
	
	
} catch (SQLException e) {
	e.printStackTrace();
}
	return checkuser;
}

public ArrayList searchUserInfo(String username)
{
	ArrayList a = new ArrayList();
	
	try {
		stmt=conn.prepareStatement("select * from userinfo where username=?");
		stmt.setString(1, username);
		rs=stmt.executeQuery();
		while(rs.next())
		{
			Userinfo uinfo=new Userinfo();
			uinfo.setUsername(rs.getString("username"));
			uinfo.setCom_name(rs.getString("com_name"));
			uinfo.setCom_addr(rs.getString("com_addr"));
			uinfo.setCom_tel(rs.getString("com_tel"));
			uinfo.setCom_code(rs.getString("com_code"));
			uinfo.setUser_name(rs.getString("user_name"));
			uinfo.setUser_tel(rs.getString("user_tel"));
			uinfo.setUser_duty(rs.getString("user_duty"));
			uinfo.setUser_dept(rs.getString("user_dept"));
			uinfo.setUser_pur(rs.getString("user_pur"));
			uinfo.setId(rs.getInt("id"));
			a.add(uinfo);
		}
	
	} catch (SQLException e) {
		
		e.printStackTrace();
	}	finally
	{
		try {
			stmt.close();
			conn.close();
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	return a;

}
public void addnewuser(Users u)
{
	try {
		stmt=conn.prepareStatement("insert into users(username,password) values(?,?)");
		stmt.setString(1, u.getUsername());
		stmt.setString(2, u.getPassword());
		stmt.execute();
	} catch (SQLException e) {
		e.printStackTrace();
	}
	
}
public void addnewusersinfo(Userinfo uinfo)
{
	try {
		stmt=conn.prepareStatement("insert into userinfo(username,com_name,com_addr,com_tel,com_code,user_name,user_tel,user_duty,user_dept) values(?,?,?,?,?,?,?,?,?)");
		stmt.setString(1, uinfo.getUsername());
		stmt.setString(2, uinfo.getCom_name());
		stmt.setString(3, uinfo.getCom_addr());
		stmt.setString(4, uinfo.getCom_tel());
		stmt.setString(5, uinfo.getCom_code());
		stmt.setString(6, uinfo.getUser_name());
		stmt.setString(7, uinfo.getUser_tel());
		stmt.setString(8, uinfo.getUser_duty());
		stmt.setString(9, uinfo.getUser_dept());
		stmt.execute();
	
	} catch (SQLException e) {
		e.printStackTrace();
	}
}
public int ckreg(String username)
{
	int a=0;
	try {
		stmt=conn.prepareStatement("select count(*) from users where username=?");
		stmt.setString(1, username);
		rs=stmt.executeQuery();
		rs.next();
		a=rs.getInt(1);
	} catch (SQLException e) {
		e.printStackTrace();
	}
	return a;
	
}
public void addNewProject(Newproject n)//发布新项目。
{
	try {
		stmt=conn.prepareStatement("insert into newproject(username,projectno,projectname,projectcost,pro_sta_date,linkname,link_tel,others) values(?,?,?,?,?,?,?,?)");
		//将从界面读取的信息加到数据库 采用insert into 语句 id字段 为自增 不需要手动添加
		
		stmt.setString(1, n.getUsername());
		stmt.setString(2, n.getProjectno());//设置第一个参数 对应上行语句中第一个问号 以下相同
		stmt.setString(3,n.getProjectname());
		stmt.setString(4, n.getProjectcost());
		stmt.setString(5, n.getPro_sta_date());
		stmt.setString(6, n.getLinkname());
		stmt.setString(7, n.getLink_tel());
		stmt.setString(8, n.getOthers());
		stmt.execute();
	} catch (SQLException e) {
		e.printStackTrace();
	}
	
	}
public void saveinfomodify(String uname,String name,String utel,String uduty,String udept,String cname,String ctel,String caddr,String ccode,String id){
	try {
		stmt=conn.prepareStatement("update userinfo set user_name=?,user_tel=?,user_duty=?,user_dept=?,com_name=?,com_addr=?,com_tel=?,com_code=? where id=?");
		stmt.setString(1, name);
		stmt.setString(2, utel);
		stmt.setString(3, uduty);
		stmt.setString(4, udept);
		stmt.setString(5, cname);
		stmt.setString(6, caddr);
		stmt.setString(7, ctel);
		stmt.setString(8, ccode);
		stmt.setInt(9, Integer.parseInt(id));
		stmt.execute();
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}
public ArrayList Searchproinfo(String username)
{
ArrayList a =new ArrayList();

try {
	stmt=conn.prepareStatement("select * from newproject where username=?");
    stmt.setString(1, username);
    rs=stmt.executeQuery();
    while(rs.next())
    {
    	Newproject npro = new Newproject();
    	npro.setProjectno(rs.getString("projectno"));
    	npro.setProjectname(rs.getString("projectname"));
    	npro.setPro_sta_date(rs.getString("pro_sta_date"));
    	npro.setLinkname(rs.getString("linkname"));
    	npro.setLink_tel(rs.getString("link_tel"));
    	npro.setProjectcost(rs.getString("projectcost"));
    	npro.setOthers(rs.getString("others"));
    	npro.setUsername(rs.getString("username"));
    	npro.setId(rs.getInt("id"));
    	a.add(npro);
    }
} catch (SQLException e) {
	
	e.printStackTrace();
}
return a ;
}
public boolean checkEmp(Emp emp)
{
	boolean checkemp=false;//登陆失败。。
try {
	stmt=conn.prepareStatement("select psw from emp where empno=?");
	stmt.setString(1,emp.getEmpno());
	rs=stmt.executeQuery();
	if (rs.next())
	{
		
		String realpassword=rs.getString("psw");
		if (realpassword.equals(emp.getPsw()))
		checkemp=true;
		
	}
	
	
} catch (SQLException e) {
	e.printStackTrace();
}
	return checkemp;
}
public ArrayList SearchEmpinfo(String empno)
{
ArrayList a =new ArrayList();

try {
	stmt=conn.prepareStatement("select * from emp where empno=?");
    stmt.setString(1, empno);
    rs=stmt.executeQuery();
    while(rs.next())
    {
    	Emp empinfo = new Emp();
    	empinfo.setId(rs.getInt("id"));
    	empinfo.setEmpno(rs.getString("empno"));
    	empinfo.setName(rs.getString("name"));
    	empinfo.setAge(rs.getString("age"));
    	empinfo.setSex(rs.getString("sex"));
    	empinfo.setJob(rs.getString("job"));
    	empinfo.setEng_date(rs.getString("eng_date"));
    	empinfo.setDeptno(rs.getString("deptno"));
    	empinfo.setTel1(rs.getString("tel1"));
    	empinfo.setTel2(rs.getString("tel2"));
    	empinfo.setSalary(rs.getString("salary"));
    	empinfo.setPurview(rs.getString("purview"));
    	empinfo.setPsw(rs.getString("psw"));
    	a.add(empinfo);
    }
} catch (SQLException e) {
	
	e.printStackTrace();
}
return a ;
}
public void modifyPsw(String empno,String newpsw)
{
	try {
		stmt=conn.prepareStatement("update emp set psw=? where empno=?");
		stmt.setString(1, newpsw);
		stmt.setString(2, empno);
		stmt.execute();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}
public void addAffiche(Affiche aff)
{
	try {
		stmt=conn.prepareStatement("insert into affiche(aff_name,aff_body,aff_time) values(?,?,?)");
		stmt.setString(1, aff.getAff_name());
		stmt.setString(2, aff.getAff_body());
		stmt.setString(3, aff.getAff_time());
		stmt.execute();
	
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}
}