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.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.Date; import java.util.List; 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.DocumentItem; import com.softberries.klerk.dao.to.IDocumentType; import com.softberries.klerk.dao.to.Person; import com.softberries.klerk.dao.to.Product; public class DocumentDao extends GenericDao<Document> { private static final String SQL_INSERT_DOCUMENT = "INSERT INTO DOCUMENT(title, notes, createdDate, transactionDate, dueDate, placeCreated, documentType, creator_id, buyer_id, seller_id) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; private static final String SQL_DELETE_DOCUMENT = "DELETE FROM DOCUMENT WHERE id = ?"; private static final String SQL_FIND_DOCUMENT_BY_ID = "SELECT * FROM DOCUMENT WHERE id = ?"; private static final String SQL_FIND_DOCUMENT_BY_TYPE = "SELECT * FROM DOCUMENT WHERE documentType = ?"; private static final String SQL_DELETE_ALL_DOCUMENTS = "DELETE FROM DOCUMENT WHERE id > 0"; private static final String SQL_FIND_DOCUMENT_ALL = "SELECT * FROM DOCUMENT"; private static final String SQL_UPDATE_DOCUMENT = "UPDATE DOCUMENT SET title = ?, notes = ?, createdDate = ?, transactionDate = ?, dueDate = ?, placeCreated = ?, documentType = ?, creator_id = ?, buyer_id = ?, seller_id = ? WHERE id = ?"; public DocumentDao(String databasefilepath) { super(databasefilepath); } @Override public List<Document> findAll() throws SQLException { List<Document> documents = new ArrayList<Document>(); try { init(); ResultSetHandler<List<Document>> h = new BeanListHandler<Document>(Document.class); documents = run.query(conn, SQL_FIND_DOCUMENT_ALL, h); //find items DocumentItemDao idao = new DocumentItemDao(this.filePath); CompanyDao cdao = new CompanyDao(this.filePath); PeopleDao pdao = new PeopleDao(this.filePath); for (Document d : documents) { d.setItems(idao.findAllByDocumentId(d.getId(), run, conn)); if (d.getDocumentType() == IDocumentType.INVOICE_PURCHASE) { d.setSeller(cdao.find(d.getSeller_id(), run, conn, st, generatedKeys)); } else { d.setBuyer(cdao.find(d.getBuyer_id(), run, conn, st, generatedKeys)); } d.setCreator(pdao.find(d.getCreator_id(), run, conn, st, generatedKeys)); } } catch (ClassNotFoundException e) { e.printStackTrace(); } finally { close(conn, st, generatedKeys); } return documents; } public List<Document> findAllByType(final int DOC_TYPE) throws SQLException { List<Document> documents = new ArrayList<Document>(); try { init(); ResultSetHandler<List<Document>> h = new BeanListHandler<Document>(Document.class); documents = run.query(conn, SQL_FIND_DOCUMENT_BY_TYPE, h, DOC_TYPE); //find items DocumentItemDao idao = new DocumentItemDao(this.filePath); CompanyDao cdao = new CompanyDao(this.filePath); PeopleDao pdao = new PeopleDao(this.filePath); for (Document d : documents) { d.setItems(idao.findAllByDocumentId(d.getId(), run, conn)); if (d.getDocumentType() == IDocumentType.INVOICE_PURCHASE) { d.setSeller(cdao.find(d.getSeller_id(), run, conn, st, generatedKeys)); } else { d.setBuyer(cdao.find(d.getBuyer_id(), run, conn, st, generatedKeys)); } d.setCreator(pdao.find(d.getCreator_id(), run, conn, st, generatedKeys)); } } catch (ClassNotFoundException e) { e.printStackTrace(); } finally { close(conn, st, generatedKeys); } return documents; } @Override public Document find(Long id) throws SQLException { Document d = null; try { init(); ResultSetHandler<Document> h = new BeanHandler<Document>(Document.class); d = run.query(conn, SQL_FIND_DOCUMENT_BY_ID, h, id); //find items, creator, seller and buyer DocumentItemDao idao = new DocumentItemDao(this.filePath); CompanyDao cdao = new CompanyDao(this.filePath); PeopleDao pdao = new PeopleDao(this.filePath); d.setItems(idao.findAllByDocumentId(d.getId(), run, conn)); if (d.getDocumentType() == IDocumentType.INVOICE_PURCHASE) { d.setSeller(cdao.find(d.getSeller_id(), run, conn, st, generatedKeys)); } else { d.setBuyer(cdao.find(d.getBuyer_id(), run, conn, st, generatedKeys)); } d.setCreator(pdao.find(d.getCreator_id(), run, conn, st, generatedKeys)); } catch (ClassNotFoundException e) { e.printStackTrace(); } finally { close(conn, st, generatedKeys); } return d; } @Override public void create(Document d) throws SQLException { try { init(); st = conn.prepareStatement(SQL_INSERT_DOCUMENT, Statement.RETURN_GENERATED_KEYS); st.setString(1, d.getTitle()); st.setString(2, d.getNotes()); st.setDate(3, new java.sql.Date(d.getCreatedDate().getTime())); st.setDate(4, new java.sql.Date(d.getTransactionDate().getTime())); st.setDate(5, new java.sql.Date(d.getDueDate().getTime())); st.setString(6, d.getPlaceCreated()); st.setInt(7, d.getDocumentType()); st.setLong(8, d.getCreator().getId()); st.setLong(9, d.getBuyer().getId()); st.setLong(10, d.getSeller().getId()); // run the query int i = st.executeUpdate(); System.out.println("i: " + i); if (i == -1) { System.out.println("db error : " + SQL_INSERT_DOCUMENT); } generatedKeys = st.getGeneratedKeys(); if (generatedKeys.next()) { d.setId(generatedKeys.getLong(1)); } else { throw new SQLException("Creating document failed, no generated key obtained."); } //if the document creation was successful, add document items DocumentItemDao idao = new DocumentItemDao(this.filePath); for (DocumentItem di : d.getItems()) { di.setDocument_id(d.getId()); idao.create(di, 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(Document d) throws SQLException { try { init(); st = conn.prepareStatement(SQL_UPDATE_DOCUMENT, Statement.RETURN_GENERATED_KEYS); st.setString(1, d.getTitle()); st.setString(2, d.getNotes()); st.setDate(3, new java.sql.Date(d.getCreatedDate().getTime())); st.setDate(4, new java.sql.Date(d.getTransactionDate().getTime())); st.setDate(5, new java.sql.Date(d.getDueDate().getTime())); st.setString(6, d.getPlaceCreated()); st.setInt(7, d.getDocumentType()); st.setLong(8, d.getCreator().getId()); st.setLong(9, d.getBuyer().getId()); st.setLong(10, d.getSeller().getId()); st.setLong(11, d.getId()); // run the query int i = st.executeUpdate(); System.out.println("i: " + i); if (i == -1) { System.out.println("db error : " + SQL_UPDATE_DOCUMENT); } //delete unused items DocumentItemDao idao = new DocumentItemDao(this.filePath); List<DocumentItem> toDel = new ArrayList<DocumentItem>(); if (d.getId() != null) { List<DocumentItem> existingItems = idao.findAllByDocumentId(d.getId(), run, conn); System.out.println("Existing items: " + existingItems.size()); for (DocumentItem di : existingItems) { System.out.println( "di id: " + di.getId().longValue() + ", di docid: " + di.getDocument_id().longValue()); System.out.println(printAllDIs(d.getItems())); if (!d.getItems().contains(di)) { System.out.println("add to remove: " + di); toDel.add(di); } } } for (DocumentItem di : toDel) { idao.delete(di.getId(), conn); } //update items for (DocumentItem di : d.getItems()) { if (di.getId() != null && di.getId() > 0) { //update System.out.println("update: " + di); idao.update(di, run, conn); } else {//create di.setDocument_id(d.getId()); idao.create(di, run, conn, generatedKeys); System.out.println("insert: " + di); } } 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); } } private String printAllDIs(List<DocumentItem> items) { StringBuilder builder = new StringBuilder(); for (DocumentItem di : items) { Long diId = di.getId(); String temp = "di id: " + diId + ", di docid: " + di.getDocument_id().longValue(); builder.append(temp); builder.append("\n"); } return builder.toString(); } @Override public void delete(Long id) throws SQLException { //delete items Document toDel = find(id); DocumentItemDao iDao = new DocumentItemDao(this.filePath); try { init(); for (DocumentItem di : toDel.getItems()) { iDao.delete(di.getId(), conn); } st = conn.prepareStatement(SQL_DELETE_DOCUMENT); 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_DOCUMENT); } 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<Document> docs = findAll(); for (Document d : docs) { delete(d.getId()); } } 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); } } }