【Java-DB】DB에 데이터 삽입(MySQL/Oracle)

【Java-DB】DB에 데이터 삽입(MySQL/Oracle)

1. 설명

안녕하세요 코이킹입니다.

이 포스트에서 다룰 예제는 Java로 INSERT SQL을 실행하여 데이터를 삽입하는 코드가 되겠습니다.

2. 소스코드

- 메서드

public boolean executeUpdateSql(String sql, List params, Connection connection) throws SQLException { Connection con = connection; PreparedStatement ps = null; ps = con.prepareStatement(sql); setSqlParams(ps, params); int res = ps.executeUpdate(); if (res > 0) { con.commit(); return true; } else { con.rollback(); return false; } } private boolean setSqlParams(PreparedStatement ps, List params) throws SQLException { int index = 1; for (Object obj : params) { if (obj instanceof String) { System.out.println("param String value =["+(String)obj+"]"); ps.setString(index, (String)obj); index++; } else if (obj instanceof Integer) { System.out.println("param Integer value =["+(Integer)obj+"]"); ps.setInt(index, (Integer)obj); index++; } else if (obj instanceof Long) { System.out.println("param Long value =["+(Long)obj+"]"); ps.setLong(index, (Long)obj); index++; } else if (obj instanceof Double) { System.out.println("param Double value =["+(Double)obj+"]"); ps.setDouble(index, (Double)obj); index++; } else if (obj instanceof Boolean) { System.out.println("param Boolean value =["+(Boolean)obj+"]"); ps.setBoolean(index, (Boolean)obj); index++; } else if (obj instanceof java.util.Date) { System.out.println("param Date value =["+((java.util.Date)obj).getTime()+"]"); ps.setDate(index, new java.sql.Date(((java.util.Date)obj).getTime())); index++; } else { System.out.println("Parameter Error "); return false; } } return true; }

- 메인

public class DB_02_Insert { static FileUtil fu = FileUtil.getInstance(); static DBUtil du = DBUtil.getInstance(); public static void main(String[] args) { try { for (String str : args ) { System.out.println("Param : "+str); } String sql = ""; List list; // MySQL Insert SQL sql = "INSERT INTO ITEM (ITEM_NAME, ITEM_DESCRIPTION, MAKER_CODE, PRICE, SALE_STATUS, UPDATED_DATE) VALUES (?,?,?,?,?,?)"; // Set Parameter list = new ArrayList(); list.add("ITEM_NAME_JDBC_05"); list.add("ITEM_DESCRIPTION_JDBC_05"); list.add("129"); list.add(12000); list.add(0); list.add(new Date()); // DB Connection getConnection(args[0]); // SQL execute if (du.executeUpdateSql(sql, list)) { System.out.println("Success Insert in MySQL!!"); } else { System.out.println("Failure Insert in MySQL!!"); } // Oracle Insert SQL sql = "INSERT INTO ITEM (ITEM_ID, ITEM_NAME, ITEM_DESCRIPTION, MAKER_CODE, PRICE, SALE_STATUS, UPDATED_DATE) VALUES (SEQ_ITEM.NEXTVAL,?,?,?,?,?,?)"; // Set Parameter list = new ArrayList(); list.add("ITEM_NAME_JDBC_01"); list.add("ITEM_DESCRIPTION_JDBC_01"); list.add("109"); list.add(10000); list.add(0); list.add(new Date()); // DB Connection getConnection(args[1]); // SQL execute if (du.executeUpdateSql(sql, list)) { System.out.println("Success Insert in Oracle!!"); } else { System.out.println("Failure Insert in Oracle!!"); } System.exit(0); } catch (Exception e) { e.printStackTrace(); System.exit(1); } } public static Connection getConnection(String conInfoFilePath) { List conInfos = fu.readPerLine(new File(conInfoFilePath)); du.setDBParam( conInfos.get(0), conInfos.get(1), conInfos.get(2), conInfos.get(3), conInfos.get(4), conInfos.get(5), conInfos.get(6), conInfos.get(7), conInfos.get(8), conInfos.get(9) ); du.initConnection(); Connection con = du.getConnection(); System.out.println("Connection Info = "+con); return con; } }

- DB 테이블 정보 (MySQL)

CREATE TABLE `item` ( `ITEM_ID` int(11) NOT NULL AUTO_INCREMENT, `ITEM_NAME` varchar(100) NOT NULL, `ITEM_DESCRIPTION` text NOT NULL, `MAKER_CODE` varchar(50) NOT NULL, `PRICE` int(11) NOT NULL, `SALE_STATUS` int(11) NOT NULL, `IMAGES` text, `UPDATED_DATE` timestamp NULL DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

- DB 테이블 정보 (Oracle)

CREATE TABLE ITEM ( ITEM_ID NUMBER(11) NOT NULL, ITEM_NAME VARCHAR2(100) NOT NULL, ITEM_DESCRIPTION VARCHAR2(1000) NOT NULL, MAKER_CODE VARCHAR2(50) NOT NULL, PRICE NUMBER(11) NOT NULL, SALE_STATUS NUMBER(11) NOT NULL, IMAGES VARCHAR2(2000) NOT NULL, UPDATED_DATE TIMESTAMP ) CREATE SEQUENCE SEQ_ITEM;

3. 실행결과【Windows(이클립스) / phpMyAdmin / Oracle SQL Developer】

4. 전체 코드

https://github.com/leeyoungseung/template-java

from http://koiking.tistory.com/50 by ccl(A) rewrite - 2021-10-24 12:27:43