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_reorganized_database.dao; //import static konditer_reorganized_database.Main; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import static java.lang.Double.valueOf; import java.security.*; import java.sql.SQLException; import java.sql.Types; import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.HashSet; import java.util.List; import java.util.Map; import java.util.logging.Level; import java.util.logging.Logger; import javax.activation.DataSource; import static jdk.nashorn.internal.objects.NativeString.substring; import static konditer_reorganized_database.Main.genIdDao; import static konditer_reorganized_database.Main.ordereDao; import konditer_reorganized_database.bean.Ordere; import static konditer_reorganized_database.util.FileUtils.getZipBytArrayImageIcon; 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 public class ReorganizedDatabase { String oldPhotoCatalog, oldPhotoDateCatalog, newPhotoCatalog; ArrayList<String> oldCakeName, oldCakeSection, oldCakeImageName, newCakeImageName; 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; } public ReorganizedDatabase() { } public void reorg() throws SQLException, IOException { userlist(); // settings(); // /// !!! ? !!! oldPhotoCatalog = "c:/WebServers/home/konditer.dp.ua/www/image/"; oldPhotoDateCatalog = "c:/WebServers/home/konditer.dp.ua/date/"; newPhotoCatalog = "c:/WebServers/home/konditer.dp.ua/www/image_cake/"; /// !!! ? !!! ordere(); // setCakeTypes(); // galary(); // galaryNotVisible(); // //System.out.println(substring("??", 0 ,"??".length()-3)); } public void settings() throws SQLException { String SQL_QUERY = "SELECT * FROM dmroy_kcake.settings"; String page, title, meta_d, meta_k, text; int pageMetadatas = 0, pageMetadatas_1 = 0, sitePages = 0, sitePages_1 = 0; List<Map<String, Object>> rows = jdbcTemplate.queryForList(SQL_QUERY); for (Map<String, Object> row : rows) { page = (String) row.get("page"); title = (String) row.get("title"); meta_d = (String) row.get("meta_d"); meta_k = (String) row.get("meta_k"); text = (String) row.get("text"); String SQL_QUERY_1 = "INSERT INTO cake_portal.page_metadatas (META_ID, META_KEYWORDS, META_DESCRIPTION) " + "VALUES ( ?, ?, ? ) "; int metaId = genIdDao.getPageMetadataId(); pageMetadatas_1 = jdbcTemplate.update(SQL_QUERY_1, new Object[] { metaId, meta_k, meta_d }); pageMetadatas += pageMetadatas_1; String SQL_QUERY_2 = "INSERT INTO cake_portal.site_pages (PAGE_ID, META_ID, PAGE_NAME, PAGE_TITLE, PAGE_CONTENT) " + "VALUES ( ?, ?, ?, ?, ? ) "; int pageId = genIdDao.getSitePageId(); sitePages_1 = jdbcTemplate.update(SQL_QUERY_2, new Object[] { pageId, metaId, page, title, text }, new int[] { Types.INTEGER, Types.INTEGER, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR }); sitePages += sitePages_1; } System.out.println(" page_metadatas " + pageMetadatas + " ?."); System.out.println(" site_pages " + sitePages + " ?."); } public void userlist() throws SQLException { String SQL_QUERY = "SELECT user, pass FROM dmroy_kcake.userlist"; //HashSet hs = new HashSet(){}; String user, pass; int rowCount = 0, rowCount_1 = 0; List<Map<String, Object>> rows = jdbcTemplate.queryForList(SQL_QUERY); for (Map<String, Object> row : rows) { user = (String) row.get("user"); pass = (String) row.get("pass"); // ?? ?? /* MessageDigest md = MessageDigest.getInstace("MD5"); md.update(pass.getBytes()); byte[] passMD5 = md.digest(); */ String SQL_QUERY_1 = "INSERT INTO cake_portal.users (USER_ID, USER_LOGIN, USER_PASSWORD) " + "VALUES ( ?, ?, ? ) "; rowCount_1 = jdbcTemplate.update(SQL_QUERY_1, new Object[] { genIdDao.getUserId(), user, pass }); rowCount += rowCount_1; } System.out.println(" users " + rowCount + " ?."); } public void ordere() throws SQLException { HashMap fileHashMap = new HashMap(); File file = new File(oldPhotoDateCatalog); String[] fileList = file.list(); for (String s : fileList) { String[] s1 = s.split(" - "); String[] dateTime = s1[0].split(" "); String[] yymmdd = dateTime[0].split("-"); String[] hhmmss = dateTime[1].split("-"); fileHashMap.put(s1[0], s1[1]); System.out.println(s + " |" + s1[0] + "|" + s1[1] + "|"); double yearDouble = valueOf(yymmdd[0]); int year = (int) yearDouble; double monthDouble = valueOf(yymmdd[1]); int month = (int) monthDouble; double dayDouble = valueOf(yymmdd[2]); double hoursDouble = valueOf(hhmmss[0]); double minutesDouble = valueOf(hhmmss[1]); double secondsDouble = valueOf(hhmmss[2]); System.out.println(" ? " + s1[0] + "; -> " + new Date(year - 1900, month - 1, (int) dayDouble, (int) hoursDouble, (int) minutesDouble, (int) secondsDouble).toString()); /* public void addOrdere(int orderId, int customerId, int orderStatusId, int deliveryId, Date orderDateIncome, Date orderDateEnd, double orderCakePrice, double orderDeliveryPrice, String orderInsidesId, String orderInfo) */ int orderId = genIdDao.getOrderId(); Date ordereDate = new Date(year - 1900, month - 1, (int) dayDouble, (int) hoursDouble, (int) minutesDouble, (int) secondsDouble); String SQL_GET_PRICE = "SELECT CAKE_PRICE " + "FROM cake_portal.cake_prices " + "WHERE CAKE_PRICE_ID = (SELECT MAX(CAKE_PRICE_ID) " + "FROM cake_portal.cake_prices " + "WHERE CAKE_PRICE_DATE <= ? ) "; double cakePrice = (double) jdbcTemplate.queryForObject(SQL_GET_PRICE, new Object[] { ordereDate }, Double.class); /* int orderId = genIdDao.getOrderId(); Date ordereDate = new Date(year-1900,month-1,(int)dayDouble,(int)hoursDouble,(int)minutesDouble,(int)secondsDouble); String SQL_GET_PRICE = "SELECT MAX(CAKE_PRICE) " + "FROM cake_portal.cake_prices " + "WHERE CAKE_PRICE_DATE < ? "; double cakePrice = (double) jdbcTemplate.queryForObject(SQL_GET_PRICE,new Object[]{ordereDate},Double.class); */ ordereDao.addOrdere(orderId, 1, 3, 1, ordereDate, ordereDate, cakePrice, 0, "[]", s1[1]); //" ? ? . ? " } //for (String[]) } public void setCakeTypes() throws SQLException { String SQL_QUERY = "SELECT * FROM dmroy_kcake.galary ORDER BY id"; HashSet<String> hs = new HashSet<>(); String name_section; int cakeTypes = 0, cakeTypes_1 = 0; System.out .println(" ? ?? ? : "); List<Map<String, Object>> rows = jdbcTemplate.queryForList(SQL_QUERY); for (Map<String, Object> row : rows) { name_section = (String) row.get("name_section"); hs.add(name_section); } String SQL_QUERY_2 = "INSERT INTO cake_portal.cake_types (CAKE_TYPE_ID, CAKE_TYPE_NAME ) " + "VALUES ( ?, ? ) "; for (String str : hs) { int cakeTypeId = genIdDao.getCakeTypeId(); cakeTypes_1 = jdbcTemplate.update(SQL_QUERY_2, new Object[] { cakeTypeId, str }); cakeTypes += cakeTypes_1; } System.out.println(" cake_types " + cakeTypes + " ?."); } public void galary() throws SQLException, FileNotFoundException, IOException { String SQL_QUERY = "SELECT * FROM dmroy_kcake.galary ORDER BY id"; //HashSet<String> hs = new HashSet<>(); String image_small, image, name_section, name_cake, weight_cake, keywords; int cakesPage = 0, cakesPage_1 = 0, cakeTypes = 0, cakeTypes_1 = 0; new File(newPhotoCatalog).mkdirs(); System.out.println(" ? : "); List<Map<String, Object>> rows = jdbcTemplate.queryForList(SQL_QUERY); for (Map<String, Object> row : rows) { image_small = (String) row.get("image_small"); image = (String) row.get("image"); name_section = (String) row.get("name_section"); name_cake = (String) row.get("name_cake"); weight_cake = (String) row.get("weight_cake"); keywords = (String) row.get("keywords"); String tmpStr = ""; for (int i = 0; i < weight_cake.length(); i++) { if (weight_cake.charAt(i) == ',') { tmpStr += '.'; } else { tmpStr += weight_cake.charAt(i); } } Double cakeWeightParse = valueOf(tmpStr); // ?? ?? ?/ ? //hs.add(name_section); String SQL_QUERY_1 = "INSERT INTO cake_portal.cakes (CAKE_ID, CUSTOMER_ID, CAKE_PRICE, ORDER_ID, " + "CAKE_NAME, CAKE_WEIGHT, CAKE_PHOTO_MINI, CAKE_PHOTO_MAXI, CAKE_KEYWORDS ) " + "VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) "; int cakeId = genIdDao.getCakeId(); oldCakeName = new ArrayList<>(); oldCakeSection = new ArrayList<>(); oldCakeImageName = new ArrayList<>(); //newCakeImageName = new ArrayList<>(); oldCakeImageName.add(image); oldCakeSection.add(name_section); oldCakeName.add(name_cake); if (cakeId % 2 == 0) { System.out.print("."); } if (cakeId % 50 == 0) { System.out.println(cakeId); } ///////////////////////////////////// //////// //File inImageMini = new File(oldPhotoCatalog+image_small+""); File inImageMini = new File(""); inImageMini = getZipBytArrayImageIcon(oldPhotoCatalog + image_small + "", 150, 112); byte[] fileArrayMini = {}; try { fileArrayMini = getBytesFromFile(inImageMini); } catch (IOException ex) { Logger.getLogger(ReorganizedDatabase.class.getName()).log(Level.SEVERE, null, ex); } FileOutputStream outImageMini = new FileOutputStream(newPhotoCatalog + cakeId + "_s.jpg"); outImageMini.write(fileArrayMini); //inImageMini = new File(oldPhotoCatalog+image_small+""); //inImageMini.delete(); new File(oldPhotoCatalog + image_small + "").delete(); //////// //File inImageMaxi = new File(oldPhotoCatalog+image+""); File inImageMaxi = new File(""); inImageMaxi = getZipBytArrayImageIcon(oldPhotoCatalog + image + "", 640, 480); byte[] fileArrayMaxi = {}; try { fileArrayMaxi = getBytesFromFile(inImageMaxi); } catch (IOException ex) { Logger.getLogger(ReorganizedDatabase.class.getName()).log(Level.SEVERE, null, ex); } FileOutputStream outImageMaxi = new FileOutputStream(newPhotoCatalog + cakeId + ".jpg"); outImageMaxi.write(fileArrayMaxi); //File inImageMaxi = new File(oldPhotoCatalog+image+""); //inImageMaxi.delete(); new File(oldPhotoCatalog + image + "").delete(); ///////////////////////////////////////// String SQL_GET_ORDER = "SELECT ORDER_ID, " + "CUSTOMER_ID, " + "ORDER_STATUS_ID, " + "DELIVERY_ID, " + "ORDER_DATE_INCOME, " + "ORDER_DATE_END, " + "ORDER_CAKE_PRICE, " + "ORDER_DELIVERY_PRICE, " + "ORDER_INSIDES_ID, " + "ORDER_INFO, TIMESTAMP " + "FROM orders " + "WHERE ORDER_INFO = ?"; Ordere order = (Ordere) jdbcTemplate.queryForObject(SQL_GET_ORDER, new Object[] { image }, new BeanPropertyRowMapper(Ordere.class)); int orderId = order.getOrderId(); double cakePrice = order.getOrderCakePrice() * cakeWeightParse; cakesPage_1 = jdbcTemplate.update(SQL_QUERY_1, new Object[] { cakeId, 1, cakePrice, orderId, name_cake, cakeWeightParse, "" + cakeId + "_s.jpg", "" + cakeId + ".jpg", keywords }); cakesPage += cakesPage_1; String SQL_SET_CAKE_TYPE = "INSERT INTO cake_portal.cakes_and_types (CAKE_AND_TYPE_ID, CAKE_ID, CAKE_TYPE_ID ) " + "VALUES (?, ?, (SELECT CAKE_TYPE_ID " + "FROM cake_portal.cake_types " + "WHERE CAKE_TYPE_NAME = ?))"; jdbcTemplate.update(SQL_SET_CAKE_TYPE, new Object[] { genIdDao.getCakeAndTypeId(), cakeId, name_section }); } System.out.println("\n cakes " + cakesPage + " ?."); /* String SQL_QUERY_2 = "INSERT INTO cake_portal.cake_types (CAKE_TYPE_ID, CAKE_TYPE_NAME ) " + "VALUES ( ?, ? ) "; for (String str: hs){ int cakeTypeId = genIdDao.getCakeTypeId(); cakeTypes_1 = jdbcTemplate.update(SQL_QUERY_2, new Object[]{cakeTypeId, str}); cakeTypes += cakeTypes_1; } System.out.println(" cake_types " + cakeTypes + " ?."); */ } public void galaryNotVisible() throws SQLException, IOException { String SQL_NOT_VISIBLE = "SELECT * " + "FROM orders " + "WHERE ORDER_ID NOT IN (SELECT ORDER_ID FROM cakes )"; String image; int cakeCount = 0, cakeCount1 = 0; double cakePrice, orderId; String orderStr = "", cakeStr = ""; System.out.println( " ? ?? , ?? ?: "); List<Map<String, Object>> rows = jdbcTemplate.queryForList(SQL_NOT_VISIBLE); for (Map<String, Object> row : rows) { orderStr = (String) row.get("ORDER_ID").toString(); orderId = valueOf(orderStr); cakeStr = (String) row.get("ORDER_CAKE_PRICE").toString(); cakePrice = valueOf(cakeStr); image = (String) row.get("ORDER_INFO"); String SQL_QUERY_1 = "INSERT INTO cake_portal.cakes (CAKE_ID, CUSTOMER_ID, CAKE_PRICE, CAKE_STATUS, ORDER_ID, " + "CAKE_NAME, CAKE_WEIGHT, CAKE_PHOTO_MINI, CAKE_PHOTO_MAXI, CAKE_KEYWORDS ) " + "VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) "; int cakeId = genIdDao.getCakeId(); ///////////////////////////////////// //////// //File inImageMini = new File(oldPhotoCatalog+image_small+""); File inImageMini = new File(""); inImageMini = getZipBytArrayImageIcon( oldPhotoCatalog + substring(image, 0, image.length() - 4) + "_s.jpg", 150, 112); byte[] fileArrayMini = {}; try { fileArrayMini = getBytesFromFile(inImageMini); } catch (IOException ex) { Logger.getLogger(ReorganizedDatabase.class.getName()).log(Level.SEVERE, null, ex); } FileOutputStream outImageMini = new FileOutputStream(newPhotoCatalog + cakeId + "_s.jpg"); outImageMini.write(fileArrayMini); //inImageMini = new File(oldPhotoCatalog+image_small+""); //inImageMini.delete(); new File(oldPhotoCatalog + substring(image, 0, image.length() - 4) + "_s.jpg").delete(); //////// //File inImageMaxi = new File(oldPhotoCatalog+image+""); File inImageMaxi = new File(""); inImageMaxi = getZipBytArrayImageIcon(oldPhotoCatalog + image + "", 640, 480); byte[] fileArrayMaxi = {}; try { fileArrayMaxi = getBytesFromFile(inImageMaxi); } catch (IOException ex) { Logger.getLogger(ReorganizedDatabase.class.getName()).log(Level.SEVERE, null, ex); } FileOutputStream outImageMaxi = new FileOutputStream(newPhotoCatalog + cakeId + ".jpg"); outImageMaxi.write(fileArrayMaxi); //File inImageMaxi = new File(oldPhotoCatalog+image+""); //inImageMaxi.delete(); new File(oldPhotoCatalog + image + "").delete(); ///////////////////////////////////////// cakeCount1 = jdbcTemplate.update(SQL_QUERY_1, new Object[] { cakeId, 1, cakePrice, false, (int) orderId, substring(image, 0, image.length() - 4), 0, "" + cakeId + "_s.jpg", "" + cakeId + ".jpg", " ? ? " }); cakeCount += cakeCount1; System.out.print("."); } System.out.println("\n , ?? - " + cakeCount + "."); } private static byte[] getBytesFromFile(File file) throws IOException { InputStream is = new FileInputStream(file); long length = file.length(); if (length > Integer.MAX_VALUE) { // File is too large } byte[] bytes = new byte[(int) length]; int offset = 0; int numRead = 0; while (offset < bytes.length && (numRead = is.read(bytes, offset, bytes.length - offset)) >= 0) { offset += numRead; } if (offset < bytes.length) { throw new IOException("Could not completely read file " + file.getName()); } is.close(); return bytes; } }