clientbillingdb.java

挺好的JAVA例子,可用MYSQL,ACCESS,ORACLE做后台,例子中包括联结及查询等涵数

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

public class ClientBillingDB {

	private java.sql.Connection conn;
	private String url;

	private static final int DUPKEY_ERRCODE = 1062;

	ClientBillingDB() throws SQLException, ClassNotFoundException {
		try {

			// /*
			//Load the hsqldb driver
			Class.forName("org.hsqldb.jdbcDriver");
			url = "jdbc:hsqldb:Clients"; // stored on disk mode
			//Open the connection
			conn = DriverManager.getConnection(url, "SA", "");
			System.out.println("Opened HSQLDB database.");
			// */
			
            /*
			//Load the MySQL driver
			Class.forName("com.mysql.jdbc.Driver");
			url = "jdbc:mysql://localhost/database01";
			//Open the connection
			conn = DriverManager.getConnection(url, "root", "");
			System.out.println("Opened MYSQL database.");
			*/

            /*
			//Load the MS Access drivers
			//Remember that in Windows 2000 and XP, ODBC connections are defined
			// in the Control Panel --> Admin Tools --> Data Sources
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			url = "jdbc:odbc:Clients";
			//Open the connection
			conn = DriverManager.getConnection(url, "", "");
			System.out.println("Opened ACCESS database.");
			*/

		} catch (Exception e) {
			System.out.println("*** Error: " + e.getMessage());
		}
	}

	public void close() throws SQLException {
		//Close the open database connection.
		conn.close();
		System.out.println("Closed the database.");
	}

	public Client[] retrieveClients() throws SQLException {
		String query = "SELECT * FROM client;";
		ResultSet rs = retrieve(query);

		//Parse the ResultSet into a LinkedList of Clients
		LinkedList results = new LinkedList();
		while (rs.next()) {
			//Create the next client object, and load the fields.
			Client c = new Client();
			c.setFields(
				rs.getInt("clientID"),
				rs.getString("firstName"),
				rs.getString("lastName"),
				rs.getString("phone"),
				rs.getString("email"),
				rs.getString("dob"),
				rs.getString("address"),
				rs.getString("miscInfo"));
			results.add(c);
		}

		Client[] retval = new Client[results.size()];
		retval = (Client[])results.toArray(retval);

		return retval;
	}

	public Transaction[] retrieveTransactions(int clientID)
		throws SQLException {
		String query =
			"SELECT * FROM transaction where clientID = "
				+ clientID
				+ " ORDER BY transactionID;";
		ResultSet rs = retrieve(query);

		//Build a LinkedList of the correct Transactions.
		LinkedList results = new LinkedList();
		while (rs.next()) {
			//Create the next Transaction object, and load its fields.
			Transaction t = new Transaction();
			t.setFields(
				rs.getInt("transactionID"),
				rs.getInt("clientID"),
				rs.getInt("amount"),
				rs.getString("tDate"),
				rs.getString("description"));
			results.add(t);
		}

		//Convert the LinkedList to an array, and return it.
		Transaction[] retval = new Transaction[results.size()];
		retval = (Transaction[])results.toArray(retval);
		return retval;
	}

	public boolean create(Client c) {
		String update =
			"INSERT INTO client(clientID, firstName, lastName, phone, email, dob, address, miscInfo) VALUES (";

		update += c.clientID
			+ ", "
			+ "'"
			+ c.firstName
			+ "', '"
			+ c.lastName
			+ "', '"
			+ c.phone
			+ "', '"
			+ c.email
			+ "', '"
			+ c.dob
			+ "', '"
			+ c.address
			+ "', '"
			+ c.miscInfo
			+ "');";

		try {
			execute(update);
		} catch (SQLException e) {
			if (e.getErrorCode() == DUPKEY_ERRCODE) { //duplicate key
				return false;
			} else
				e.printStackTrace();
		}
		return true;
	}

	public boolean create(Transaction t) {
		//Build an SQL insert statement for the new Transaction.

		String update =
			"INSERT INTO transaction(transactionID, clientID, description, amount, tDate) VALUES (";

		//Specify the fields.
		update += t.transactionID
			+ ", "
			+ ""
			+ t.clientID
			+ ", '"
			+ t.description
			+ "', "
			+ t.amount
			+ ", '"
			+ t.date
			+ "');";

		try {
			execute(update);
		} catch (SQLException e) {
			if (e.getErrorCode() == DUPKEY_ERRCODE) { //duplicate key
				return false;
			} else
				e.printStackTrace();
		}
		return true;
	}

