studao.java

包含了学生管理系统的一些基本操作以及相关窗口页面实现。

package com.hb.studentmanager.date;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import javax.swing.ImageIcon;

import oracle.sql.BLOB;

import com.hb.studentmanager.connection.ConnectionDateBase;
import com.sun.jmx.snmp.Timestamp;


public class StuDAO {
	ConnectionDateBase cdb=new ConnectionDateBase();//调用JDBC配置文件
	
	Connection con=cdb.getDateBaseConnection();//调用Connection中的连接
	
	PreparedStatement ps=null;
	
	ResultSet rs=null;
	
	 /**
     * 查询数据库，初始化页面显示栏
     * @return
     */
    public List select(){
    	List list = new ArrayList();//得到一个数组
    	try {
    		 String sql="select * from student order by stu_id";//查询学生表按学号来排序
			 ps = con.prepareStatement(sql);
			 rs = ps.executeQuery();
			while(rs.next()){//遍历结果集
				StuDTO dto =new StuDTO();
				dto.setStu_id(rs.getString("stu_id"));//得到学号
				dto.setStu_name(rs.getString("stu_name"));//得到姓名
				dto.setStu_sex(rs.getString("stu_sex"));//得到性别
				dto.setStu_bir(rs.getDate("stu_bir")+"");//得到出生日期
				dto.setStu_add(rs.getString("stu_add"));//得到家庭住址
				dto.setCla_id(rs.getString("cla_id"));
				list.add(dto);//将数据对象添加到数组里面
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return list;//将数组反回去
    }
	
	
	/**
	 * 得到StuAction中StuAddFrame传过来的值
	 * 并插入到数据库
	 * @param dto
	 */
	public void insert(StuDTO dto){
		try {
			String sql="insert into student (stu_id,stu_name,stu_sex,stu_bir,stu_add,stu_tel,stu_pol,stu_rem,cla_id) values(?,?,?,to_date('"+dto.getStu_bir()+"','yyyy-MM-dd'),?,?,?,?,?)";//将数据插入到学生表中
			ps = con.prepareStatement(sql);
			ps.setString(1, dto.getStu_id());//设置学号			
			ps.setString(2, dto.getStu_name());//设置学生姓名
			ps.setString(3, dto.getStu_sex());//设置学生性别
			ps.setString(4, dto.getStu_add());//设置学生家庭住址
			ps.setString(5, dto.getStu_tel());//设置学生联系方式
			ps.setString(6, dto.getStu_pol());//设置学生的政治面貌
			ps.setString(7, dto.getStu_rem());//设置学生备注
			ps.setString(8, dto.getCla_id());//设置学生所在班级
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//将图片存入数据库
		BLOB blob = null;
		try {
			con.setAutoCommit(false);
			// 1.委托oracle建立一个空的blob字段值
			ps = con.prepareStatement(""+ "update student set stu_pho =empty_blob() where stu_id=?");//根据学号更新照片
			ps.setString(1, dto.getStu_id());
			ps.executeUpdate();
			// 2.把空的 blob 字段读回来
			ps = con.prepareStatement("" + "select stu_pho "+ "from student where stu_id=? " + "for update");
			ps.setString(1, dto.getStu_id());
			rs = ps.executeQuery();
			while(rs.next()){
			blob = (BLOB) rs.getBlob(1);
			}
			if (blob == null) {
			}
			else{
				if(dto.getFile()!=null){
			
			// 3.将二进制数据写入blob对象
			InputStream in = new FileInputStream(dto.getFile());
			OutputStream out = blob.getBinaryOutputStream();
			int len = in.available();
			byte[] b = new byte[len];
			in.read(b);
			out.write(b);
			in.close();
			out.close();
			// 4.更新数据库
			ps = con.prepareStatement("update student set stu_pho=? where stu_id=?");
			ps.setBlob(1, blob);
			ps.setString(2, dto.getStu_id());
			ps.executeUpdate();
			con.commit();
			con.setAutoCommit(true);
			}
			}
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		} catch (IOException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				ps.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}
	}
	
	/**
	 * 根据学号更新student中的数据
	 * @param stuid
	 */
	public void stuUpdate(StuDTO dto){
			try {
				String sql="update student set stu_name=? , stu_sex=? ,stu_bir=to_date('"+dto.getStu_bir()+"','yyyy-MM-dd'),stu_add=?,stu_tel=?,stu_pol=?,stu_rem=?,cla_id=? where stu_id=?";//根据学号来更新数据库里学生表字段
				ps=con.prepareStatement(sql);
				ps.setString(1, dto.getStu_name());//更新学生姓名
				ps.setString(2, dto.getStu_sex());//更新学生性别
				ps.setString(3, dto.getStu_add());//更新学生家庭住址
				ps.setString(4, dto.getStu_tel());//更新学生联系方式
				ps.setString(5, dto.getStu_pol());//更新学生政治面貌
				ps.setString(6, dto.getStu_rem());//更新学生备注
				ps.setString(7, dto.getCla_id());//更新学生班级
				ps.setString(8, dto.getStu_id());//更新学生学号
				ps.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	            // 将图片存入数据库
			    if(dto.getFile()==null){
			    	return;
			    }
				BLOB blob = null;
				try {
					con.setAutoCommit(false);
					// 1.委托oracle建立一个空的blob字段值
					ps = con.prepareStatement(""+ "update student set stu_pho =empty_blob() where stu_id=?");
					ps.setString(1, dto.getStu_id());
					ps.executeUpdate();
					// 2.把空的 blob 字段读回来
					ps = con.prepareStatement("" + "select stu_pho "+ "from student where stu_id=? " + "for update");
					ps.setString(1, dto.getStu_id());
					rs = ps.executeQuery();
					while(rs.next()){
					blob = (BLOB) rs.getBlob(1);
					}
					if (blob == null) {
						
					}
					// 3.将二进制数据写入blob对象
					else
					{
					if(dto.getFile()!=null)
					{
					FileInputStream in = new FileInputStream(dto.getFile());
					OutputStream out = blob.getBinaryOutputStream();
					int len = in.available();
					byte[] b = new byte[len];
					in.read(b);
					out.write(b);
					in.close();
					out.close();
					// 4.更新数据库
					ps = con.prepareStatement("update student set stu_pho=? where stu_id=?");//更新学生照片
					ps.setBlob(1, blob);
					ps.setString(2, dto.getStu_id());
					ps.executeUpdate();
					con.commit();
					con.setAutoCommit(true);
					}
					}
				} catch (FileNotFoundException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					try {
						con.rollback();
					} catch (SQLException e1) {
						// TODO Auto-generated catch block
						e1.printStackTrace();
					}
				} catch (SQLException e) {
					try {
						con.rollback();
					} catch (SQLException e1) {
						// TODO Auto-generated catch block
						e1.printStackTrace();
					}
					e.printStackTrace();
				} catch (IOException e) {
					try {
						con.rollback();
					} catch (SQLException e1) {
						// TODO Auto-generated catch block
						e1.printStackTrace();
					}
					e.printStackTrace();
				} finally {
					try {
						rs.close();
						ps.close();
						con.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}		
	}
	
	/**
	 * 得到StuAction中的学号并从数据库中删除
	 * @param stuid
	 */
    public void delete(String stuid){
		String sql="delete from student where stu_id=?";//跟据学号删除学生表中的数据
		PreparedStatement stmt;
		try {
			stmt = con.prepareStatement(sql);
			stmt.setString(1, stuid);//把Stuid的值传给stu_id
			stmt.executeUpdate();//删除后更新数据
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    }
    /**
     * 跟据学号查询所有学生数据，并传到StuAction中
     * @param stuid
     * @return
     */
    public List selectAll(String stuid){
    	 List list=new ArrayList();
    	 try{
    	    ps=con.prepareStatement("select * from student where Stu_id=?");
    	    ps.setString(1, stuid);
    		rs=ps.executeQuery();
    		while(rs.next())
    		{
    			StuDTO dto= new StuDTO();
    			dto.setStu_id(stuid);//获得学号
    			dto.setStu_name(rs.getString("Stu_name"));//获得姓名
    			dto.setStu_sex(rs.getString("Stu_sex"));//获得性别
    			dto.setStu_bir(rs.getDate("Stu_bir")+"");//获得出生日期
    			dto.setStu_add(rs.getString("Stu_add"));//获得家庭住址
    			dto.setStu_tel(rs.getString("Stu_tel"));//获得联系电话
    			dto.setStu_pol(rs.getString("Stu_pol"));//获得政治面貌
    			dto.setStu_pho(rs.getBlob("Stu_pho"));//获得照片
    			dto.setStu_rem(rs.getString("Stu_rem"));//获得备注
    			dto.setCla_id(rs.getString("cla_id"));//获得班级号
    			BLOB blob = (BLOB) rs.getBlob("Stu_pho");//将照片转换委BLOB形式
    			if(blob==null)
    			{	
    			}
    			else
    			{
    				InputStream in = blob.getBinaryStream();//将照片转换为流对象
    				byte []pho=new byte[50*1024];//设置照片大小
    				in.read(pho);//读出照片
    				ImageIcon icon=new ImageIcon(pho);//把照片放到ICON中
    				dto.setIcon(icon);//吧ICON放到dto中
    				in.close();
    			}
    			list.add(dto);//将根据学号查询到的学生表数据放入List数组中
    		}
    	}catch(SQLException e)
    	{
    		e.printStackTrace();
    	}catch(IOException e)
    	{
    		e.printStackTrace();
    	}	
    	return list;//返回数组到StuAction
    }
    
    /**
     * 得到学生表中的主键
     * @param dto
     * @return
     */
    public List selectStuPk(StuDTO dto){
    	List list =new ArrayList();
    	String stuid=dto.getStu_id();
    	try {
    		String sql="select * from student where stu_id=?";//根据学号查询学生表
			ps=con.prepareStatement(sql);
			ps.setString(1, stuid);//
			rs=ps.executeQuery();
			while(rs.next()){
				dto=new StuDTO();
				dto.setStu_id(rs.getString("stu_id"));//得到学号
				list.add(dto);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return list; 	
    }
    
    /**
     * 查询班级表，得到班号并返回班号到StuAtion中
     * @return
     */
    public List getClassName(){
    	List list = new ArrayList();
    	try {
			ps=con.prepareStatement("select cla_id from class");//查询班级表
			rs=ps.executeQuery();
			while(rs.next()){
				StuDTO dto =new StuDTO();
				dto.setCla_id(rs.getString("cla_id"));//得到班号
	            list.add(dto);//将数组添加到数组中
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return list;
    }
    
    /**
     * 联合查询
     * @param stuid
     * @param name
     * @param sex
     * @param bir
     * @param addr
     * @param claid
     * @return
     */
    public List selsetStudent(String stuid,String name,String sex,String bir1,String bir2,String addr,String claid){
    	List list = new ArrayList();
    	try {
    		/**
    		 * 下列判断是表中出现空值时候根据情况依次排除
    		 */
    		if(name.equals("")&&addr.equals("")){
    			String sql="select * from student where stu_id like '%"+stuid+"%'  and stu_sex like '%"+sex+"%' and  stu_bir >=to_date('"+bir1+"','yyyy-mm-dd') and stu_bir<=to_date('"+bir2+"','yyyy-mm-dd')  and cla_id like  '%"+claid+"%'";
    			ps=con.prepareStatement(sql);
    		}
    		else{
    			if(name.equals("")&&!addr.equals("")){
    				String sql="select * from student where stu_id like '%"+stuid+"%' and stu_sex like '%"+sex+"%' and stu_bir >=to_date('"+bir1+"','yyyy-mm-dd') and stu_bir<=to_date('"+bir2+"','yyyy-mm-dd') and stu_add like '%"+addr+"%' and cla_id like  '%"+claid+"%'";
    				ps=con.prepareStatement(sql);
    			}
    			else{
    				if(!name.equals("")&&addr.equals("")){
    					String sql="select * from student where stu_id like '%"+stuid+"%' and Stu_name like '%"+name+"%' and stu_sex like '%"+sex+"%' and stu_bir >=to_date('"+bir1+"','yyyy-mm-dd') and stu_bir<=to_date('"+bir2+"','yyyy-mm-dd') and cla_id like  '%"+claid+"%'";
    					ps=con.prepareStatement(sql);
    				}
    				else{
    					String sql="select * from student where stu_id like '%"+stuid+"%' and Stu_name like '%"+name+"%' and stu_sex like '%"+sex+"%' and stu_bir >=to_date('"+bir1+"','yyyy-mm-dd') and stu_bir<=to_date('"+bir2+"','yyyy-mm-dd') and stu_add like '%"+addr+"%' and cla_id like  '%"+claid+"%'";
    					ps=con.prepareStatement(sql);
    				}
    			}
    		}
			rs=ps.executeQuery();
			while(rs.next()){
				StuDTO dto=new StuDTO();
				dto.setStu_id(rs.getString("stu_id"));
				dto.setStu_name(rs.getString("stu_name"));
				dto.setStu_sex(rs.getString("stu_sex"));
				dto.setStu_bir(rs.getDate("stu_bir")+"");
				dto.setStu_add(rs.getString("stu_add"));
				dto.setCla_id(rs.getString("cla_id"));
				list.add(dto);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return list;
    }
}