JDBC connection from Eclipse to MSSQL server using sqljdbc4.jar

Question

I am trying to make a jdbc connection to MS SQL server 2014 using sqljdbc4 from an Eclipse Web project, without any luck whatsoever.

Here is what I have tried so far:

Create a test class outside of the Web project, add jar to build path and try to make a connection - success
Place jar under project's WEB-INF/lib, add jar to build path with and without adding a Web App Library for the project and try to make a connection - failure
Place jar under the central Tomcat lib and try to make a connection - failure

Most of the forums have users who have succeeded by doing number (2) in the list above. I am just starting out with JDBC and it took a while to even get to this stage. But unfortunately, couldn't get any further. I am stuck at this point for close to 7 hours now and the frustrating thing is it works every time from a regular java project. Why is that so, when any kind of project in an IDE requires the jar to be in its classpath?

Not sure how much help this will be of, but here is the code that I had come up with that tries to establish the connection. And it always leads to an SQLException : No suitable driver found for jdbc:sqlserver on the first line after 'try'.

public class SQLConnector {
    private static final String DB_SERVER = "jdbc:sqlserver://SAI;"
                + "DatabaseName=LibraryManagementSystem";
    private static final String DB_USER="sa";
    private static final String DB_PASS="abc732XYZ";

    public static Connection getDatabaseConnection() {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(DB_SERVER, DB_USER, DB_PASS);
            if(connection != null) {
                System.out.println("Connection successful");
            }
        }
        catch(SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
}

Kindly review and help.

Here is my project tree for the Web application

Mark Rotteveel · Accepted Answer · 2014-07-26 14:56:05Z

1

Yes placing the jar under project's WEB-INF/lib should resolve the problem but which jar are you placing is equally important.

Since you have not used Class.forName() to load the class I am assuming you are using JDBC 4.0 (JAVA6/7). So you need to place the jar sqljdbc4.jar under that directory.

You can download it from here.

edited Jul 26, 2014 at 14:56

Mark Rotteveel

110k240 gold badges160 silver badges232 bronze badges

answered Jul 26, 2014 at 14:54

Aniket Thakur

69.4k43 gold badges290 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Sai Over a year ago

Thanks Aniket. I downloaded it from here. What is the difference between the link to 2.0 that you have provided and what I am using? And also the Connection class works fine from non-Web Applcation context. How is this possible?

Aniket Thakur Over a year ago

Looks like yours is more recent driver which provides access till 'SQL Server 2014, SQL Server 2012' unlike my download link which supports only till 2008 version.

Aniket Thakur Over a year ago

Also for non web application context JVM will pickup the driver from any directory that is in your classpath. When you run web app is tomcat container all your libraries are in WEB-INF/lib folder which is by default in your classpath.

Sai Over a year ago

On the technet site, I see that all the documentation is provided assuming the SQL server version to be 2012. I am using 2014 although I am not convinced this is the reason as I am able to establish a connection from a non-Web application.

Aniket Thakur Over a year ago

Yes it is not. You are using correct jar. It is a CLASSPATH issue. Since you are already able to establish connection in non web context.

|

Sai · Accepted Answer · 2014-07-28 02:02:23Z

I ended up using Tomcat Connection pooling and it worked. This is done by creating a Context.xml file under META-INF under Web content with the following content.

    <Context>
        <!-- Specify a JDBC datasource -->
        <Resource name="jdbc/LibraryManagementSystem" auth="Container"
        type="javax.sql.DataSource" username="sa" password="abc732XYZ"
        driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
        url="jdbc:sqlserver://SAI:1433;DatabaseName=LibraryManagementSystem"
        maxActive="10" maxIdle="4" />
   </Context>

And suitably changing the connector class to reflect Connection pooling by using DataSource and Initial Context. Also, connection pooling is recommended over using plain jdbc.

public class SQLConnector 
{
    private static final String DB_NAME = "jdbc/LibraryManagementSystem";
    private static Connection connection;

    public static Connection getDatabaseConnection() {

        try {
            Context initContext  = new InitialContext();
            Context envContext  = (Context)initContext.lookup("java:/comp/env");
            DataSource dataSource = (DataSource)envContext.lookup(DB_NAME);
            connection = dataSource.getConnection();
        } 
        catch (NamingException | SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
}

Collectives™ on Stack Overflow

JDBC connection from Eclipse to MSSQL server using sqljdbc4.jar

2 Answers 2

7 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Linked

Related