JDBC调用存储过程实例

 

书本上没有介绍JDBC访问SQL存储过程的方法，我这里写出来，供大家参考

 

 

先是用SQL2005写的1个存储过程，既有输入参数，又有输出，而且还有返回的结果集：

 

数据库是书本上的一级标题和二级标题

建表的代码：

一级标题：

CREATE TABLE [dbo].[FirstLevelTitle]([Id] [int] IDENTITY(1,1) NOT NULL,[TitleName] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,[Creator] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,[CreateTime] [datetime] NOT NULL,CONSTRAINT [PK_FirstLevelTitle] PRIMARY KEY CLUSTERED ( [Id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO 

二级标题：

CREATE TABLE [dbo].[SecondLevelTitle]([Id] [int] IDENTITY(1,1) NOT NULL,[TitleName] [varchar](100) COLLATE Chinese_PRC_CI_AS NOT NULL,[FilePath] [varchar](200) COLLATE Chinese_PRC_CI_AS NOT NULL,[Creator] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,[CreateTime] [datetime] NOT NULL,[ParentTitle] [int] NOT NULL, CONSTRAINT [PK_SecondLevelTitle] PRIMARY KEY CLUSTERED ([Id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO 

 

存储过程：

--通过一级标题的名字查询相应的二级标题，并返回最新插入的时间

--又有运行结果，又返回了参数

create proc [dbo].[findSecondTitles] @title varchar(50) ,@time datetime outputasbegindeclare @fid intif ((select count(*) from firstleveltitle where titlename=@title)=0)beginset @time = getdate();endelsebegin--先查询一级的IDselect @fid = id from firstleveltitle where titlename=@title;select * from secondleveltitle where parenttitle=@fidselect @time = max(createtime) from secondleveltitle where parenttitle=@fidendend 

 

JDBC代码：

BaseDao类

public class BaseDao {private static final String DRIVER_CLASS = "com.microsoft.sqlserver.jdbc.SQLServerDriver";private static final String DATABASE_URL = "jdbc:sqlserver://localhost:1433;DatabaseName=news";private static final String DATABASE_USRE = "sa";private static final String DATABASE_PASSWORD = "111111";/** * 返回连接 * * @return Connection */public Connection getConnection() {Connection conn = null;try {Class.forName(DRIVER_CLASS);conn = DriverManager.getConnection(DATABASE_URL, DATABASE_USRE, DATABASE_PASSWORD);} catch (Exception e) {e.printStackTrace();}return conn;}/** * 关闭连接 * * @param dbConnection Connection */public void closeConnection(Connection conn) {try {if (conn != null && (!conn.isClosed())) {conn.close();}} catch (SQLException se) {se.printStackTrace();}}/** * 关闭结果集 */public void closeResultSet(ResultSet rs) {try {if (rs != null) {rs.close();rs = null;}} catch (SQLException se) {se.printStackTrace();}}/** * 关闭语句 */public void closeStatement(PreparedStatement ps) {try {if (ps != null) {ps.close();ps = null;}} catch (SQLException se) {se.printStackTrace();}}/** * 关闭所有 * * @param connection * @param statement * @param resultSet */public void closeAll(Connection conn, PreparedStatement ps, ResultSet rs) {closeResultSet(rs);closeStatement(ps);closeConnection(conn);}/** * 关闭所有 * * @param connection * @param statement * @param resultSet */public void closeAll(Connection conn, PreparedStatement ps) {closeStatement(ps);closeConnection(conn);}} 

FirstTitleDao类：

public class FirstTitleDao extends BaseDao {/** * 调用存储过程，通过一级标题的名字查询相应的二级标题，并返回最新插入的时间 * * @param title 一级标题的名字 * @return Object数组，第一个元素为查询的二级标题，第二个元素为最新输入的时间 */public Object[] findSecondTitles(String title) {Object[] objs = new Object[2];Connection conn = null;CallableStatement cs = null;ResultSet rs = null;Timestamp time = null;List<SecondTitle> secondTitles = new ArrayList<SecondTitle>();try {conn = getConnection();cs = conn.prepareCall("{call findSecondTitles(?,?)}");// 输入参数cs.setString(1, title);// 输出参数cs.registerOutParameter(2, Types.TIMESTAMP);rs = cs.executeQuery();while (rs.next()) {SecondTitle secondTitle = new SecondTitle();secondTitle.setId(rs.getInt("id"));secondTitle.setTitleName(rs.getString("titlename"));secondTitle.setFilePath(rs.getString("filepath"));secondTitle.setCreator(rs.getString("creator"));secondTitle.setCreateTime(rs.getTimestamp("createtime"));secondTitle.setParentTitle(rs.getInt("parenttitle"));secondTitles.add(secondTitle);}// 得到第二个输出参数的值time = cs.getTimestamp(2);} catch (SQLException ex) {ex.printStackTrace();} finally {super.closeAll(conn, cs, rs);}objs[0] = secondTitles;objs[1] = time;return objs;}} 

测试类：

public class TestTitle {/** * @param args */@SuppressWarnings("unchecked")public static void main(String[] args) {// 调用存储过程FirstTitleDao firstTitleDao = new FirstTitleDao();Object[] objects = firstTitleDao.findSecondTitles("国际新闻");List<SecondTitle> secondTitles = (List<SecondTitle>) objects[0];for (SecondTitle secondTitle : secondTitles) {System.out.println(secondTitle.getId());System.out.println(secondTitle.getTitleName());System.out.println("-------------");}System.out.println(objects[1]);}}