Java implementation calls MySQL stored procedures detailed _java

Objective

Stored Procedures (Stored Procedure) are stored in the database after the first compilation and then called again without recompiling, the user executes it by specifying the name of the stored procedure and giving the parameter, if the stored procedure has parameters.

Java calls MySQL stored procedures, need to connect with JDBC, environment eclipse

First look at the database stored procedures 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

I. Structure of the Engineering directory

Second, Book.java

Package Com.scd.book;

public class Book {
 private String name;//books name
 private double price;/prices
 private int bookcount;//Quantity
 pri Vate 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;
 }

Third, 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 Database driver, register to drive 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}/** * Querying data through stored procedures * @return list<book>/public list<book> findall () {List &LT book> list = new arraylist<book> (); Instantiate List Object Connection conn = getconnection ();
   Create database connection try {//Call stored procedure CallableStatement cs = Conn.preparecall ("{Call Findallbook ()}"); ResultSet rs = Cs.executequery (); Performs a query operation and gets the result set while (Rs.next ()) {Book book = new book ();///Instantiate the book Object Book.setname (rs.getstring ("name")); /Assign value to the Name property Book.setprice (rs.getdouble ("price")); Assign a value to the Price property Book.setbookcount (Rs.getint ("Bookcount")); Assign a value to the Bookcount property Book.setauthor (rs.getstring ("author"));
   Assign a value to the Author property List.add (book);
  }}catch (Exception e) {e.printstacktrace ();  } return list; Return list}/** * Main function call stored procedure (test use) * @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 (); }
 }
}

Four, right key Run as--> Java application, console output

Execute the SQL statement in the stored procedure

Mysql> select * from Tb_books;
+------------------+-------+-----------+----------+
| name    | price | bookcount | author
| +------------------+-------+-----------+----------+
| Java Bundle Primer to Proficient | 56.78 |  13 | Mr Sun |
| Data Structure   | 67.3 |  8962 | Mr Sun |
| Compiling principle   | 78.66 |  5767 | Mr Sun |
| Data Structure   | 67.42 |  775 | Mr.cheng |
+------------------+-------+-----------+----------+
rows in Set (0.00 sec)
mysql> call Findallbook ();
+------------------+-------+-----------+----------+
| name    | price | bookcount | author
| +------------------+-------+-----------+----------+
| Java Bundle Primer to Proficient | 56.78 |  13 | Mr Sun |
| Data Structure   | 67.3 |  8962 | Mr Sun |
| Compiling principle   | 78.66 |  5767 | Mr Sun |
| Data Structure   | 67.42 |  775 | Mr.cheng |
+------------------+-------+-----------+----------+
rows in Set (0.00 sec)

Summarize

The above is Java invoke the MySQL stored procedure all content, hope this article content to everybody's study or work can bring certain help, if has the question everybody may the message exchange.