StockDB.java 2.96 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
package example.corba;

import java.sql.*;

/**
 * This class handles the JDBC side of things. It opens a connection to
 * the database, and performes queries on that database.
 *
 * In essence, you could use this class on it's own. The rest of the classes
 * in this example handle either the CORBA mechanism, or the frontend.
 *
 * Note: Before you ask, why perform a query on each call, you have to remember
 * that an object could be changed by another client, and we need to ensure that
 * the returned data is live and accurate.
 *
16
 * $Id: StockDB.java,v 1.3 2001/10/25 05:59:58 momjian Exp $
17 18 19
 */
public class StockDB
{
20 21 22 23 24 25 26 27 28 29 30 31
	Connection con;
	Statement st;

	// the current stock number
	int id = -1;

	public void connect(String url, String usr, String pwd) throws Exception
	{
		Class.forName("org.postgresql.Driver");
		System.out.println("Connecting to " + url);
		con = DriverManager.getConnection(url, usr, pwd);
		st = con.createStatement();
32
	}
33 34 35 36

	public void closeConnection() throws Exception
	{
		con.close();
37
	}
38 39 40 41

	public void fetchItem(int id) throws Exception
	{
		this.id = id;
42
	}
43 44 45 46 47

	public int newItem() throws Exception
	{
		// tba
		return -1;
48
	}
49 50 51 52 53 54 55 56 57 58 59 60

	public String getDescription() throws SQLException
	{
		ResultSet rs = st.executeQuery("select description from stock where id=" + id);
		if (rs != null)
		{
			rs.next();
			String s = rs.getString(1);
			rs.close();
			return s;
		}
		throw new SQLException("No ResultSet");
61
	}
62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133

	public int getAvailable() throws SQLException
	{
		ResultSet rs = st.executeQuery("select avail from stock where id=" + id);
		if (rs != null)
		{
			rs.next();
			int v = rs.getInt(1);
			rs.close();
			return v;
		}
		throw new SQLException("No ResultSet");
	}

	public int getOrdered() throws SQLException
	{
		ResultSet rs = st.executeQuery("select ordered from stock where id=" + id);
		if (rs != null)
		{
			rs.next();
			int v = rs.getInt(1);
			rs.close();
			return v;
		}
		throw new SQLException("No ResultSet");
	}

	public boolean isItemValid() throws SQLException
	{
		ResultSet rs = st.executeQuery("select valid from stock where id=" + id);
		if (rs != null)
		{
			rs.next();
			boolean b = rs.getBoolean(1);
			rs.close();
			return b;
		}
		throw new SQLException("No ResultSet");
	}

	public void addNewStock(int amount) throws SQLException
	{
		st.executeUpdate("update stock set avail=avail+" + amount +
						 ", ordered=ordered-" + amount +
						 " where id=" + id + " and ordered>=" + amount);
	}

	public void removeStock(int amount) throws SQLException
	{
		st.executeUpdate("update stock set avail=avail-" + amount +
						 " where id=" + id);
	}

	public void orderStock(int amount) throws SQLException
	{
		st.executeUpdate("update stock set ordered=ordered+" + amount +
						 " where id=" + id);
	}

	public int getLastID() throws SQLException
	{
		ResultSet rs = st.executeQuery("select max(id) from stock");
		if (rs != null)
		{
			rs.next();
			int v = rs.getInt(1);
			rs.close();
			return v;
		}
		throw new SQLException("No ResultSet");
	}

134
}