程序博客网 > mmd制作软件手机

Spring中JdbcTemplate实现存储过程…

来源：互联网 发布：mmd制作软件手机 编辑：程序博客网 时间：2024/05/29 15:40
package com.lrq.user.dao.impl;

import java.io.Serializable;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import oracle.jdbc.driver.OracleTypes;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import com.lrq.user.constant.ConditionConstants;
import com.lrq.user.dao.UserDao;
import com.lrq.user.domain.User;
import com.lrq.user.exception.DaoException;
import com.lrq.user.util.DBCPUtils;
@Repository
public class UserDaoJdbcImpl implements UserDao {
        @Autowired
  private JdbcTemplate jdbcTemplate;

      public void add(User user) {
                String sql = "{call USERPACKAGE.insertUser(?,?,?,?,?,?,?,?,?,?,?,?)}";
              jdbcTemplate.update(sql, user.getUserID(), user.getUserName(),
                              user.getUserName(), user.getLogonPwd(), user.getSex(),
                              user.getBirthday(), user.getEducation(), user.getTelephone(),
                               user.getInterest(), user.getPath(), user.getFilename(),
                             user.getRemark());
  }

       public void delete(Serializable userID) {
           String sql = "{call USERPACKAGE.deleteUser(?)}";
            jdbcTemplate.update(sql, userID);
   }

       public void update(User user) {
             String sql = "{call USERPACKAGE.updateUser(?,?,?,?,?,?,?,?,?,?,?,?)}";
              jdbcTemplate.update(sql, user.getUserName(), user.getUserName(),
                            user.getLogonPwd(), user.getSex(), user.getBirthday(),
                              user.getEducation(), user.getTelephone(), user.getInterest(),
                               user.getPath(), user.getFilename(), user.getRemark(),
                               user.getUserID());
  }

       @SuppressWarnings("unchecked")
      public User find(final Serializable userID) {
               final String sql = "{call USERPACKAGE.findUserById(?,?)}";
          return jdbcTemplate.execute(new CallableStatementCreator() {

                    public CallableStatement createCallableStatement(Connection conn)
                                   throws SQLException {
                               // 创建存储过程了,得到了连接,怎么创建存储过程; 得告诉他,存储过程中接受和返回的参数类型;
                            CallableStatement callableStatement = conn.prepareCall(sql);
                                callableStatement.setObject(1, userID);
                             callableStatement.registerOutParameter(2, OracleTypes.CURSOR);
                              return callableStatement;
                   }
           }, new CallableStatementCallback<User>() {

                        public User doInCallableStatement(CallableStatement call)
                                   throws SQLException, DataAccessException {
                          call.execute();
                             ResultSet rs = (ResultSet) call.getObject(2);// 结果集是以对象的形式返回的;
                            List<User> users = resultSet2Users(rs);
                               return users != null && users.size() > 0 ? users.get(0) : null;
                      }
           });
 }

       public User findByUserName(final String userName) {
         final String sql = "{call USERPACKAGE.findUserByUserName(?,?)}";
            return jdbcTemplate.execute(new CallableStatementCreator() {

                    public CallableStatement createCallableStatement(Connection conn)
                                   throws SQLException {
                               // 用connection装配成callablestatement,并返回;
                               CallableStatement callableStatement = conn.prepareCall(sql);
                                callableStatement.setString(1, userName);
                           callableStatement.registerOutParameter(2, OracleTypes.CURSOR);
                              return callableStatement;
                   }
           }, new CallableStatementCallback<User>() {

                        public User doInCallableStatement(CallableStatement call)
                                   throws SQLException, DataAccessException {
                          call.execute();// 这个必须要执行;
                            // 上面装配好了的callablement对象过来直接使用就是了;
                              Object object = call.getObject(2);
                          if (!(object instanceof ResultSet)) {
                                       return null;
                                }
                           ResultSet rs = (ResultSet) object;
                          List<User> users = resultSet2Users(rs);
                               return users != null && users.size() > 0 ? users.get(0) : null;
                      }
           });
 }

