studentimpl.java

用Java编写的简易的学生信息管理系统,有涉及到java web等

package com.impl;

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 com.bean.Student;
import com.db.DBConnection;

//2	系统功能基本要求
//1.	求每个fScorei的平均值；
//2.	对某个fScorei进行排序；
//3.	求某个学生的总分及平均值；
//4.	以学号、名字为关键字查找其对应的学生信息；
//5.	删除某个学生的信息；
//6.	插入一个学生的信息；
//格式：sStuNo，sName，sSex，iYea，iMonth，iDay，sDepartment，sSpecialty，fScore1，fScore2，fScore3，fScore4
//要求用:Aaccess数据库(用SQL语言查询)
//9.项目的Java程序要求//
//	JSP（用HTML标11.	识语言）+Servlet+Bean（仅为数据库所用）

public class Studentimpl {
	String url="";
	public Studentimpl(String url){
		this.url=url;
	}
	/**
	 * 对某个学科进行排序
	 * 
	 * @param i学科序号
	 * @return 列表
	 */
	public List compositor(int i) {
		List list = new ArrayList();
		StringBuffer sql = new StringBuffer(
				"select a.*,b.*, c.* from STUDENT a,PERSON b,BIRTHDAY c where a.sStuNo=b.sStuNo and a.sStuNo=c.sStuNo order by fScore");
		sql.append(i);
		Connection conn = DBConnection.getConection(url);
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql.toString());
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Student student = new Student();
				student.setSStuNo(rs.getString("sStuNo"));
				student.setSName(rs.getString("sName"));
				student.setSSex(rs.getString("sSex"));
				student.setDay(rs.getInt("iDay"));
				student.setMonth(rs.getInt("iMonth"));
				student.setYear(rs.getInt("iYea"));
				student.setSDepartment(rs.getString("sDepartment"));
				student.setSSpecialty(rs.getString("sSpecialty"));
				student.setFScore1(rs.getFloat("fScore1"));
				student.setFScore2(rs.getFloat("fScore2"));
				student.setFScore3(rs.getFloat("fScore3"));
				student.setFScore4(rs.getFloat("fScore4"));
				list.add(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.closePs(ps);
			DBConnection.closeConnection(conn);
		}

