Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package konditer.client.dao; import java.sql.Types; import java.util.ArrayList; import java.util.GregorianCalendar; import java.util.List; import java.util.Map; import java.util.logging.Level; import java.util.logging.Logger; import javax.sql.DataSource; import static konditer.client.Main.contactDao; import konditer.client.bean.Contact; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Repository; /** * * @author */ @Repository("contactDao") public class ContactDao implements ContactDaoInterface { // private DataSource dataSource = null; private JdbcTemplate jdbcTemplate = null; // public void setDataSource(DataSource dataSource) { // this.dataSource = dataSource; // } @Autowired public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; } @Override public void addContact(int contactId, int contactTypeId, int customerId, String contactData) { String SQL_QUERY = "INSERT INTO contacts (CONTACT_ID, CONTACT_TYPE_ID, CUSTOMER_ID, CONTACT_DATA) " + "VALUES ( ?, ?, ?, ? )"; int rowCount = 0; try { rowCount = jdbcTemplate.update(SQL_QUERY, new Object[] { contactId, contactTypeId, customerId, contactData }); Logger.getLogger(ContactDao.class.getName()).log(Level.SEVERE, " : {0} .", rowCount); System.out.println(contactDao.getContact(contactId).toString()); } catch (DataAccessException ex) { rowCount = 0; Logger.getLogger(ContactDao.class.getName()).log(Level.SEVERE, " . ?: {0} .", rowCount); } } @Override public Contact getContact(int contactId) { String SQL_QUERY = "SELECT CONTACT_ID, CONTACT_TYPE_ID, CUSTOMER_ID, CONTACT_DATA, TIMESTAMP " + "FROM contacts " + "WHERE CONTACT_ID = ?"; Contact contact = (Contact) jdbcTemplate.queryForObject(SQL_QUERY, new Object[] { contactId }, new BeanPropertyRowMapper(Contact.class)); return contact; } @Override public ArrayList<Contact> getAllContacts() { String SQL_QUERY = "SELECT CONTACT_ID, CONTACT_TYPE_ID, CUSTOMER_ID, CONTACT_DATA, TIMESTAMP " + " FROM contacts " + " ORDER BY CUSTOMER_ID"; List<Map<String, Object>> rows = jdbcTemplate.queryForList(SQL_QUERY); ArrayList<Contact> contactList = getContactsList(rows); return contactList; } @Override public ArrayList<Contact> getAllContacts(String contactData) { String SQL_QUERY = contactData; List<Map<String, Object>> rows = jdbcTemplate.queryForList(SQL_QUERY); ArrayList<Contact> contactList = getContactsList(rows); return contactList; } @Override public ArrayList<Contact> getAllContactsByContactTypeId(int contactTypeId) { throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates. } @Override public ArrayList<Contact> getAllContactsByContactData(String contactData) { String SQL_QUERY = "SELECT CONTACT_ID, CONTACT_TYPE_ID, CUSTOMER_ID, CONTACT_DATA, TIMESTAMP " + "FROM contacts " + "WHERE CONTACT_DATA like '%" + contactData + "%' " + "ORDER BY CONTACT_ID "; System.out.println("SQL_QUERY: {" + SQL_QUERY + "}"); List<Map<String, Object>> rows = jdbcTemplate.queryForList(SQL_QUERY); ArrayList<Contact> contactList = getContactsList(rows); return contactList; } @Override public ArrayList<Contact> getAllContactsByCustomerId(int customerId) { String SQL_QUERY = "SELECT CONTACT_ID, CONTACT_TYPE_ID, CUSTOMER_ID, CONTACT_DATA, TIMESTAMP " + "FROM contacts " + "WHERE CUSTOMER_ID = ? " + "ORDER BY CONTACT_ID "; List<Map<String, Object>> rows = jdbcTemplate.queryForList(SQL_QUERY, new Object[] { customerId }); ArrayList<Contact> contactList = getContactsList(rows); return contactList; } @Override public ArrayList<Contact> getAllPhones() { String SQL_QUERY = "SELECT CONTACT_ID, CONTACT_TYPE_ID, CUSTOMER_ID, CONTACT_DATA, TIMESTAMP " + "FROM contacts " + "WHERE CONTACT_TYPE_ID = (SELECT CONTACT_TYPE_ID FROM contact_types WHERE CONTACT_TYPE_NAME = 'phone') " + "ORDER BY CONTACT_ID "; List<Map<String, Object>> rows = jdbcTemplate.queryForList(SQL_QUERY); ArrayList<Contact> contactList = null; if (!rows.isEmpty()) { contactList = getContactsList(rows); } return contactList; } @Override public ArrayList<Contact> getAllPhonesByCustomerId(int customerId) { String SQL_QUERY = "SELECT CONTACT_ID, CONTACT_TYPE_ID, CUSTOMER_ID, CONTACT_DATA, TIMESTAMP " + "FROM contacts " + "WHERE CUSTOMER_ID = ? " + "AND CONTACT_TYPE_ID = (SELECT CONTACT_TYPE_ID FROM contact_types WHERE CONTACT_TYPE_NAME = 'phone') " + "ORDER BY CONTACT_ID "; List<Map<String, Object>> rows = jdbcTemplate.queryForList(SQL_QUERY, new Object[] { customerId }); ArrayList<Contact> contactList = null; if (!rows.isEmpty()) { contactList = getContactsList(rows); } return contactList; } @Override public int updateContact(int contactId, int newContactTypeId, int newCustomerId, String newContactData) { System.out.println(" ContactDao.updateContact c : " + "contactId: " + contactId + " | newContactTypeId: " + newContactTypeId + " | newCustomerId: " + newCustomerId + " | newContactData=" + newContactData); String SQL_QUERY = "UPDATE contacts " + "SET CONTACT_TYPE_ID = ?, " + "CUSTOMER_ID = ?, " + "CONTACT_DATA = ? " + "WHERE CONTACT_ID = ?"; int rowCount = 0; try { rowCount = jdbcTemplate.update(SQL_QUERY, new Object[] { newContactTypeId, newCustomerId, newContactData, contactId }, new int[] { Types.INTEGER, Types.INTEGER, Types.VARCHAR, Types.INTEGER }); Logger.getLogger(ContactDao.class.getName()).log(Level.SEVERE, " . ?: {0} .", rowCount + "\n" + contactDao.getContact(contactId).toString()); } catch (DataAccessException e) { rowCount = 0; Logger.getLogger(ContactDao.class.getName()).log(Level.SEVERE, " . ?: {0} .", rowCount + ";\nDataAccessException.message:{" + e.getMessage() + "}"); } return rowCount; } @Override public int deleteContact(int contactId) { String SQL_QUERY = "DELETE FROM contacts WHERE CONTACT_ID = ?"; int rowCount = jdbcTemplate.update(SQL_QUERY, new Object[] { contactId }, new int[] { Types.INTEGER }); Logger.getLogger(ContactDao.class.getName()).log(Level.SEVERE, " : {0} .", rowCount); return rowCount; } private ArrayList<Contact> getContactsList(List<Map<String, Object>> rows) { ArrayList<Contact> contactList = new ArrayList<>(); for (Map row : rows) { Contact contact = new Contact(); contact.setContactId(Integer.parseInt(String.valueOf(row.get("CONTACT_ID")))); contact.setContactTypeId(Integer.parseInt(String.valueOf(row.get("CONTACT_TYPE_ID")))); contact.setCustomerId(Integer.parseInt(String.valueOf(row.get("CUSTOMER_ID")))); contact.setContactData(String.valueOf(row.get("CONTACT_DATA"))); //// String[] dateArrFull = (String.valueOf(row.get("TIMESTAMP"))).split(" "); String[] dateArrDate = dateArrFull[0].split("-"); int year = Integer.parseInt(String.valueOf(dateArrDate[0]))/* -1900 */; int month = Integer.parseInt(String.valueOf(dateArrDate[1])) - 1; int day = Integer.parseInt(String.valueOf(dateArrDate[2])); String[] dateArrTime = dateArrFull[1].split(":"); int hour = Integer.parseInt(String.valueOf(dateArrTime[0])); int minut = Integer.parseInt(String.valueOf(dateArrTime[1])); double second = Double.parseDouble(String.valueOf(dateArrTime[2])); contact.setTimestamp(new GregorianCalendar(year, month, day, hour, minut, (int) second).getTime()); /// contactList.add(contact); } return contactList; } @Override public ArrayList<Contact> getTimestamp() { String SQL_QUERY = "SELECT CONTACT_ID, TIMESTAMP " + "FROM contacts "; List<Map<String, Object>> rows = jdbcTemplate.queryForList(SQL_QUERY); ArrayList<Contact> contactList = new ArrayList<>(); for (Map row : rows) { Contact contact = new Contact(); contact.setContactId(Integer.parseInt(String.valueOf(row.get("CONTACT_ID")))); //// String[] dateArrFull = (String.valueOf(row.get("TIMESTAMP"))).split(" "); String[] dateArrDate = dateArrFull[0].split("-"); int year = Integer.parseInt(String.valueOf(dateArrDate[0]))/* -1900 */; int month = Integer.parseInt(String.valueOf(dateArrDate[1])) - 1; int day = Integer.parseInt(String.valueOf(dateArrDate[2])); String[] dateArrTime = dateArrFull[1].split(":"); int hour = Integer.parseInt(String.valueOf(dateArrTime[0])); int minut = Integer.parseInt(String.valueOf(dateArrTime[1])); double second = Double.parseDouble(String.valueOf(dateArrTime[2])); contact.setTimestamp(new GregorianCalendar(year, month, day, hour, minut, (int) second).getTime()); /// contactList.add(contact); } return contactList; } }