bookinfo_dao.java

这是一个Struts网上商城(图书)的管理系统源代码.

package com.bookshop.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import com.bookshop.dto.bookinfo_dto;
import com.bookshop.dto.member_dto;
import com.bookshop.db.*;

//首页显示6条信息
public class bookinfo_dao {
	public ArrayList book_select()
	{
		ArrayList list = new ArrayList();
		Connection conn =null;
		conn=DBConnection.getConnection();
		Statement st = null;
		ResultSet rs = null;
		String strSQL=null;
		
		try {
			st = conn.createStatement();
			strSQL="SELECT top 6 * FROM tb_bookinfo";
			rs = st.executeQuery(strSQL);
			while (rs.next()) {
				bookinfo_dto newInfo=new bookinfo_dto();
				newInfo.setIsbn(rs.getString("isbn"));
				newInfo.setBookname(rs.getString("bookname"));
				newInfo.setType(rs.getString("type"));
				newInfo.setPublisher(rs.getString("publisher"));
				newInfo.setWriter(rs.getString("writer"));
				newInfo.setIntroduce(rs.getString("introduce"));
				newInfo.setPrice(rs.getFloat("price"));
				newInfo.setPdate(rs.getString("pdate"));
				newInfo.setCover(rs.getString("cover"));
				newInfo.setIntime(rs.getString("intime"));
				newInfo.setNewbook(rs.getString("newbook"));
				newInfo.setCommend(rs.getString("commend"));
				
				list.add(newInfo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			
		}
		finally
		{
    	 try {
    	        rs.close();
    	        st.close();
    	        conn.close();
    	    } catch (SQLException ex1) {
    	        ex1.printStackTrace();
    	    }    	
	    }				
		return list;
	}
//新书上架
	public ArrayList newbook()
	{
		ArrayList list = new ArrayList();
		Connection conn =null;
		conn=DBConnection.getConnection();
		Statement st = null;
		ResultSet rs = null;
		String strSQL=null;
		
		try {
			st = conn.createStatement();
			strSQL="SELECT * FROM tb_bookinfo where newbook=1";
			rs = st.executeQuery(strSQL);
			while (rs.next()) {
				bookinfo_dto newInfo=new bookinfo_dto();
				newInfo.setIsbn(rs.getString("isbn"));
				newInfo.setBookname(rs.getString("bookname"));
				newInfo.setPublisher(rs.getString("publisher"));

				
				list.add(newInfo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.print(strSQL);
		}
		finally
		{
    	 try {
    	        rs.close();
    	        st.close();
    	        conn.close();
    	    } catch (SQLException ex1) {
    	        ex1.printStackTrace();
    	    }    	
	    }				
		return list;
		
		
	}
	//详细信息查询
	public bookinfo_dto book_sel(String isbn){
		Connection conn =null;
		conn=DBConnection.getConnection();
		Statement st = null;
		ResultSet rs = null;
		String strSQL=null;
		bookinfo_dto dto=new bookinfo_dto();
		try {
			st = conn.createStatement();
			strSQL="SELECT * FROM tb_bookinfo where isbn='"+isbn+"'";
			rs = st.executeQuery(strSQL);
			System.out.print(strSQL);
			if(rs.next()) {
				
				dto.setIsbn(rs.getString("isbn"));
				dto.setBookname(rs.getString("bookname"));
				dto.setType(rs.getString("type"));
				dto.setPublisher(rs.getString("publisher"));
				dto.setWriter(rs.getString("writer"));
				dto.setIntroduce(rs.getString("introduce"));
				dto.setPrice(rs.getFloat("price"));
				dto.setPdate(rs.getString("pdate"));
				dto.setCover(rs.getString("cover"));
				dto.setIntime(rs.getString("intime"));
				dto.setNewbook(rs.getString("newbook"));
				dto.setCommend(rs.getString("commend"));

				
			}
		} catch (SQLException e) {
			e.printStackTrace();
			//System.out.print(strSQL);
		}
		finally
		{
    	 try {
    	        rs.close();
    	        st.close();
    	        conn.close();
    	    } catch (SQLException ex1) {
    	        ex1.printStackTrace();
    	    }    	
	    }				
		return dto;
		
	}
	//图书分类
	public ArrayList booksort()
	{
		Connection conn =null;
		conn=DBConnection.getConnection();
		Statement st = null;
		ResultSet rs = null;
		String strSQL=null;
		ArrayList list=new ArrayList();
		
		try {
			st = conn.createStatement();
			strSQL="select  type from tb_bookinfo group by type";
			rs = st.executeQuery(strSQL);
			System.out.print(strSQL);
			while (rs.next()) {
				bookinfo_dto dto=new bookinfo_dto();
				//dto.setIsbn(rs.getString("isbn"));
				//dto.setBookname(rs.getString("bookname"));
				dto.setType(rs.getString("type"));
				list.add(dto);				
			}
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.print(strSQL);
		}
		finally
		{
    	 try {
    	        rs.close();
    	        st.close();
    	        conn.close();
    	    } catch (SQLException ex1) {
    	        ex1.printStackTrace();
    	    }    	
	    }				
		return list;
		
	}
	
	//分类查询所有信息
	public ArrayList book(String type)
	{
		Connection conn =null;
		conn=DBConnection.getConnection();
		Statement st = null;
		ResultSet rs = null;
		String strSQL=null;
		ArrayList list=new ArrayList();
		
		try {
			st = conn.createStatement();
			strSQL="select  * from tb_bookinfo where type='"+type+"'";
			rs = st.executeQuery(strSQL);
			System.out.print(strSQL);
			while (rs.next()) {
				bookinfo_dto dto=new bookinfo_dto();
				dto.setIsbn(rs.getString("isbn"));
				dto.setBookname(rs.getString("bookname"));
				dto.setPublisher(rs.getString("publisher"));
				list.add(dto);				
			}
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.print(strSQL);
		}
		finally
		{
    	 try {
    	        rs.close();
    	        st.close();
    	        conn.close();
    	    } catch (SQLException ex1) {
    	        ex1.printStackTrace();
    	    }    	
	    }				
		return list;
		
	}
	//执行搜索操作
	public ArrayList search(String book)
	{
		Connection conn =null;
		conn=DBConnection.getConnection();
		Statement st = null;
		ResultSet rs = null;
		String strSQL=null;
		ArrayList list=new ArrayList();
		
		try {
			st = conn.createStatement();
			strSQL="select  * from tb_bookinfo where bookname like '"+book+"%'";
			rs = st.executeQuery(strSQL);
			System.out.print(strSQL);
			while (rs.next()) {
				bookinfo_dto dto=new bookinfo_dto();
				dto.setIsbn(rs.getString("isbn"));
				dto.setBookname(rs.getString("bookname"));
				dto.setPublisher(rs.getString("publisher"));
				list.add(dto);				
			}
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.print(strSQL);
		}
		finally
		{
    	 try {
    	        rs.close();
    	        st.close();
    	        conn.close();
    	    } catch (SQLException ex1) {
    	        ex1.printStackTrace();
    	    }    	
	    }				
		return list;
		
	}
	
	//销售排行
	public ArrayList sort()
	{
		ArrayList list = new ArrayList();
		Connection conn =null;
		conn=DBConnection.getConnection();
		Statement st = null;
		ResultSet rs = null;
		String strSQL=null;
		
		try {
			st = conn.createStatement();
			strSQL="select * from (select top 10 * from (select count(*) as n,isbn from tb_order_detail group by isbn)  as tab order by n desc) o join tb_bookinfo as b on o.isbn=b.isbn";
			rs = st.executeQuery(strSQL);
			while (rs.next()) {
				bookinfo_dto newInfo=new bookinfo_dto();
				newInfo.setIsbn(rs.getString("isbn"));
				newInfo.setBookname(rs.getString("bookname"));
				newInfo.setPublisher(rs.getString("publisher"));

				
				list.add(newInfo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.print(strSQL);
		}
		finally
		{
    	 try {
    	        rs.close();
    	        st.close();
    	        conn.close();
    	    } catch (SQLException ex1) {
    	        ex1.printStackTrace();
    	    }    	
	    }				
		return list;
		
		
	}
	
}