java JDBC连接MySQL数据库调用存储过程进行查询

• 主程序代码
• • 工具类
  • 文件信息
  • 存储过程
• 结果截图

package Mysql;

import util.JDBCUtils;
import java.sql.*;
import java.util.Scanner;

public class SaveProcess {
    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入需要查询的名字，或姓名当中的一个字。");
        String nm = sc.next();
        search(nm);
    }
    public static void search(String name) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        //1.获取连接对象
        try {
            //1.获取连接对象
            conn = JDBCUtils.getConnection();
            //2.定义sql语句
            String sql ="CALL reader_namelist(?)";
            //3.获取执行sql的对象
            pstmt =  conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            //4.准备参数
            pstmt.setString(1,name);
            //5.执行查询操作
            rs = pstmt.executeQuery();


            //处理结果集
            if(rs.next()){
                System.out.println("查询结果: ");
                System.out.println("读者号         读者姓名        读者类别      读者系别");
                rs.previous(); //前移一步
            }else {
                System.out.println("查不到数据");
            }
            while(rs.next()){
                System.out.println(rs.getString(1).trim() + "           " + rs.getString(2).trim() + "         " + rs.getString(3).trim() + "          " + rs.getString(4).trim());
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtils.close(pstmt,conn);
        }
    }
}

上面第三步如果仅仅将sql参数传入会报错( Operation not allowed for a result set of type ResultSet.TYPE_FORWARD_ONLY.)
这是因为出现了游标异常，可根据需要改变prepareStatement的参数进而控制游标。具体操作在这不一一列出，将上述报错信息百度即可得知。

package util;

import java.io.FileReader;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

public class JDBCUtils {
    private static String url;
    private static String  user;
    private static String passward;
    private static String driver;
    
    static {
        //读取资源文件,获取值。
        try {
            //1.创建Properties集合类.
            Properties pro = new Properties();

  

            //2.加载文件
            pro.load(new FileReader("E:\IDEA\IdeaProjects\jdbc\jdbc\src\cn\jdbc.properties"));
            //3.获取数据,赋值
            url = pro.getProperty("url");
            user = pro.getProperty("user");
            passward = pro.getProperty("passward");
            driver = pro.getProperty("driver");
            //4.注册驱动
            Class.forName(driver);
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,user,passward);
    }

    
    public static void close(Statement stmt, Connection conn) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
    public static void close(ResultSet rs, Statement stmt, Connection conn) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static void close(PreparedStatement pstmt, Connection conn) {
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

这个类是博主连接JDBC的通用工具类，可根据需要进行修改。

url = jdbc:mysql:///books
user = root
passward = 123
driver = com.mysql.cj.jdbc.Driver

以上是把MySQL的信息写入了文件，通过jdbc工具类进行调用(博主觉得这样更加方便)。

DELIMITER $$
CREATE PROCEDURE reader_namelist
(IN readern VARCHAR(10))
BEGIN
  SELECt readerno,readername,readertype,readerdept
  FROM reader
  WHERe readername LIKE CONCAT('%',readern,'%')
  ORDER BY readertype;
END $$

上面是用得到的MySQL存储过程。

数据库

reader表