goodsdao.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 GoodsDao {
	// 取得一个数据库连接
	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 goodsSelect(String sql)throws Exception{
		ArrayList<Goods> result = new ArrayList<Goods>();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			conn = getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				Goods goods = new Goods();
				goods.setBookid(rs.getInt("bookid"));
				goods.setBookname(rs.getString("bookname"));
				goods.setAuthor(rs.getString("author"));
				goods.setPublish(rs.getString("publish"));
				goods.setStorenum(rs.getInt("storenum"));
				goods.setPrice(rs.getDouble("price"));
				goods.setIsbn(rs.getString("isbn"));
				goods.setPages(rs.getInt("pages"));
				goods.setPack(rs.getString("pack"));
				goods.setSort(rs.getString("sort"));
				goods.setWords(rs.getInt("words"));
				goods.setImageurl(rs.getString("imageurl"));
				goods.setContent(rs.getString("content"));
				
	
	
				result.add(goods);
				
			}
			
		}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 goodsInsert(Goods goods) throws Exception{
		Connection conn = null;
		PreparedStatement ps = null;
		// 根据user 对象的属性生成SQL语句
		String sql = "insert into goods(bookname,author,publish,storenum,price,isbn,pages,pack,sort,words,imageurl,content) values('"+ goods.getBookname()+"','"+ goods.getAuthor()+"','"+goods.getPublish()+"','"+goods.getStorenum()+"','"+goods.getPrice()+"','"+goods.getIsbn()+"','"+goods.getPages()+"','"+goods.getPack()+"','"+goods.getSort()+"','"+goods.getWords()+"','"+goods.getImageurl()+"','"+goods.getContent()+"')";
		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 goodsUpdate(Goods goods) throws Exception{
		Connection conn = null;
		PreparedStatement ps =null;
		// 根据user对象的属性生成SQL语句
		String sql = "update goods set bookname='"+goods.getBookname()+"',author='"+goods.getAuthor()+"',publish='"+goods.getPublish()+"',storenum='"+goods.getStorenum()+"',price='"+goods.getPrice()+"',isbn='"+goods.getIsbn()+"',pages='"+goods.getPages()+"',pack='"+goods.getPack()+"',sort='"+goods.getSort()+"',words='"+goods.getWords()+"',imageurl='"+goods.getImageurl()+"',content='"+goods.getContent()+"'where bookid='"+goods.getBookid()+"'";
		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 goodsDelete(Goods goods) throws Exception{
		Connection conn = null;
		PreparedStatement ps =null;
		try{
			conn = getConnection();
		    String sql="delete from goods where bookid='"+goods.getBookid()+"'";
		    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());
			}
		
	}
}
}