Skip to content

Commit bcb02ea

Browse files
pborissowpborissow
committed
- Fixed bug in the getRecordset() method in the Connection class where errors were not being propagated
 - Added new getRecordset() method to the Connection class to allow users more fine grained control of the Recordset options - Updated the javaxt.utils.Date class to return javaxt.utils.Date in all getter and setter methods - Fixed bug in the init() method in the Model class to handle idle prepared statements git-svn-id: svn://192.168.0.80/JavaXT/javaxt-core@1507 2c7b0aa6-e0b2-3c4e-bb4a-8b65b6c465ff
1 parent dc3b3d5 commit bcb02ea

File tree

3 files changed

+195
-74
lines changed

3 files changed

+195
-74
lines changed

‎src/javaxt/sql/Connection.java‎

Lines changed: 121 additions & 24 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,7 @@
11
package javaxt.sql;
22
import java.sql.SQLException;
33
import javaxt.utils.Generator;
4+
import java.util.*;
45

56
//******************************************************************************
67
//** Connection Class
@@ -182,18 +183,125 @@ public void close(){
182183
//** getRecordset
183184
//**************************************************************************
184185
/** Used to execute a SQL statement and returns a Recordset as an iterator.
185-
* This simplifies using the Recordset object insofar as it eliminate the
186-
* need to call the hasNext(), moveNext(), and close() methods. Instead,
187-
* you can execute a query and iterate through records like this:
188-
<pre>
189-
Connection conn = db.getConnection();
190-
for (Recordset rs : conn.getRecordset("select distinct(name) from contacts")){
186+
* Example:
187+
<pre>
188+
try (javaxt.sql.Connection conn = db.getConnection()){
189+
for (Recordset rs : conn.getRecordset("select distinct(first_name) from contacts")){
190+
System.out.println(rs.getValue(0));
191+
}
192+
}
193+
catch(Exception e){
194+
e.printStackTrace();
195+
}
196+
</pre>
197+
* Note that records returned by this method are read-only. See the other
198+
* getRecordset() methods for options to create or update records.
199+
*/
200+
public Generator<Recordset> getRecordset(String sql) throws SQLException {
201+
return getRecordset(sql, true);
202+
}
203+
204+
205+
//**************************************************************************
206+
//** getRecordset
207+
//**************************************************************************
208+
/** Used to execute a SQL statement and returns a Recordset as an iterator.
209+
* Provides an option to return records that are read-only or editable. To
210+
* perform a query with read-only records, do something like this:
211+
<pre>
212+
try (javaxt.sql.Connection conn = db.getConnection()){
213+
for (Recordset rs : conn.getRecordset("select * from contacts", true)){
191214
System.out.println(rs.getValue(0));
192215
}
193-
conn.close();
194-
</pre>
216+
}
217+
catch(Exception e){
218+
e.printStackTrace();
219+
}
220+
</pre>
221+
* To insert records, do something like this:
222+
<pre>
223+
try (javaxt.sql.Connection conn = db.getConnection()){
224+
for (Recordset rs : conn.getRecordset("select * from contacts where id=-1", false)){
225+
rs.addNew();
226+
rs.setValue("first_name", "John");
227+
rs.setValue("last_name", "Smith");
228+
rs.update();
229+
}
230+
}
231+
catch(Exception e){
232+
e.printStackTrace();
233+
}
234+
</pre>
235+
* To update existing records, do something like this:
236+
<pre>
237+
try (javaxt.sql.Connection conn = db.getConnection()){
238+
for (Recordset rs : conn.getRecordset("select * from contacts where last_name='Smith'", false)){
239+
String firstName = rs.getValue("first_name").toString();
240+
if (firstName.equals("John")){
241+
rs.setValue("name", "Jonathan");
242+
rs.update();
243+
}
244+
}
245+
}
246+
catch(Exception e){
247+
e.printStackTrace();
248+
}
249+
</pre>
250+
*/
251+
public Generator<Recordset> getRecordset(String sql, boolean readOnly) throws SQLException {
252+
HashMap<String, Object> props = new HashMap<>();
253+
props.put("readOnly", readOnly);
254+
if (readOnly) props.put("fetchSize", 1000);
255+
return getRecordset(sql, props);
256+
}
257+
258+
259+
//**************************************************************************
260+
//** getRecordset
261+
//**************************************************************************
262+
/** Used to execute a SQL statement and returns a Recordset as an iterator.
263+
* Example:
264+
<pre>
265+
try (javaxt.sql.Connection conn = db.getConnection()){
266+
for (Recordset rs : conn.getRecordset("select * from contacts",
267+
new HashMap&lt;String, Object&gt;() {{
268+
put("readOnly", true);
269+
put("fetchSize", 1000);
270+
}}))
271+
{
272+
273+
System.out.println(rs.getValue("first_name") + " " + rs.getValue("last_name"));
274+
}
275+
}
276+
catch(Exception e){
277+
e.printStackTrace();
278+
}
279+
</pre>
280+
* @param sql Query statement. This parameter is required.
281+
* @param props Recordset options (e.g. readOnly, fetchSize, batchSize).
282+
* See the Recordset class for more information about this properties. This
283+
* parameter is optional.
195284
*/
196-
public Generator<Recordset> getRecordset(final String sql, final boolean readOnly) throws SQLException {
285+
public Generator<Recordset> getRecordset(final String sql, Map<String, Object> props) throws SQLException {
286+
287+
if (props==null){
288+
props = new HashMap<>();
289+
props.put("readOnly", true);
290+
props.put("fetchSize", 1000);
291+
}
292+
293+
Boolean readOnly = new Value(props.get("readOnly")).toBoolean();
294+
if (readOnly==null) readOnly = true;
295+
Integer fetchSize = new Value(props.get("fetchSize")).toInteger();
296+
if (fetchSize==null) fetchSize = 1000;
297+
Integer batchSize = new Value(props.get("batchSize")).toInteger();
298+
if (batchSize==null) batchSize = 0;
299+
300+
301+
final boolean _readOnly = readOnly;
302+
final int _fetchSize = fetchSize;
303+
final int _batchSize = batchSize;
304+
197305

198306
final Connection conn = this;
199307
try (Generator g = new Generator<Recordset>(){
@@ -202,15 +310,16 @@ public Generator<Recordset> getRecordset(final String sql, final boolean readOnl
202310
@Override
203311
public void run() throws InterruptedException {
204312
rs = new Recordset();
205-
if (readOnly) rs.setFetchSize(1000);
313+
if (_readOnly) rs.setFetchSize(_fetchSize);
206314
try{
207-
rs.open(sql, conn, readOnly);
315+
rs.open(sql, conn, _readOnly);
316+
if (!_readOnly) rs.setBatchSize(_batchSize);
208317
while (rs.next()){
209318
this.yield(rs);
210319
}
211320
}
212321
catch(Exception e){
213-
InterruptedException ex = new InterruptedException(e.getMessage());
322+
RuntimeException ex = new RuntimeException(e.getMessage());
214323
ex.setStackTrace(e.getStackTrace());
215324
throw ex;
216325
}
@@ -227,18 +336,6 @@ public void close() {
227336
}
228337

229338

230-
//**************************************************************************
231-
//** getRecordset
232-
//**************************************************************************
233-
/** Used to execute a SQL statement and returns a Recordset as an iterator.
234-
* The Recordset is read-only. Use the other getRecordset() method for
235-
* creating and updating records.
236-
*/
237-
public Generator<Recordset> getRecordset(String sql) throws SQLException {
238-
return getRecordset(sql, true);
239-
}
240-
241-
242339
//**************************************************************************
243340
//** execute
244341
//**************************************************************************

‎src/javaxt/sql/Model.java‎

Lines changed: 39 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -161,9 +161,10 @@ protected final void init(long id) throws SQLException {
161161
* to a field. For example, "ST_AsText(coordinate) as coordinate".
162162
* @deprecated This method will be removed in a future release.
163163
*/
164-
protected final void init(long id, String...fieldNames) throws SQLException {
164+
private final void init(long id, String...fieldNames) throws SQLException {
165165

166166

167+
//Compile query
167168
StringBuilder sql = new StringBuilder("select ");
168169
boolean addID = true;
169170
for (int i=0; i<fieldNames.length; i++){
@@ -178,14 +179,14 @@ protected final void init(long id, String...fieldNames) throws SQLException {
178179
sql.append(" where id=");
179180

180181

182+
//Execute query
183+
PreparedStatement stmt = null;
184+
String query = sql.toString() + "?";
181185
try{
182186

183-
//Execute query using a prepared statement
187+
//Get or create prepared statement
184188
synchronized(sqlCache){
185-
186-
//Get or create a prepared statement from the sql cache
187-
String query = sql.toString() + "?";
188-
PreparedStatement stmt = sqlCache.get(query);
189+
stmt = sqlCache.get(query);
189190
if (stmt==null){
190191
Connection conn = getConnection(this.getClass());
191192
stmt = conn.getConnection().prepareStatement(query);
@@ -194,36 +195,44 @@ protected final void init(long id, String...fieldNames) throws SQLException {
194195

195196
//TODO: Launch thread to close idle connections
196197
}
198+
}
197199

198200

199-
//Execute prepared statement
200-
stmt.setLong(1, id);
201-
java.sql.ResultSet rs = stmt.executeQuery();
202-
if (!rs.next()){
203-
rs.close();
204-
throw new IllegalArgumentException();
205-
}
206-
207-
update(rs);
208-
this.id = id;
209-
201+
//Execute prepared statement
202+
stmt.setLong(1, id);
203+
java.sql.ResultSet rs = stmt.executeQuery();
204+
if (!rs.next()){
210205
rs.close();
206+
throw new IllegalArgumentException();
211207
}
212208

209+
update(rs);
210+
this.id = id;
211+
212+
rs.close();
213+
213214
}
214215
catch(IllegalArgumentException e){
215216
throw new SQLException(modelName + " not found");
216217
}
217218
catch(Exception e){
218219

219220

221+
if (stmt!=null && stmt.getConnection().isClosed()){
222+
synchronized(sqlCache){
223+
sqlCache.remove(query);
224+
sqlCache.notifyAll();
225+
}
226+
}
227+
228+
220229
//Execute query without a prepared statement
221230
Connection conn = null;
222231
try{
223232
conn = getConnection(this.getClass());
224233

225234
Recordset rs = new Recordset();
226-
String query = sql.toString() + id;
235+
query = sql.toString() + id;
227236
rs.open(query, conn);
228237
if (rs.EOF){
229238
rs.close();
@@ -278,7 +287,7 @@ public void save() throws SQLException {
278287

279288

280289
//Identify and remove fields that we do not want to update in the database
281-
ArrayList<java.lang.reflect.Field> arr = new ArrayList<java.lang.reflect.Field>();
290+
ArrayList<java.lang.reflect.Field> arr = new ArrayList<>();
282291
it = fields.keySet().iterator();
283292
while (it.hasNext()){
284293
java.lang.reflect.Field f = it.next();
@@ -468,7 +477,17 @@ else if (packageName.startsWith("javaxt.geospatial.geometry") ||
468477
stmt.executeUpdate();
469478
}
470479
catch(SQLException e){
471-
throw Exception("Failed to save " + className + ". " + e.getMessage(), e);
480+
481+
if (stmt!=null && stmt.getConnection().isClosed()){
482+
synchronized(insertStatements){
483+
insertStatements.remove(className);
484+
insertStatements.notifyAll();
485+
}
486+
save();
487+
}
488+
else{
489+
throw Exception("Failed to save " + className + ". " + e.getMessage(), e);
490+
}
472491
}
473492

474493

0 commit comments

Comments
 (0)