mysql存储过程实例（一）---------------图书管理系统部分查找

最新推荐文章于 2024-05-28 14:49:40 发布

小秋蜀黍

最新推荐文章于 2024-05-28 14:49:40 发布

阅读量2.3k

点赞数

分类专栏： mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/sinat_32133675/article/details/76092857

版权

mysql 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

mysql数据库框架如下：
这里写图片描述
authors表存放的是作者信息，titles表存放的是书籍的信息，titleauthor作为存放的是俩表中相关联的信息，即au_id title_id ，书本作者对应几本书。
现要实现一个功能：通过一个作者编号，查询出书的数量，其中合著和单著的数量。
思路：1.通过au_id 找title_id的数量
2.判断合著数量和单著数量（难点）
用java方法实现：

链接数据库代码

package it.com.getCon;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;



public class Conn {

    public Connection getCon(){
        Connection con =null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/pubs10";
            String user = "root";
            String pwd = "mysql";
            con=DriverManager.getConnection(url,user,pwd);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return con;
    }
    public void close(){
        Connection con = getCon();
        try {
            if(!con.isClosed()){
                con.close();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    public static void main(String[] args) {
        // TODO Auto-generated method stub

        Conn c = new Conn();
        c.getCon();
    }

}

javabean内容：

package it.com.Dao;

import it.com.getCon.Conn;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;



public class BookDao {

    //通用版本的查询
    public List<Map> selectAll(String sql){
        Conn c = new Conn();
        Connection conn = c.getCon();
        List<Map> list = new ArrayList<Map>();
        try {
            PreparedStatement pst=conn.prepareStatement(sql);
            ResultSetMetaData rm = pst.getMetaData();//获取查询的结构
            int m =rm.getColumnCount();//获取查询的总列数
            ResultSet rs = pst.executeQuery();
            while(rs.next()){
                Map map = new HashMap();
                for(int i=1;i<=m;i++){
                    String col=rm.getColumnName(i);

                    map.put(col,rs.getString(col));
                }
                list.add(map);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return list;
    }


    /*1、根据aid进行统计查找
     * 2、计算图书总数量
     * 3、得到每一个图书的信息
     */
    //根据au—id查作者出了多少本书
    public int titleById(String auid){
        String sql="SELECT * FROM titleauthor WHERE au_id='"+auid+"'";
        List<Map> list = selectAll(sql);
        int n = list.size();
        return n;
    }
    //根据au—id查作者出了多少本书
        public List<Map> titleById1(String auid){
            String sql="SELECT * FROM titleauthor WHERE au_id='"+auid+"'";
            List<Map> list = selectAll(sql);
            //int n = list.size();
            return list;
        }
    //查找每一本书是单著还是合著
        public int danAndhe(String tid){
            String sql = "SELECT COUNT(1) FROM titleauthor WHERE title_id='"+tid+"'";
            List<Map> list = selectAll(sql);
            Map map =list.get(0);
            int n =Integer.parseInt( map.get("COUNT(1)").toString());
            return n;
        }
    //查询某一个作者出版的图书情况：编号，总数，单著，合著
        public Map  selectDan(String aid){
            List<Map> list = null;
            list=titleById1(aid);//title_id的list集合
            int dan =0;
            int he =0;
            Map map = new HashMap();
            for(Map map1:list){
                String tid =map1.get("title_id").toString();
                int m =danAndhe(tid);
                if(m==1){
                    dan++;
                }else{
                    he++;
                }
            }
            map.put("bianhao", aid);
            map.put("zongshu", titleById(aid));

            map.put("danzhu", dan);
            map.put("hezhu", he);
            return map;
        }
    //查询所有作者出版的图书情况：编号，总数，单著，合著
        public List<Map> selectAll1(){

            String sql = "SELECT au_id FROM titleauthor ";
            List<Map > list = selectAll(sql);
            List<Map> list1 = new ArrayList<Map>();
            Map map = new HashMap();
            for(Map map1:list){
                String aid = map1.get("au_id").toString();
                map = selectDan(aid);
                list1.add(map);
            }
            return list1;
        }
        public static void main(String[] args){

            BookDao book = new BookDao();
            List<Map> list1 = book.selectAll1();
        for(Map map:list1){
            System.out.println(map);
          }
        }
}

结果显示：
这里写图片描述
由于在java中编写代码，隐蔽性不高。企业中一般采取存储过程编写；
mysql存储过程实现给定作者编号，查询出书数目，合著，单著数量如下：

DELIMITER $$
DROP PROCEDURE IF EXISTS pp1 $$
CREATE PROCEDURE `pubs10`.`pp1`(aid VARCHAR(20),ver INT)

    BEGIN
       DECLARE cnt INT DEFAULT 0;
       DECLARE dan INT DEFAULT 0;
       DECLARE he INT DEFAULT 0;
       DECLARE i INT DEFAULT 1;
       DECLARE tcnt INT DEFAULT 0;
       DECLARE tid VARCHAR(20);

       DROP TABLE linshi;
       IF ver=0 THEN
       DELETE FROM information;
       END IF;


       SET @a = 0;
       CREATE TABLE linshi SELECT @a:=@a+1 AS xh,title_id FROM titleauthor WHERE au_id=aid;
       SELECT COUNT(1) INTO cnt FROM linshi;

       WHILE i<=cnt DO
       SELECT title_id INTO tid FROM linshi WHERE xh=i;
       SELECT COUNT(1) INTO tcnt FROM titleauthor WHERE title_id=tid;
       IF tcnt=1 THEN
       SET dan=dan+1;
       ELSE
       SET he=he+1;
       END IF;
       SET i=i+1;
       END WHILE;
       INSERT INTO information VALUES (aid,cnt,dan,he);
       IF ver=0 THEN
       SELECT * FROM information;
       END IF;
    END$$

DELIMITER ;

运行结果测试：
这里写图片描述

查询所有作者出书数目信息存储过程：

DELIMITER $$
DROP PROCEDURE IF EXISTS pp2 $$
CREATE PROCEDURE `pubs10`.`pp2`()

    BEGIN
    DECLARE cnt INT DEFAULT 0;
    DECLARE i INT DEFAULT 1;
    DECLARE aid VARCHAR(20);

    DROP TABLE linshi_1;
    DELETE FROM information;

    SET @a=0;
    CREATE TABLE linshi_1 SELECT @a:=@a+1 AS xh,au_id FROM (SELECT DISTINCT au_id FROM titleauthor) a;
    SELECT COUNT(1) INTO cnt FROM linshi_1;
    WHILE i<=cnt DO
      SELECT au_id INTO aid FROM linshi_1 WHERE xh=i;
      CALL pp1(aid,1);
      SET i=i+1;
    END WHILE;
    SELECT * FROM information;
    END$$

DELIMITER ;

测试结果：
这里写图片描述

难点：在存储过程中，select的结果相当于return（直接跳出），当循环执行select，就会只显示第一次执行的结果

只需要在java中直接调用存储过程即可：

public List<Map> test(){
        Conn c = new Conn();
        Connection conn = c.getCon();
        List<Map> list = new ArrayList<Map>();
        String sql="{call pp2()}";
        try {
            CallableStatement pst=conn.prepareCall(sql);

            ResultSet rs = pst.executeQuery();
            while (rs.next()){
                Map map = new HashMap();
                map.put("aid",rs.getString("aid"));
                map.put("cnt",rs.getString("cnt"));
                map.put("dan",rs.getString("dan"));
                map.put("he",rs.getString("he"));
                list.add(map);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return list;
    }

小秋蜀黍

关注

0
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
mysql存储过程实例（一）---------------图书管理系统部分查找

mysql数据库框架如下： authors表存放的是作者信息，titles表存放的是书籍的信息，titleauthor作为存放的是俩表中相关联的信息，即au_id title_id ，书本作者对应几本书。现要实现一个功能：通过一个作者编号，查询出书的数量，其中合著和单著的数量。思路：1.通过au_id 找title_id的数量 2.判断合著数量和单著数量（难点）
复制链接

扫一扫