how to connect to database and display query vqlus using proper java/jsp

hello there,

dont know if i post this question in the right place but can anyone help me with this:

i am new to jsp and with months of coding i found out that i am doing it the WRONG WAY

and since i dont want to continue doing like that i decided to fix it up.

i have a jsp page here where in everything was just all mixed up (eg : the design and functions)

<%@ page import="java.sql.*" 
import="java.text.*"   
import="java.math.*"   
%><head>
<script language="javascript">
function editRecord(id){
    var f=document.form;
    f.method="post";
    //f.action='Jgeneral.jsp?id='+id;
   
    f.submit();

}
function checkAll(field)
{
for (i = 0; i < field.length; i++)
	field[i].checked = true ;
}

function uncheckAll(field)
{
for (i = 0; i < field.length; i++)
	field[i].checked = false ;
}
</script>
<SCRIPT LANGUAGE="JavaScript">
<!-- Begin  CHECK ALL/ UNCHECK ALL 
function checkAll(field)
{
for (i = 0; i < field.length; i++)
	field[i].checked = true ;
}

function uncheckAll(field)
{
for (i = 0; i < field.length; i++)
	field[i].checked = false ;
}
//  End -->
</script>

function submitFirstForm() {
        document.dropdown.action='ValidationUpdate.jsp'
        document.dropdown.method = 'post'
        document.dropdown.submit();
}
</script>

<br>
<form name="dropdown" action ="ValidationFilter.jsp">
		<%  String url = "jdbc:jtds:sqlserver://tmp-sql-02/";
		Connection conn=null;
		String id= "sa";
		String pass = "jughead";
		String classPath = "net.sourceforge.jtds.jdbc.Driver";
	
		%>

<%@ page import="java.sql.*" %>
		<%@ page import="java.io.*" %>
		<%
		try{
		Class.forName(classPath);
		conn = java.sql.DriverManager.getConnection(url, id, pass);
		}catch(Exception exc){
		out.println(exc.toString());
		}
		%>
		<%
		Statement stm=conn.createStatement();
		ResultSet getDealer= stm.executeQuery("select DISTINCT left([wtbcnd],len([wtbcnd])-8) as Dealer From swmwtym ORDER BY left([wtbcnd],len([wtbcnd])-8) "); 
	
		%>
		<center>
		  <table border="0" width="907" cellspacing="2" cellpadding="4">
            <tr>
              <td width="7%"><b>Dealer:</b></td>
            <td><select name="DealerSelect" id="drp_dwn">
                  <% while(getDealer.next()) { %>
                  <option> <%=getDealer.getString(1)%> </option>
                  
                  <% }
                  String Dealer2= request.getParameter("DealerSelect");
                  out.print(Dealer2);
                  %>
                </select>
                  <%
	//	getDealer.close();
	//	stm.close();
	//	conn.close();
		/*-----------------------------------------------------*/
		%></td>
            </tr>
            <tr>
              <td width="7%"><b>Batch:</b></td>
              <td width="14%"><input type="text" name="bat" size="20"/>
              </td>
              <td width="79%"><input type="submit" style="background-color:gray;font-weight:bold;color:white;" value="Filter" name="Filter" >
              </td>
            </tr>
          </table>
		</center>	
		
</form>

<center>
<table width="910" border="1" bordercolor="#FFFFFF">
  <tr>
    <td width="62"><input type="button" name="submit" style="background-color:gray;font-weight:bold;color:white;" value="Update"  onClick="submitFirstForm()"></td>
    <td width="74"><input type="button" style="background-color:gray;font-weight:bold;color:white;" name="CheckAll" value="Check All"
onClick="checkAll(document.myform.checkbox)"></td>
    <td width="89"><input type="button" style="background-color:gray;font-weight:bold;color:white;" name="UnCheckAll" value="Uncheck All"
onClick="uncheckAll(document.myform.checkbox)"></td>
    <td width="657"><input type="submit" style="background-color:gray;font-weight:bold;color:white;" value ="Validate"></td>
  </tr>
</table>
</center>

<!--  <table border="1" id="results" cellpadding="1" cellspacing="0" bordercolor="#FFFFFF" 
 style="width: 700px; height: 105px;"><tr valign="top">

<td bgcolor="#CC6666" width="100" class="style4"><p align="left"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#FFFFFF">ClaimNo</font></b></p> </td>

