JDBC connection from Eclipse to MSSQL server using

问题:

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:

1. Create a test class outside of the Web project, add jar to build path and try to make a connection - success
2. 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
3. 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

回答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.

回答2:

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;
    }
}