Java tutorial
/** * Copyright 1999-2009 The Pegadi Team * * 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 org.pegadi.server.sources; import org.pegadi.server.SourceServer; import org.pegadi.sources.*; import org.pegadi.sqlsearch.SearchTerm; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.PreparedStatementCreator; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.support.GeneratedKeyHolder; import javax.sql.DataSource; import java.sql.*; import java.util.List; public class SourceServerImpl implements SourceServer { private final Logger log = LoggerFactory.getLogger(getClass()); private JdbcTemplate template; public void setDataSource(DataSource dataSource) { template = new JdbcTemplate(dataSource); } public int saveSource(Source source) { if (source.getID() == -1) return insertSource(source); else { updateSource(source); return -1; } } public void deleteSource(Source source) { template.update("DELETE FROM Source_person WHERE id=?", source.getID()); template.update("DELETE FROM Source_Phone_Number WHERE sourceID=?", source.getID()); template.update("DELETE FROM Source_Contact WHERE sourceID=?", source.getID()); template.update("DELETE FROM Source_Category_MemberShip WHERE sourceID=?", source.getID()); log.info("Deleted source with id={}", source.getID()); } private int insertSource(final Source source) { GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder(); template.update(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection conn) throws SQLException { PreparedStatement stmt = conn.prepareStatement( "INSERT INTO Source_person (name,email,url,address,postnumber,postaddress,position,notes,organization) VALUES (?,?,?,?,?,?,?,?,?)", new String[] { "ID" }); stmt.setString(1, source.getName()); stmt.setString(2, source.getEmail()); stmt.setString(3, source.getURL()); stmt.setString(4, source.getAddress()); stmt.setString(5, source.getPostNumber()); stmt.setString(6, source.getPostAddress()); stmt.setString(7, source.getPosition()); stmt.setString(8, source.getNotes()); stmt.setInt(9, source.getOrganizationID()); return stmt; } }, generatedKeyHolder); int id = generatedKeyHolder.getKey().intValue(); source.setID(id); updatePhoneNumbers(source); updateContacts(source); return id; } private void updateSource(final Source source) { template.update(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection conn) throws SQLException { PreparedStatement stmt = conn.prepareStatement( "UPDATE Source_person SET name=?,email=?,url=?,address=?,postnumber=?,postaddress=?,position=?,notes=?,organization=? WHERE ID=?"); stmt.setString(1, source.getName()); stmt.setString(2, source.getEmail()); stmt.setString(3, source.getURL()); stmt.setString(4, source.getAddress()); stmt.setString(5, source.getPostNumber()); stmt.setString(6, source.getPostAddress()); stmt.setString(7, source.getPosition()); stmt.setString(8, source.getNotes()); stmt.setInt(9, source.getOrganizationID()); stmt.setInt(10, source.getID()); return stmt; } }); updatePhoneNumbers(source); updateContacts(source); } public void updatePhoneNumbers(final Source source) { template.update("DELETE FROM Source_Phone_Number WHERE sourceID=?", source.getID()); for (PhoneNumber number : source.getPhoneNumbers()) { template.update("INSERT INTO Source_Phone_Number (type,number,description,sourceID) VALUES (?,?,?,?)", number.getType().name(), number.getNumber(), number.getDescription(), source.getID()); } } public void updateContacts(Source source) { final List<Contact> contacts = source.getContacts(); template.update("DELETE FROM Source_Contact WHERE sourceID=?", source.getID()); for (Contact contact : contacts) { template.update("INSERT INTO Source_Contact (sourceID,time_of_contact,notes) VALUES (?,?,?)", contact.getSourceID(), new Date(contact.getDate().getTime()), contact.getNotes()); } log.info("Inserting {} contacts for source {}", contacts.size(), source.getID()); } public List<Source> getSourcesBySearchTerm(SearchTerm term) { return template.query(term.getQuery("Source_person.ID"), new RowMapper<Source>() { @Override public Source mapRow(ResultSet rs, int rowNum) throws SQLException { return getSourceByID(rs.getInt(1)); } }); } public Source getSourceByID(int ID) { return template.queryForObject( "SELECT ID,name,email,url,address,postnumber,postaddress,position,notes,organization FROM Source_person WHERE ID=?", new RowMapper<Source>() { @Override public Source mapRow(ResultSet rs, int rowNum) throws SQLException { return getSourceFromResultSet(rs); } }, ID); } public List<Category> getSourceCategories() { return template.query("SELECT ID,name from Source_Category ORDER BY name", new RowMapper<Category>() { @Override public Category mapRow(ResultSet rs, int rowNum) throws SQLException { return getCategoryFromResultSet(rs); } }); } public List<Contact> getContactsBySource(int sourceID) { return template.query( "SELECT ID,sourceID,time_of_contact,notes FROM Source_Contact WHERE sourceID=? ORDER BY time_of_contact DESC,ID", new RowMapper<Contact>() { @Override public Contact mapRow(ResultSet rs, int rowNum) throws SQLException { return getContactFromResultSet(rs); } }, sourceID); } public List<Category> getCategoriesBySource(int sourceID) { return template.query( "SELECT Source_Category.ID,Source_Category.name FROM Source_person,Source_Category,Source_Category_MemberShip WHERE Source_person.ID=Source_Category_MemberShip.sourceID AND Source_Category.ID=Source_Category_MemberShip.categoryID AND Source_person.ID=?", new RowMapper<Category>() { @Override public Category mapRow(ResultSet rs, int rowNum) throws SQLException { return getCategoryFromResultSet(rs); } }, sourceID); } public void updateSourceCategoryMemberships(int sourceID, List<Category> members) { deleteAllCategoryMemberships(sourceID); for (Category member : members) insertCategoryMembership(sourceID, member.getID()); } private void deleteAllCategoryMemberships(int sourceID) { template.update("DELETE FROM Source_Category_MemberShip WHERE categoryID=?", sourceID); } private void insertCategoryMembership(int sourceID, int categoryID) { template.update("INSERT INTO Source_Category_MemberShip (sourceID,categoryID) VALUES (?,?)", sourceID, categoryID); } private Contact getContactFromResultSet(ResultSet rs) throws SQLException { Contact c = new Contact(); c.setID(rs.getInt(1)); c.setSourceID(rs.getInt(2)); c.setDate(rs.getDate(3)); c.setNotes(rs.getString(4)); return c; } private Category getCategoryFromResultSet(ResultSet rs) throws SQLException { Category c = new Category(); c.setID(rs.getInt(1)); c.setName(rs.getString(2)); return c; } private Source getSourceFromResultSet(ResultSet rs) throws SQLException { Source source = new Source(); source.setID(rs.getInt(1)); source.setName(rs.getString(2)); source.setEmail(rs.getString(3)); source.setURL(rs.getString(4)); source.setAddress(rs.getString(5)); source.setPostNumber(rs.getString(6)); source.setPostAddress(rs.getString(7)); source.setPosition(rs.getString(8)); source.setNotes(rs.getString(9)); source.setOrganizationID(rs.getInt(10)); source.setPhoneNumbers(getPhoneNumbersBySource(source.getID())); List<Contact> conts = getContactsBySource(source.getID()); if (conts.size() != 0) // No need to flood the server log with useless data log.info("Setting {} contacts for source {}", conts.size(), source.getName()); source.setContacts(conts); return source; } private List<PhoneNumber> getPhoneNumbersBySource(int ID) { return template.query( "SELECT Source_Phone_Number.ID, Source_Phone_Number.type, Source_Phone_Number.number, Source_Phone_Number.description FROM Source_Phone_Number WHERE sourceID = ? ORDER BY Source_Phone_Number.ID", new RowMapper<PhoneNumber>() { @Override public PhoneNumber mapRow(ResultSet rs, int rowNum) throws SQLException { PhoneNumberType t = PhoneNumberType.valueOf(rs.getString(2)); PhoneNumber p = new PhoneNumber(t, rs.getString(3), rs.getString(4)); p.setID(rs.getInt(1)); return p; } }, ID); } public void addSourceCategory(Category newCategory) { template.update("INSERT INTO Source_Category (name) VALUES (?)", newCategory.getName()); log.info("Added a new category: {}", newCategory.getName()); } public void updateSourceCategory(Category category) { template.update("UPDATE Source_Category SET name=? WHERE ID=?", category.getID(), category.getName()); log.info("Updated category: {}, {}", category.getID(), category.getName()); } public void deleteSourceCategory(Category category) { template.update("DELETE FROM Source_Category WHERE ID=?", category.getID()); template.update("DELETE FROM Source_Category_MemberShip WHERE categoryID=?", category.getID()); log.info("Deleted category: '{}' id={}", category.getName(), category.getID()); } }