dao.java

学生选课 用java和sql server 2000用asp完成的学生选课管理系统

package com.xuanke.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Dao {
	private Connection conn = null;

	// private String drivername="com.microsoft.jdbc.sqlserver.SQLServerDriver";
	// private String
	// url="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=xuanke";
	private String drivername = "org.gjt.mm.mysql.Driver";

	private String url = "jdbc:mysql://localhost:3306/xuanke";

	// private String username="sa";
	// private String pwd="sa";
	private String username = "root";

	private String pwd = "root";
	PreparedStatement stmt = null;

	public Dao() {

		try {
			Class.forName(drivername).newInstance();
			conn = DriverManager.getConnection(url, username, pwd);
		
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
//  ************************用户模块***************************************************************
	public boolean userlogin(String username, String password) {
		boolean res = false;// 默认是登录失败的
	
		ResultSet rs = null;
		try {
			stmt = conn
					.prepareStatement("select * from yonghu where username=? and password=?");
			stmt.setString(1, username);
			stmt.setString(2, password);
			rs = stmt.executeQuery();
			if (rs.next()) {
				// if(password.equals(rs.getString("password")))
				res = true;// 表示登录成功

			} else
				res = false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			onclose();

		}
		return res;
	}

	// 得到用户级别 0为普通用户 1为管理员用户
	// 得到用户级别 0为普通用户 1为管理员用户
	public int getstage(String username) {
		int a = 0;
		
		ResultSet rs = null;
		try {
			stmt = conn
					.prepareStatement("select stage from yonghu where username=?");
			stmt.setString(1, username);
			rs = stmt.executeQuery();
			if (rs.next())
				a = rs.getInt("stage");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			onclose();
		}

		return a;
	}

	public boolean addUser(String username, String password, String stage)// 这个方法返回真值表示注册成功，返回假值表示注册失败
	{
		
		boolean bo = false;// 表示注册失败
		ResultSet res = null;
		try {
			stmt = conn
					.prepareStatement("select * from yonghu where username=?");// 去找用户是否存在
			stmt.setString(1, username);// 设定参数username
			res = stmt.executeQuery();
			if (!(res.next()))// 说明用户没有找到
			{

				stmt = conn
						.prepareStatement("insert into yonghu(username,password,stage) values(?,?,?)");// 插入新用户信息
				stmt.setString(1, username);// 参数啊
				stmt.setString(2, password);// 还是参数
				stmt.setString(3, stage);
				stmt.execute();// 执行
				bo = true;// 表示注册成功
			}

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

	public int totalpage() {
		int totalpage = 0;
		
		ResultSet res = null;
		String sql = "select * from yonghu";
		try {
			stmt = conn.prepareStatement(sql);
			res = stmt.executeQuery();
			if (res.next()) {
				int x = res.getInt(1);
				if (x % 10 == 0) {
					totalpage = x % 10;
				} else
					totalpage = x % 10 + 1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			onclose();
		}
		return totalpage;
	}

	public ResultSet pagers(int topage)// 翻页程序
	{
		
		ResultSet res = null;
		try {
			stmt = conn
					.prepareStatement("select * from yonghu order by id desc limit ?,10");
			stmt.setInt(1, (topage - 1) * 10);
			res = stmt.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			onclose();
		}
		return res;
	}

	public void deleteUser(String username) {
		

		try {
			stmt = conn.prepareStatement("delete from yonghu where username=?");
			stmt.setString(1, username);
			stmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			onclose();
		}

	}
	//*****************学生模块***************************************************************************
	public boolean addStudent(String xuehao, String xingming, String sex,String age,String banji)// 这个方法返回真值表示注册成功，返回假值表示注册失败
	{
		
		boolean bo = false;// 表示注册失败
//		ResultSet res = null;
		try {
//			stmt = conn
//					.prepareStatement("select * from student where xuehao=?");// 去找用户是否存在
//			stmt.setString(1, xuehao);// 设定参数username
//			res = stmt.executeQuery();
//			if (!(res.next()))// 说明用户没有找到
//			{

				stmt = conn
						.prepareStatement("insert into student(xuehao,xingming,sex,age,banji) values(?,?,?,?,?)");// 插入新用户信息
				stmt.setString(1, xuehao);// 参数啊
				stmt.setString(2, xingming);// 还是参数
				stmt.setString(3, sex);
				stmt.setString(4, age);
				stmt.setString(5, banji);
				stmt.execute();// 执行
				bo = true;// 表示注册成功
//			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			onclose();
		}
		return bo;
	}
	
	public int stutotalpage() {
		int totalpage = 0;
		
		ResultSet res = null;
		String sql = "select * from student";
		try {
			stmt = conn.prepareStatement(sql);
			res = stmt.executeQuery();
			if (res.next()) {
				int x = res.getInt(1);
				if (x % 10 == 0) {
					totalpage = x % 10;
				} else
					totalpage = x % 10 + 1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			onclose();
		}
		return totalpage;
	}

	public ResultSet stupagers(int topage)// 翻页程序
	{
		
		ResultSet res = null;
		try {
			stmt = conn
					.prepareStatement("select * from student order by id desc limit ?,10");
			stmt.setInt(1, (topage - 1) * 10);
			res = stmt.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			onclose();
		}
		return res;
	}
	public void deleteStu(String xuehao) {
	

		try {
			stmt = conn.prepareStatement("delete from student where xuehao=?");
			stmt.setString(1, xuehao);
			stmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			onclose();
		}

	}
	//****************课程管理*************************************
	
	public boolean addCource(String courcename, String teacher, String jiaoshi,String time,float score)// 这个方法返回真值表示注册成功，返回假值表示注册失败
	{
		
		boolean bo = false;// 表示注册失败

		try {
			

				stmt = conn
						.prepareStatement("insert into cource(courcename,teacher,jiaoshi,time,score) values(?,?,?,?,?)");// 插入新用户信息
				stmt.setString(1, courcename);// 参数啊
				stmt.setString(2, teacher);// 还是参数
				stmt.setString(3, jiaoshi);
				stmt.setString(4, time);
				stmt.setFloat(5, score);
				stmt.execute();// 执行
				bo = true;// 表示注册成功
			

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			onclose();
		}
		return bo;
	}
	public int coutotalpage() {
		int totalpage = 0;
		
		ResultSet res = null;
		String sql = "select * from cource";
		try {
			stmt = conn.prepareStatement(sql);
			res = stmt.executeQuery();
			if (res.next()) {
				int x = res.getInt(1);
				if (x % 10 == 0) {
					totalpage = x % 10;
				} else
					totalpage = x % 10 + 1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			onclose();
		}
		return totalpage;
	}

	public ResultSet coupagers(int topage)// 翻页程序
	{
	
		ResultSet res = null;
		try {
			stmt = conn
					.prepareStatement("select * from cource order by id desc limit ?,10");
			stmt.setInt(1, (topage - 1) * 10);
			res = stmt.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			onclose();
		}
		return res;
	}
	public void deleteCou(String courcename) {
		

		try {
			stmt = conn.prepareStatement("delete from cource where courcename=?");
			stmt.setString(1, courcename);
			stmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			onclose();
		}

	}
	
	//**********************选课模块**************************
	public boolean addChoose(String courcename, String teacher, String jiaoshi,String time,float score,String xuehao)// 这个方法返回真值表示注册成功，返回假值表示注册失败
	{
		
		boolean bo = false;// 表示注册失败

		try {
			

				stmt = conn
						.prepareStatement("insert into classt(courcename,teacher,jiaoshi,time,score,xuehao) values(?,?,?,?,?,?)");// 插入新用户信息
				stmt.setString(1, courcename);// 参数啊
				stmt.setString(2, teacher);// 还是参数
				stmt.setString(3, jiaoshi);
				stmt.setString(4, time);
				stmt.setFloat(5, score);
				stmt.setString(6, xuehao);
				stmt.execute();// 执行
				bo = true;// 表示注册成功
			

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			onclose();
		}
		return bo;
	}
	public int clatotalpage() {
		int totalpage = 0;
		
		ResultSet res = null;
		String sql = "select * from cource";
		try {
			stmt = conn.prepareStatement(sql);
			res = stmt.executeQuery();
			if (res.next()) {
				int x = res.getInt(1);
				if (x % 10 == 0) {
					totalpage = x % 10;
				} else
					totalpage = x % 10 + 1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			onclose();
		}
		return totalpage;
	}

	public ResultSet clapagers(int topage)// 翻页程序
	{
		
		ResultSet res = null;
		try {
			stmt = conn
					.prepareStatement("select * from cource order by id desc limit ?,10");
			stmt.setInt(1, (topage - 1) * 10);
			res = stmt.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			onclose();
		}
		return res;
	}
	
	public ResultSet selCource(String cource){
		
		ResultSet res=null;
		try {
			stmt=conn.prepareStatement("select * from cource where courcename=?");
			stmt.setString(1, cource);
			res=stmt.executeQuery();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			onclose();
		}
		return res;
		
	}
	//*****************************查课模块****************************************************
	public int chatotalpage() {
		int totalpage = 0;
		
		ResultSet res = null;
		String sql = "select * from classt";
		try {
			stmt = conn.prepareStatement(sql);
			res = stmt.executeQuery();
			if (res.next()) {
				int x = res.getInt(1);
				if (x % 10 == 0) {
					totalpage = x % 10;
				} else
					totalpage = x % 10 + 1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			onclose();
		}
		return totalpage;
	}

	public ResultSet chapagers(int topage)// 翻页程序
	{
		
		ResultSet res = null;
		try {
			stmt = conn
					.prepareStatement("select * from classt order by id desc limit ?,10");
			stmt.setInt(1, (topage - 1) * 10);
			res = stmt.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			onclose();
		}
		return res;
	}
	public void delcha(String courcename) {
		

		try {
			stmt = conn.prepareStatement("delete from classt where courcename=?");
			stmt.setString(1, courcename);
			stmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			onclose();
		}

	}
	//************************关闭********************************
	public void onclose(){
		if(stmt!=null){
			try {
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
	}
}