• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 676
  • 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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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