order_dao.java

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

package com.bookshop.dao;
import java.util.*;
import com.bookshop.db.*;
import java.sql.*;
import com.bookshop.dto.*;
public class order_dao {
	//用户定单查询
	public ArrayList order_sel(String username)
	{
		Connection conn=DBConnection.getConnection();
		Statement st=null;
		ResultSet rs=null;
		ArrayList list=new ArrayList();
		String sql="";
		try{
			sql="select * from tb_order where username='"+username+"'";
			System.out.println(sql);
			st=conn.createStatement();
			rs=st.executeQuery(sql);
			while(rs.next()){
				order_dto dto=new order_dto();
				dto.setOrderid(rs.getString("orderid"));
				dto.setBnumber(rs.getString("bnumber"));
				dto.setTruename(rs.getString("truename"));
				dto.setPay(rs.getString("pay"));
				dto.setCarry(rs.getString("carry"));
				dto.setRebate(rs.getString("rebate"));
				dto.setOrderdate(rs.getString("orderdate"));
				list.add(dto);
			}
			
		}catch(SQLException e){e.printStackTrace();}
		
		finally{
			try{
				rs.close();
				st.close();
				conn.close();
			}catch(SQLException ex){ex.printStackTrace();}
		}
		return list;
	}
	
	//详细用户信息查询
	public order_dto par_sel(String orderid)
	{
		Connection conn=DBConnection.getConnection();
		Statement st=null;
		ResultSet rs=null;
		ArrayList list=new ArrayList();
		String sql="";
		order_dto dto=new order_dto();
		try{
			sql="select * from tb_order where orderid='"+orderid+"'";
			st=conn.createStatement();
			rs=st.executeQuery(sql);
			if(rs.next()){
				
				dto.setOrderid(rs.getString("orderid"));
				dto.setAddress(rs.getString("address"));
				dto.setPostcode(rs.getString("postcode"));
				dto.setTel(rs.getString("tel"));
				dto.setEmail(rs.getString("email"));
				dto.setBz(rs.getString("bz"));
		
				
			}
			System.out.print(sql);
		}catch(SQLException e){e.printStackTrace();}
		
		finally{
			try{
				rs.close();
				st.close();
				conn.close();
			}catch(SQLException ex){ex.printStackTrace();}
		}
		return dto;
	}
	
  //定单号详细内容查询	
	public ArrayList probe_sel(String orderid)
	{
		Connection conn=DBConnection.getConnection();
		Statement st=null;
		ResultSet rs=null;
		ArrayList list=new ArrayList();
		String sql="";
		try{
			sql="select * from tb_bookinfo as a,tb_order_detail as b where a.isbn = b.isbn and b.orderid='"+orderid+"'";
			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.setPublisher(rs.getString("publisher"));
				dto.setPrice(rs.getFloat("price"));
				dto.setNumber(rs.getString("number"));

				list.add(dto);
			}
			
		}catch(SQLException e){e.printStackTrace();}
		
		finally{
			try{
				rs.close();
				st.close();
				conn.close();
			}catch(SQLException ex){ex.printStackTrace();}
		}
		return list;
	}
	//生成新定单
	public int order_add(order_dto order)
	{
		Connection conn=DBConnection.getConnection();
		StringBuffer sb=new StringBuffer();
		sb.append("insert into tb_order (username,truename,address,postcode,tel,pay,carry,email,bz,rebate,bnumber)values(?,?,?,?,?,?,?,?,?,?,?)");
		PreparedStatement ps=null;
		int n=0;
		try{
			ps=conn.prepareStatement(sb.toString());
			ps.setString(1, order.getUsername());
			ps.setString(2,order.getTruename());
			ps.setString(3,order.getAddress());
			ps.setString(4, order.getPostcode());
			ps.setString(5, order.getTel());
			ps.setString(6,order.getPay());
			ps.setString(7, order.getCarry());
			ps.setString(8, order.getEmail());
			ps.setString(9,order.getBz());
			ps.setString(10, "1");
			ps.setString(11, order.getBnumber());

			
			conn.setAutoCommit(false);
			n=ps.executeUpdate();
			conn.commit();
			
		}catch(SQLException e){e.printStackTrace();}
		finally{
			try{
				
				ps.close();
				conn.close();
			//	System.out.print(sb);
			}catch(SQLException ex){ex.printStackTrace();}
		}
		

		return n;
	}
	public order_dto orderid()
	{
		
		//读取刚生成的定单号
		Connection conn=DBConnection.getConnection();
		String sql="select top 1 * from tb_order order by orderid desc";
		ResultSet rs=null;
		Statement st=null;
		order_dto dto=new order_dto();
		try{
		     st=conn.createStatement();
		     rs=st.executeQuery(sql);
		     if(rs.next()){
		        
		        dto.setOrderid(rs.getString("orderid"));
		     
		     }
		}catch(SQLException e){e.printStackTrace();}
		finally{
			
			try{
				
				rs.close();
				st.close();
				conn.close();
			}catch(SQLException e){e.printStackTrace();}
		}
		//System.out.print(end);
		return dto;
	}
	
}