Win a copy of Learn Spring Security (video course) this week in the Spring forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JSP MSSQL - Connection Problem

 
Shi Hao Wey
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi, guys!

Recently I'm trying to connection MS SQL Server 2005 from JSP, and I face a problem:
When I declare my connection in declaraction tag, the server will display ClassNotFoundException. But if I put my connection in script tag, it is working fine.

eg:
<%!
Driver driver = null;
Connection conn=null;
PreparedStatement = null;
ResultSet rs=null;

private Connection getConnect(){
if(driver==null){
driver = (Driver)Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
}
Connection cnn = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;instanceName=instance1;databaseName=testDB;integratedSecurity=true","username","password");
return cnn;
}

private void displayData(){
conn=getConnect();
pstmt = conn.prepareStatement("SELECT * FROM users");
rs=pstmt.executeQuery();
while(rs.next()){
out.println(rs.getString(1));
}
rs.close();
pstmt.close();
conn.close();
}

%>
I used this method to do the connection for oracle and mysql, both are working fine. But when I connect to ms sql, it doesn't work. I have downloaded the latest sqljdbc4.jar, put it in tomcat/lib folder, and restart the tomcat.

However, when I tried to put the connection in jsp script tag, it works.
<%
Driver driver = null;
Connection conn=null;
PreparedStatement = null;
ResultSet rs=null;

if(driver==null){
driver = (Driver)Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
}
conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;instanceName=instance1;databaseName=testDB;integratedSecurity=true","username","password");


pstmt = conn.prepareStatement("SELECT * FROM users");
rs=pstmt.executeQuery();
while(rs.next()){
out.println(rs.getString(1));
}
rs.close();
pstmt.close();
conn.close();

%>

Anyone know what is the problem?? If put the connection in the script tag, it is quite troublesome for me as I have a few jsp pages need to connect to database. Or there is another way to do it?

Thank you...
 
Scott Selikoff
author
Saloon Keeper
Posts: 4007
18
Eclipse IDE Flex Google Web Toolkit
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Not on topic I know, but JDBC inside of JSP is really bad, especially since you mention you have multiple pages that need to use the database. You should put it in a reusable class file.

As for your issue "<%!" is reserved for class declarations, not body code, therefore "<%" should be used.

 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic