mysql的动态sql应用

运行环境：mysql 5.x,选择5.0及以上版本
/*----------------储存过程1，取出数据库中所有表信息-------------------------*/

/*author:henry
  procedure name:sys_tables
  create_date:20091023
*/
create procedure sys_tables(in databasename varchar(20))
begin 
/*declare tmp_id int;*/ 
/*create temporary #table_field(id,fieldname)*/ 
drop table if exists tmp_tables; 
create temporary table  tmp_tables 
( 
`id` int not null, 
`tablename` varchar(40) not null, 
`tabletype` varchar(40) not null 
); 
set @rowno:=0; 
set @str=concat("insert tmp_tables(id,tablename,tabletype) select @rowno:=@rowno+1, table_name,table_type from information_schema.tables where table_schema='",databasename); 
set @str=concat(@str,"'"); 
prepare stmt1 from @str; 
execute stmt1; 
deallocate prepare stmt1; 
/*select @tmp_id:=min(id) from tmp_tables; 
while @tmp_id is not null do 

select @tmp_table_name:=tablename from tmp_tables where id=@tmp_id; 
call sys_tables_fmt(@tmp_table_name); 
delete from tmp_tables where id=@tmp_id; 
select @tmp_id:=min(id) from tmp_tables; 
end while;*/ 
select * from tmp_tables; 
drop temporary table tmp_tables; 
end

/*----------------储存过程2，取出对应表信息-------------------------*/

/*author:henry
  procedure name:sys_tables
  create_date:20091023
*/
create procedure sys_tables_fmt(in tablename varchar(20))
begin
/*set @str=concat("select column_name from ",tablename)*/
/*创建临时表#table_field(id,fieldname)*/
drop table if exists table_field;
create temporary table  table_field
(
	`id` int not null,
	`fieldname` varchar(40) not null,
	`datatype` varchar(20) not null
);
set @rowno=0;
set @str=concat("insert table_field(id,fieldname,datatype) select ordinal_position as rowno,column_name,data_type from information_schema.columns where table_schema='wobuild' and table_name='",tablename);
set @str=concat(@str,"'");
prepare stmt1 from @str;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

select * from table_field;
drop temporary table table_field;

end

/*--------------------用jdbc中进行的调用-------------------*/
由于在测试的时候无法肯定多个结果集的取值，所以在此分两个储存过程来处理了：
java后台代码,主要参数为schema_obj="testbase";：当然也可作为动态参数传入；

package com.woBuild.dataBaseDao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class TestDao {
	public static ArrayList selectDown() {
        ArrayList lise = new ArrayList();
        Connection con = null;
        //PreparedStatement s = null;
        ResultSet rs_par = null;
        ResultSet rs_chi = null;
        CallableStatement cstmpar=null;
        CallableStatement cstmchi=null;
        con = DataBaseConnection.createConnection();
        String rel="";
        String bean_obj="";
        String schema_obj="testbase";
        try {
        	String PROC_EXEC_SQL_PAR="{CALL sys_tables(?)}" ;
        	cstmpar = con.prepareCall(PROC_EXEC_SQL_PAR); 
        	cstmpar.setString(1,schema_obj); 
        	rs_par = cstmpar.executeQuery();
        	while(rs_par.next()){
        	      String tablename=rs_par.getString("tablename").trim();
        	      bean_obj=tablename;
        	      String tabletype=rs_par.getString("tabletype").trim();
        	      if(tabletype.equals("BASE TABLE")){
        	    	  String PROC_EXEC_SQL_CHI="{CALL sys_tables_fmt(?)}" ;
        	    	  cstmchi = con.prepareCall(PROC_EXEC_SQL_CHI); 
        	    	  cstmchi.setString(1,bean_obj); 
        	          rs_chi = cstmchi.executeQuery();
        	          while(rs_chi.next()){ 
                	      String datatype=rs_chi.getString("datatype").trim();
                	      String fieldname=rs_chi.getString("fieldname").trim();
                	      if(datatype.equals("varchar")||datatype.equals("char")||datatype.equals("datetime")){
                	    	  rel+="String "+fieldname+"=rs.getString(\""+fieldname+"\");\n";
                	    	  
                	      }
                	      else if(datatype.equals("int")){
                	    	  rel+="Integer "+fieldname+"=rs.getInt(\""+fieldname+"\");\n"; 
                	      }  
        	          	} 
        	          rs_chi.beforeFirst();
        	          while(rs_chi.next()){
              	      	 String fieldname=rs_chi.getString("fieldname").trim();
              	      	 String fieldnameTmp=fieldname.substring(0, 1).toUpperCase()+fieldname.substring(1, fieldname.length());
              	      	 rel+=bean_obj+".set"+fieldnameTmp+"("+fieldname+")\n";
                	   }
                	
        	          System.out.println(rel);
        	          rel="";
        	      }
        	      
        	} 
        } catch (SQLException ex1) {
            ex1.printStackTrace();
        } finally {
        	try {
        		cstmpar.close();
        		cstmchi.close();
        		rs_par.close();
        		rs_chi.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
        	
        }
     return lise;
    }
	
	public static void main(String[] args){
		List li=TestDao.selectDown();
		}
}

/*------------------数据库连接就不写出来了-----------------------*/
执行出来的结果，就是生成对应数据库的setXXX,及getXXX，可以借此进行扩展发挥，不同的数据库java代码不用动，只需更改相应的储存过程就行了，我们可以借此扩展自动生成我们想要的bean及相应的save（），query（），update（），这里就不在赘述了.

侃一下：俺不喜欢hibernate，所以请不喜欢hibernate的人也顶下