java resultset using mysql stored procedure

[Pradyut Bhattacharya]

The blog available here

Obtaining a java sql resultset using mysql stored procedure is quite a headache....

any better implementation is always a welcome....

I have used "MySQL Connector/J" jdbc driver available here

I have used java.sql.CallableStatement in the java class.


The Table in mysql is: -
-----------------------------------------------------------------------
DROP TABLE IF EXISTS `test_post`;
CREATE TABLE `test_post` (
`Post_Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`Subject` varchar(50) NOT NULL,
`Post_msg` text NOT NULL,
PRIMARY KEY (`Post_Id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-----------------------------------------------------------------------

The stored procedure is: -
-----------------------------------------------------------------------
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_select_test_post`(in id integer)
BEGIN
SELECT * FROM test_post where post_id=id;
END
-----------------------------------------------------------------------

The java code: -
-----------------------------------------------------------------------
public class Main {

/**
* @param args the command line arguments
*/
public static void main(String[] args) {
// TODO code application logic here
//System.out.print();
try
{
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/void?" + "user=myUsername&password=myPassword");
CallableStatement stmt = conn.prepareCall(" call sp_select_test_post(?) ");
stmt.setInt(1, 2);
stmt.execute();
ResultSet rs = stmt.getResultSet();
/*ResultSetMetaData rmeta = rs.getMetaData();
System.out.print(rmeta.getColumnCount());*/
while(rs.next()) {
System.out.print(rs.getString(2) + "\n");
}
System.out.print("connected");
conn.close();
}
catch (ClassNotFoundException cnfe) {
System.out.print(cnfe);
}
catch(InstantiationException ie) {
System.out.print(ie);
}
catch(IllegalAccessException iae) {
System.out.print(iae);
}
catch(SQLException sqle) {
System.out.print(sqle);
}
}
}
-----------------------------------------------------------------------

Hope that runs in your systems too... Good Luck..
Please post any better implementation ... thanks...
Cheers!!!

Pradyut Kumar Bhattacharya
http://pradyut.tk
http://oop-edge.spaces.msn.com/
http://pradyutb.blogspot.com/
http://praddy-photos.blogspot.com/

New Delhi, India

--
Posted By Pradyut to oop-praddy at 11/29/2008 08:42:00 PM

--
Pradyut
http://pradyut.tk
http://oop-edge.blogspot.com/
http://pradyutb.blogspot.com/
http://praddy-photos.blogspot.com/
http://oop-edge.spaces.live.com/
http://www.flickr.com/photos/praddy
http://groups.google.com/group/oop_programming
India

[Pradyut Bhattacharya]

The files....