manager_index_dao.java

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

package com.bookshop.dao;
import java.util.*;

import com.bookshop.db.*;
import java.sql.*;

import com.bookshop.dto.*;

public class manager_index_dao {
	//后台管理页
	//查询所有书籍信息
	public ArrayList book_select()
	{
		Connection conn=DBConnection.getConnection();
		ResultSet rs=null;
		Statement st=null;
		ArrayList list=new ArrayList();
		String sql="select * from tb_bookinfo";
		try{
			st=conn.createStatement();
			rs=st.executeQuery(sql);
			while(rs.next())
			{
				bookinfo_dto dto=new bookinfo_dto();
				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(Float.parseFloat(rs.getString("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"));
				
				list.add(dto);
			}
		}catch(SQLException e){e.printStackTrace();}
		finally{
			try{
				rs.close();
				st.close();
				conn.close();
			}catch(SQLException e){e.printStackTrace();}
		}
		
		return list;
	}
	//按isbn查询信息
	public bookinfo_dto isbn_select(String isbn)
	{
		Connection conn=DBConnection.getConnection();
		ResultSet rs=null;
		Statement st=null;
		System.out.print("hello");
		String sql="select * from tb_bookinfo where isbn='"+isbn+"'";
		bookinfo_dto dto=new bookinfo_dto();
		try{
			st=conn.createStatement();
			rs=st.executeQuery(sql);
			
			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(Float.parseFloat(rs.getString("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();}
		finally{
			try{
				rs.close();
				st.close();
				conn.close();
			}catch(SQLException e){e.printStackTrace();}
		}
		
		return dto;
	}
	
	//修改书籍信息
	public int book_upd(bookinfo_dto dto)
	{
		Connection conn=DBConnection.getConnection();
		StringBuffer sb=new StringBuffer();
		sb.append("update tb_bookinfo set bookname=?,type=?,publisher=?,writer=?,introduce=?" 
				+",price=?,pdate=?,cover=?,newbook=?,commend=? where isbn=?");
		PreparedStatement ps=null;
		int n=0;
        System.out.print(dto.getPublisher()+"___________");
        System.out.print(dto.getIntroduce()+"___________");
		try{
			ps=conn.prepareStatement(sb.toString());
			
			ps.setString(1, dto.getBookname());
			ps.setString(2,dto.getType());
			ps.setString(3,dto.getPublisher());
			ps.setString(4,dto.getWriter());
			ps.setString(5,dto.getIntroduce());
			ps.setFloat(6,dto.getPrice());
			ps.setString(7,dto.getPdate());
			ps.setString(8,dto.getCover());
			ps.setInt(9,Integer.parseInt(dto.getNewbook()));
			ps.setInt(10,Integer.parseInt(dto.getCommend()));
			ps.setString(11,dto.getIsbn());
			
			conn.setAutoCommit(false);
			n=ps.executeUpdate();
			conn.commit();
			//System.out.print(sb);
		}catch(SQLException e){e.printStackTrace();return -1;}
		finally{
			
			try{
				
				ps.close();
				conn.close();
			}catch(SQLException e){e.printStackTrace();}
		}
		
		return n;
		
		
	}
	
	//添加新书
	public int book_insert(bookinfo_dto dto){
		Connection conn=DBConnection.getConnection();
		StringBuffer sb=new StringBuffer();
		PreparedStatement ps=null;
		sb.append("insert into tb_bookinfo (isbn,bookname,type,publisher,writer,introduce,price,pdate,cover,newbook,commend)values(?,?,?,?,?,?,?,?,?,?,?)");
		int n=0;
		
		try{
			ps=conn.prepareStatement(sb.toString());
			
			ps.setString(1,dto.getIsbn());
			ps.setString(2, dto.getBookname());
			ps.setString(3,dto.getType());
			ps.setString(4,dto.getPublisher());
			ps.setString(5,dto.getWriter());
			ps.setString(6,dto.getIntroduce());
			ps.setFloat(7,dto.getPrice());
			ps.setString(8,dto.getPdate());
			ps.setString(9,dto.getCover());
			ps.setInt(10,Integer.parseInt(dto.getNewbook()));
			ps.setInt(11,Integer.parseInt(dto.getCommend()));
			
			conn.setAutoCommit(false);
			n=ps.executeUpdate();
			conn.commit();
			
		}catch(SQLException e){e.printStackTrace();}
		finally{
			
			try{
				
				ps.close();
				conn.close();
			}catch(SQLException e){e.printStackTrace();}
		}
		return n;	
	}
	
	//删除图书
	public int book_delete(String isbn){
		Connection conn=DBConnection.getConnection();
		Statement st=null;
		int n=0;
		String sql="";
		try{
			st=conn.createStatement();
			sql="delete from tb_bookinfo where isbn='"+isbn+"'";
			n=st.executeUpdate(sql);
		}catch(SQLException e){e.printStackTrace();return -1;}
		finally{
			
			try{
				st.close();
				conn.close();
			}catch(SQLException e){e.printStackTrace();}
		}
		
		return n;
		
	}
	
//求分页最大页	
	public int maxpage(int pagesize)
	 {
	      
	      Connection conn=DBConnection.getConnection();
	      ResultSet r = null;
	      PreparedStatement pstm = null;
	      int rowcount=0;   //总记录数
	      int maxpage=0;
	     
	      
	      String countSql="select count(*) as t  from tb_bookinfo";
	     try {
	    	
	         pstm = conn.prepareStatement(countSql);
	         r = pstm.executeQuery();
	         while (r.next()) {
	             rowcount = r.getInt("t");
	           
		         if(rowcount%pagesize!=0)
		         {
		           maxpage=rowcount/pagesize+1;
		         }
		         else
		         {
		        	 maxpage=rowcount/pagesize;
		         }
	         }
	     }catch (SQLException ex) {
	            ex.printStackTrace();
	        } finally {          
	        	 try {
	        		 r.close();
	        		 pstm.close();
	                 conn.close();
	             } catch (SQLException ex1) { }                              
	        }
	      return maxpage;
	 }
	    
//求当前页码
	public ArrayList sel_book(int pagesize, int curpage) {
		 ArrayList list = new ArrayList();
	     Connection conn = DBConnection.getConnection();
	    
	     ResultSet rs = null;
	     PreparedStatement pstmt=null;
	     String selectSQL = "select * from tb_bookinfo";	     
	     int tip =pagesize * (curpage - 1);  //定位到指定的记录
	     
	     try {
	       pstmt = conn.prepareStatement(selectSQL, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
	     rs = pstmt.executeQuery();
	     System.out.print(tip);
	       if (tip <= 0) {
	           rs.beforeFirst();
	       } else {
	           if (!rs.absolute(tip)) {
	               rs.beforeFirst();
	           }
	       }
	       for (int i = 1; rs.next() && i <= pagesize; i++) {
				bookinfo_dto dto=new bookinfo_dto();
				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(Float.parseFloat(rs.getString("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"));
				
				list.add(dto);
	      }

	   
	        } catch (SQLException ex) {
	            ex.printStackTrace();
	        } finally {          
	        	 try {
	        		 rs.close();
	        		 pstmt.close();
	                 conn.close();
	             } catch (SQLException ex1) { }                              
	        }
	        return list;
			}
	
	
	
	}