Preface
Stored Procedure is stored in the database and is called again after the first compilation, and does not need to be compiled again. The user executes it by specifying the name of the stored procedure and giving parameters (if the stored procedure has parameters).
Java calls MySQL stored procedures, which require JDBC connection and environment eclipse
First, check the stored procedures of the database in MySQL, and then write code calls
mysql> show procedure status;+------+-------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |+------+-------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+| book | findAllBook | PROCEDURE | root@localhost | 2016-09-04 11:13:31 | 2016-09-04 11:13:31 | DEFINER | | gbk | gbk_chinese_ci | utf8_general_ci || book | pro_test | PROCEDURE | root@localhost | 2016-11-13 08:27:17 | 2016-11-13 08:27:17 | DEFINER | | gbk | gbk_chinese_ci | utf8_general_ci || book | pro_user | PROCEDURE | root@localhost | 2016-11-13 08:44:34 | 2016-11-13 08:44:34 | DEFINER | | gbk | gbk_chinese_ci | utf8_general_ci |+------+-------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+rows in set (0.01 sec)mysql> show create procedure findAllBook;+-------------+------------------------+---------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |+-------------+------------------------+---------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+| findAllBook | NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `findAllBook`()begin select * from tb_books;end | gbk | gbk_chinese_ci | utf8_general_ci |+-------------+------------------------+---------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+row in set (0.00 sec)
1. Project directory structure
2. Book.java
package com.scd.book;public class Book { private String name; //book name private double price; //price private int bookCount; //quantity private String author; //author public String getName() { //System.out.println(name); return name; } public void setName(String name) { this.name = name; } public double getPrice() { return price; } public void setPrice(double price) { this.price = price; } public int getBookCount() { return bookCount; } public void setBookCount(int bookCount) { this.bookCount = bookCount; } public String getAuthor() { return author; } public void setAuthor(String author) { //System.out.println(author); this.author = author; }}3. FindBook.java
package com.scd.book;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;public class FindBook { /** * Get database connection* @return Connection object*/ public Connection getConnection() { Connection conn = null; //Database connection try { Class.forName("com.mysql.jdbc.Driver"); //Load the database driver and register it to the driver manager/*Database link address*/ String url = "jdbc:mysql://localhost:3306/book?useUnicode=true&characterEncoding=UTF-8"; String username = "root"; String password = "123456"; /*Create Connection link*/ conn = DriverManager.getConnection(url, username, password); } catch (ClassNotFoundException e){ e.printStackTrace(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return conn; //Return database connection} /** * Query data through stored procedures* @return List<Book> */ public List<Book> findAll() { List <Book> list = new ArrayList<Book>(); //Instantiate List object Connection conn = getConnection(); //Create database connection try { //Calling stored procedure CallableStatement cs = conn.prepareCall("{call findAllBook()}"); ResultSet rs = cs.executeQuery(); //Execute query operation and get the result set while(rs.next()) { Book book = new Book(); //Instantiate the Book object book.setName(rs.getString("name")); //Assign the name attribute book.setPrice(rs.getDouble("price")); //Assign the price attribute book.setBookCount(rs.getInt("bookCount")); //Assign the bookCount attribute book.setAuthor(rs.getString("author")); //Assign the author attribute list.add(book); } }catch(Exception e) { e.printStackTrace(); } return list; //Return list } /** * Main function calls stored procedure (tested) * @param args */ public static void main(String[] args) { FindBook fb = new FindBook(); //System.out.println(fb.findAll()); for (Book book : fb.findAll()) { System.out.print(book.getName() + "--" + book.getPrice() + "--"); System.out.print(book.getBookCount() + "--" + book.getAuthor()); System.out.println(); } }}4. Right-click Run As --> Java Application, console output
5. Execute SQL statements in stored procedures
mysql> select * from tb_books;+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 67.42 | 775 | Mr.Cheng |+------------------+-------+-----------+----------+rows in set (0.00 sec)
Summarize
The above is the entire content of Java calling MySQL stored procedures. I hope that the content of this article will be of some help to everyone's study or work. If you have any questions, you can leave a message to communicate.