mysql返回一个结果集的存储过程小例子

以你之姓@ 2023-08-17 15:47 68阅读 0赞

# 1、数据库： #

> **mysql数据库，在mysql数据库中新建一个tbtest数据库，在tbtest库中新建person表。字段如下  
> 人员表（姓名（字符串），手机(字符串)，地址（字符串））**

CREATE TABLE `person` (
      `p_name` varchar(50) DEFAULT NULL,
      `p_number` varchar(50) DEFAULT NULL,
      `p_address` varchar(50) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

> 创建好表，自己添加一点数据

# 2、创建结果集： #

### 2.1、要求： ###

> **1.存储过程名称：sp\_test\_get\_persons  
> 2.返回含有2个字段的结果集，一个字段是int类型的id，另外一个是varchar(100)类型的name  
> 3.调用时，可以指定最大返回结果集中的数据个数。例如：call sp\_test\_get\_persons(10) 调用后返回的结果集中最多10条记录。**

## 2.1、Navicat创建结果集 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xlbW9uU25t_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xlbW9uU25t_size_16_color_FFFFFF_t_70 1][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xlbW9uU25t_size_16_color_FFFFFF_t_70 2][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xlbW9uU25t_size_16_color_FFFFFF_t_70 3][]

> **保存，设置名字为：sp\_test\_get\_persons**

一个简单的结果集就ok了

# 3、需要的jar包： #

>  **1、maven项目在pom.xml中<dependencies>里加入这个：**

<dependency>
                    <groupId>mysql</groupId>
                    <artifactId>mysql-connector-java</artifactId>
                    <version>5.0.8</version>
                </dependency>

>  **2、或者 简单的java项目，在加入jar包，并导入项目依赖**

**mysql-connector-java5.0.8**

# 4、新建java项目： #

## 4.1、DBManager.java ##

> **创建jdbc工具类**

package com.lemon.test;
     
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
     
    public class DBManager {
     
    	 //数据库信息
        private static final String url = "jdbc:mysql://localhost:3306/tbtest";
        private static final String name = "com.mysql.jdbc.Driver";
        private static final String username = "root";
        private static final String password = "123456";
        
        private static Connection connection = null;
        private static PreparedStatement preparedStatement = null;
     
        //创建连接
        public DBManager(){
            try{
                Class.forName(name);
                connection = DriverManager.getConnection(url, username, password);
     
            }catch(Exception e){
                e.printStackTrace();
            }
        }
     
        //关闭连接
        private void close(){
            try{
                connection.close();
                preparedStatement.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        
        //查询
        public void queryAll() throws SQLException{
        	String sql = "select * from person";
        	preparedStatement = connection.prepareStatement(sql);
        	
        	 String name,number,address;
     
             try
             {
                 //执行sql语句
                 ResultSet result = preparedStatement.executeQuery();
     
                 //查询到数据，对应赋值
                 while(result.next()){
                     name = result.getString(1);
                     number = result.getString(2);
                     address = result.getString(3);
     
                     //显示
                     System.out.println(name + ",\t" + number + ",\t" + address + ".\t");
                 }
     
                 result.close();
     
             }catch (Exception e){
                 e.printStackTrace();
             }
        }
    	
        //增加
        public void addPerson(String name ,String number ,String address) throws SQLException{
        	String sql = "insert into person (p_name,p_number,p_address) values (?,?,?)";
        	preparedStatement = connection.prepareStatement(sql);
        	preparedStatement.setString(1, name);
        	preparedStatement.setString(2, number);
        	preparedStatement.setString(3, address);
        	
        	int row = preparedStatement.executeUpdate();
        		if(row > 0){
        			System.out.println("添加成功!");
        	    }
        		preparedStatement.close();
        	    
        }
        
        //删除
        public void delPersonByName(String name) throws SQLException{
        	String sql = "delete from person where p_name = ?";
        	//
        	preparedStatement = connection.prepareStatement(sql);
        	//完整sql语句
        	preparedStatement.setString(1, name);
     
        	int row = preparedStatement.executeUpdate();
        	if(row > 0){
        	    System.out.println("删除成功!");
        	}else{
        	    System.out.println("请正确输入需要删除的姓名！");
        	}
        	preparedStatement.close();
        	    
        }
        
        //更新
        public void updatePerson(String name ,String number ,String address) throws SQLException{
     
        	String sql = "update person set";
        	if(number != null){
        		sql = sql + " p_number = ?";
        	}
        	if(number != null && address != null){
        	    sql = sql + ",";
            }
        	if(address != null){
        		sql = sql + "p_address = ?";
        	}
        	sql = sql + "where p_name = ?";
        	//
        	preparedStatement = connection.prepareStatement(sql);
     
        	int i = 1;
            //完整sql语句
            if(number != null){
                preparedStatement.setString(i, number);
                i++;
            }
            if(address != null){
                preparedStatement.setString(i, address);
                i++;
            }
            preparedStatement.setString(i, name);
            int row = preparedStatement.executeUpdate();
            if(row > 0){
                System.out.println("更新成功!");
            }else{
                System.out.println("请正确输入需要更新的姓名！");
            }
            preparedStatement.close();
        }
    }

## 4.2、test.java ##

> **测试类：**

package com.lemon.test;
    
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.Scanner;
    
    import javax.swing.DefaultButtonModel;
    
    public class test {
    
    	public static void main(String[] args) throws SQLException, ClassNotFoundException {
            Scanner input = new Scanner(System.in);
            //实例化
            DBManager dbManager = new DBManager();
            
            //查询
            //dbManager.queryAll();
            
            //添加
            //dbManager.addPerson("张三","123456789","上海");
            //dbManager.queryAll();
            
            //按照名字删除
            //dbManager.delPersonByName("张三");
            //按照名字更新
            //dbManager.updatePerson("sss","123213","12321");
    
            System.out.println("请输入返回的最大结果集");
            int num = input.nextInt();
            System.out.println(num);
            //返回结果集
            dbManager.printResult(num);
    	}
    }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xlbW9uU25t_size_16_color_FFFFFF_t_70]: /images/20230810/325b9d31dcfa422db46eeb6acfc44bb9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xlbW9uU25t_size_16_color_FFFFFF_t_70 1]: /images/20230810/cd37bad9756c4f82893af12d826a3f21.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xlbW9uU25t_size_16_color_FFFFFF_t_70 2]: /images/20230810/df802e6691e245b4909ea55efe1abd26.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xlbW9uU25t_size_16_color_FFFFFF_t_70 3]: /images/20230810/2345d9a9c175417aaebbf0ab61399332.png