dbconnector.java

功能简单的网上电子书店 javabean

//问题:如果链接数据库失败,如何把程序中的错误输出到网页,目前程序只是System.out.println("数据库链接失败!")
//在books里增加Count一项
//在Book类里增加private int count;
//getTopTen()中,取出所有书,然后根据count进行排序,此算法有待改进

/*
 * coding by ROse_MM
 * 07/11/8
 * ver 1.0
 */

package database;

import item.Book;
import java.util.*;
import java.sql.*;

public class DBconnector {
	private static Connection con;
	private static Statement sta;
	private static ResultSet re;
	private static String logname = "mm";
	private static String logpass = "123";
	// 以下为执行SQL语句的String
	private static String checkname;
	private static String checkall;
	private static String findbook;
	private static String adduser;
	private static String addSendTab;
	private static String searchbook;
	private static String searchcount;
	
	// 链接数据库
	private static boolean getConnection(){
		try{
			  Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");  // 注册驱动
			  con = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=bookstore",logname,logpass);//
			  sta = con.createStatement();
			  return true;
		}catch(Exception e)
		  {
			e.printStackTrace(System.out);
			return false;
		  }
	}
	
	
	// 真为没有重
	public static boolean checkName(String name){
		try {
			if(getConnection()==true){
				checkname = "select UserName from users where UserName ='"+name+"'";
				re = sta.executeQuery(checkname);
				if(re.next()==false){
					re.close();
					sta.close();
					con.close();
					return true;// 没有重名
				}
					
				else{
					re.close();
					sta.close();
					con.close();
					return false;
				}
			}
			else {
				System.out.println("数据库链接失败!");
				re.close();
				sta.close();
				con.close();
				return false;
				}
		} catch (SQLException e) {
			
			e.printStackTrace();
			return false;
		}
	}
	
	// 真为对应,登录成功
	public static boolean checkAll(String name,String password){
		try {
			if(getConnection()==true){
				checkall = "select UserName,UserPassword from users where UserName ='"+name+"',UserPassword='"+password+"'";
				re = sta.executeQuery(checkall);
				if(re.next()==false){
					re.close();
					sta.close();
					con.close();
					return false;// 登录失败,密码错或没有该用户
				}
				else{
					re.close();
					sta.close();
					con.close();
					return true;
				}
			}
			else {
				System.out.println("数据库链接失败!");
				re.close();
				sta.close();
				con.close();
				return false;
				}
		} catch (SQLException e) {
			
			e.printStackTrace();
			return false;
			
		}
	}
	
	public static Book getBookByID(int bookid){
		try {
			if(getConnection()==true){
				findbook="select BookID from books where BookID ="+bookid;
				re = sta.executeQuery(findbook);
				if(re.next()==false){
					re.close();
					sta.close();
					con.close();
					System.out.println("数据库中没有此书!");
					Book book_null=new Book();
					return book_null;
				}
				else{
					Book book_search = new Book();
					book_search.setTitle(re.getString("Title"));
					book_search.setAuthor(re.getString("Author"));
					book_search.setISBN(re.getString("ISBN"));
					book_search.setPrice(re.getDouble("Price"));
					book_search.setPublish(re.getString("Publish"));
					book_search.setDescription(re.getString("Description"));
					re.close();
					sta.close();
					con.close();
					return book_search;
				}
			}
			else {
				System.out.println("数据库链接失败!");
				re.close();
				sta.close();
				con.close();
				Book book_null=new Book();
				return book_null;
				}
		} catch (SQLException e) {
			
			e.printStackTrace();
			Book book_null=new Book();
			return book_null;
		}
	}
	
