SQL Server stored procedure and jdbc

Question

I am struggling with SQL Server 2005 and JDBC. I have a stored procedure:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER proc [dbo].[sp_logincheck]
    @username as nvarchar(50),
    @password as nvarchar(50)
as
begin
    select * 
    from users 
    where user_name = @username 
      and password = @password 
      and is_active = 'Yes'
end

And my User class is:

import java.sql.*;

public class User {
    private String username;

    private User(String username){
        this.username = username;
    }

    public static User login(String username, char [] password) throws SQLException{
        if(username == null || password == null){
            throw new IllegalArgumentException("Illegal arguments passed to method");
        }
        if(login1(username, password)){
            return new User(username);
        }
        return null;
    }

    private static boolean login1(String username, char [] password) throws SQLException{
        Connection connection = null;
        CallableStatement statement = null;
        try{
            connection = DriverManager.getConnection(AppParameters.dbURL, AppParameters.dbUsername, AppParameters.dbPassword);
        }catch(SQLException e){
            throw e;
        }
        try{
            statement = connection.prepareCall("{ ? = call dbo.sp_logincheck(?,?) }");
            statement.registerOutParameter(1, Types.INTEGER);
            statement.setString(2, username);
            statement.setString(3, new String(password));
            statement.execute();
            if(statement.getInt(1) == 1){
                System.out.println("Login Successfull");
                return true;
            }
            System.out.println("Login Failed");
            return false;
        }catch(SQLException sqle){
            sqle.printStackTrace();
            throw sqle;
        }finally{
            try{
                statement.close();
            }catch(Exception e){
            }
            try{
                connection.close();
            }catch(Exception e){
            }
        }
    }

    public  String getUsername(){
        return username;
    }
}

Calling login() method always prints Login Failed. How can I use stored procedure in SQL Server and using JDBC to perform user login? Or going with raw SQL statements is better? Please guide me on this.

EDIT:

I would also like to know how to get the ResultSet from the above stored procedure, as I have a select query inside the stored procedure.

gaborsch · Accepted Answer · 2013-01-15 09:22:31Z

1

Try the following:

ResultSet rs = statement.executeQuery();
if (! rs.isLast()) {
    // match
} else {
    // no match
}

Also, I'd recommend to select only the count (select count(*) as cnt from ...), and use like int count = rs.next().getInt("cnt");

answered Jan 15, 2013 at 9:22

gaborsch

15.8k6 gold badges40 silver badges50 bronze badges

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

1 Comment

lol Over a year ago

its working.. but instead of using if(!rs.isLast()), I am using return rs.next(); it will be fine, right?

Andremoniy · Accepted Answer · 2019-08-30 11:49:10Z

1

Try to change select * from users... in your sql statement to select count(*) from users... in the stored procedure .

Otherwise your expression if(statement.getInt(1) == 1){ does not make any sense.

edited Aug 30, 2019 at 11:49

answered Jan 15, 2013 at 7:39

Andremoniy

35.2k25 gold badges143 silver badges259 bronze badges

5 Comments

lol Over a year ago

I did that, but same output :(

Andremoniy Over a year ago

Have you restarted you application after altering stored procedure?

Andremoniy Over a year ago

Please, add System.out.println(statement.getInt(1)); after statement.execute(); and show us what it prints out.

gaborsch Over a year ago

@Meraman Have you tried ResultSet rs = statement.executeQuery();, and rs.next() - if you have rows, the password matches. Also, I'd recommend to select only the count, and use like int count = rs.getInt("count");

lol Over a year ago

@Andremoniy statement.getInt(1) is always returning 0 for valid or invalid users.

Abhishek Bhandari · Accepted Answer · 2013-01-15 09:20:06Z

0

If

statement.getInt(1)

is equals the number of rows resulted or there are at least one row . Then this may be a data mismatch case . No data found

edited Jan 15, 2013 at 9:20

answered Jan 15, 2013 at 9:13

Abhishek Bhandari

6231 gold badge5 silver badges16 bronze badges

1 Comment

gaborsch Over a year ago

Reading from the statement has no sense, he could get the value he set for the first parameter of the query, it if were int. You can do it even before calling the query, this will surely not contain any result.

Collectives™ on Stack Overflow

SQL Server stored procedure and jdbc

3 Answers 3

1 Comment

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related