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; public class CompanyDao extends GenericDao<Company> { private static final String SQL_INSERT_COMPANY = "INSERT INTO COMPANY(name, vatid, telephone, mobile, email, www) VALUES(?, ?, ?, ?, ?, ?)"; private static final String SQL_DELETE_COMPANY = "DELETE FROM COMPANY WHERE id = ?"; private static final String SQL_FIND_COMPANY_BY_ID = "SELECT * FROM COMPANY WHERE id = ?"; private static final String SQL_DELETE_ALL_COMPANIES = "DELETE FROM COMPANY WHERE id > 0"; private static final String SQL_FIND_COMPANY_ALL = "SELECT * FROM COMPANY"; private static final String SQL_UPDATE_COMPANY = "UPDATE COMPANY SET name = ?, vatid = ?, telephone = ?, mobile = ?, email = ?, www = ? WHERE id = ?"; public CompanyDao(String databasefilepath) { super(databasefilepath); } @Override public List<Company> findAll() throws SQLException { List<Company> companies = new ArrayList<Company>(); try { init(); ResultSetHandler<List<Company>> h = new BeanListHandler<Company>(Company.class); companies = run.query(conn, SQL_FIND_COMPANY_ALL, h); // find addresses AddressDao adrDao = new AddressDao(); for (Company c : companies) { c.setAddresses(adrDao.findAllByCompanyId(c.getId(), run, conn)); } } catch (ClassNotFoundException e) { e.printStackTrace(); } finally { close(conn, st, generatedKeys); } return companies; } @Override public Company 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 Company find(Long id, QueryRunner run, Connection conn, PreparedStatement st, ResultSet generatedKeys) throws SQLException { Company p = null; ResultSetHandler<Company> h = new BeanHandler<Company>(Company.class); p = run.query(conn, SQL_FIND_COMPANY_BY_ID, h, id); if (p != null) { // find addresses AddressDao adrDao = new AddressDao(); p.setAddresses(adrDao.findAllByCompanyId(p.getId(), run, conn)); } return p; } @Override public void create(Company c) throws SQLException { try { init(); st = conn.prepareStatement(SQL_INSERT_COMPANY, Statement.RETURN_GENERATED_KEYS); st.setString(1, c.getName()); st.setString(2, c.getVatid()); st.setString(3, c.getTelephone()); st.setString(4, c.getTelephone()); st.setString(5, c.getTelephone()); st.setString(6, c.getTelephone()); // run the query int i = st.executeUpdate(); System.out.println("i: " + i); if (i == -1) { System.out.println("db error : " + SQL_INSERT_COMPANY); } generatedKeys = st.getGeneratedKeys(); if (generatedKeys.next()) { c.setId(generatedKeys.getLong(1)); } else { throw new SQLException("Creating company failed, no generated key obtained."); } // if the company creation was successfull, add addresses AddressDao adrDao = new AddressDao(); for (Address adr : c.getAddresses()) { adr.setCompany_id(c.getId()); adrDao.create(adr, run, conn, generatedKeys); if (adr.isMain()) { c.setAddress(adr); } } 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(Company c) throws SQLException { try { init(); st = conn.prepareStatement(SQL_UPDATE_COMPANY); st.setString(1, c.getName()); st.setString(2, c.getVatid()); 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_COMPANY); } // 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.setCompany_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 { // delete addresses Company toDel = find(id); AddressDao adrDao = new AddressDao(); try { init(); for (Address adr : toDel.getAddresses()) { adrDao.delete(adr.getId(), conn); } st = conn.prepareStatement(SQL_DELETE_COMPANY); 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_COMPANY); } 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<Company> companies = findAll(); for (Company c : companies) { delete(c.getId()); } } catch (Exception e) { // rollback the transaction but rethrow the exception to the calle e.printStackTrace(); throw new SQLException(e); } finally { close(conn, st, generatedKeys); } } }