	// 真为系统错误,注册失败
	public static boolean addUser(String name,String password,String email){
		try {
			if(getConnection()==true){
				adduser = "insert into users (UserName,UserPassword,Email) values ('"+name+"','"+password+"','"+email+"')";
				sta.executeUpdate(adduser);
				re.close();
				sta.close();
				con.close();
				return true;
			}
			else {
				System.out.println("数据库链接失败!");
				re.close();
				sta.close();
				con.close();
				return false;
				}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	/*
	 * public boolean creatSendTab(String username,String rname,String
	 * credit,String addr,String postnum,ArrayList<Book> books){
	 * if(getConnection()==true){
	 *  } else return false; }
	 */
	
	public static ArrayList<Book> getTwentyBooks(String CID,int start){
		try {
			if(getConnection()==true){
				ArrayList<Book> books_twen = new ArrayList<Book>();
				int num=0,j=0;
				searchbook = "select * from books where CatID="+CID;
				re = sta.executeQuery(searchbook);
				if(re.next()==true){
					num = re.getInt("BookID");// 得到CID类书从第几本开始
				}
				int count = start%20;
				num += count;
				searchbook = "select * from books where BookID="+num;
				re = sta.executeQuery(searchbook);
				while(re.next()&&j<20){
					Book book_ever_twen = new Book();
					book_ever_twen.setTitle(re.getString("Title"));
					book_ever_twen.setAuthor(re.getString("Author"));
					book_ever_twen.setISBN(re.getString("ISBN"));
					book_ever_twen.setPrice(re.getDouble("Price"));
					book_ever_twen.setPublish(re.getString("Publish"));
					book_ever_twen.setDescription(re.getString("Description"));
					books_twen.add(book_ever_twen);
					++j;
				}
				/*
				 * while(re.next()){ Book book_twen = new Book();
				 * book_twen.setTitle(re.getString("Title"));
				 * book_twen.setAuthor(re.getString("Author"));
				 * book_twen.setISBN(re.getString("ISBN"));
				 * book_twen.setPrice(re.getDouble("Price"));
				 * book_twen.setPublish(re.getString("Publish"));
				 * book_twen.setDescription(re.getString("Description"));
				 * books_all.add(book_twen);
				 *  } int count = start%20; int j=0; ArrayList<Book> books_twen =
				 * new ArrayList<Book>(); for(int i=20*count;i<20*count+20;++i){
				 * //赋值 ++j; }
				 */
				re.close();
				sta.close();
				con.close();
				return books_twen;
			}
			else {
				System.out.println("数据库链接失败!");
				re.close();
				sta.close();
				con.close();
				ArrayList<Book> arraylist_null = new ArrayList<Book>();
				return arraylist_null;
				}
		} catch (SQLException e) {
			ArrayList<Book> arraylist_null = new ArrayList<Book>();
			
			e.printStackTrace();
			return arraylist_null;
		}
	}
	
	public static ArrayList<Book> getTopten(){
			try {
				if(getConnection()==true){
					ArrayList<Book> books_all = new ArrayList<Book>();
					ArrayList<Book> books_topten = new ArrayList<Book>();
					searchcount = "select * from books";
					re = sta.executeQuery(searchcount);
					while(re.next()){
						// books_all.add(Integer.valueOf(re.getString("Count")));
						Book book_ever_top = new Book();
						book_ever_top.setCount(re.getInt("Count"));
						book_ever_top.setTitle(re.getString("Title"));
						book_ever_top.setAuthor(re.getString("Author"));
						book_ever_top.setISBN(re.getString("ISBN"));
						book_ever_top.setPrice(re.getDouble("Price"));
						book_ever_top.setPublish(re.getString("Publish"));
						book_ever_top.setDescription(re.getString("Description"));
						books_all.add(book_ever_top);
						// 排序,从高到低
						// 存
						
					}
					re.close();
					sta.close();
					con.close();
					return books_topten;
				}
				else {
					System.out.println("数据库链接失败!");
					re.close();
					sta.close();
					con.close();
					ArrayList<Book> arraylist_null = new ArrayList<Book>();
					return arraylist_null;
					}
			} catch (SQLException e) {
				
				e.printStackTrace();
				ArrayList<Book> arraylist_null = new ArrayList<Book>();
				return arraylist_null;
			}
		
		}
}


			
			
	// public static ArrayList<Book> getNewten()