goodsorderdao.java

整个系统采用MVC开发模式

package bean;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;



public class GoodsOrderDao {
	// 取得一个数据库连接
	public Connection getConnection()throws SQLException,InstantiationException,IllegalAccessException,
	       ClassNotFoundException{
		
		Connection conn = null;
		Class.forName("com.mysql.jdbc.Driver").newInstance();
//		String url = "jdbc.mysql://192.168.2.164:3306/shopping";
//		String user = "D6";
//		String password = "123";
		String url = "jdbc:mysql://localhost/shopping?user=admin&password=admin";
		conn = DriverManager.getConnection(url);
		return conn;
		
	}
	// 根据传入的SQL语句，返回一个对象的链表
	public ArrayList orderSelect(String sql)throws Exception{
		ArrayList<GoodsOrder> result = new ArrayList<GoodsOrder>();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			conn = getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				GoodsOrder order = new GoodsOrder();
				order.setCost(rs.getDouble("cost"));
				order.setHowtopay(rs.getString("howtopay"));
				order.setOrdernumber(rs.getInt("ordernumber"));
				order.setOrdertime(rs.getString("ordertime"));
				order.setProduct(rs.getString("product"));
				order.setReceiver(rs.getString("receiver"));
				order.setState(rs.getString("state"));
				order.setUsername(rs.getString("username"));
			    order.setAddress(rs.getString("address"));
				order.setUserphone(rs.getString("userphone"));
				order.setPostalcode(rs.getInt("postalcode"));
				order.setSendgoodstime(rs.getString("sendgoodstime"));
				result.add(order);
		
				
			}
			
		}catch (SQLException  sqle){
			throw new SQLException("select data exception: "+ sqle.getMessage());
			
			
		}catch(Exception e){
			throw new Exception("System e exception: "+e.getMessage());
			
		}finally{
			try{
				if(rs != null){
					rs.close();
				}
			}catch(Exception e){
				throw new Exception("statement close exception: "+ e.getMessage());
			}
			try{
				if(stmt !=null){
					stmt.close();
				}
			}catch (Exception e){
				throw new Exception("statement close exception: "+e.getMessage());
			}
			try{
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				throw new Exception("cennection close exception: "+ e.getMessage());
			}
		}
		return result;
		
	}
	// 根据传入的对象向数据库插入一条记录
	public void orderInsert(GoodsOrder order) throws Exception{
		Connection conn = null;
		PreparedStatement ps = null;
		// 根据对象的属性生成SQL语句
		String sql = "insert into goodsorder(receiver,howtopay,cost,state,ordertime,username,product,address,userphone,postalcode,sendgoodstime) values('"+ order.getReceiver()+"','"+order.getHowtopay()+"','"+order.getCost()+"','"+order.getState()+"','"+order.getOrdertime()+"','"+order.getUsername()+"','"+order.getProduct()+"','"+order.getAddress()+"','"+order.getUserphone()+"','"+order.getPostalcode()+"','"+order.getSendgoodstime()+"')";
		try{
			conn = getConnection();
			ps = conn.prepareStatement(sql);
			ps.executeUpdate();
		}catch(SQLException sqle){
			throw new Exception ("insert data exception "+ sqle.getMessage());
			
		}finally{
			try{
				if(ps != null){
					ps.close();
				}
			}catch(Exception e){
				throw new Exception("statement close exception: "+ e.getMessage());
			}
			
			try{
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				throw new Exception("cennection close exception: "+ e.getMessage());
			}
					
		}
		
	}
	// 根据传入的对象更新数据库记录
	public void orderUpdate(GoodsOrder order) throws Exception{
		Connection conn = null;
		PreparedStatement ps =null;
		// 根据对象的属性生成SQL语句
		String sql = "update goodsorder set receiver='"+order.getReceiver()+"',howtopay='"+order.getHowtopay()+"',cost='"+order.getCost()+"',state='"+order.getState()+"',ordertime='"+order.getOrdertime()+"',username='"+order.getUsername()+"',product='"+order.getProduct()+"',address='"+order.getAddress()+"',userphone='"+order.getUserphone()+"',postalcode='"+order.getPostalcode()+"',sendgoodstime='"+order.getSendgoodstime()+"'where ordernumber='"+order.getOrdernumber()+"'";
		try{
			conn = getConnection();
			ps = conn.prepareStatement(sql);
			ps.executeUpdate();
		}catch(SQLException sqle){
			throw new Exception ("update data exception "+ sqle.getMessage());
			
		}finally{
			try{
				if(ps != null){
					ps.close();
				}
			}catch(Exception e){
				throw new Exception("statement close exception: "+ e.getMessage());
			}
			
			try{
				if(conn != null){
					conn.close();
				}
				}catch(Exception e){
				throw new Exception("cennection close exception: "+ e.getMessage());
			}
			}
		
}
	public void orderDelete(GoodsOrder order) throws Exception{
		Connection conn = null;
		PreparedStatement ps =null;
		try{
			conn = getConnection();
		    String sql="delete from goodsorder where ordernumber='"+order.getOrdernumber()+"'";
		    ps = conn.prepareStatement(sql);
		    ps.executeUpdate();
		}catch(SQLException sqle){
			throw new Exception("delet data exception: "+ sqle.getMessage());
		}finally{
			try{
				if(ps != null){
					ps.close();
				}
			}catch(Exception e){
				throw new Exception("statement close exception: "+ e.getMessage());
			}
			
			try{
				if(conn != null){
					conn.close();
				}
				}catch(Exception e){
				throw new Exception("cennection close exception: "+ e.getMessage());
			}
		
	}
}
}