datadao.java

选课系统,基于SSH2的选课系统,欢迎下载

package xiong.gang.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.servlet.ServletException;

import xiong.gang.servlet.DataSourceServlet;
import xiong.gang.student.bean.ChengJiBean;

public class DataDAO {
	private Connection conn = null;

	private Statement st = null;

	private PreparedStatement pr = null;

	private ResultSet re = null;

	public DataDAO() {
		try {
			conn = new DataSourceServlet().getconn();
		} catch (ServletException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public String login(String uname, String upassword, String role) {
		String result = "";
		String sql = null;
		if (role.equals("student")) {
			sql = "select sno,spassword from student_info where sno=?";
		} else if (role.equals("teacher")) {
			sql = "select tno,tpassword from teacher_info where tno=?";
		} else {
			sql = "select mno,mpassword from manager_info where mno=?";
		}
		try {
			pr = conn.prepareStatement(sql);
			pr.setString(1, uname);
			re = pr.executeQuery();
			if (re.next()) {
				if (upassword.equals(re.getString(2))) {
					result = role;
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}

	public String getname(String no, String role) {
		String result = null;
		String sql = null;
		if (role.equals("student")) {
			sql = "select sname from student_info where sno=?";
		} else if (role.equals("teacher")) {
			sql = "select tname from teacher_info where tno=?";
		} else {
			sql = "select mname from manager_info where mno=?";
		}
		try {
			pr = conn.prepareStatement(sql);
			pr.setString(1, no);
			re = pr.executeQuery();
			if (re.next()) {
				result = re.getString(1);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}

	// course_info.ctime,course_info.caddress;course_info.cxianxuannianji;course_info.cxianxuanshu;course_info.cselectednum;

	public ResultSet studentmainxianshi() {
		try {
			st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			re = st
					.executeQuery("select course_info.cno,course_info.cname,course_info.cjieshao,teacher_info.tname,course_info.cxibu,teacher_info.tjieshao,course_info.ctime,course_info.caddress,course_info.cxianxuannianji,course_info.cxianxuanshu,course_info.cselectednum from course_info,teacher_info where course_info.ctno=teacher_info.tno order by course_info.cno");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return re;
	}
	
	public ResultSet teachermainxianshi(String tno) {
		try {
			st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			re = st.executeQuery("select elected_info.esno,elected_info.esname,elected_info.esbanji,elected_info.esxibu,elected_info.ecname,elected_info.echengji from elected_info where elected_info.etno="+tno+" order by elected_info.esno");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return re;
	}

	public ChengJiBean chengJi(String sno) {
		ChengJiBean chengji = new ChengJiBean();
		try {
			pr = conn
					.prepareStatement("select elected_info.esno,elected_info.esname,elected_info.ecname,"
							+ "elected_info.etname,elected_info.esxibu,elected_info.ectime,elected_info.ecaddress,"
							+ "elected_info.echengji,elected_info.ecno from elected_info where esno=?");
			pr.setString(1, sno);
			re = pr.executeQuery();
			if (re.next()) {
				chengji.setEsno(re.getString(1));
				chengji.setEsname(re.getString(2));
				chengji.setEcname(re.getString(3));
				chengji.setEtname(re.getString(4));
				chengji.setEsxibu(re.getString(5));
				chengji.setEctime(re.getString(6));
				chengji.setEcaddress(re.getString(7));
				chengji.setEchengji(re.getString(8));
				chengji.setEcno(re.getString(9));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return chengji;
	}

	public int updateMiMaStudent(String no, String oldpassword, String newpassword) {
		int flag = 0;
		try {
			pr = conn.prepareStatement("update student_info set spassword=? where sno=?&&spassword=?");
			pr.setString(1, newpassword);
			pr.setString(2, no);
			pr.setString(3, oldpassword);
			flag = pr.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
	public int updateMiMaTeacher(String no, String oldpassword, String newpassword) {
		int flag = 0;
		try {
			pr = conn.prepareStatement("update teacher_info set tpassword=? where tno=?&&tpassword=?");
			pr.setString(1, newpassword);
			pr.setString(2, no);
			pr.setString(3, oldpassword);
			flag = pr.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
	
	public int updateMiMaManager(String no, String oldpassword, String newpassword) {
		int flag = 0;
		try {
			pr = conn.prepareStatement("update manager_info set mpassword=? where mno=?&&mpassword=?");
			pr.setString(1, newpassword);
			pr.setString(2, no);
			pr.setString(3, oldpassword);
			flag = pr.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	// insert into table1(field1,field2) values(value1,value2)
	public int xuanke(String cno, String sno, String nianji) {
		int flag = 0;
		String esno = null;
		String esname = null;
		String esbanji = null;
		String esxibu = null;
		String ecno = null;
		String ecname = null;
		String etno = null;
		String etname = null;
		String ectime = null;
		String ecaddress = null;
		String cxianxuanshu1 = null;
		int xianxuanshu = 0;
		String name = null;
		String cxianxuannianji1 = null;
		String selectednum=null;
		try {
			pr = conn
					.prepareStatement("select elected_info.esname from elected_info where elected_info.esno=?");
			pr.setString(1, sno);
			re = pr.executeQuery();
			if (re.next()) {
				name = re.getString("elected_info.esname");
			}
			if (name == null) {
				pr = conn
						.prepareStatement("select student_info.sno,student_info.sname,student_info.sbanji,student_info.sxibu from student_info where student_info.sno=?");
				pr.setString(1, sno);
				re = pr.executeQuery();
				if (re.next()) {
					esno = re.getString("student_info.sno");
					esname = re.getString("student_info.sname");
					esbanji = re.getString("student_info.sbanji");
					esxibu = re.getString("student_info.sxibu");
				}
				// System.out.println(esno+esname+esbanji+esxibu);

				pr = conn
						.prepareStatement("select course_info.cxianxuanshu from course_info where course_info.cno=?");
				pr.setString(1, cno);
				re = pr.executeQuery();
				if (re.next()) {
					cxianxuanshu1 = re.getString("course_info.cxianxuanshu");
				}
				// System.out.println(selectednum);
				// System.out.println(cxianxuanshu1);
				pr = conn.prepareStatement("select course_info.cselectednum from course_info where course_info.cno=?");
		        pr.setString(1, cno);
		        re = pr.executeQuery();
		       if (re.next()) {
		    	   selectednum = re.getString("course_info.cselectednum");
		}

				if (Integer.valueOf(cxianxuanshu1) > Integer.valueOf(selectednum)) {
					pr = conn.prepareStatement("select student_info.snianji from student_info where student_info.sno=?");