/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package db;

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

/**
 *
 * @author Vlad
 */
public class Connect2DB
  {
    private String ServerName;
    private String DBName;
    private String Login;
    private String Pwd;
    private String ConUrl;
    private String ConErr;
    private Boolean Connected;
    private Connection con;

    //constructors
    public Connect2DB()
      {
        this.ServerName="";
        this.DBName    ="";
        this.Login     ="";
        this.Pwd       ="";
        this.ConUrl    ="";
        this.ConErr    ="";
        this.Connected =false;
        this.con = null;
      }

    public Connect2DB(String ServerName, String DBName, String Login, String Pwd)
      {
        this.ServerName = ServerName;
        this.DBName = DBName;
        this.Login = Login;
        this.Pwd = Pwd;
        this.ConUrl="jdbc:sqlserver://"+ServerName+"\\"+DBName+":1433;databaseName="+DBName+"";                
        this.Connected =false;
        this.con = null;
      }

    //methods, etc
    public void setConUrl(String ServerName, String DBName)
      {
        this.ConUrl ="jdbc:sqlserver://"+ServerName+"\\"+DBName+":1433;databaseName="+DBName+"";         
      }

    public String getConUrl()
      {
        return ConUrl;
      }

    public String getConErr()
      {
        return ConErr;
      }

    public Boolean getConnected()
      {
        return Connected;
      }

    public Connection getCon()
      {
        return con;
      }

    public String getDBName()
      {
        return DBName;
      }
    
            
    //connect 2 DB
    public void Connect()
      {  
        if (Connected) {return;}        
        if (con!=null) {return;}        
          try
          {
            DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());                         
              try
              {
                con = DriverManager.getConnection(this.ConUrl, this.Login, this.Pwd);                    
                this.Connected=true;                
              } catch (Exception e)
              {                   
                 this.ConErr="Ошибка подключения к SQL серверу: "+e.getMessage();
                 this.Connected=false;
              }//conection                               
          } catch (Exception e)
          {
            this.ConErr="Ошибка загрузки драйвера БД: "+e.getMessage();
            this.Connected=false;            
          }//driver manager        
      }//connect   
    
    
    //close connection
    public void Disconnect()
      {
        if (!Connected) {return;}        
        if (con==null)  {return;}
          try
          {
            con.close();              
          } catch (Exception e)
          {
             this.ConErr="Ошибка отключения от SQL сервера: "+e.getMessage();             
          }
       this.Connected=false;             
      }//disconnect
        
  }

package listtrud;

import db.Connect2DB;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;
import java.util.Calendar;
import java.util.Formatter;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 *
 * @author Vlad
 */
