Java实现调用MySQL存储过程详解

发布于:2021-12-04 20:37:16

前言


存储过程(Stored Procedure)是存储在数据库中经过第一次编译后再次调用不需要再次编译,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。


Java调用MySQL的存储过程,需要用JDBC连接,环境eclipse


首先查看MySQL中的数据库的存储过程,接着编写代码调用


?


1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

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)


一、工程目录结构



二、Book.java


?


1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

package com.scd.book;

?

public class Book {

?private String name; //图书名称

?private double price; //价格

?private int bookCount; //数量

?private String 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;

?}

?

}


三、FindBook.java


?


1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

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 {

?/**

??* 获取数据库连接

??* @return Connection对象

??*/

?public Connection getConnection()

?{

??Connection conn = null; //数据库连接

??try

??{

???Class.forName("com.mysql.jdbc.Driver"); //加载数据库驱动,注册到驱动管理器

???/*数据库链接地址*/

???String url = "jdbc:mysql://localhost:3306/book?useUnicode=true&characterEncoding=UTF-8";

???String username = "root";

???String password = "123456";

???/*创建Connection链接*/

???conn = DriverManager.getConnection(url, username, password);

????

??}

??catch (ClassNotFoundException e){

????

???e.printStackTrace();

??} catch (SQLException e) {

???// TODO Auto-generated catch block

???e.printStackTrace();

??}

??return conn; //返回数据库连接

???

?}

?/**

??* 通过存储过程查询数据

??* @return List

??*/

?public List findAll()

?{

??List list = new ArrayList(); //实例化List对象

??Connection conn = getConnection(); //创建数据库连接

??try

??{

???//调用存储过程

???CallableStatement cs = conn.prepareCall("{call findAllBook()}");

???ResultSet rs = cs.executeQuery(); //执行查询操作,并获取结果集

???while(rs.next())

???{

????Book book = new Book(); //实例化Book对象

????book.setName(rs.getString("name")); //对name属性赋值

????book.setPrice(rs.getDouble("price")); //对price属性赋值

????book.setBookCount(rs.getInt("bookCount")); //对bookCount属性赋值

????book.setAuthor(rs.getString("author")); //对author属性赋值

????list.add(book);

???}

????

??}catch(Exception e)

??{

???e.printStackTrace();

??}?

??return list;? //返回list

?}

?/**

??* 主函数 调用存储过程(测试使用)

??* @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();

??}

?}

}


四、右键 Run As --> Java Application, 控制台输出



五、执行存储过程中的 sql语句


?


1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

mysql> select * from tb_books;

+------------------+-------+-----------+----------+

| name??? | price | bookCount | author |

+------------------+-------+-----------+----------+

| Java丛入门到精通 | 56.78 |? 13 | Mr. Sun |

| 数据结构?? | 67.3 |? 8962 | Mr. Sun |

| 编译原理?? | 78.66 |? 5767 | Mr. Sun |

| 数据结构?? | 67.42 |? 775 | Mr.Cheng |

+------------------+-------+-----------+----------+

rows in set (0.00 sec)

mysql> call findAllBook();

+------------------+-------+-----------+----------+

| name??? | price | bookCount | author |

+------------------+-------+-----------+----------+

| Java丛入门到精通 | 56.78 |? 13 | Mr. Sun |

| 数据结构?? | 67.3 |? 8962 | Mr. Sun |

| 编译原理?? | 78.66 |? 5767 | Mr. Sun |

| 数据结构?? | 67.42 |? 775 | Mr.Cheng |

+------------------+-------+-----------+----------+

rows in set (0.00 sec)


总结


以上就是Java调用MySQL存储过程的全部内容,希望本文的内容对大家的学*或者工作能带来一定的帮助,如果有疑问大家可以留言交流。

相关推荐

最新更新

猜你喜欢