Javabean incorporating sql queries.
Below i have my java code which is a java bean which is to be used to connect to an sql database, run a query, store the query results in a vector of vectors and then using my jsp page(code further down page) i wish to display the resulting table entries(the vector) within an html table.
Could anyone possibly take a look at the following 2 pieces of code, my JAVA and JSP and suggest what i need to do to achieve my objectives. Thanks in advance. Bert.
JAVA Bean code
Code:package myjavadocs; import java.sql.*; import java.util.Vector; public class sqlquery { String result = ""; String query = "select * from pix;"; String url = "jdbc:mysql://myjavasql."; String driver = "org.gjt.mm.mysql.Driver"; String uname = ""; String dpass = ""; //Step 1) Make a database connection private Connection dbconn = null; Vector mainvector = new Vector(); public Vector getResults(String query) { try { Class.forName(driver); dbconn = DriverManager.getConnection(url, uname, dpass); //Create an SQL statement Statement statement = dbconn.createStatement(); if (statement.execute(query)) { //Step 2) If we have a result lets loop through ResultSet results = statement.getResultSet(); ResultSetMetaData metadata = results.getMetaData(); //Validate result. Note switch to while loop if we plan on multiple results from query if(results != null) { //Use results setmetadata object to determine the columns int li_columns = metadata.getColumnCount(); Vector tmp = new Vector(); tmp.addElement(metadata.getColumnLabel(1)); tmp.addElement(metadata.getColumnLabel(2)); tmp.addElement(metadata.getColumnLabel(3)); tmp.addElement(metadata.getColumnLabel(4)); tmp.addElement(metadata.getColumnLabel(5)); tmp.addElement(metadata.getColumnLabel(6)); mainvector.addElement(tmp); //loop throught the columns and append data to our table while(results.next()) { tmp = new Vector(); tmp.addElement(results.getObject(1).toString()); tmp.addElement(results.getObject(2).toString()); tmp.addElement(results.getObject(3).toString()); tmp.addElement(results.getObject(4).toString()); tmp.addElement(results.getObject(5).toString()); tmp.addElement(results.getObject(6).toString()); mainvector.addElement(tmp); } } } } catch (ClassNotFoundException e) { result = "<tr><td> Error creating database drive class!"; result += " <br/>" + e.toString() + "</td></tr>"; } catch (SQLException e) { result = "<tr><td> Error processing the SQL!"; result += " <br/>" + e.toString() + "</td></tr>"; } finally { try { if (dbconn !=null) { dbconn.close();} } catch (SQLException e) { result = " <tr><td> Error in closing mainvector."; result += " <br/>" + e.toString() + "</td></tr>"; } } return mainvector; } public Vector getResults() { return mainvector; } }
JSP Code
Code:<%@page contentType="text/html"%> <%@page pageEncoding="UTF-8"%> <jsp:useBean id="sqlquery" scope="page" class="myjavadocs.sqlquery" /> <html> <body> <table border = "2"> <%=sqlquery.getResults()%> <table> </body> </html>
<moderator added code tags>
Reply With Quote