view plaincopy to clipboardprint?
CREATE TABLE "USER_BASIC_INFO"
( "USER_ID" NUMBER(10,0) NOT NULL ENABLE,
"USER_NAME" VARCHAR2(50 BYTE) NOT NULL ENABLE,
"REAL_NAME" VARCHAR2(50 BYTE),
"PASSWORD" VARCHAR2(32 BYTE) NOT NULL ENABLE,
"EMAIL" VARCHAR2(50 BYTE) NOT NULL ENABLE,
"USER_TYPE" VARCHAR2(50 BYTE),
"USER_STATE" VARCHAR2(50 BYTE),
"LOGIN_COUNT" NUMBER(8,0) DEFAULT 0,
"QUESTION" VARCHAR2(200 BYTE),
"ANSWER" VARCHAR2(200 BYTE),
"ACCOUNT" VARCHAR2(50 BYTE) DEFAULT -1,
"BRANCH_ID" VARCHAR2(50 BYTE) DEFAULT -1,
"CREATE_DATE" DATE DEFAULT sysdate,
"MODIFY_DATE" DATE DEFAULT sysdate,
"LAST_VISIT" DATE DEFAULT sysdate,
"USER_LEVEL" VARCHAR2(50 BYTE),
"EXPERIENCE_TIME" NUMBER(4,0) DEFAULT 0,
"CUSTID" NUMBER(20,0),
CONSTRAINT "PK_USER_BASIC_INFO" PRIMARY KEY ("USER_ID")
)
view plaincopy to clipboardprint?
CREATE TABLE "USER_EXTEND_INFO"
( "USER_ID" NUMBER(10,0) NOT NULL ENABLE,
"SEX" VARCHAR2(50 BYTE),
"BIRTHDAY" DATE,
"RACE" VARCHAR2(50 BYTE),
"PROVINCE" VARCHAR2(50 BYTE),
"CITY" VARCHAR2(50 BYTE),
"CARD_TYPE" VARCHAR2(50 BYTE),
"CARD_ID" VARCHAR2(50 BYTE),
"TELEPHONE" VARCHAR2(50 BYTE),
"MOBILEPHONE" VARCHAR2(50 BYTE),
"FAX" VARCHAR2(50 BYTE),
"ADDRESS" VARCHAR2(100 BYTE),
"POST_CODE" VARCHAR2(50 BYTE),
"DEGREE" VARCHAR2(50 BYTE),
"VOCATION" VARCHAR2(50 BYTE),
"SOURCE" VARCHAR2(100 BYTE),
CONSTRAINT "PK_USER_EXTEND_INFO" PRIMARY KEY ("USER_ID")
)
view plaincopy to clipboardprint?
create or replace PACKAGE Types
AS
TYPE ref_cursor IS REF CURSOR;
END;
view plaincopy to clipboardprint?
create or replace PROCEDURE QUERY_USER_BASIC_INFO
( P_USER_NAME IN USER_BASIC_INFO.USER_NAME % TYPE
, P_REAL_NAME IN USER_BASIC_INFO.REAL_NAME % TYPE
, P_USER_TYPE IN USER_BASIC_INFO.USER_TYPE % TYPE
, P_BRANCH_ID IN USER_BASIC_INFO.BRANCH_ID % TYPE
, P_BEGIN_DATE IN DATE
, P_END_DATE IN DATE
, P_BEGIN IN NUMBER
, P_END IN NUMBER
, P_RESULT OUT NUMBER
, P_CURSOR OUT TYPES.ref_cursor
) AS
v_sql varchar2(1000);
v_sqlCount varchar2(1000);
BEGIN
v_sql:='SELECT * FROM (
SELECT
row_.*,
ROWNUM rownum_
FROM (
SELECT e.*,f.SEX,
f.BIRTHDAY,
f.RACE,
f.PROVINCE,
f.CITY,
f.CARD_TYPE,
f.CARD_ID,
f.TELEPHONE,
f.MOBILEPHONE,
f.FAX,
f.ADDRESS,
f.POST_CODE,
f.DEGREE,
f.VOCATION,
f.SOURCE,g.key_name FROM USER_BASIC_INFO e left join user_extend_info f on e.user_id=f.user_id
left join dictionary g on e.user_type=g.key_code and g.parent_id=11
where 1=1';
IF P_USER_NAME IS NOT NULL THEN
v_sql := v_sql || 'and e.USER_NAME LIKE ' || '''' || P_USER_NAME || '''';
ELSE IF P_REAL_NAME IS NOT NULL THEN
v_sql := v_sql || 'and e.REAL_NAME LIKE ' || '''' || P_REAL_NAME || '''';
ELSE IF P_USER_TYPE IS NOT NULL THEN
v_sql := v_sql || 'and e.USER_TYPE = ' || '''' || P_USER_TYPE || '''';
ELSE IF P_BRANCH_ID IS NOT NULL THEN
v_sql := v_sql || 'and e.BRANCH_ID = ' || '''' || P_BRANCH_ID || '''';
ELSE IF P_BEGIN_DATE IS NOT NULL THEN
v_sql := v_sql || 'and e.CREATE_DATE between ' || P_BEGIN_DATE || 'and ' || P_END_DATE;
END IF;
END IF;
END IF;
END IF;
END IF;
v_sql := v_sql || ') row_ WHERE ROWNUM <= ' || P_END || ') WHERE rownum_ >' || P_BEGIN;
--DBMS_OUTPUT.PUT_LINE(v_sql);
open P_CURSOR for v_sql;
v_sqlCount := 'SELECT count(1) FROM USER_BASIC_INFO e left join user_extend_info f on e.user_id=f.user_id';
IF P_USER_NAME IS NOT NULL THEN
v_sql := v_sql || 'and e.USER_NAME LIKE ' || '''' || P_USER_NAME || '''';
ELSE IF P_REAL_NAME IS NOT NULL THEN
v_sql := v_sql || 'and e.REAL_NAME LIKE ' || '''' || P_REAL_NAME || '''';
ELSE IF P_USER_TYPE IS NOT NULL THEN
v_sql := v_sql || 'and e.USER_TYPE = ' || '''' || P_USER_TYPE || '''';
ELSE IF P_BRANCH_ID IS NOT NULL THEN
v_sql := v_sql || 'and e.BRANCH_ID = ' || '''' || P_BRANCH_ID || '''';
ELSE IF P_BEGIN_DATE IS NOT NULL THEN
v_sql := v_sql || 'and e.CREATE_DATE between ' || P_BEGIN_DATE || 'and ' || P_END_DATE;
END IF;
END IF;
END IF;
END IF;
END IF;
EXECUTE IMMEDIATE v_sqlCount into P_RESULT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
P_RESULT := 9006;
WHEN OTHERS THEN
P_RESULT := 9005;
END QUERY_USER_BASIC_INFO;
view plaincopy to clipboardprint?
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd">
<!-- iBatis SQL Map 文件 -->
<sqlMap namespace="com.cssweb.idm.user.pojo.UserBasicInfo">
<typeAlias alias="userBasicInfo" type="com.cssweb.idm.user.pojo.UserBasicInfo"/>
<resultMap id="detailMap" class="userBasicInfo">
<result property="userId" column="USER_ID" jdbcType="Long"/>
<result property="userName" column="USER_NAME" jdbcType="String" nullValue=""/>
<result property="realName" column="REAL_NAME" jdbcType="String" nullValue=""/>
<result property="password" column="PASSWORD" jdbcType="String" nullValue=""/>
<result property="email" column="EMAIL" jdbcType="String" nullValue=""/>
<result property="userType" column="USER_TYPE" jdbcType="String" nullValue=""/>
<result property="userState" column="USER_STATE" jdbcType="String" nullValue=""/>
<result property="loginCount" column="LOGIN_COUNT" jdbcType="java.lang.Integer" nullValue="0"/>
<result property="question" column="QUESTION" jdbcType="String" nullValue=""/>
<result property="answer" column="ANSWER" jdbcType="String" nullValue=""/>
<result property="account" column="ACCOUNT" jdbcType="java.lang.Integer" nullValue="0"/>
<result property="branchId" column="BRANCH_ID" jdbcType="String" nullValue=""/>
<result property="createDate" column="CREATE_DATE" jdbcType="java.util.Date" nullValue="1900/01/01 00:00:00"/>
<result property="modifyDate" column="MODIFY_DATE" jdbcType="java.util.Date" nullValue="1900/01/01 00:00:00"/>
<result property="lastVisit" column="LAST_VISIT" jdbcType="java.util.Date" nullValue="1900/01/01 00:00:00"/>
<result property="userLevel" column="USER_LEVEL" jdbcType="String" nullValue=""/>
<result property="experienceTime" column="EXPERIENCE_TIME" jdbcType="java.lang.Integer" nullValue="0"/>
<result property="custId" column="CUSTID" jdbcType="Long" nullValue="0"/>
<result property="sex" column="SEX" jdbcType="String" nullValue=""/>
<result property="birthday" column="BIRTHDAY" jdbcType="java.util.Date" nullValue="1900/01/01 00:00:00"/>
<result property="race" column="RACE" jdbcType="String" nullValue=""/>
<result property="province" column="PROVINCE" jdbcType="String" nullValue=""/>
<result property="city" column="CITY" jdbcType="String" nullValue=""/>
<result property="cardType" column="CARD_TYPE" jdbcType="String" nullValue=""/>
<result property="cardId" column="CARD_ID" jdbcType="String" nullValue=""/>
<result property="telephone" column="TELEPHONE" jdbcType="String" nullValue=""/>
<result property="mobilePhone" column="MOBILEPHONE" jdbcType="String" nullValue=""/>
<result property="fax" column="FAX" jdbcType="String" nullValue=""/>
<result property="address" column="ADDRESS" jdbcType="String" nullValue=""/>
<result property="postCode" column="POST_CODE" jdbcType="String" nullValue=""/>
<result property="degree" column="DEGREE" jdbcType="String" nullValue=""/>
<result property="vocation" column="VOCATION" jdbcType="String" nullValue=""/>
<result property="source" column="SOURCE" jdbcType="String" nullValue=""/>
<result property="typeName" column="KEY_NAME" jdbcType="String" nullValue=""/>
</resultMap>
<parameterMap id="procQueryMap" class="java.util.HashMap" >
<parameter property="P_USER_NAME" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="P_REAL_NAME" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="P_USER_TYPE" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="P_BRANCH_ID" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="P_BEGIN_DATE" jdbcType="DATE" javaType="java.util.Date" mode="IN"/>
<parameter property="P_END_DATE" jdbcType="DATE" javaType="java.util.Date" mode="IN"/>
<parameter property="P_BEGIN" jdbcType="INTEGER" javaType="java.lang.Integer" mode="IN"/>
<parameter property="P_END" jdbcType="INTEGER" javaType="java.lang.Integer" mode="IN"/>
<parameter property="P_RESULT" jdbcType="INTEGER" javaType="java.lang.Integer" mode="OUT"/>
<parameter property="P_CURSOR" javaType="java.sql.ResultSet" jdbcType="ORACLECURSOR" mode="OUT"/>
</parameterMap>
<procedure id="queryUserBasicInfoProc" parameterMap="procQueryMap" resultMap="detailMap">
{call QUERY_USER_BASIC_INFO (?,?,?,?,?,?,?,?,?,?)}
</procedure>
</sqlMap>
view plaincopy to clipboardprint?
/**
* <p>this method is used by people get user list.
* @param map
* @return List
* @throws DataAccessException
*/
@SuppressWarnings("unchecked")
public List<UserBasicInfo> getList(Map<String, Object> map)
throws DataAccessException {
return this.getSqlMapClientTemplate().queryForList(statementName + ".queryUserBasicInfoProc", map);
}
分享到:
相关推荐
ibatis调用oracle存储过程分页
ibatis调用oracle存储过程
Ibatis调用Oracle存储过程,返回自定义的类型。
iBatis调用存储过程
ibatis调用oracle的函数,存储过程的方法,从网上收集的,还没有整理。
ibatis调用存储过程使用示例,处理带参数及无参数类型的存储过程,是一个不错的例子。
用ibatis调用存储过程的介绍,使用java语言进行开发
Ibatis中调用其它的SQL语句很容易,那么如何在Ibatis中调用存储过程呢?
IBATIS调用存储过程
ibatis调用存储过程.txt [removed] var share_info = {}; [removed]
ibatis调存储过程返回游标,资料整理
NULL 博文链接:https://jun1986.iteye.com/blog/1290064
NULL 博文链接:https://fengtiejun.iteye.com/blog/1603851
ibatis 读取oracle clob类型
ibatis oracle ibatis-2.3.3.jar ojdbc14.jar ibatis连接oracle所需的jar包
ibatis+oracle简单实例,其中包括ibatis jar包以及oracle驱动包
使用J2EE+Spring+Ibatis+JDBC 调用存储过程,并返回结果集的配置详细。 供学习参考。
spring+ibatis+oracle分页缓存源码
Struts2-spring-ibatis 整合及存储过程分页.