bookdao.java

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

package com.dongfang.dao;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.Reader;
import java.sql.*;
import java.util.*;

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

public class BookDAO {
	public String dateFormat(String date)
	{
		String sdate = "";
		sdate = date.substring(0, 19);
		return sdate;
	}
	public String clobToString(Clob clob)
	{
		String contents = "";
		try {
			Reader reader = clob.getCharacterStream();
			String buffer = "";
			BufferedReader br = new BufferedReader(reader);
			try {
				while((buffer=br.readLine())!=null)
				{
					contents +=buffer;
				}
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return contents;
	}
	//添加一本新书
	public boolean save(Book book)
	{
		boolean isSaved =false;
		Connection conn =null;
		PreparedStatement pstmt = null;
		String sql = "insert into book values(?,?,?,?,?,?,?,to_date(?,'YYYY-MM-DD HH:MI:SS AM'),?,?,?,?)";
		conn = DBTools.getConn();
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, book.getId());
			pstmt.setInt(2, book.getSort().getId());
			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());
			pstmt.setString(8, book.getSaledate());
			
			pstmt.setInt(9, book.getSalecount());
			pstmt.setString(10, book.getImage());
			pstmt.setString(11, book.getWriter());
			pstmt.setString(12, book.getPublish());
			int temp = pstmt.executeUpdate();
			if(temp>0)
			{
				isSaved = 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 isSaved;
	}
	//自动改变id
	public int getNextId()
	{
		int myId = 0;
		Connection conn =null;
		Statement stm = null;
		ResultSet rs = null;
		String sql = "select max(id) id from book";
		conn = DBTools.getConn();
		try {
			stm = conn.createStatement();
			rs = stm.executeQuery(sql);
			if(rs.next())
			{
				myId = rs.getInt("id");
			}
		} 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 ++myId;
	}
	//根据keyword来查询book
	public List getBookBykeyword(String keyword)
	{
		//System.out.println(keyword);
		List bookList = new ArrayList();
		Connection conn = null;
		Statement stm = null;
		ResultSet rs = null;
		conn = DBTools.getConn();
		
		SortDAO sortDAO = new SortDAO();
		String sql1 = "";
		if(keyword.equals(""))
		{
			sql1 = "select * from book";
		}
		else
		{
			sql1 = "select * from book where name like'%"+keyword+"%'";
		}
		try {
			stm = conn.createStatement();
			rs = stm.executeQuery(sql1);
			while(rs.next())
			{
				Book book = new Book();
				book.setId(rs.getInt("id"));
				int sortId = rs.getInt("sortId");
				Sort sort = sortDAO.getSortById(sortId);
				book.setSort(sort);
				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(dateFormat(rs.getString("saledate")));
				book.setImage(rs.getString("image"));
				book.setWriter(rs.getString("writer"));
				book.setPublish(rs.getString("publish"));

				//System.out.println(dateFormat(rs.getString("saledate")));
				//System.out.println(rs.getString("saledate"));
				bookList.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 bookList;
	}

	public Book getBookById(int id)
	{
		Book book = null;
		Connection conn = null;
		Statement stm = null;
		ResultSet rs = null;
		conn = DBTools.getConn();
		try {
			stm = conn.createStatement();
			rs = stm.executeQuery("select * from book where id="+id+"");
			if(rs.next())
			{
				book = new Book();
				book.setId(rs.getInt("id"));
				int sortId = rs.getInt("sortid");
				SortDAO sortdao = new SortDAO();
				Sort sort = sortdao.getSortById(sortId);
				book.setSort(sort);
				book.setName(rs.getString("name"));
				book.setPrice(rs.getDouble("price"));
				book.setSaleprice(rs.getDouble("saleprice"));
				book.setSalecount(rs.getInt("salecount"));
				book.setDescript(clobToString(rs.getClob("descript")));
				book.setContents(clobToString(rs.getClob("contents")));
				book.setSaledate(dateFormat(rs.getString("saledate")));
				book.setImage(rs.getString("image"));
				book.setWriter(rs.getString("writer"));
				book.setPublish(rs.getString("publish"));
				
			}
		} 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;
	}
//	删除一个book
	public boolean delOneBook(Book book)
	{
		boolean isDel = false;
		Connection conn = null;
		Statement stm = null;
		ResultSet rs = null;
		conn = DBTools.getConn();
		String sql = "delete book where id="+book.getId()+"";
        try {
			stm = conn.createStatement();
			int temp = stm.executeUpdate(sql);
			if(temp>0)
			{
				isDel = true;
			}
			
		} 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 isDel;
	}
	//更新一本书
	public void updateBook(Book book)
	{
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		conn = DBTools.getConn();
		String sql = "update book set id=?,sortid=?,name=?,price=?,saleprice=?," +
				"descript=?,contents=?,saledate=to_date(?,'YYYY-MM-DD HH:MI:SS AM'),salecount=?,image=? " +
				"writer=?,publish=? where id=?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, book.getId());
			pstmt.setInt(2, book.getSort().getId());
			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());
			pstmt.setString(8, book.getSaledate());
			pstmt.setInt(9, book.getSalecount());
			pstmt.setString(10, book.getImage());
			pstmt.setString(11, book.getWriter());
			pstmt.setString(12, book.getPublish());
			pstmt.setInt(13, book.getId());
			pstmt.executeUpdate();
		} 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();
				}
		}
		
	}
}