Advertisement
Guest User

Untitled

a guest
Jan 11th, 2017
119
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 8.13 KB | None | 0 0
  1.  
  2.  
  3. //import java.sql.Connection;
  4. import java.sql.PreparedStatement;
  5. import java.sql.ResultSet;
  6. import java.sql.SQLException;
  7. import java.sql.Statement;
  8. import java.util.Vector;
  9.  
  10. import oracle.jdbc.OraclePreparedStatement;
  11. import oracle.jdbc.OracleConnection;
  12. import oracle.jdbc.OracleTypes;
  13. import oracle.jdbc.pool.OracleDataSource;
  14.  
  15.  
  16. public class Main {
  17. protected static OracleConnection conn;
  18. protected static OracleDataSource ods;
  19. protected static long idEmploye=0;
  20. private static int NB_MANAGER=200;
  21.  
  22. public static void main(String[] args) {
  23. Main a = new Main();
  24. long debut;
  25. try {
  26.  
  27. a.initDbCache(true);
  28. // a.initDb(true);
  29. debut=System.currentTimeMillis();
  30. for(int i=1;i<=NB_MANAGER;i++) {
  31. // a.InsertEmployeeInit("Manager_"+i, 50);
  32. a.InsertEmployee("Manager_"+i, 50);
  33. // a.InsertEmployeeSeq("Manager_"+i, 50);
  34. // a.InsertEmployeeSeqOptim("Manager_"+i, 50);
  35.  
  36. }
  37. System.out.println("Duree Insert ="+(System.currentTimeMillis()-debut)/1000+" s");
  38.  
  39.  
  40. debut=System.currentTimeMillis();
  41. int nbEmployee=0;
  42. for(int i=1;i<=NB_MANAGER;i++) {
  43. nbEmployee+=a.selectEmployee("Manager_"+i).size();
  44. }
  45. System.out.println("Duree Select ="+(System.currentTimeMillis()-debut)/1000+" s pour "+nbEmployee+" employes");
  46. conn.close();
  47. //ods.close();
  48. } catch (SQLException e) {
  49. // TODO Auto-generated catch block
  50. e.printStackTrace();
  51. System.out.println("Class=" + e.getStackTrace()[1].getClassName());
  52. }
  53. }
  54.  
  55. private void initDb(boolean purge) throws SQLException {
  56. ods = new OracleDataSource();
  57. ods.setURL("jdbc:oracle:thin:@127.0.0.1:1521:ELVISEN");
  58. ods.setUser("ELV_ADMIN");
  59. ods.setPassword("ELV_ADMIN");
  60. conn=(OracleConnection) ods.getConnection();
  61. conn.setAutoCommit(false);
  62.  
  63. if(purge) {
  64. System.out.println("truncate table employee");
  65. Statement stmt = conn.createStatement();
  66. stmt.execute("truncate table employee");
  67. stmt.close();
  68. System.out.println("truncate table employee - DONE");
  69. }
  70. }
  71.  
  72.  
  73. private void initDbCache(boolean purge) throws SQLException {
  74. ods = new OracleDataSource();
  75. ods.setURL("jdbc:oracle:thin:@127.0.0.1:1521:ELVISEN");
  76. ods.setUser("ELV_ADMIN");
  77. ods.setPassword("ELV_ADMIN");
  78.  
  79. // Set Implicit Cache
  80. ods.setImplicitCachingEnabled(true);
  81.  
  82. conn=(OracleConnection)ods.getConnection();
  83. conn.setAutoCommit(false);
  84. conn.setDefaultRowPrefetch(200);
  85.  
  86.  
  87. // Define Statement and preparedStatement cache
  88. conn.setStatementCacheSize(10);
  89.  
  90. // Use Implicit cache for this connection
  91. conn.setImplicitCachingEnabled(true);
  92.  
  93. if(purge) {
  94. // Purge employee table
  95. System.out.println("truncate table employee");
  96. Statement stmt = conn.createStatement();
  97. stmt.execute("truncate table employee");
  98. stmt.close();
  99. System.out.println("truncate table employee - DONE");
  100. }
  101.  
  102.  
  103. }
  104.  
  105. public int InsertEmployeeInit(String ManagerName, int nbEmployee) {
  106. int success = 0;
  107. long managerId=idEmploye++;
  108. try {
  109. // Create preparedStatement
  110. PreparedStatement pstmt = conn.prepareStatement("Insert into EMPLOYEE(EMPLOYEE_ID, NAME, MANAGER_ID,DEPT_ID) values (?,?,?,1)");
  111.  
  112. // Set Manager data
  113. pstmt.setLong(1,managerId);
  114. pstmt.setString(2, ManagerName);
  115. pstmt.setNull(3, OracleTypes.NUMBER);
  116.  
  117. success += pstmt.executeUpdate(); // Send to database
  118. //conn.commit();
  119. // Create employee for Manager
  120. for(int nb=1;nb<=nbEmployee;nb++) {
  121. pstmt.setLong(1,idEmploye++);
  122. pstmt.setString(2, "Robert_"+idEmploye);
  123. pstmt.setLong(3, managerId);
  124. success += pstmt.executeUpdate(); // Send to database
  125. //conn.commit();
  126. }
  127.  
  128. // Commit transaction Manager + Employee
  129. conn.commit();
  130.  
  131. // Close statement
  132. pstmt.close();
  133. } catch (SQLException ex) {
  134. ex.printStackTrace();
  135. try {
  136. conn.rollback();
  137. } catch (SQLException ex1) {
  138. ex1.printStackTrace();
  139. }
  140. }
  141. return success;
  142. }
  143.  
  144. public int InsertEmployee(String ManagerName, int nbEmployee) {
  145. int success = 0;
  146. long managerId=idEmploye++;
  147. try {
  148. // Create preparedStatement
  149. PreparedStatement pstmt = conn.prepareStatement("Insert into EMPLOYEE(EMPLOYEE_ID, NAME, MANAGER_ID,DEPT_ID) values (?,?,?,1)");
  150.  
  151. // Set Manager data
  152. pstmt.setLong(1,managerId);
  153. pstmt.setString(2, ManagerName);
  154. pstmt.setNull(3, OracleTypes.NUMBER);
  155.  
  156. success += pstmt.executeUpdate(); // Send to database
  157. //pstmt.addBatch(); // store into JDBC driver
  158.  
  159. // Create employee for Manager
  160. for(int nb=1;nb<=nbEmployee;nb++) {
  161. pstmt.setLong(1,idEmploye++);
  162. pstmt.setString(2, "Robert_"+idEmploye);
  163. pstmt.setLong(3, managerId);
  164. // success += pstmt.executeUpdate(); // Send to database
  165. pstmt.addBatch(); // store into JDBC driver
  166. }
  167. success = pstmt.executeBatch().length; // Send all batch into database
  168.  
  169. // Commit transaction Manager + Employee
  170. conn.commit();
  171.  
  172. // Close statement
  173. pstmt.close();
  174. } catch (SQLException ex) {
  175. ex.printStackTrace();
  176. try {
  177. conn.rollback();
  178. } catch (SQLException ex1) {
  179. ex1.printStackTrace();
  180. }
  181. }
  182. return success;
  183. }
  184.  
  185. public int InsertEmployeeSeq(String ManagerName, int nbEmployee) {
  186. int success = 0;
  187. int idManager;
  188. try {
  189. PreparedStatement stmt = conn.prepareStatement("select seq_emp.nextval from dual");
  190. ResultSet res= stmt.executeQuery();
  191. if(res.next()) {
  192. idManager = res.getInt(1);
  193.  
  194. PreparedStatement pstmtMgr = conn.prepareStatement("Insert into EMPLOYEE(EMPLOYEE_ID, NAME,DEPT_ID) values (?,?,1)");
  195. PreparedStatement pstmtEmp = conn.prepareStatement("Insert into EMPLOYEE(EMPLOYEE_ID, NAME, MANAGER_ID,DEPT_ID) values (SEQ_EMP.nextval,?,?,1)");
  196. pstmtMgr.setInt(1, idManager);
  197. pstmtMgr.setString(2, ManagerName);
  198. success=pstmtMgr.executeUpdate();
  199. for(int nb=1;nb<=nbEmployee;nb++) {
  200. pstmtEmp.setString(1, "Robert_"+(idEmploye++));
  201. pstmtEmp.setInt(2,idManager);
  202. pstmtEmp.addBatch();
  203. }
  204.  
  205. success += pstmtEmp.executeBatch().length;
  206. conn.commit();
  207. pstmtEmp.close();
  208. pstmtMgr.close();
  209. }
  210. res.close();
  211. stmt.close();
  212. stmt=null;
  213. } catch (SQLException ex) {
  214. ex.printStackTrace();
  215. try {
  216. conn.rollback();
  217. } catch (SQLException ex1) {
  218. ex1.printStackTrace();
  219. }
  220. }
  221. return success;
  222. }
  223.  
  224. public int InsertEmployeeSeqOptim(String ManagerName, int nbEmployee) {
  225. int success = 0;
  226. int idManager;
  227. try {
  228. String cols[] = {"EMPLOYEE_ID"};
  229. OraclePreparedStatement pstmtMgr = (OraclePreparedStatement)conn.prepareStatement("Insert into EMPLOYEE(EMPLOYEE_ID, NAME,DEPT_ID) values (SEQ_EMP.nextval,?,1)",cols);
  230. pstmtMgr.setString(1, ManagerName);
  231. success=pstmtMgr.executeUpdate();
  232. ResultSet res=pstmtMgr.getGeneratedKeys();
  233. if(res.next()) {
  234. idManager = res.getInt(1);
  235. // System.out.println("idManager="+idManager);
  236. PreparedStatement pstmtEmp = conn.prepareStatement("Insert into EMPLOYEE(EMPLOYEE_ID, NAME, MANAGER_ID,DEPT_ID) values (SEQ_EMP.nextval,?,?,1)");
  237. for(int nb=1;nb<=nbEmployee;nb++) {
  238. pstmtEmp.setString(1, "Robert_"+(idEmploye++));
  239. pstmtEmp.setInt(2,idManager);
  240. pstmtEmp.addBatch();
  241. }
  242.  
  243. success += pstmtEmp.executeBatch().length;
  244. conn.commit();
  245. pstmtEmp.close();
  246. res.close();
  247. } else {
  248. System.out.println("!!!! pas trouve");
  249. }
  250. pstmtMgr.close();
  251. } catch (SQLException ex) {
  252. ex.printStackTrace();
  253. try {
  254. conn.rollback();
  255. } catch (SQLException ex1) {
  256. ex1.printStackTrace();
  257. }
  258. }
  259. return success;
  260. }
  261.  
  262. public Vector<String> selectEmployee(String ManagerName) {
  263. Vector<String> listName = new Vector<String>();
  264. try {
  265. PreparedStatement stmt = conn.prepareStatement("select e.name from employee e, employee m where m.employee_id=e.manager_id and m.name=?");
  266. ((OraclePreparedStatement)stmt).setRowPrefetch(100);
  267. stmt.setString(1,ManagerName);
  268. ResultSet res = stmt.executeQuery();
  269. while(res.next()) {
  270. listName.add(res.getString(1));
  271. }
  272. res.close();
  273. stmt.close();
  274. } catch (SQLException ex) {
  275. ex.printStackTrace();
  276. }
  277. return listName;
  278.  
  279.  
  280. }
  281.  
  282.  
  283. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement