Categories
discuss

Spring security : java.sql.SQLException: Column Index out of range, 3 > 2

I’m using spring 4 and I want to use spring security 3 to control my login form. So I have the following tables in database :

users table :

CREATE TABLE IF NOT EXISTS `users` (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(45) NOT NULL,
  password VARCHAR(45) NOT NULL,
  email VARCHAR(45) NOT NULL,
  enabled TINYINT NOT NULL DEFAULT 1 ,
  PRIMARY KEY (id),
  UNIQUE KEY uni_name_email_key (name, email)
  )ENGINE = InnoDB;

roles table :

CREATE TABLE IF NOT EXISTS `roles` (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(45) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uni_name_id_role (name)
  )ENGINE = InnoDB;

And users_roles

CREATE TABLE IF NOT EXISTS `users_roles` (
  id INT(11) NOT NULL AUTO_INCREMENT,
  user_id INT(11) NOT NULL ,
  role_id INT(11) NOT NULL ,
  PRIMARY KEY (id),
  UNIQUE KEY uni_user_role_key (user_id,role_id),
  CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES users (id),
  CONSTRAINT fk_role_id FOREIGN KEY (role_id) REFERENCES roles (id)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION
)ENGINE = InnoDB;

My spring-security config file has the following lines :

<?xml version="1.0" encoding="UTF-8"?>
<beans:beans xmlns="http://www.springframework.org/schema/security"
             xmlns:beans="http://www.springframework.org/schema/beans"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://www.springframework.org/schema/beans
           http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
           http://www.springframework.org/schema/security
           http://www.springframework.org/schema/security/spring-security.xsd">

    <http use-expressions="true">
        <intercept-url pattern="/users**" access="hasRole('ROLE_ADMIN')" />
        <intercept-url pattern="/users/ **" access="hasRole('ROLE_ADMIN')" />
        <intercept-url pattern="/roles **" access="hasRole('ROLE_ADMIN')" />
        <intercept-url pattern="/roles/ **" access="hasRole('ROLE_ADMIN')" />
        <form-login login-page='/login'/>
        <logout logout-url="/logout"/>
        <logout />
    </http>

    <authentication-manager>
        <authentication-provider>
            <jdbc-user-service data-source-ref="dataSource"
                users-by-username-query="select users.name, roles.name from users
                    join users_roles on users.id = users_roles.user_id
                    join roles on users_roles.role_id = roles.id
                    where users.name = ?"
                authorities-by-username-query="select users.name, users.password , 1
                    from users where users.name = ?" />
        </authentication-provider>
    </authentication-manager>

</beans:beans>

When i try to connect i have the following error :

> 2014-12-31 20:35:21 ERROR UsernamePasswordAuthenticationFilter:218 -
> An internal error occurred while trying to authenticate the user.
> org.springframework.security.authentication.InternalAuthenticationServiceException:
> PreparedStatementCallback; SQL [select users.name, roles.name from
> users join users_roles on users.id = users_roles.user_id join roles on
> users_roles.role_id = roles.id where users.name = ?]; Column Index out
> of range, 3 > 2. ; nested exception is java.sql.SQLException: Column
> Index out of range, 3 > 2.    at
> org.springframework.security.authentication.dao.DaoAuthenticationProvider.retrieveUser(DaoAuthenticationProvider.java:110)
>   at
> org.springframework.security.authentication.dao.AbstractUserDetailsAuthenticationProvider.authenticate(AbstractUserDetailsAuthenticationProvider.java:132)
>   at
> org.springframework.security.authentication.ProviderManager.authenticate(ProviderManager.java:156)
>   at
> org.springframework.security.authentication.ProviderManager.authenticate(ProviderManager.java:177)
>   at
> org.springframework.security.web.authentication.UsernamePasswordAuthenticationFilter.attemptAuthentication(UsernamePasswordAuthenticationFilter.java:94)
>   at
> org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:211)
>   at
> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>   at
> org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:110)
>   at
> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>   at
> org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87)
>   at
> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>   at
> org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:192)
>   at
> org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160)
>   at
> org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:344)
>   at
> org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:261)
>   at
> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
>   at
> org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
>   at
> org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
>   at
> org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
>   at
> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
>   at
> org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
>   at
> org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
>   at
> org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
>   at
> org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
>   at
> org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
>   at
> org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
>   at
> org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
>   at
> org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)
>   at
> org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606)
>   at
> org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
>   at java.lang.Thread.run(Thread.java:745) Caused by:
> org.springframework.dao.TransientDataAccessResourceException:
> PreparedStatementCallback; SQL [select users.name, roles.name from
> users join users_roles on users.id = users_roles.user_id join roles on
> users_roles.role_id = roles.id where users.name = ?]; Column Index out
> of range, 3 > 2. ; nested exception is java.sql.SQLException: Column
> Index out of range, 3 > 2.    at
> org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:108)
>   at
> org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
>   at
> org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
>   at
> org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
>   at
> org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:660)
>   at
> org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:695)
>   at
> org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:727)
>   at
> org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:737)
>   at
> org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:787)
>   at
> org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl.loadUsersByUsername(JdbcDaoImpl.java:192)
>   at
> org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl.loadUserByUsername(JdbcDaoImpl.java:151)
>   at
> org.springframework.security.authentication.dao.DaoAuthenticationProvider.retrieveUser(DaoAuthenticationProvider.java:102)
>   ... 30 more Caused by: java.sql.SQLException: Column Index out of
> range, 3 > 2.     at
> com.mysql.jdbc.SQLError.createSQLException(SQLError.java:996)     at
> com.mysql.jdbc.SQLError.createSQLException(SQLError.java:935)     at
> com.mysql.jdbc.SQLError.createSQLException(SQLError.java:924)     at
> com.mysql.jdbc.SQLError.createSQLException(SQLError.java:870)     at
> com.mysql.jdbc.ResultSetImpl.checkColumnBounds(ResultSetImpl.java:758)
>   at com.mysql.jdbc.ResultSetImpl.getBoolean(ResultSetImpl.java:1503)
>   at
> org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl$1.mapRow(JdbcDaoImpl.java:196)
>   at
> org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl$1.mapRow(JdbcDaoImpl.java:192)
>   at
> org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:93)
>   at
> org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:60)
>   at
> org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:708)
>   at
> org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:644)
>   ... 37 more

Could you please tell me what’s wrong in my configuration ?

Thank you

Answer

The users-by-username-query and authorities-by-username-query are mixed up. Move the first SQL query to the second and vice versa.

Maybe you want to change 1 to users.enabled too.

Categories
discuss

Node.js: How to run asynchronous code sequentially

I have this chunk of code

User.find({}, function(err, users) {
    for (var i = 0; i < users.length; i++) {
        pseudocode
        Friend.find({
            'user': curUser._id
        }, function(err, friends) * * ANOTHER CALLBACK * * {
            for (var i = 0; i < friends.length; i++) {
                pseudocode
            }
            console.log("HERE I'm CHECKING " + curUser);
            if (curUser.websiteaccount != "None") {
                request.post({
                    url: 'blah',
                    formData: blah
                }, function(err, httpResponse, body) { * * ANOTHER CALLBACK * *
                        pseudocode
                    sendMail(friendResults, curUser);
                });
            } else {
                pseudocode
                sendMail(friendResults, curUser);
            }
        });
        console.log("finished friend");
        console.log(friendResults);
        sleep.sleep(15);
        console.log("finished waiting");
        console.log(friendResults);
    }
});

There’s a couple asynchronous things happening here. For each user, I want to find their relevant friends and concat them to a variable. I then want to check if that user has a website account, and if so, make a post request and grab some information there. Only thing is, that everything is happening out of order since the code isn’t waiting for the callbacks to finish. I’ve been using a sleep but that doesn’t solve the problem either since it’s still jumbled.

I’ve looked into async, but these functions are intertwined and not really separate, so I wasn’t sure how it’d work with async either.

Any suggestions to get this code to run sequentially?

Thanks!

Answer

I prefer the promise module to q https://www.npmjs.com/package/promise because of its simplicity

var Promises = require('promise');
var promise = new Promises(function (resolve, reject) {
    // do some async stuff
    if (success) {
        resolve(data);
    } else {
        reject(reason);
    }
});
promise.then(function (data) {
    // function called when first promise returned
    return new Promises(function (resolve, reject) {
        // second async stuff
        if (success) {
            resolve(data);
        } else {
            reject(reason);
        }
    });
}, function (reason) {
    // error handler
}).then(function (data) {
    // second success handler
}, function (reason) {
    // second error handler
}).then(function (data) {
    // third success handler
}, function (reason) {
    // third error handler
});

As you can see, you can continue like this forever. You can also return simple values instead of promises from the async handlers and then these will simply be passed to the then callback.

Categories
discuss

Design Patterns for Data Access Layer

I have an application which uses a database (MongoDB) to store information. In the past I have used a class full of static methods to save and retrieve data but I have since realised this is not very object-oriented-ish or future proof.

Even though it is very unlikely I will change database I would rather something that does not tie me too strongly to Mongo. I would also like to be able to cache results with the option to refresh the cached object from the database but this is not essential and can be done in another place.

I have looked at data access objects but they do not seem very well-defined and I can’t find any good examples of implementation (In Java or a similar language). I also have many one off cases such as finding usernames for tab completion which seem not well suited and would make the DAO large and bloated.

Are there any design patterns that would facilitate getting and saving objects without being too database specific? Good examples of implementation would be helpful (preferably in Java).

Answer

Well, the common approach to data storage in java is, as you noted, not at all very object-oriented. This is in and of itself neither bad or good: “object-orientedness” is neither advantage nor disadvantage, it’s just one of many paradigms, that sometimes helps with good architecture design (and sometimes does not).

The reason DAOs in java aren’t usually object-oriented is exactly what you want to achieve – relaxing your dependency on the database specific. In a better-designed language, that allowed for multiple inheritance, this, or course, can be done very elegantly in an object-oriented way, but with java, it just seems to be more trouble than it is worth.

In a broader sense, the non-OO approach helps decouple your application-level data from the way it is stored. This is more than (non) dependency on the specifics of a particular database, but also the storage schemas, which is especially important when using relational databases (don’t get me started on ORM): you can have a well-designed relational schema seamlessly translated into the application OO model by your DAO.

So, what most DAOs are in java nowadays are essentially what you mentioned in the beginning – classes, full of static methods. One difference is that, instead of making all the methods static, it is better to have a single static “factory method” (probably, in a different class), that returns a (singleton) instance of your DAO, which implements a particular interface, used by application code to access the database:

public interface GreatDAO {
    User getUser(int id);
    void saveUser(User u);
}
public class TheGreatestDAO implements GreatDAO {
   protected TheGeatestDAO(){}
   ... 
}
public class GreatDAOFactory {
     private static GreatDAO dao = null;
     protected static synchronized GreatDao setDAO(GreatDAO d) {
         GreatDAO old = dao;
         dao = d;
         return old;
     }
     public static synchronized GreatDAO getDAO() {
         return dao == null ? dao = new TheGreatestDAO() : dao;
     }
}

public class App {
     void setUserName(int id, String name) {
          GreatDAO dao =  GreatDAOFactory.getDao();
          User u = dao.getUser(id);
          u.setName(name);
          dao.saveUser(u);
     }
}

Why do it this way as opposed to static methods? Well, what if you do decide to switch to a different database? Naturally, you’d create a new DAO class, implementing the logic for your new storage. If you were using static methods, you would now have to go through all your code, accessing the DAO, and change it to use your new class, right? This could be a huge pain. And what if then you change your mind and want to switch back to the old db?

With this approach, all you need to do is to change the GreatDAOFactory.getDAO() and make it create an instance of a different class, and all your application code will be using the new database without any changes.

In real life, this is often done without any changes to the code at all: the factory method gets the implementation class name via a property setting, and instantiates it using reflection, so, all you need to do to switch implementations is edit a property file. There are actually frameworks – like spring or guice – that manage this “dependency injection” mechanism for you, but I won’t go into details, first, because it is really beyond the scope of your question, and also, because I am not necessarily convinced that the benefit you get from using those frameworks is worth the trouble integrating with them for most applications.

Another (probably, more likely to be taken advantage of) benefit of this “factory approach” as opposed to static is testability. Imagine, that you are writing a unit test, that should test the logic of your App class independently of any underlying DAO. You don’t want it to use any real underlying storage for several reasons (speed, having to set it up, and clean up afterwords, possible collisions with other tests, possibility of polluting test results with problems in DAO, unrelated to App, which is actually being tested, etc.).

To do this, you want a test framework, like Mockito, that allows you to “mock out” the functionality of any object or method, replacing it with a “dummy” object, with predefined behavior (I’ll skip the details, because, this again is beyond the scope). So, you can create this dummy object replacing your DAO, and make the GreatDAOFactory return your dummy instead of the real thing by calling GreatDAOFactory.setDAO(dao) before the test (and restoring it after). If you were using static methods instead of the instance class, this would not be possible.

One more benefit, which is kinda similar to switching databases I described above is “pimping up” your dao with additional functionality. Suppose that your application becomes slower as the amount of data in the database grows, and you decide that you need a cache layer. Implement a wrapper class, that uses the real dao instance (provided to it as a constructor param) to access the database, and caches the objects it reads in memory, so that they can be returned faster. You can then make your GreatDAOFactory.getDAO instantiate this wrapper, for the application to take advantage of it.

(This is called “delegation pattern” … and seems like pain in the butt, especially when you have lots of methods defined in your DAO: you will have to implement all of them in the wrapper, even to alter behavior of just one. Alternatively, you could simply subclass your dao, and add caching to it this way. This would be a lot less boring coding upfront, but may become problematic when you do decide to change the database, or, worse, to have an option of switching implementations back and forth.)

One equally broadly used (but, in my opinion, inferior) alternative to the “factory” method is making the dao a member variable in all classes that need it:

public class App {
   GreatDao dao;
   public App(GreatDao d) { dao = d; }
}

This way, the code that instantiates these classes needs to instantiate the dao object (could still use the factory), and provide it as a constructor parameter. The dependency injection frameworks I mentioned above, usually do something similar to this.

This provides all the benefits of the “factory method” approach, that I decribed earlier, but, like I said, is not as good in my opinion. The disadvantages here are having to write a constructor for each of your app classes, doing the same exact thing over, and over, and also not being able to instantiate the classes easily when needed, and some lost readability: with a large enough code base, a reader of your code, not familiar with it, will have hard time understanding which actual implementation of the dao is used, how it is instantiated, whether it is a singleton, a thread-safe implementation, whether it keeps state, or caches anything, how the decisions on choosing a particular implementation are made etc.

Categories
discuss

Bottom soft NavigationBar overlaps my ListView

I ran my app on a Nexus 5 (Android 5) but I encountered the problem that the soft NavigationBar at the bottom overlaps the last item of my ListView. I’ve tried to add fitsSystemWindows to my style and the ListView but that didn’t work.

The XML of my layout:

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="vertical"
    android:background="@color/sf4l"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent">

    <ListView android:id="@id/android:list"
        android:layout_width="match_parent"
        android:layout_height="fill_parent"
        android:layout_weight="1"
        android:background="@color/sf4l" />
</LinearLayout>

Answer

Add this to your themes.xml in a values-v21 dir:

<item name="android:windowDrawsSystemBarBackgrounds">false</item>

Example (I’m using AppCompat for actionbars):

<style name="Theme" parent="Theme.AppCompat.Light.DarkActionBar">
    <item name="homeAsUpIndicator">@drawable/new_indicator</item>
    <item name="android:homeAsUpIndicator">@drawable/new_indicator</item>
    <item name="actionModeBackground">@android:color/black</item>
    <item name="android:windowDrawsSystemBarBackgrounds">false</item>
</style>
Categories
discuss

Android studio importing in gradle issue

I am new to Android Studio and was just trying to import my eclipse project. i am trying to resolve this issue for quite a while now but unable to. I have a mainactivity project which uses mmany other libraries staggeredgridview, devsmart, google maps and appcompactv7. I am getting following 2 errors.

Note: mainactivity project is the one which i am working on and uses other external libraries.

Error:(7, 5) uses-sdk:minSdkVersion 8 cannot be smaller than version 9 declared in library B:Android Studio ProjectsmainActivitybuildintermediatesexploded-aarcom.google.android.gmsplay-services6.5.87AndroidManifest.xml 

Suggestion: use tools:overrideLibrary="com.google.android.gms" to force usage
:mainActivity:processDebugManifest FAILED
Error:Execution failed for task ':mainActivity:processDebugManifest'.
Manifest merger failed with multiple errors, see logs

build.gradle of mainactivity

apply plugin: 'com.android.application'

android {
compileSdkVersion 17
buildToolsVersion "21.1.2"

defaultConfig {
    applicationId "com.example.staggeredgridviewdemo"
    minSdkVersion 8
    targetSdkVersion 16
}

buildTypes {
    release {
        minifyEnabled false
        proguardFiles getDefaultProguardFile('proguard-android.txt'), 'proguard-rules.txt'
        }
    }
}

dependencies {
    compile project(':devsmartAndroid')
    compile project(':staggeredGridViewmaster')
    compile 'com.google.guava:guava:16.0.1'
    compile 'com.google.android.gms:play-services:+'
    compile 'com.android.support:appcompat-v7:19.1.0'
    compile files('libs/commons-codec-1.6.jar')
}

build.grade for devsmart

apply plugin: 'com.android.library'

android {
    compileSdkVersion 14
    buildToolsVersion "21.1.2"

    defaultConfig {
        minSdkVersion 4
        targetSdkVersion 4
    }

buildTypes {
    release {
        minifyEnabled false
        proguardFiles getDefaultProguardFile('proguard-android.txt'), 'proguard-rules.txt'
        }
    }
}

dependencies {
    compile files('libs/CWAC-SackOfViewsAdapter.jar')
    compile('com.android.support:appcompat-v7:19.1.0') {
        // really use 19.1.0 even if something else resolves higher
       force = true
    }
}

build.gradle for sttageredgridview

apply plugin: 'com.android.library'

android {
    compileSdkVersion 17
    buildToolsVersion "21.1.2"

defaultConfig {
    minSdkVersion 8
    targetSdkVersion 16
}

buildTypes {
    release {
        minifyEnabled false
        proguardFiles getDefaultProguardFile('proguard-android.txt'), 'proguard-rules.txt'
        }
    }
}

dependencies {
    compile('com.android.support:appcompat-v7:19.1.0') {
        // really use 19.1.0 even if something else resolves higher
       force = true
    }
}

Answer

Just edit minsdk of your build.gradle of mainactivity to 9 :

defaultConfig {
    applicationId "com.example.staggeredgridviewdemo"
    minSdkVersion 9 // changed line
    targetSdkVersion 16
}

Note : Also did the same for other libs if there minsdk is small then 9

Source: stackoverflow
Text is available under the Creative Commons Attribution-ShareAlike License; additional terms may apply. By using this site, you agree to the Privacy Policy, and Copyright Policy. Content is available under CC BY-SA 3.0 unless otherwise noted. The answers/resolutions are collected from stackoverflow, are licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0 © No Copyrights, All Questions are retrived from public domain..