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 UpdateSQLStatements
 31 {
 32     Connection  localConnection = null;
 33 
 34     public UpdateSQLStatements()
 35     {
 36         BD_Connector localBD_Connector = new BD_Connector();
 37         localConnection = localBD_Connector.getConnection();
 38     }
 39     /*
 40      *
 41      */
 42      public int simpleUpdate(String passedTableName, String name, String email, int passedID)
 43      {
 44         if(null == localConnection)
 45             return(Constants.ERROR_RUN);
 46         PreparedStatement   localPreparedStatement      = null;
 47         //==========================
 48         try
 49         {
 50             // UPDATE table_name
 51             // SET column1=value1,column2=value2,...
 52             // WHERE some_column=some_value;
 53             // Notice the WHERE clause in the SQL UPDATE statement!
 54             // The WHERE clause specifies which record or records that should be updated.
 55             // If you omit the WHERE clause, all records will be updated!
 56 
 57                 String qryString        = "";
 58                 String tableName        = DatabaseKeysPropertyManager.getParameter(passedTableName);
 59                 //==========================
 60                 qryString       =       "UPDATE "
 61                                 + tableName
 62                                 + "SET email="
 63                                 + email
 64                                 + ", ID="
 65                                 + passedID
 66                                 + "WHERE name="
 67                                 + name;
 68                 localPreparedStatement = localConnection.prepareStatement(qryString);
 69                 localPreparedStatement.executeQuery();
 70                 localPreparedStatement.close();
 71         }
 72         catch(MissingResourceException eMissingResourceException)
 73         {
 74             eMissingResourceException.printStackTrace();
 75             return(Constants.ERROR_RUN);
 76         }
 77         catch(SQLException eSQLException)
 78         {
 79             eSQLException.printStackTrace();
 80             return(Constants.ERROR_RUN);
 81         }
 82         return(Constants.NORMAL_RUN);
 83      }
 84     /*
 85      *
 86      */
 87      public int updateRecordFromAnotherTable(String customersTableName, String suppliersTableName)
 88      {
 89         if(null == localConnection)
 90             return(Constants.ERROR_RUN);
 91         PreparedStatement   localPreparedStatement      = null;
 92         //==========================
 93         try
 94         {
 95             // SQL UPDATE statement, whenever a supplier_id matched a customer_id value,
 96             // the supplier_name would be overwritten to the customer name from the customers table.
 97             // UPDATE suppliers
 98             // SET supplier_name = (SELECT customers.name
 99             //                      FROM customers
100             //                      WHERE customers.customer_id = suppliers.supplier_id)
101             // WHERE EXISTS (SELECT customers.name
102             //               FROM customers
103             //               WHERE customers.customer_id = suppliers.supplier_id);
104                 String qryString        = "";
105                 String customersTable        = DatabaseKeysPropertyManager.getParameter(customersTableName);
106                 String suppliersTable        = DatabaseKeysPropertyManager.getParameter(suppliersTableName);
107                 //==========================
108                 qryString       =   "UPDATE "
109                                 + suppliersTable
110                                 + " SET supplier_name = (SELECT "
111                                 + customersTable
112                                 + ".name "
113                                 + "FROM "
114                                 + customersTable
115                                 + " WHERE "
116                                 + customersTable
117                                 + ".customer_id = "
118                                 + suppliersTable
119                                 + ".supplier_id)"
120                                 + "WHERE EXISTS (SELECT "
121                                 + customersTable
122                                 + ".name "
123                                 + "FROM "
124                                 + customersTable
125                                 + " WHERE "
126                                 + customersTable
127                                 + ".customer_id = "
128                                 + suppliersTable
129                                 + ".supplier_id);";
130 
131                 localPreparedStatement = localConnection.prepareStatement(qryString);
132                 localPreparedStatement.executeQuery();
133                 localPreparedStatement.close();
134          }
135         catch(MissingResourceException eMissingResourceException)
136         {
137             eMissingResourceException.printStackTrace();
138             return(Constants.ERROR_RUN);
139         }
140         catch(SQLException eSQLException)
141         {
142             eSQLException.printStackTrace();
143             return(Constants.ERROR_RUN);
144         }
145         return(Constants.NORMAL_RUN);
146      }
147     /*
148      *
149      */
150      public int update_DUPLICATE_KEY_UPDATE(String customersTableName, String name, int value_1, int value_2)
151      {
152         if(null == localConnection)
153             return(Constants.ERROR_RUN);
154         PreparedStatement   localPreparedStatement      = null;
155         //==========================
156         try
157         {
158             //  INSERT INTO table (name,value_1_ID,value_2_ID) VALUES (1,2,3)
159             // ON DUPLICATE KEY UPDATE value_2_ID=value_2;
160 
161                 String qryString        = "";
162                 String customersTable        = DatabaseKeysPropertyManager.getParameter(customersTableName);
163                 //==========================
164                 qryString       =   "INSERT INTO "
165                                 + customersTable
166                                 + "(name,value_1_ID,value_2_ID) "
167                                 + "VALUES("
168                                 +name
169                                 + ","
170                                 + value_1
171                                 + ", "
172                                 + value_2
173                                 + ");"
174                                 + "ON DUPLICATE KEY UPDATE value_2_ID="
175                                 + value_2;
176 
177                 localPreparedStatement = localConnection.prepareStatement(qryString);
178                 localPreparedStatement.executeQuery();
179                 localPreparedStatement.close();
180          }
181         catch(MissingResourceException eMissingResourceException)
182         {
183             eMissingResourceException.printStackTrace();
184             return(Constants.ERROR_RUN);
185         }
186         catch(SQLException eSQLException)
187         {
188             eSQLException.printStackTrace();
189             return(Constants.ERROR_RUN);
190         }
191         return(Constants.NORMAL_RUN);
192      }
193 }
194 
195