postgresql一般crudストレージプロセスリファレンス
110816 ワード
http://blog.csdn.net/cdnight/article/details/18082255
ここでは、テストを再三デバッグして成功したpostgresデータベースの単一テーブルcrudストレージ・プロシージャです.マルチ結果の戻り方については、getPageByConditionの書き方を参照してください.refcursorを使用して、cursorを返します.他のout、inoutパラメータを返すことができますが、refcursorはトランザクションで呼び出さなければなりません.だからjava端の呼び出し過程は注意しなければなりません.いいでしょう.dalテンプレートを同時に出して、みんなは直接copyで使うことができます.
PostgreSQL
対応dal呼び出しファイル:
java
http://blog.csdn.net/cdnight/article/details/18078751
http://blog.csdn.net/cdnight/article/details/18001807
http://www.cnblogs.com/stephen-liu74/category/343171.html
ここでは、テストを再三デバッグして成功したpostgresデータベースの単一テーブルcrudストレージ・プロシージャです.マルチ結果の戻り方については、getPageByConditionの書き方を参照してください.refcursorを使用して、cursorを返します.他のout、inoutパラメータを返すことができますが、refcursorはトランザクションで呼び出さなければなりません.だからjava端の呼び出し過程は注意しなければなりません.いいでしょう.dalテンプレートを同時に出して、みんなは直接copyで使うことができます.
1 /******************************************************************
2 * ：test3
3 * Made by
4 ******************************************************************/
5 --use MYDB;--
6 /******************************************************************
7 **************************** ***************************
8 ******************************************************************/
9 ------------------------------------
10 -- ： ， 。
11 -- ， ， ，
12 -- sql 。
13 -- ：
14 ---_offset int
15 ---_limit int ， ， pagesize。
16 ---_columns varchar(800)
17 ---_where varchar(800) ： where id<10 where， 。
18 ---_orderby varchar(800) ， ：order by id
19 ---_totalCount int 。
20 ---_totalPages int 。
21 ------------------------------------
22 create or replace function test3_getListByCondition(
23 INOUT pageindex INT,
24 INOUT pagesize INT,
25 IN _columns VARCHAR(800),
26 IN _where VARCHAR(800),
27 IN _orderby VARCHAR(800),
28 out _totalCount INT,
29 out _totalPages INT)
30 returns SETOF record
31 AS
32 $$
33 DECLARE condition_columns VARCHAR(800);
34 DECLARE condition_where varchar(800);
35 DECLARE condition_orderby VARCHAR(800);
36 DECLARE _dymatic_sql VARCHAR(1600);
37 DECLARE _beginNO INT;
38 DECLARE _dynamic_getCount varchar(1600);
39 DECLARE _theOffset INT;
40 DECLARE _tmpInt1 INT;
41 BEGIN
42 condition_where:=ltrim(rtrim(COALESCE(_where,'')));
43 condition_orderby:=ltrim(rtrim(COALESCE(_orderby,'order by t3id')));
44 condition_columns:=ltrim(rtrim(COALESCE(_columns,'*')));
45 -- ， sql 。
46 IF "character_length"(condition_where)>0 THEN
47 IF strpos(condition_where, 'where ')!=1 THEN
48 condition_where:='where ' || condition_where;
49 END IF;
50 END IF;
51 --order by
52 IF "character_length"(condition_orderby)>0 THEN
53 IF strpos(condition_orderby, 'order ')!=1 THEN
54 condition_orderby:='order by '||condition_orderby;
55 END IF;
56 END IF;
57
58 -- pageindex pagesize
59 IF pageindex<1 THEN
60 pageindex:=1;
61 END IF;
62 IF pagesize<1 THEN
63 pagesize:=20;
64 END IF;
65
66 _dynamic_getCount:='select count(*) from test3 '||condition_where|| ' ' ;
67 EXECUTE _dynamic_getCount INTO _totalCount;
68
69 IF _totalCount<1 THEN
70 pageindex:=1;
71 RETURN;
72 END IF;
73 --
74 _tmpInt1:=_totalCount%pagesize;
75 IF _tmpInt1=0 THEN
76 _totalPages:=_totalCount / pagesize;
77 ELSE
78 _totalPages:=(_totalCount-_tmpInt1)/pagesize+1;
79 END IF;
80
81 IF _totalPages < pageindex then
82 pageindex:=_totalPages;
83 END IF;
84
85 _theOffset:=(pageindex-1) * pagesize+1;
86
87 _dymatic_sql:='select '||condition_columns||' from test3 '||condition_where||' '||condition_orderby||' limit '||pagesize||' '|| ' offset '||_theOffset||' ';
88 --raise info ' ：%',_dymatic_sql;
89 return query EXECUTE _dymatic_sql;
90 END;
91 $$ language plpgsql VOLATILE;
92
93
94
95 ------------------------------------
96 -- ： ， 。
97 -- ， ， ，
98 -- sql 。
99 -- ：
100 ---_offset int
101 ---_limit int ， ， pagesize。
102 ---_columns varchar(800)
103 ---_where varchar(800) ： where id<10 where， 。
104 ---_orderby varchar(800) ， ：order by id
105 ---_totalCount int 。
106 ------------------------------------
107 create or replace function test3_getPageByCondition(
108 INOUT pageindex INT,
109 INOUT pagesize INT,
110 IN _columns VARCHAR(800),
111 IN _where VARCHAR(800),
112 IN _orderby VARCHAR(800),
113 out _totalCount INT,
114 out _totalPages INT,
115 out _refcursor refcursor
116 )
117 returns SETOF record
118 AS
119 $$
120 DECLARE condition_columns VARCHAR(800);
121 DECLARE condition_where varchar(800);
122 DECLARE condition_orderby VARCHAR(800);
123 DECLARE _dymatic_sql VARCHAR(1600);
124 DECLARE _beginNO INT;
125 DECLARE _dynamic_getCount varchar(1600);
126 DECLARE _theOffset INT;
127 DECLARE _tmpInt1 INT;
128 BEGIN
129 condition_where:=ltrim(rtrim(COALESCE(_where,'')));
130 condition_orderby:=ltrim(rtrim(COALESCE(_orderby,'order by t3id')));
131 condition_columns:=ltrim(rtrim(COALESCE(_columns,'*')));
132 -- ， sql 。
133 IF "character_length"(condition_where)>0 THEN
134 IF strpos(condition_where, 'where ')!=1 THEN
135 condition_where:='where ' || condition_where;
136 END IF;
137 END IF;
138 --order by
139 IF "character_length"(condition_orderby)>0 THEN
140 IF strpos(condition_orderby, 'order ')!=1 THEN
141 condition_orderby:='order by '||condition_orderby;
142 END IF;
143 END IF;
144
145 -- pageindex pagesize
146 IF pageindex<1 THEN
147 pageindex:=1;
148 END IF;
149 IF pagesize<1 THEN
150 pagesize:=20;
151 END IF;
152
153 _dynamic_getCount:='select count(*) from test3 '||condition_where|| ' ' ;
154 EXECUTE _dynamic_getCount INTO _totalCount;
155
156 IF _totalCount<1 THEN
157 pageindex:=1;
158 RETURN;
159 END IF;
160 --
161 _tmpInt1:=_totalCount%pagesize;
162 IF _tmpInt1=0 THEN
163 _totalPages:=_totalCount / pagesize;
164 ELSE
165 _totalPages:=(_totalCount-_tmpInt1)/pagesize+1;
166 END IF;
167
168 IF _totalPages < pageindex then
169 pageindex:=_totalPages;
170 END IF;
171
172 _theOffset:=(pageindex-1) * pagesize+1;
173
174 _dymatic_sql:='select '||condition_columns||' from test3 '||condition_where||' '||condition_orderby||' limit '||pagesize||' '|| ' offset '||_theOffset||' ';
175 --raise info ' ：%',_dymatic_sql;
176 open _refcursor for EXECUTE _dymatic_sql;
177 RETURN NEXT;
178 END;
179 $$ language plpgsql VOLATILE;
180
181 ------------------------------------
182 -- ：
183 ------------------------------------
184 create or replace function test3_getRecord(in _id integer)
185 returns SETOF test3
186 AS
187 $$
188 BEGIN
189 return query select * from test3 where t3id=_id LIMIT 1 OFFSET 0;
190 END;
191 $$ LANGUAGE plpgsql VOLATILE;
192
193
194 ------------------------------------
195 -- ： ， ，
196 -- ， ， ，
197 -- sql 。
198 -- ：
199 ---_topN int topN 。
200 ---_columns varchar(800)
201 ---_where varchar(800) ： where id<10 where， 。
202 ---_orderby varchar(800) ， ：order by id
203 ------------------------------------
204 create or replace function test3_getTopNbyCondition(IN _topN int,IN _columns VARCHAR(800),IN _where VARCHAR(800),IN _orderby VARCHAR(800))
205 returns SETOF test3
206 AS
207 $$
208 DECLARE condition_columns VARCHAR(800);
209 DECLARE condition_where varchar(800);
210 DECLARE condition_orderby VARCHAR(800);
211 DECLARE _dymatic_sql VARCHAR(1600);
212 BEGIN
213 condition_where:=ltrim(rtrim(COALESCE(_where,'')));
214 condition_orderby:=ltrim(rtrim(COALESCE(_orderby,'order by t3id')));
215 condition_columns:=ltrim(rtrim(COALESCE(_columns,'*')));
216
217 -- ， sql 。
218 IF "character_length"(condition_where)>0 THEN
219 IF strpos(condition_where, 'where ')!=1 THEN
220 condition_where:='where ' || condition_where;
221 END IF;
222 END IF;
223 --order by
224 IF "character_length"(condition_orderby)>0 THEN
225 IF strpos(condition_orderby, 'order ')!=1 THEN
226 condition_orderby:='order by '||condition_orderby;
227 END IF;
228 END IF;
229 _dymatic_sql:='select '||condition_columns||' from test2 '||condition_where||' '||condition_orderby||' limit '||CAST(_topN as VARCHAR)|| ' offset 0 ';
230 --raise info ' ：%',_dymatic_sql;
231 return query EXECUTE _dymatic_sql;
232 END;
233 $$ language plpgsql VOLATILE;
234
235
236 /******************************************************************
237 ***************************** ******************************
238 ******************************************************************/
239 ------------------------------------
240 -- ：
241 ------------------------------------
242 create or replace function test3_DeleteList(in ids VARCHAR(800),out status boolean,out msg VARCHAR(200))
243 returns record
244 AS
245 $$
246 DECLARE _arr_ids int[];
247 DECLARE _str_ids "text";
248 DECLARE _str_sql VARCHAR(1600);
249 DECLARE _effects int;
250 BEGIN
251
252 IF "character_length"(ids)<1 THEN
253 status:=false;
254 msg:=' ！';
255 return;
256 end if;
257 _arr_ids:=tools_str2intarray(ids, ',');
258 _str_ids:=tools_stringify(_arr_ids,',');
259 --pkey ， ， <@ pkey 。 ？
260 /* */
261 --_str_sql:='DELETE FROM test3 where t3id in ('||_str_ids||') ;';
262 --EXECUTE _str_sql;
263 /* */
264 delete from test3 where t3id =ANY( _arr_ids);
265 GET DIAGNOSTICS _effects = ROW_COUNT;
266 IF _effects>0 THEN
267 status:=true;
268 msg:=' '||_effects||' ！';
269 ELSE
270 status:=false;
271 msg:=' ！';
272 end if;
273
274 END
275 $$ LANGUAGE plpgsql VOLATILE;
276
277
278 /******************************************************************
279 **************************** *****************************
280 ******************************************************************/
281
282 ------------------------------------
283 -- ：
284 ------------------------------------
285
286 create or replace function test3_Insert(
287 in __t3name varchar(400) ,
288 in __t_birthday date ,
289 in __myage smallint ,
290 in __isadmin boolean ,
291 in __myintro text ,
292 in __price float ,
293 out __t3id integer,
294 out _status boolean,
295 out _msg varchar(200))
296 returns record AS $$
297 BEGIN
298
299 Insert into test3
300 (
301 "t3name","t_birthday","myage","isadmin","myintro","price"
302 )
303 values(
304 __t3name,__t_birthday,__myage,__isadmin,__myintro,__price
305 );
306 /* 。*/
307 if FOUND then
308 _status:=true;
309 _msg:=' .';
310 __t3id:=currval(pg_get_serial_sequence('test3', 't3id'));
311 else
312 _status:=false;
313 _msg:=' !';
314 end if;
315 end;
316 $$ LANGUAGE plpgsql VOLATILE;
317
318 ------------------------------------
319 -- ：
320 ------------------------------------
321 create or replace function test3_Update(
322 in __t3name varchar(400) ,
323 in __t_birthday date ,
324 in __myage smallint ,
325 in __isadmin boolean ,
326 in __myintro text ,
327 in __price float ,
328 in __t3id integer,
329 out _status boolean,
330 out _msg varchar(200))
331 returns record AS $$
332 BEGIN
333
334
335 update test3 set
336 "t3name"=__t3name,"t_birthday"=__t_birthday,"myage"=__myage,"isadmin"=__isadmin,"myintro"=__myintro,"price"=__price where t3id=__t3id;
337 /* 。*/
338 if FOUND then
339 _status:=true;
340 _msg:=' .';
341 else
342 _status:=false;
343 _msg:=' !';
344 end if;
345 end;
346 $$ LANGUAGE plpgsql VOLATILE;PostgreSQL
対応dal呼び出しファイル:
1 package EasisWeb.DAL;
2
3 import EasisWeb.config.DBPool;
4 import Easis.Common.StringUtil;
5 import Easis.util.DataRow;
6 import Easis.util.DataTable;
7 import Easis.util.DataTableHelper;
8 import java.util.Date;
9 import Easis.DBUtility.PooledConnection;
10 import java.sql.*;
11 import java.util.List;
12 import java.util.ArrayList;
13 import Easis.util.OperationResult;
14 import Easis.util.PagerResult;
15 import EasisWeb.Model.test3Model;
16 /**
17 * CodeGen ， “ ”
18 * ：
19 * @author
20 * @version 1.00
21 */
22 public class test3DAL {
23
24 /* column*/
25 public static final String col_t3id="test3";
26 public static final String col_t3name="test3";
27 public static final String col_t_birthday="test3";
28 public static final String col_myage="test3";
29 public static final String col_isadmin="test3";
30 public static final String col_myintro="test3";
31 public static final String col_price="test3";
32 public static final String PKColumn="t3id";
33
34
35 /**
36 * ：test3_ListByCondition
37 * ：
38 *@param pageindex
39 *@param pagesize
40 *@param columns
41 *@param condition where
42 *@param orderColumn order by
43 *
44 *@return
45 */
46
47 public PagerResult getPageListByCondition(int pageindex,int pagesize, String columns, String condition, String orderColumn){
48 PagerResult pres=new PagerResult();
49 //output
50 int _total = 0 ;
51 int _pagesize = 0 ;
52 int _pageindex = 0 ;
53 int _totalpages = 0 ;
54 //output
55 //
56 DataTable res__datatable=new DataTable();
57 try{
58 PooledConnection __myconn=DBPool.getConnection();
59 __myconn.setAutoCommit(false); // return refcursor must within a transaction
60 CallableStatement _stmt=__myconn.prepareCall("{ call test3_getPageByCondition( ?, ?, ?, ?, ?, ?, ?, ?)}");
61 _stmt.setInt(1,pageindex);
62 _stmt.setInt(2,pagesize);
63 _stmt.registerOutParameter(1,Types.INTEGER);
64 _stmt.registerOutParameter(2,Types.INTEGER);
65 _stmt.setString(3,columns);
66 _stmt.setString(4,condition);
67 _stmt.setString(5,orderColumn);
68 _stmt.registerOutParameter(6, Types.INTEGER);
69 _stmt.registerOutParameter(7, Types.INTEGER);
70 _stmt.registerOutParameter(8,Types.OTHER);
71 _stmt.execute();
72 ResultSet __rslist=(ResultSet)_stmt.getObject(8);
73 res__datatable=DataTableHelper.rs2datatable(__rslist);
74 //
75 _total=_stmt.getInt(6);
76 pres.totalrecords=_total;
77 _pageindex=_stmt.getInt(1);
78 pres.totalrecords=_total;
79 _pagesize=_stmt.getInt(2);
80 pres.pageindex=_pageindex;
81 pres.pagesize=_pagesize;
82 _totalpages=_stmt.getInt(7);
83 pres.totalpages=_totalpages;
84 pres.datasource=res__datatable;
85 //--
86 __myconn.commit();
87 __myconn.setAutoCommit(true); // ， autocommit 。
88 //
89 __rslist.close();
90 _stmt.close();
91 __myconn.close();}
92 catch (Exception __e){
93 System.out.println(" [test3DAL] List_Condition 。");
94 __e.printStackTrace();
95 }
96 return pres;
97 }
98
99 /**
100 * ：test3_Insert
101 * ：
102 *param t3id 【 】
103 t3name
104 t_birthday
105 myage
106 isadmin
107 myintro
108 price
109 *
110 *@return
111 */
112 public OperationResult Insert(test3Model model){
113 OperationResult __ores=new OperationResult();
114 /*output */
115 int t3id = 0 ;
116 DataRow returnInfo=new DataRow();
117 boolean status = false ;
118 String message = "" ;
119 /*output */
120 /* */
121
122 try{
123 PooledConnection __myconn=DBPool.getConnection();
124 CallableStatement _stmt=__myconn.prepareCall("{call test3_Insert(?,?,?,?,?,?,?,?,?)}");
125
126 _stmt.setObject(1,model.t3name,Types.VARCHAR);
127 _stmt.setObject(2,new Timestamp(model.t_birthday.getTime()),Types.DATE);
128 _stmt.setObject(3,model.myage,Types.SMALLINT);
129 _stmt.setObject(4,model.isadmin,Types.BOOLEAN);
130 _stmt.setObject(5,model.myintro,Types.VARCHAR);
131 _stmt.setObject(6,model.price,Types.FLOAT);
132 _stmt.registerOutParameter(7,Types.INTEGER,-1);
133 _stmt.registerOutParameter(8, Types.BOOLEAN,1);
134 _stmt.registerOutParameter(9, Types.VARCHAR,200);
135 _stmt.execute();
136
137
138 /* */
139 t3id=_stmt.getInt(7);
140 status=_stmt.getBoolean(8);
141 message=_stmt.getString(9);
142
143
144 __ores.id= t3id;
145 __ores.status=status;
146 __ores.message=message;
147 /* */
148
149 _stmt.close();
150 __myconn.close();}
151 catch (Exception __e){
152 __e.printStackTrace();
153 __ores.message=__e.toString();
154 }
155 return __ores;
156 }
157
158
159 /**
160 * ：test3_Update
161 * ：
162 * t3id【 】
163 t3name
164 t_birthday
165 myage
166 isadmin
167 myintro
168 price
169 *
170 *@return
171 */
172 public OperationResult Update(test3Model model){
173 OperationResult __ores=new OperationResult();
174 /*output */
175 boolean status = false ;
176 String message = "" ;
177 /*output */
178 /* */
179 DataTable res__datatable=new DataTable();
180 try{
181 PooledConnection __myconn=DBPool.getConnection();
182 CallableStatement _stmt=__myconn.prepareCall("{ call test3_Update( ?,?,?,?,?,?,?,?,?)}");
183
184
185 _stmt.setObject(1,model.t3name,Types.VARCHAR);
186 _stmt.setObject(2,new Timestamp(model.t_birthday.getTime()),Types.DATE);
187 _stmt.setObject(3,model.myage,Types.SMALLINT);
188 _stmt.setObject(4,model.isadmin,Types.BOOLEAN);
189 _stmt.setObject(5,model.myintro,Types.VARCHAR);
190 _stmt.setObject(6,model.price,Types.FLOAT);
191 _stmt.setInt(7,model.t3id);
192 _stmt.registerOutParameter(8, Types.BOOLEAN,1);
193 _stmt.registerOutParameter(9, Types.VARCHAR,400);
194 _stmt.execute();
195 /* */
196 status=_stmt.getBoolean(8);
197 message=_stmt.getString(9);
198
199 __ores.status=status;
200 __ores.message=message;
201
202 /* */
203
204 _stmt.close();
205 __myconn.close();}
206 catch (Exception __e){
207 __e.printStackTrace();
208 }
209 return __ores;
210 }
211
212 /**
213 * ：test3_DeleteList
214 * ：
215 *@param ids 【 ：ids :nvarchar java ：String :400 】
216 *
217 *@return
218 */
219 public OperationResult DeleteList( String ids){
220 /*output */
221 OperationResult __ores=new OperationResult();
222 boolean status = false ;
223 String message = "" ;
224 /*output */
225 /* */
226 DataTable res__datatable=new DataTable();
227 try{
228 PooledConnection __myconn=DBPool.getConnection();
229 CallableStatement _stmt=__myconn.prepareCall("{ call test3_DeleteList( ?, ?, ?)}");
230
231 _stmt.setString(1,ids);
232
233 _stmt.registerOutParameter(2, Types.BOOLEAN,1);
234
235 _stmt.registerOutParameter(3, Types.VARCHAR,400);
236 _stmt.execute();
237 /* */
238 status=_stmt.getBoolean(2);
239 message=_stmt.getString(3);
240
241 __ores.status=status;
242 __ores.message=message;
243 /* */
244
245 _stmt.close();
246 __myconn.close();}
247 catch (Exception __e){
248 __e.printStackTrace();
249 }
250 return __ores;
251 }
252
253 /**
254 * ：test3_GetRecord
255 * ：
256 *@param t3id 【 ：id :int java ：int : 】
257 *
258 *@return DataTable 。
259 */
260
261
262
263 public test3Model GetRecord( int t3id ){
264 /* */
265 DataTable res__datatable=new DataTable();
266 test3Model model=new test3Model();
267 try{
268 PooledConnection __myconn=DBPool.getConnection();
269 CallableStatement _stmt=__myconn.prepareCall("{ call test3_GetRecord( ?)}");
270
271 _stmt.setInt(1,t3id);
272 ResultSet __rslist =_stmt.executeQuery();
273 res__datatable=DataTableHelper.rs2datatable(__rslist);
274 model=tryParseModel(res__datatable.get(0));
275
276 /* */
277 __rslist.close();
278 _stmt.close();
279 __myconn.close();}
280 catch (Exception __e){
281 __e.printStackTrace();
282 }
283
284 return model;
285 }
286
287 /**
288 * ：test3_Top_Condition
289 * ：
290 *@param topN 【 ：topN :int java ：int : 】
291 *@param columns 【 ：columns :nvarchar java ：String :800 】
292 *@param condition 【 ：condition :nvarchar java ：String :800 】
293 *@param orderColumn 【 ：orderColumn :nvarchar java ：String :800 】
294 *
295 *@return DataTable 。
296 */
297 public DataTable Top_Condition( int topN, String columns, String condition, String orderColumn ){
298 /* */
299 DataTable res__datatable=new DataTable();
300 try{
301 PooledConnection __myconn=DBPool.getConnection();
302 CallableStatement _stmt=__myconn.prepareCall("{ call test3_Top_Condition( ?, ?, ?, ?)}");
303
304 _stmt.setInt(1,topN);
305
306 _stmt.setString(2,columns);
307
308 _stmt.setString(3,condition);
309
310 _stmt.setString(4,orderColumn);
311 ResultSet __rslist =_stmt.executeQuery();
312 res__datatable=DataTableHelper.rs2datatable(__rslist);
313
314 /* */
315 __rslist.close();
316 _stmt.close();
317 __myconn.close();}
318 catch (Exception __e){
319 __e.printStackTrace();
320 }
321
322 return res__datatable;
323 }
324
325 public test3Model tryParseModel(DataRow drow){
326 test3Model model=new test3Model();
327 if(drow==null){
328 return model;
329 }
330
331 /*
332 return "boolean";
333 return "Date";
334 return "double";
335 return "float";
336 return "int";
337 return "long";
338 return "String";
339 return "Object";
340 */
341
342 /* */
343 model.t3id = drow.get("t3id").toInt();
344 model.t3name = drow.get("t3name").toString();
345 model.t_birthday = drow.get("t_birthday").toDate();
346 model.myage = drow.get("myage").toInt();
347 model.isadmin = drow.get("isadmin").toBoolean();
348 model.myintro = drow.get("myintro").toString();
349 model.price = drow.get("price").toFloat();
350 return model;
351 }
352
353 public List<test3Model> tryParseList(List<DataRow> dataList){
354 List<test3Model> modellist=new ArrayList<test3Model>();
355 if(dataList==null){
356 return modellist;
357 }
358
359 for(DataRow drow :dataList){
360 modellist.add(tryParseModel(drow));
361 }
362
363 return modellist;
364 }
365 }java
http://blog.csdn.net/cdnight/article/details/18078751
http://blog.csdn.net/cdnight/article/details/18001807
http://www.cnblogs.com/stephen-liu74/category/343171.html