[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 660
  • Last Modified:

Assign value to form field from sql query

Hi, how can I assign a value to a form field from a sql query? Specifically I want to pass more information from the query to the processing page than the user needs to type in. Something like this:

<input type="hidden" value="???" />


0
Sparkle101
Asked:
Sparkle101
  • 6
  • 3
1 Solution
 
bloodredsunCommented:
assuming you have a resultSet from the query

<input type="hidden" value="<%=rs.getString("column_title")%>" />
0
 
Sparkle101Author Commented:
No I don't have a result set. Do I need to?
0
 
bloodredsunCommented:
e.g.
<%
//here I assume you get your connection

 String s = "" ;
    try {
        // Create a result set containing all data from my_table
        Statement stmt = connection.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM my_table");
   
        // Fetch each row from the result set
        while (rs.next()) {
            // Get the data from the row using the column index
            s = rs.getString(1);
             //or
            // Get the data from the row using the column name
            //s = rs.getString("col_string");
        }
    } catch (SQLException e) {
    }

%>

<form name="myForm">
<input type="hidden" value="<%= s %>" />
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
bloodredsunCommented:
>>Sparkle101

Yes, that's how you get data from a database in Java.

Here's an example using a javabean to take the connection code out of the page. Outputting the resultset info is very easy and you can just use the example above to do it
...........
ConnectionBean.java code // our javabean
<code>
package com.bloodredsun.beans ;
import java.sql.*;

public class Connectionbean{

private Connection conn ;
private Statement stmt ;
private static final String driver = "" ; // your db driver
private static final String dbURL= "" ; //your db url
private static final String login= "" ; // your db login
private static final String password= "" ; //your db password


  public ConnectionBean(){
    try{
      Class.forName( driver ) ;
      conn = DriverManager.getConnection(dbURL, login, password);
      stmt = conn.createStatement() ;

    }catch (Exception e){
      System.out.print("Whoops, buggered") ;
    }
  }//end ConnectionBbean constructor

      public ResultSet executeQuery ( String pSql){
      return stmt.executeQuery( pSql ) ;
      }
}//end ConnectionBean.java
</code>

Database.jsp code
<code>
<%@ page import="java.sql.*, com.bloodredsun.beans.*" %>
<jsp:useBean id="beanconnection" class="ConnectionBean" scope="request"/>
<html>
<body>
<%
String sql = "Select * from myTable" ;
ResultSet rs = beanconnection.executeQuery( sql) ;
%>
<%-- We now have the ResultSet, do what you will --%>
<%
while ( rs.next() ){
%>
A line of db stuff <%= rs.getString("column_name") %>

<%
}
%>
</body>
</html>
</code>

This is bare-arsed stuff, you will have to complete it with the normal extra methods in the bean for closing the connection, and other things like that and therer is minimal exception handling (in fact I've not tested this but written it straight out, but the theory is sound). What you can see is that Javabeans allow you to COMPONENTIZE code in that the code required for creating the db connection and statement were put in the JavaBean. This can then be dropped in as many pages as you like with out the need for repeatedly setting up the connection.

Once you have got used to JavaBeans in your JSP, I would recommend then doing it in a servlet. Then, once you've done that, have a look at custom tags and you'll see why JSTL is so popular (JSTL just being a set of standardised custom tags).

My next advice would be to look at dataSources and connection pools, this has a huge performance benefit for larger projects and also takes you into the world of JNDI (which isn't a frightening as it seems!).

Good luck and happy coding.
0
 
bloodredsunCommented:
If you want to use JSTL instead here's an example:

example.jsp
----------
<%@ taglib uri='http://java.sun.com/jstl/core' prefix='c' %>
<%@ taglib uri='http://java.sun.com/jstl/sql' prefix='sql' %>

<sql:setDataSource var="dataSrc" driver="oracle.jdbc.driver.OracleDriver" url="jdbc:oracle:thin:@localhost:1521:ORCL"  user="username"  password="password" />

Then query like so:

<sql:query var="queryResultsSet" dataSource="${dataSrc}"> <!-- here is the datasource from earlier -->
  select * from blog group by created desc limit ?
  <sql:param value="${6}"/>
</sql:query>


Iterate over the resultset by using the column names

<c:forEach var='row' items='${queryResultsSet}'>
      <c:out value='${row.ID}'/>, <c:out value='${row.FIRST_NAME}'/>, <c:out value='${row.LAST_NAME}'/>
</c:forEach>
0
 
Sparkle101Author Commented:
Yes, this is more like what I'm looking for. Although I have some experience with three tier architecture with ASP.NET and VB.NET I want to solve this particular task as quick and dirty as possible with jsp and JSTL. But, the syntax for the input tag will be?

<input type="hidden" value="${dataSrc.FIRST_NAME}">

or something?

0
 
bloodredsunCommented:
This is okay for JSTL 1.1, JSP 2.0 (tomcat 5 basically)
<input type="hidden" value="${dataSrc.row[0].FIRST_NAME}">


This is for JSTL 1.0, JSP 1.2
<c:forEach var='row' items='${queryResultsSet}'>
     <input type="hidden" value="<c:out value='${row.FIRST_NAME}'/>">
</c:forEach>
0
 
Sparkle101Author Commented:
Thanks bloodredsun, great help as always!
0
 
bloodredsunCommented:
thanks mate :-)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now