D:\JavaFrameworks\InsuranceFramework\src\java\db_adapter\InsertSQLStatements.java |
1
2
3
4
5
6 package db_adapter;
7
8
9
10 @author
11
12 import java.io.*;
13 import java.sql.Connection;
14 import java.sql.DriverManager;
15 import java.sql.PreparedStatement;
16 import java.sql.ResultSet;
17 import java.sql.SQLException;
18 import java.util.ArrayList;
19 import java.util.Hashtable;
20 import java.util.MissingResourceException;
21 import java.util.ResourceBundle;
22
23
24
25
26 import utils.*;
27 import constants.*;
28
29 public class InsertSQLStatements
30 {
31 Connection localConnection = null;
32
33 public InsertSQLStatements()
34 {
35 BD_Connector localBD_Connector = new BD_Connector();
36 localConnection = localBD_Connector.getConnection();
37 }
38
39
40
41 public int insert(String passedTableName, String name, String email)
42 {
43 if(null == localConnection)
44 return(Constants.ERROR_RUN);
45 PreparedStatement localPreparedStatement = null;
46
47 try
48 {
49
50
51
52 String qryString = "";
53 String tableName = DatabaseKeysPropertyManager.getParameter(passedTableName);
54
55 qryString = "INSERT INTO "
56 + tableName
57 + " (username, email)"
58 + "VALUES "
59 + "( "
60 + "'"
61 + name
62 + "',"
63 + "'"
64 + email
65 + "')"
66 + "ON DUPLICATE KEY UPDATE email = '"
67 + email
68 + "'";
69 localPreparedStatement = localConnection.prepareStatement(qryString);
70 localPreparedStatement.executeQuery();
71 localPreparedStatement.close();
72 }
73 catch(MissingResourceException eMissingResourceException)
74 {
75 eMissingResourceException.printStackTrace();
76 return(Constants.ERROR_RUN);
77 }
78 catch(SQLException eSQLException)
79 {
80 eSQLException.printStackTrace();
81 return(Constants.ERROR_RUN);
82 }
83 return(Constants.NORMAL_RUN);
84 }
85
86
87
88 public int insertUpdate(String passedTableName, String name, String email)
89 {
90 if(null == localConnection)
91 return(Constants.ERROR_RUN);
92 PreparedStatement localPreparedStatement = null;
93 ResultSet localResultSet = null;
94
95 try
96 {
97
98
99
100 String qryString = "";
101 String tableName = DatabaseKeysPropertyManager.getParameter(passedTableName);
102
103 qryString = "SELECT * FROM "
104 + tableName
105 + " WHERE username = "
106 + "'"
107 + name
108 + "'";
109 localPreparedStatement = localConnection.prepareStatement(qryString);
110 localPreparedStatement.executeQuery();
111
112 int numberOfRecords = 0;
113 localResultSet = localPreparedStatement.executeQuery();
114 if(localResultSet.next())
115 numberOfRecords = 1;
116
117 localPreparedStatement.close();
118
119 switch(numberOfRecords)
120 {
121 case 0:
122 qryString = "INSERT INTO "
123 + tableName
124 + " (username, email)"
125 + "VALUES "
126 + "( "
127 + "'"
128 + name
129 + "',"
130 + "'"
131 + email
132 + "')";
133 localPreparedStatement = localConnection.prepareStatement(qryString);
134 localPreparedStatement.executeQuery();
135 localPreparedStatement.close();
136 break;
137 default:
138 qryString = "UPDATE "
139 + tableName
140 + " (username, email)"
141 + "VALUES "
142 + "( "
143 + "'"
144 + name
145 + "',"
146 + "'"
147 + email
148 + "')";
149 localPreparedStatement = localConnection.prepareStatement(qryString);
150 localPreparedStatement.executeQuery();
151 localPreparedStatement.close();
152 }
153 }
154 catch(MissingResourceException eMissingResourceException)
155 {
156 eMissingResourceException.printStackTrace();
157 return(Constants.ERROR_RUN);
158 }
159 catch(SQLException eSQLException)
160 {
161 eSQLException.printStackTrace();
162 return(Constants.ERROR_RUN);
163 }
164 return(Constants.NORMAL_RUN);
165 }
166
167
168
169 public int batchInsert(String passedTableName, ArrayList passedArrayList)
170 {
171 if(null == localConnection)
172 return(Constants.ERROR_RUN);
173 PreparedStatement localPreparedStatement = null;
174 ResultSet localResultSet = null;
175
176 try
177 {
178
179
180
181 String qryString = "";
182 String tableName = DatabaseKeysPropertyManager.getParameter(passedTableName);
183
184 qryString = "INSERT INTO "
185 + tableName
186 + "(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) "
187 + "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
188 localPreparedStatement = localConnection.prepareStatement(qryString);
189
190 java.util.Date utilDate = new java.util.Date();
191 java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
192 java.sql.Time sqlTime = new java.sql.Time(utilDate.getTime());
193
194 for(int count = 0; count < passedArrayList.size(); count++)
195 {
196 localPreparedStatement.setString(1, "");
197 localPreparedStatement.setString(2, "");
198 localPreparedStatement.setString(3, "");
199 localPreparedStatement.setString(4, "");
200 localPreparedStatement.setString(5, "");
201 localPreparedStatement.setString(6, "");
202 localPreparedStatement.setString(7, "");
203 localPreparedStatement.setString(8, "");
204 localPreparedStatement.setDate(9, sqlDate);
205 localPreparedStatement.setTime(10, sqlTime);
206 localPreparedStatement.addBatch();
207 }
208 localPreparedStatement.executeBatch();
209 localPreparedStatement.close();
210 }
211 catch(MissingResourceException eMissingResourceException)
212 {
213 eMissingResourceException.printStackTrace();
214 return(Constants.ERROR_RUN);
215 }
216 catch(SQLException eSQLException)
217 {
218 eSQLException.printStackTrace();
219 return(Constants.ERROR_RUN);
220 }
221 return(Constants.NORMAL_RUN);
222 }
223
224 }
225
226