bookdao.java

网上书店后台管理源码基于struts1.2+oracle数据库

package com.dongfang.dao;

import java.io.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import com.dongfang.po.Book;
import com.dongfang.po.Sort;

public class BookDAO {
	
	public String clobToString(Clob clob)
	{
		String content = "";
		if(clob!=null)
		{
			try {
				Reader reader = clob.getCharacterStream();
				BufferedReader br = new BufferedReader(reader);
				String s = "";
				while((s=br.readLine())!=null)
				{
					content +=s;
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		return content;
	}
	public String formatDate (String sdate) {
		sdate = sdate.substring(0,19);
		return sdate;
	}
	//查询所有的书刊类别
	public ArrayList getSorts()
	{
		ArrayList al = new ArrayList();
		Connection conn = null;
		Statement stm = null;
		ResultSet rs = null;
		conn = Tools.getConn();
		try {
			stm = conn.createStatement();
			rs = stm.executeQuery("select * from sorts");
			while(rs.next())
			{
				Sort sort = new Sort();
				sort.setId(rs.getInt("id"));
				sort.setName(rs.getString("name"));
				al.add(sort);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
				try {
					if(rs!=null)
						rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

				try {
					if(stm!=null)
						stm.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

				try {
					if(conn!=null)
						conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return al;
	}
	//获得最流行的几本书
	public ArrayList getTopBooks(int type)
	{
		
		ArrayList al = new ArrayList();
		Connection conn = null;
		Statement stm = null;
		ResultSet rs = null;
		conn = Tools.getConn();
		String sql = null;
		int disNum = 0;//最前面的几行
		if(type==1)
		{
			disNum = 5;
		    sql = "select * from (select * from book order by saleDate desc) where rownum< "+disNum+"";	
		}
		else
		{
			disNum = 11;
			sql = "select * from (select * from book order by salecount desc) where rownum< "+disNum+"";
		}
		try {
			stm = conn.createStatement();
			rs = stm.executeQuery(sql);
			while(rs.next())
			{
				Book book = new Book();
				book.setId(rs.getInt("id"));
				book.setSortid(rs.getInt("sortid"));
				book.setName(rs.getString("name"));
				book.setPrice(rs.getDouble("price"));
				book.setSaleprice(rs.getDouble("saleprice"));
				book.setDescript(clobToString(rs.getClob("descript")));
				book.setContents(clobToString(rs.getClob("contents")));
				book.setSaledate(rs.getString("saledate"));
				book.setSalecount(rs.getInt("salecount"));
				book.setWriter(rs.getString("writer"));
				book.setPublish(rs.getString("publish"));
				book.setImage(rs.getString("image"));
				al.add(book);
			}
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
				try {
					if(rs!=null)
						rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

				try {
					if(stm!=null)
						stm.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

				try {
					if(conn!=null)
						conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return al ;
	}
	//获得一本书的具体内容
	public Book getBookById(int bookId)
	{
		Book book = null;
		Connection conn = null;
		Statement stm = null;
		ResultSet rs = null;
		conn = Tools.getConn();
		try {
			stm = conn.createStatement();
			rs = stm.executeQuery("select * from book where id="+bookId+"");
			
			while(rs.next())
			{
			    book = new Book();
				book.setId(rs.getInt("id"));
				book.setName(rs.getString("name"));
				book.setSortid(rs.getInt("sortId"));
				book.setPrice(rs.getDouble("price"));
				book.setSaleprice(rs.getDouble("salePrice"));
				book.setDescript(clobToString(rs.getClob("descript")));
				book.setContents(clobToString(rs.getClob("contents")));
				book.setSaledate(rs.getString("saledate"));
				book.setSalecount(rs.getInt("salecount"));
				book.setWriter(rs.getString("writer"));
				book.setPublish(rs.getString("publish"));
				book.setImage(rs.getString("image"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
				try {
					if(rs!=null)
						rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

				try {
					if(stm!=null)
						stm.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

				try {
					if(conn!=null)
						conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return book;
	}
	//根据sortid来查询
    public List getMatchBookBySortID(int sortId)
    {
    	List list = new ArrayList();
    	Connection conn = null;
		Statement stm = null;
		ResultSet rs = null;
		try {
			conn = Tools.getConn();
			stm = conn.createStatement();
			rs = stm.executeQuery("select * from book where sortid="+sortId+"");
			while(rs.next())
			{
				Book book = new Book();
				book.setId(rs.getInt("id"));
				book.setName(rs.getString("name"));
				book.setSortid(rs.getInt("sortid"));
				book.setPrice(rs.getDouble("price"));
				book.setSaleprice(rs.getDouble("saleprice"));
				book.setContents(clobToString(rs.getClob("contents")));
				book.setDescript(clobToString(rs.getClob("descript")));
				book.setSaledate(rs.getString("saledate"));
				book.setSalecount(rs.getInt("salecount"));
				book.setWriter(rs.getString("writer"));
				book.setPublish(rs.getString("publish"));
				book.setImage(rs.getString("image"));
				list.add(book);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
				try {
					if(rs!=null)
						rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

				try {
					if(stm!=null)
						stm.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

				try {
					if(conn!=null)
						conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
    	return list;
    }
//  根据sortid来查询
    public List getMatchBookByKeyword(String Keyword)
    {
    	List list = new ArrayList();
    	Connection conn = null;
		Statement stm = null;
		ResultSet rs = null;
		String sql ="";
		
		
		sql = "select * from book where name like '%"+Keyword+"%'";
		
		conn = Tools.getConn();
		try {
			stm = conn.createStatement();
			rs = stm.executeQuery(sql);
			while(rs.next())
			{
				Book book = new Book();
				book.setId(rs.getInt("id"));
				book.setName(rs.getString("name"));
				book.setSortid(rs.getInt("sortid"));
				book.setPrice(rs.getDouble("price"));
				book.setSaleprice(rs.getDouble("saleprice"));
				book.setContents(clobToString(rs.getClob("contents")));
				book.setDescript(clobToString(rs.getClob("descript")));
				book.setSaledate(rs.getString("saledate"));
				book.setSalecount(rs.getInt("salecount"));
				book.setWriter(rs.getString("writer"));
				book.setPublish(rs.getString("publish"));
				book.setImage(rs.getString("image"));
				list.add(book);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
				try {
					if(rs!=null)
						rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

				try {
					if(stm!=null)
						stm.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

				try {
					if(conn!=null)
						conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
    	return list;
    	//更新一条记录
    	
    }
    public boolean updateOneBook(Book book)
	{
		
		
    	boolean isUpdate = false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		conn =Tools.getConn();
		String sql ="update book set id=?,sortid=?,name=?,price=?,saleprice=?,descript=?,contents=?,saledate=sysdate,salecount=?,image=? where id=?";
		try {
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, book.getId());
			pstmt.setInt(2, book.getSortid());
			pstmt.setString(3, book.getName());
			pstmt.setDouble(4, book.getPrice());
			pstmt.setDouble(5, book.getSaleprice());
			pstmt.setString(6, book.getDescript());
			pstmt.setString(7,book.getContents());
			//System.out.println(book.getSalecount());
			//pstmt.setString(8, book.getSaledate());
			pstmt.setInt(8, book.getSalecount());
			pstmt.setString(9, book.getImage());
			pstmt.setInt(10, book.getId());
			int temp = pstmt.executeUpdate();
			if(temp>0)
			{
				isUpdate = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
		finally
		{
			try {
				if(pstmt!=null)
					pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

			try {
				if(conn!=null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return isUpdate;
	}
}