I am reading a geojson file and they can be very large, I think that the best option is to stream the geojson file and go logging it into the database with SQL manually.
Because it is not efficient with a JPA + Hibernate model, because it has many rows and even if it is lazy as soon as I remove the records it brings everything.
So I am following this:
- JAVA - Best approach to parse huge (extra large) JSON file
- https://sites.google.com/site/gson/streaming
I'm using jdbc template: https://spring.io/guides/gs/relational-data-access/
My problems are:
- I don't know how to pass the name and inputStream parameters to the RelationalDataAccessApplication class from SchemeService
- I do not know how to get the name of the table because even if it is public it only allows me to access the run method.
- I don't know how to call the dropTable and readTable functions from SchemeService.
Here is my code of RelationalDataAccessApplication Class:
@SpringBootApplication
public class RelationalDataAccessApplication implements CommandLineRunner {
private static final Logger log = LoggerFactory.getLogger(RelationalDataAccessApplication.class);
private String checkedNameTable;
public static void main(String[] args) {
SpringApplication.run(RelationalDataAccessApplication.class, args);
}
@Autowired
JdbcTemplate jdbcTemplate;
@Override
public void run(String... strings) throws Exception {
log.info("Deserialize GEOJSON");
DeserializeJSONFileToPropsAndGeom.initialize(inputStream);
List<String> properties = DeserializeJSONFileToPropsAndGeom.getProperties();
List<String> geometries = DeserializeJSONFileToPropsAndGeom.getGeometries();
log.info("CHECK IF TABLE EXISTS");
checkedNameTable= nameTable("nombre pasado por el usuario");
log.info("CREATING TABLE");
jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS " + checkedNameTable + " ( table_id SERIAL, properties jsonb not null, geom geometry(GeometryZ,4326), primary key (table_id));");
log.info("INSERT DATA");
for (int i=0; i<properties.size(); i++) {
Object property = properties.get(i).toString().replace("'", "´");
Object geometry = geometries.get(i);
String SQL = "INSERT INTO " + checkedNameTable + " ( properties, geom ) VALUES ( '" + property + "', ST_Force3D(ST_SetSRID(ST_GeomFromGeoJSON('" + geometry + "'), 4326) ));";
jdbcTemplate.batchUpdate(SQL);
}
}
public String getCheckedNameTable() {
return checkedNameTable;
}
private void dropTable(String nameTable) throws Exception {
log.info("DROP TABLE " + nameTable);
if (nameTable != null) {
String SQL = "DROP TABLE IF EXISTS " + nameTable + ";";
jdbcTemplate.execute(SQL);
}
}
private List<Map<String, Object>> readTable(String nameTable) throws Exception {
log.info("READ TABLE " + nameTable);
if (nameTable != null) {
String SQL = "SELECT table_id, CAST(properties AS text), GeometryType(geom) FROM " + nameTable + " ORDER BY table_id ASC;";
return jdbcTemplate.queryForList(SQL);
}
return null;
}
private String nameTable(String name) {
UUID uuid = UUID.randomUUID();
return (checkifExistTable(name)) ?
name.toLowerCase() :
name.toLowerCase() + "_" + uuid.toString().replace("-", "_");
}
private boolean checkifExistTable(String name) {
String SQL = "select table_name from information_schema.tables where table_schema = 'public'";
boolean exists = false;
int count = jdbcTemplate.queryForObject(SQL, new Object[] { "paramValue" }, Integer.class);
exists = count > 0;
return exists;
}
}
Here is my code of SchemeService Class:
@Service
@Transactional
public class SchemeService extends DaoCrudService {
@Autowired
private SchemeDao schemeDao;
public <T> T createScheme(final String creatorId, final String name, final String description, final InputStream inputStream, final String attachmentId, final String filePath, final Class<T> targetVOClass) {
Scheme scheme = createScheme(creatorId, name, description, inputStream, attachmentId, filePath);
return getDozerService().map(scheme, targetVOClass);
}
protected Scheme createScheme(final String creatorId, final String name, final String description, final InputStream inputStream, final String attachmentId, final String filePath) {
RelationalDataAccessApplication.main(name, inputStream);
String tableId = RelationalDataAccessApplication.getCheckedNameTable();
Scheme scheme = new Scheme();
scheme.setCreator(creatorId);
scheme.setName(name);
scheme.setDescription(description);
scheme.setTableId(tableId);
scheme.setAttachmentId(attachmentId);
scheme.setFilePath(filePath);
createScheme(scheme);
return scheme;
}
public List<Map<String, Object>> readTable(final String name) {
return RelationalDataAccessApplication.readTable(name);
}
public void deleteScheme(final SchemeCriteria schemeCriteria) {
SchemeVO scheme = read(schemeDao, schemeCriteria, SchemeVO.class);
String nameTable = scheme.getTableId();
// Call drop Table
RelationalDataAccessApplication.dropTable(nameTable);
delete(schemeDao, schemeCriteria);
}
}
