数据库存储过程以及用JDBC API调用的示例

  • 12月 5, 2010

1. 存储过程定义

将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来, 那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时,只需调用execute,即可自动完成命令。

2. 为什么要用存储过程

1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
3.存储过程可以重复使用,可减少数据库开发人员的工作量
4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权


3. 一个简单的实现分页功能的存储过程

DELIMITER $$
 
DROP PROCEDURE IF EXISTS `neoremind`.`sp_page`$$
 
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_page`(
 IN _pagecurrent INT,/*当前页*/
 IN _pagesize INT,/*每页的记录数*/
 IN _ifelse VARCHAR(1000),/*显示字段*/
 IN _where VARCHAR(1000),/*条件*/
 IN _order VARCHAR(1000)/*排序*/
)
    COMMENT '分页存储过程'
BEGIN
 IF _pagesize<=1 THEN
  SET _pagesize=20;
 END IF;
 IF _pagecurrent < 1 THEN
  SET _pagecurrent = 1;
 END IF;
 
 SET @strsql = concat('select ',_ifelse,' from ',_where,' ',_order,' limit ',_pagecurrent*_pagesize-_pagesize,',',_pagesize);
 PREPARE stmtsql FROM @strsql;
 EXECUTE stmtsql;
 deallocate PREPARE stmtsql;
 SET @strsqlcount=concat('select count(1) as count from ',_where);/*count(1) 这个字段最好是主键*/
 PREPARE stmtsqlcount FROM @strsqlcount;
 EXECUTE stmtsqlcount;
 deallocate PREPARE stmtsqlcount;
END$$
 
DELIMITER ;

调用方法：

CALL sp_Page(2,100,'*','user','order by iduser asc');


4. 利用Java调用存储过程

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource;
 
public class InvokeMySQLProcedure {
 
	private static MysqlConnectionPoolDataSource dataSource;
 
	/*
	 * 获取数据库连接
	 */
	public Connection getConnection() throws Exception{
		return dataSource.getConnection();
	}
 
	/*
	 * 静态初始化块，新建数据库连接池
	 */
	static
	{
		dataSource = new MysqlConnectionPoolDataSource();
	    dataSource.setUser("root");
	    dataSource.setPassword("passw0rd");
	    dataSource.setServerName("localhost");
	    dataSource.setPort(3306);
	    dataSource.setDatabaseName("neoremind");
	}
 
 
	public static void main(String[] args){
 
		// 当前页
		int pagecurrent = 1;
		// 每页显示数量
		int pagesize = 5;
		// 查找字段
		String columnNameList = "*";
		// 表名
		String table = "user";
		// 排序策略
		String order = "order by iduser asc";
 
		Connection connection =  null;
		try{
			connection = new InvokeMySQLProcedure().getConnection();
			connection.setAutoCommit(false);
			// 调用SQL存储过程
		    CallableStatement proc = connection.prepareCall("{call sp_page(?,?,?,?,?) }");
		    proc.setInt(1, pagecurrent);
		    proc.setInt(2, pagesize);
		    proc.setString(3, columnNameList);
		    proc.setString(4, table);
		    proc.setString(5, order);
		    // 执行开始，得到结果
		    ResultSet rs = proc.executeQuery();
		    connection.commit();
		    // 打印结果
		    System.out.println("id  |  username");
		    System.out.println("--------------------");
		    while(rs.next())
			{
				int id = rs.getInt(1);
				String title = rs.getString(2);
			    System.out.print(id + "  |  ");
			    System.out.println(title);
			}
		} catch(Exception e){
			System.err.println(e.getMessage());
			e.printStackTrace();
			if (connection != null) {
				try {
					connection.rollback();
				} catch (Exception e1) {
					System.err.println(e1.getMessage());
					e1.printStackTrace();
				}
			}
		}finally{
			try{
				if(connection != null)
				{
					connection.close();
				}
			} catch(Exception e)
			{
				e.printStackTrace();
			}
		}
	}
}

Leave a Comment.