bookdaoimpl.java

一个经典购物车（shoppingcart）实例代码

package com.jpioneer.application.book.dao.impl;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import com.jpioneer.application.book.dao.BookDAO;
import com.jpioneer.application.book.domain.Book;
import com.jpioneer.application.util.DBCon;

public class BookDaoImpl implements BookDAO{

	public void addBook(Book b) throws Exception {
		int authorid=b.getAuthorId();
		String authorname=b.getAuthorName();
		String bookname=b.getBookName();
		int numbersold=b.getNumbersold();
		Connection con=DBCon.getCon();
		Statement stm=con.createStatement();
		String sql="insert into books(authorid,authorname,bookname,numbersold) values("+authorid+",'"+authorname+"','"+bookname+"',"+numbersold+")";
		int i=stm.executeUpdate(sql);
		if(i>0){
			System.out.println("add success");
		}
		stm.close();
		
	}

	public void updateBook(Book b) throws Exception {
		int authorid=b.getAuthorId();
		String authorname=b.getAuthorName();
		String bookname=b.getBookName();
		int numbersold=b.getNumbersold();
		Connection con=DBCon.getCon();
		Statement stm=con.createStatement();
		String sql="update books set authorname='"+authorname+"',bookname='"+bookname+"',numbersold="+numbersold +" where authorid="+authorid;
		int i=stm.executeUpdate(sql);
		if(i>0){
			System.out.println("update success");
		}
		stm.close();
		
	}

	public void deleteBook(int id) throws Exception{
		Connection con=DBCon.getCon();
		Statement stm=con.createStatement();
		String sql="delete from books where authorid="+id;
		int i=stm.executeUpdate(sql);
		if(i>0){
			System.out.println("delete Success");
		}
		stm.close();
		
	}

	public boolean isExists(int id) throws Exception{
		Connection con=DBCon.getCon();
		Statement stm=con.createStatement();
		String sql="select count(*) from books where authorid="+id;
		ResultSet rs=stm.executeQuery(sql);
		rs.next();
		boolean flag=false;
		if(rs.getInt(1)>0){
			flag=true;
		}
		return flag;
	}
   /*
    *  content:查询所有关于书的纪录
    * rerurn:List 
    * param：
    */
	public List listAllBooks() throws Exception{
		List list =new ArrayList();
		Connection con=DBCon.getCon();
		String sql="select * from books";
		PreparedStatement stm=con.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
		ResultSet rs=stm.executeQuery();
		Book b=null;
		while (rs.next()){
			int authorid=rs.getInt("authorid");
			String authorname=rs.getString("authorname");
			String bookname=rs.getString("bookname");
			int numbersold=rs.getInt("numbersold");
			b=new Book();
			b.setAuthorId(authorid);
			b.setAuthorName(authorname);
			b.setBookName(bookname);
			b.setNumbersold(numbersold);
			list.add(b);
		}
		return list;
	}

	public List getByAuthorName(String authorName) throws Exception{
		List list =new ArrayList();
		Connection con=DBCon.getCon();
		String sql="select * from books where authorname like ?";
		PreparedStatement stm=con.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
		stm.setString(1,"%"+authorName+"%");
		ResultSet rs=stm.executeQuery();
		Book b=null;
		while (rs.next()){
			int authorid=rs.getInt("authorid");
			String authorname=rs.getString("authorname");
			String bookname=rs.getString("bookname");
			int numbersold=rs.getInt("numbersold");
			b=new Book();
			b.setAuthorId(authorid);
			b.setAuthorName(authorname);
			b.setBookName(bookname);
			b.setNumbersold(numbersold);
			list.add(b);
		}
		return list;
	}

	public Book getByAuhtorId(int id) throws Exception{
		List list =new ArrayList();
		Connection con=DBCon.getCon();
		String sql="select * from books where authorid="+id;
		PreparedStatement stm=con.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
		ResultSet rs=stm.executeQuery();
		rs.next();
		int authorid=rs.getInt("authorid");
		String authorname=rs.getString("authorname");
		String bookname=rs.getString("bookname");
		int numbersold=rs.getInt("numbersold");
		Book b=new Book();
		b.setAuthorId(authorid);
		b.setAuthorName(authorname);
		b.setBookName(bookname);
		b.setNumbersold(numbersold);
		
		return b;
	}

	public void addProcBook(Book b) throws Exception {
		Connection con=DBCon.getCon();
		int authorid=b.getAuthorId();
		String authorname=b.getAuthorName();
		String bookname=b.getBookName();
		int numbersold=b.getNumbersold();
		CallableStatement cstm=con.prepareCall("{?=call savebooks(?,?,?,?)}");
		cstm.registerOutParameter(1,Types.INTEGER);
		cstm.setInt(2,authorid);
		cstm.setString(3,authorname);
		cstm.setString(4,bookname);
		cstm.setInt(5,numbersold);
		cstm.execute();
		int i=cstm.getInt(1);
		if(i==-1){
			System.out.println("Exists!");
		}else{
			System.out.println("add Success!!!");
		}
		
	}



}