MYSQL存储过程<二>:执行用字符串拼成的sql语句

为什么80%的码农都做不了架构师？>>>   

参考资料： http://blog.sina.com.cn/s/blog_6819fa800100ky2j.html

//存储过程

DELIMITER $$

DROP PROCEDURE IF EXISTS `fans`.`voteresultstat`$$

CREATE DEFINER=`fans`@`%` PROCEDURE `voteresultstat`(in _playerid varchar(100))

begin

set @varPlayerid = _playerid;/**此处必须用成员变量*/

set @selectSql = 'select * from voteplayer';

if _playerid <> '' then

set @selectSql = concat(@selectSql,' where voteplayer.playerid=?');

end if;

set @selectSql = concat(@selectSql,' order by voteplayer.createtime desc ');

prepare stmt from @selectSql;/**预编译prepare stmt from ... 执行字符串sql语句*/

if _playerid <> '' then

EXECUTE stmt using @varPlayerid; /**执行带有参数的sql*/

else

EXECUTE stmt; /**执行不带有参数的sql*/

end if;

end$$

DELIMITER ;

SQLyob调用:

            call voteresultstat('');

JAVA调用：

            try {

Connection conn=MySqlDataSourceFactory.getInstance().getConnection();

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

cs.setString(1,"12");

cs.execute();

ResultSet rs = cs.getResultSet();

int count=0;

while(rs.next()){

System.out.println(rs.getInt("votes"));

count++;

if(count==10){

break;

}

}

cs.close();

conn.close();

}

catch (SQLException e) {

e.printStackTrace();

}

转载于:https://my.oschina.net/bysag/blog/71867