001: /*
002: * $Id: DatabaseConnection.java,v 1.14 2007/09/18 11:27:16 agoubard Exp $
003: *
004: * Copyright 2003-2005 Orange Nederland Breedband B.V.
005: * See the COPYRIGHT file for redistribution and use restrictions.
006: */
007: package com.mycompany.petstore.api;
008:
009: import java.io.File;
010: import java.io.FileInputStream;
011: import java.sql.Connection;
012: import java.sql.DriverManager;
013: import java.sql.SQLException;
014: import java.util.Iterator;
015: import java.util.Properties;
016: import java.util.StringTokenizer;
017:
018: import org.xins.common.collections.InvalidPropertyValueException;
019: import org.xins.common.collections.MissingRequiredPropertyException;
020: import org.xins.common.collections.PropertyReader;
021: import org.xins.common.manageable.DeinitializationException;
022: import org.xins.common.manageable.InitializationException;
023: import org.xins.common.manageable.Manageable;
024:
025: /**
026: * Manageable used to share the connection with the database and execute common
027: * queries.
028: *
029: * @version $Revision: 1.14 $
030: * @author <a href="mailto:anthony.goubard@japplis.com">Anthony Goubard</a>
031: */
032: public class DatabaseConnection extends Manageable {
033:
034: /**
035: * The API.
036: */
037: private APIImpl _api;
038:
039: /**
040: * Connection to the database.
041: */
042: private Connection _connection;
043:
044: /**
045: * Creates a new instance of DatabaseConnection
046: */
047: public DatabaseConnection(APIImpl api) {
048: _api = api;
049: }
050:
051: protected void initImpl(PropertyReader properties)
052: throws MissingRequiredPropertyException,
053: InvalidPropertyValueException, InitializationException {
054:
055: try {
056: if (_connection != null) {
057: _connection.close();
058: }
059: } catch (SQLException sqlEx) {
060: // Ignore as we're building a connection
061: }
062:
063: RuntimeProperties runtimeProps = (RuntimeProperties) _api
064: .getProperties();
065: String driverName = runtimeProps.getPetstoreDatabaseDriver();
066: String databaseURL = runtimeProps.getPetstoreDatabaseUrl()
067: .replace('\\', File.separatorChar);
068: String userName = runtimeProps.getPetstoreDatabaseUsername();
069: String password = runtimeProps.getPetstoreDatabasePassword();
070:
071: try {
072: Class.forName(driverName);
073: _connection = DriverManager.getConnection(databaseURL,
074: userName, password);
075: } catch (ClassNotFoundException cnfex) {
076: // Could not find the database driver
077: throw new InitializationException(cnfex);
078: } catch (SQLException sqlEx) {
079: // Could not connect to the database
080: sqlEx.printStackTrace();
081: throw new InitializationException(sqlEx);
082: }
083:
084: // Creates the tables if needed.
085: try {
086: _connection.createStatement().executeQuery(
087: "SELECT email FROM CUSTOMERS");
088: } catch (SQLException sqlEx) {
089: try {
090: _connection
091: .createStatement()
092: .executeUpdate(
093: "CREATE TABLE PETS (id int, petname char(30), price float, age int, quantity int)");
094: // _connection.createStatement().execute("CREATE TABLE USERS (email varchar(70), password varchar(20), gender varchar(6), " +
095: // "firstname varchar(20), lastname varchar(20), address varchar(60), phonenumber varchar(12)");
096: _connection
097: .createStatement()
098: .executeUpdate(
099: "CREATE TABLE CUSTOMERS (email char(60), password char(20), gender char, "
100: + "firstname char(20), lastname char(20), address char(60), phonenumber char(30))");
101: _connection
102: .createStatement()
103: .executeUpdate(
104: "CREATE TABLE ORDERS (petid int, email char(60), quantity int, status char(10))");
105: Properties petsProps = new Properties();
106: String databaseLocation = runtimeProps
107: .getPetstoreDatabaseLocation().replace('/',
108: File.separatorChar).replace('\\',
109: File.separatorChar);
110: FileInputStream databaseBootstrap = new FileInputStream(
111: databaseLocation + File.separator
112: + "pets.properties");
113: petsProps.load(databaseBootstrap);
114: databaseBootstrap.close();
115: Iterator itPets = petsProps.keySet().iterator();
116: while (itPets.hasNext()) {
117: String nextPetID = (String) itPets.next();
118: String petData = petsProps.getProperty(nextPetID);
119: StringTokenizer stPetData = new StringTokenizer(
120: petData, ",");
121: if (stPetData.hasMoreTokens()) {
122: String petName = stPetData.nextToken().trim();
123: String petPrice = stPetData.nextToken().trim();
124: String age = stPetData.nextToken().trim();
125: String quantity = stPetData.nextToken().trim();
126: _connection.createStatement().executeUpdate(
127: "INSERT INTO PETS (id, petname, price, age, quantity) "
128: + " VALUES (" + nextPetID
129: + ", '" + petName + "', "
130: + petPrice + ", " + age + ", "
131: + quantity + ")");
132: }
133: }
134: _connection
135: .createStatement()
136: .executeUpdate(
137: "INSERT INTO CUSTOMERS (email, password, gender, firstname, lastname, address, phonenumber) "
138: + "VALUES ('test@test.com', 'tester', 'Mister', 'John', 'Doe', 'Baker Street 1, 1011PZ London, UK', '')");
139: } catch (Exception ex) {
140: ex.printStackTrace();
141: throw new InitializationException(ex);
142: }
143: }
144: }
145:
146: protected void deinitImpl() throws Throwable {
147: _connection.close();
148: }
149:
150: /**
151: * Gets the connection to the database.
152: *
153: * @return
154: * the connection to the database, never <code>null</code>.
155: */
156: Connection getConnection() {
157: return _connection;
158: }
159: }
|