The blog available
here<http://oop-edge.blogspot.com/2008/11/java-resultset-using-mysql-store...>
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<http://dev.mysql.com/downloads/connector/j/5.1.html>
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<http://oop-edge.blogspot.com/2008/11/java-resultset-using-mysql-store...>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
|
