MySQL stored procedures and program calls in Java

MySQL stored procedures and program calls in Java

A stored procedure is a program stored in a database (just like a subroutine in a regular language). To be precise, MySQL supports two types of "routines (routines: one is the stored procedure, and the other is the function that can return values in other SQL statements (like the pre-loaded functions in Mysql, such as pi ()). This article is written for friends who are learning the MySQL stored procedure. In this article, we start by creating an example of the stored procedure in the MySQL CLI (command line interface, then, by calling the created stored procedure in the Java program, you can get started with the MySQL stored procedure.

MySQL stored procedure and Java stored procedure call

1. Example of creating a MySQL Stored Procedure

The specific creation process is as follows:

-- Start the MySQL Service
C:/Documents and Settings/Zengming Zhang> net start mysql

The MySQL service has been started successfully.

-- Log on to the MySQL Console
C:/Documents and Settings/Zengming Zhang> mysql-u root-p
Enter password :**********
Welcome to the MySQL monitor. Commands end with; or/g.
Your MySQL connection id is 1 to server version: 5.0.18-nt

Type 'help; 'or'/H' for help. type'/C' to clear the buffer.

-- Select database
Mysql> use test;
Database changed

-- Create an example table
Mysql> create table zzm (
-> Id int primary key auto_increment,
-> Name varchar (10)
-> );
Query OK, 0 rows affected (0.20 sec)
Mysql> insert into zzm (name) values ('zhang ');
Query OK, 1 row affected (0.08 sec)

Mysql> insert into zzm (name) values ('zeng ');
Query OK, 1 row affected (0.05 sec)

Mysql> insert into zzm (name) values ('ming ');
Query OK, 1 row affected (0.05 sec)

Mysql> select * from zzm;
+ ---- + ------- +
| Id | name |
+ ---- + ------- +
| 1 | zhang |
| 2 | zeng |
| 3 | ming |
+ ---- + ------- +
3 rows in set (0.00 sec)

-- Change the command terminator (because the default command Terminator -- semicolon (;) is often used in procedure (;)
-- Therefore, when creating a procedure, you need to define a new terminator to indicate that the command for creating a procedure ends)
-- Change the end symbol to the dollar sign -- $
Mysql> delimiter $

-- Create a stored procedure p3
-- The stored procedure is named p3, which contains two parameters,
-- One is of the input type (marked as IN). The parameter name is nameid and the type is int,
-- One is output type (marked as OUT), the parameter name is person_name, And the type is varchar (10)
-- This stored procedure is used to query all the contents of the zzm table and output the result set.
-- Query the field name whose ID is recorded in the table and output it to the second output type parameter.
-- No result set is output.
Mysql> create procedure p3 (IN nameid int, OUT person_name varchar (10 ))
-> Begin
-> Select * from test. zzm;
-> Select zzm. name into person_name from test. zzm where zzm. id = nameid;
-> End
-> $
Query OK, 0 rows affected (0.00 sec)

-- After creation, view all created stored procedures in the database.
Mysql> show procedure status $
+ ------ + ----------- + ---------------- + --------------------- + --------------- + --------- +
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment |
+ ------ + ----------- + ---------------- + --------------------- + --------------- + --------- +
| Test | p3 | PROCEDURE | root @ localhost | 16:40:21 | 16:40:21 | DEFINER |
+ ------ + ----------- + ---------------- + --------------------- + --------------- + --------- +
1 row in set (0.02 sec)

-- Call a stored procedure
Mysql> call p3 (3, @ name) $
+ ---- + ------- +
| Id | name |
+ ---- + ------- +
| 1 | zhang |
| 2 | zeng |
| 3 | ming |
+ ---- + ------- +
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Mysql> select @ name $
+ ------- +
| @ Name |
+ ------- +
| Ming |
+ ------- +
1 row in set (0.00 sec)

2. Example of a program that calls a stored procedure in a Java program

Import java. SQL. CallableStatement;
Import java. SQL. Connection;
Import java. SQL. DriverManager;
Import java. SQL. ResultSet;
Import java. SQL. Types;

Public class Main {

Public static void main (String [] args ){
/* Parameters for JDBC connection to the MySQL database */
String driverName = "com. mysql. jdbc. Driver ";
String userName = "root ";
String userPasswd = "nicegiving ";
String dbName = "test ";
String url = "jdbc: mysql: // localhost/" + dbName + "? User = "+ userName
+ "& Password =" + userPasswd;
Connection connection = null;
CallableStatement stmt = null;

Try {
// Load the database driver
Class. forName (driverName). newInstance ();
// Connect to the database
Connection = DriverManager. getConnection (url );
// Call the stored procedure, which has two parameters
Stmt = connection. prepareCall ("{call p3 (?,?)} ");
// The first parameter is input. Set the value of the first parameter here: set the first parameter to an integer of 3.
Stmt. setInt (1, 3 );
// The second parameter is output. Set the output type of the second parameter to VARCHAR.
Stmt. registerOutParameter (2, Types. VARCHAR );
// Execute the Stored Procedure
Boolean hadResults = stmt.exe cute ();

// If a query statement exists, the execution process returns the result set, which contains the following content:
System. out. println ("Data from table :");
While (hadResults ){
ResultSet rs = stmt. getResultSet ();
While (rs. next ()){
String id = rs. getString (1 );
String name = rs. getString (2 );
System. out. println ("ID =" + id + "/tName =" + name );
}
HadResults = stmt. getMoreResults ();
}

// Obtain the returned value of the stored procedure
System. out. println ("/nData from procedure :");
String name = stmt. getString (2); // obtain the second parameter, because the second parameter is of the output type.
System. out. println ("Name =" + name );
} Catch (Exception e ){
System. out. println (e. toString ());
} Finally {
Try {
Stmt. close ();
Connection. close ();
} Catch (Exception ex ){
System. out. println (ex. getMessage ());
}
}
}

}

-- Program execution result --
Data from table:
ID = 1 Name = zhang
ID = 2 Name = zeng
ID = 3 Name = ming

Data from procedure:
Name = ming

Iii. Summary

This article provides the creation of the most basic MySQL stored procedure and examples of calling the Java program. The purpose is to build a general understanding of the MySQL stored procedure and its usage in the program, I hope it will help you. Readers can refer to other professional books to discuss MySQL stored procedures in depth. I will also post the latest learning results in this area and discuss them with you.

This article permanently updates the link address: