JDBC를 이용한 Dummy Data Insert
1. java project 생성
2. pom.xm에 mysql jar 추가
<!-- MySql --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.6</version> </dependency> |
3. Source Code
|
public class ConnectDB {
// JDBC driver name and database URL private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; private static final String DB_URL = "jdbc:mysql://xxx.xxx.xxx.xxx:3306/schema"; // Database credentials private static final String USER = "ID"; private static final String PASS = "PASSWORD";
private static Connection conn = null; private static Statement stmt = null; private static PreparedStatement pstmt = null;
public static void makeDummy() { long start = System.currentTimeMillis(); int idx = 1000000; long count = 1000000;
try{ //STEP 2: Register JDBC driver Class.forName(JDBC_DRIVER); //STEP 3: Open a connection System.out.println("Connecting to database..."); conn = DriverManager.getConnection(DB_URL,USER,PASS); //STEP 4: Execute a query System.out.println("Creating statement..."); stmt = conn.createStatement();
// set AutoCommit false conn.setAutoCommit(false); pstmt = conn.prepareStatement( "INSERT INTO schema.tablename (" + "PHY_FILE_ID,SRC_PHY_FILE_ID,FILE_CLSF_CD,FILE_EXT_NM,FILE_SIZE,FILE_STORE_STORAGE_ID,PHY_FILE_NM,PHY_FILE_PATH_VAL,PHY_FILE_FINGERPRT_VAL,FILE_TYP_CD," + "FILE_ENC_SECT_CD,FILE_ENC_INDEX_VAL,FILE_CMPRSS_YN,ORDER_SEQ,PHY_FILE_STAT_CD,FIRST_REG_USER_ID,LAST_MOD_USER_ID,FIRST_REG_DTM,LAST_MOD_DTM)" + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,now(),now())"); // 1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7 // create the prepared statment size 18 (141984389871210000) // 111111111111000000
for (long i = 0; i < count; i++) { //PHY_FILE_ID pstmt.setBigDecimal(1, new BigDecimal("10000000000"+(idx+i))); //SRC_PHY_FILE_ID pstmt.setBigDecimal(2, new BigDecimal("142053756205350000")); //FILE_CLSF_CD pstmt.setString(3, "01"); //FILE_EXT_NM pstmt.setString(4, "jpg"); //FILE_SIZE pstmt.setLong(5, 213497); //FILE_STORE_STORAGE_ID pstmt.setInt(6, 2); //PHY_FILE_NM pstmt.setString(7, "142053756205350000"); //PHY_FILE_PATH_VAL pstmt.setString(8, "201501/1"); //PHY_FILE_FINGERPRT_VAL pstmt.setString(9, "7b1492e0b1a0d9fe024edcef94578152"); //FILE_TYP_CD pstmt.setString(10, "JPG"); //FILE_ENC_SECT_CD pstmt.setString(11, "00012"); //FILE_ENC_INDEX_VAL pstmt.setString(12, "358"); //FILE_CMPRSS_YN pstmt.setString(13, "N"); //ORDER_SEQ pstmt.setInt(14, 1); //PHY_FILE_STAT_CD pstmt.setString(15, "1000"); //FIRST_REG_USER_ID pstmt.setInt(16, 1); //LAST_MOD_USER_ID pstmt.setInt(17, 1); //FIRST_REG_DTM // DB current Time : now() //LAST_MOD_DTM // DB current Time : now()
// Add row to the batch. pstmt.addBatch(); pstmt.executeUpdate(); }
// Commit the transaction conn.commit();
//STEP 6: Clean-up environment stmt.close(); conn.close(); }catch(SQLException se){ //Handle errors for JDBC se.printStackTrace(); }catch(Exception e){ //Handle errors for Class.forName e.printStackTrace(); }finally{ //finally block used to close resources try{ if(stmt!=null) stmt.close(); }catch(SQLException se2){ se2.printStackTrace(); }// nothing we can do
try{ if(conn!=null) conn.close(); }catch(SQLException se){ se.printStackTrace(); }//end finally try }//end try
System.out.println("Goodbye!"); System.out.println("Create " + count + " Items"); long workTime = System.currentTimeMillis() - start; int ZZ = (int)(workTime % 1000); long worksec = workTime / 1000; int SS = (int)(worksec % 60); long workmin = worksec / 60; int MM = (int)(workmin % 60); int HH = (int)(workmin/60);
StringBuffer disp = new StringBuffer(); if(HH > 0) { disp.append(HH); disp.append("h "); } if(MM > 0) { disp.append(MM); disp.append("m "); } if(SS > 0) { disp.append(SS); disp.append("s "); } disp.append("."); disp.append(ZZ);
System.out.println("Working Time : " + disp.toString() ); }
public static void main(String[] args) { makeDummy(); } } |