Step 1: Verify SQL Server Configuration

Before coding, make sure SQL Server is ready:

Step 2: Add JDBC Driver to Maven

1
2
3
4
5
6
7
8

<dependencies>
    <!-- SQL Server JDBC Driver -->
    <dependency>
        <groupId>com.microsoft.sqlserver</groupId>
        <artifactId>mssql-jdbc</artifactId>
        <version>11.2.0.jre8</version>
    </dependency>
</dependencies>

Step 3: Create Database and Tables

Open SSMS → New Query → Run:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

-- Create Database
CREATE DATABASE JSPShop;
GO

USE JSPShop;
GO

-- Product Table
CREATE TABLE Products (
    ProductID INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL,
    Price DECIMAL(10,2) NOT NULL,
    Stock INT NOT NULL
);

-- Users Table
CREATE TABLE Users (
    UserID INT PRIMARY KEY IDENTITY(1,1),
    Username NVARCHAR(50) NOT NULL UNIQUE,
    Password NVARCHAR(255) NOT NULL,
    Role NVARCHAR(20) NOT NULL
);

-- Insert sample data
INSERT INTO Products (Name, Price, Stock) VALUES
(N'Laptop Dell', 1500.00, 10),
(N'Logitech Mouse', 25.50, 50),
(N'Auto Keyboard', 75.00, 30);

INSERT INTO Users (Username, Password, Role) VALUES
(N'admin', 'admin123', N'ADMIN'),
(N'john', '123456', N'CUSTOMER');

Step 4: Create DBContext class in Utility package

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

package com.jsp.shop.utils;

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

public class DBContext {
    protected Connection connection;

    public DBContext() {
        try {
            String url = "jdbc:sqlserver://localhost:1433;"
                    + "databaseName=JSPShop;"
                    + "user=sa;"
                    + "password=P@ssword123;"
                    + "encrypt=true;"
                    + "trustServerCertificate=true;";
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            connection = DriverManager.getConnection(url);
        } catch (ClassNotFoundException | SQLException ex) {
            System.out.println("Database connection failed: " + ex);
        }
    }
}

Notes:

Step 5: Test the Connection

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

package Controllers;

import dal.DBContext;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import jakarta.servlet.ServletException;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;

public class TestDBServlet extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");

        try (PrintWriter out = response.getWriter()) {
            DBContext db = new DBContext();
            Connection conn = db.connection;
            if (conn != null && !conn.isClosed()) {
                out.println("<h1>Database Connection Successful!</h1>");
            } else {
                out.println("<h1>Database Connection Failed!</h1>");
            }
            // Optionally close the connection here if DBContext didn't manage it.
            try {
                if (conn != null && !conn.isClosed()) conn.close();
            } catch (Exception ignored) {}
        } catch (Exception ex) {
            // Print a friendly message to browser and full stack trace to server logs
            response.getWriter().println("<h1>Test failed: " + ex.getMessage() + "</h1>");
            ex.printStackTrace();
        }
    }
}

Note:Why no @WebServlet?

1
2

@WebServlet("/testdb")
public class TestDBServlet extends HttpServlet

We update in web.xml

Step 5: Configure web.xml mapping

File: ./WEB-INF/web.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="https://jakarta.ee/xml/ns/jakartaee"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="https://jakarta.ee/xml/ns/jakartaee
                             https://jakarta.ee/xml/ns/jakartaee/web-app_5_0.xsd"
         version="5.0">

    <servlet>
        <servlet-name>TestDBServlet</servlet-name>
        <servlet-class>Controllers.TestDBServlet</servlet-class>
    </servlet>

    <servlet-mapping>
        <servlet-name>TestDBServlet</servlet-name>
        <url-pattern>/testdb</url-pattern>
    </servlet-mapping>

    <!-- welcome file list if needed -->
    <welcome-file-list>
        <welcome-file>index.jsp</welcome-file>
    </welcome-file-list>
</web-app>

Build & Deploy

If your project artifactId is jsp-shop, context root usually /jsp-shop → http://localhost:8080/jsp-shop/testdb.