dbbook.java

《jsp编程起步》里面的所有源代码

package Db;

import java.sql.*;
import Bk.*;
import java.util.*;

public class DbBook{
	Connection Conn=null;
	Statement stmt=null;
	String sDBDriver = "com.jnetdirect.jsql.JSQLDriver";
	String sConnStr = "jdbc:JSQLConnect://localhost/mydb";
	
	public DbBook(){
		try{
			Class.forName("com.jnetdirect.jsql.JSQLDriver");
			Conn=DriverManager.getConnection(sConnStr,"sa","eren&candle");
			stmt=Conn.createStatement();
		}catch(Exception e){
			System.err.println(e);			
		}
	}
	
	public Vector	getBook(int category,javax.servlet.jsp.JspWriter out,String keyword){
		Vector vBook=new Vector();
		ResultSet rs;
		String SQL="";
		try{	if(keyword==null)
				SQL="SELECT * FROM book WHERE category='"+category+"'";
			else{
				if(category==0)
					SQL="SELECT * FROM book WHERE name LIKE '%"+keyword+"%'";
					else SQL="SELECT * FROM book WHERE category='"+category+"' AND name LIKE '%"+keyword+"%'";
			}
			
			rs=stmt.executeQuery(SQL);
			while(rs.next()){
				Book  book=new Book();
				book.id=rs.getLong("id");
				book.name=rs.getString("name");
				book.price=rs.getFloat("price");
				book.category=rs.getInt("category");
				book.quantity=rs.getLong("quantity");
				vBook.add(book);
				
				
			}
			
			rs.close();	
		}
		catch(Exception e){
			System.err.println(e);			
		}
		
		return vBook;
	}
	public Book getABook(long saleid,javax.servlet.jsp.JspWriter out){
		Book  book=new Book();
		try{	
			ResultSet rs=stmt.executeQuery("SELECT * FROM book WHERE id='"+saleid+"'");
			
			while(rs.next()){
			
				book.id=rs.getLong("id");
				book.name=rs.getString("name");
				book.price=rs.getFloat("price");
				book.category=rs.getInt("category");
				book.quantity=rs.getLong("quantity");
							
				
			}
			
			rs.close();	
		}
		catch(Exception e){
			System.err.println(e);			
		}
		return book;
	}
	public BookDetail getDetail(long bookid,javax.servlet.jsp.JspWriter out){
		BookDetail bookdtl=new BookDetail();
		
		try{	
			ResultSet rs=stmt.executeQuery("SELECT * FROM book_detail WHERE bookid='"+bookid+"'");
			
			while(rs.next()){
				bookdtl.id=rs.getLong("id");
				bookdtl.bookid=rs.getLong("bookid");
				bookdtl.author=rs.getString("author");
				bookdtl.introduce=rs.getString("introduce");
				bookdtl.publisher=rs.getString("publisher");
				bookdtl.pagenum=rs.getInt("pagenum");
				bookdtl.edition=rs.getString("edition");		
				
			}
			rs.close();	
		}
		catch(Exception e){
			System.err.println(e);			
		}
		
		return bookdtl;
	}
	
	public boolean decrease(long saleid,int num){
		try{	
			stmt.executeUpdate("UPDATE book SET quantity=quantity-"+num+" WHERE id="+saleid);
		}
		catch(Exception e){
			System.err.println(e);	
			return false;		
		}
		
		return true;
	}
	public long getBookId(String name){
		ResultSet rs=null;
		long id=-1;
		try{	
			rs=stmt.executeQuery("SELECT * FROM book WHERE name='"+name+"'");
			while(rs.next()){
				id=rs.getLong("id");
			}
		}
		catch(Exception e){
			System.err.println(e);	
			return -1l;		
		}
		return id;
	}
	public boolean addBook(int category,String name,String author,float price,String publisher,
			int pagenum,String edition,int quantity,String intro){
				
		long saleid=-1l;
		ResultSet rs=null;
		try{	
			rs=stmt.executeQuery("SELECT * FROM book WHERE name='"+name+"'");
			while(rs.next()){
				saleid=rs.getLong("id");
			}
		}catch(Exception e){
				System.err.println(e);	
				return false;		
		}
		if(saleid!=-1l){	//已经存在
			try{
				stmt.executeUpdate("UPDATE book SET quantity=quantity+"+quantity+"WHERE id="+saleid);
			}catch(Exception e){
				System.err.println(e);	
				return false;		
			}
		}else{
			try{
				stmt.executeUpdate("INSERT INTO book VALUES('"+name+"','"+price+"','"+category+"','"+quantity+"')");
				long bookid=getBookId(name);
				stmt.executeUpdate("INSERT INTO book_detail VALUES('"+bookid+"','"+author+"','"+publisher+"','"+pagenum+"','"+edition+"','"+intro+"')");
			}catch(Exception e){
				System.err.println(e);	
				return false;		
			}
		}
		return true;
	}
	public boolean destroy(){
		try{
			Conn.close();
		}catch(Exception e){
			return false;	
		}
		return true;
	}
}