MetricService.java

package com.etao.adhoc.metric;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.alimama.web.adhoc.Upload;

import backtype.storm.utils.Utils;


public class MetricService {
	private static Logger LOG = Logger.getLogger(Upload.class);

	private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
	Connection conn;
	Map conf;
	private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	public MetricService() {}
	
	private void open()
	{
		conf = Utils.readStormConfig("adhoc.yaml");
		String url = (String) conf.get("metric.mysql.url");
		String username = (String) conf.get("metric.mysql.username");
		String password = (String) conf.get("metric.mysql.password");
		try {
		
			Class.forName(JDBC_DRIVER);
			conn = DriverManager.getConnection(url, username, password);
		} catch (ClassNotFoundException e) {
			LOG.error(url+","+username+","+password,e);
		} catch (SQLException e) {
			LOG.error(url+","+username+","+password,e);
		}
	
	}
	public void insert(Metric metric) {
		this.open();
		String sql = "INSERT INTO adhoc_metric" +
				"(thedate,type,tablename,linecnt,impression,finclick,finprice," +
				"alipay_direct_num,alipay_direct_amt,alipay_indirect_num," +
				"alipay_indirect_amt)" +
				" VALUES(?,?,?,?,?,?,?,?,?,?,?) ";
		PreparedStatement pstmt;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,	metric.getThedate()); 
			pstmt.setLong(2, metric.getType());
			pstmt.setString(3,metric.getTablename());
			pstmt.setLong(4,metric.getLineCnt());
			pstmt.setLong(5, metric.getImpression());
			pstmt.setLong(6, metric.getFinClick());
			pstmt.setFloat(7, metric.getFinPrice());
			pstmt.setLong(8, metric.getAlipayDirectNum());
			pstmt.setFloat(9, metric.getAlipayDirectAmt());
			pstmt.setLong(10, metric.getAlipayIndirectNum());
			pstmt.setFloat(11,metric.getAlipayIndirectAmt());
			pstmt.executeUpdate();
			System.out.println(pstmt.toString());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.err.println("[" + sdf.format(new Date(System.currentTimeMillis())) + "] "
					+ "Error when insert Metric: "
					+ metric);
			e.printStackTrace();
		}
		this.close();
	}
	public void delete(Metric metric) {
		this.open();
		String sql = "DELETE FROM adhoc_metric" +
				" WHERE thedate=?" +
				" AND type=?" +
				" AND tablename=? ";
		PreparedStatement pstmt;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,	metric.getThedate()); 
			pstmt.setLong(2, metric.getType());
			pstmt.setString(3,metric.getTablename());
			pstmt.executeUpdate();
			System.out.println(pstmt.toString());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.err.println("[" + sdf.format(new Date(System.currentTimeMillis())) + "] "
					+ "Error when delete Metric: "
					+ metric);
			e.printStackTrace();
		}
		this.close();
		
	}
	public HashMap<String,ArrayList<Metric>> getMetric(String thedate) {
		this.open();
		String sql = "SELECT sum(linecnt) as linecnt,sum(impression) as impression,sum(finclick) as finclick,sum(finprice) as finprice," +
				"sum(alipay_direct_num) as alipay_direct_num,sum(alipay_direct_amt)," +
				"sum(alipay_indirect_num),sum(alipay_indirect_amt),tablename,type " +
				" FROM adhoc_metric" +
				" WHERE thedate=?" +
				" group by tablename,type " +
				" order by tablename,type";
		PreparedStatement pstmt=null;
		
		HashMap<String,ArrayList<Metric>> rtn=new HashMap<String,ArrayList<Metric>>();
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, thedate);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				Metric metric = new Metric();
				metric.setThedate(thedate);
			
				metric.setLineCnt(rs.getLong(1));
				metric.setImpression(rs.getLong(2));
				metric.setFinClick(rs.getLong(3));
				metric.setFinPrice(rs.getFloat(4));
				metric.setAlipayDirectNum(rs.getLong(5));
				metric.setAlipayDirectAmt(rs.getFloat(6));
				metric.setAlipayIndirectNum(rs.getLong(7));
				metric.setAlipayIndirectAmt(rs.getFloat(8));
				String tablename=rs.getString(9);
				metric.setType(rs.getLong(10));

				metric.setTablename(tablename);
				ArrayList<Metric> list=rtn.get(tablename);
				if(list==null)
				{
					list=new ArrayList<Metric>();
					rtn.put(tablename, list);
				}
				list.add(metric);
			}
		} catch (SQLException e) {
			LOG.error(String.valueOf(pstmt),e);

		}
		
		
		this.close();
		return rtn;
	}
	
	public List<String> getRecentDays(String tableName, String fieldName, int n) throws SQLException {
		this.open();
		List<String> days = null;
		String sql = "SELECT thedate" +
				" FROM adhoc_metric" +
				" group by thedate order by thedate desc" +
				" LIMIT %d";
		Statement stmt;
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(String.format(sql,n));
			days = new ArrayList<String>();
			while(rs.next()) {	
				days.add(rs.getString(1));
			} 
			this.close();
		return days;
	}
	
	public void close() {
		if(conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				System.err.println("[" + sdf.format(new Date(System.currentTimeMillis())) + "] "
						+ "Error when close Connection");
				e.printStackTrace();
			}
			
			conn=null;
		}
	}

	

}