       public User findByLogonName(final String logonName) {
               final String sql = "{call USERPACKAGE.findUserByLogonName(?,?)}";
           return jdbcTemplate.execute(new CallableStatementCreator() {

                    public CallableStatement createCallableStatement(Connection conn)
                                   throws SQLException {
                               // 给存储过程构建参数列表;
                               CallableStatement callableStatement = conn.prepareCall(sql);
                                callableStatement.setString(1, logonName);
                          callableStatement.registerOutParameter(2, OracleTypes.CURSOR);
                              return callableStatement;
                   }
           }, new CallableStatementCallback<User>() {

                        public User doInCallableStatement(CallableStatement call)
                                   throws SQLException, DataAccessException {
                          call.execute();// 这个必须要执行;
                            // 得到存储过程,从中获取结果集,cursor是通过getObject()返回的;
                            Object object = call.getObject(2);
                          if (!(object instanceof ResultSet))
                                 return null;
                                ResultSet rs = (ResultSet) object;
                          List<User> users = resultSet2Users(rs);
                               return users != null && users.size() > 0 ? users.get(0) : null;
                      }
           });
 }

       // 这个方法最后用上,替换;
 private List<User> resultSet2Users(ResultSet rs) throws SQLException {

            if (rs != null) {
                   List<User> users = new ArrayList<User>();
                       User user = null;
                   while (rs.next()) {
                         user = new User();
                          user.setUserID(rs.getString("userID"));
                             user.setUserName(rs.getString("userName"));
                         user.setLogonName(rs.getString("logonName"));
                               user.setLogonPwd(rs.getString("logonPwd"));
                         user.setSex(rs.getString("sex"));
                           user.setBirthday(rs.getString("birthday"));
                         user.setEducation(rs.getString("education"));
                               user.setTelephone(rs.getString("telephone"));
                               user.setInterest(rs.getString("interest"));
                         user.setPath(rs.getString("path"));
                         user.setFilename(rs.getString("filename"));
                         user.setRemark(rs.getString("remark"));
                             users.add(user);
                    }
                   return users;
               }
           return null;
        }

       public User findByNamePwd(final String logonName, final String logonPwd) {
          final String sql = "{call USERPACKAGE.findUserByNamePwd(?,?,?)}";
           return jdbcTemplate.execute(new CallableStatementCreator() {

                    public CallableStatement createCallableStatement(Connection conn)
                                   throws SQLException {
                               // 用数据库连接对象构建callablestatement并返回;
                                CallableStatement callableStatement = conn.prepareCall(sql);
                                callableStatement.setString(1, logonName);
                          callableStatement.setString(2, logonPwd);
                           callableStatement.registerOutParameter(3, OracleTypes.CURSOR);
                              return callableStatement;
                   }
           }, new CallableStatementCallback<User>() {

                        public User doInCallableStatement(
                                  CallableStatement callableStatement) throws SQLException,
                                   DataAccessException {
                               callableStatement.execute();// 这个必须要执行;
                               Object object = callableStatement.getObject(3);
                             if (!(object instanceof ResultSet)) {
                                       return null;
                                }
                           ResultSet rs = (ResultSet) object;
                          List<User> users = resultSet2Users(rs);
                               return users != null && users.size() > 0 ? users.get(0) : null;
                      }
           });
 }

       public List<User> findAllUsers() {
            final String sql = "{call USERPACKAGE.findAllUsers(?)}";
            return jdbcTemplate.execute(new CallableStatementCreator() {
                        public CallableStatement createCallableStatement(Connection conn)
                                   throws SQLException {
                               CallableStatement callableStatement = conn.prepareCall(sql);
                                callableStatement.registerOutParameter(1, OracleTypes.CURSOR);
                              return callableStatement;
                   }
           }, new CallableStatementCallback<List<User>>() {
                        public List<User> doInCallableStatement(
                                      CallableStatement callableStatement) throws SQLException,
                                   DataAccessException {
                               callableStatement.execute();// 这个必须要执行;
                               Object object = callableStatement.getObject(1);
                             if (!(object instanceof ResultSet))
                                 return null;
                                ResultSet rs = (ResultSet) object;
                          return resultSet2Users(rs);
                 }
           });
 }

       
    public List<User> findByCondition(Map<String, String> condition) {
              // 如果参数为null,查询所有,在 递进来的参数进行trim处理;
               try {
                       if (condition == null || condition.size() < 1) {
                             return findAllUsers();
                      }
                   String sql = "select  userID,userName,logonName,logonPwd,sex,birthday,education,telephone,interest,path,filename,remark from S_USER where 1=1";
                     StringBuilder sb = new StringBuilder();
                     ArrayList<Object> arg = new ArrayList<Object>();
                        sb.append(sql);
                     addSql(condition, sb, arg, ConditionConstants.USERNAME);
                    addSql(condition, sb, arg, ConditionConstants.SEX);
                 addSql(condition, sb, arg, ConditionConstants.EDUCATION);
                   addSql(condition, sb, arg, ConditionConstants.FILENAME);
                    sql = sb.toString();
                        Object[] args = arg.toArray();
                      return jdbcTemplate.query(sql,
                                      ParameterizedBeanPropertyRowMapper.newInstance(User.class),
                                 args);
              } catch (DataAccessException e) {
                   e.printStackTrace();
                        throw new DaoException(e);
          }
   }

