Hello-Java MySql存储过程

最新推荐文章于 2024-07-11 21:41:59 发布

weixin_34235135

最新推荐文章于 2024-07-11 21:41:59 发布

阅读量84

点赞数

文章标签：数据库 java

原文链接：https://my.oschina.net/wjgood/blog/30432

版权

--创建存储过程

create procedure ttt()

begin
select * from tb_user;

select * from tb_role;
end

--注释：其中存储过程将返回第一个查询结果，用作结果，也就是查询select * from tb_user;

--Java中调用

package com.weijun.util;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DB {

public Connection getCon(){
  Connection con = null;
  try{
  Class.forName("com.mysql.jdbc.Driver");
  con = DriverManager.getConnection("jdbc:mysql://localhost:3306/wj","root","mysql");
  }catch(SQLException e){
   e.printStackTrace();
  } catch (ClassNotFoundException e) {
   e.printStackTrace();
  }
  return con;
}

public void printPoJO(String sql){
  Connection con = this.getCon();
  Statement stmt = null;
  try {
   stmt = con.createStatement();

   CallableStatement cl = con.prepareCall("{call ttt()}");
   cl.execute();
   ResultSet rs = cl.getResultSet();
   while(rs.next()){
    System.out.println(rs.getString(3));
   }
  } catch (SQLException e) {
   e.printStackTrace();
  }finally{
   try {
    con.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
}

/**
* @param args
*/
public static void main(String[] args) {
  new DB().printPoJO("");
}

}

drop procedure if exists core_get_hotel;

delimiter $$
create procedure core_get_hotel() 
begin
DECLARE _hotelid INTEGER;  
DECLARE _cityid varchar(100);
DECLARE _HotelName varchar(100);
DECLARE _AreaID      varchar(100);
DECLARE _latitude    varchar(100);
DECLARE _Longitude   varchar(100);
DECLARE _address     varchar(3000);
DECLARE _rating      varchar(100);
DECLARE _description varchar(10000);


DECLARE done INT DEFAULT 0; 
declare cur_hotel cursor for 
select 
t1.HotelID as hotelid
,t1.CityID as cityid
,t1.HotelName as  hotelname
,t1.AreaID as areaid
,t1.Latitude as latitude
,t1.Longitude as longitude
,t1.Address as address
,t2.Rating as rating
from hotellist t1 left join hotelaward t2 
on t1.HotelID = t2.HotelID and t2.ProviderID = 0 limit 0,300; 

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;  


DROP TABLE IF EXISTS tmp_table;  
CREATE TEMPORARY TABLE tmp_table(
   hotelid INTEGER,
   cityid varchar(100),
   hotelname varchar(100),
   areaid varchar(100),
   latitude varchar(100),
   longitude varchar(100),
   address varchar(3000),
   rating varchar(100) default null,
   description varchar(10000)
)ENGINE=MEMORY DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT; 

OPEN cur_hotel;  
	emp_loop: LOOP  
	FETCH cur_hotel INTO _hotelid,_cityid,_hotelname,_areaid,_latitude,_longitude,_address,_rating;    
	IF done=1 THEN 
		LEAVE emp_loop;  
	END IF;
	#####################				  
	select group_concat(t1.Description) as description into _description from hoteltextitem as t1 where t1.HotelID = _hotelid;
	
	INSERT INTO tmp_table(hotelid,cityid,hotelname,areaid,latitude,longitude,address,rating,description) 
	VALUES(_hotelid,_cityid,_hotelname,_areaid,_latitude,_longitude,_address,_rating,_description); 
END LOOP emp_loop;  
CLOSE cur_hotel;  

select * from tmp_table;
end$$ 


delimiter ;
call core_get_hotel();

转载于:https://my.oschina.net/wjgood/blog/30432