public class ListTrud extends HttpServlet
  {   
    
    private String ServerName="";
    private String DBName    ="";
    private String UserName  ="";
    private String Pwd       ="";
    private String backurl   ="";
    private String detailurl ="";
    
    
    private GetListTrud glt;       

    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException
      {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();                                                   
        
        //получаем параметры сервлета
        try
          {                       
           ServletContext sc = getServletContext();
           
           this.ServerName=sc.getInitParameter("ServerName");
           this.DBName    =sc.getInitParameter("DBName");
           this.UserName  =sc.getInitParameter("UserName");
           this.Pwd       =sc.getInitParameter("Pwd");
           this.backurl   =sc.getInitParameter("backurl");
           this.detailurl =sc.getInitParameter("detailurl");           
          } catch (Exception e)
          {
            out.println("Нет или неправильные параметры: "+e.getLocalizedMessage()+"<br>");  
          }
...

            Connect2DB con = new Connect2DB(this.ServerName, this.DBName, this.UserName, this.Pwd);
            con.Connect();
            if (con.getConnected())
            {
                              
              glt = new GetListTrud(con.getCon(), viewflag);
              glt.FillData();              
              if (glt.getFillDataResult()) 
                 {
   ...тут большой кусок кода /протупил малеха, но переделывать не буду , так как это тестовый сервлет /
                 }
glt.CloseStatement();      

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package listtrud;

/**
 *
 * @author Vlad
 */

import java.sql.*;

public class GetListTrud
 {
    private Connection c;
    private Integer viewflag;   
    private PreparedStatement cst;
    private ResultSet rs;
    private Boolean fillDataResult;
    private String fillDataStr;
    
    //constructors
    public GetListTrud()
      {        
        c=null;
        viewflag=-1;
        cst=null;
        rs=null;
        fillDataResult=false;
        fillDataStr   ="";       
      }

    public GetListTrud(Connection con, Integer viewflag)
      {
        this.c=con;
        this.viewflag=viewflag;
        cst=null;
        rs=null;
        fillDataResult=false;
        fillDataStr   ="";  
      }
    
    //methods, etc       
    public ResultSet getRs()
      {
        return rs;        
      }

    public Boolean getFillDataResult()
      {
        return fillDataResult;
      }

    public String getFillDataStr()
      {
        return fillDataStr;
      }
    
         
    //заполняем данными список    
    public void FillData()  
      {
       if (c==null) 
       {
         this.fillDataResult=false;
         this.fillDataStr="Неверные параметры для выполнения SP. (c=null)";
         return;
       }       
       if (viewflag<0 || viewflag>2) 
       {
         this.fillDataResult=false;
         this.fillDataStr="Неверные параметры для выполнения SP. (viewflag="+viewflag.toString()+")";
         return;
       }                      
       try           
          {
            this.cst = c.prepareStatement("{call [dbo].[up_OK_BRO_S_TrudSpisok](?,?,?)}");
            this.cst.setString(1, "'NH'");
            this.cst.setString(2, "'OFF'");
            this.cst.setInt(3, this.viewflag);
            this.rs = cst.executeQuery();              
            this.fillDataResult=true;
             
          } catch (Exception e)
          {
           this.fillDataResult=false;
           this.fillDataStr="Невозможно выполнить SP:"+"<br>"+e.getMessage();          
          }
      }//filldata
          
    
    public void CloseStatement()
      {
        if (this.rs!=null) 
        {
            try
            {
             rs.close();    
            } catch (Exception e)
            {
            }
            
        }
        if (this.cst!=null) 
        {         
            try
            {
              this.cst.close();     
            } catch (Exception e)
            {
            }
        }
      }
    
  }

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="3.0" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd">
    <servlet>
        <servlet-name>ListTrud</servlet-name>
        <servlet-class>listtrud.ListTrud</servlet-class>
    </servlet>
    
    <servlet-mapping>
        <servlet-name>ListTrud</servlet-name>
        <url-pattern>/lt</url-pattern>
    </servlet-mapping>    
<!--
    <context-param>
     <param-name>name</param-name>
     <param-value>My Company, Incorporated</param-value>
    </context-param> 
  -->  
    <session-config>
        <session-timeout>
            30
        </session-timeout>
    </session-config>
</web-app>

<?xml version="1.0" encoding="UTF-8"?>
<Context antiJARLocking="true" path="/ListTrud"> 
    
<Parameter name="ServerName" value="ServerName" />
<Parameter name="DBName" value="DBName" />
<Parameter name="UserName" value="username" />
<Parameter name="Pwd" value="userpwd" />
<Parameter name="backurl" value="http://localhost:8080/ListTrud" />
<Parameter name="detailurl" value="http://localhost:8080/TrudData/?id="/>
</Context>

    //получаем данные
    public  void FillData()
    {
      if (this.IDTrud==0 || this.IDTrud==null)
              {
                this.fillDataResult=false;
                this.fillDataStr="Неверный параметр IDTrud";
                return;
              }
        try
        {
          Connect2DB con = new Connect2DB(this.ServerName, this.DBName, this.UserName, this.Pwd);
          con.Connect();
          if (!con.getConnected())
          {
            this.fillDataResult=false;
            this.fillDataStr=con.getConErr();
            return;
          }
              
          Connection c = con.getCon();    
          
            try
            {
             PreparedStatement cst=c.prepareStatement("{call [dbo].[up_OK_BRO_S_TrudData](?,?,?)}");
             cst.setString(1, "'NH'");
             cst.setString(2, "'OFF'");
             cst.setInt(3, this.IDTrud);
             ResultSet rs = cst.executeQuery();                                              
             //заполняем данными запроса private поля             
             if (rs!=null)
             {
               while (rs.next())  
               {   
                 this.FIO=rs.getString("FIO");
                 this.TabNo=Integer.toString(rs.getInt("TabNo"));
                 this.NalNo=rs.getString("NalNo");
                 this.Ceh=rs.getString("NameCeh");
                 this.Podr=rs.getString("NamePodr");
                 this.Dolzh=rs.getString("NameDol");
                 this.Razr = rs.getString("NameRazr");
                 this.Pr_Date = rs.getString("DateInSTR");
                 this.Pr_Prikaz = rs.getString("PrikazIn");
                 this.Pr_PrikazDate = rs.getString("PrikazDateInSTR");
                 if (rs.getInt("WorkStatus")==1)
                 {    
                  this.Uv_Date  = rs.getString("DateOutSTR");
                  this.Uv_Prikaz = rs.getString("PrikazOut");
                  this.Uv_PrikazDate = rs.getString("PrikazDateOutSTR");
                  this.Uv_st  = rs.getString("NameUv");
                 } 
                 this.City = rs.getString("NameTown");
                 this.Adress = rs.getString("FullAdress");
                 this.Tel = rs.getString("Fulltel");                         
               }  
               this.fillDataResult=true;  
             }//rs!=null   
             
             if (rs!=null) {rs.close();}
             if (cst!=null) {cst.close();}                         
            } catch (Exception e)
            {
              this.fillDataResult=false;
              this.fillDataStr="Ошибка выполнения SP:"+e.getMessage();  
            }                   
          con.Disconnect();
        } catch (Exception e)
        {
          this.fillDataResult=false;
          this.fillDataStr="Невозможно подключиться к SQL  серверу: "+e.getMessage();            
        }//connect2DB          
    }//FillData