       private void addSql(Map<String, String> condition, StringBuilder sb,
                  ArrayList<Object> arg, String field) {
                String fieldValue = condition.get(field);
           if (!StringUtils.isBlank(fieldValue)) {
                     sb.append(ConditionConstants.AND);
                  sb.append(field);
                   sb.append(ConditionConstants.EQUAL);
                        arg.add(fieldValue);
                }
   }

       public void updateRoleByUserId(String userId, String rid) {
         throw new UnsupportedOperationException("不支持的操作");
      }

       public List<User> findUsersByRid(String rid) {
                throw new UnsupportedOperationException("不支持的操作");
      }

}


oracle存储过程文件:

create or replace PACKAGE BODY USERPACKAGE AS

  PROCEDURE insertUser(v_userID in VARCHAR2,v_username in VARCHAR2,
                       v_logonName in VARCHAR2,v_logonPwd in VARCHAR2,
                       v_sex in VARCHAR2,v_birthday in VARCHAR2,
                       v_education in VARCHAR2,v_telephone in VARCHAR2,
                       v_interest in VARCHAR2,v_path in VARCHAR2,
                       v_filename in VARCHAR2,v_remark in VARCHAR2) AS
  BEGIN
    insert into s_user(userID, username, logonname, 
                       logonpwd, sex, birthday, 
                       education, telephone, interest, 
                       path, filename, remark) 
                values(v_userID,v_username,v_logonName,v_logonPwd,
                       v_sex ,v_birthday, v_education ,v_telephone ,
                       v_interest ,v_path , v_filename ,v_remark );
  END insertUser;

  PROCEDURE updateUser(v_username in VARCHAR2,
                       v_logonName in VARCHAR2,v_logonPwd in VARCHAR2,
                       v_sex in VARCHAR2,v_birthday in VARCHAR2,
                       v_education in VARCHAR2,v_telephone in VARCHAR2,
                       v_interest in VARCHAR2,v_path in VARCHAR2,
                       v_filename in VARCHAR2,v_remark in VARCHAR2,
                       v_userID in VARCHAR2) AS
  BEGIN
   update s_user set username= v_username, logonname= v_logonname, logonpwd= v_logonpwd,
                     sex= v_sex, birthday= v_birthday, education= v_education,
                     telephone= v_telephone, interest= v_interest, path= v_path,
                     filename= v_filename, remark= v_remark where userid= v_userid;
  END updateUser;

  PROCEDURE deleteUser(v_userID in VARCHAR2) AS
  BEGIN
   delete from s_user where userid= v_userid;
  END deleteUser;

  PROCEDURE findUserById(v_userID in VARCHAR2,v_userList out userCursor) AS
  BEGIN
    open v_userlist for select * from s_user where userid= v_userid;
  END findUserById;

  PROCEDURE findUserByUserName(v_username in VARCHAR2,v_userList out userCursor) AS
  BEGIN
   open v_userlist for select * from s_user where username=v_username;
  END findUserByUserName;

  PROCEDURE findUserByLogonName(v_logonname in VARCHAR2,v_userList out userCursor) AS
  BEGIN
    open v_userlist for SELECT * from s_user where logonname= v_logonname;
  END findUserByLogonName;

  PROCEDURE findUserByNamePwd(v_logonname in VARCHAR2,v_logonPwd in VARCHAR2,v_userList out userCursor) AS
  BEGIN
   open v_userlist for select * from s_user where logonname= v_logonname and logonpwd= v_logonpwd;
  END findUserByNamePwd;

  PROCEDURE findAllUsers(v_userList out userCursor) AS
  BEGIN
   open v_userlist for select * from s_user;
  END findAllUsers;

END USERPACKAGE;


