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.DocumentItem; public class DocumentItemDao { private String path; public DocumentItemDao(String path) { this.path = path; } private static final String SQL_INSERT_DOCUMENTITEM = "INSERT INTO DOCUMENTITEM(priceNetSingle, priceGrossSingle, priceTaxSingle, priceNetAll, priceGrossAll, priceTaxAll, taxValue, quantity, product_id, document_id, product_name) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; private static final String SQL_DELETE_DOCUMENTITEM = "DELETE FROM DOCUMENTITEM WHERE id = ?"; private static final String SQL_FIND_DOCUMENTITEM_BY_ID = "SELECT * FROM DOCUMENTITEM WHERE id = ?"; private static final String SQL_DELETE_ALL_DOCUMENTITEMS = "DELETE FROM DOCUMENTITEM WHERE id > 0"; private static final String SQL_FIND_DOCUMENTITEM_ALL = "SELECT * FROM DOCUMENTITEM"; private static final String SQL_FIND_DOCUMENTITEM_ALL_BY_DOCUMENT_ID = "SELECT * FROM DOCUMENTITEM WHERE DOCUMENT_ID = ?"; private static final String SQL_UPDATE_DOCUMENTITEM = "UPDATE DOCUMENTITEM SET priceNetSingle = ?, priceGrossSingle = ?, priceTaxSingle = ?, priceNetAll = ?, priceGrossAll = ?, priceTaxAll = ?, taxValue = ?, quantity = ?, product_id = ?, document_id = ?, product_name = ? WHERE id = ?"; public List<DocumentItem> findAll(QueryRunner run, Connection conn) throws SQLException { List<DocumentItem> items = new ArrayList<DocumentItem>(); ResultSetHandler<List<DocumentItem>> h = new BeanListHandler<DocumentItem>(DocumentItem.class); items = run.query(conn, SQL_FIND_DOCUMENTITEM_ALL, h); ProductDao pdao = new ProductDao(path); for (DocumentItem di : items) { di.setProduct(pdao.find(di.getProduct_id(), run, conn)); } return items; } public List<DocumentItem> findAllByDocumentId(Long docId, QueryRunner run, Connection conn) throws SQLException { List<DocumentItem> items = new ArrayList<DocumentItem>(); ResultSetHandler<List<DocumentItem>> h = new BeanListHandler<DocumentItem>(DocumentItem.class); items = run.query(conn, SQL_FIND_DOCUMENTITEM_ALL_BY_DOCUMENT_ID, h, docId); ProductDao pdao = new ProductDao(this.path); for (DocumentItem di : items) { di.setProduct(pdao.find(di.getProduct_id(), run, conn)); } return items; } public DocumentItem find(Long id, QueryRunner run, Connection conn) throws SQLException { DocumentItem p = null; ResultSetHandler<DocumentItem> h = new BeanHandler<DocumentItem>(DocumentItem.class); p = run.query(conn, SQL_FIND_DOCUMENTITEM_BY_ID, h, id); ProductDao pdao = new ProductDao(this.path); p.setProduct(pdao.find(p.getProduct_id(), run, conn)); return p; } //priceNetSingle, priceGrossSingle, priceTaxSingle, priceNetAll, priceGrossAll, priceTaxAll, taxValue, quantity, product_id, document_id public void create(DocumentItem c, QueryRunner run, Connection conn, ResultSet generatedKeys) throws SQLException { PreparedStatement st = conn.prepareStatement(SQL_INSERT_DOCUMENTITEM, Statement.RETURN_GENERATED_KEYS); st.setString(1, c.getPriceNetSingle()); st.setString(2, c.getPriceGrossSingle()); st.setString(3, c.getPriceTaxSingle()); st.setString(4, c.getPriceNetAll()); st.setString(5, c.getPriceGrossAll()); st.setString(6, c.getPriceTaxAll()); st.setString(7, c.getTaxValue()); st.setString(8, c.getQuantity()); if (c.getProduct().getId().longValue() == 0 && c.getDocument_id().longValue() == 0) { throw new SQLException( "For DocumentItem corresponding product and document it belongs to need to be specified"); } if (c.getProduct().getId() != 0) { st.setLong(9, c.getProduct().getId()); } else { st.setNull(9, java.sql.Types.NUMERIC); } if (c.getDocument_id().longValue() != 0) { st.setLong(10, c.getDocument_id()); } else { st.setNull(10, java.sql.Types.NUMERIC); } st.setString(11, c.getProduct().getName()); // run the query int i = st.executeUpdate(); System.out.println("i: " + i); if (i == -1) { System.out.println("db error : " + SQL_INSERT_DOCUMENTITEM); } generatedKeys = st.getGeneratedKeys(); if (generatedKeys.next()) { c.setId(generatedKeys.getLong(1)); } else { throw new SQLException("Creating user failed, no generated key obtained."); } } public void update(DocumentItem c, QueryRunner run, Connection conn) throws SQLException { PreparedStatement st = conn.prepareStatement(SQL_UPDATE_DOCUMENTITEM); st.setString(1, c.getPriceNetSingle()); st.setString(2, c.getPriceGrossSingle()); st.setString(3, c.getPriceTaxSingle()); st.setString(4, c.getPriceNetAll()); st.setString(5, c.getPriceGrossAll()); st.setString(6, c.getPriceTaxAll()); st.setString(7, c.getTaxValue()); st.setString(8, c.getQuantity()); if (c.getProduct().getId().longValue() == 0 && c.getDocument_id().longValue() == 0) { throw new SQLException( "For DocumentItem corresponding product and document it belongs to need to be specified"); } if (c.getProduct().getId() != 0) { st.setLong(9, c.getProduct().getId()); } else { st.setNull(9, java.sql.Types.NUMERIC); } if (c.getDocument_id().longValue() != 0) { st.setLong(10, c.getDocument_id()); } else { st.setNull(10, java.sql.Types.NUMERIC); } st.setString(11, c.getProduct().getName()); st.setLong(12, c.getId()); // run the query int i = st.executeUpdate(); System.out.println("i: " + i); if (i == -1) { System.out.println("db error : " + SQL_UPDATE_DOCUMENTITEM); } } public void delete(Long id, Connection conn) throws SQLException { PreparedStatement st = conn.prepareStatement(SQL_DELETE_DOCUMENTITEM); 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_DOCUMENTITEM); } } public void deleteAll(Connection conn) throws SQLException { PreparedStatement st = conn.prepareStatement(SQL_DELETE_ALL_DOCUMENTITEMS); int i = st.executeUpdate(); System.out.println("i: " + i); if (i == -1) { System.out.println("db error : " + SQL_DELETE_ALL_DOCUMENTITEMS); } } }