		return list;
	}

	/**
	 * 获得每科成绩的平均值
	 * 
	 * @return 列表
	 */
	public List getAvg() {
		String sql = "select avg(fScore1) as fScore_1 ,avg(fScore2) as fScore_2,avg(fScore3) as fScore_3, avg(fScore4) as fScore_4 from STUDENT ";
		List list = new ArrayList();
		Connection conn = DBConnection.getConection(url);
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql.toString());
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				Student student = new Student();
				student.setFScore1(rs.getFloat("fScore_1"));
				student.setFScore2(rs.getFloat("fScore_2"));
				student.setFScore3(rs.getFloat("fScore_3"));
				student.setFScore4(rs.getFloat("fScore_4"));
				list.add(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.closePs(ps);
			DBConnection.closeConnection(conn);
		}
		return list;
	}
	public boolean getSno(String sStuNo) {
		String sql="select count(sStuNo) as count from student where sStuNo='"+sStuNo+"'";
		Connection conn=DBConnection.getConection(url);
		Statement stat = null;
		String count="";
		try {
			stat=conn.createStatement();
			ResultSet rs=stat.executeQuery(sql);
			if(rs.next()){
				count=rs.getString("count");
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally{
			DBConnection.closeStat(stat);
			DBConnection.closeConnection(conn);
		}
		if("0".equals(count)){
			return false;
		}else{
			return true;
		}
		
	}
	/**
	 * 获得每个学生的平均值及总分
	 * 
	 * @return 学生平均值及总分列表
	 */
	public float getStudentAvg(String sStuNo) {
		String sql="select (t.fscore1+t.fscore2+t.fscore3+t.fscore4)/4 as avg from student t where sstuno='"+sStuNo+"'";
		Connection conn=DBConnection.getConection(url);
		PreparedStatement ps=null;
		String avg = null;
		try {
			ps=conn.prepareStatement(sql);
			ResultSet rs=ps.executeQuery();
			if(rs.next()){
				avg= rs.getString("avg");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBConnection.closePs(ps);
			DBConnection.closeConnection(conn);
		}
		;
		return Float.parseFloat(avg);
	}

	/**
	 * 查找学生信息 （姓名的模糊查找）
	 * 
	 * @param sName
	 *            学生姓名 （姓名为空表示无此条件。）
	 * @param sStuNo
	 *            学生编号（-1：无此条件）
	 * @return 学生信息列表
	 */
	public List getStudentInfo(String sStuNo, String sName) {
		List list = new ArrayList();
		StringBuffer sql = new StringBuffer("select a.*,b.*, c.* from STUDENT a,PERSON b,BIRTHDAY c where a.sStuNo=b.sStuNo and a.sStuNo=c.sStuNo");
		
		if (sStuNo != null && !sStuNo.trim().equals(""))
			sql.append(" and a.sStuNo='" + sStuNo+"'");
		if (sName != null && !sName.trim().equals("")) {
			//sql.append(" and b.sName like '*" + sName + "*' ");
			sql.append(" and b.sName = '" + sName + "' ");
		} 
		Connection conn = DBConnection.getConection(url);
		PreparedStatement ps = null;
		ResultSet rs =null;
		try {
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			while (rs.next()) {
				Student student = new Student();
				student.setSStuNo(rs.getString("sStuNo"));
				student.setSName(rs.getString("sName"));
				System.out.println(student.getSName());
				student.setSSex(rs.getString("sSex"));
				student.setYear(rs.getInt("iYea"));
				student.setMonth(rs.getInt("iMonth"));
				student.setDay(rs.getInt("iDay"));
				student.setSDepartment(rs.getString("sDepartment"));
				student.setSSpecialty(rs.getString("sSpecialty"));
				student.setFScore1(rs.getFloat("fScore1"));
				student.setFScore2(rs.getFloat("fScore2"));
				student.setFScore3(rs.getFloat("fScore3"));
				student.setFScore4(rs.getFloat("fScore4"));
				System.out.println(student.getSStuNo());
				list.add(student);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.closeRs(rs);
			DBConnection.closePs(ps);
			DBConnection.closeConnection(conn);
		}

		return list;
	}

	/**
	 * 删除某个学生信息
	 * 
	 * @param sStuNo
	 *            学生编号
	 */
	public boolean delete(String sStuNo) {
		String sql1 = "delete from STUDENT a where  a.sStuNo='" + sStuNo+"'";
		String sql2 = "delete from PERSON b where  b.sStuNo='" + sStuNo+"'";
		String sql3 = "delete from BIRTHDAY c where  c.sStuNo='" + sStuNo+"'";

		Connection conn = DBConnection.getConection(url);
		Statement stat = null;

		try {
			stat = conn.createStatement();
			stat.executeUpdate(sql1);
			stat.executeUpdate(sql2);
			stat.executeUpdate(sql3);
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}finally{
			DBConnection.closeStat(stat);
			DBConnection.closeConnection(conn);
		}
	}

	/**
	 * 添加学生信息
	 * 
	 * @param student
	 */
	public boolean toinsert(Student student) {
		String sql = "insert into STUDENT(sStuNo,sDepartment,sSpecialty,fScore1,fScore2,fScore3,fScore4) values(?,?,?,?,?,?,?)";
		String sql2 = "insert into PERSON(sStuNo,sName,sSex) values(?,?,?)";
		String sql3 = "insert into BIRTHDAY(sStuNo,iYea,iMonth,iDay) values(?,?,?,?)";
		Connection conn = DBConnection.getConection(url);
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, student.getSStuNo());
			ps.setString(2, student.getSDepartment());
			ps.setString(3, student.getSSpecialty());
			ps.setFloat(4, student.getFScore1());
			ps.setFloat(5, student.getFScore2());
			ps.setFloat(6, student.getFScore3());
			ps.setFloat(7, student.getFScore4());
			ps.executeUpdate();
			ps=conn.prepareStatement(sql2);
			ps.setString(1, student.getSStuNo());
			ps.setString(2, student.getSName());
			ps.setString(3, student.getSSex());
			ps.executeUpdate();
			ps=conn.prepareStatement(sql3);
			ps.setString(1, student.getSStuNo());
			ps.setInt(2, student.getYear());
			ps.setInt(3, student.getMonth());
			ps.setInt(4, student.getDay());
			ps.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			DBConnection.closePs(ps);
			DBConnection.closeConnection(conn);
		}

	}

}