orderdao.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.Order;


public class OrderDAO {
	
	public String clobToString(Clob clob){
		String content = "";
		if (clob!=null) {
		try {
			Reader read = clob.getCharacterStream();
			BufferedReader br = new BufferedReader(read);
			String s = null;
			while((s = br.readLine())!=null){
				content += s;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		}
		return content;
	}
	public String dateFormat(String date)
	{
		String sdate = "";
		sdate = date.substring(0, 19);
		return sdate;
	}
	//查询所有的订单
	
	public List getAllOrders(String keyword)
	{
		List orderList = new ArrayList();
		Connection conn =null;
		Statement stm = null;
		ResultSet rs = null;
		String sql = null;
		if(keyword.equalsIgnoreCase(""))
		{	
			 sql= "select * from orders where tag=0";
		}
		else 
		{
			sql = "select * from orders where tag=0 and orderno like'%"+keyword+"%'";
		}
		conn = DBTools.getConn();
		try {
			stm = conn.createStatement();
			rs = stm.executeQuery(sql);
			while(rs.next())
			{
				Order order = new Order();
				order.setId(rs.getInt("id"));
				order.setOrderno(rs.getString("orderno"));
				//System.out.println(rs.getString("orderno"));
				order.setUserid(rs.getInt("userid"));
				order.setRealname(rs.getString("realname"));
				order.setAddress(rs.getString("address"));
				order.setZip(rs.getString("zip"));
				order.setTel(rs.getString("tel"));
				order.setMemo(rs.getString("memo"));
				order.setEmail(rs.getString("email"));
				order.setPayment(rs.getString("payment"));
				order.setPrice(rs.getDouble("price"));
				order.setTag(rs.getInt("id"));
				order.setTime(dateFormat(rs.getString("time")));
				orderList.add(order);
			}
		} 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 orderList;
	}
	public List getProcessedOrders(String keyword)
	{
		List orderList = new ArrayList();
		Connection conn =null;
		Statement stm = null;
		ResultSet rs = null;
		String sql = null;
		if(keyword.equalsIgnoreCase(""))
		{	
			 sql= "select * from orders where tag=1";
		}
		else 
		{
			sql = "select * from orders where tag=1 and orderno like'%"+keyword+"%'";
		}
		conn = DBTools.getConn();
		try {
			stm = conn.createStatement();
			rs = stm.executeQuery(sql);
			while(rs.next())
			{
				Order order = new Order();
				order.setId(rs.getInt("id"));
				order.setOrderno(rs.getString("orderno"));
				//System.out.println(rs.getString("orderno"));
				order.setUserid(rs.getInt("userid"));
				order.setRealname(rs.getString("realname"));
				order.setAddress(rs.getString("address"));
				order.setZip(rs.getString("zip"));
				order.setTel(rs.getString("tel"));
				order.setMemo(rs.getString("memo"));
				order.setEmail(rs.getString("email"));
				order.setPayment(rs.getString("payment"));
				order.setPrice(rs.getDouble("price"));
				order.setTag(rs.getInt("id"));
				order.setTime(dateFormat(rs.getString("time")));
				orderList.add(order);
			}
		} 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 orderList;
	}
	public void updateOrder(Order order)
	{
		List orderList = new ArrayList();
		Connection conn =null;
		PreparedStatement pstmt = null;
		String sql = "update orders set orderno=?,userid=?,realname=?,address=?," +
		"zip=?,tel=?,payment=?,email=?,memo=?,price=?,time=to_date(?,'YYYY-MM-DD HH:M1:SS AM'),tag=? " +
		"where id=?";
		conn = DBTools.getConn();
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,order.getOrderno());
			pstmt.setInt(2,order.getUserid());
			pstmt.setString(3,order.getRealname());
			pstmt.setString(4,order.getAddress());
			pstmt.setString(5,order.getZip());
			pstmt.setString(6,order.getTel());
			pstmt.setString(7,order.getPayment());
			pstmt.setString(8,order.getEmail());
			pstmt.setString(9,order.getMemo());
			pstmt.setDouble(10,order.getPrice());
			
			pstmt.setInt(11,order.getTag());
			pstmt.setInt(12,order.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.isClosed()){
					conn.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}		
		
		}
			
	}
	
	public Order getOrder(int orderId) {
		String sql = "select * from orders where id="+orderId;
		Order order = null;
		Connection conn = DBTools.getConn();
		Statement stmt = null;
		ResultSet rs = null;
		
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			
			if (rs.next()) {
				order = new Order();
				order.setAddress(rs.getString("address"));
				order.setEmail(rs.getString("email"));
				order.setId(rs.getInt("id"));
				order.setMemo(clobToString(rs.getClob("memo")));
				order.setOrderno(rs.getString("orderno"));
				order.setPayment(rs.getString("payment"));
				order.setPrice(rs.getDouble("price"));
				order.setRealname(rs.getString("realname"));
				order.setTag(rs.getInt("tag"));
				order.setTel(rs.getString("tel"));
				order.setTime(rs.getString("time"));
				order.setUserid(rs.getInt("userid"));
				order.setZip(rs.getString("zip"));
			}
		} 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 (stmt!=null){
					stmt.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			try {
				if (conn!=null&&!conn.isClosed()){
					conn.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}		
		}
		return order;
	}
}