JSP使用存储过程

来源：互联网发布：国外源码网站编辑：程序博客网时间：2024/05/25 08:14

1、建立对象

package org.ygwy.procedure.test;

public class InfoVo {
private String Id;
private String JournalNumber;
private String RcName;
private String HouseNumber;
private String HCName;
private String OwnerName;
private String FeeYearMonth;
private String EfsNum;
private String EfeNum;
private String EssNum;
private String EseNum;
private String EtsNum;
private String EteNum;
private String Etotal;
private String EPrice;
private String WfsNum;
private String WfeNum;
private String WssNum;
private String WseNum;
private String WtsNum;
private String WteNum;
private String Wtotal;
private String WPrice;
private String Dkm;
private String Dkmje;
private String HouseArea;
private String PropertyExpensePrice;
private String Wyje;
private String LateFee;
private String UltimoBalance;
private String InstantBalance;
private String AmountReceivable;
private String RealReceivable;
private String RepairFee;
private String OtherFee;
public String getId() {
  return Id;
}
public void setId(String id) {
  Id = id;
}
public String getJournalNumber() {
  return JournalNumber;
}
public void setJournalNumber(String journalNumber) {
  JournalNumber = journalNumber;
}
public String getRcName() {
  return RcName;
}
public void setRcName(String rcName) {
  RcName = rcName;
}
public String getHouseNumber() {
  return HouseNumber;
}
public void setHouseNumber(String houseNumber) {
  HouseNumber = houseNumber;
}
public String getHCName() {
  return HCName;
}
public void setHCName(String name) {
  HCName = name;
}
public String getOwnerName() {
  return OwnerName;
}
public void setOwnerName(String ownerName) {
  OwnerName = ownerName;
}
public String getFeeYearMonth() {
  return FeeYearMonth;
}
public void setFeeYearMonth(String feeYearMonth) {
  FeeYearMonth = feeYearMonth;
}
public String getEfsNum() {
  return EfsNum;
}
public void setEfsNum(String efsNum) {
  EfsNum = efsNum;
}
public String getEfeNum() {
  return EfeNum;
}
public void setEfeNum(String efeNum) {
  EfeNum = efeNum;
}
public String getEssNum() {
  return EssNum;
}
public void setEssNum(String essNum) {
  EssNum = essNum;
}
public String getEseNum() {
  return EseNum;
}
public void setEseNum(String eseNum) {
  EseNum = eseNum;
}
public String getEtsNum() {
  return EtsNum;
}
public void setEtsNum(String etsNum) {
  EtsNum = etsNum;
}
public String getEteNum() {
  return EteNum;
}
public void setEteNum(String eteNum) {
  EteNum = eteNum;
}
public String getEtotal() {
  return Etotal;
}
public void setEtotal(String etotal) {
  Etotal = etotal;
}
public String getEPrice() {
  return EPrice;
}
public void setEPrice(String price) {
  EPrice = price;
}
public String getWfsNum() {
  return WfsNum;
}
public void setWfsNum(String wfsNum) {
  WfsNum = wfsNum;
}
public String getWfeNum() {
  return WfeNum;
}
public void setWfeNum(String wfeNum) {
  WfeNum = wfeNum;
}
public String getWssNum() {
  return WssNum;
}
public void setWssNum(String wssNum) {
  WssNum = wssNum;
}
public String getWseNum() {
  return WseNum;
}
public void setWseNum(String wseNum) {
  WseNum = wseNum;
}
public String getWtsNum() {
  return WtsNum;
}
public void setWtsNum(String wtsNum) {
  WtsNum = wtsNum;
}
public String getWteNum() {
  return WteNum;
}
public void setWteNum(String wteNum) {
  WteNum = wteNum;
}
public String getWtotal() {
  return Wtotal;
}
public void setWtotal(String wtotal) {
  Wtotal = wtotal;
}
public String getWPrice() {
  return WPrice;
}
public void setWPrice(String price) {
  WPrice = price;
}
public String getDkm() {
  return Dkm;
}
public void setDkm(String dkm) {
  Dkm = dkm;
}
public String getDkmje() {
  return Dkmje;
}
public void setDkmje(String dkmje) {
  Dkmje = dkmje;
}
public String getHouseArea() {
  return HouseArea;
}
public void setHouseArea(String houseArea) {
  HouseArea = houseArea;
}
public String getPropertyExpensePrice() {
  return PropertyExpensePrice;
}
public void setPropertyExpensePrice(String propertyExpensePrice) {
  PropertyExpensePrice = propertyExpensePrice;
}
public String getWyje() {
  return Wyje;
}
public void setWyje(String wyje) {
  Wyje = wyje;
}
public String getLateFee() {
  return LateFee;
}
public void setLateFee(String lateFee) {
  LateFee = lateFee;
}
public String getUltimoBalance() {
  return UltimoBalance;
}
public void setUltimoBalance(String ultimoBalance) {
  UltimoBalance = ultimoBalance;
}
public String getInstantBalance() {
  return InstantBalance;
}
public void setInstantBalance(String instantBalance) {
  InstantBalance = instantBalance;
}
public String getAmountReceivable() {
  return AmountReceivable;
}
public void setAmountReceivable(String amountReceivable) {
  AmountReceivable = amountReceivable;
}
public String getRealReceivable() {
  return RealReceivable;
}
public void setRealReceivable(String realReceivable) {
  RealReceivable = realReceivable;
}
public String getRepairFee() {
  return RepairFee;
}
public void setRepairFee(String repairFee) {
  RepairFee = repairFee;
}
public String getOtherFee() {
  return OtherFee;
}
public void setOtherFee(String otherFee) {
  OtherFee = otherFee;
}
}
2、数据库连接类

