www.pudn.com > 使用 Java 读取条形码代码文件.rar > DatabaseAccess.java

package com.jmart.dbaccess; 
 
import com.jmart.*; 
import com.jmart.data.*; 
import java.sql.*; 
import java.util.*; 
import java.io.*; 
 
public class DatabaseAccess  
{ 
	private DatabaseAccess databaseAccess; 
	private String userName; 
	private String password; 
	private String schemaName; 
	private String databaseName; 
	 
/** 
 *  
 */ 
public DatabaseAccess() { 
	super(); 
} 
/** 
 *  
 * @param data com.jmart.InventoryData 
 */ 
 
// reads item # and manufacturer # from incoming object and removes corresponding record from db if it exists 
 	 
public boolean delInventory(InventoryData data) 
{ 
	Database db = null; 
	boolean deleteProgress = false; 
	grabDBproperties(); 
	try 
	{ 
		db = new Database(userName,password,databaseName); 
		// make sure a record does exist 
		String dbStatement = "select count(*) from " + schemaName  +".POSTABLE where itemnumber = " + Integer.parseInt(data.getItemNumber()) 
			+ " and manufacturerNumber = " + Integer.parseInt(data.getManufacturerNumber()); 
		ResultSet rs = db.executeQuery(dbStatement); 
		rs.next(); 
		if (rs.getInt(1)>=1) 
		{ 
			dbStatement = "delete from " + schemaName + ".POSTABLE where itemnumber = " + data.getItemNumber()  
				+ " and manufacturerNumber = " + Integer.parseInt(data.getManufacturerNumber()); 
			int accomplished = db.executeUpdate(dbStatement); 
			if (accomplished == 1) deleteProgress = true; 
		} 
		else return false; 
	} 
	catch (Exception e) 
	{ 
		System.out.println("An error has occurred: " + e); 
	} 
	finally 
	{ 
		db.close(); 
	} 
	return deleteProgress; 
} 
/** 
 * Insert the method's description here. 
 * Creation date: (5/25/2002 3:41:28 PM) 
 */ 
public void grabDBproperties()  
{ 
	Properties dbProps = new Properties(); 
	try 
	{ 
		FileInputStream in = new FileInputStream("db.ini"); 
		dbProps.load(in); 
		in.close(); 
	} 
	catch (Exception e) 
	{ 
		System.out.println("There was a problem reading the ini file."); 
	} 
 
	userName = dbProps.getProperty("userName"); 
	password = dbProps.getProperty("password"); 
	schemaName = dbProps.getProperty("schemaName"); 
	databaseName = dbProps.getProperty("databaseName"); 
 
} 
/** 
 * Insert the method's description here. 
 * Creation date: (5/11/2002 1:12:19 AM) 
 * @param args java.lang.String[] 
 */ 
public static void main(String[] args)  
{ 
	try 
	{ 
		InventoryData data = new InventoryData(); 
		DatabaseAccess dummy = new DatabaseAccess(); 
		 
		boolean test = dummy.updInventory(data); 
		System.out.println(test); 
		 
		//boolean test = dummy.delInventory(data); 
		 
		 
		data.setItemNumber("344356"); 
		data.setManufacturerNumber("34347"); 
		 
		//data = dummy.delInventory(data); 
		System.out.println(dummy.delInventory(data)); 
		//System.out.println(data.getQuantity()); 
	} 
	catch(Exception e) 
	{ 
		System.out.println(e); 
	} 
} 
/** 
 * 
 * @return com.jmart.InventoryData 
 * @param data com.jmart.InventoryData 
 */ 
 
