Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- //import java.sql.Connection;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- import java.util.Vector;
- import oracle.jdbc.OraclePreparedStatement;
- import oracle.jdbc.OracleConnection;
- import oracle.jdbc.OracleTypes;
- import oracle.jdbc.pool.OracleDataSource;
- public class Main {
- protected static OracleConnection conn;
- protected static OracleDataSource ods;
- protected static long idEmploye=0;
- private static int NB_MANAGER=200;
- public static void main(String[] args) {
- Main a = new Main();
- long debut;
- try {
- a.initDbCache(true);
- // a.initDb(true);
- debut=System.currentTimeMillis();
- for(int i=1;i<=NB_MANAGER;i++) {
- // a.InsertEmployeeInit("Manager_"+i, 50);
- a.InsertEmployee("Manager_"+i, 50);
- // a.InsertEmployeeSeq("Manager_"+i, 50);
- // a.InsertEmployeeSeqOptim("Manager_"+i, 50);
- }
- System.out.println("Duree Insert ="+(System.currentTimeMillis()-debut)/1000+" s");
- debut=System.currentTimeMillis();
- int nbEmployee=0;
- for(int i=1;i<=NB_MANAGER;i++) {
- nbEmployee+=a.selectEmployee("Manager_"+i).size();
- }
- System.out.println("Duree Select ="+(System.currentTimeMillis()-debut)/1000+" s pour "+nbEmployee+" employes");
- conn.close();
- //ods.close();
- } catch (SQLException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- System.out.println("Class=" + e.getStackTrace()[1].getClassName());
- }
- }
- private void initDb(boolean purge) throws SQLException {
- ods = new OracleDataSource();
- ods.setURL("jdbc:oracle:thin:@127.0.0.1:1521:ELVISEN");
- ods.setUser("ELV_ADMIN");
- ods.setPassword("ELV_ADMIN");
- conn=(OracleConnection) ods.getConnection();
- conn.setAutoCommit(false);
- if(purge) {
- System.out.println("truncate table employee");
- Statement stmt = conn.createStatement();
- stmt.execute("truncate table employee");
- stmt.close();
- System.out.println("truncate table employee - DONE");
- }
- }
- private void initDbCache(boolean purge) throws SQLException {
- ods = new OracleDataSource();
- ods.setURL("jdbc:oracle:thin:@127.0.0.1:1521:ELVISEN");
- ods.setUser("ELV_ADMIN");
- ods.setPassword("ELV_ADMIN");
- // Set Implicit Cache
- ods.setImplicitCachingEnabled(true);
- conn=(OracleConnection)ods.getConnection();
- conn.setAutoCommit(false);
- conn.setDefaultRowPrefetch(200);
- // Define Statement and preparedStatement cache
- conn.setStatementCacheSize(10);
- // Use Implicit cache for this connection
- conn.setImplicitCachingEnabled(true);
- if(purge) {
- // Purge employee table
- System.out.println("truncate table employee");
- Statement stmt = conn.createStatement();
- stmt.execute("truncate table employee");
- stmt.close();
- System.out.println("truncate table employee - DONE");
- }
- }
- public int InsertEmployeeInit(String ManagerName, int nbEmployee) {
- int success = 0;
- long managerId=idEmploye++;
- try {
- // Create preparedStatement
- PreparedStatement pstmt = conn.prepareStatement("Insert into EMPLOYEE(EMPLOYEE_ID, NAME, MANAGER_ID,DEPT_ID) values (?,?,?,1)");
- // Set Manager data
- pstmt.setLong(1,managerId);
- pstmt.setString(2, ManagerName);
- pstmt.setNull(3, OracleTypes.NUMBER);
- success += pstmt.executeUpdate(); // Send to database
- //conn.commit();
- // Create employee for Manager
- for(int nb=1;nb<=nbEmployee;nb++) {
- pstmt.setLong(1,idEmploye++);
- pstmt.setString(2, "Robert_"+idEmploye);
- pstmt.setLong(3, managerId);
- success += pstmt.executeUpdate(); // Send to database
- //conn.commit();
- }
- // Commit transaction Manager + Employee
- conn.commit();
- // Close statement
- pstmt.close();
- } catch (SQLException ex) {
- ex.printStackTrace();
- try {
- conn.rollback();
- } catch (SQLException ex1) {
- ex1.printStackTrace();
- }
- }
- return success;
- }
- public int InsertEmployee(String ManagerName, int nbEmployee) {
- int success = 0;
- long managerId=idEmploye++;
- try {
- // Create preparedStatement
- PreparedStatement pstmt = conn.prepareStatement("Insert into EMPLOYEE(EMPLOYEE_ID, NAME, MANAGER_ID,DEPT_ID) values (?,?,?,1)");
- // Set Manager data
- pstmt.setLong(1,managerId);
- pstmt.setString(2, ManagerName);
- pstmt.setNull(3, OracleTypes.NUMBER);
- success += pstmt.executeUpdate(); // Send to database
- //pstmt.addBatch(); // store into JDBC driver
- // Create employee for Manager
- for(int nb=1;nb<=nbEmployee;nb++) {
- pstmt.setLong(1,idEmploye++);
- pstmt.setString(2, "Robert_"+idEmploye);
- pstmt.setLong(3, managerId);
- // success += pstmt.executeUpdate(); // Send to database
- pstmt.addBatch(); // store into JDBC driver
- }
- success = pstmt.executeBatch().length; // Send all batch into database
- // Commit transaction Manager + Employee
- conn.commit();
- // Close statement
- pstmt.close();
- } catch (SQLException ex) {
- ex.printStackTrace();
- try {
- conn.rollback();
- } catch (SQLException ex1) {
- ex1.printStackTrace();
- }
- }
- return success;
- }
- public int InsertEmployeeSeq(String ManagerName, int nbEmployee) {
- int success = 0;
- int idManager;
- try {
- PreparedStatement stmt = conn.prepareStatement("select seq_emp.nextval from dual");
- ResultSet res= stmt.executeQuery();
- if(res.next()) {
- idManager = res.getInt(1);
- PreparedStatement pstmtMgr = conn.prepareStatement("Insert into EMPLOYEE(EMPLOYEE_ID, NAME,DEPT_ID) values (?,?,1)");
- PreparedStatement pstmtEmp = conn.prepareStatement("Insert into EMPLOYEE(EMPLOYEE_ID, NAME, MANAGER_ID,DEPT_ID) values (SEQ_EMP.nextval,?,?,1)");
- pstmtMgr.setInt(1, idManager);
- pstmtMgr.setString(2, ManagerName);
- success=pstmtMgr.executeUpdate();
- for(int nb=1;nb<=nbEmployee;nb++) {
- pstmtEmp.setString(1, "Robert_"+(idEmploye++));
- pstmtEmp.setInt(2,idManager);
- pstmtEmp.addBatch();
- }
- success += pstmtEmp.executeBatch().length;
- conn.commit();
- pstmtEmp.close();
- pstmtMgr.close();
- }
- res.close();
- stmt.close();
- stmt=null;
- } catch (SQLException ex) {
- ex.printStackTrace();
- try {
- conn.rollback();
- } catch (SQLException ex1) {
- ex1.printStackTrace();
- }
- }
- return success;
- }
- public int InsertEmployeeSeqOptim(String ManagerName, int nbEmployee) {
- int success = 0;
- int idManager;
- try {
- String cols[] = {"EMPLOYEE_ID"};
- OraclePreparedStatement pstmtMgr = (OraclePreparedStatement)conn.prepareStatement("Insert into EMPLOYEE(EMPLOYEE_ID, NAME,DEPT_ID) values (SEQ_EMP.nextval,?,1)",cols);
- pstmtMgr.setString(1, ManagerName);
- success=pstmtMgr.executeUpdate();
- ResultSet res=pstmtMgr.getGeneratedKeys();
- if(res.next()) {
- idManager = res.getInt(1);
- // System.out.println("idManager="+idManager);
- PreparedStatement pstmtEmp = conn.prepareStatement("Insert into EMPLOYEE(EMPLOYEE_ID, NAME, MANAGER_ID,DEPT_ID) values (SEQ_EMP.nextval,?,?,1)");
- for(int nb=1;nb<=nbEmployee;nb++) {
- pstmtEmp.setString(1, "Robert_"+(idEmploye++));
- pstmtEmp.setInt(2,idManager);
- pstmtEmp.addBatch();
- }
- success += pstmtEmp.executeBatch().length;
- conn.commit();
- pstmtEmp.close();
- res.close();
- } else {
- System.out.println("!!!! pas trouve");
- }
- pstmtMgr.close();
- } catch (SQLException ex) {
- ex.printStackTrace();
- try {
- conn.rollback();
- } catch (SQLException ex1) {
- ex1.printStackTrace();
- }
- }
- return success;
- }
- public Vector<String> selectEmployee(String ManagerName) {
- Vector<String> listName = new Vector<String>();
- try {
- PreparedStatement stmt = conn.prepareStatement("select e.name from employee e, employee m where m.employee_id=e.manager_id and m.name=?");
- ((OraclePreparedStatement)stmt).setRowPrefetch(100);
- stmt.setString(1,ManagerName);
- ResultSet res = stmt.executeQuery();
- while(res.next()) {
- listName.add(res.getString(1));
- }
- res.close();
- stmt.close();
- } catch (SQLException ex) {
- ex.printStackTrace();
- }
- return listName;
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement