CODEWITHSUNDEEP
javasql-servernetbeans
rerashhh
rerashhh

Reputation: 1711

Cannot connect to sqlserver from java application

i have web service which needs to connect to a sqlserver to do the login, but somehow it does not work and it throws an exception. I used the following steps to connect:

From service client:

 TrackingWS_Service service=new TrackingWS_Service();
 User user = service.getTrackingWSPort().login(username,password);

TrackingWS_Service which is a generated class from the same project:

@WebServiceClient(name = "TrackingWS", targetNamespace = "http://webservices/", wsdlLocation =  "http://localhost:8080/TrackingSystem/TrackingWS?wsdl")
 public class TrackingWS_Service extends Service
 {
    @WebEndpoint(name = "TrackingWSPort")
    public TrackingWS getTrackingWSPort() {
    return super.getPort(new QName("http://webservices/", "TrackingWSPort"), TrackingWS.class);
    }
 }

the interface TrackingWs:

 @WebService(name = "TrackingWS", targetNamespace = "http://webservices/")
 @XmlSeeAlso({
 ObjectFactory.class
 })
 public interface TrackingWS {

    @WebMethod
    @WebResult(targetNamespace = "")
    @RequestWrapper(localName = "login", targetNamespace = "http://webservices/", className = "webservices.Login")
    @ResponseWrapper(localName = "loginResponse", targetNamespace = "http://webservices/", className = "webservices.LoginResponse")
    @Action(input = "http://webservices/TrackingWS/loginRequest", output = "http://webservices/TrackingWS/loginResponse")
     public User login(
    @WebParam(name = "arg0", targetNamespace = "")
    String arg0,
    @WebParam(name = "arg1", targetNamespace = "")
    String arg1);
 }

And in the service project i have the following:

TrackingWs - the web service:

 @WebService(serviceName = "TrackingWS")
 public class TrackingWS {
   @WebMethod
   public User login(String username,String password){
   DBOperations dbOperations=new DBOperations();
   return dbOperations.findUser(username, password);     
   }
 }

DBOperations:

public class DBOperations {
private Connection connection = null;
private ResultSet rs = null;
private java.sql.PreparedStatement pst = null;

public synchronized User findUser(String username,String password){
connection=Connect.ConnectDb();

String sql="SELECT * FROM [Assignment5].[dbo].[User] WHERE Username=? AND Password=?";
        User user=null;
        int id;
        String usrname;
        String pasword;
        String type;
    try{
        pst=connection.prepareStatement(sql);
        pst.setString(1, username);
        pst.setString(2, password);
        rs=pst.executeQuery();
        if(rs.next()){
                    id=rs.getInt("ID");
                    usrname=rs.getString("Username");
                    pasword=rs.getString("Password");
                type=rs.getString("Type");    
                user=new User(id,usrname,pasword,type);
                    close();
                    return user;
            }

    }
    catch(SQLException e){
        e.printStackTrace();

    }
    try {
        close();
    } catch (SQLException ex) {
        Logger.getLogger(DBOperations.class.getName()).log(Level.SEVERE, null, ex);
    }
  return user;
}

}

and finally the connection:

public class Connect { // Connection conn=null;

public static Connection ConnectDb(){

    try {
    Connection conn = DriverManager.getConnection("jdbc:sqlserver://localhost;databaseName=Assignment5;integratedSecurity=true;");  
    return conn;
}
catch (Exception e) {
        JOptionPane.showMessageDialog(null, e);
                    return null;
}
}

}

I set the sqljdbc4 and i also copied the sqljdbc_auth.dll in the web service project. I have no idea why it throws the exception when calling getConnection().

Upvotes: 0

Views: 755

Answers (2)

rerashhh
rerashhh

Reputation: 1711

finally i made it work, i just had to to add the jdbc jar to glassfish-4.1\glassfish\domains\domain1\lib folder

Upvotes: 0

Ankit Nanglia
Ankit Nanglia

Reputation: 23

Possible reason for getting the Exception while connecting SQLServer with java

  1. When the class path is not properly set.
  2. Add the resource of the database in the context.xml file of the server you are using like this
<Resource name="jdbc/vt"

auth="Container"

type="javax.sql.DataSource"

username="ankit"

password="ankit"

driverClassName="com.mysql.jdbc.Driver"

url="jdbc:sqlserver://localhost\\SQLEXPRESS;Database=vt"

maxActive="15"

maxIdle="3"/>
  1. After adding the resource set the reference to that resource in your application web.xml file like this
<resource-ref>
    <description>Connection Pool</description>
    <res-ref-name>jdbc/vt</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
</resource-ref>

Note: the resource are according to my project, change it accordingly to yours.

Hope it helps

Upvotes: 1

Related Questions