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.Date; import java.util.GregorianCalendar; import java.util.List; import java.util.Map; import java.util.logging.Level; import java.util.logging.Logger; import javax.activation.DataSource; import static konditer.client.Main.ordereDao; import konditer.client.bean.Ordere; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Repository; /** * * @author */ @Repository("ordereDao") public class OrdereDao implements OrdereDaoInterface { 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 addOrdere(int orderId, int customerId, int orderStatusId, int deliveryId, Date orderDateIncome, Date orderDateEnd, double orderCakePrice, double orderDeliveryPrice, double orderWeight, String orderInsidesId, String orderInfo) { String SQL_QUERY = "INSERT INTO orders ( ORDER_ID, " + "CUSTOMER_ID, " + "ORDER_STATUS_ID, " + "DELIVERY_ID, " + "ORDER_DATE_INCOME, " + "ORDER_DATE_END, " + "ORDER_CAKE_PRICE, " + "ORDER_DELIVERY_PRICE, " + "ORDER_WEIGHT, " + "ORDER_INSIDES_ID, " + "ORDER_INFO ) " + "VALUES (?,?,?,?,?,?,?,?,?,?,?)"; int rowCount = jdbcTemplate.update(SQL_QUERY, new Object[] { orderId, customerId, orderStatusId, deliveryId, orderDateIncome, orderDateEnd, orderCakePrice, orderDeliveryPrice, orderWeight, orderInsidesId, orderInfo }, new int[] { Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.TIMESTAMP, Types.TIMESTAMP, Types.DOUBLE, Types.DOUBLE, Types.DOUBLE, Types.VARCHAR, Types.VARCHAR }); Logger.getLogger(CakeDao.class.getName()).log(Level.SEVERE, " : {0} .", rowCount); System.out.println(ordereDao.getOrdere(orderId).toString()); } @Override public Ordere getOrdere(int orderId) { String SQL_QUERY = "SELECT ORDER_ID, " + "CUSTOMER_ID, " + "ORDER_STATUS_ID, " + "DELIVERY_ID, " + "ORDER_DATE_INCOME, " + "ORDER_DATE_END, " + "ORDER_CAKE_PRICE, " + "ORDER_DELIVERY_PRICE, " + "ORDER_WEIGHT, " + "ORDER_INSIDES_ID, " + "ORDER_INFO, TIMESTAMP " + "FROM orders " + "WHERE ORDER_ID = ?"; Ordere order = (Ordere) jdbcTemplate.queryForObject(SQL_QUERY, new Object[] { orderId }, new BeanPropertyRowMapper(Ordere.class)); return order; } @Override public ArrayList<Integer> getOrdereInsidesId(int orderId) { ArrayList<Integer> orderInsidesId = new ArrayList<>(); String SQL_QUERY = "SELECT ORDER_ID, CUSTOMER_ID, DELIVERY_ID, ORDER_STATUS_ID, " + "ORDER_DATE_INCOME, ORDER_DATE_END, ORDER_INFO, " + "ORDER_CAKE_PRICE, ORDER_DELIVERY_PRICE, ORDER_WEIGHT, ORDER_INSIDES_ID, TIMESTAMP " + "FROM orders " + "ORDER BY ORDER_ID DESC"; List<Map<String, Object>> rows = jdbcTemplate.queryForList(SQL_QUERY); ArrayList<Ordere> orderList = getOrdersList(rows); for (Ordere or : orderList) { orderInsidesId.add(or.getOrderId()); } return orderInsidesId; } @Override public ArrayList<Ordere> getAllOrders() { String SQL_QUERY = "SELECT ORDER_ID, CUSTOMER_ID, DELIVERY_ID, ORDER_STATUS_ID, " + "ORDER_DATE_INCOME, ORDER_DATE_END, ORDER_INFO, " + "ORDER_CAKE_PRICE, ORDER_DELIVERY_PRICE, ORDER_WEIGHT, ORDER_INSIDES_ID, TIMESTAMP " + "FROM orders " + "ORDER BY ORDER_ID DESC"; List<Map<String, Object>> rows = jdbcTemplate.queryForList(SQL_QUERY); ArrayList<Ordere> orderList = getOrdersList(rows); return orderList; } @Override public ArrayList<Ordere> getAllOrdersOpen() { String SQL_QUERY = "SELECT ORDER_ID, CUSTOMER_ID, DELIVERY_ID, ORDER_STATUS_ID, " + "ORDER_DATE_INCOME, ORDER_DATE_END, ORDER_INFO, " + "ORDER_CAKE_PRICE, ORDER_DELIVERY_PRICE, ORDER_WEIGHT, ORDER_INSIDES_ID, TIMESTAMP " + "FROM orders " + "WHERE ORDER_STATUS_ID IN (1, 2) " + "ORDER BY ORDER_DATE_END ASC"; List<Map<String, Object>> rows = jdbcTemplate.queryForList(SQL_QUERY); ArrayList<Ordere> orderList = getOrdersList(rows); return orderList; } @Override public ArrayList<Ordere> getAllOrders(String searchValue) { String SQL_QUERY = searchValue; List<Map<String, Object>> rows = jdbcTemplate.queryForList(SQL_QUERY); ArrayList<Ordere> orderList = getOrdersList(rows); return orderList; } @Override public ArrayList<Ordere> getAllOrdersByCustomerId(int customerId) { String SQL_QUERY = "SELECT ORDER_ID, CUSTOMER_ID, DELIVERY_ID, ORDER_STATUS_ID, " + "ORDER_DATE_INCOME, ORDER_DATE_END, ORDER_INFO, " + "ORDER_CAKE_PRICE, ORDER_DELIVERY_PRICE, ORDER_WEIGHT, ORDER_INSIDES_ID, TIMESTAMP " + "FROM orders " + "WHERE CUSTOMER_ID = ? " + "ORDER BY ORDER_DATE_END ASC"; List<Map<String, Object>> rows = jdbcTemplate.queryForList(SQL_QUERY, new Object[] { customerId, }); ArrayList<Ordere> orderList = getOrdersList(rows); return orderList; } @Override public ArrayList<Ordere> getAllOrdersByOrderStatusId(int orderStatusId) { throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates. } @Override public ArrayList<Ordere> getAllOrdersByDate(String orderDate) { String SQL_QUERY = "SELECT ORDER_ID, CUSTOMER_ID, DELIVERY_ID, ORDER_STATUS_ID, " + "ORDER_DATE_INCOME, ORDER_DATE_END, ORDER_INFO, " + "ORDER_CAKE_PRICE, ORDER_DELIVERY_PRICE, ORDER_WEIGHT, ORDER_INSIDES_ID, TIMESTAMP " + "FROM orders " + "WHERE date(ORDER_DATE_END) = ? " + "ORDER BY ORDER_DATE_END ASC"; List<Map<String, Object>> rows = jdbcTemplate.queryForList(SQL_QUERY, new Object[] { orderDate }); ArrayList<Ordere> orderList = getOrdersList(rows); return orderList; } @Override public int getCountOrdersByDate(String orderDate) { int count; String SQL_QUERY = "SELECT count(*) FROM orders " + " WHERE date(ORDER_DATE_END) = ? " + " AND ORDER_STATUS_ID IN (1,2,3) "; count = (int) jdbcTemplate.queryForInt(SQL_QUERY, new Object[] { orderDate }); return count; } @Override public ArrayList<Ordere> searchAllOrders(String sqlQuery) { String SQL_QUERY = sqlQuery; List<Map<String, Object>> rows = jdbcTemplate.queryForList(SQL_QUERY); ArrayList<Ordere> orderList = getOrdersList(rows); return orderList; } @Override public int updateOrdere(int orderId, int newCustomerId, int newDeliveryId, int newOrderStatusId, Date newOrderDateIncome, Date newOrderDateEnd, double orderCakePrice, double newOrderDeliveryPrice, double newOrderWeight, String orderInsidesId, String newOrderInfo) { String SQL_QUERY = "UPDATE orders " + "SET CUSTOMER_ID = ?, " + "DELIVERY_ID = ?, " + "ORDER_STATUS_ID = ?, " + "ORDER_DATE_INCOME = ?, " + "ORDER_DATE_END = ?, " + "ORDER_INFO = ?, " + "ORDER_CAKE_PRICE = ?, " + "ORDER_DELIVERY_PRICE = ?, " + "ORDER_WEIGHT = ?, " + "ORDER_INSIDES_ID = ? " + "WHERE ORDER_ID = ? "; int rowCount = 0; rowCount = jdbcTemplate.update(SQL_QUERY, new Object[] { newCustomerId, newDeliveryId, newOrderStatusId, newOrderDateIncome, newOrderDateEnd, newOrderInfo, orderCakePrice, newOrderDeliveryPrice, newOrderWeight, orderInsidesId, orderId }, new int[] { Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.TIMESTAMP, Types.TIMESTAMP, Types.VARCHAR, Types.DOUBLE, Types.DOUBLE, Types.DOUBLE, Types.VARCHAR, Types.INTEGER }); Logger.getLogger(OrdereDao.class.getName()).log(Level.SEVERE, " : {0} .", rowCount); return rowCount; } @Override public int deleteOrdere(int orderId) { throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates. } private ArrayList<Ordere> getOrdersList(List<Map<String, Object>> rows) { ArrayList<Ordere> orderList = new ArrayList<>(); for (Map row : rows) { Ordere order = new Ordere(); order.setOrderId(Integer.parseInt(String.valueOf(row.get("ORDER_ID")))); order.setCustomerId(Integer.parseInt(String.valueOf(row.get("CUSTOMER_ID")))); order.setDeliveryId(Integer.parseInt(String.valueOf(row.get("DELIVERY_ID")))); order.setOrderStatusId(Integer.parseInt(String.valueOf(row.get("ORDER_STATUS_ID")))); /// //String[] dateArr = (String.valueOf(row.get("ORDER_DATE_INCOME"))).substring(0, 10).split("-"); String[] dateArrFull = (String.valueOf(row.get("ORDER_DATE_INCOME"))).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])); order.setOrderDateIncome(new GregorianCalendar(year, month, day, hour, minut, (int) second).getTime()); //System.out.println("OrdereDao - ORDER_DATE_INCOME:" + new Date(year, month, day, hour, minut, (int)second)); /// /// String[] dateArrFull1 = (String.valueOf(row.get("ORDER_DATE_END"))).split(" "); String[] dateArrDate1 = dateArrFull1[0].split("-"); int year1 = Integer.parseInt(String.valueOf(dateArrDate1[0]))/*-1900*/; int month1 = Integer.parseInt(String.valueOf(dateArrDate1[1])) - 1; int day1 = Integer.parseInt(String.valueOf(dateArrDate1[2])); String[] dateArrTime1 = dateArrFull1[1].split(":"); int hour1 = Integer.parseInt(String.valueOf(dateArrTime1[0])); int minut1 = Integer.parseInt(String.valueOf(dateArrTime1[1])); double second1 = Double.parseDouble(String.valueOf(dateArrTime1[2])); order.setOrderDateEnd( new GregorianCalendar(year1, month1, day1, hour1, minut1, (int) second1).getTime()); /// order.setOrderInfo(String.valueOf(row.get("ORDER_INFO"))); order.setOrderCakePrice(Double.parseDouble(String.valueOf(row.get("ORDER_CAKE_PRICE")))); order.setOrderDeliveryPrice(Double.parseDouble(String.valueOf(row.get("ORDER_DELIVERY_PRICE")))); order.setOrderWeight(Double.parseDouble(String.valueOf(row.get("ORDER_WEIGHT")))); order.setOrderInsidesId(String.valueOf(row.get("ORDER_INSIDES_ID"))); /// dateArrFull = (String.valueOf(row.get("TIMESTAMP"))).split(" "); dateArrDate = dateArrFull[0].split("-"); year = Integer.parseInt(String.valueOf(dateArrDate[0]))/* -1900 */; month = Integer.parseInt(String.valueOf(dateArrDate[1])) - 1; day = Integer.parseInt(String.valueOf(dateArrDate[2])); dateArrTime = dateArrFull[1].split(":"); hour = Integer.parseInt(String.valueOf(dateArrTime[0])); minut = Integer.parseInt(String.valueOf(dateArrTime[1])); second = Double.parseDouble(String.valueOf(dateArrTime[2])); order.setTimestamp(new GregorianCalendar(year, month, day, hour, minut, (int) second).getTime()); /// orderList.add(order); } return orderList; } @Override public ArrayList<Ordere> getTimestamp() { String SQL_QUERY = "SELECT ORDER_ID, TIMESTAMP " + "FROM orders "; List<Map<String, Object>> rows = jdbcTemplate.queryForList(SQL_QUERY); ArrayList<Ordere> orderList = new ArrayList<>(); for (Map row : rows) { Ordere order = new Ordere(); order.setOrderId(Integer.parseInt(String.valueOf(row.get("ORDER_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])); order.setTimestamp(new GregorianCalendar(year, month, day, hour, minut, (int) second).getTime()); /// orderList.add(order); } return orderList; } }