teadao.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.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.swing.ImageIcon;

import oracle.sql.BLOB;

import com.hb.studentmanager.connection.ConnectionDateBase;

public class TeaDAO {
	ConnectionDateBase cdb=new ConnectionDateBase();
	Connection con=cdb.getDateBaseConnection();
	/**
	 * 将插入的信息加入到数据库 
	 * @param dto
	 */
	public void insert(TeaDTO dto)
	{
		String sql="insert into teacher (tea_id,tea_name,tea_sex,tea_bir,tea_add,tea_tel,tea_lev,tea_rem,tea_asp) values(?,?,?,to_date('"+dto.getTea_bir()+"','yyyy-MM-dd'),?,?,?,?,?)";
		PreparedStatement stmt;
		try {
			stmt = con.prepareStatement(sql);
			stmt.setString(1, dto.getTea_id());//获取教师编号
			stmt.setString(2, dto.getTea_name());//获取教师姓名
			stmt.setString(3, dto.getTea_sex());//获取教师性别
			stmt.setString(4, dto.getTea_add());//获取教师地址
			stmt.setString(5, dto.getTea_tel());//获取教师电话
			stmt.setString(6, dto.getTea_lev());//获取教师级别
			stmt.setString(7, dto.getTea_rem());//获取教师备注
			stmt.setString(8, dto.getTea_asp());//获取教师主攻方向
			stmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
//		 将图片存入数据库
		PreparedStatement ps = null;
		ResultSet rs = null;
		BLOB blob = null;
		try {
			con.setAutoCommit(false);
			// 1.委托oracle建立一个空的blob字段值
			ps = con.prepareStatement(""+ "update teacher set tea_pho =empty_blob() where tea_id=?");
			ps.setString(1, dto.getTea_id());
			ps.executeUpdate();
			// 2.把空的 blob 字段读回来
			ps = con.prepareStatement("" + "select tea_pho "+ "from teacher where tea_id=? " + "for update");
			ps.setString(1, dto.getTea_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 teacher set tea_pho=? where tea_id=?");
			ps.setBlob(1, blob);
			ps.setString(2, dto.getTea_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();
			}

		}
	}
	/**
	 * 将选中的记录从数据库中删除
	 * @param teaid
	 */
	public void delete(String teaid)
	{
		String sql="delete from teacher where tea_id=?";
		PreparedStatement stmt;
		try
		{
			stmt=con.prepareStatement(sql);
			stmt.setString(1, teaid);
			stmt.executeUpdate();
		}catch(SQLException e)
		{
			e.printStackTrace();
		}
		
	}
	/**
	 * 查询所有教师的记录，并返回
	 * @return
	 */
	public List select()
	{
		List list =new ArrayList();
		try
		{
			PreparedStatement ps=con.prepareStatement("select * from teacher");
			ResultSet rs=ps.executeQuery();
			while(rs.next())
			{
				TeaDTO dto= new TeaDTO();
				dto.setTea_add(rs.getString("tea_add"));//获取教师的地址
				dto.setTea_asp(rs.getString("tea_asp"));//获取教师的主攻方向
				dto.setTea_bir(rs.getDate("tea_bir")+"");//获取教师的出生日期
				dto.setTea_id(rs.getString("tea_id"));//获取教师的编号
				dto.setTea_lev(rs.getString("tea_lev"));//获取教师的级别
				dto.setTea_name(rs.getString("tea_name"));//获取教师的姓名
				dto.setTea_pho(rs.getBlob("tea_pho"));//获取教师的相片
				dto.setTea_rem(rs.getString("tea_rem"));//获取教师的备注
				dto.setTea_sex(rs.getString("tea_sex"));//获取教师的性别
				dto.setTea_tel(rs.getString("tea_tel"));//获取教师的电话
				list.add(dto);
			}
		}catch(SQLException e)
		{
			e.printStackTrace();
		}
		return list;//返回数组
	}
	/**
	 * 查询教师编号为teaid的教师的相关属性
	 * @param teaid
	 * @return
	 */
	public List select2(String teaid)
	{
		List list=new ArrayList();
		try
		{
			PreparedStatement ps=con.prepareStatement("select * from teacher  where tea_id like '%"+teaid+"%' ");
			ResultSet rs=ps.executeQuery();
			while(rs.next())
			{
				TeaDTO dto=new TeaDTO();
				dto.setTea_add(rs.getString("tea_add"));//获取教师的地址
				dto.setTea_asp(rs.getString("tea_asp"));//获取教师的主攻方向
				dto.setTea_bir(rs.getDate("tea_bir")+"");//获取教师的出生日期
				dto.setTea_id(rs.getString("tea_id"));//获取教师的编号
				dto.setTea_lev(rs.getString("tea_lev"));//获取教师的级别
				dto.setTea_name(rs.getString("tea_name"));//获取教师的姓名
				dto.setTea_pho(rs.getBlob("tea_pho"));//获取教师的相片
				dto.setTea_rem(rs.getString("tea_rem"));//获取教师的备注
				dto.setTea_sex(rs.getString("tea_sex"));//获取教师的性别
				dto.setTea_tel(rs.getString("tea_tel"));//获取教师的电话
				list.add(dto);
			}
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 对所修改的教师进行更新到数据库
	 * @param dto
	 */
 public void update(TeaDTO dto)
 {
	 String sql="update  teacher set tea_name=?,tea_sex=?,tea_add=?,tea_tel=?,tea_lev=?,tea_rem=?,tea_asp=?, tea_bir=to_date('"+dto.getTea_bir()+"','yyyy-MM-dd') where tea_id=?";
		PreparedStatement stmt;
		try {
			stmt = con.prepareStatement(sql);
			
			stmt.setString(1, dto.getTea_name());
			stmt.setString(2, dto.getTea_sex());
			stmt.setString(3, dto.getTea_add());
			stmt.setString(4, dto.getTea_tel());
			stmt.setString(5, dto.getTea_lev());
			stmt.setString(6, dto.getTea_rem());
			stmt.setString(7, dto.getTea_asp());
			stmt.setString(8, dto.getTea_id());
			stmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
//		 将图片存入数据库
		if(dto.getFile()==null)
		{
			
		}
		else
		{
		PreparedStatement ps = null;
		ResultSet rs = null;
		BLOB blob = null;
		try {
			con.setAutoCommit(false);
			// 1.委托oracle建立一个空的blob字段值
			ps = con.prepareStatement(""+ "update teacher set tea_pho =empty_blob() where tea_id=?");
			ps.setString(1, dto.getTea_id());
			ps.executeUpdate();
			// 2.把空的 blob 字段读回来
			ps = con.prepareStatement("" + "select tea_pho "+ "from teacher where tea_id=? " + "for update");
			ps.setString(1, dto.getTea_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 teacher set tea_pho=? where tea_id=?");
			ps.setBlob(1, blob);
			ps.setString(2, dto.getTea_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();
			}

		}
		}
 }
 /**
  * 查询教师编号为teaid的德教师的所有属性
  * @param teaid
  * @return
  */
 public List select1(String teaid)
 {
	 List list=new ArrayList();
	 try{
	 PreparedStatement ps=con.prepareStatement("select * from teacher where tea_id=?");
	   ps.setString(1, teaid);
		ResultSet rs=ps.executeQuery();
		while(rs.next())
		{
			TeaDTO dto= new TeaDTO();
			
			dto.setTea_add(rs.getString("tea_add"));//获取教师的地址
			dto.setTea_asp(rs.getString("tea_asp"));//获取教师的主攻方向
			dto.setTea_bir(rs.getDate("tea_bir")+"");//获取教师的出生日期
			dto.setTea_id(rs.getString("tea_id"));//获取教师的编号
		
			dto.setTea_lev(rs.getString("tea_lev"));//获取教师的级别
		
			dto.setTea_name(rs.getString("tea_name"));//获取教师的姓名
			dto.setTea_pho(rs.getBlob("tea_pho"));//获取教师的相片
			dto.setTea_rem(rs.getString("tea_rem"));//获取教师的备注
			dto.setTea_sex(rs.getString("tea_sex"));//获取教师的性别
			dto.setTea_tel(rs.getString("tea_tel"));//获取教师的电话
			BLOB blob = (BLOB) rs.getBlob("tea_pho");
			
			if(blob==null)
			{
				
				
			}
			 
			else
			{
				InputStream in = blob.getBinaryStream();
				byte []pho=new byte[50*1024];
				in.read(pho);
				ImageIcon icon=new ImageIcon(pho);
				dto.setIcon(icon);
				in.close();
			}
			
			list.add(dto);
		}
	}catch(SQLException e)
	{
		e.printStackTrace();
	}catch(IOException e)
	{
		e.printStackTrace();
	}
	return list;//返回数组
 }
 /**
  * 进行综合查询
  * @param teaid
  * @param teaname
  * @param teasex
  * @param teaadd
  * @param teabir1
  * @param teabir2
  * @return
  */
 public List select3(String teaid,String teaname,String teasex,String teaadd,String teabir1,String teabir2)
	{
		List list=new ArrayList();
		try
		{   PreparedStatement ps=null;
			if(teaname.equals("")&&teaadd.equals(""))
			{
				 ps=con.prepareStatement("select * from teacher  where tea_id like '%"+teaid+"%' and  tea_sex like '%"+teasex+"%'  and tea_bir >=to_date('"+teabir1+"','yyyy-mm-dd') and tea_bir<=to_date('"+teabir2+"','yyyy-mm-dd')");
			}
			else
			{
			if(teaname.equals("")&&!teaadd.equals(""))
			{
				 ps=con.prepareStatement("select * from teacher  where tea_id like '%"+teaid+"%' and  tea_sex like '%"+teasex+"%' and tea_add like '%"+teaadd+"%' and tea_bir >=to_date('"+teabir1+"','yyyy-mm-dd') and tea_bir<=to_date('"+teabir2+"','yyyy-mm-dd')");
			}
			else if(!teaname.equals("")&&teaadd.equals(""))
			{
				 ps=con.prepareStatement("select * from teacher  where tea_id like '%"+teaid+"%' and tea_name like '%"+teaname+"%' and tea_sex like '%"+teasex+"%'  and tea_bir >=to_date('"+teabir1+"','yyyy-mm-dd') and tea_bir<=to_date('"+teabir2+"','yyyy-mm-dd')");
			}
			else
			{
				 ps=con.prepareStatement("select * from teacher  where tea_id like '%"+teaid+"%' and tea_name like '%"+teaname+"%' and tea_sex like '%"+teasex+"%' and tea_add like '%"+teaadd+"%' and tea_bir >=to_date('"+teabir1+"','yyyy-mm-dd') and tea_bir<=to_date('"+teabir2+"','yyyy-mm-dd')");
			
			}
			}
			//PreparedStatement ps=con.prepareStatement("select * from teacher  where tea_id like '%"+teaid+"%' and tea_name like '%"+teaname+"%' and tea_sex like '%"+teasex+"%' and tea_add like '%"+teaadd+"%' and tea_bir >=to_date('"+teabir1+"','yyyy-mm-dd') and tea_bir<=to_date('"+teabir2+"','yyyy-mm-dd')");
			ResultSet rs=ps.executeQuery();
			while(rs.next())
			{
				
				TeaDTO dto=new TeaDTO();
				dto.setTea_add(rs.getString("tea_add"));//获取教师的地址
				dto.setTea_asp(rs.getString("tea_asp"));//获取教师的主攻方向
				dto.setTea_bir(rs.getDate("tea_bir")+"");//获取教师的出生日期
				dto.setTea_id(rs.getString("tea_id"));//获取教师的编号
				dto.setTea_lev(rs.getString("tea_lev"));//获取教师的级别
				dto.setTea_name(rs.getString("tea_name"));//获取教师的姓名
				dto.setTea_pho(rs.getBlob("tea_pho"));//获取教师的相片
				dto.setTea_rem(rs.getString("tea_rem"));//获取教师的备注
				dto.setTea_sex(rs.getString("tea_sex"));//获取教师的性别
				dto.setTea_tel(rs.getString("tea_tel"));//获取教师的电话
				list.add(dto);
			}
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		return list;
	}
}