JSON 데이터 DB INSERT
1. 테이블 생성
String query = "CREATE TABLE STATION_DATA(";
query += " STATION_SEQ NUMBER(10) PRIMARY KEY NOT NULL, /* 순번 */ ";
query += " ADDR VARCHAR2(200), /* 주소 */ ";
query += " CHARGETP VARCHAR2(100), /* 충전기타입 */ ";
query += " CPID NUMBER(10), /* 충전기ID */ ";
query += " CPNM VARCHAR2(20), /* 충전기명칭 */ ";
query += " CPSTAT VARCHAR2(100), /* 충전기상태 */ ";
query += " CPTP VARCHAR2(100), /* 충전방식 */ ";
query += " CSID NUMBER(10), /* 충전소ID */ ";
query += " CSNM VARCHAR2(100), /* 충전소명칭 */ ";
query += " LAT VARCHAR2(50), /* 위도 */ ";
query += " LONGI VARCHAR2(50), /* 경도 */ ";
query += " STATUPDATETIME VARCHAR2(100)) /* 갱신시간 */ ";
2. JSON 파싱
//JSON 파일 읽기
FileReader reader = new FileReader(filePath);
//JSONParser 생성
JSONParser parser = new JSONParser();
//읽은 JSON 파일 -> JSONObject
JSONObject jsonobj = (JSONObject) parser.parse(reader);
//JSONObject -> JSONArray key = ex) data : [{},{},{},{}, ...]
JSONArray jsonArr = (JSONArray)jsonobj.get("data");
3. 데이터 INSERT
String SQL = "insert into STATION_DATA(STATION_SEQ,addr, chargeTp, cpId, cpNm, cpStat, cpTp, csId, csNm, lat, longi, statUpdatetime) values(STATION_SEQ.NEXTVAL,?,?,?,?,?,?,?,?,?,?,?)";
//PreParedStatement 객체 생성, 객체 생성시 SQL 문장 저장
pstmt = conn.prepareStatement(SQL);
//다건 JSON객체 (JSONArray)
//FOR문 활용하여 JSONArray 값 get
if(jsonArr.size()>0) {
for(int i=0; i<jsonArr.size(); i++) {
JSONObject jsonObj = (JSONObject)jsonArr.get(i);
System.out.println((String)jsonObj.get("addr"));
System.out.println((String)jsonObj.get("chargeTp"));
System.out.println((Long)jsonObj.get("cpId"));
System.out.println((String)jsonObj.get("cpNm"));
System.out.println((String)jsonObj.get("cpStat"));
System.out.println((String)jsonObj.get("cpTp"));
System.out.println((Long)jsonObj.get("csId"));
System.out.println((String)jsonObj.get("csNm"));
System.out.println((String)jsonObj.get("lat"));
System.out.println((String)jsonObj.get("longi"));
System.out.println((String)jsonObj.get("statUpdatetime"));
addr=(String)jsonObj.get("addr");
chargeTp=(String)jsonObj.get("chargeTp");
cpId=(Long)jsonObj.get("cpId");
cpNm=(String)jsonObj.get("cpNm");
cpStat=(String)jsonObj.get("cpStat");
cpTp=(String)jsonObj.get("cpTp");
csId=(Long)jsonObj.get("csId");
csNm=(String)jsonObj.get("csNm");
lat=(String)jsonObj.get("lat");
longi=(String)jsonObj.get("longi");
statUpdatetime=(String)jsonObj.get("statUpdatetime");
//psmt.set<데이터타입><? 순서, 값)
pstmt.setString(1, addr);
pstmt.setString(2, chargeTp);
pstmt.setLong(3, cpId);
pstmt.setString(4, cpNm);
pstmt.setString(5, cpStat);
pstmt.setString(6, cpTp);
pstmt.setLong(7, csId);
pstmt.setString(8, csNm);
pstmt.setString(9, lat);
pstmt.setString(10, longi);
pstmt.setString(11, statUpdatetime);
int r = pstmt.executeUpdate();
System.out.println("SQL 실행:"+r+"개 의 row삽입");
}
}