import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;

import org.geotools.data.DataStore;
import org.geotools.data.DataStoreFinder;
import org.geotools.data.DefaultTransaction;
import org.geotools.data.Query;
import org.geotools.data.Transaction;
import org.geotools.data.collection.ListFeatureCollection;
import org.geotools.data.simple.SimpleFeatureCollection;
import org.geotools.data.simple.SimpleFeatureSource;
import org.geotools.data.simple.SimpleFeatureStore;
import org.geotools.data.store.ContentFeatureCollection.WrappingFeatureIterator;
import org.geotools.factory.CommonFactoryFinder;
import org.geotools.factory.GeoTools;
import org.geotools.feature.FeatureCollection;
import org.geotools.feature.FeatureIterator;
import org.geotools.jdbc.JDBCDataStore;
import org.geotools.jdbc.JDBCDataStoreFactory;
import org.opengis.feature.simple.SimpleFeature;
import org.opengis.feature.simple.SimpleFeatureType;
import org.opengis.filter.FilterFactory2;
import org.opengis.filter.identity.FeatureId;


public class GeoToolsTest {
	private static final FilterFactory2 FILTER_FACTORY = CommonFactoryFinder.getFilterFactory2(GeoTools.getDefaultHints());

	private static final String MSSQL_DBTYE = "sqlserver";
	private static final String MSSQL_HOST = "<SQLSERVER_HOST>";
	private static final int MSSQL_PORT = 1433;
	private static final String MSSQL_USER = "USER";
	private static final String MSSQL_PASSWORD = "PWD";
	private static final String MSSQL_DATABASENAME = "DB";
	private static final String MSSQL_SCHEMA = "dbo";
	private static final String[] EMPTY_STRING_ARRAY = new String[] {"Country"};

	public static void main(String[] args) throws Exception {
		DataStore dataStore = createDataStore();
		try {
			SimpleFeatureSource read = dataStore.getFeatureSource("READ_TABLE");
			
			//insert
			FeatureCollection<SimpleFeatureType, SimpleFeature> fc = read.getFeatures();
			List<SimpleFeature> features = new ArrayList<SimpleFeature>();
			FeatureIterator<SimpleFeature> it = fc.features();
			while(it.hasNext()){
				features.add(it.next());
			}
			System.out.println("Search Table WRITE_TABLE before insert");
			search(dataStore, "WRITE_TABLE");

			System.out.println("Insert Table WRITE_TABLE");
			insert(dataStore, "WRITE_TABLE", features);

			//search
			System.out.println("Search Table WRITE_TABLE after insert");
			search(dataStore, "WRITE_TABLE");
			
			//empty table after search
			Connection c = ((JDBCDataStore)dataStore).getConnection(Transaction.AUTO_COMMIT);
			Statement st = null;
			try {
				st = c.createStatement();
				st.execute("DELETE from WRITE_TABLE");
			}finally {
				st.close();
				c.close();
			}
		}finally {
			dataStore.dispose();
		}		
	}

	private static void search(DataStore dataStore, String tableName) throws IOException {
		List<String> columnNameList = new ArrayList<String>();
		columnNameList.add("Country");

		org.opengis.filter.Filter filter = org.opengis.filter.Filter.INCLUDE;

		filter = FILTER_FACTORY.bbox(
				FILTER_FACTORY.property("SP_GEOMETRY"),
				-180,
				-90,
				180,
				90,
				"EPSG:4326");

		Query query = new Query(
				tableName,
				filter,
				columnNameList.toArray(EMPTY_STRING_ARRAY)
				);

		SimpleFeatureSource sfs = dataStore.getFeatureSource(tableName);
		SimpleFeatureCollection fc = sfs.getFeatures(query);
		FeatureIterator<SimpleFeature> iterator = fc.features();
		try {
			while (iterator.hasNext()) {
				SimpleFeature sf = iterator.next();
				System.out.println(sf.getID());
			}
		}finally {
			((WrappingFeatureIterator)iterator).close();
		}
	}

	private static DataStore createDataStore() throws SQLException, IOException{
		Map<String,Object> params = new HashMap<String,Object>();
		params.put( "dbtype", MSSQL_DBTYE);
		params.put( "host", MSSQL_HOST);
		params.put( "port", MSSQL_PORT);
		params.put( "schema", MSSQL_SCHEMA);
		params.put( "database", MSSQL_DATABASENAME);
		params.put( "user", MSSQL_USER);
		params.put( "passwd", MSSQL_PASSWORD);
		params.put(JDBCDataStoreFactory.EXPOSE_PK.key, Boolean.TRUE);
		DataStore dataStore = DataStoreFinder.getDataStore(params);
		return dataStore;
	}

	private static void insert(DataStore dataStore, String tableName, List<SimpleFeature> features) throws SQLException {
		SimpleFeatureStore store;
		try {
			store = (SimpleFeatureStore) dataStore.getFeatureSource(tableName);
		}
		catch (IOException e) {
			throw new RuntimeException(e);
		}
		ListFeatureCollection fc = new ListFeatureCollection(((SimpleFeatureStore)store).getSchema(), features);
		Transaction transaction = new DefaultTransaction("Insert" + new Random().nextInt());
		store.setTransaction(transaction);
		try {

			List<FeatureId> keys = store.addFeatures(fc);
			System.out.println(keys);
			transaction.commit();
		}
		catch (Exception e) {
			try {
				transaction.rollback();
			}
			catch (IOException e1) {
				throw new IllegalStateException(e1);
			}
		}
		finally {
			try {
				transaction.close();
			}
			catch (IOException e) {
			}
		}
	}
}