uppicoper.java

某高校的门户网站源码

package com.zzx.updownload;

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

import com.zzx.util.DataConn;

public class UpPicOper {

	private Connection conn = null;
	private PreparedStatement psmt = null;

	public void insert(UpPic pic) {
		String sql = "insert into uppic(filename,filecontent,pictype)values(?,?,?)";

		try {
			conn = new DataConn().getConn();
			psmt = conn.prepareStatement(sql);
			psmt.setString(1, pic.getFilename());
			psmt.setString(2, pic.getFilecontent());
			psmt.setInt(3, pic.getType());
			psmt.executeUpdate();
			psmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	public List<UpPic> queryAll() {

		String sql = "select * from uppic";
		List<UpPic> all = new ArrayList();
		ResultSet rs = null;
		try {
			conn = new DataConn().getConn();
			psmt = conn.prepareStatement(sql);
			rs = psmt.executeQuery();

			while (rs.next()) {
				UpPic uppic = new UpPic();
				uppic.setId(rs.getInt(1));
				uppic.setFilename(rs.getString(2));
				uppic.setFilecontent(rs.getString(3));
				all.add(uppic);
			}
			rs.close();
			psmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return all;

	}

	public List<UpPic> queryAll(int currentPage, int lineSize) {

		String sql = "SELECT * FROM uppic order by id desc limit "
				+ (currentPage - 1) * lineSize + "," + lineSize;

		List<UpPic> all = new ArrayList();
		ResultSet rs = null;
		try {
			conn = new DataConn().getConn();
			psmt = conn.prepareStatement(sql);
			rs = psmt.executeQuery();
	
			while (rs.next()) {
				UpPic uppic = new UpPic();
				uppic.setId(rs.getInt(1));
				uppic.setFilename(rs.getString(2));
				uppic.setFilecontent(rs.getString(3));
				uppic.setType(rs.getInt(4));
				all.add(uppic);
			}
			rs.close();
			psmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return all;

	}

	//用来对新闻图片查询
	public List<UpPic> queryAll(int lineSize) {

		String sql = "SELECT * FROM uppic where pictype = 1 order by id desc limit 0 ,"
				+ lineSize;
		
		List<UpPic> all = new ArrayList();
		ResultSet rs = null;
		try {
			conn = new DataConn().getConn();
			psmt = conn.prepareStatement(sql);
			rs = psmt.executeQuery();

			while (rs.next()) {
				UpPic uppic = new UpPic();
				uppic.setId(rs.getInt(1));
				uppic.setFilename(rs.getString(2));
				all.add(uppic);
			}
			rs.close();
			psmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return all;

	}

	public List<UpPic> queryAll(int type, int currentPage, int lineSize) {


		String sql = "SELECT * FROM uppic where pictype = " + type
				+ " order by id desc limit " + (currentPage - 1) * lineSize
				+ "," + lineSize;

		List<UpPic> all = new ArrayList();
		ResultSet rs = null;
		try {
			conn = new DataConn().getConn();
			psmt = conn.prepareStatement(sql);
			rs = psmt.executeQuery();

			while (rs.next()) {
				UpPic uppic = new UpPic();
				uppic.setId(rs.getInt(1));
				uppic.setFilename(rs.getString(2));
				uppic.setFilecontent(rs.getString(3));
				all.add(uppic);
			}
			rs.close();
			psmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return all;

	}

	public void delete(int id) {
		Connection conn = new DataConn().getConn();
		PreparedStatement psmt = null;
		String sql = "delete from uppic where id =?";
		try {
			psmt = conn.prepareStatement(sql);
			psmt.setInt(1, id);
			psmt.executeUpdate();
			psmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public int queryCount() {
		int count = 0;
		Connection conn = new DataConn().getConn();
		PreparedStatement psmt = null;
		ResultSet rs = null;
		String sql = "SELECT count(*) FROM uppic";
		try {
			psmt = conn.prepareStatement(sql);
			rs = psmt.executeQuery();
			while (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		System.out.println(count);
		return count;
	}

	
	public int queryCount(int type) {
		int count = 0;
		Connection conn = new DataConn().getConn();
		PreparedStatement psmt = null;
		ResultSet rs = null;
		String sql = "SELECT count(*) FROM uppic where pictype = "+type;
		try {
			psmt = conn.prepareStatement(sql);
			rs = psmt.executeQuery();
			while (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		System.out.println(count);
		return count;
	}

}