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; public class AddressDao { private static final String SQL_INSERT_ADDRESS = "INSERT INTO ADDRESS(country, city, street, postCode, houseNumber, flatNumber, notes, main, person_id, company_id) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; private static final String SQL_DELETE_ADDRESS = "DELETE FROM ADDRESS WHERE id = ?"; private static final String SQL_FIND_ADDRESS_BY_ID = "SELECT * FROM ADDRESS WHERE id = ?"; private static final String SQL_DELETE_ALL_ADDRESSES = "DELETE FROM ADDRESS WHERE id > 0"; private static final String SQL_FIND_ADDRESS_ALL = "SELECT * FROM ADDRESS"; private static final String SQL_FIND_ADDRESS_ALL_BY_COMPANY_ID = "SELECT * FROM ADDRESS WHERE COMPANY_ID = ?"; private static final String SQL_FIND_ADDRESS_ALL_BY_PERSON_ID = "SELECT * FROM ADDRESS WHERE PERSON_ID = ?"; private static final String SQL_UPDATE_ADDRESS = "UPDATE ADDRESS SET country = ?, city = ?, street = ?, postCode = ?, houseNumber = ?, flatNumber = ?, notes = ?, main = ?, person_id = ?, company_id = ? WHERE id = ?"; public List<Address> findAll(QueryRunner run, Connection conn) throws SQLException { List<Address> companies = new ArrayList<Address>(); ResultSetHandler<List<Address>> h = new BeanListHandler<Address>(Address.class); companies = run.query(conn, SQL_FIND_ADDRESS_ALL, h); return companies; } public List<Address> findAllByCompanyId(Long companyId, QueryRunner run, Connection conn) throws SQLException { List<Address> addresses = new ArrayList<Address>(); ResultSetHandler<List<Address>> h = new BeanListHandler<Address>(Address.class); addresses = run.query(conn, SQL_FIND_ADDRESS_ALL_BY_COMPANY_ID, h, companyId); return addresses; } public List<Address> findAllByPersonId(Long companyId, QueryRunner run, Connection conn) throws SQLException { List<Address> addresses = new ArrayList<Address>(); ResultSetHandler<List<Address>> h = new BeanListHandler<Address>(Address.class); addresses = run.query(conn, SQL_FIND_ADDRESS_ALL_BY_PERSON_ID, h, companyId); return addresses; } public Address find(Long id, QueryRunner run, Connection conn) throws SQLException { Address p = null; ResultSetHandler<Address> h = new BeanHandler<Address>(Address.class); p = run.query(conn, SQL_FIND_ADDRESS_BY_ID, h, id); return p; } public void create(Address c, QueryRunner run, Connection conn, ResultSet generatedKeys) throws SQLException { PreparedStatement st = conn.prepareStatement(SQL_INSERT_ADDRESS, Statement.RETURN_GENERATED_KEYS); st.setString(1, c.getCountry()); st.setString(2, c.getCity()); st.setString(3, c.getStreet()); st.setString(4, c.getPostCode()); st.setString(5, c.getHouseNumber()); st.setString(6, c.getFlatNumber()); st.setString(7, c.getNotes()); st.setBoolean(8, c.isMain()); if (c.getPerson_id().longValue() == 0 && c.getCompany_id().longValue() == 0) { throw new SQLException("For Address either Person or Company needs to be specified"); } if (c.getPerson_id().longValue() != 0) { st.setLong(9, c.getPerson_id()); } else { st.setNull(9, java.sql.Types.NUMERIC); } if (c.getCompany_id().longValue() != 0) { st.setLong(10, c.getCompany_id()); } else { st.setNull(10, java.sql.Types.NUMERIC); } // run the query int i = st.executeUpdate(); System.out.println("i: " + i); if (i == -1) { System.out.println("db error : " + SQL_INSERT_ADDRESS); } generatedKeys = st.getGeneratedKeys(); if (generatedKeys.next()) { c.setId(generatedKeys.getLong(1)); } else { throw new SQLException("Creating address failed, no generated key obtained."); } } public void update(Address c, QueryRunner run, Connection conn) throws SQLException { PreparedStatement st = conn.prepareStatement(SQL_UPDATE_ADDRESS); st.setString(1, c.getCountry()); st.setString(2, c.getCity()); st.setString(3, c.getStreet()); st.setString(4, c.getPostCode()); st.setString(5, c.getHouseNumber()); st.setString(6, c.getFlatNumber()); st.setString(7, c.getNotes()); st.setBoolean(8, c.isMain()); if (c.getPerson_id() == null && c.getCompany_id() == null) { throw new SQLException("Either Person or Company needs to be specified"); } if (c.getPerson_id().longValue() != 0) { st.setLong(9, c.getPerson_id()); } else { st.setNull(9, java.sql.Types.NUMERIC); } if (c.getCompany_id().longValue() != 0) { st.setLong(10, c.getCompany_id()); } else { st.setNull(10, java.sql.Types.NUMERIC); } st.setLong(11, c.getId()); // run the query int i = st.executeUpdate(); System.out.println("i: " + i); if (i == -1) { System.out.println("db error : " + SQL_UPDATE_ADDRESS); } } public void delete(Long id, Connection conn) throws SQLException { PreparedStatement st = conn.prepareStatement(SQL_DELETE_ADDRESS); 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_ADDRESS); } } public void deleteAll(Connection conn) throws SQLException { PreparedStatement st = conn.prepareStatement(SQL_DELETE_ALL_ADDRESSES); int i = st.executeUpdate(); System.out.println("i: " + i); if (i == -1) { System.out.println("db error : " + SQL_DELETE_ALL_ADDRESSES); } } }