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

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;    }