db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <de...@segel.com>
Subject RE: updatable selects
Date Tue, 20 Nov 2007 21:13:45 GMT
Again, it depends on the application.

If you're embedding derby, then you'll want to hold a connection open as
long as your application is alive. The reason being that it costs about
100ms to open a JDBC connection to a database compared with 10ms for an
execute stmt. (YMMV but the order of magnitude exists in setting up the
connection, and that's the point.)

There are other reasons why you'd want to maintain the connection. Caching
of prepared statements as an example...

So, I'd rethink your design. The cost of the connection object isn't always
that expensive vs some of the underlying objects held/referenced by the
connection object. 


> -----Original Message-----
> From: Developer Dude [mailto:developer.dude@comcast.net]
> Sent: Tuesday, November 20, 2007 12:40 PM
> To: 'Derby Discussion'
> Subject: RE: updatable selects
> 
> Or client/server based period.
> 
> My current use of Derby is as a Swing client side cache for server objects
> that can number in the millions for any given view (multiple views may be
> open) and that I don't want to hold in memory in order to show in a view.
> So
> I use the embedded version solely (the server I am hitting uses SQLServer,
> or Oracle, depending on the clients needs/wants).
> 
> Still, I don't hold open a connection for any longer than I need to run a
> CRUD operation (single or batch). I do all the DB/data store ops in the
> background and notify the model when the ops change it. The notify methods
> use invokeLater() so that I can keep the Swing thread and the background
> threads separate. I have no problem opening and closing many connections
> (one connection per thread, I use a multi-reader/single writer lock in the
> methods).
> 
> I try to keep the model and the view as disconnected from the backing data
> store as possible via a processor interface that handle all CRUD
> operations
> (there are background processors to populate the data store and model from
> the server). It is much cleaner and I can swap in a different caching
> store
> as needed without changing a lot of model code, or having users of the
> model
> worry about the complexities of the data store. The data store can be
> reused
> for different models and views.
> 
> -----Original Message-----
> From: msegel@dbrack01.segel.com [mailto:msegel@dbrack01.segel.com]
> Sent: Tuesday, November 20, 2007 9:33 AM
> To: Derby Discussion
> Subject: Re: updatable selects
> 
> Depends on the app.
> 
> Not everything is web based.
> Especially if you've embedded the engine in your app you'll want to keep
> the
> connection for a long time.
> 
> Sent via BlackBerry by AT&T
> 
> -----Original Message-----
> From: "Developer Dude" <developer.dude@comcast.net>
> 
> Date: Tue, 20 Nov 2007 09:33:59
> To:"'Derby Discussion'" <derby-user@db.apache.org>
> Subject: RE: updatable selects
> 
> 
> Do you really want to hold a connection open for that long? I believe
> connections can time out.
> 
> Who knows how long the user may let that screen sit there in edit mode?
> 
> -----Original Message-----
> From: Erik Steen Jakobsen [mailto:esj@logodata.dk]
> Sent: Tuesday, November 20, 2007 12:33 AM
> To: Derby Discussion
> Subject: RE: updatable selects
> 
> 
> I've investigated further ...
> Documentation says (as I read it! ) that type_scroll_insensitive  should
> give updatable selects (ie : resultset which is updatable).
> 
> 
> But only ResultSet.TYPE_FORWARD_ONLY (!) gives the functionality I seek
> but
> without the ease.
> And even though I can use it... it is a logical pain to me Since model of
> derby/jdbc/swing seems to be :
> 
> database -> resultset -> modeldata
> datebase <- resultset <- modeldata
> 
> then this picture gives a nice possibility ... given that I can lock the
> rows in the database.
> 
> 1: You present a "view" to a kill  ...
> 2: the user updates freely while you are listening to the actions, you
> move
> the cursor in the resultset and updates it accordingly .
> 
> 3:Use ends action , you move the cursor to the updated rows and you
> commit.
> or in case of user regrets ... you just don't.-
> 
> 
> That would be beautiful and elegant programming ...KISS!
> But I have been away from sql programming for a very long time.
> So I guess I read the documentation wrong:-) Or seek the wrong solution
> 
> Test code
> ---- CLIP ---
> //ps this code is only a slightly changed version of the documentation.
> --
> // You'll need to define a databse("testdb") with a table "tab1" with id,
> empname, addr1, addr2,account where account is an int
> 
>    /*
>  * testit.java
>  *
>  * Created on 19. november 2007, 22:12
>  *
>  * To change this template, choose Tools | Template Manager
>  * and open the template in the editor.
>  */
> 
> package testjavadb;
> 
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.ResultSet;
> import java.sql.SQLException;
> import java.sql.Statement;
> 
> /**
>  *
>  * @author esj
>  */
> public class testit
> {
> 
>     /** Creates a new instance of testit */
>     public static void main(String[] args)
>     {
>         try
>         {
> //            Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
>             Class.forName("org.apache.derby.jdbc.ClientDriver");
>         } catch (ClassNotFoundException ex)
>         {
>             ex.printStackTrace();
>         }
> 
>         try
>         {
> //            Connection conn =
> DriverManager.getConnection("jdbc:derby:C:/Documents and
> Settings/esj/.netbeans/5.5.1/derby/testdb");
> 
>             Connection conn =
> DriverManager.getConnection("jdbc:derby://localhost:1527/testdb");
> 
>             conn.setAutoCommit(false);
> 
> // Create the statement with concurrency mode CONCUR_UPDATABLE // to allow
> result sets to be updatable //OBS OBS OBS // only forward_only CAN make
> the
> code work!
> //            Statement stmt =
> conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
> 
>             Statement stmt =
> conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
>                         ResultSet.CONCUR_UPDATABLE,
>                         ResultSet.CLOSE_CURSORS_AT_COMMIT);
> 
> // this will NOT WORK ... ONLY ResultSet.TYPE_FORWARD_ONLY // Updatable
> statements have some requirements // for example, select must be on a
> single
> table
> 
>             ResultSet uprs = stmt.executeQuery(
>                         "SELECT * " +
>                         "FROM \"tab1\" FOR UPDATE of \"account\""); //
> only
> account can be updated (according to docs)
>             String theDept="viby";
> 
> // lOOk out this is not legal with forward only.-)
>             uprs.absolute(2);
> //
> 
>             while (uprs.next())
>             {
>                 String empname = uprs.getString("empname");
>                 String dep = uprs.getString("addr2");
>                 int no = uprs.getInt("id");
>                 int account  = uprs.getInt("account");
>                 if (dep.equals(theDept))
>                 {
> // if the current row meets our criteria, // update the updatable column
> in
> the row
>                     account+=(no +1);
>                     uprs.updateInt("account",account );
>                     uprs.updateRow();
>                     System.out.println("Updating account for employee:"
> +
>                                 empname +";" + dep + " to " + account);
>                 }
>             }
>             conn.commit(); // commit the transaction // close object
>             uprs.close();
>             stmt.close();
> // Close connection if the application does not need it any more
>             conn.close();
>         } catch (SQLException ex)
>         {
>             ex.printStackTrace();
>         }
>     }
> }
> 
> --- end clip ---
> -----Original Message-----
> From: Dag.Wanvik@Sun.COM [mailto:Dag.Wanvik@Sun.COM]
> Sent: 19. november 2007 16:45
> To: Derby Discussion
> Subject: Re: updatable selects
> 
> Erik Steen Jakobsen <esj@logodata.dk> writes:
> 
> > All I get from derby is resultsets that are read_only when I ask for
> > concurrent updatable....
> 
> You can update through JDBC result sets if you specify
> ResultSet.CONCUR_UPDATABLE. You do not even need for specify "FOR UPDATE"
> in
> that case in the underlying query (default if you use CONCUR_UPDATABLE).
> 
> But not all queries are updatable, cf. this section of the reference
> manual:
> 
> http://db.apache.org/derby/docs/dev/ref/rrefsqlj41360.html#rrefsqlj41360
> __sqlj15384
> 
> Essentially, only simple, single table selects are updatable.
> 
> Hope this helps! If not, can you provide a repro?
> 
> Thanks,
> Dag
> 
> 
> 
> 
> 




Mime
View raw message