<td bgcolor="#CC6666" width="100" class="style5"> 
   <p align="left"><b> 
	<font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#FFFFFF">RepairNo</font></b></p> </td>
 
  <td bgcolor="#CC6666" width="90" class="style5"> 
   <p align="left"><b> 
	<font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#FFFFFF">ClaimDate</font></b></p> </td>
	
	<td bgcolor="#CC6666" width="90" class="style5"> 
   <p align="left"><b> 
	<font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#FFFFFF">PartNo</font></b></p> </td>
 
 <td bgcolor="#CC6666" width="90" class="style5"> 
   <p align="left"><b> 
	<font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#FFFFFF">OPRNO</font></b></p></td>
 
 <td bgcolor="#CC6666" width="90" class="style5"> 
   <p align="left"><b> 
	<font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#FFFFFF">Status</font></b></p> </td>
 
 <td bgcolor="#CC6666" width="90" class="style5"> 
   <p align="left"><b> 
	<font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#FFFFFF">Parts location</font></b></p> </td>
 
 <td bgcolor="#CC6666" width="50" class="style2"> 
   <p align="center"><b> 
    <font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#FFFFFF">Condition</font></b></p> </td>
 
 <td bgcolor="#CC6666" width="90" class="style2"> 
   <p align="center"><b>
    <font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#FFFFFF">Cause</font></b></p> </td>
 
 <td bgcolor="#CC6666" width="50" class="style2"> 
   <p align="center"><b> 
    <font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#FFFFFF">Remedy</font></b></p> </td> -->

<%
/* CONNECTION FOR TABLES */		
	
		   
		   java.sql.Connection con;
		   java.sql.Statement s;
		   java.sql.ResultSet rs;
		   java.sql.PreparedStatement pst;
		   
		 	
		   try {
			    //String url= "jdbc:jtds:sqlserver://tmp-sql-02/";
			    // String id= "";
			    // String pass = ""; 
			   con=null;
     		   s=null;
     		   pst=null;
     		   rs=null;
     		  
   		   
              Class.forName("net.sourceforge.jtds.jdbc.Driver");
              con = java.sql.DriverManager.getConnection(url, id, pass);
               
              String sql =("SELECT DISTINCT * "+
            		  "FROM SWTCLM1 "+
            		  "WHERE NOT C1CLNO IN (SELECT WTCLNO FROM SWMWTYM WHERE WTCLNO IN (SELECT DISTINCT C1CLNO FROM SWTCLM1)) "+
            		  "AND NOT C1CLNO IN (SELECT RPCLNO FROM SWMREPP WHERE RPCLNO IN (SELECT DISTINCT C1CLNO FROM SWTCLM1)) "+
            		  "AND NOT C1CLNO IN (SELECT OPCLNO FROM SWMOPRN WHERE OPCLNO IN (SELECT DISTINCT C1CLNO FROM SWTCLM1)) "+ 
            		  "AND NOT C1CLNO IN (SELECT CTCLNO FROM SWMCTXT WHERE CTCLNO IN (SELECT DISTINCT C1CLNO FROM SWTCLM1)) "+
            		  "AND NOT C1CLNO IN (SELECT SBCLNO FROM SWMSBLT WHERE SBCLNO IN (SELECT DISTINCT C1CLNO FROM SWTCLM1)) "+
            		  "AND NOT C1CLNO IN (SELECT JRCLNO FROM SWMJRSN WHERE JRCLNO IN (SELECT DISTINCT C1CLNO FROM SWTCLM1))");										
                										
              s = con.createStatement();
              rs = s.executeQuery(sql);
              int i=0;

while(rs.next()){
	   
	   
	   
	 //CONDITIONS
	 /* if ()
	 {
		 
	 } */
	 
	 
	 String ClaimNo = rs.getString ("");
	 
	%>
<%-- <tr  valign="top">

</html>

what i want to do now is to do it properly and that is to
separate the design into the connections and functionalities.
but i dont really know how to start with it.

the class thing i supposed? (please correct me if im wrong)
looking forward for your help and advices. thank you so much. >.<

God Bless you

I recommend novices to JSP read the following articles:

The Secret Life of JSPs

The Front Man

The first will make sure that you understand what JSP is, and how it operates.

The second will answer your question regarding proper web application structure.

And above all, remember to keep Java code out of the JSP.

hello Bear Bibeault, :)

i really appreciate the articles you have shown me.
and it did help me to understand some things about jsp .
and its great.

but the thing is, i still dont know how to get started with
the question i ask a while ago.

how to connect to database and display query values using proper java/jsp?

and now i was eager more to learn how .

super thanks.

God Bless you.

Hi khamhielle,
You need to separate the presentation from business logic.. Here is a simple how
You need a servlet (say DisplayController.java)
You need a JSP (MyView.jsp)
Then..through your web browser.. you should call the servlet (assuming you have configured the servlet in web.xml)
In the servlet, you will retrieve the contents from database and put the contents in a list or map or any data structure of your choice. Then you need to get that variable ready to be accessible by UI. That means you should send the variable by adding it at as an attribute to the request.

Then pass on the view to JSP. You can do that by using the RequestDispatcher

In JSP you should use your scriptlets/JSTL to just iterate over the results variable that is retreived from attributes which is a list or or map and display them

Hope its clear.. Can ask if its not!