다건데이터 DB INSERT

뚱이·2022년 11월 29일
0

DB

목록 보기
3/3

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삽입");
				}
			}

0개의 댓글