Re: in(...) clause and PreparedStatement
От | Sergey Pariev |
---|---|
Тема | Re: in(...) clause and PreparedStatement |
Дата | |
Msg-id | 42AEDD6F.5030501@tnet.dp.ua обсуждение исходный текст |
Ответ на | Re: in(...) clause and PreparedStatement (Csaba Nagy <nagy@ecircle-ag.com>) |
Список | pgsql-jdbc |
Thanks a lot for the insightful advice !!! I can't understand how I haven't get such design by myself - it seems so clear now :). It will definitely improve my code and overall design a lot. Thanks again and best regards, Sergey. Csaba Nagy пишет: >Sergey, > >Doing big processing in chunks is a good thing to do. It will avoid long >running transactions, and allow you to interrupt the operation if >needed. >However, there might be a better way to do it then retrieving the id's >and using "IN": use a temporary table to store the id's, and then use >the subselect query on that table. It should be fast, cause it will only >contain the id's you want to process. The "temporary" table could easily >be a permanent table, and contain more than one set of processing ids, >and a special "transactionid" field you can select on. After processing >you can delete the unnecessary rows to keep the table small, and >probably you want to vacuum it often. >The big advantage is that with this solution you can use prepared >statements, the code will be simpler, chunking is easily achieved by >only selecting so many ids to the temp table, and it is probably also >the fastest way you can process the data, as you don't have to move any >data back and forth between the server and the client. > >HTH, >Csaba. > > >On Tue, 2005-06-14 at 12:20, Sergey Pariev wrote: > > >>Thanks to all for the instant and detailed replies. >> >>I have to say I posted simplified example of the query I need to run, in >>reality I don't know how many items will be in the IN clause. I have to >>do 3 queries (update, then insert ... select from this table and delete >>) on particular table given the set of keys which I retrieve in the >>other query, which it complex so I can't dublicate it 3 times - so I >>can't just write >> >>SELECT * FROM mytable WHERE t_id IN (select t_id from other_table where >>...). >> >>Currently I retrieve keys at first and concatenate them into string like >>1,2,3,... , then I issue my queries like >> >>st.executeUpdate("update mytable set a_field = 1 where t_id IN ("+keys+")" ; >> >>and so on. It is working as for now, but is really ugly, so I desided to >>improve things and was just wandering if there a way to use variable >>number of parameters. >> >>Considering the thing you guys wrote I'm propably will rewrite my code >>to use fixed number of parameters - it wouldn' t be too hard since I'm >>processing it in chunks anyway. It will be only in the next version of >>my app though - don't want to fix the thing which are working :). >> >>Thanks again, Sergey. >> >>Csaba Nagy пишет: >> >> >> >>>Sergey, >>> >>>Additionally to what Dave wrote you: if you want to use the prepared >>>statement with variable number of parameters, you can sometimes use a >>>query with a fixed number of parameters, and if you have more >>>parameters, execute it chunk-wise, if you have less parameters then set >>>the additional ones to null. This variant complicates your code >>>considerably though, but the query is prepared and reusable. I would >>>think it only matters if you reuse the prepared statement for a large >>>number of executions. >>> >>>This only works if you don't have to have all the parameters processed >>>in one statement, i.e. you can chunk your query and the final cumulated >>>results will be the same. >>> >>>In any other case you will not be able to use prepared statements, i.e. >>>you'll need to build your query each time. It is still advisable to do >>>it via JDBC prepared statements, and not build it directly by hand, >>>because that will take care for all the escaping necessary for your >>>parameter values. This means you should build a query with as many ? >>>signs as many parameters you have, and then set them in a loop or so. >>> >>>HTH, >>>Csaba. >>> >>> >>>On Mon, 2005-06-13 at 15:07, Dave Cramer wrote: >>> >>> >>> >>> >>>>Yes, because it thinks "1,2,3" is a string >>>> >>>>you would have to do >>>> >>>>IN(?,?,?) >>>> >>>>then >>>> >>>>setObject(1, 1); >>>>setObject(2, 2); >>>>setObject(3, 3); >>>> >>>>Dave >>>> >>>>On 13-Jun-05, at 9:08 AM, Sergey Pariev wrote: >>>> >>>> >>>> >>>> >>>> >>>>>Hi all. >>>>> It may be stupid question, but is there way to use >>>>>PreparedStatement with queries like >>>>>SELECT * FROM mytable WHERE t_id IN (1,2,3) ? >>>>> I've googled but haven't found nothing explicitly said on this >>>>>topic. >>>>> I've tried the following >>>>> PreparedStatement st = conn.prepareStatement("SELECT * FROM >>>>>mytable WHERE t_id IN ( ? )"); >>>>> st.setObject(1,"1,2,3"); >>>>> >>>>> and get error complaining on type mismatch. >>>>> >>>>> Thanks in advance, Sergey. >>>>> >>>>>---------------------------(end of >>>>>broadcast)--------------------------- >>>>>TIP 2: you can get off all lists at once with the unregister command >>>>> (send "unregister YourEmailAddressHere" to >>>>>majordomo@postgresql.org) >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>---------------------------(end of broadcast)--------------------------- >>>>TIP 6: Have you searched our list archives? >>>> >>>> http://archives.postgresql.org >>>> >>>> >>>> >>>> >>> >>> >>> >>> >>---------------------------(end of broadcast)--------------------------- >>TIP 9: the planner will ignore your desire to choose an index scan if your >> joining column's datatypes do not match >> >> > > >---------------------------(end of broadcast)--------------------------- >TIP 7: don't forget to increase your free space map settings > >
В списке pgsql-jdbc по дате отправления: