Java tutorial
/* * Copyright 2011 Research In Motion Limited. * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package eclserver.db.objects; /** * * @author rbalsewich */ import java.sql.Statement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Connection; import java.util.ArrayList; import java.util.List; import org.json.simple.JSONValue; import org.json.simple.JSONObject; import org.json.simple.JSONArray; /** * * @author rbalsewich */ public class ContactsDao { /** Creates a new instance of ContactsDao */ public ContactsDao(Connection connection) { this(connection, "ContactsRecords"); } public ContactsDao(Connection connection, String addressListName) { this.objectName = addressListName; try { System.out.println("Received connection from factory... building Addresses List. \n"); dbConnection = connection; stmtGetListEntries = dbConnection.prepareStatement(strGetListEntries); stmtSaveNewRecord = dbConnection.prepareStatement(strSaveAddress, Statement.RETURN_GENERATED_KEYS); stmtUpdateExistingRecord = dbConnection.prepareStatement(strUpdateAddress); stmtGetAddress = dbConnection.prepareStatement(strGetAddress); stmtDeleteAddress = dbConnection.prepareStatement(strDeleteAddress); stmtNukeAddresses = dbConnection.prepareStatement(strNukeAddresses); } catch (SQLException ex) { System.out.println("Exception creating AddressDAO: " + ex.getSQLState() + ex.getMessage()); } } public int saveRecord(AddressObject record) { System.out.println("Saving record: " + record.getLastName() + "," + record.getFirstName()); int id = -1; try { stmtSaveNewRecord.clearParameters(); stmtSaveNewRecord.setString(1, record.getGroupName()); stmtSaveNewRecord.setString(2, record.getLastName()); stmtSaveNewRecord.setString(3, record.getFirstName()); stmtSaveNewRecord.setString(4, record.getEmail()); stmtSaveNewRecord.setString(5, record.getHomePhone()); stmtSaveNewRecord.setString(6, record.getWorkPhone()); stmtSaveNewRecord.setString(7, record.getMobilePhone()); stmtSaveNewRecord.setString(8, record.getPin()); stmtSaveNewRecord.setString(9, record.getAddress1()); stmtSaveNewRecord.setString(10, record.getAddress2()); stmtSaveNewRecord.setString(11, record.getCity()); stmtSaveNewRecord.setString(12, record.getState()); stmtSaveNewRecord.setString(13, record.getZip()); stmtSaveNewRecord.setString(14, record.getCountry()); stmtSaveNewRecord.setString(15, record.getTitle()); stmtSaveNewRecord.setString(16, record.getCompany()); int rowCount = stmtSaveNewRecord.executeUpdate(); ResultSet results = stmtSaveNewRecord.getGeneratedKeys(); if (results.next()) { id = results.getInt(1); } } catch (SQLException sqle) { System.out.println("AddressDAO Exception in Save Record: " + sqle.getMessage()); } return id; } public boolean editRecord(AddressObject record) { boolean bEdited = false; try { stmtUpdateExistingRecord.clearParameters(); stmtUpdateExistingRecord.setString(1, record.getGroupName()); stmtUpdateExistingRecord.setString(2, record.getLastName()); stmtUpdateExistingRecord.setString(3, record.getFirstName()); stmtUpdateExistingRecord.setString(4, record.getEmail()); stmtUpdateExistingRecord.setString(5, record.getHomePhone()); stmtUpdateExistingRecord.setString(6, record.getWorkPhone()); stmtUpdateExistingRecord.setString(7, record.getMobilePhone()); stmtUpdateExistingRecord.setString(8, record.getPin()); stmtUpdateExistingRecord.setString(9, record.getAddress1()); stmtUpdateExistingRecord.setString(10, record.getAddress2()); stmtUpdateExistingRecord.setString(11, record.getCity()); stmtUpdateExistingRecord.setString(12, record.getState()); stmtUpdateExistingRecord.setString(13, record.getZip()); stmtUpdateExistingRecord.setString(14, record.getCountry()); stmtUpdateExistingRecord.setString(15, record.getTitle()); stmtUpdateExistingRecord.setString(16, record.getCompany()); stmtUpdateExistingRecord.setInt(17, record.getId()); stmtUpdateExistingRecord.executeUpdate(); bEdited = true; } catch (SQLException sqle) { System.out.println("AddressDao Exception Update Record:" + sqle.getMessage()); } return bEdited; } public boolean deleteRecord(int id) { boolean bDeleted = false; try { stmtDeleteAddress.clearParameters(); stmtDeleteAddress.setInt(1, id); stmtDeleteAddress.executeUpdate(); bDeleted = true; } catch (SQLException sqle) { System.out.println("SQLEXCEPTION in ContactsDao.deleteRecord " + sqle.getMessage()); } return bDeleted; } public boolean nukeRecords() { boolean bDeleted = false; try { stmtNukeAddresses.executeUpdate(); bDeleted = true; } catch (SQLException sqle) { System.out.println("Error nuking addresses: " + sqle.getMessage()); } return bDeleted; } public boolean deleteRecord(AddressObject record) { int id = record.getId(); return deleteRecord(id); } public List<AddressObject> getListEntries() { List<AddressObject> listEntries = new ArrayList<AddressObject>(); Statement queryStatement = null; ResultSet results = null; try { queryStatement = dbConnection.createStatement(); results = queryStatement.executeQuery(strGetListEntries); while (results.next()) { int id = results.getInt(1); String gName = results.getString(2); String lName = results.getString(3); String fName = results.getString(4); String email = results.getString(5); String hPhone = results.getString(6); String wPhone = results.getString(7); String mPhone = results.getString(8); String dPin = results.getString(9); String a1 = results.getString(10); String a2 = results.getString(11); String city = results.getString(12); String state = results.getString(13); String zip = results.getString(14); String country = results.getString(15); String title = results.getString(16); String company = results.getString(17); AddressObject entry = new AddressObject(gName, lName, fName, email, hPhone, wPhone, mPhone, dPin, a1, a2, city, state, zip, country, title, company, id); listEntries.add(entry); } } catch (SQLException sqle) { System.out.println("Error getting List entires: " + sqle.getMessage()); } return listEntries; } public AddressObject getAddress(int index) { AddressObject address = null; try { stmtGetAddress.clearParameters(); stmtGetAddress.setInt(1, index); ResultSet result = stmtGetAddress.executeQuery(); if (result.next()) { int id = result.getInt("ID"); String groupName = result.getString("GROUPNAME"); String lastName = result.getString("LASTNAME"); String firstName = result.getString("FIRSTNAME"); String email = result.getString("EMAIL"); String homePhone = result.getString("HOMEPHONE"); String workPhone = result.getString("WORKPHONE"); String mobilePhone = result.getString("MOBILEPHONE"); String pin = result.getString("DEVICEPIN"); String add1 = result.getString("ADDRESS1"); String add2 = result.getString("ADDRESS2"); String city = result.getString("CITY"); String state = result.getString("STATE"); String zip = result.getString("ZIP"); String country = result.getString("COUNTRY"); String title = result.getString("TITLE"); String company = result.getString("COMPANY"); address = new AddressObject(groupName, lastName, firstName, email, homePhone, workPhone, mobilePhone, pin, add1, add2, city, state, zip, country, title, company, id); } } catch (SQLException sqle) { System.out.println("SQLEXCEPTION in ContactsDao.getAddress " + sqle.getMessage()); } return address; } public String getJSONString(String machineName, String confirmURL) { // {"Source": [ {"machinename":"localhost" }], // "Confirmation": [ {"url":"http://CI0000001380643/PushConfirmation/PUSHConfirmationHandler.ashx?MyMessage=ContactsAdded"}], String jsonString = "{\"Source\":[{\"machinename\":\"" + machineName + "\"}],"; jsonString += "\"Confirmation\":[{\"url\":\"" + confirmURL + "\"}],"; jsonString += "\"Contacts\":["; JSONObject m1 = new JSONObject(); m1.clear(); // JSONArray list1 = new JSONArray(); try { stmtGetListEntries.clearParameters(); ResultSet result = stmtGetListEntries.executeQuery(); while (result.next()) { m1.clear(); m1.put("GROUPNAME", result.getString("GROUPNAME")); m1.put("FIRSTNAME", result.getString("FIRSTNAME")); m1.put("LASTNAME", result.getString("LASTNAME")); m1.put("TITLE", result.getString("TITLE")); m1.put("COMPANY", result.getString("COMPANY")); m1.put("EMAIL", result.getString("EMAIL")); m1.put("HOMEPHONE", result.getString("HOMEPHONE")); m1.put("WORKPHONE", result.getString("WORKPHONE")); m1.put("MOBILEPHONE", result.getString("MOBILEPHONE")); m1.put("PIN", result.getString("DEVICEPIN")); m1.put("ADDRESS1", result.getString("ADDRESS1")); m1.put("ADDRESS2", result.getString("ADDRESS2")); m1.put("CITY", result.getString("CITY")); m1.put("STATE", result.getString("STATE")); m1.put("ZIPCODE", result.getString("ZIP")); m1.put("COUNTRY", result.getString("COUNTRY")); System.out.println("database record " + m1.toString()); jsonString += "" + m1.toString() + ","; } } catch (SQLException sqle) { System.out.println("SQLEXCEPTION in ContactsDao.getJSONString " + sqle.getMessage()); } //TBF: remove last comma correctly if (jsonString.endsWith(",")) { jsonString = jsonString.substring(0, jsonString.length() - 1); } jsonString += "]}"; System.out.println("\n\n\n JSON STRING " + jsonString); return jsonString; } private String objectName; private Connection dbConnection; private PreparedStatement stmtSaveNewRecord; private PreparedStatement stmtUpdateExistingRecord; private PreparedStatement stmtGetListEntries; private PreparedStatement stmtGetAddress; private PreparedStatement stmtDeleteAddress; private PreparedStatement stmtNukeAddresses; private static final String strGetAddress = "SELECT * FROM SAMPLE.CSV_PUSHCONTACTS " + "WHERE ID = ?"; private static final String strSaveAddress = "INSERT INTO SAMPLE.CSV_PUSHCONTACTS " + " (GROUPNAME, LASTNAME, FIRSTNAME, EMAIL, HOMEPHONE, WORKPHONE, " + " MOBILEPHONE, DEVICEPIN, ADDRESS1, ADDRESS2, CITY, STATE, ZIP, " + " COUNTRY, TITLE, COMPANY) " + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; private static final String strGetListEntries = "SELECT ID, GROUPNAME, LASTNAME, FIRSTNAME, EMAIL, HOMEPHONE, " + " WORKPHONE, MOBILEPHONE, DEVICEPIN, ADDRESS1, ADDRESS2, CITY, STATE, " + " ZIP, COUNTRY, TITLE, COMPANY " + " FROM SAMPLE.CSV_PUSHCONTACTS " + " ORDER BY LASTNAME ASC"; private static final String strUpdateAddress = "UPDATE SAMPLE.CSV_PUSHCONTACTS " + "SET GROUPNAME = ?, " + " LASTNAME = ?, " + " FIRSTNAME = ?, " + " EMAIL = ?, " + " HOMEPHONE = ?, " + " WORKPHONE = ?, " + " MOBILEPHONE = ?, " + " DEVICEPIN = ?, " + " ADDRESS1 = ?, " + " ADDRESS2 = ?, " + " CITY = ?, " + " STATE = ?, " + " ZIP = ?, " + " COUNTRY = ?, " + " TITLE = ?, " + " COMPANY = ? " + "WHERE ID = ?"; private static final String strDeleteAddress = "DELETE FROM SAMPLE.CSV_PUSHCONTACTS " + "WHERE ID = ?"; private static final String strNukeAddresses = "DELETE FROM SAMPLE.CSV_PUSHCONTACTS"; }