下载地址:http://download.csdn.net/detail/liangrui1988/5760453
下载之后是MyEclipse项目，需要的存存储过程代码：
--存储过程 包头
create or replace package myPack is
type c_cursors is ref cursor;
procedure deUser(v_id long);--删除
procedure getAllUser(myCursor out myPack.c_cursors);--查询
end myPack;
--存储过程 包体
create or replace package body myPack as
/****************删除*******************/
procedure deUser(v_id long) is
e_table exception;
v_sql varchar2(200);
begin
if v_id is null then
raise e_table;
end if;
v_sql:='delete from fuck where id=:1';
execute immediate v_sql using v_id;
exception
when e_table then
dbms_output.put_line('参数不能为空!');
end;
/****************查询*******************/
procedure getAllUser(myCursor out myPack.c_cursors) as
v_sqlS varchar2(200);
begin
v_sqlS:='select * from fuck';
open myCursor for v_sqlS;
end;
end myPack;
------------------------
select * from fuck
dao实现类代码:
package accp.dao.imple;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import javax.sql.DataSource;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.object.MappingSqlQuery;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import acc.util.PageBean;
import accp.bean.Fuck;
import accp.dao.UserDao;
public class UserDaoImple /*extends HibernateDaoSupport*/ implements UserDao {
/**
* sql 原生sql支持
*
*/
private JdbcTemplate jdbcTemplate;
private SimpleJdbcTemplate simpleJdbcTemplate;
private DataSource dataSource;
private SimpleJdbcCall jdbcCall;
//通过注入得得到DataSoruce再强转
@SuppressWarnings("deprecation")
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);//jdbcTemplate 方式查询
this.dataSource= dataSource;//mappingSqlQuery方式
//使用Simple 方式 更好
this.simpleJdbcTemplate=new SimpleJdbcTemplate(dataSource);
jdbcTemplate.setResultsMapCaseInsensitive(true);
//创建simplejdbccall
this.jdbcCall=new SimpleJdbcCall(this.jdbcTemplate)
.withProcedureName("myPack.getAllUser")
.withoutProcedureColumnMetaDataAccess()
.returningResultSet("fucks",
ParameterizedBeanPropertyRowMapper
.newInstance(Fuck.class));
}
/**
* MappingSqlQuery 类 SQL查询
*/
@SuppressWarnings("unchecked")
public Fuck login(Fuck u) {
/* List<Fuck> list=this.getHibernateTemplate().find(
"from Fuck fu where fu.name=? and fu.password=? ",
new Object[]{u.getName(),u.getPassword()});*/
/*Session session=sessionFactory.getCurrentSession();
Query query=session.createQuery("from Fuck fu where fu.name=? and fu.password=? ");
query.setString(0, u.getName());
query.setString(1, u.getPassword());
List<Fuck> list=query.list();*/
//sql
Fuck uu;
String sql="select id,name,password from fuck where name=? and password=?";
uu=this.jdbcTemplate.queryForObject(sql,
new Object[]{u.getName(),u.getPassword()},
new RowMapper(){
//查询并将结果记录为一个简单的数据模型。
public Object mapRow(ResultSet rs, int rowNum)
throws SQLException {
Fuck fuck=new Fuck();
fuck.setId(rs.getLong("id"));
fuck.setName(rs.getString("name"));
fuck.setPassword(rs.getString("password"));
return fuck;
}
});
return uu;
}
public int getCount(Fuck u) {
/*查询一个简单的数字
* String sql="select count(*) from fuck where name=? and password=?";
int i=jdbcTemplate.queryForInt(sql,new Object[]{u.getName(),u.getPassword()});
*/
/*String sql="select count(*) from fuck where name=? and password=?";
int i=jdbcTemplate.queryForInt(sql,new Object[]{u.getName(),u.getPassword()});
*/
return 0;
}
public Fuck getFuck(int ids){
//方式一 使用jdbcTemplate
MappingSql mappingSqlQuery =new MappingSql(dataSource);
Object[] parms = new Object[1];
parms[0] = ids;
//执行sql 传入参数 返回查询结果
List customers = mappingSqlQuery.execute(parms);
if (customers.size() > 0) {
return (Fuck) customers.get(0);
}
else {
return null;
}
/*
* 方式二 使用SimpleJdbcTemplate
*/
/*String sql="select id, name,password FROM fuck WHERE id = ?";
ParameterizedRowMapper<Fuck> prm=new ParameterizedRowMapper<Fuck>(){
public Fuck mapRow(ResultSet rs, int arg1) throws SQLException {
Fuck fuck=new Fuck();
fuck.setId(rs.getLong("id"));
fuck.setName(rs.getNString("name"));
fuck.setPassword(rs.getString("password"));
return fuck;
}
};
@SuppressWarnings("deprecation")
Fuck fus=this.simpleJdbcTemplate.queryForObject(sql, prm, new Object[]{ids});
return fus;*/
}
public void updateF(Fuck u) {
jdbcTemplate.update("update fuck set name=?,password=? where id=?",
new Object[]{u.getName(),u.getPassword(),u.getId()});
}
public void saveF(Fuck u) {
jdbcTemplate.update("insert into fuck values(user_id.nextval,?,?)",
new Object[]{u.getName(),u.getPassword()});
}
/**
* 内部类 继承MappingSqlQuery类
* @author liangrui
*
*/
static class MappingSql extends MappingSqlQuery
{
//构造方法传入DataSource 数据源对象,调用declarParamter 传入需要的SQL值的内型
public MappingSql(DataSource ds){
super(ds, "SELECT id, name,password FROM fuck WHERE id = ?");
super.declareParameter(new SqlParameter("id", Types.INTEGER));
compile();
}
/**
* 这个对象实列后 调用execute()方法 传入Object[]数组参数 它初执行，
* 并反回resultSet 结果集
*/
@Override
protected Object mapRow(ResultSet rs, int id) throws SQLException {
Fuck cust = new Fuck();
cust.setId(Long.parseLong(rs.getObject("id").toString()));
cust.setName(rs.getString("name"));
cust.setPassword(rs.getString("password"));
return cust;
}
}
public List<Fuck> getShowList() {
List<Fuck> f;
f=jdbcTemplate.queryForObject("select * from fuck",
new RowMapper<List<Fuck>>(){
public List<Fuck> mapRow(ResultSet rs, int arg1) throws SQLException {
List<Fuck> listF=new ArrayList<Fuck>();
while(rs.next()){
System.out.println("封装中............");
Fuck f=new Fuck();
f.setId(rs.getLong("id"));
f.setName(rs.getString("name"));
f.setPassword(rs.getString("password"));
listF.add(f);
}
return listF;
}
});
return f;
}
/**
* 存储过程删除用户
*/
public void deleteF(Fuck u) {
jdbcTemplate.update("call mypack.deuser(?)",
new Object[]{Long.valueOf(u.getId())});
}
public Fuck getProcedure() {
return null;
}
//------------存储过程 游标 获取全部用户---------------------------------
public List<Fuck> getProcShowList() {
Map map=jdbcCall.execute(new HashMap<String,Object>(0));
System.out.println("size: "+map.size());
return (List<Fuck>) map.get("fucks");
}
//---------------------------分页处理-------------------------------------------
public List<Fuck> getPageShow(PageBean pb) {
System.out.println("pb.getOrader(): "+pb.getOrader() +" 当前页: "+pb.getCurrentPage());
String sql="select * from (select t.*,rownum r from fuck t " +
"where rownum<=? order by ? )where r>? ";
List<Fuck> lists=null;
lists=jdbcTemplate.queryForObject(sql,
//设置参数
new Object[]{
pb.getCurrentPage()*pb.getPageSize(),//当前页*每页显示的行数=最大数据截止的行数
pb.getOrader(),
(pb.getCurrentPage()-1)*pb.getPageSize()},
//获取数据，并反回对象
new RowMapper<List<Fuck>>(){
public List<Fuck> mapRow(ResultSet rs, int arg1)
throws SQLException {
List<Fuck> list=new ArrayList<Fuck>();
while(rs.next()){
Fuck fuck=new Fuck();
fuck.setId(rs.getLong("id"));
fuck.setName(rs.getString("name"));
fuck.setPassword(rs.getString("password"));
list.add(fuck);
}
return list;
}
});
//pb.setData(lists);
return lists;
}
public int getFuckTotalRow() {
int total=jdbcTemplate.queryForInt("select count(*) from fuck");
return total;
}
}
spring xml
<?xml version="1.0" encoding="UTF-8"?>
<beans
xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:p="http://www.springframework.org/schema/p"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd"
>
<!-- 配制数据源
<bean id="myDataResource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
<property name="url" value="jdbc:oracle:thin:@localhost:1521:ABC"/>
<property name="username" value="tenement"/>
<property name="password" value="rui"/>
<property name="initialSize" value="3"/>
<property name="maxActive" value="500"/>
<property name="maxIdle" value="3"/>
<property name="minIdle" value="1"/>
</bean>
-->
<!-- 配制数据池 -->
<bean id="myDataResource" class="org.springframework.jndi.JndiObjectFactoryBean">
<property name="jndiName" value="java:comp/env/jdbc/SSHnewPool"/>
</bean>
<!-- 配制原生sql查询方式 注入dataSource -->
<bean id="UserDao" class="accp.dao.imple.UserDaoImple">
<property name="dataSource" ref="myDataResource"/>
</bean>
<!-- session cofnig
<bean id="mySession" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<property name="dataSource" ref="myDataResource"/>
<property name="hibernateProperties">
<value>
hibernate.dialect=org.hibernate.dialect.OracleDialect
hibernate.hbm2ddl.auto=update
hibernate.show_sql=true
</value>
</property>
<property name="mappingResources">
<list>
<value>/accp/bean/Fuck.hbm.xml</value>
</list>
</property>
</bean>
-->
<!-- 指定sessionfatory 的事务
<bean id="myThransactions" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
<property name="sessionFactory" ref="mySession"/>
</bean>
-->
<!-- 事务方法 规则
<tx:advice id="txAdvice" transaction-manager="myThransactions">
<tx:attributes> -->
<!-- 这些开头的方法 加上 不要读锁
<tx:method name="get*" read-only="true"/>
<tx:method name="find*" read-only="true"/>
<tx:method name="search*" read-only="true"/>
<tx:method name="query*" read-only="true"/>
-->
<!-- 这些开头的方法 设为REQUIRED 如果存在一个事务 ，则支看当前事务。 如果没有则开启一个新事务
设为supports时 如果存在一个事务 ，则支看当前事务。如果没有则安非事务处理
<tx:method name="add*" propagation="REQUIRED"/>
<tx:method name="del*" propagation="REQUIRED"/>
<tx:method name="update*" propagation="REQUIRED"/>
<tx:method name="do*" propagation="REQUIRED"/>
<tx:method name="*" propagation="REQUIRED" read-only="true"/>
</tx:attributes>
</tx:advice>
-->
<!-- aop 切入点
<aop:config>
<aop:pointcut expression="execution(* accp.dao..*.*(..))" id="myPointcut"/>
<aop:advisor advice-ref="txAdvice" pointcut-ref="myPointcut"/>
</aop:config>
-->
<!--
<bean id="hiber" class="org.springframework.orm.hibernate3.HibernateTemplate">
<property name="sessionFactory" ref="mySession"></property>
</bean>
-->
<!-- 配置事务
<bean id="myTransaction" class="accp.dao.imple.UserDaoImple">
<property name="sessionFactory" ref="mySession"/>
</bean>
-->
<bean id="serverInterface" class="accp.service.imple.ServiceImple">
<property name="userDao" ref="UserDao"/>
</bean>
<bean id="userAction" class="accp.action.UserAction">
<property name="userService" ref="serverInterface"/>
</bean>
</beans>struts xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE struts PUBLIC
"-//Apache Software Foundation//DTD Struts Configuration 2.1//EN"
"http://struts.apache.org/dtds/struts-2.1.dtd">
<struts>
<package name="default1" extends="struts-default, spring-default">
<action name="user_*" class="userAction" method="{1}">
<result name="success">ok.jsp</result>
<result name="TestConut">testConut.jsp</result>
<result name="TestMappingSql">testConut.jsp</result>
<result name="saveOK" type="redirectAction">user_showList.action</result>
<result name="deleteOK" type="redirectAction">user_showList.action</result>
<result name="updateSave">update.jsp</result>
<result name="showListr">userList.jsp</result>
<result name="pageShow">pageUSer.jsp</result>
</action>
</package>
</struts>
3633
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
