jsp连结sqlserver2008数据库

  最近在学习java，连数据库的时候很郁闷，main方法连结正确，jsp一调用就错误，后台发现sqljdbc4.jar这个jar 在main中和在jsp中 是不一样的。

在main中只需右键项目build path 添加进sqljdbc4.jar 就好了，而在jsp中 你需要把sqljdbc4.jar copy到WebRoot/Web-inf/lib中 

添加完以后，系统会自动帮你处理，2边都可以使用。 

1：DBHelper类：

package news2;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class dbHelper {
	private static final String DRIVER_CLASS ="com.microsoft.sqlserver.jdbc.SQLServerDriver";
	private static final String DATABASE_URL = "jdbc:sqlserver://localhost:1433;databasename=news";
	
	private static final String DATABASE_USER ="sa";
	private static final String DATABASE_PASSWORD = "123456";
	
	public static Connection getConnection(){
		Connection dbConnection = null;
		try{
			Class.forName(DRIVER_CLASS);
			dbConnection = DriverManager.getConnection(DATABASE_URL,DATABASE_USER,DATABASE_PASSWORD);
		}
		catch(Exception e){
			e.printStackTrace();
		}
		return dbConnection;
		
	}
	
	public static void closeConnection(Connection dbConnection){
		try{
			if(dbConnection != null && (!dbConnection.isClosed())){
				dbConnection.close();
			}
		}
		catch(SQLException sqlE){
			sqlE.printStackTrace();
		}
	}
	
	public static void closeResultSet(ResultSet res){
		try{
			if(res != null){
				res.close();
				res = null;
			}
		}
		catch(SQLException e){
			e.printStackTrace();
		}
	}
	
	public static void closeStatement(Statement stat){
		try{
		      if(stat != null){
			     stat.close();
				 stat = null;
			  }
		   }
		   catch(SQLException e){
			  e.printStackTrace();
		   }
}
}

2：调用DBHelper类

package news2;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


public class Control {

	public List<newsModel> getAllFirstLevelTitleList2(){
		List<newsModel> list =new ArrayList<newsModel>();
		Connection dbConnection = null;
		PreparedStatement pStatement = null;
		ResultSet res = null;
		try{
			dbConnection = dbHelper.getConnection();
			String sql = " select * from FirstLevelTitle order by Createtime desc ";
			pStatement  = dbConnection.prepareStatement(sql);
			res = pStatement.executeQuery();
			while(res.next()){
				int id = res.getInt("id");
				String Title = res.getString("TitleName");
				String creator = res.getString("Creator");
				Date time = res.getDate("CreateTime");
				newsModel fTitle = new newsModel(id, Title, creator, time);
				list.add(fTitle);
			}
		}catch(SQLException sqlE){
			sqlE.printStackTrace();
		}finally{
			dbHelper.closeResultSet(res);
			dbHelper.closeStatement(pStatement);
			dbHelper.closeConnection(dbConnection);
		}
		return list;
	}
}

3：Model类

package news2;

import java.util.Date;

public class newsModel {
	private int id;
	private String titleName;
	private String creator;  //创建者
	private Date createTime;
	
	public newsModel(int id,String titleName,String creator,Date createTime)
	{
		this.id = id;
		this.titleName = titleName;
		this.creator = creator;
		this.createTime = createTime;
	}
	
	
	public int getId(){
		return this.id;
	}
	
	public String getTitleName(){
		return titleName;
	}
	
	
	public String getCreator(){
		return creator;
	}
	

	public Date getCreateTime(){
		return createTime;
	}
	
	
	
	public void setId(int par){
		this.id = par;
	}
	
	
	
	public void setTitleName(String par){
		this.titleName = par;
	}
	
	
	
	public void setCreator(String par){
		this.creator = par;
	}
	
	
	
	public void setCreateTime(Date par){
		this.createTime = par;
	}
}

3：直接测试看结果，在mian函数中

package news2;

import java.util.*;

public class demo {
	public static void main(String[] args){
		Control c = new Control();
		List<newsModel> list = c.getAllFirstLevelTitleList2();
		System.out.println(list.size());
	}
}

4：直接测试看结果，在 jsp页面中

<%@page import="news2.newsModel"%>
<%@page import="news2.Control"%>
<%@ page language="java" import="java.util.*" pageEncoding="ISO-8859-1"%>

<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>My JSP 'index.jsp' starting page</title>
	<meta http-equiv="pragma" content="no-cache">
	<meta http-equiv="cache-control" content="no-cache">
	<meta http-equiv="expires" content="0">    
	<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
	<meta http-equiv="description" content="This is my page">
	<!--
	<link rel="stylesheet" type="text/css" href="styles.css">
	-->
  </head>
  
  <body>
    This is my JSP page. <br>
    <% Control c = new Control();
    	List<newsModel> list = c.getAllFirstLevelTitleList2();
     %>
     <%=list.size() %>
  </body>
</html>