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 output as begin declare @fid int if ((select count(*) from firstleveltitle where titlename=@title)=0) begin set @time = getdate(); end else begin --先查询一级的ID select @fid = id from firstleveltitle where titlename=@title; select * from secondleveltitle where parenttitle=@fid select @time = max(createtime) from secondleveltitle where parenttitle=@fid end end 

 

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]); } }