package org.ygwy.procedure.test;

import java.sql.Connection;
import java.sql.DriverManager;

public class DBConnection {
    static Connection con = null;
    public static Connection getConnection(){
        try {
            Class.forName("com.mysql.jdbc.Driver");  //注册数据库驱动
            String url = "jdbc:mysql://localhost:3306/ygwy"; //db_database02数据库名
            String user = "root";      //数据库连接用户名
            String password = "root";     //数据库连接密码
            con = DriverManager.getConnection(url, user, password);
            con.setAutoCommit(false);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return con;
    }
}

3、操作类

package org.ygwy.procedure.test;

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

public class InfoDao {
  public List findAll(String s, String e) {
  List list = new ArrayList(); // 声明List对象
  Connection con = null; // 声明Connection对象
  CallableStatement proc = null; // 声明CallableStatement
  ResultSet rs = null; // ResultSet对象
  try {
   con = DBConnection.getConnection(); // 创建数据库连接
   proc = con.prepareCall("{call pro_test(?,?)}");// 调用存储过程
   proc.setString(1, s);
   proc.setString(2, e);
   rs = proc.executeQuery(); // 执行获得结果集
   while (rs.next()) { // 遍历结果集
    InfoVo gib = new InfoVo(); // 声明GuestInfoBean对象
    gib.setId(rs.getString(1));
    gib.setJournalNumber(rs.getString(2));
    gib.setRcName(rs.getString(3));
    gib.setHouseNumber(rs.getString(4));
    gib.setHCName(rs.getString(5));
    gib.setOwnerName(rs.getString(6));
    gib.setFeeYearMonth(rs.getString(7));
    gib.setEfsNum(rs.getString(8));
    gib.setEfeNum(rs.getString(9));
    gib.setEssNum(rs.getString(10));
    gib.setEseNum(rs.getString(11));
    gib.setEtsNum(rs.getString(12));
    gib.setEteNum(rs.getString(13));
    gib.setEtotal(rs.getString(14));
    gib.setEPrice(rs.getString(15));
    gib.setWfsNum(rs.getString(16));
    gib.setWfeNum(rs.getString(17));
    gib.setWssNum(rs.getString(18));
    gib.setWseNum(rs.getString(19));
    gib.setWtsNum(rs.getString(20));
    gib.setWteNum(rs.getString(21));
    gib.setWtotal(rs.getString(22));
    gib.setWPrice(rs.getString(23));
    gib.setDkm(rs.getString(24));
    gib.setDkmje(rs.getString(25));
    gib.setHouseArea(rs.getString(26));
    gib.setPropertyExpensePrice(rs.getString(27));
    gib.setWyje(rs.getString(28));
    gib.setLateFee(rs.getString(29));
    gib.setUltimoBalance(rs.getString(30));
    gib.setInstantBalance(rs.getString(31));
    gib.setAmountReceivable(rs.getString(32));
    gib.setRealReceivable(rs.getString(33));
    gib.setRepairFee(rs.getString(34));
    gib.setOtherFee(rs.getString(35));
    list.add(gib); // 加入list列表
   }
   rs.close(); // 关闭rs对象
   proc.close(); // 关闭proc对象
  } catch (SQLException ex) {
   ex.printStackTrace();
  } finally {
   try {
    con.close(); // 关闭con对象
   } catch (SQLException ex) {
    ex.printStackTrace();
   } finally {
    con = null;
   }
  }
  return list; // 返回list列表
}
}
4、JSP页面

<%@page contentType="text/html" pageEncoding="gbk"%>
<%@page import="org.ygwy.procedure.test.InfoDao" %>
<%@page import="org.ygwy.procedure.test.InfoVo" %>
<%@page import="java.util.*" %>
<html>
<head>
<title>JDBC中使用存储过程</title>
<link href="css/css.css" rel="stylesheet" type="text/css">
<style type="text/css">
<!--
.STYLE1 {
color: #FFFFFF;
font-weight: bold;
}
.STYLE2 {
font-size: 10pt;
font-weight: bold;
line-height: 23px;
}

-->
</style>
</head>
<body>

</body>
</html>