mealdaoimpl.java

JavaEE订餐项目的功能：用户登录、用户注册、添加订餐信息、查询订餐、结算、权限验证

package com.amaker.dao.impl;

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.amaker.bean.Meal;
import com.amaker.bean.MealBean;
import com.amaker.bean.MealType;
import com.amaker.dao.MealDao;
import com.amaker.util.ConnectionUtil;

public class MealDaoImpl implements MealDao {

	public List listMeal() {
		String sql = "select m.id, "+
					       " m.`orderTime`, "+
					       " sm.`fullName`, "+
					       " mt.`name`, "+
					       " mt.price, "+
					       " m.num, "+
					       " (mt.price*m.num) as total, "+
					       " m.detail, "+
					       " m.isPay "+
					       " from MealTbl as m "+
					       " left join MealTypeTbl as mt "+
					       " on m.mealTypeID = mt.id "+
					       " left join SystemMemberTbl as sm "+
					       " on sm.id = m.userID";
		
		Connection conn = new ConnectionUtil().getConnection();
		

		try {
			Statement stmt = conn.createStatement();

			ResultSet rs = stmt.executeQuery(sql);

			List list = new ArrayList();

			while (rs.next()) {
				
				int id = rs.getInt(1);
				String orderTime = rs.getString(2);
				String fullName =rs.getString(3);
				String mealTypeName =rs.getString(4);
				int price = rs.getInt(5);
				int num  = rs.getInt(6);
				int total = rs.getInt(7);
				
				String detail = rs.getString(8);
				
				int pay = rs.getInt(9);
				
				MealBean mb = new MealBean();
				
				mb.setDetail(detail);
				mb.setFullName(fullName);
				mb.setId(id);
				mb.setMealTypeName(mealTypeName);
				mb.setOrderTime(orderTime);
				mb.setNum(num);
				mb.setPrice(price);
				mb.setTotal(total);
				
				mb.setPay(pay);
				
				
				list.add(mb);
				
			}
			return list;
		} catch (SQLException e) {

			e.printStackTrace();
		}

		return null;
	}

	public List listMealType() {

		Connection conn = new ConnectionUtil().getConnection();
		String sql = " select id,name from MealTypeTbl";

		try {
			Statement stmt = conn.createStatement();

			ResultSet rs = stmt.executeQuery(sql);

			List list = new ArrayList();

			while (rs.next()) {
				int id = rs.getInt(1);
				String name = rs.getString(2);
				MealType mt = new MealType();
				mt.setId(id);
				mt.setName(name);

				list.add(mt);
			}
			return list;
		} catch (SQLException e) {

			e.printStackTrace();
		}

		return null;
	}

	public void save(Meal m) {

		/*
		 * CREATE TABLE `mealtbl` ( `id` int(11) NOT NULL auto_increment,
		 * `orderTime` varchar(20) default NULL, `userID` int(11) default NULL,
		 * `mealTypeID` int(11) default NULL, `num` int(11) default NULL,
		 * `isPay` int(11) default '0', `detail` varchar(200) default NULL,
		 * PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
		 */
		Connection conn = new ConnectionUtil().getConnection();
		String sql = " insert into MealTbl(orderTime,userID,mealTypeID,num,detail) values(?,?,?,?,?) ";

		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, m.getOrderTime());
			pstmt.setInt(2, m.getUserID());
			pstmt.setInt(3, m.getMealTypeID());
			pstmt.setInt(4, m.getNum());
			pstmt.setString(5, m.getDetail());
			pstmt.executeUpdate();

		} catch (SQLException e) {

			e.printStackTrace();
		}

	}

	public String getTime() {
		String sql = "select sysdate() from dual " ;
		
		Connection conn = new ConnectionUtil().getConnection();
		

		try {
			Statement stmt = conn.createStatement();

			ResultSet rs = stmt.executeQuery(sql);
			if(rs.next()){
				String time = rs.getString(1);
				return time;
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public void update(String[] ids) {
		Connection conn = new ConnectionUtil().getConnection();
		
		String sql = " update MealTbl set isPay=1 where id=?";
		
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			if(ids!=null&&ids.length>0)
			for (int i = 0; i < ids.length; i++) {
				pstmt.setInt(1, new Integer(ids[i]));
				pstmt.executeUpdate();
			}			
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		
	}

}