bookmanager.java

基于java/mysql的图书管理系统,主要使用的技术有java+jsp+tomcat+mysql+eclipse jdk版本1.6.0

package com.zyuntao.struts.model;

import java.sql.*;

import com.zyuntao.struts.database.*;
import com.zyuntao.struts.form.*;

import java.util.*; 
public class BookManager {
	Condatabase datasource;
	public BookManager()
	{	
	}
	public BookManager(Condatabase datasource)
	{	
		this.datasource = datasource;
	}
	public Collection mybook(String username) throws SQLException
	{
		
		Condatabase datasource=new Condatabase();
		Connection conn=datasource.getConnection();
		String sql="select bookid,bookname,author,type from booklist where username=? order by bookid asc";
	    PreparedStatement ps=null;
	    ResultSet rs=null;
	    
	    Collection BookForm=new ArrayList();
		try{
			ps=conn.prepareStatement(sql);
			ps.setString(1,username);
			rs=ps.executeQuery();
			
			while(rs.next())
				
			{
				BookForm book=new BookForm();
				book.setBookid(rs.getInt("bookid"));
				book.setBookname(rs.getString("bookname"));
				book.setAuthor(rs.getString("author"));
				book.setType(rs.getString("type"));
				BookForm.add(book);
			}
	}finally{
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
			}
		}

		if (ps != null) {
			try {
				ps.close();
			} catch (SQLException e) {
			}
		}

		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
			}
		
	}
	}
	if(BookForm.isEmpty())
		{return null;}
	return BookForm;
		}


public Collection booklist() throws SQLException{
Condatabase datasource=new Condatabase();
Connection conn=datasource.getConnection();
String sql="select * from booklist order by bookid asc";
Statement stmt=null;
ResultSet rs=null;
Collection BookForm=new ArrayList();
try{stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
while(rs.next()){
BookForm book=new BookForm();
book.setBookid(rs.getInt("bookid"));
book.setBookname(rs.getString("bookname"));
book.setAuthor(rs.getString("author"));
book.setType(rs.getString("type"));
book.setBorrow(rs.getInt("borrow"));
book.setUsername(rs.getString("username"));
BookForm.add(book);
}}
finally {

	if (rs != null) {
		try {
			rs.close();
		} catch (SQLException e) {
		}
	}

	if (stmt != null) {
		try {
			stmt.close();
		} catch (SQLException e) {
		}
	}

	if (conn != null) {
		try {
			conn.close();
		} catch (SQLException e) {
		}
	}
}
return BookForm ;	
}
public boolean borrow(int bookid,String username) throws SQLException
{Condatabase datasource=new Condatabase();
Connection conn=datasource.getConnection();
String sql1="select borrow,bookname from booklist where bookid=?";
String sql2="update booklist set borrow=1,username=? where bookid=?";
PreparedStatement ps=null;
PreparedStatement ps2=null;
ResultSet rs=null;
BookForm book=new BookForm();
boolean i;
try{
	ps=conn.prepareStatement(sql1);
	ps.setInt(1,bookid);
	rs=ps.executeQuery();
	
	if(rs.next())
		
	{
		
		book.setBorrow(rs.getInt("borrow"));
		book.setBookname(rs.getString("bookname"));
{
	}
}if(book.getBorrow()==0&&book.getBookname()!=null){
ps2=conn.prepareStatement(sql2);
ps2.setString(1,username);
ps2.setInt(2,bookid);
ps2.executeUpdate();


i=true;
}else
{i=false;}
}
finally{
	if(rs !=null){try{
		rs.close();}
		catch(SQLException e){}}
		if(ps !=null){try{
			ps.close();}
			catch(SQLException e){}}
    	if(ps2 !=null){try{
    		ps.close();}
    		catch(SQLException e){}}
	    	if (conn != null) {
	    		try {
	    			conn.close();
	    		} catch (SQLException e) {
	    		}
	    	}
}
return i;
}
public boolean returnbook(int bookid,String username) throws SQLException
{Condatabase datasource=new Condatabase();
Connection conn=datasource.getConnection();
String sql1="select username from booklist where bookid=?";
String sql2="update booklist set borrow=0,username=null where bookid=?";
PreparedStatement ps=null;
PreparedStatement ps2=null;
ResultSet rs=null;
BookForm book=new BookForm();
boolean i;
try{
	ps=conn.prepareStatement(sql1);
	ps.setInt(1,bookid);
	rs=ps.executeQuery();
	
	if(rs.next())
		
	{
		
		book.setUsername(rs.getString("username"));
{
	}
}if(username.equals(book.getUsername())){
ps2=conn.prepareStatement(sql2);
ps2.setInt(1,bookid);
ps2.executeUpdate();


i=true;
}else
{i=false;}}
finally{
	if(rs !=null){try{
		rs.close();}
		catch(SQLException e){}}
		if(ps !=null){try{
			ps.close();}
			catch(SQLException e){}}
    	if(ps2 !=null){try{
    		ps.close();}
    		catch(SQLException e){}}
	    	if (conn != null) {
	    		try {
	    			conn.close();
	    		} catch (SQLException e) {
	    		}
	    	}
}
return i;
}
public void addbook(String bookname,String author,String type) throws SQLException{
	Condatabase datasource=new Condatabase();
Connection conn=datasource.getConnection();
String sql="insert into booklist (bookname,author,type,borrow) values (?,?,?,0);";
PreparedStatement ps = null;
try{ps=conn.prepareStatement(sql);
ps.setString(1,bookname);
ps.setString(2,author);
ps.setString(3,type);
ps.executeUpdate();
}
finally {

	if (ps != null) {
		try {
			ps.close();
		} catch (SQLException e) {
		}
	}

	if (conn != null) {
		try {
			conn.close();
		} catch (SQLException e) {
		}
	}
}

}
	
public boolean delbook(int bookid) throws SQLException
{Condatabase datasource=new Condatabase();
Connection conn=datasource.getConnection();
String sql1="select bookname from booklist where bookid=?";
String sql2="delete from booklist where bookid=?";
PreparedStatement ps=null;
PreparedStatement ps2=null;;
ResultSet rs=null;
BookForm book=new BookForm();
boolean i;
try{
	ps=conn.prepareStatement(sql1);
	ps.setInt(1,bookid);
	rs=ps.executeQuery();
	
	if(rs.next())
		
	{
		
		book.setBookname(rs.getString("bookname"));
{
	}
}if(book.getBookname()!=null){
ps2=conn.prepareStatement(sql2);
ps2.setInt(1,bookid);
ps2.executeUpdate();


i=true;
}else
{i=false;}}
finally{
	if(rs !=null){try{
		rs.close();}
		catch(SQLException e){}}
		if(ps !=null){try{
			ps.close();}
			catch(SQLException e){}}
    	if(ps2 !=null){try{
    		ps2.close();}
    		catch(SQLException e){}}
    	if (conn != null) {
	    		try {
	    			conn.close();
	    		} catch (SQLException e) {
	    		}
	    	}
}
return i;
}
}