Scenarios :
Note : for one table only..............
1 @ Scenario : Updating Specific Rows based on Where Condition and several fields.
'update tablename(dasari) set columnname= ? where column_name in (?) and column_name in (?) and column_name =curdate() and column_name is null limit ? ';
2 @ Scenario : For Retrieving all the Rows Based on Specific Column_name :
' select * from tablename(dasari) where column_name=?';
Below is the Example Program for Employee Information : Passing only IN Parameters
Step -1 : Create Table Name with following fields.
CREATE TABLE `alerts` (
`id` bigint(20) NOT NULL auto_increment,
`inserted_on` date default NULL,
`msisdn` varchar(50) default NULL,
`op_id` smallint(6) default NULL,
`spid` int(11) default NULL,
`flag` bigint(10) unsigned default NULL,
PRIMARY KEY (`id`),
KEY `idx_insertedon` (`inserted_on`),
KEY `idx_spid` (`op_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Step -2 Create a procedure
DELIMITER $$
DROP PROCEDURE IF EXISTS `databasename`.`dasari_procedure`$$
CREATE DEFINER=`username`@`localhost` PROCEDURE `dasari_procedure
`(in strSpids varchar(512),in strOpid varchar(512),
in nLimit int, in nFlag bigint)
BEGIN
declare nLimit1 int(10);
declare nFlag1 bigint;
declare strOpid1 varchar(512);
declare strSpids1 varchar(512);
if (nLimit <= 0) then
set @nLimit1 = 100;
else
set @nLimit1 = nLimit;
end if;
set @nFlag1 = nFlag;
set @strOpid1 = strOpid;
set @strSpids1 = strSpids;
start transaction;
prepare stmt from 'update alerts set flag = ? where op_id in (?) and spid in (?) and inserted_on = curdate() and flag is null limit ?';
execute stmt using @nFlag1,@strOpid1,@strSpids1,@nLimit1;
deallocate prepare stmt;
commit;
prepare stmt from 'select * from alerts where flag = ?';
execute stmt using @nFlag1;
deallocate prepare stmt;
END$$
DELIMITER ;
Step -3 Calling Stored Procdeure
String sqlQuery ="{call dasari_procedure(?,?,?,?)}";
CallableStatment cstmt=con.prepareCall(sqlQuery);
cstmt.setString(1,1);
cstmt.setString(1,1);
cstmt.setInt(3,100);
cstmt.setInt(4,12121212);
cstmt.executeUpdate(); // flag will be update instead of null replace with 12121212 based on where flag is null in stored procedure.
// Retrieval of Resultset in same Stored Procedure.. after updating the records
ResultSet rs=cstmt.getResultSet();
while(rs.next())
{
String name=rs.getString("column_name');
------------------------------------------- etc,,,,,,,,,
}
Happy Learning !!!
Cheers
Dasari
