studentaccess.java

基本完成上传本地图片和信息的分类显示

package com.bean;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParsePosition;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

public class StudentAccess extends DatabaseAccess {
	private ResultSet rs = null;
	private Connection con = null;
	private PreparedStatement preparedStmt = null;
	private Statement stm = null;

	// private private CallableStatement preparedcall = null;

	public StudentAccess() {
		// TODO Auto-generated constructor stub
		super();
		con = super.getCon();
	}

	public StudentAccess(String jndi) {
		super(jndi);
		con = super.getCon();
		// TODO Auto-generated constructor stub
	}

	public Student getStudent(int id) {
		Student bean = new Student();
		String query = "select id,name,birthplace,birthday,picture from T_Student where id=?";
		try {
			preparedStmt = con.prepareStatement(query);
			preparedStmt.setInt(1, id);
			rs = preparedStmt.executeQuery();
			while (rs.next()) {
				bean.setId(rs.getInt("id"));
				bean.setName(rs.getString("name"));
				bean.setBirthPlace(rs.getString("birthplace"));
				bean.setBirthday(rs.getDate("birthday") + "");
				// bean.setPhoto(null);
				bean.setBytes(rs.getBytes("picture"));
				if (bean.getBytes() == null)
					System.out.println("null");
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return bean;
	}

	public boolean delStudent(int id) {
		String query = "delete from T_Student where id=?";
		boolean flag = false;
		try {
			preparedStmt = con.prepareStatement(query);
			preparedStmt.setInt(1, id);
			preparedStmt.executeUpdate();
			int result = preparedStmt.executeUpdate();
			if (result > 0) {
				flag = true;
			} else {
				flag = false;
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return flag;

	}

	public ArrayList getStudentList(int pageSize, int intPage) {
		ArrayList array = new ArrayList();
		String query = "select id,name,birthplace,birthday,picture from T_Student";
		int pages = 0;
		try {
			stm = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			rs = stm.executeQuery(query);
			rs.last();

			pages = (rs.getRow() + pageSize - 1) / pageSize;
			if (intPage > pages)
				intPage = pages;
			if (pages > 0) {
				rs.absolute((intPage - 1) * pageSize + 1);
				int i = 0;
				while (i < pageSize && !rs.isAfterLast()) {
					Student bean = new Student();
					bean.setId(rs.getInt("id"));
					bean.setName(rs.getString("name"));
					bean.setBirthPlace(rs.getString("birthplace"));
					bean.setBirthday(rs.getDate("birthday") + "");
					// bean.setPhoto(null);
					// bean.setBytes(rs.getBytes("picture"));
					array.add(bean);
					rs.next();
					i++;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		array.add(pages + "");
		return array;
	}

	public ArrayList getStudentList(String parm, int pageSize, int intPage) {
		ArrayList array = new ArrayList();
		String query = "select id,name,birthplace,birthday from T_Student"
				+ " where name like ?";
		int pages = 0;
		try {
			preparedStmt = con
					.prepareStatement(query, ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_UPDATABLE);
			String name = "%" + parm + "%";
			preparedStmt.setString(1, name);

			System.out.println(name);
			// stm = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
			// ResultSet.CONCUR_READ_ONLY);
			// rs = stm.executeQuery(query + name);
			// rs.last();
			rs = preparedStmt.executeQuery();
			rs.last();

			pages = (rs.getRow() + pageSize - 1) / pageSize;
			if (intPage > pages)
				intPage = pages;
			if (pages > 0) {
				rs.absolute((intPage - 1) * pageSize + 1);
				int i = 0;
				while (i < pageSize && !rs.isAfterLast()) {
					Student bean = new Student();
					bean.setId(rs.getInt("id"));
					bean.setName(rs.getString("name"));
					bean.setBirthPlace(rs.getString("birthplace"));
					bean.setBirthday(rs.getDate("birthday") + "");
					// bean.setPhoto(null);
					// bean.setBytes(rs.getBytes("picture"));
					array.add(bean);
					rs.next();
					i++;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		array.add(pages + "");

		return array;
	}

	public boolean addNewStudent(Student bean) {
		boolean flag = false;
		String query = "insert into T_Student(name,birthplace,birthday,picture) values(?,?,?,?) ";
		try {
			preparedStmt = con.prepareStatement(query);
			// preparedStmt.setInt(1, bean.getId());
			preparedStmt.setString(1, bean.getName());
			preparedStmt.setString(2, bean.getBirthPlace());
			preparedStmt.setDate(3, Date.valueOf(bean.getBirthday()));

			File files = bean.getPhoto();
			FileInputStream fis = new FileInputStream(files);
			preparedStmt.setBinaryStream(4, fis, (int) files.length());

			int result = preparedStmt.executeUpdate();
			if (result > 0) {
				flag = true;
			} else {
				flag = false;
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}

	public Student editStudent(Student bean) {

		String query = "update T_Student set name = ?,birthplace=?,birthday=?";
		String s2 = " where id=" + bean.getId();

		if (bean.getPhoto() == null)
			query = query + s2;
		else
			query = query + ",picture=?" + s2;

		try {
			preparedStmt = con.prepareStatement(query);
			// preparedStmt.setInt(1, bean.getId());
			preparedStmt.setString(1, bean.getName());
			preparedStmt.setString(2, bean.getBirthPlace());
			preparedStmt.setDate(3, Date.valueOf(bean.getBirthday()));
			if (bean.getPhoto() != null) {
				File files = bean.getPhoto();
				FileInputStream fis = new FileInputStream(files);
				preparedStmt.setBinaryStream(4, fis, (int) files.length());
			}
			int result = preparedStmt.executeUpdate();
			if (result > 0) {
				Student bn = getStudent(bean.getId());
				return bn;
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		// return bean;
		return null;

	}

}