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();/*//获取src路径下的文件的方式 ————》ClassLoader类加载器ClassLoader classLoader = JDBCUtils.class.getClassLoader();URL res = classLoader.getResource("E:\\IDEA\\IdeaProjects\\jdbc\\jdbc\\src\\cn\\jdbc.properties");String path = res.getPath();System.out.println(path);*///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();}}/*** 获取连接** @return 连接对象*/public static Connection getConnection() throws SQLException {return DriverManager.getConnection(url,user,passward);}/*** 释放资源* @param stmt* @param conn*/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();}}}/*** 释放资源* @param stmt* @param conn*/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:///booksuser = rootpassward = 123driver = com.mysql.cj.jdbc.Driver

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

存储过程

DELIMITER $$CREATE PROCEDURE reader_namelist(IN readern VARCHAR(10))BEGINSELECT readerno,readername,readertype,readerdeptFROM readerWHERE readername LIKE CONCAT('%',readern,'%')ORDER BY readertype;END $$

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

数据库

reader表

结果截图

如果觉得《java JDBC连接MySQL数据库调用存储过程进行查询》对你有帮助，请点赞、收藏，并留下你的观点哦！