downloaddaoimple.java

本程序的功能如下： 1）本程序完全采用jsp脚本技术 2）后台管理

package org.xg.download.imple;

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

import org.xg.download.Download;
import org.xg.download.dao.DownloadDAO;
import org.xg.download.db.ConnectionDB;

public class DownloadDAOImple implements DownloadDAO
{
	private ConnectionDB dbc = null;

	public DownloadDAOImple() 
	{
		this.dbc = new ConnectionDB();
	}
	
	public int getAllCount() throws Exception
	{
		int count = 0;
		String sql = "SELECT COUNT(id) from download";
		PreparedStatement pstmt = null;
		try {
			pstmt = this.dbc.getConnection().prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				count = rs.getInt(1);
			}
			pstmt.close();
			rs.close();
		} catch (Exception e) {
			throw e;
		}
		return count;
	}

	public int getByLikeCount(String cond) throws Exception
	{
		int count = 0;
		String sql = "SELECT COUNT(id) from download WHERE title LIKE ?";
		PreparedStatement pstmt = null;
		try {
			pstmt = this.dbc.getConnection().prepareStatement(sql);
			pstmt.setString(1, "%" + cond + "%");
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				count = rs.getInt(1);
			}
			pstmt.close();
			rs.close();
		} catch (Exception e) {
			throw e;
		}
		return count;
	}

	public void insert(Download download) throws Exception
	{
		String sql = "insert into download (filename,downcount,uploadtime,verify,title,desccontent,filesize) " +
				"values(?,?,?,?,?,?,?)" ;
		PreparedStatement pstmt = null;
		pstmt = this.dbc.getConnection().prepareStatement(sql) ;
		pstmt.setString(1, download.getFilename()) ;
		pstmt.setInt(2, download.getDowncount()) ;
		pstmt.setDate(3, download.getUploadtime()) ;
		pstmt.setString(4, download.getVerify()) ;
		pstmt.setString(5, download.getTitle()) ;
		pstmt.setString(6, download.getDesccontent()) ;
		pstmt.setInt(7, download.getFilesize()) ;
		pstmt.executeUpdate() ;
		pstmt.close() ;
		this.dbc.close() ;

	}

	public List queryAll(int currentPage, int lineSize) throws Exception
	{
		List<Download> all = new ArrayList<Download>();
		String sql = "SELECT * FROM download limit "
				+ (currentPage - 1) * lineSize + "," + lineSize;
		PreparedStatement pstmt = null;
		try {
			pstmt = this.dbc.getConnection().prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				Download d = new Download();
				d.setId(rs.getInt(1));
				d.setFilename(rs.getString(2)) ;
				d.setDowncount(rs.getInt(3)) ;
				d.setUploadtime(rs.getDate(4)) ;
				d.setVerify(rs.getString(5)) ;
				d.setTitle(rs.getString(6)) ;
				d.setDesccontent(rs.getString(7)) ;
				d.setFilesize(rs.getInt(8)) ;
				all.add(d);
			}
			rs.close();
			pstmt.close();
		} catch (Exception e) {
			throw e;
		} finally {
			this.dbc.close();
		}
		return all;
	}

	public List queryByLike(String cond, int currentPage, int lineSize)
			throws Exception
	{
		List<Download> all = new ArrayList<Download>();
		String sql = "SELECT * FROM download WHERE title LIKE ? limit "
				+ (currentPage - 1) * lineSize + "," + lineSize;
		PreparedStatement pstmt = null;
		try {
			pstmt = this.dbc.getConnection().prepareStatement(sql);
			pstmt.setString(1, "%" + cond + "%");
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				Download d = new Download();
				d.setId(rs.getInt(1));
				d.setFilename(rs.getString(2)) ;
				d.setDowncount(rs.getInt(3)) ;
				d.setUploadtime(rs.getDate(4)) ;
				d.setVerify(rs.getString(5)) ;
				d.setTitle(rs.getString(6)) ;
				d.setDesccontent(rs.getString(7)) ;
				d.setFilesize(rs.getInt(8)) ;
				all.add(d);
			}
			rs.close();
			pstmt.close();
		} catch (Exception e) {
			throw e;
		} finally {
			this.dbc.close();
		}
		return all;
	}

	public Download selectById(int id) throws Exception
	{
		String sql = "select * from download where id=?" ;
		PreparedStatement pstmt = null;
		Download d = null ;
		pstmt = this.dbc.getConnection().prepareStatement(sql);
		pstmt.setInt(1, id) ;
		ResultSet rs = pstmt.executeQuery() ;
		if(rs.next())
		{
			d = new Download();
			d.setId(rs.getInt(1));
			d.setFilename(rs.getString(2)) ;
			d.setDowncount(rs.getInt(3)) ;
			d.setUploadtime(rs.getDate(4)) ;
			d.setVerify(rs.getString(5)) ;
			d.setTitle(rs.getString(6)) ;
			d.setDesccontent(rs.getString(7)) ;
			d.setFilesize(rs.getInt(8)) ;
		}
		rs.close() ;
		pstmt.close();
		return d ;
	}

	public void updateById(int id) throws Exception
	{
		String sql = "update download set downcount=downcount+1 where id=?" ;
		PreparedStatement pstmt = null;
		pstmt = this.dbc.getConnection().prepareStatement(sql);
		pstmt.setInt(1, id) ;
		pstmt.executeUpdate() ;
		pstmt.close();
	}

	public void deleteById(int id) throws Exception
	{
		String sql = "delete from download where id=?" ;
		PreparedStatement pstmt = null ;
		pstmt = this.dbc.getConnection().prepareStatement(sql) ;
		pstmt.setInt(1, id) ;
		pstmt.executeUpdate() ;
		pstmt.close() ;
	}

	public void updateByVerify(int id, String verify) throws Exception
	{
		String sql = "update download set verify=? where id=?" ;
		PreparedStatement pstmt = null ;
		pstmt = this.dbc.getConnection().prepareStatement(sql) ;
		pstmt.setString(1, verify) ;
		pstmt.setInt(2, id) ;
		pstmt.executeUpdate() ;
		pstmt.close() ;
	}

}