1 /*
  2  * To change this template, choose Tools | Templates
  3  * and open the template in the editor.
  4  */
  5 
  6 package db_adapter;
  7 
  8 /**
  9  *
 10  * @author sameldin
 11  */
 12 
 13 import java.io.*;
 14 import java.sql.Connection;
 15 import java.sql.DriverManager;
 16 import java.sql.PreparedStatement;
 17 import java.sql.ResultSet;
 18 import java.sql.SQLException;
 19 import java.util.ArrayList;
 20 import java.util.Hashtable;
 21 import java.util.MissingResourceException;
 22 import java.util.ResourceBundle;
 23 
 24 //import org.apache.log4j.Logger;
 25 //import org.apache.log4j.PropertyConfigurator;
 26 
 27 import utils.*;
 28 import constants.*;
 29 
 30 public class Batch
 31 {
 32     Connection  localConnection = null;
 33 
 34     public Batch()
 35     {
 36         BD_Connector localBD_Connector = new BD_Connector();
 37         localConnection = localBD_Connector.getConnection();
 38     }
 39     /*
 40      *
 41      */
 42      public int simpleBatchDelete(String passedTableName)
 43      {
 44         if(null == localConnection)
 45             return(Constants.ERROR_RUN);
 46         PreparedStatement   localPreparedStatement      = null;
 47         //==========================
 48         try
 49         {
 50                 // DELETE FROM my_table
 51                 // WHERE id in (17, 23, 64) AND year=2012 AND value=16
 52                 String qryString        = "";
 53                 String table_1        = DatabaseKeysPropertyManager.getParameter(passedTableName);
 54                 //==========================
 55                 qryString       =   "DELETE FROM "
 56                                 + table_1
 57                                 + " WHERE id in (17, 23, 64) AND year=2012 AND value=16 ";
 58 
 59                 localPreparedStatement = localConnection.prepareStatement(qryString);
 60                 localPreparedStatement.executeQuery();
 61                 localPreparedStatement.close();
 62         }
 63         catch(MissingResourceException eMissingResourceException)
 64         {
 65             eMissingResourceException.printStackTrace();
 66             return(Constants.ERROR_RUN);
 67         }
 68         catch(SQLException eSQLException)
 69         {
 70             eSQLException.printStackTrace();
 71             return(Constants.ERROR_RUN);
 72         }
 73 
 74         return(Constants.NORMAL_RUN);
 75      }
 76     /*
 77      *
 78      */
 79      public int SimpleBatchInsert(String passedTableName, ArrayList passedArrayList)
 80      {
 81         // UPDATE DEPT SET MGRNO=? WHERE DEPTNO=?");
 82         if(null == localConnection)
 83             return(Constants.ERROR_RUN);
 84         PreparedStatement   localPreparedStatement      = null;
 85         ResultSet           localResultSet              = null;
 86         //==========================
 87         try
 88         {
 89 
 90                 String qryString        = "";
 91                 String tableName        = DatabaseKeysPropertyManager.getParameter(passedTableName);
 92                 //==========================
 93                 qryString       =       "UPDATE "
 94                                 + tableName
 95                                 + "SET (item_number, TXT2, TXT3. TXT4) "
 96                                 + "VALUES(?, ?, ?, ?)";
 97                 // First batch
 98                 localPreparedStatement.setString(1, "itevalue");
 99                 localPreparedStatement.setString(2, "xxx");
100                 localPreparedStatement.setString(3, "ccc");
101                 localPreparedStatement.setString(4, "vvvv");
102                 localPreparedStatement.addBatch();
103                 //=============================
104                 // Second batch
105                 localPreparedStatement.setString(1, "itevalue");
106                 localPreparedStatement.setString(2, "xxx");
107                 localPreparedStatement.setString(3, "ccc");
108                 localPreparedStatement.setString(4, "vvvv");
109                 localPreparedStatement.addBatch();
110                 //==============================
111                 localPreparedStatement = localConnection.prepareStatement(qryString);
112                 //-------------------------
113                 // PreparedStatement.executeBatch()  returns -1 which is illegal.
114                 //  the return value equals the number of rows affected by all SQL statements executed by Statement.executeBatch(). 
115                 int [] numUpdates   =   localPreparedStatement.executeBatch();
116                 localPreparedStatement.close();
117         }
118         catch(MissingResourceException eMissingResourceException)
119         {
120             eMissingResourceException.printStackTrace();
121             return(Constants.ERROR_RUN);
122         }
123         catch(SQLException eSQLException)
124         {
125             eSQLException.printStackTrace();
126             return(Constants.ERROR_RUN);
127         }
128         return(Constants.NORMAL_RUN);
129      }
130     /*
131      *
132      */
133      public int batchInsert(String passedTableName, ArrayList passedArrayList)
134      {
135         if(null == localConnection)
136             return(Constants.ERROR_RUN);
137         PreparedStatement   localPreparedStatement      = null;
138         ResultSet           localResultSet              = null;
139         //==========================
140         try
141         {
142             // INSERT INTO users (username, email) VALUES ('Jo', 'jo@email.com')
143             // ON DUPLICATE KEY UPDATE email = 'jo@email.com'
144 
145                 String qryString        = "";
146                 String tableName        = DatabaseKeysPropertyManager.getParameter(passedTableName);
147                 //==========================
148                 qryString       =       "INSERT INTO "
149                                                 + tableName
150                                                 + "(item_number, TXT2, TXT3, APP_ID, TXT1, LST_CHG_BY_JOB, LST_CHG_BY_HQR, LST_CHG_BY_USR, LST_CHG_DATE, LST_CHG_TIME) "
151                                                 + "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
152                 localPreparedStatement = localConnection.prepareStatement(qryString);
153                 //-------------------------
154                 // dates operations
155                 java.util.Date utilDate = new java.util.Date();
156                 java.sql.Date  sqlDate  = new java.sql.Date(utilDate.getTime());
157                 java.sql.Time  sqlTime  =  new java.sql.Time(utilDate.getTime());
158 
159                 for(int count = 0; count < passedArrayList.size(); count++)
160                 {
161                         localPreparedStatement.setString(1, "");
162                         localPreparedStatement.setString(2, "");
163                         localPreparedStatement.setString(3, "");
164                         localPreparedStatement.setString(4, "");
165                         localPreparedStatement.setString(5, "");
166                         localPreparedStatement.setString(6, "");
167                         localPreparedStatement.setString(7, "");
168                         localPreparedStatement.setString(8, "");
169                         localPreparedStatement.setDate(9, sqlDate);
170                         localPreparedStatement.setTime(10, sqlTime);
171                         // add the bacth
172                         localPreparedStatement.addBatch();
173                 }
174                 localPreparedStatement.executeBatch();
175                 localPreparedStatement.close();
176         }
177         catch(MissingResourceException eMissingResourceException)
178         {
179             eMissingResourceException.printStackTrace();
180             return(Constants.ERROR_RUN);
181         }
182         catch(SQLException eSQLException)
183         {
184             eSQLException.printStackTrace();
185             return(Constants.ERROR_RUN);
186         }
187         return(Constants.NORMAL_RUN);
188      }
189 
190 }
191 
192