books.java

CMS（内容管理系统）

package com.wrox.databases;

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

public class Books
{
  String error;
  Connection con;
  public Books() {}
  public void connect() throws ClassNotFoundException,SQLException,Exception {
    try{
	  Class.forName("com.mysql.jdbc.Driver").newInstance();
	  con = DriverManager.getConnection(
		  "jdbc:mysql://localhost/Wrox?user=root&password=root");
	} catch (ClassNotFoundException cnfe){
	  error = "ClassNotFoundException: Could not locate DB driver.";
	  throw new ClassNotFoundException(error);
	} catch (SQLException cnfe){
	  error = "SQLException: Could not connect to database.";
	  throw new SQLException(error);
	} catch (Exception e) {
	  error = "Exception: An unknown error occurred while connecting " + "to database.";
	  throw new Exception(error);
	}
  }
  public void disconnect() throws SQLException {
    try{
	  if (con != null)
	  {
		  con.close();
	  }
	} catch (SQLException sqle){
	  error = ("SQLException: Unable to close the database connection.");
	  throw new SQLException(error);
	}
  }
  public ResultSet viewBooks() throws SQLException, Exception {
    ResultSet rs = null;
	try
	{
	  String queryString = ("SELECT * FROM Book;");
	  Statement stmt = con.createStatement();
	  rs = stmt.executeQuery(queryString);
	} 
	catch (SQLException sqle)
	{
		error = "SQLException: Could not execute the query.";
		throw new SQLException(error);
	}
	catch (Exception e){
	    error = "An exception occured while retrieving books.";
		throw new Exception(error);
	}
	return rs;
  }

public  static  String  UnicodeToChinese(String  s){  
   try{  
         if(s==null  || s.equals(""))  return  "";  
         String  newstring=null;  
         newstring=new String(s.getBytes(),"GB2312");   
         return  newstring;  
       }  
   catch( Exception  e)  
   {  
   return  s;  
   }  
   }  
 
public  static  String  ChineseToUnicode(String  s){  
   try{  
   if(s==null  || s.equals(""))  return  "";  
   String  newstring=null;  
   newstring=new  String(s.getBytes("gb2312"),"ISO8859_1");  
     return  newstring;  
   }  
   catch( Exception  e)  
   {  
   return  s;  
 }  
   }


  public void addBooks( String title, float price, int cid,String intro)
	  throws SQLException, Exception{
    if (con != null){
		try{
  		  title = ChineseToUnicode(title);//title=new String(title.getBytes(),"GB2312");
		  intro = ChineseToUnicode(intro);
		  
		  PreparedStatement updatebooks;
		  updatebooks = con.prepareStatement("insert into Book(Title,Category_ID ,Price ,Intro,timeUpdate) values(?,?,?,?,?);");
		  //updatebooks.setInt(1,id);
		  updatebooks.setString(1,title);
		  updatebooks.setInt(2,cid);
		  updatebooks.setFloat(3,price);
  		  updatebooks.setString(4,intro);
		  updatebooks.setTimestamp(5,new Timestamp((new java.util.Date()).getTime()));	//add datetime type data
		  updatebooks.execute();
		  
		}  catch(SQLException sqle){
		  error = "SQLException: update failed,possible duplicate entry";
		  throw new SQLException(error);
		}
    } else {
	  error = "Exception: Connection to database was lost.";
	  throw new Exception(error);
	}
  }
  public void removeBooks(String[] pkeys) throws SQLException, Exception{
    if (con!=null)
    {
		try{
		  PreparedStatement delete;
		  delete = con.prepareStatement("DELETE FROM Book where Title_ID=?;");
		  for (int i = 0;i< pkeys.length ;i++ )
		  {
			  delete.setInt(1,Integer.parseInt(pkeys[i]));
			  delete.execute();
		  }
		} catch (SQLException sqle){
		  error = "SQLException: update failed,possible duplicate entry";
		  throw new SQLException(error);
		} catch (Exception e){
		  error = "An exception occured while deleting books.";
		  
		  throw new Exception(e.toString());
		}
    } else {
	  error="Exception: Connection to database was lost.";
	  throw new Exception(error);
	}
  }


  public ResultSet executeQuery(String sql) {
    ResultSet  rs = null;
    try {
    Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
    rs = stmt.executeQuery(sql);
    }
    catch(SQLException ex) {
      System.err.println("aq.executeQuery: " + ex.getMessage());
    }
    return rs;
  }

  public void executeUpdate(String sql) {
    Statement stmt = null;
    ResultSet rs=null;
    try {
    stmt = con.createStatement();
    stmt.executeUpdate(sql);
    stmt.close();
    }
    catch(SQLException ex) {
      System.err.println("aq.executeQuery: " + ex.getMessage());
    }
  }


}