注意点:1:jdbc调用存储过程查询的时候,返回的结果是一个游标对象,我们如何从游标对象中获取我们需要的数据并返回;
          2:存储过程中的实质内容必须由我们进行指定,然后由spring工具类进行调用,得到结果后,有我们事先写好的结果集处理器进行处理;
      最终返回结果,这一过程用的是策略设计模式;将处理过程进行了封装,以匿名内部类的形式做为参数进行传递;
   3:Spring的这一封装策略也符合编程习惯,其实跟jdbc差不了多少,传递参数,构造存储过程对象将处理过程封装成对象,由spring进行调用并处理;
 
阅读(270)| 评论(0)
推荐 转载

历史上的今天

最近读者

热度

在LOFTER的更多文章

评论

package com.lrq.user.dao.impl;

import java.io.Serializable;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import oracle.jdbc.driver.OracleTypes;

import org.apache.commons.lang3.StringUtils;
', blogTag:'', blogUrl:'blog/static/21727620920138303345501',isPublished:1, istop:false, type:0, modifyTime:1384066746737,publishTime:1380484012212,permalink:'blog/static/21727620920138303345501', commentCount:0,mainCommentCount:0, recommendCount:0, bsrk:-100, publisherId:0,recomBlogHome:false, currentRecomBlog:false, attachmentsFileIds:[],vote:{}, groupInfo:{}, friendstatus:'none',followstatus:'unFollow', pubSucc:'', visitorProvince:'',visitorCity:'', visitorNewUser:false, postAddInfo:{}, mset:'000',mcon:'', srk:-100, remindgoodnightblog:false, isBlackVisitor:false,isShowYodaoAd:false,hostIntro:'JAVA软件工程师,有扎实的Java基础,熟悉JavaEE技术,对框架的底层原理熟悉,学习能力强。',hmcon:'0', selfRecomBlogCount:'0', lofter_single:'' }
{ifx.visitorName==visitor.userName} ${x.visitorNickname|escape} {else}${x.visitorNickname|escape} {/if}
{if x.moveFrom=='wap'}
0 0
mmd制作软件手机 mmd制作软件手机
原创粉丝点击
热门IT博客
淘宝定金不退淘宝定金不退
詹姆斯季后赛数据詹姆斯季后赛数据
信捷触摸屏软件
msp430用什么软件编程
淘宝自动登录怎么取消
上海编程培训机构
数据可视化大屏展示
北京数据分析师工资
js获取手机屏幕宽度
上海零境网络 起诉
淘宝的卖家中心不见了
萌叔装机 知乎
武功山 知乎
len在vb中什么意思
美国钻井平台数据最新
深圳linux培训
会计应对大数据时代
外资买卖数据哪里找
苹果mac怎么安装迅雷
菲律宾淘宝
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 忘记qq邮箱独立密码怎么办 网易邮箱账号忘了怎么办 微信登录密码忘了怎么办 微信太久没登录登录不上怎么办 邮箱独立密码忘记了怎么办 苹果设置id没有邮箱怎么办 苹果手机设置id没有邮箱怎么办 邮箱的附件过期了怎么办 邮箱里的附件过期了怎么办 邮箱中附件过期了怎么办 扣扣邮箱附件过期怎么办 公司网页版邮箱进不去了怎么办 农行客户端密码忘记了怎么办 中国银行客户端密码忘记了怎么办 建行客户端登录密码忘记了怎么办 中国移动客户端密码忘记了怎么办 再歪一点授权码绑定怎么办 网易邮箱号忘了怎么办 忘记支付宝账号和密码怎么办 支付宝账号密码忘了怎么办 发邮箱文件超2g怎么办 报考计算机二级邮箱不存在怎么办 苹果邮箱登录要imap密码怎么办 注销微信支付后怎么办 手机卡注销后支付宝怎么办 12306手机邮箱都换了怎么办 网易手机邮箱手机换了怎么办 崩坏3号被盗了怎么办 qq账号被永久冻结了怎么办 淘宝账号被永久冻结了怎么办 qq账号被永久冻结怎么办 多多理财账号冻结了怎么办 苹果id安全问题忘记了怎么办 网易邮箱帐号忘了怎么办 网易邮箱密码忘记了怎么办 网易邮箱密码忘了怎么办 网易邮箱忘记邮箱账号怎么办 崩坏3三无号被盗怎么办 qq邮箱给58占用怎么办 支付宝邮箱被占用怎么办 苹果手机下载不了qq怎么办

程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里