	public boolean update(int oldID, Client c) {
		//Build a SQL update statement for the Client.
		String update =
			"UPDATE client SET clientID = "
				+ 
				+ c.clientID
				+ ", firstName = '"
				+ c.firstName
				+ "', lastName = '"
				+ c.lastName
				+ "', phone = '"
				+ c.phone
				+ "', email = '"
				+ c.email
				+ "', dob = '"
				+ c.dob
				+ "', address = '"
				+ c.address
				+ "', miscInfo = '"
				+ c.miscInfo
				+ "' WHERE clientID = '"
				+ oldID
				+ "';";
		try {
			execute(update);
		} catch (SQLException e) {
			if (e.getErrorCode() == DUPKEY_ERRCODE) {
				return false;
			} else
				e.printStackTrace();
		}
		return true;
	}

	public boolean update(int oldID, Transaction t) {
		//Build a SQL update statement for the Transaction.
		String update =
			"UPDATE transaction SET amount = "
				+ t.amount
				+ ", tDate = '"
				+ t.date
				+ "', description = '"
				+ t.description
				+ "', transactionID = "
				+ t.transactionID
				+ " WHERE transactionID = "
				+ oldID
				+ " and clientID = " + t.clientID + ";";
		try {
			execute(update);
		} catch (SQLException e) {
			if (e.getErrorCode() == DUPKEY_ERRCODE) {
				return false;
			} else
				e.printStackTrace();
		}
		return true;
	}

	public void delete(Client c) throws SQLException {
		//Build and execute a SQL delete statement for the Transaction.
		String query =
			"DELETE FROM client WHERE clientID = " + c.clientID + ";";
		execute(query);
	}

	public void delete(Transaction t) throws SQLException {
		//Build and execute a SQL delete statement for the Transaction.
		String query =
			"DELETE FROM transaction WHERE transactionID = "
				+ t.transactionID
				+ " AND clientID = "
				+ t.clientID
				+ ";";
		execute(query);
	}
	
	public void deleteTransactionsByClientID(int clientID) throws SQLException {
			//Build and execute a SQL delete statement for the Transaction.
			String query =
				"DELETE FROM transaction WHERE clientID = "
					+ clientID + ";";
			execute(query);
		}

	private void execute(String cmd) throws SQLException {
		//Execute a SQL statement.
		//If a primary key was autogenerated
		//(ie. for an INSERT statement), extract it and return it.
		//Otherwise return -1.

		Statement s = conn.createStatement();
		//int result = s.executeUpdate(cmd, Statement.RETURN_GENERATED_KEYS);
		int result = s.executeUpdate(cmd);
		//System.out.println(result);
		/*  Access could not handle the following statements
		    so they have been removed for now.
		    However, MYSQL was able to handle them correctly. DS
		
		ResultSet keys = s.getGeneratedKeys();
		if (keys != null && keys.getMetaData().getColumnCount() > 0) {
		    keys.next();
		    int newKey = keys.getInt("GENERATED_KEY");
		    System.out.println("New key is: " + newKey);
		    keys.close();
		    s.close();
		    return newKey;
		}
		else
		    return -1;
		*/
	}

	private ResultSet retrieve(String query) throws SQLException {
		//System.out.printn(query);

		//Execute a SQL statement, usually a SELECT, which returns a ResultSet.
		Statement s = conn.createStatement();
		ResultSet res = s.executeQuery(query);
		return res;
	}
	
	/*
	//For debugging purposes only...	 
	private void dumpResultSet(ResultSet rs) throws SQLException {
		//Print a ResultSet to stdout for debugging.

		ResultSetMetaData rsm = rs.getMetaData();

		int max = rsm.getColumnCount();
		for (int i = 1; i <= max; i++)
			System.out.print(rsm.getColumnName(i) + "\t");
		System.out.println();

		while (rs.next()) {
			for (int i = 1; i <= max; i++)
				System.out.print(rs.getString(i) + "\t");
			System.out.println();
		}

	}
	*/

}