Java tutorial
/******************************************************************************* * Copyright (c) 2011 Softberries Krzysztof Grajek. * All rights reserved. This program and the accompanying materials * are made available under the terms of the Eclipse Public License v1.0 * which accompanies this distribution, and is available at * http://www.eclipse.org/legal/epl-v10.html * * Contributors: * Softberries Krzysztof Grajek - initial API and implementation ******************************************************************************/ package com.softberries.klerk.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.ResultSetHandler; import org.apache.commons.dbutils.handlers.BeanHandler; import org.apache.commons.dbutils.handlers.BeanListHandler; import com.softberries.klerk.dao.to.Address; import com.softberries.klerk.dao.to.Company; import com.softberries.klerk.dao.to.Document; import com.softberries.klerk.dao.to.Person; public class PeopleDao extends GenericDao<Person> { private static final String SQL_INSERT_PERSON = "INSERT INTO PERSON(firstname, lastname, telephone, mobile, email, www) VALUES(?, ?, ?, ?, ?, ?)"; private static final String SQL_DELETE_PERSON = "DELETE FROM PERSON WHERE id = ?"; private static final String SQL_FIND_PERSON_BY_ID = "SELECT * FROM PERSON WHERE id = ?"; private static final String SQL_DELETE_ALL_PEOPLE = "DELETE FROM PERSON WHERE id > 0"; private static final String SQL_FIND_PERSON_ALL = "SELECT * FROM PERSON"; private static final String SQL_UPDATE_PERSON = "UPDATE PERSON SET firstname = ?, lastname = ?, telephone = ?, mobile = ?, email = ?, www = ? WHERE id = ?"; public PeopleDao(String databasefilepath) { super(databasefilepath); } @Override public List<Person> findAll() throws SQLException { List<Person> people = new ArrayList<Person>(); try { init(); ResultSetHandler<List<Person>> h = new BeanListHandler<Person>(Person.class); people = run.query(conn, SQL_FIND_PERSON_ALL, h); // find addresses AddressDao adrDao = new AddressDao(); for (Person c : people) { c.setAddresses(adrDao.findAllByPersonId(c.getId(), run, conn)); } } catch (ClassNotFoundException e) { e.printStackTrace(); } finally { close(conn, st, generatedKeys); } return people; } @Override public Person find(Long id) throws SQLException { try { init(); return this.find(id, run, conn, st, generatedKeys); } catch (ClassNotFoundException e) { e.printStackTrace(); } finally { close(conn, st, generatedKeys); } return null; } /** * Used by other DAO's, reuses existing connection, runner and result set * objects * * @param id * @param run * @param conn * @param st * @param generatedKeys * @return * @throws SQLException */ public Person find(Long id, QueryRunner run, Connection conn, PreparedStatement st, ResultSet generatedKeys) throws SQLException { Person p = null; ResultSetHandler<Person> h = new BeanHandler<Person>(Person.class); p = run.query(conn, SQL_FIND_PERSON_BY_ID, h, id); if (p != null) { // find addresses AddressDao adrDao = new AddressDao(); p.setAddresses(adrDao.findAllByPersonId(p.getId(), run, conn)); } return p; } @Override public void create(Person c) throws SQLException { try { init(); st = conn.prepareStatement(SQL_INSERT_PERSON, Statement.RETURN_GENERATED_KEYS); st.setString(1, c.getFirstName()); st.setString(2, c.getLastName()); st.setString(3, c.getTelephone()); st.setString(4, c.getMobile()); st.setString(5, c.getEmail()); st.setString(6, c.getWww()); // run the query int i = st.executeUpdate(); System.out.println("i: " + i); if (i == -1) { System.out.println("db error : " + SQL_INSERT_PERSON); } generatedKeys = st.getGeneratedKeys(); if (generatedKeys.next()) { c.setId(generatedKeys.getLong(1)); } else { throw new SQLException("Creating user failed, no generated key obtained."); } // if the person creation was successfull, add addresses AddressDao adrDao = new AddressDao(); for (Address adr : c.getAddresses()) { adr.setPerson_id(c.getId()); adrDao.create(adr, run, conn, generatedKeys); } conn.commit(); } catch (Exception e) { // rollback the transaction but rethrow the exception to the caller conn.rollback(); e.printStackTrace(); throw new SQLException(e); } finally { close(conn, st, generatedKeys); } } @Override public void update(Person c) throws SQLException { try { init(); st = conn.prepareStatement(SQL_UPDATE_PERSON); st.setString(1, c.getFirstName()); st.setString(2, c.getLastName()); st.setString(3, c.getTelephone()); st.setString(4, c.getMobile()); st.setString(5, c.getEmail()); st.setString(6, c.getWww()); st.setLong(7, c.getId()); // run the query int i = st.executeUpdate(); System.out.println("i: " + i); if (i == -1) { System.out.println("db error : " + SQL_UPDATE_PERSON); } // delete unused addresses AddressDao adrDao = new AddressDao(); List<Address> toDel = new ArrayList<Address>(); if (c.getId() != null) { List<Address> existingAddresses = adrDao.findAllByCompanyId(c.getId(), run, conn); for (Address adr : existingAddresses) { if (!c.getAddresses().contains(adr)) { toDel.add(adr); } } } for (Address adr : toDel) { adrDao.delete(adr.getId(), conn); } // update addresses for (Address adr : c.getAddresses()) { if (adr.getId() != null && adr.getId() > 0) { // update adrDao.update(adr, run, conn); } else {// create adr.setPerson_id(c.getId()); adrDao.create(adr, run, conn, generatedKeys); } } conn.commit(); } catch (Exception e) { // rollback the transaction but rethrow the exception to the caller conn.rollback(); e.printStackTrace(); throw new SQLException(e); } finally { close(conn, st, generatedKeys); } } @Override public void delete(Long id) throws SQLException { Person toDel = find(id); AddressDao adrDao = new AddressDao(); try { init(); for (Address adr : toDel.getAddresses()) { adrDao.delete(adr.getId(), conn); } st = conn.prepareStatement(SQL_DELETE_PERSON); st.setLong(1, id); // run the query int i = st.executeUpdate(); System.out.println("i: " + i); if (i == -1) { System.out.println("db error : " + SQL_DELETE_PERSON); } conn.commit(); } catch (Exception e) { // rollback the transaction but rethrow the exception to the caller conn.rollback(); e.printStackTrace(); throw new SQLException(e); } finally { close(conn, st, generatedKeys); } } @Override public void deleteAll() throws SQLException { try { List<Person> people = findAll(); for (Person p : people) { delete(p.getId()); } } catch (Exception e) { // rollback the transaction but rethrow the exception to the caller e.printStackTrace(); throw new SQLException(e); } finally { close(conn, st, generatedKeys); } } }