JSP!!! 2

[garfield11]

Hi all,

I'm very new to JSP so while doing it I encountered problems and hope someone can help. First, I would like to know how can i connect to a database (Currently using Microsoft SQL Server 7.0)? Also, in JSP how to create a SQL query to select eg. name from a table called employee?

Thanks!

Love_Garfield

 

[meadandale]

First of all, I advise against embedding database connection code in a jsp. You should try and use jsp's for presentation and let a servlet do the grunt work (i.e. use the MVC pattern).

Connecting to a database from a jsp is the same as connecting from any other Java code. If you are unfamiliar with this I suggest you check out the JDBC tutorial on Sun's java site.

Finally, if you are new to SQL, I'd also suggest that you take a SQL tutorial. There are plenty available on the Internet.

 

[Sudmill]

Hopefully, this may help.

If you want to use the MSSQL, I suggest you download the JDBC driver for SQL 7/2000 from

http://www.microsoft.com.

It is easy to use with the online help.

You should ideally put your database access code in a java bean or servlet. You can of course put this database access code in a JSP page, but this will lead to pages that quickly become unmaintainable. Read up on Java Beans (note they are less complex than EJB's) because theyll make your life much easier by enabling you to reuse your code.

After downloading you shoud add 3 jar files to current
JVM's classpath(msbase.jar;mssqlserver.jar;msutil.jar)

The following is codesnippet:
....

String sDBDriver = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
String sConnStr = "jdbc:microsoft:sqlserver://yourSQLserverBox:1433";

Class.forName(sDBDriver);
Connection conn = DriverManager.getConnection(sConnStr,"YourUser","YourPassword"
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from employee"
...
Cheers

John (Sudmill)

 

[multiplex77]

Hi John,

I'm trying to do the same thing. Can you explain what I should do to :
"After downloading you shoud add 3 jar files to current
JVM's classpath(msbase.jar;mssqlserver.jar;msutil.jar)"?

I'm not sure how to access my current JVM's class path. Thanks for the help!

 

[sem]

In fact JSTL already contains tags for accessing database. You may download the latest version from java.sun.com. I suppose that for simple application this may be a simplest way, though for more complex ones I'd recommend to use MVC pattern.

Regards, Dima

 

[Sudmill]

Im not sure how you are running these JSP's, be they from Websphere or some other application server. I seem to remember that I added them to my Windows class path.

To access this you will either need to go into a DOS window and use the "set" command to add the full path and filenames of your three files onto the end of your class path (careful not to replace it!).

Let me know if this helps!

John.

Cheers

John (Sudmill)

 

[multiplex77]

Hi John

THanks again. I am running my JSPs using JBoss (with the Tomcat add-on).

I edited the &quot;C:\<JBossroot>\bin\run.bat&quot; file and inserted into the second line:
&quot;set CLASSPATH=c:\mssql_jdbc_driver\lib\mbase.jar;c:\mssql_jdbc_driver\lib\msutil.jar;c:\mssql_jdbc_driver\lib\mssqlserver.jar;&quot;

where C:\mssql_jdbc_driver is the folder where my *.jar files are located.

This is what my .jsp page looks like:
-----Start test.jsp -----------
<%
try {
String sDBDriver = &quot;com.microsoft.jdbc.sqlserver.SQLServerDriver&quot;;
String sConnStr = &quot;jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=multiplex;User=sa;Password=secret;&quot;;

Class.forName(sDBDriver);
java.sql.Connection conn = java.sql.DriverManager.getConnection(sConnStr);
java.sql.Statement stmt = conn.createStatement();
java.sql.ResultSet rs = stmt.executeQuery(&quot;select 'Multiplex' As Name&quot;
while (rs.next()) { %>
<%= rs.getString(&quot;Name&quot %><br>
<%}
}
catch (ClassNotFoundException cnfe)
{
System.err.println(&quot;Unable to load MSSQL database driver!&quot;
System.err.println(&quot;ClassNotFoundException: &quot; + cnfe.getMessage());
}
%>
------End test.jsp -----------------------

But then when I run test.jsp, I get a ClassNotFoundException saying its not able to load the MSSQL driver.

Thanks for the help!

 

[meadandale]

Just stick your jars in the WEB-INF/lib folder of your webapp. The server will find them automatically.

 

[multiplex77]

Hooray!! It works! Thanks meandandale, what you instructed worked! This has been a struggle since last week. THANKS!!! A star for you!

I could remove the CLASSPATH statement and it still worked.

 

[meadandale]