content: 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;
  		}
  	}
  
  	
  
  }
  