orderproductdao.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 OrderProductDao {
	// 取得一个数据库连接
	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 orderproductSelect(String sql)throws Exception{
		ArrayList<OrderProduct> result = new ArrayList<OrderProduct>();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			conn = getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				OrderProduct orderproduct = new OrderProduct();
				orderproduct.setGoodsid(rs.getInt("goodsid"));
				orderproduct.setGoodsname(rs.getString("goodsname"));
				orderproduct.setGoodsnum(rs.getInt("goodsnum"));
				orderproduct.setCurrentprice(rs.getDouble("currentprice"));
				orderproduct.setInorder(rs.getInt("inorder"));
				result.add(orderproduct);
				
				
				
			}
			
		}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 orderproductInsert(OrderProduct orderproduct) throws Exception{
		Connection conn = null;
		PreparedStatement ps = null;
		// 根据user 对象的属性生成SQL语句
		String sql = "insert into orderproduct(goodsid,goodsname,goodsnum,currentprice,inorder) values('"+ orderproduct.getGoodsid()+"','"+ orderproduct.getGoodsname()+"','"+orderproduct.getGoodsnum()+"','"+orderproduct.getCurrentprice()+"','"+orderproduct.getInorder()+"')";
		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 orderproductUpdate(OrderProduct orderproduct) throws Exception{
		Connection conn = null;
		PreparedStatement ps =null;
		// 根据对象的属性生成SQL语句
		String sql = "update orderproduct set goodsid='"+orderproduct.getGoodsid()+"',goodsname='"+orderproduct.getGoodsname()+"',goodsnum='"+orderproduct.getGoodsnum()+"',currentprice='"+orderproduct.getCurrentprice()+"',inorder='"+orderproduct.getInorder()+"'";
		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 orderproductDelete(OrderProduct orderproduct) throws Exception{
		Connection conn = null;
		PreparedStatement ps =null;
		try{
			conn = getConnection();
		    String sql="delete from orderproduct where inorder='"+orderproduct.getInorder()+"'";
		    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());
			}
		
	}

	}
	}