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 com.candy.db; import com.candy.common.Pair; import com.candy.middle.GlobalConfig; import static com.candy.middle.GlobalConfig.MAX_FUNDAMENTAL_COLUMN; import java.lang.reflect.Method; import java.util.ArrayList; import java.util.Calendar; import java.util.Date; import java.util.GregorianCalendar; import java.util.LinkedHashMap; import java.util.List; import java.util.Map; import org.hibernate.HibernateException; import org.hibernate.Query; import org.hibernate.Session; /** * * @author zho55500 */ public class FundamentalDataProc extends HibernateBase { private final Method methodSetValueLst[] = new Method[GlobalConfig.MAX_FUNDAMENTAL_COLUMN]; private final Method methodGetValueLst[] = new Method[GlobalConfig.MAX_FUNDAMENTAL_COLUMN]; private final Method methodSetNameLst[] = new Method[GlobalConfig.MAX_FUNDAMENTAL_COLUMN]; private final Method methodGetNameLst[] = new Method[GlobalConfig.MAX_FUNDAMENTAL_COLUMN]; private final String FUNC_SETVALUE_KEYWORD = "setC"; private final String FUNC_GETVALUE_KEYWORD = "getC"; private final String FUNC_SETNAME_KEYWORD = "setN"; private final String FUNC_GETNAME_KEYWORD = "getN"; private final FundamentaldataId pkDataId = new FundamentaldataId(); private enum MSG_TYPE { UNINIT }; // singleton private static class Holder { static final FundamentalDataProc INSTANCE = new FundamentalDataProc(); } public static FundamentalDataProc getInstance() { return Holder.INSTANCE; } /** * convert hibernate mapping object to more flexible Object */ public static class FundamentalDataRec { private LinkedHashMap<String, Double> finDatas = new LinkedHashMap(); private ArrayList<String> nameLstOrder = null; public int year, quarter, type; public void setYear(int year) { this.year = year; } public void setQuarter(int quarter) { this.quarter = quarter; } public String getColumnName() { return String.format("%d/%d", year, quarter); } /** * calculated based on quarter * @return */ public Date getDate() { Calendar cal = new GregorianCalendar(year, quarter * 3, 0); return cal.getTime(); } /** * called once * @return */ public Pair<Integer, Integer> getYearQuarter() { return new Pair(year, quarter); } public LinkedHashMap<String, Double> getNameValues() { return finDatas; } public ArrayList<String> getNameListInOrder() { if (nameLstOrder == null) { nameLstOrder = new ArrayList(); nameLstOrder.addAll(finDatas.keySet()); } return nameLstOrder; } @Deprecated public void setDataByName(String parent, String name, Double value) { // String str = parent+"/"+name; finDatas.put(name, value); } public void setNameValue(String name, Double value) { finDatas.put(name, value); } /** * get finreport value by name * @param name * @return */ public Double getDataByName(String name) { // String str = parent+"/"+name; return finDatas.get(name); } public int getYear() { return year; } public int getQuarter() { return quarter; } public int getType() { return type; } public void setType(int type) { this.type = type; } } private FundamentalDataProc() { initFunc(); } private void initFunc() { // TODO Fundamentaldata fdata = new Fundamentaldata(); try { Class cla = Class.forName(fdata.getClass().getName()); Method[] methods = cla.getDeclaredMethods(); for (Method me : methods) { // System.out.println(me.toString()); int vidx = getIdxFromFuncName(me.toString(), FUNC_SETVALUE_KEYWORD); if (vidx != -1 && vidx < MAX_FUNDAMENTAL_COLUMN) { methodSetValueLst[vidx] = me; continue; } int nidx = getIdxFromFuncName(me.toString(), FUNC_SETNAME_KEYWORD); if (nidx != -1 && nidx < MAX_FUNDAMENTAL_COLUMN) { methodSetNameLst[nidx] = me; continue; } int gvidx = getIdxFromFuncName(me.toString(), FUNC_GETNAME_KEYWORD); if (gvidx != -1 && gvidx < MAX_FUNDAMENTAL_COLUMN) { methodGetNameLst[gvidx] = me; continue; } int gnidx = getIdxFromFuncName(me.toString(), FUNC_GETVALUE_KEYWORD); if (gnidx != -1 && gnidx < MAX_FUNDAMENTAL_COLUMN) { methodGetValueLst[gnidx] = me; continue; } } } catch (ClassNotFoundException e) { System.out.println("ERROR ? FundamentalData Class Not found!"); } catch (IndexOutOfBoundsException e) { System.out.println("ERROR ? IndexOutOfBoundsException!"); e.printStackTrace(); } } /* * extract '0' from the funcation name 'setN0(xxx)' */ private int getIdxFromFuncName(String funcName, String keyWord) { int index = funcName.indexOf(keyWord); if (index != -1) { int lastidx = funcName.indexOf('(', index); if (lastidx != -1) { String thestr = funcName.substring(index + keyWord.length(), lastidx); try { int midx = Integer.parseInt(thestr); return midx; } catch (NumberFormatException e) { // skip this line return -1; } } } return -1; } private void errorMsg(MSG_TYPE type) { switch (type) { case UNINIT: System.out.println("ERROR - db not initialized"); break; } } /** * select one record by date(year,quarter) and type TODO year and quarter * @param symbol * @param type * @param year * @param quarter * @return */ public FundamentalDataRec readDataByDate(String symbol, int type, int year, int quarter) { try { begin(); Query q = getSession().createQuery( "from Fundamentaldata where symbol = :symbol and year = :year and quarter = :quarter and type = :type"); q.setString("symbol", symbol); q.setInteger("year", year); q.setInteger("quarter", quarter); q.setInteger("type", type); Fundamentaldata fdata = (Fundamentaldata) q.uniqueResult(); commit(); if (fdata == null) return null; FundamentalDataRec rec = new FundamentalDataRec(); for (int i = 0; i < methodGetNameLst.length; i++) { try { Double value = (Double) methodGetValueLst[i].invoke(fdata); // return double String name = (String) methodGetNameLst[i].invoke(fdata); if (name != null) { rec.setNameValue(name, value); } } catch (Exception e) { // skip it System.out.println("ERROR - unable to get column value/name = " + i); } } return rec; } catch (HibernateException e) { e.printStackTrace(); return null; } } /** * read records from table * @param symbol * @param count * @param qtv * @return */ public ArrayList<FundamentalDataRec> readDataByCount(String symbol, int type, int count, boolean qtv) { try { begin(); Query q = null; if (qtv) q = getSession().createQuery( "from Fundamentaldata where symbol = :symbol and quarter != 0 and type = :type order by year desc"); else q = getSession().createQuery( "from Fundamentaldata where symbol = :symbol and quarter = 0 and type = :type order by year desc"); q.setString("symbol", symbol); q.setInteger("type", type); if (count != 0) { q.setFirstResult(0); q.setMaxResults(count); } List<Fundamentaldata> results = q.list(); commit(); if (results.isEmpty()) { return null; } else { ArrayList<FundamentalDataRec> retLst = new ArrayList(); for (Fundamentaldata fdata : results) { FundamentalDataRec rec = new FundamentalDataRec(); rec.year = fdata.getId().getYear(); rec.quarter = fdata.getId().getQuarter(); rec.setType(fdata.getId().getType()); for (int i = 0; i < methodGetValueLst.length; i++) { try { Double value = (Double) methodGetValueLst[i].invoke(fdata); // return double String name = (String) methodGetNameLst[i].invoke(fdata); if (name != null) { rec.setNameValue(name, value); } // else { // TODO // System.out.println("ERROR - fundamental name is null " + i); // } } catch (Exception e) { System.out.println("ERROR - unable to get column value/name = " + i); } } retLst.add(rec); } return retLst; } } catch (HibernateException e) { return null; } } /** * * @param symbol * @param type * @param year * @param quarter * @return */ // @Deprecated // public boolean isDataExist(String symbol, int type, int year, int quarter) { // try { // begin(); // Query q = getSession().createQuery("from Fundamentaldata where symbol = :symbol and year = :year and quarter = :quarter and type = :type"); // q.setString("symbol", symbol); // q.setInteger("year", year); // q.setInteger("quarter", quarter); // q.setInteger("type", type); // boolean exist = (q.uniqueResult() != null); // commit(); // return exist; // }catch (HibernateException e) { // return false; // } // } public boolean isDataExistByFileName(String symbol, String fileName, int type) { try { begin(); Query q = getSession().createQuery( "from Fundamentaldata where symbol = :symbol and filename = :filename and type = :type"); q.setString("symbol", symbol); q.setInteger("type", type); q.setString("filename", fileName); boolean exist = (q.uniqueResult() != null); commit(); return exist; } catch (HibernateException e) { return false; } } /* * write Fundamental dataToDb to table */ // public boolean writeData(String symbol, FundamentalDataRec rec) { // try { // Session session = sf.openSession(); // Transaction tx = session.beginTransaction(); // Fundamentaldata dataToDb = new Fundamentaldata(); // pkDataId.setSymbol(symbol); // pkDataId.setYear((short)rec.getYear()); // pkDataId.setQuarter((byte)rec.getQuarter()); // pkDataId.setType((byte)rec.getType()); // dataToDb.setId(pkDataId); // LinkedHashMap<String, Double> recItems = rec.getNameValues(); // int count = 0; // for (Map.Entry<String, Double> pair : recItems.entrySet()) { // String name = pair.getKey(); // if (name.length() > GlobalConfig.MAX_XBRLNAME_LENGTH) { // name = name.substring(0, GlobalConfig.MAX_XBRLNAME_LENGTH); // } // Double value = pair.getValue(); // try { // methodSetValueLst[count].invoke(dataToDb, value); // methodSetNameLst[count].invoke(dataToDb, name); // count++; // } catch (Exception e) { // e.printStackTrace(); // System.out.println("Error - invoke name =" + name + " value=" + value); // } // } // session.save(dataToDb); // tx.commit(); // session.close(); // return true; // } catch (HibernateException e) { // e.printStackTrace(); // return false; // } // } /** * * @param symbol * @param year * @param quarter * @param type * @param nameValues * @return */ public boolean writeData(String symbol, int year, int quarter, int type, String dateStr, LinkedHashMap<String, Double> nameValues) { Session session = getSession(); try { begin(); Fundamentaldata dataToDb = new Fundamentaldata(); pkDataId.setSymbol(symbol); pkDataId.setYear((short) year); pkDataId.setQuarter((byte) quarter); pkDataId.setType((byte) type); dataToDb.setId(pkDataId); dataToDb.setFilename(dateStr); int count = 0; for (Map.Entry<String, Double> entry : nameValues.entrySet()) { String name = entry.getKey(); if (name.length() > GlobalConfig.MAX_XBRLNAME_LENGTH) { name = name.substring(0, GlobalConfig.MAX_XBRLNAME_LENGTH); } Double value = entry.getValue(); try { if (value != null) methodSetValueLst[count].invoke(dataToDb, value); methodSetNameLst[count].invoke(dataToDb, name); count++; } catch (NumberFormatException e) { e.printStackTrace(); System.out.println("Error - invoke name =" + name + " value=" + value); } catch (Exception e) { e.printStackTrace(); System.out.println("Error - invoke name =" + name + " value=" + value); } } session.save(dataToDb); commit(); return true; } catch (HibernateException e) { e.printStackTrace(); return false; } } public ArrayList<FundamentalDataRec> getLastQData(String symbol, int lastQoffset) { try { Query q = getSession().createQuery( "from Fundamentaldata where symbol = :symbol and quarter > :offset order by year desc"); q.setString("symbol", symbol); q.setInteger("offset", lastQoffset); List<Fundamentaldata> results = q.list(); if (results.isEmpty()) { return null; } else { ArrayList<FundamentalDataRec> retLst = new ArrayList(); for (Fundamentaldata fdata : results) { FundamentalDataRec rec = new FundamentalDataRec(); rec.year = fdata.getId().getYear(); rec.quarter = fdata.getId().getQuarter() - lastQoffset; rec.setType(fdata.getId().getType()); for (int i = 0; i < methodGetValueLst.length; i++) { try { Double value = (Double) methodGetValueLst[i].invoke(fdata); // return double String name = (String) methodGetNameLst[i].invoke(fdata); if (name != null) { rec.setNameValue(name, value); } } catch (Exception e) { System.out.println("ERROR - unable to get column value/name = " + i); } } retLst.add(rec); } return retLst; } } catch (HibernateException e) { return null; } } /** * delete UnProcessed Q data * @param symbol * @param year * @param quarter * @param type */ public boolean deleteQData(String symbol, int year, int oldQtr, int type) { Session session = getSession(); try { begin(); Query q = session.createQuery( "delete from Fundamentaldata where symbol = :symbol and year = :year and quarter =:quarter and type = :type"); q.setString("symbol", symbol); q.setInteger("year", year); q.setInteger("quarter", oldQtr); q.setInteger("type", type); int rowCount = q.executeUpdate(); commit(); return (rowCount == 1); } catch (HibernateException e) { e.printStackTrace(); return false; } } public static void main(String args[]) { FundamentalDataProc fdProc = new FundamentalDataProc(); // UpdateHandler uh = fdProc.getLastQ2("msft", 10); // fdProc.updateLastQ(uh); // fdProc.updateLastQData("ibm", 2012,12,10,0, null); } }