Java tutorial
/* * Copyright 2010-2013 Amazon.com, Inc. or its affiliates. All Rights Reserved. * * Licensed under the Apache License, Version 2.0 (the "License"). * You may not use this file except in compliance with the License. * A copy of the License is located at * * http://aws.amazon.com/apache2.0 * * or in the "license" file accompanying this file. This file is distributed * on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either * express or implied. See the License for the specific language governing * permissions and limitations under the License. */ package jp.sanix; import java.io.BufferedReader; import java.io.BufferedWriter; import java.io.File; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.io.FileOutputStream; import java.io.FileWriter; import java.io.IOException; import java.io.InputStreamReader; import java.text.ParseException; import java.util.zip.GZIPInputStream; import java.util.Calendar; import java.util.regex.Pattern; import org.json.JSONException; import com.amazonaws.AmazonServiceException; import com.amazonaws.regions.Region; import com.amazonaws.regions.Regions; import com.amazonaws.services.s3.AmazonS3; import com.amazonaws.services.s3.AmazonS3Client; import com.amazonaws.services.s3.model.GetObjectRequest; import com.amazonaws.services.s3.model.ListObjectsRequest; import com.amazonaws.services.s3.model.ObjectListing; import com.amazonaws.services.s3.model.PutObjectRequest; import com.amazonaws.services.s3.model.S3Object; import com.amazonaws.services.s3.model.S3ObjectSummary; public class mukaino { public static final String PG_CON_LOCAL = "jdbc:postgresql://localhost:19000/sanix_eye_rawdata_production"; public static final String PG_CON = "jdbc:postgresql://sanix-eye-rawdata-r01.cpoo4hhfnsex.ap-northeast-1.rds.amazonaws.com:5432/sanix_eye_rawdata_production"; public static final String PG_USER = "sanix_eye_rawdat"; public static final String PG_PASS = "HFKbK_4whYj2"; public static void main(String[] args) throws IOException, SQLException, AmazonServiceException, JSONException, NullPointerException, ParseException { AmazonS3 s3 = new AmazonS3Client(); s3.setRegion(Region.getRegion(Regions.AP_NORTHEAST_1)); String[] ids = { "A0000089", "A0002570" }; Connection db; if (System.getProperty("user.name").toString().equals("ec2-user")) { db = DriverManager.getConnection(PG_CON, PG_USER, PG_PASS); } else { db = DriverManager.getConnection(PG_CON_LOCAL, PG_USER, PG_PASS); } for (String id : ids) { pvSensor.getInstance().getById(id); Statement st = db.createStatement(); ResultSet rs = st.executeQuery("SELECT data, pvs_unique_code FROM data WHERE pvs_serial_id='" + id + "' ORDER BY created_at DESC OFFSET 0 LIMIT 1;"); rs.next(); String json = rs.getString(1); String key = rs.getString(2); rs.close(); st.close(); Calendar cal = Calendar.getInstance(); cal.add(Calendar.DAY_OF_MONTH, -1); // 1?? Calendar end = Calendar.getInstance(); cal.add(Calendar.DAY_OF_MONTH, -1); // 1??? do { String mDate = String.format("%04d-%02d-%02d", cal.get(Calendar.YEAR), cal.get(Calendar.MONTH) + 1, cal.get(Calendar.DAY_OF_MONTH)); xlsSheetMukaino xls = new xlsSheetMukaino(json); /* AWS S3????? */ String bucketName = "pvdata-storage-production"; System.out.print("Get s3 data by key='" + bucketName + String.format("data/%s/%04d/%02d/%02d/", key, cal.get(Calendar.YEAR), cal.get(Calendar.MONTH) + 1, cal.get(Calendar.DAY_OF_MONTH))); ObjectListing objectListing = s3.listObjects(new ListObjectsRequest().withBucketName(bucketName) .withPrefix("data/" + key + "/" + mDate.replace("-", "/"))); /* get data from s3 */ do { for (S3ObjectSummary objectSummary : objectListing.getObjectSummaries()) { String keyname = objectSummary.getKey(); S3Object object = s3.getObject(new GetObjectRequest(bucketName, keyname)); BufferedReader reader = new BufferedReader( new InputStreamReader(new GZIPInputStream(object.getObjectContent()))); while (reader.ready()) { String line = reader.readLine(); try { json = line.substring(line.indexOf("{")); xls.putData(json); } catch (NullPointerException e) { } } reader.close(); object.close(); System.out.print("."); } objectListing = s3.listNextBatchOfObjects(objectListing); } while (objectListing.getMarker() != null); System.out.println(); xls.writeBuffer(); File file = File.createTempFile("temp", ".xlsx"); file.deleteOnExit(); xls.putFile(new FileOutputStream(file)); s3.putObject(new PutObjectRequest("sanix-data-analysis", "Rw6WHQLqGcvs3wpWwALh80IHAiZqWYzw/" + id + "-" + mDate + ".xlsx", file)); System.out.println("Finished: " + mDate); cal.add(Calendar.DAY_OF_MONTH, 1); } while (cal.before(end)); } db.close(); File file = File.createTempFile("temp", ".html"); file.deleteOnExit(); BufferedWriter bw = new BufferedWriter(new FileWriter(file)); ObjectListing objectListing = s3.listObjects(new ListObjectsRequest().withBucketName("sanix-data-analysis") .withPrefix("Rw6WHQLqGcvs3wpWwALh80IHAiZqWYzw/")); bw.write("<html><head></head><body><ul>\n"); do { for (S3ObjectSummary objectSummary : objectListing.getObjectSummaries()) { String keyname = objectSummary.getKey(); if (Pattern.compile("\\.xlsx$|\\.html$").matcher(keyname).find()) { bw.write("<li><a href=\"https://s3-ap-northeast-1.amazonaws.com/sanix-data-analysis/" + keyname + "\">" + keyname.replaceAll("^[^\\/]*\\/", "") + "</a></li>\n"); } } objectListing = s3.listNextBatchOfObjects(objectListing); } while (objectListing.getMarker() != null); bw.write("</ul></body></html>\n"); bw.flush(); bw.close(); s3.putObject( new PutObjectRequest("sanix-data-analysis", "Rw6WHQLqGcvs3wpWwALh80IHAiZqWYzw/index.html", file)); } }