jb3cqhZm

1@Service
2@Transactional
3public class TableGeoJsonGenerator {
4
5	@Autowired
6	private EntityManager em;
7	
8	public String initTable(String name, ArrayList<Object> properties, ArrayList<Object> geometries) {
9		String nameTableFinal = "";
10		
11		if (!chekifExistTable(name)) {
12			nameTableFinal = name.toLowerCase();
13			createTableWithColumns(nameTableFinal);
14			insertIntoTable(nameTableFinal, properties, geometries);
15		} else {
16			UUID uuid = UUID.randomUUID();
17			nameTableFinal = name.toLowerCase() + "_" + uuid.toString().replace("-", "_");
18			createTableWithColumns(nameTableFinal);
19			insertIntoTable(nameTableFinal, properties, geometries);
20		}
21		return nameTableFinal;
22	}
23	
24	private boolean chekifExistTable(String name) {
25		String SQL = "select table_name from information_schema.tables where table_schema = 'public'";
26		return executeSQLWithResults(SQL, name);
27	}
28	
29	private void createTableWithColumns(String name) {
30		String SQL = "CREATE TABLE IF NOT EXISTS " + name + " ( table_id SERIAL, properties jsonb not null, geom geometry(GeometryZ,4326), primary key (table_id));";
31		executeSQL(SQL);
32	}
33	
34	private void insertIntoTable(String nameTable, ArrayList<Object> properties, ArrayList<Object> geometries) {
35		
36		for (int i=0; i<properties.size(); i++) {
37			Object property = properties.get(i);
38			Object geometry = geometries.get(i);
39			String SQL = "INSERT INTO " + nameTable + " ( properties ) VALUES ( '" + property + "', '" + geometry + "' );";
40			executeSQL(SQL);			
41		}
42	}
43	
44	private void executeSQL(String SQL) {
45		try {
46			Query query = em.createNativeQuery(SQL);
47			query.executeUpdate();				
48		} catch(Throwable e) {
49			throw e;
50		} finally {
51			em.close();
52		}
53	}
54	
55	private boolean executeSQLWithResults(String SQL, String nameTable) {
56		boolean found = false;
57		try {
58			Query query = em.createNativeQuery(SQL);
59			List<?> list = query.getResultList();
60			for (Object result : list) {
61				if (nameTable.toLowerCase().equals(result.toString())) {
62					found = true;
63				}
64			}
65		} catch(Throwable e) {
66			throw e;
67		} finally {
68			em.close();
69		}
70		return found;
71	}
72}