14.java 操作存储过程

最新推荐文章于 2022-11-23 17:38:49 发布

信行合一

最新推荐文章于 2022-11-23 17:38:49 发布

阅读量974

点赞数

分类专栏： 20_mysql

本文链接：https://blog.csdn.net/tengdazhang770960436/article/details/45953729

版权

20_mysql 专栏收录该内容

23 篇文章 2 订阅

订阅专栏

1.新建表

CREATE TABLE test (
`id`  int(20) NOT NULL AUTO_INCREMENT ,
`name`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=4
ROW_FORMAT=COMPACT
;

2.新建存储过程

1.创建存储过程，指定参数值

drop procedure if exists pr_param_in2out;
create procedure pr_param_in2out
(
in pid int,
out pname varchar(50) CHARSET utf8
)
begin
# 创建存储过程，指定参数的字符集，如果带有中文varchar需要指定 它的字符集为 utf-8，否则 java 取值会报错的
 SELECT `name` INTO pname from test where id=pid;
end;

2.创建存储过程，返回多个结果集

drop procedure if exists pr_param_in2out1;
create procedure pr_param_in2out1
(
in pid int,
)
begin
# 可以返回多个结果集
 SELECT * from test where id=pid;
 SELECT * from test;
end;

3创建存储过程，返回单个结果集

drop procedure if exists pr_param_in2out2;
create procedure pr_param_in2out2
(
in pid int
)
begin
 SELECT * from test where id=pid;
end;

4. 创建带有游标的存储过程

drop procedure if exists pro_handledata;
CREATE PROCEDURE `pro_handledata`()
BEGIN
 #本存储过程用来根据student表和class表来加工生成stu_info表的数据
 DECLARE done INT DEFAULT 0;
 #定义stu_info表所需要的变量
 DECLARE var_sid INTEGER(10);
 DECLARE var_sname VARCHAR(50) CHARSET utf8  DEFAULT '';
 DECLARE var_classid INTEGER(10);

 DECLARE var_classname VARCHAR(50) CHARSET utf8  DEFAULT '';

 #定义游标
 DECLARE cur_student CURSOR FOR
 SELECT sid,sname,classid  from student;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

 #循环处理纳税主体的数据
 OPEN cur_student;

   FETCH cur_student INTO var_sid,var_sname,var_classid;

   WHILE done = 0 DO
    #获取var_classid所对应的班级名称
    SELECT getClassName(var_classid) INTO var_classname;
    #插入数据
    INSERT INTO stu_info VALUES(var_sid,var_sname,var_classname);
     
    FETCH cur_student INTO var_sid,var_sname,var_classid;

   END WHILE;   

  CLOSE cur_student;

 END;

5.创建函数，指定返回值

drop FUNCTION if exists fc_getName;
CREATE FUNCTION `fc_getName`(pid INTEGER)
RETURNS varchar(50)
DETERMINISTIC
BEGIN
 #本函数根据传递进来的班级编号，获取它所在班级名称
 DECLARE pname VARCHAR(50) CHARSET utf8  DEFAULT '';
 SELECT `name` INTO name from test where id=pid;
 RETURN pname;
 END;

3.存储过程的一个综合示例

1.创建三张表：

1.1.student

CREATE TABLE `student` (
 `sid`  int(10) NOT NULL DEFAULT 0 ,
 `sname`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
 `classid`  int(10) NULL DEFAULT NULL ,
 PRIMARY KEY (`sid`)
 )
 插入数据：
 INSERT INTO `student` VALUES (1, '小强', 1);
 INSERT INTO `student` VALUES (2, '小刚', 2);
 INSERT INTO `student` VALUES (3, '小明', 3);
 INSERT INTO `student` VALUES (4, '小王', 1);

1.2.class

 CREATE TABLE `class` (
 `cid`  int(10) NOT NULL DEFAULT 0 ,
 `cname`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
 PRIMARY KEY (`cid`)
 )
 插入数据：
 INSERT INTO `class` VALUES (1, '一班');
 INSERT INTO `class` VALUES (2, '二班');
 INSERT INTO `class` VALUES (3, '三班');

1.3.stu_info

CREATE TABLE `stu_info` (
 `id`  int(10) NOT NULL DEFAULT 0 ,
 `name`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
 `className`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
 PRIMARY KEY (`id`)
 )

2.创建根据班级id获取班级名称的函数getClassName

 CREATE FUNCTION `getClassName`(classId INTEGER)
  RETURNS varchar(50) CHARSET utf8
  DETERMINISTIC
 BEGIN
 #本函数根据传递进来的班级编号，获取它所在班级名称
 DECLARE className VARCHAR(50) CHARSET utf8  DEFAULT '';
 SELECT `cname` INTO className from class where cid=classId;
 RETURN className;
 END;

3.创建自动处理数据的存储过程process_data

CREATE PROCEDURE `process_data`()
 BEGIN
 #本存储过程用来根据student表和class表来加工生成stu_info表的数据
 DECLARE done INT DEFAULT 0;
 #定义stu_info表所需要的变量
 DECLARE var_sid INTEGER(10);
 DECLARE var_sname VARCHAR(50) CHARSET utf8  DEFAULT '';
 DECLARE var_classid INTEGER(10);
 DECLARE var_classname VARCHAR(50) CHARSET utf8  DEFAULT '';

 #定义游标
 DECLARE cur_student CURSOR FOR
 SELECT sid,sname,classid  from student;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

 #循环处理纳税主体的数据
 OPEN cur_student;

   FETCH cur_student INTO var_sid,var_sname,var_classid;

   WHILE done = 0 DO
    #获取var_classid所对应的班级名称
    SELECT getClassName(var_classid) INTO var_classname;
    #插入数据
    INSERT INTO stu_info VALUES(var_sid,var_sname,var_classname);
     
    FETCH cur_student INTO var_sid,var_sname,var_classid;

   END WHILE;   

  CLOSE cur_student;

 END;

4.执行存储过程结果：

 id name className
 1 小强 一班
 2 小刚 二班
 3 小明 三班
 4 小王 一班

5.注意：

在mysql中经常会出现：1366 - Incorrect string value: '\xE4\xB8\xAD\xE6\x96\x87' for column 'cName' at row 1类似的错误，这儿中错误主要是字符集问题导致的，一般都需要在定义变量，或者创建表的时候指定列或者变量的编码格式为utf8.

4.使用java 操作存储过程

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Types;

public class MySqlTester {
	public static String user;
	public static String password;
	public static String url;
	public static Connection conn;
	
	public static void main(String[] args) throws Exception {
		init_mysql();
//		insert();
//		update();
		delete();
//		insertAndGetGeneraKey();
//		executeProcedureInAndOut();
//		executeProcedureGetMultiResultSet();
//		executeProcedureGetResultSet();
//		executeFunction1();
//		executeFunction2();
	}
	
	   
	/**      
	 * 描述:新增
	 * <pre>
	 * 举例：
	 * </pre>
	 * @throws Exception      
	 */
	public static void insert() throws Exception {
		Statement stmt = conn.createStatement();
		String insert = "insert into test(name) values('hehe')";
		stmt.execute(insert);
		
		String query = "select * from test";
		ResultSet result = stmt.executeQuery(query);
		while (result.next()) {
			System.out.println("id : " + result.getInt("id")
					+ "\tname : " + result.getString("name"));
		}
	}
	
	/**      
	 * 描述:修改
	 * <pre>
	 * 举例：
	 * </pre>
	 * @throws Exception      
	 */
	public static void update() throws Exception {
		Statement stmt = conn.createStatement();
		String update = "update test set name='haha' where id=3";
		stmt.execute(update);
		
		String query = "select * from test";
		ResultSet result = stmt.executeQuery(query);
		while (result.next()) {
			System.out.println("id : " + result.getInt("id")
					+ "\tname : " + result.getString("name"));
		}
	}
	
	/**      
	 * 描述:删除
	 * <pre>
	 * 举例：
	 * </pre>
	 * @throws Exception      
	 */
	public static void delete() throws Exception {
		Statement stmt = conn.createStatement();
		String update = "delete from test where id=3";
		stmt.execute(update);
		
		String query = "select * from test";
		ResultSet result = stmt.executeQuery(query);
		while (result.next()) {
			System.out.println("id : " + result.getInt("id")
					+ "\tname : " + result.getString("name"));
		}
	}


	/**
	 * 新增数据并且返回自增的 key 值
	 * 
	 * @throws Exception
	 */
	public static void insertAndGetGeneraKey() throws Exception {
		String sqlInsert = "insert into test(name) values('test')";
		PreparedStatement pstmt = null;
		pstmt = conn.prepareStatement(sqlInsert,
				Statement.RETURN_GENERATED_KEYS);
		pstmt.execute();
		ResultSet result = pstmt.getGeneratedKeys();
		result.next();
		int key = result.getInt(1);
		System.out.println("自增之后的 id ： " + key);
	}

	

	/**
	 * 描述:jdbc 操作存储过程:根据输入值获取一个返回值
	 * 
	 * <pre>
	 * 举例：
	 * </pre>
	 * 
	 * @throws Exception
	 */
	public static void executeProcedureInAndOut() throws Exception {
		String sql = "{call pr_param_in2out(?,?)}";
		CallableStatement callableStatement = conn.prepareCall(sql);
		callableStatement.registerOutParameter(2, Types.VARCHAR);
		callableStatement.setInt(1, 1);
		callableStatement.execute();
		String name = callableStatement.getString(2);
		System.out.println(name);
	}

	/**
	 * 描述:jdbc 操作存储过程:返回多个返回结果集
	 * 
	 * <pre>
	 * 举例：
	 * </pre>
	 * 
	 * @throws Exception
	 */
	public static void executeProcedureGetMultiResultSet() throws Exception {
		String sql = "{call pr_param_in2out1(?)}";
		CallableStatement callableStatement = conn.prepareCall(sql);
		callableStatement.setInt(1, 1);

		boolean hadResults = callableStatement.execute();
		int i = 0;
		while (hadResults) {
			System.out.println("result No:----" + (++i));
			ResultSet rs = callableStatement.getResultSet();
			while (rs != null && rs.next()) {
				int id1 = rs.getInt(1);
				String name1 = rs.getString(2);
				System.out.println(id1 + ":" + name1);
			}
			hadResults = callableStatement.getMoreResults(); // 检查是否存在更多结果集
		}
	}

	/**
	 * 描述:jdbc 操作存储过程:返回单个返回结果集
	 * 
	 * <pre>
	 * 举例：
	 * </pre>
	 * 
	 * @throws Exception
	 */
	public static void executeProcedureGetResultSet() throws Exception {
		String sql = "{call pr_param_in2out2(?)}";
		CallableStatement callableStatement = conn.prepareCall(sql);
		callableStatement.setInt(1, 1);

		callableStatement.execute();
		ResultSet rs = callableStatement.getResultSet();
		while (rs != null && rs.next()) {
			int id1 = rs.getInt(1);
			String name1 = rs.getString(2);
			System.out.println(id1 + ":" + name1);
		}
	}

	/**
	 * 描述:jdbc 操作函数1:根据输入值获取一个返回值
	 * 
	 * <pre>
	 * 举例：
	 * </pre>
	 * 
	 * @throws Exception
	 */
	public static void executeFunction1() throws Exception {
		String sql = "{?=call fc_getName(?)}";
		CallableStatement callableStatement = conn.prepareCall(sql);
		// 注意这个设置值得时候，第一个 ? 并没有被记入到参数个数中，后面括号里面有多少 ? 就是多少个
		callableStatement.registerOutParameter(1, Types.VARCHAR);
		callableStatement.setInt(1, 1);
		callableStatement.execute();
		String name = callableStatement.getString(1);
		System.out.println(name);
	}

	/**
	 * 描述:jdbc 操作函数2:根据输入值获取一个返回值
	 * 
	 * <pre>
	 * 举例：
	 * </pre>
	 * 
	 * @throws Exception
	 */
	public static void executeFunction2() throws Exception {
		String sql = "select fc_getName(?)";
		CallableStatement callableStatement = conn.prepareCall(sql);
		// 注意这个设置值得时候，第一个 ? 并没有被记入到参数个数中，后面括号里面有多少 ? 就是多少个
		callableStatement.setInt(1, 1);
		callableStatement.execute();
		ResultSet rs = callableStatement.getResultSet();
		String name = null;
		if (rs.next()) {
			name = rs.getString(1);
		}
		System.out.println(name);
	}

	/**
	 * 描述:初始化数据库连接
	 * 
	 * <pre>
	 * 举例：
	 * </pre>
	 * 
	 * @return
	 */
	public static Connection init_mysql() {
		try {
			// 初始化url,user,password
			// url参数:"jdbc:数据库类型:主机IP/数据库名?用户名=&密码=&characterEncoding="
			url = "jdbc:mysql://localhost/test1?characterEncoding=utf-8";
			user = "root";
			password = "123456";
			
			// 加载驱动
			Class.forName("org.gjt.mm.mysql.Driver");
			// 建立连接
			conn = DriverManager.getConnection(url, user, password);
		} catch (Exception e) {
			System.out.println("数据库连接异常!");
			e.printStackTrace();
		}
		return conn;
	}

}

信行合一

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
14.java 操作存储过程

1.新建表CREATE TABLE test (`id` int(20) NOT NULL AUTO_INCREMENT ,`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,PRIMARY KEY (`id`))ENGINE=InnoDBDEFAULT CHARACTER SET=...
复制链接

扫一扫

专栏目录