dataconnection.java

这是学生管理网站的java源码,可以管理学生信息

package org.myJavabeans;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class DataConnection {

	private Connection conn = null;

	private String sql = null;

	private PreparedStatement prepstmt = null;

	public DataConnection() {
		this.getConnection();
	}

	public Connection getConnection() {

		try {
			Context ctx = new InitialContext();
			DataSource ds = (DataSource) ctx
					.lookup("java:comp/env/jdbc/TestDB");
			conn = ds.getConnection();
			return conn;

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

	/*
	 * 察看是否是管理员或教师
	 */
	public int isAdmin(String t_id, String t_password) {

		sql = "select t_password from teachers " + "where t_id=?";
		try {
			if (conn == null || conn.isClosed()) {
				this.getConnection();
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
			prepstmt = conn.prepareStatement(sql);
			prepstmt.setString(1, t_id);
			ResultSet rs = prepstmt.executeQuery();
			if (rs.next()) {
				// 有纪录
				if ((rs.getString("t_password")).equals(t_password)) {
					// 密码正确
					if (t_id.equals("admin")) {
						// 是管理员
						rs.close();
						this.closedatabase();
						return 1;
					} else {
						// 是教师
						rs.close();
						this.closedatabase();
						return 3;
					}
				} else {
					// 密码不正确
					rs.close();
					this.closedatabase();
					return 2;
				}
			} else {
				rs.close();
				this.closedatabase();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		// 没有记录，不是教师和管理员
		return 0;
	}

	/*
	 * 返回教师姓名
	 */
	public String executename(String t_id) {
		sql = "select t_name from teachers where t_id=?";
		try {
			if (conn == null || conn.isClosed()) {
				this.getConnection();
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
			prepstmt = conn.prepareStatement(sql);
			prepstmt.setString(1, t_id);
			ResultSet rs = prepstmt.executeQuery();
			if (rs.next()) {
				String name = rs.getString("t_name");
				rs.close();
				this.closedatabase();
				return name;
			} else {
				rs.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		this.closedatabase();
		return null;
	}

	/*
	 * 教师id是否重复
	 */
	public boolean isUseTeacherid(String t_id) {
		sql = "select t_name from teachers where t_id=?";
		try {
			if (conn == null || conn.isClosed()) {
				this.getConnection();
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
			prepstmt = conn.prepareStatement(sql);
			prepstmt.setString(1, t_id);
			ResultSet rs = prepstmt.executeQuery();
			if (rs.next()) {
				rs.close();
				this.closedatabase();
				return true;
			} else {
				rs.close();
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		this.closedatabase();
		return false;
	}
	/*
	 * 向数据库中添加教师信息
	 */
	public boolean InsertTeacher(String t_name, String t_id, String t_password,
			String t_sex, String t_school, String t_class, String t_phone,
			String t_address, String t_text) {
		//察看class表中是否有班级纪录
		
		sql = "insert into teachers(t_id,t_password,t_name,t_sex,t_school,t_class,t_phone,t_address,t_text) "
				+ "values(?,?,?,?,?,?,?,?,?)";
		try {
			if (conn == null || conn.isClosed()) {
				this.getConnection();
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
			prepstmt = conn.prepareStatement(sql);
			prepstmt.setString(1, t_id);
			prepstmt.setString(2, t_password);
			prepstmt.setString(3, t_name);
			prepstmt.setString(4, t_sex);
			prepstmt.setString(5, t_school);
			prepstmt.setString(6, t_class);
			prepstmt.setString(7, t_phone);
			prepstmt.setString(8, t_address);
			prepstmt.setString(9, t_text);
			int i = prepstmt.executeUpdate();
			if (i > 0) {
				this.closedatabase();
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		this.closedatabase();
		return false;

	}

	/*
	 * 删除教师信息
	 */
	public boolean deleteTeacher(String t_id) {
		sql = "delete from teachers where t_id=?";
		try {
			if (conn == null || conn.isClosed()) {
				this.getConnection();
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
			prepstmt = conn.prepareStatement(sql);
			prepstmt.setString(1, t_id);
			int i = prepstmt.executeUpdate();
			if (i > 0) {
				this.closedatabase();
				return true;
			} else {
				this.closedatabase();
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		return false;
	}

	/*
	 * 返回教师详细信息
	 */
	public TeacherBean returnTeacherAll(String t_id) {
		sql = "select * from teachers where t_id=?";
		try {
			if (conn == null || conn.isClosed()) {
				this.getConnection();
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
			prepstmt = conn.prepareStatement(sql);
			prepstmt.setString(1, t_id);
			ResultSet rs = prepstmt.executeQuery();
			if (rs.next()) {
				TeacherBean t = new TeacherBean();
				t.setT_id(rs.getString("t_id"));
				t.setT_password(rs.getString("t_password"));
				t.setT_name(rs.getString("t_name"));
				t.setT_address(rs.getString("t_address"));
				t.setT_class(rs.getString("t_class"));
				t.setT_phone(rs.getString("t_phone"));
				t.setT_school(rs.getString("t_school"));
				t.setT_sex(rs.getString("t_sex"));
				t.setT_text(rs.getString("t_text"));
				rs.close();
				this.closedatabase();
				return t;
			} else {
				rs.close();
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		this.closedatabase();
		return null;
	}

	/*
	 * 修改教师信息
	 */
	public boolean UpdateTeacher(String t_name, String t_id, String t_password,
			String t_sex, String t_school, String t_class, String t_phone,
			String t_address, String t_text) {
		sql = "update teachers set t_password=?,t_name=?,t_sex=?,t_school=?,"
				+ "t_class=?,t_phone=?,t_address=?,t_text=?" + " where t_id=?";
		try {
			if (conn == null || conn.isClosed()) {
				this.getConnection();
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
			prepstmt = conn.prepareStatement(sql);
			prepstmt.setString(1, t_password);
			prepstmt.setString(2, t_name);
			prepstmt.setString(3, t_sex);
			prepstmt.setString(4, t_school);
			prepstmt.setString(5, t_class);
			prepstmt.setString(6, t_phone);
			prepstmt.setString(7, t_address);
			prepstmt.setString(8, t_text);
			prepstmt.setString(9, t_id);
			int i = prepstmt.executeUpdate();
			if (i > 0) {
				this.closedatabase();
				return true;
			} else {
				this.closedatabase();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return false;
	}

	/*
	 * 察看所有教师信息
	 */

	public List SeeAllTeacher() {
		sql = "select t_id,t_name,t_sex,t_class from teachers";
		List list = new ArrayList();
		try {
			if (conn == null || conn.isClosed()) {
				this.getConnection();
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
			prepstmt = conn.prepareStatement(sql);
			ResultSet rs = prepstmt.executeQuery();
			while (rs.next()) {
				if (rs.getString("t_id").equals("admin")) {
					continue;
				} else {
					TeacherBean t = new TeacherBean();
					t.setT_id(rs.getString("t_id"));
					t.setT_name(rs.getString("t_name"));
					t.setT_class(rs.getString("t_class"));
					t.setT_sex(rs.getString("t_sex"));
					list.add(t);
				}

			}
			rs.close();
			this.closedatabase();
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		this.closedatabase();
		return null;
	}
/*
 * 返回所有学生信息
 */
	public List SeeAllStudent() {
		sql = "select stu_id,stu_name,stu_sex,stu_class from student";
		List list = new ArrayList();
		try {
			if (conn == null || conn.isClosed()) {
				this.getConnection();
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
			prepstmt = conn.prepareStatement(sql);
			ResultSet rs = prepstmt.executeQuery();
			while (rs.next()) {
				
					StudentBean s = new StudentBean();
					s.setStu_id(rs.getString("stu_id"));
					s.setStu_name(rs.getString("stu_name"));
					s.setStu_class(rs.getString("stu_class"));
					s.setStu_sex(rs.getString("stu_sex"));
					list.add(s);
			}
			rs.close();
			this.closedatabase();
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		this.closedatabase();
		return null;
	}
	/*
	 * 学生id是否重复
	 */
	public boolean isUseStudentid(String stu_id) {
		sql = "select stu_name from student where stu_id=?";
		try {
			if (conn == null || conn.isClosed()) {
				this.getConnection();
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
			prepstmt = conn.prepareStatement(sql);
			prepstmt.setString(1, stu_id);
			ResultSet rs = prepstmt.executeQuery();
			if (rs.next()) {
				rs.close();
				this.closedatabase();
				return true;
			} else {
				rs.close();
			}

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

	/*
	 * 向数据库中添加学生信息
	 */
	public boolean InsertStudent(String stu_name, String stu_id,String stu_sex, String stu_school, String stu_class, String stu_phone,
			String stu_address, String stu_text) {
		sql = "insert into student(stu_id,stu_name,stu_sex,stu_school,stu_class,stu_phone,stu_address,stu_text) "
				+ "values(?,?,?,?,?,?,?,?)";
		try {
			if (conn == null || conn.isClosed()) {
				this.getConnection();
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
			prepstmt = conn.prepareStatement(sql);
			prepstmt.setString(1, stu_id);
			prepstmt.setString(2, stu_name);
			prepstmt.setString(3, stu_sex);
			prepstmt.setString(4, stu_school);
			prepstmt.setString(5, stu_class);
			prepstmt.setString(6, stu_phone);
			prepstmt.setString(7, stu_address);
			prepstmt.setString(8, stu_text);
			int i = prepstmt.executeUpdate();
			if (i > 0) {
				this.closedatabase();
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		this.closedatabase();
		return false;

	}

	/*
	 * 删除学生信息
	 */
	public boolean deleteStudent(String stu_id) {
		sql = "delete from student where stu_id=?";
		try {
			if (conn == null || conn.isClosed()) {
				this.getConnection();
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
			prepstmt = conn.prepareStatement(sql);
			prepstmt.setString(1, stu_id);
			int i = prepstmt.executeUpdate();
			if (i > 0) {
				this.closedatabase();
				return true;
			} else {
				this.closedatabase();
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		return false;
	}

	/*
	 * 返回学生详细信息
	 */
	public StudentBean returnStudentAll(String stu_id) {
		sql = "select * from student where stu_id=?";
		try {
			if (conn == null || conn.isClosed()) {
				this.getConnection();
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
			prepstmt = conn.prepareStatement(sql);
			prepstmt.setString(1, stu_id);
			ResultSet rs = prepstmt.executeQuery();
			if (rs.next()) {
				StudentBean t = new StudentBean();
				t.setStu_id(rs.getString("stu_id"));
				t.setStu_name(rs.getString("stu_name"));
				t.setStu_address(rs.getString("stu_address"));
				t.setStu_class(rs.getString("stu_class"));
				t.setStu_phone(rs.getString("stu_phone"));
				t.setStu_school(rs.getString("stu_school"));
				t.setStu_sex(rs.getString("stu_sex"));
				t.setStu_text(rs.getString("stu_text"));
				rs.close();
				this.closedatabase();
				return t;
			} else {
				rs.close();
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		this.closedatabase();
		return null;
	}

	/*
	 * 修改学生信息
	 */
	public boolean UpdateStudent(String stu_name, String stu_id,String stu_sex, String stu_school, String stu_class, String stu_phone,
			String stu_address, String stu_text) {
		sql = "update student set stu_name=?,stu_sex=?,stu_school=?,"
				+ "stu_class=?,stu_phone=?,stu_address=?,stu_text=?" + " where stu_id=?";
		try {
			if (conn == null || conn.isClosed()) {
				this.getConnection();
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
			prepstmt = conn.prepareStatement(sql);
			prepstmt.setString(1, stu_name);
			prepstmt.setString(2, stu_sex);
			prepstmt.setString(3, stu_school);
			prepstmt.setString(4, stu_class);
			prepstmt.setString(5, stu_phone);
			prepstmt.setString(6, stu_address);
			prepstmt.setString(7, stu_text);
			prepstmt.setString(8, stu_id);
			int i = prepstmt.executeUpdate();
			if (i > 0) {
				this.closedatabase();
				return true;
			} else {
				this.closedatabase();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return false;
	}

	/*
	 * 释放连接
	 */
	public void closedatabase() {
		try {
			if (prepstmt != null) {
				prepstmt.close();
			}
			if (conn != null) {
				conn.close();
			}

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