orderdao.java

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

package com.dongfang.dao;

import java.io.*;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.dongfang.po.Order;

public class OrderDAO {
	//
	public String dateFormat(String date)
	{
		String sdate = "";
		sdate = date.substring(0, 19);
		return sdate;
	}
	public String clobToString(Clob clob)
	{
		String content = "";
		try {
			Reader reader = clob.getCharacterStream();
			BufferedReader bf = new BufferedReader(reader);
			String s ="";
			try {
				while((s=bf.readLine())!=null)
				{
					content += s;
				}
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return content;
	}
	public int getNextId()
	{
		int myID = 0;
		Connection conn = null;
		Statement stm = null;
		ResultSet rs = null;
		conn = Tools.getConn();
		try {
			stm = conn.createStatement();
			rs = stm.executeQuery("select max(id) id from orders");
			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;
	}
	//保存一个订单
	public boolean saveOrder(Order order)
	{
		boolean isSaved = false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		conn = Tools.getConn();
		String sql = "insert into orders values(?,?,?,?,?,?,?,?,?,?,?,to_date(?,'YYYY-MM-DD HH:MI:SS AM'),?)";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, order.getId());
			pstmt.setString(2, order.getOrderno());
			pstmt.setInt(3, order.getUserid());
			pstmt.setString(4, order.getRealname());
			pstmt.setString(5, order.getAddress());
			pstmt.setString(6, order.getZip());
			pstmt.setString(7, order.getTel());
			pstmt.setString(8, order.getPayment());
			pstmt.setString(9, order.getEmail());
			pstmt.setString(10, order.getMemo());
			
			//System.out.print("price "+order.getTime());
			pstmt.setDouble(11, order.getPrice());
			pstmt.setString(12,order.getTime());
			pstmt.setInt(13, order.getTag());
			//pstmt.setInt(14, order.getSalecount());
			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;
	}
	//根据userid来查询order
	public List getOrderByUserId(int userId)
	{
		List list = new ArrayList();
		Connection conn = null;
		Statement stm = null;
		ResultSet rs = null;
		conn = Tools.getConn();
		try {
			stm = conn.createStatement();
			rs = stm.executeQuery("select * from orders where userid="+userId+"");
			while(rs.next())
			{
				Order 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(dateFormat(rs.getString("time")));
				
				order.setUserid(rs.getInt("userid"));
				order.setZip(rs.getString("zip"));
				list.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 list;
	}
	public boolean deleteOrder(String orderno)
	{
		boolean isDelete =false;
		Connection conn = null;
		Statement stm = null;
		conn = Tools.getConn();
		try {
			
			stm = conn.createStatement();
			int temp = stm.executeUpdate("delete orders where orderno='"+orderno+"'");
			if(temp>0)
			{
				isDelete =true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
				
				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 isDelete;
		
	}
}