【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<Object>();
			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<Object>();
			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<String> 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