dao.java

网上选课系统 JSP + ACCESS数据库 供参考 如有不足 请指点

package com.xuanke.dao;

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

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 = "oracle.jdbc.driver.OracleDriver";

	private String url = "jdbc:oracle:thin:@127.0.0.1:1521:XE";

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

	private String pwd = "888888";

	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 Integer selectId() {
		String driver = "oracle.jdbc.driver.OracleDriver";
		String url = "jdbc:oracle:thin:@127.0.0.1:1521:XE";
		String userName = "wangpeng";
		String password = "888888";
		Integer id = null;
		try {
			Class.forName(driver).newInstance();
			Connection con = DriverManager.getConnection(url, userName,
					password);
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery("select stu.nextval from dual");
			while (rs.next()) {
				id = rs.getInt("nextval");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		// 建立连接

		return id;
	}

	public boolean userlogin(String username, String password) {
		boolean res = false;// Ĭ���ǵ�¼ʧ�ܵ�

		ResultSet rs = null;
		try {
			stmt = conn
					.prepareStatement("select * from wp_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 wp_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) throws SQLException// ����������ֵ��ʾע��ɹ������ؼ�ֵ��ʾע��ʧ��
	{

		boolean bo = false;// ��ʾע��ʧ��
		ResultSet res = null;
		try {
			conn.setAutoCommit(false);
			stmt = conn
					.prepareStatement("select * from wp_yonghu where username=?");// ȥ���û��Ƿ����
			stmt.setString(1, username);// �趨����username
			res = stmt.executeQuery();
			if (!(res.next()))// ˵���û�û���ҵ�
			{
				stmt = conn
						.prepareStatement("insert into wp_yonghu(id,username,password,stage) values(?,?,?,?)");// �������û���Ϣ
				stmt.setInt(1, selectId());
				stmt.setString(2, username);// ����
				stmt.setString(3, password);// ���Dz���
				stmt.setString(4, stage);
				stmt.execute();// ִ��
				bo = true;// ��ʾע��ɹ�
			}
			conn.commit();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			conn.rollback();
			e.printStackTrace();
			onclose();
		}
		return bo;
	}
	//计算用户所占页面
	public int totalpage() {
		int totalpage = 0;

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

	public ResultSet pagers(int topage)// ��ҳ����
	{

		ResultSet res = null;
		try {

			String sql = "select * from ( select rownum r,id,username,password,stage from wp_yonghu) where r between ? and ?";
			stmt = conn.prepareStatement(sql);
			if(topage == 1){
				stmt.setInt(1, (topage - 1) * 10);
				stmt.setInt(2, ((topage - 1) * 10) + 10);
			}else{
				stmt.setInt(1, (topage - 1) * 10+1);
				stmt.setInt(2, ((topage - 1) * 10) + 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 wp_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) throws SQLException// ����������ֵ��ʾע��ɹ������ؼ�ֵ��ʾע��ʧ��
	{

		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()))// ˵���û�û���ҵ�
			// {
			conn.setAutoCommit(false);

			stmt = conn
					.prepareStatement("insert into wp_student(id,xuehao,xingming,sex,age,banji) values(?,?,?,?,?,?)");// �������û���Ϣ
			stmt.setInt(1, selectId());
			stmt.setString(2, xuehao);// ����
			stmt.setString(3, xingming);// ���Dz���
			stmt.setString(4, sex);
			stmt.setString(5, age);
			stmt.setString(6, banji);
			stmt.execute();// ִ��
			bo = true;// ��ʾע��ɹ�
			// }
			conn.commit();

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

	public int stutotalpage() {
		int totalpage = 0;

		ResultSet res = null;
		String sql = "select count(*) from wp_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 - x % 10) / 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 (select rownum r,id,xuehao,xingming,sex,age,banji from wp_student) where r between ? and ?");
			if(topage == 1){
				stmt.setInt(1, (topage - 1) * 10);
				stmt.setInt(2, ((topage - 1) * 10) + 10);
			}else{
				stmt.setInt(1, (topage - 1) * 10+1);
				stmt.setInt(2, ((topage - 1) * 10) + 10);
			}
			res = stmt.executeQuery();
		} catch (SQLException e) {