 // reads out the item number and manufacturer number and returns 
 // the matching data object, setting property recordFound to true; 
 // if no object is found in DB, recordFound in InventoryData object is set to false  
 // with the item number and manufacturer name still in the object  
  
public InventoryData qryInventory(InventoryData data)  
{ 
	Database db = null; 
	grabDBproperties(); 
	// following is for testing only 
	/*data.setItemNumber("344356"); 
	/data.setManufacturerNumber("34345");*/ 
	// end testing code 
	try 
	{ 
		db = new Database(userName,password,databaseName); 
		String dbStatement = "select count(*) from "+ schemaName + ".POSTABLE where itemnumber = " + Integer.parseInt(data.getItemNumber()) 
			+ " and manufacturerNumber = " + Integer.parseInt(data.getManufacturerNumber()); 
		ResultSet rs = db.executeQuery(dbStatement); 
		rs.next(); 
		// case record exists 
		if (rs.getInt(1)>=1) 
		{ 
		 
			dbStatement = "SELECT * FROM " + schemaName + ".POSTABLE WHERE ITEMNUMBER =" + Integer.parseInt(data.getItemNumber()) + " AND "  
				+ " MANUFACTURERNUMBER = " + Integer.parseInt(data.getManufacturerNumber()); 
			rs = db.executeQuery(dbStatement); 
			data.setRecordFound(true); 
			rs.next(); 
 
			data.setManufacturerNumber(rs.getString("MANUFACTURERNUMBER")); 
			data.setItemNumber(rs.getString("ITEMNUMBER")); 
			data.setManufacturerName(rs.getString("MANUFACTURERNAME")); 
			data.setPrice(rs.getBigDecimal("PRICE", 2)); 
			data.setQuantity(rs.getInt("QUANTITY")); 
			data.setName(rs.getString("NAME")); 
		} 
		else data.setRecordFound(false); 
	} 
	catch (Exception e) 
	{ 
		System.out.println("An error has occurred: " + e); 
	} 
	 
	finally 
	{ 
		db.close(); 
	} 
	return data; 
} 
/** 
 *  
 * @param data com.jmart.InventoryData 
 */ 
public boolean updInventory(InventoryData data) 
{ 
	 
	// update the quantity of the item with matching item number and 
	// manufacturer number with the quantity being either positive or negative 
	Database db = null; 
	grabDBproperties(); 
	 
	// following is for testing only 
	 
	/*data.setItemNumber("344356"); 
	data.setManufacturerNumber("34346"); 
	data.setManufacturerName("Fritolay"); 
	data.setQuantity(60); 
	data.setName("BogusName"); 
	data.setPrice(new java.math.BigDecimal("23.32"));*/ 
	 
	//end testing code 
	 
	boolean updateProgress=false; 
	String dbStatement; 
	 
	 
	try 
	{ 
		db = new Database(userName,password,databaseName); 
		dbStatement = "SELECT COUNT(*) FROM " + schemaName + ".POSTABLE WHERE ITEMNUMBER =" + Integer.parseInt(data.getItemNumber()) + " AND "  
			+ " MANUFACTURERNUMBER = " + Integer.parseInt(data.getManufacturerNumber()); 
		ResultSet results = db.executeQuery(dbStatement); 
		results.next(); 
		int count = results.getInt(1); 
		// add item to database 
		if (count==0) 
		{ 
			// add item given incoming quantity is greater than 0 
			if (data.getQuantity()>0) 
			{ 
				dbStatement = "INSERT INTO " + schemaName + ".POSTABLE VALUES(" + Integer.parseInt(data.getItemNumber()) + "," + Integer.parseInt(data.getManufacturerNumber()) 
				+ ",'" + data.getName() +  "','" + data.getManufacturerName() + "'," + data.getQuantity()  
				+ "," + data.getPrice() + ")"; 
				int accomplished = db.executeUpdate(dbStatement); 
				if (accomplished==1) updateProgress=true; 
			} 
		} 
		// update item in database 
		else  
		{ 
			dbStatement = "SELECT QUANTITY FROM " + schemaName + ".POSTABLE WHERE ITEMNUMBER =" + Integer.parseInt(data.getItemNumber()) + " AND "  
				+ " MANUFACTURERNUMBER = " + Integer.parseInt(data.getManufacturerNumber()); 
			results = db.executeQuery(dbStatement); 
			results.next(); 
			int currentQuantity=results.getInt(1); 
			int desiredQuantity=currentQuantity+data.getQuantity(); 
			// make sure desired quantity is not negative before performing update 
			if (desiredQuantity>=0) 
			{ 
			 
				dbStatement = "UPDATE " + schemaName + ".POSTABLE SET QUANTITY =" + desiredQuantity 
					+ " WHERE ITEMNUMBER =" + Integer.parseInt(data.getItemNumber()) + " AND "  
					+ " MANUFACTURERNUMBER = " + Integer.parseInt(data.getManufacturerNumber()); 
				int accomplished = db.executeUpdate(dbStatement); 
				if (accomplished==1) updateProgress=true; 
			} 
		}	 
	} 
	catch (Exception e) 
	{ 
		System.out.println("A problem has occured: " + e); 
	} 
	finally 
	{ 
		db.close(); 
	} 
	return updateProgress; 
} 
}