I have created a form login page with two fields email and password and a submit button in bonitasoft studio community edition 6.4.1.
There is a form page validator that will check whether the email id and password
entered by the user in the login form already exist in the database.
If yes then submit will work else error will be given that email id or password is invalid.
The validator type is groovy expression and in the parameter I have written the groovy script :
import groovy.sql.Sql;
import java.sql.Driver;
def host = “localhost:3306”;
def user = “root”;
def pass = “root”;
def schema = “test”;
def sql = BonitaSql.newInstance(“jdbc:mysql://$host/$schema”,“$user”,“$pass”, new com.mysql.jdbc.Driver());
def query=“”;
query = “select count(‘*’) from test.project where email='”+loginemail+“’ and password='”+loginpassword+“';”
def rowcount = 0;
rowcount = sql.execute(query);
if(rowcount == 0)
return false; //this does not work or checked.
else
return true; //this is returned always irrespective of what is the outcome of the if condition.
The return type for the validator is boolean.
The issue is that the value returned by the script is always the last return statement.
There are two possibilities :
The select count does not return the int value as I have put rowcount = sql.execute(query); and then compared the rowcount in if condition.
my quick 2 cents:
1- Have you tested the SQL query ? I would have use ‘like’ instead of ‘=’:
→ query = “select count(‘*’) from test.project where email like '”+loginemail+“’ and password like '”+loginpassword+“';”
2- I have found this on the internet ( Processing SQL Statements with JDBC )
→ execute: Returns true if the first object that the query returns is a ResultSet object.
I don’t know enough the sql package you use in the script. May be there is a misunderstanding about the “execute” method returned value.
The database is mysql and the query :
“select count(‘*’) from test.project where email like '”+loginemail+“’ and password like '”+loginpassword+“';”
runs fine when I run it in the mysql query browser.
I have used similar script to insert values from the form fields into the database and that works fine.
It seems that the query is correct but the result value that I am getting by running select count(*) query may be the cause of the issue.
Here again, just an idea: long time ago I solved a similar problem with a query such as:
select email from test.project where email like ‘“+loginemail+”’ and password like ‘“+loginpassword+”’;"
Indeed, there is no need to count the records. You just want to know whether there is a record or not.
This being said, may be there is also a misunderstanding here. I believe even with a count(), the execution of a select query returns a resultset and it will contain the number of records. The java code would look like:
query = "select count('') as nbRecords from test.project where…"
ResultSet rs = stmt.executeQuery(query);
if (rs.next()){
int nbOfRecords = rs.getInt(“nbRecords”); // if the number of records is required
…
return true
} else {
return false
}
Bonitasoft empowers development teams with Bonita, the open-source and extensible platform to solve the most demanding process automation use cases. The Bonita platform accelerates delivery of complex applications with clear separation between capabilities for visual programming and for coding. Bonita integrates with existing solutions, orchestrates heterogeneous systems, and provides deep visibility into processes across the organization.