base.jee.api.sql.BulkLoadLocations Maven / Gradle / Ivy

Go to download
/**
 * Creative commons Attribution-NonCommercial license.
 *
 * http://creativecommons.org/licenses/by-nc/2.5/au/deed.en_GB
 *
 * NO WARRANTY IS GIVEN OR IMPLIED, USE AT YOUR OWN RISK.
 */
package base.jee.api.sql;

import base.Command;
import base.security.PermissionException;

import javax.sql.DataSource;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import static base.jee.api.cassandra.BulkLoadLocations.lchomp;
import static base.jee.api.cassandra.BulkLoadLocations.dchomp;
import static base.jee.api.cassandra.BulkLoadLocations.qchomp;

public class BulkLoadLocations extends Command {

	private SqlAPI api;
	private InputStream locations;
	private InputStream ipLocations;

	public BulkLoadLocations(SqlAPI api, InputStream locations, InputStream ipLocations) throws PermissionException {

		if(api == null) {
			throw new IllegalArgumentException("Invalid parameter: api");
		}

		this.api = api;
		this.locations = locations;
		this.ipLocations = ipLocations;
	}

	public BulkLoadLocations() {
	}

	@Override
	protected void execute() throws IOException {
		DataSource ds = api.getDataSource();
		String line;

		Connection c = null;
		java.sql.PreparedStatement s = null;
		java.sql.PreparedStatement t = null;
		java.sql.PreparedStatement u = null;
		ResultSet r = null;

		try {
			c = ds.getConnection();
			c.setAutoCommit(false);

			BufferedReader in = new BufferedReader(new InputStreamReader(locations));
			s = c.prepareStatement("insert into location (id, country, city, latitude, longitude) values(?,?,?,?,?)");
			while((line = in.readLine()) != null) {
				String[] parts = line.split(",");
				if(parts.length == 9) {
					if(parts[0].matches("^[0-9].*")) {
						Long id = lchomp(parts[0]);
						String country = qchomp(parts[1]);
						String city = qchomp(parts[3]);
						Double latitude = dchomp(parts[5]);
						Double longitude = dchomp(parts[6]);
						if(id != null && longitude != null) {
							s.setLong(1, id);
							s.setString(2, country);
							s.setString(3, city);
							s.setDouble(4, latitude);
							s.setDouble(5, longitude);
							s.execute();
							continue;
						}
					}
				}
				System.out.println("skip>> " + line);
			}
			in.close();

			in = new BufferedReader(new InputStreamReader(ipLocations));
			t = c.prepareStatement("insert into ip_location (start_ip, end_ip, location) values(?,?,?)");
			while((line = in.readLine()) != null) {
				String[] parts = line.split(",");
				if(parts.length == 3) {
					if(parts[0].matches("^\"[0-9].*")) {
						Long start = lchomp(parts[0]);
						Long end = lchomp(parts[1]);
						Long location = lchomp(parts[2]);
						if(start != null && location != null) {
							s.setLong(1, start);
							s.setLong(2, end);
							s.setLong(3, location);
							s.execute();
							continue;
						}
					}
				}
				System.out.println("skip>> " + line);
			}
			in.close();

			c.commit();
			c.close();
			c = null;
		} catch(SQLException e) {
			throw new IOException(e);
		} finally {
			if(r != null) { try { r.close(); } catch(SQLException e) { } }
			if(s != null) { try { s.close(); } catch(SQLException e) { } }
			if(t != null) { try { t.close(); } catch(SQLException e) { } }
			if(u != null) { try { u.close(); } catch(SQLException e) { } }
			if(c != null) {
				try { c.rollback(); } catch(SQLException e) { }
				try { c.close(); } catch(SQLException e) { }
			}
		}

	}

	@Override
	public String getJsonParameters() {
		return "{" +
				"\"locations\":\"...\"," +
				"\"ip_locations\":\"...\"" +
				"}";
	}

}