postgreSQL的crud操作详解

postgreSQL学习笔记

欢迎大家指出问题!

版本从6.0开始支持SQL解释器

安装一路下一步。

1.创建数据库

​ //命令行操作
​ createdb database_name;
​ CREATE DATABASE database_name;
psql -l //展示所有数据库
psql database_name //进入数据库

2.删除数据库

​ dropdb database;
​ DROP DATABASE database_name;

3.创建表

​ create table 表名 (title varchar(255), content text);
//修改表名
alter table posts rename to 表名;

//查看表信息(结构)
\d 表名
//执行这个sql文件
\i a.sql
//切换数据显示方式(横向和纵向显示)
\x

create table posts (
id serial primary key,
title varchar(255) not null,
content text check(length(content) > 3),
is_draft boolean default FALSE,
created_date timestamp default ‘now’);

CREATE TABLE public.students
(
id integer NOT NULL,
name character(128),
subjects character(1),
CONSTRAINT student2_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.students
OWNER TO postgres;
COMMENT ON TABLE public.students
IS ‘这是一个学生信息表’;

案例表:

4.删除表

​ DROP TABLE students;

5.数据类型

数值型:

integer real serial(序列型,一般用于自增字段)

文字型: ​

char varchar text

布尔型: ​

boolean

日期型: ​

date time timestamp

特色类型: ​

Array inet(网口类型) JSON XML

6.添加表约束

unique:所在数据中值必须唯一

check:字段设置条件,可以指定函数check(length(content) > 3)必须超过3个字符

default:字段默认值

7.INSERT语句

insert into 表名(cloum1,cloum2) value(a,b);

8.数据抽出选项:

order by asc升序 desc降序

limit 限制返回数据条数

offset 偏移量(从哪条数据开始)

//分页查询limit和offset结合使用。
select * from users order by score desc limit 3;
select * from users order by score desc limit 3 offset 3;

9.统计抽出数据

distinct 去重

group by/having(在group by之后进行再次筛选) 分组

select team,max(score) from users group by team having max(score)>25 order by max(score);

10.方便的函数

length concat(连接两个字符串) alias(别名) substring(截取字符串) random

select player,length(player) from users;
select player,concat(player,”/”,team) from users;
select player,concat(player,”/”,team) as “球员信息” from users;
select concat(‘我’,substring(team,1,1)) as “球队首文字” from users;
//随机抽奖
select player from users order by random() limit 1;

11.更新和删除

update [table] set [field=newvalue,…] where …
delete from [table] where …

eg:

update users set score = score + 100 where team IN (“勇士”,”骑士”);

12.变更表结构

//alter table [tablename] …
//给表添加一条fullname字段
alter table users add fullname varchar(255);

//给哪个表的字段建索引(索引提高查询效率,但是增删效果不好)
create index 索引名 on users(字段名);
//删除索引
drop index 索引名;

//修改列名
alter table users rename player to nba_player;
//修改字段的的长度
alter table users alter nba_player type varchar(128);

13.操作多个表

//两表的关联查询
select users.player,twitters.content from users,twitters where users.id = twitters.user_id;
//别名
select u.player,t.content from users as u,twitters as t where u.id = t.user_id;

14.使用视图

​ 视图:视图就是一个select语句,把业务系统中常用的select语句简化成一个类似于表的对象,便于简单读取和开发。 (对于经常使用的select语句建立视图便于编码和管理)

//创建一个视图(通过 \dv 查看视图描述)
create view curry_twitters as select u.player,t.content from users as u,twitters as t where u.id = t.user_id;
//要进行查询时
select * from curry_twitters;
//删除视图
drop view curry_twitters;

15.使用事务

数据库事务:是指作为单个逻辑工作单元执行的一系列操作,要么一起成功,要么一起失败。必须满足ACID(原子性、 一致性、隔离性、持久性)

PostgreSQL数据库事务使用

begin 开启事务

commit 提交

rollback 回滚

eg:
begin;
update users set score = 50 where player = ‘库里’;
update users set score = 60 where player = ‘哈登’;
commit; //如果不想跟新可以 rollback;

补充:postgresql一般crud存储过程参考

这里是一份经过再三调试测试而成功的postgres数据库单表crud存储过程,请注意,对于多结果的返回方式,请查看getPageByCondition的书写方式,用的是refcursor,返回一个cursor,同时可以返回其他out,inout参数,但是refcursor必须在事务中调用,所以java端的调用过程需要注意,好吧,我同时放出一份dal样板,大家可以直接copy来用。

/******************************************************************
* 表名:test3
* Made by 码农下的天桥
******************************************************************/
–use MYDB;–你可以指定自己的数据库
/******************************************************************
****************************各种常用查询***************************
******************************************************************/
————————————
–用途:复杂形式的查询语句,用于查询分页数据。
–这个是泛用型的,假如你要根据用户输入去查询,那么最好不要用这个了,
–以免出现sql注入。
–参数说明:
—_offset int 需要取的记录的开始位置
—_limit int 需要获取记录的总条数,针对分页而言,就是分页的pagesize。
—_columns varchar(800) 需要获取的字段
—_where varchar(800) 需要过滤的条件譬如: where id<10 可以带where,不过建议不要带。
—_orderby varchar(800) 需要进行排序的提交,譬如:order by id
—_totalCount int 返回总共记录条数。
—_totalPages int 返回总共页数。
————————————
create or replace function test3_getListByCondition(
INOUT pageindex INT,
INOUT pagesize INT,
IN _columns VARCHAR(800),
IN _where VARCHAR(800),
IN _orderby VARCHAR(800),
out _totalCount INT,
out _totalPages INT)
returns SETOF record
AS
$$
DECLARE condition_columns VARCHAR(800);
DECLARE condition_where varchar(800);
DECLARE condition_orderby VARCHAR(800);
DECLARE _dymatic_sql VARCHAR(1600);
DECLARE _beginNO INT;
DECLARE _dynamic_getCount varchar(1600);
DECLARE _theOffset INT;
DECLARE _tmpInt1 INT;
BEGIN
condition_where:=ltrim(rtrim(COALESCE(_where,”)));
condition_orderby:=ltrim(rtrim(COALESCE(_orderby,’order by t3id’)));
condition_columns:=ltrim(rtrim(COALESCE(_columns,’*’)));
–分析传过来的参数,构造动态sql语句。
IF “character_length”(condition_where)>0 THEN
IF strpos(condition_where, ‘where ‘)!=1 THEN
condition_where:=’where ‘ || condition_where;
END IF;
END IF;
–order by 语句构造
IF “character_length”(condition_orderby)>0 THEN
IF strpos(condition_orderby, ‘order ‘)!=1 THEN
condition_orderby:=’order by ‘||condition_orderby;
END IF;
END IF;

–判断pageindex是否合法及pagesize是否合法
IF pageindex<1 THEN
pageindex:=1;
END IF;
IF pagesize<1 THEN
pagesize:=20;
END IF;

_dynamic_getCount:=’select count(*) from test3 ‘||condition_where|| ‘ ‘ ;
EXECUTE _dynamic_getCount INTO _totalCount;
IF _totalCount<1 THEN
pageindex:=1;
RETURN;
END IF;
–计算总共页数
_tmpInt1:=_totalCount%pagesize;
IF _tmpInt1=0 THEN
_totalPages:=_totalCount / pagesize;
ELSE
_totalPages:=(_totalCount-_tmpInt1)/pagesize+1;
END IF;
IF _totalPages < pageindex then
pageindex:=_totalPages;
END IF;
_theOffset:=(pageindex-1) * pagesize+1;
_dymatic_sql:=’select ‘||condition_columns||’ from test3 ‘||condition_where||’ ‘||condition_orderby||’ limit ‘||pagesize||’ ‘|| ‘ offset ‘||_theOffset||’ ‘;
–raise info ‘动态构造语句为:%’,_dymatic_sql;
return query EXECUTE _dymatic_sql;
END;
$$ language plpgsql VOLATILE;
————————————
–用途:复杂形式的查询语句,用于查询多条记录数据。
–这个是泛用型的,假如你要根据用户输入去查询,那么最好不要用这个了,
–以免出现sql注入。
–参数说明:
—_offset int 需要取的记录的开始位置
—_limit int 需要获取记录的总条数,针对分页而言,就是分页的pagesize。
—_columns varchar(800) 需要获取的字段
—_where varchar(800) 需要过滤的条件譬如: where id<10 可以带where,不过建议不要带。
—_orderby varchar(800) 需要进行排序的提交,譬如:order by id
—_totalCount int 返回总共记录条数。
————————————
create or replace function test3_getPageByCondition(
INOUT pageindex INT,
INOUT pagesize INT,
IN _columns VARCHAR(800),
IN _where VARCHAR(800),
IN _orderby VARCHAR(800),
out _totalCount INT,
out _totalPages INT,
out _refcursor refcursor
)
returns SETOF record
AS
$$
DECLARE condition_columns VARCHAR(800);
DECLARE condition_where varchar(800);
DECLARE condition_orderby VARCHAR(800);
DECLARE _dymatic_sql VARCHAR(1600);
DECLARE _beginNO INT;
DECLARE _dynamic_getCount varchar(1600);
DECLARE _theOffset INT;
DECLARE _tmpInt1 INT;
BEGIN
condition_where:=ltrim(rtrim(COALESCE(_where,”)));
condition_orderby:=ltrim(rtrim(COALESCE(_orderby,’order by t3id’)));
condition_columns:=ltrim(rtrim(COALESCE(_columns,’*’)));
–分析传过来的参数,构造动态sql语句。
IF “character_length”(condition_where)>0 THEN
IF strpos(condition_where, ‘where ‘)!=1 THEN
condition_where:=’where ‘ || condition_where;
END IF;
END IF;
–order by 语句构造
IF “character_length”(condition_orderby)>0 THEN
IF strpos(condition_orderby, ‘order ‘)!=1 THEN
condition_orderby:=’order by ‘||condition_orderby;
END IF;
END IF;
–判断pageindex是否合法及pagesize是否合法
IF pageindex<1 THEN
pageindex:=1;
END IF;
IF pagesize<1 THEN
pagesize:=20;
END IF;
_dynamic_getCount:=’select count(*) from test3 ‘||condition_where|| ‘ ‘ ;
EXECUTE _dynamic_getCount INTO _totalCount;
IF _totalCount<1 THEN
pageindex:=1;
RETURN;
END IF;
–计算总共页数
_tmpInt1:=_totalCount%pagesize;
IF _tmpInt1=0 THEN
_totalPages:=_totalCount / pagesize;
ELSE
_totalPages:=(_totalCount-_tmpInt1)/pagesize+1;
END IF;
IF _totalPages < pageindex then
pageindex:=_totalPages;
END IF;
_theOffset:=(pageindex-1) * pagesize+1;
_dymatic_sql:=’select ‘||condition_columns||’ from test3 ‘||condition_where||’ ‘||condition_orderby||’ limit ‘||pagesize||’ ‘|| ‘ offset ‘||_theOffset||’ ‘;
–raise info ‘动态构造语句为:%’,_dymatic_sql;
open _refcursor for EXECUTE _dymatic_sql;
RETURN NEXT;
END;
$$ language plpgsql VOLATILE;
————————————
–用途:获取其中一条记录
————————————
create or replace function test3_getRecord(in _id integer)
returns SETOF test3
AS
$$
BEGIN
return query select * from test3 where t3id=_id LIMIT 1 OFFSET 0;
END;
$$ LANGUAGE plpgsql VOLATILE;
————————————
–用途:复杂形式的查询语句,用于查询前面第几条记录,这个就相当好了
–这个是泛用型的,假如你要根据用户输入去查询,那么最好不要用这个了,
–以免出现sql注入。
–参数说明:
—_topN int 需要取的topN条记录。
—_columns varchar(800) 需要获取的字段
—_where varchar(800) 需要过滤的条件譬如: where id<10 可以带where,不过建议不要带。
—_orderby varchar(800) 需要进行排序的提交,譬如:order by id
————————————
create or replace function test3_getTopNbyCondition(IN _topN int,IN _columns VARCHAR(800),IN _where VARCHAR(800),IN _orderby VARCHAR(800))
returns SETOF test3
AS
$$
DECLARE condition_columns VARCHAR(800);
DECLARE condition_where varchar(800);
DECLARE condition_orderby VARCHAR(800);
DECLARE _dymatic_sql VARCHAR(1600);
BEGIN
condition_where:=ltrim(rtrim(COALESCE(_where,”)));
condition_orderby:=ltrim(rtrim(COALESCE(_orderby,’order by t3id’)));
condition_columns:=ltrim(rtrim(COALESCE(_columns,’*’)));
–分析传过来的参数,构造动态sql语句。
IF “character_length”(condition_where)>0 THEN
IF strpos(condition_where, ‘where ‘)!=1 THEN
condition_where:=’where ‘ || condition_where;
END IF;
END IF;
–order by 语句构造
IF “character_length”(condition_orderby)>0 THEN
IF strpos(condition_orderby, ‘order ‘)!=1 THEN
condition_orderby:=’order by ‘||condition_orderby;
END IF;
END IF;
_dymatic_sql:=’select ‘||condition_columns||’ from test2 ‘||condition_where||’ ‘||condition_orderby||’ limit ‘||CAST(_topN as VARCHAR)|| ‘ offset 0 ‘;
–raise info ‘动态构造语句为:%’,_dymatic_sql;
return query EXECUTE _dymatic_sql;
END;
$$ language plpgsql VOLATILE;
/******************************************************************
*****************************记录删除******************************
******************************************************************/
————————————
–用途:删除多条记录
————————————
create or replace function test3_DeleteList(in ids VARCHAR(800),out status boolean,out msg VARCHAR(200))
returns record
AS
$$
DECLARE _arr_ids int[];
DECLARE _str_ids “text”;
DECLARE _str_sql VARCHAR(1600);
DECLARE _effects int;
BEGIN
IF “character_length”(ids)<1 THEN
status:=false;
msg:=’没有指定需要删除的数据!’;
return;
end if;
_arr_ids:=tools_str2intarray(ids, ‘,’);
_str_ids:=tools_stringify(_arr_ids,’,’);
–pkey为主键,自增的整数, <@ 表示判断pkey是不是在数组里面。是不是很方便?
/*动态构造执行*/
–_str_sql:=’DELETE FROM test3 where t3id in (‘||_str_ids||’) ;’;
–EXECUTE _str_sql;
/*直接执行*/
delete from test3 where t3id =ANY( _arr_ids);
GET DIAGNOSTICS _effects = ROW_COUNT;
IF _effects>0 THEN
status:=true;
msg:=’成功删除’||_effects||’条记录!’;
ELSE
status:=false;
msg:=’没有删除任何记录!’;
end if;

END
$$ LANGUAGE plpgsql VOLATILE;

/******************************************************************
****************************添加及编辑*****************************
******************************************************************/

————————————
–用途:增加一条记录
————————————

create or replace function test3_Insert(
in __t3name varchar(400) ,
in __t_birthday date ,
in __myage smallint ,
in __isadmin boolean ,
in __myintro text ,
in __price float ,
out __t3id integer,
out _status boolean,
out _msg varchar(200))
returns record AS $$
BEGIN

Insert into test3
(
“t3name”,”t_birthday”,”myage”,”isadmin”,”myintro”,”price”
)
values(
__t3name,__t_birthday,__myage,__isadmin,__myintro,__price
);
/*判断添加记录是否成功。*/
if FOUND then
_status:=true;
_msg:=’成功添加记录.’;
__t3id:=currval(pg_get_serial_sequence(‘test3’, ‘t3id’));
else
_status:=false;
_msg:=’无法添加记录!’;
end if;
end;
$$ LANGUAGE plpgsql VOLATILE;

————————————
–用途:修改一条记录
————————————
create or replace function test3_Update(
in __t3name varchar(400) ,
in __t_birthday date ,
in __myage smallint ,
in __isadmin boolean ,
in __myintro text ,
in __price float ,
in __t3id integer,
out _status boolean,
out _msg varchar(200))
returns record AS $$
BEGIN

update test3 set
“t3name”=__t3name,”t_birthday”=__t_birthday,”myage”=__myage,”isadmin”=__isadmin,”myintro”=__myintro,”price”=__price where t3id=__t3id;
/*判断保存记录是否成功。*/
if FOUND then
_status:=true;
_msg:=’成功保存记录.’;
else
_status:=false;
_msg:=’无法保存记录!’;
end if;
end;
$$ LANGUAGE plpgsql VOLATILE;

对应dal调用文件:

package EasisWeb.DAL;

import EasisWeb.config.DBPool;
import Easis.Common.StringUtil;
import Easis.util.DataRow;
import Easis.util.DataTable;
import Easis.util.DataTableHelper;
import java.util.Date;
import Easis.DBUtility.PooledConnection;
import java.sql.*;
import java.util.List;
import java.util.ArrayList;
import Easis.util.OperationResult;
import Easis.util.PagerResult;
import EasisWeb.Model.test3Model;
/**
* 这是利用CodeGen工具生成的自动访问数据库的一个模板,作者为“码农下的天桥”
*生成的类名称:
* @author 码农下的天桥
* @version 1.00
*/
public class test3DAL {

/*表格各种column*/
public static final String col_t3id=”test3″;
public static final String col_t3name=”test3″;
public static final String col_t_birthday=”test3″;
public static final String col_myage=”test3″;
public static final String col_isadmin=”test3″;
public static final String col_myintro=”test3″;
public static final String col_price=”test3″;
public static final String PKColumn=”t3id”;

/**
*存储过程名称:test3_ListByCondition
*存储过程参数:
*@param pageindex
*@param pagesize
*@param columns 需要获取的字段
*@param condition where条件语句
*@param orderColumn order by排序语句
*
*@return 分页对象
*/

public PagerResult getPageListByCondition(int pageindex,int pagesize, String columns, String condition, String orderColumn){
PagerResult pres=new PagerResult();
//output参数定义
int _total = 0 ;
int _pagesize = 0 ;
int _pageindex = 0 ;
int _totalpages = 0 ;
//output参数定义结束
//调用存储过程
DataTable res__datatable=new DataTable();
try{
PooledConnection __myconn=DBPool.getConnection();
__myconn.setAutoCommit(false); // return refcursor must within a transaction
CallableStatement _stmt=__myconn.prepareCall(“{ call test3_getPageByCondition( ?, ?, ?, ?, ?, ?, ?, ?)}”);
_stmt.setInt(1,pageindex);
_stmt.setInt(2,pagesize);
_stmt.registerOutParameter(1,Types.INTEGER);
_stmt.registerOutParameter(2,Types.INTEGER);
_stmt.setString(3,columns);
_stmt.setString(4,condition);
_stmt.setString(5,orderColumn);
_stmt.registerOutParameter(6, Types.INTEGER);
_stmt.registerOutParameter(7, Types.INTEGER);
_stmt.registerOutParameter(8,Types.OTHER);
_stmt.execute();
ResultSet __rslist=(ResultSet)_stmt.getObject(8);
res__datatable=DataTableHelper.rs2datatable(__rslist);
//取回参数
_total=_stmt.getInt(6);
pres.totalrecords=_total;
_pageindex=_stmt.getInt(1);
pres.totalrecords=_total;
_pagesize=_stmt.getInt(2);
pres.pageindex=_pageindex;
pres.pagesize=_pagesize;
_totalpages=_stmt.getInt(7);
pres.totalpages=_totalpages;
pres.datasource=res__datatable;
//–提交并还原
__myconn.commit();
__myconn.setAutoCommit(true); //返回游标必须在一个事务中,提交完以后将autocommit还原。
//释放资源
__rslist.close();
_stmt.close();
__myconn.close();}
catch (Exception __e){
System.out.println(“在运行[test3DAL]的List_Condition时候出现错误。”);
__e.printStackTrace();
}
return pres;
}

/**
*存储过程名称:test3_Insert
*存储过程参数:
*param t3id 【主键】
t3name
t_birthday
myage
isadmin
myintro
price
*
*@return
*/
public OperationResult Insert(test3Model model){
OperationResult __ores=new OperationResult();
/*output参数定义*/
int t3id = 0 ;
DataRow returnInfo=new DataRow();
boolean status = false ;
String message = “” ;
/*output参数定义结束*/
/*调用存储过程*/

try{
PooledConnection __myconn=DBPool.getConnection();
CallableStatement _stmt=__myconn.prepareCall(“{call test3_Insert(?,?,?,?,?,?,?,?,?)}”);

_stmt.setObject(1,model.t3name,Types.VARCHAR);
_stmt.setObject(2,new Timestamp(model.t_birthday.getTime()),Types.DATE);
_stmt.setObject(3,model.myage,Types.SMALLINT);
_stmt.setObject(4,model.isadmin,Types.BOOLEAN);
_stmt.setObject(5,model.myintro,Types.VARCHAR);
_stmt.setObject(6,model.price,Types.FLOAT);
_stmt.registerOutParameter(7,Types.INTEGER,-1);
_stmt.registerOutParameter(8, Types.BOOLEAN,1);
_stmt.registerOutParameter(9, Types.VARCHAR,200);
_stmt.execute();

/*取回参数*/
t3id=_stmt.getInt(7);
status=_stmt.getBoolean(8);
message=_stmt.getString(9);

__ores.id= t3id;
__ores.status=status;
__ores.message=message;
/*释放资源*/

_stmt.close();
__myconn.close();}
catch (Exception __e){
__e.printStackTrace();
__ores.message=__e.toString();
}
return __ores;
}

/**
*存储过程名称:test3_Update
*存储过程参数:
* t3id【主键】
t3name
t_birthday
myage
isadmin
myintro
price
*
*@return
*/
public OperationResult Update(test3Model model){
OperationResult __ores=new OperationResult();
/*output参数定义*/
boolean status = false ;
String message = “” ;
/*output参数定义结束*/
/*调用存储过程*/
DataTable res__datatable=new DataTable();
try{
PooledConnection __myconn=DBPool.getConnection();
CallableStatement _stmt=__myconn.prepareCall(“{ call test3_Update( ?,?,?,?,?,?,?,?,?)}”);

_stmt.setObject(1,model.t3name,Types.VARCHAR);
_stmt.setObject(2,new Timestamp(model.t_birthday.getTime()),Types.DATE);
_stmt.setObject(3,model.myage,Types.SMALLINT);
_stmt.setObject(4,model.isadmin,Types.BOOLEAN);
_stmt.setObject(5,model.myintro,Types.VARCHAR);
_stmt.setObject(6,model.price,Types.FLOAT);
_stmt.setInt(7,model.t3id);
_stmt.registerOutParameter(8, Types.BOOLEAN,1);
_stmt.registerOutParameter(9, Types.VARCHAR,400);
_stmt.execute();
/*取回参数*/
status=_stmt.getBoolean(8);
message=_stmt.getString(9);

__ores.status=status;
__ores.message=message;

/*释放资源*/

_stmt.close();
__myconn.close();}
catch (Exception __e){
__e.printStackTrace();
}
return __ores;
}

/**
*存储过程名称:test3_DeleteList
*存储过程参数:
*@param ids 【参数名称:ids 参数类型:nvarchar 对应java类型:String 长度:400 】
*
*@return
*/
public OperationResult DeleteList( String ids){
/*output参数定义*/
OperationResult __ores=new OperationResult();
boolean status = false ;
String message = “” ;
/*output参数定义结束*/
/*调用存储过程*/
DataTable res__datatable=new DataTable();
try{
PooledConnection __myconn=DBPool.getConnection();
CallableStatement _stmt=__myconn.prepareCall(“{ call test3_DeleteList( ?, ?, ?)}”);

_stmt.setString(1,ids);
_stmt.registerOutParameter(2, Types.BOOLEAN,1);
_stmt.registerOutParameter(3, Types.VARCHAR,400);
_stmt.execute();
/*取回参数*/
status=_stmt.getBoolean(2);
message=_stmt.getString(3);

__ores.status=status;
__ores.message=message;
/*释放资源*/

_stmt.close();
__myconn.close();}
catch (Exception __e){
__e.printStackTrace();
}
return __ores;
}

/**
*存储过程名称:test3_GetRecord
*存储过程参数:
*@param t3id 【参数名称:id 参数类型:int 对应java类型:int 长度:非字符类型 】
*
*@return DataTable对象。
*/

public test3Model GetRecord( int t3id ){
/*调用存储过程*/
DataTable res__datatable=new DataTable();
test3Model model=new test3Model();
try{
PooledConnection __myconn=DBPool.getConnection();
CallableStatement _stmt=__myconn.prepareCall(“{ call test3_GetRecord( ?)}”);

_stmt.setInt(1,t3id);
ResultSet __rslist =_stmt.executeQuery();
res__datatable=DataTableHelper.rs2datatable(__rslist);
model=tryParseModel(res__datatable.get(0));

/*释放资源*/
__rslist.close();
_stmt.close();
__myconn.close();}
catch (Exception __e){
__e.printStackTrace();
}

return model;
}

/**
*存储过程名称:test3_Top_Condition
*存储过程参数:
*@param topN 【参数名称:topN 参数类型:int 对应java类型:int 长度:非字符类型 】
*@param columns 【参数名称:columns 参数类型:nvarchar 对应java类型:String 长度:800 】
*@param condition 【参数名称:condition 参数类型:nvarchar 对应java类型:String 长度:800 】
*@param orderColumn 【参数名称:orderColumn 参数类型:nvarchar 对应java类型:String 长度:800 】
*
*@return DataTable对象。
*/
public DataTable Top_Condition( int topN, String columns, String condition, String orderColumn ){
/*调用存储过程*/
DataTable res__datatable=new DataTable();
try{
PooledConnection __myconn=DBPool.getConnection();
CallableStatement _stmt=__myconn.prepareCall(“{ call test3_Top_Condition( ?, ?, ?, ?)}”);

_stmt.setInt(1,topN);

_stmt.setString(2,columns);

_stmt.setString(3,condition);

_stmt.setString(4,orderColumn);
ResultSet __rslist =_stmt.executeQuery();
res__datatable=DataTableHelper.rs2datatable(__rslist);

/*释放资源*/
__rslist.close();
_stmt.close();
__myconn.close();}
catch (Exception __e){
__e.printStackTrace();
}

return res__datatable;
}

public test3Model tryParseModel(DataRow drow){
test3Model model=new test3Model();
if(drow==null){
return model;
}

/*
return “boolean”;
return “Date”;
return “double”;
return “float”;
return “int”;
return “long”;
return “String”;
return “Object”;
*/

/*尝试赋值*/
model.t3id = drow.get(“t3id”).toInt();
model.t3name = drow.get(“t3name”).toString();
model.t_birthday = drow.get(“t_birthday”).toDate();
model.myage = drow.get(“myage”).toInt();
model.isadmin = drow.get(“isadmin”).toBoolean();
model.myintro = drow.get(“myintro”).toString();
model.price = drow.get(“price”).toFloat();
return model;
}

public List<test3Model> tryParseList(List<DataRow> dataList){
List<test3Model> modellist=new ArrayList<test3Model>();
if(dataList==null){
return modellist;
}

for(DataRow drow :dataList){
modellist.add(tryParseModel(drow));
}

return modellist;
}
}

这只是一份模板而已。

以上为个人经验,希望能给大家一个参考,也希望大家多多支持。如有错误或未考虑完全的地方,望不吝赐教。


数据运维技术 » postgreSQL的crud操作详解