You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ofbiz.apache.org by Madhi Krishnan <ma...@udyogmandir.in> on 2018/09/20 07:48:03 UTC
How to use Raw sql query in ofbiz
Hey all can anyone please tell me how to use raw sql query in ofbiz..
If you have any sources or example it will be better..
Thank You
Re: How to use Raw sql query in ofbiz
Posted by Nicolas Malin <ni...@nereide.fr>.
Hello
You can call directly your DAO with an sql, you can the
GenericDAO.select function
Or if it's only on where condition, when you create your EntityCondition
use EntityCondition.makeConditionWhere(mySqlCondition)
Nicolas
On 9/20/18 9:48 AM, Madhi Krishnan wrote:
> Hey all can anyone please tell me how to use raw sql query in ofbiz..
> If you have any sources or example it will be better..
> Thank You
>
Re: How to use Raw sql query in ofbiz
Posted by Madhi Krishnan <ma...@udyogmandir.in>.
Thank you guys...
That helped me a lot..
On Thu, Sep 20, 2018 at 2:59 PM Aditya Sharma <
aditya.sharma@hotwaxsystems.com> wrote:
> Hello Madhi Krishnan,
>
> I would recommend using OFBiz OOTB implementation for the database related
> operations.
>
> To fetch data from the database, you can use EntityQuery
> <
> https://www.hotwaxsystems.com/ofbiz/ofbiz-development/ofbiz-entity-query-api-simplifies-query-process/
> >
> API
>
> If you still intend to do it using SQL query. You can do that using
> SQLProcessor
> <
> https://ci.apache.org/projects/ofbiz/site/javadocs/org/ofbiz/entity/jdbc/SQLProcessor.html
> >
> class methods.
>
> Check out EntitySQLProcessor.groovy
> <
> https://svn.apache.org/repos/asf/ofbiz/ofbiz-framework/trunk/framework/webtools/groovyScripts/entity/EntitySQLProcessor.groovy
> >
> for
> reference.
>
> HTH
>
> Thanks and Regards,
>
> *Aditya Sharma* | Enterprise Software Engineer
> HotWax Systems <http://www.hotwaxsystems.com/>
> [image: https://www.linkedin.com/in/aditya-p-sharma/]
> <https://www.linkedin.com/in/aditya-p-sharma/>
>
>
> On Thu, Sep 20, 2018 at 2:12 PM Chandrabhan Pal <
> chandrabhan.pal@vnnogile.in>
> wrote:
>
> > Hello Madhi,
> >
> > You can try external connection to database ,find below reference code.
> >
> > try{
> > Class.forName("org.postgresql.Driver");
> > Connection connection =
> > DriverManager.getConnection("jdbc:postgresql://127.0.0.1/
> > "+"DB_Name","username","password");
> > //Below I have written sql on order_item entity
> > String rawSQLQuery= "select
> > product_id,count(product_id),item_description, sum(quantity*unit_price)
> > as item_value from order_item"+
> > " where created_stamp between 'startDate' and
> > 'endDate'"+
> > "group by item_description, product_id"+
> > " order by item_value desc limit 10";
> >
> > String rawSQLQuery1 = rawSQLQuery;
> >
> > PreparedStatement ps =
> > connection.prepareStatement(rawSQLQuery1);
> > ResultSet rs = ps.executeQuery();
> > while (rs.next()) {
> > //put you logic here with resultSet data
> > }
> > catch(Exception e){
> > //Handle Exception
> > }
> >
> > On 9/20/2018 1:18 PM, Madhi Krishnan wrote:
> > > Hey all can anyone please tell me how to use raw sql query in ofbiz..
> > > If you have any sources or example it will be better..
> > > Thank You
> > >
> >
> >
>
Re: How to use Raw sql query in ofbiz
Posted by Aditya Sharma <ad...@hotwaxsystems.com>.
Hello Madhi Krishnan,
I would recommend using OFBiz OOTB implementation for the database related
operations.
To fetch data from the database, you can use EntityQuery
<https://www.hotwaxsystems.com/ofbiz/ofbiz-development/ofbiz-entity-query-api-simplifies-query-process/>
API
If you still intend to do it using SQL query. You can do that using
SQLProcessor
<https://ci.apache.org/projects/ofbiz/site/javadocs/org/ofbiz/entity/jdbc/SQLProcessor.html>
class methods.
Check out EntitySQLProcessor.groovy
<https://svn.apache.org/repos/asf/ofbiz/ofbiz-framework/trunk/framework/webtools/groovyScripts/entity/EntitySQLProcessor.groovy>
for
reference.
HTH
Thanks and Regards,
*Aditya Sharma* | Enterprise Software Engineer
HotWax Systems <http://www.hotwaxsystems.com/>
[image: https://www.linkedin.com/in/aditya-p-sharma/]
<https://www.linkedin.com/in/aditya-p-sharma/>
On Thu, Sep 20, 2018 at 2:12 PM Chandrabhan Pal <ch...@vnnogile.in>
wrote:
> Hello Madhi,
>
> You can try external connection to database ,find below reference code.
>
> try{
> Class.forName("org.postgresql.Driver");
> Connection connection =
> DriverManager.getConnection("jdbc:postgresql://127.0.0.1/
> "+"DB_Name","username","password");
> //Below I have written sql on order_item entity
> String rawSQLQuery= "select
> product_id,count(product_id),item_description, sum(quantity*unit_price)
> as item_value from order_item"+
> " where created_stamp between 'startDate' and
> 'endDate'"+
> "group by item_description, product_id"+
> " order by item_value desc limit 10";
>
> String rawSQLQuery1 = rawSQLQuery;
>
> PreparedStatement ps =
> connection.prepareStatement(rawSQLQuery1);
> ResultSet rs = ps.executeQuery();
> while (rs.next()) {
> //put you logic here with resultSet data
> }
> catch(Exception e){
> //Handle Exception
> }
>
> On 9/20/2018 1:18 PM, Madhi Krishnan wrote:
> > Hey all can anyone please tell me how to use raw sql query in ofbiz..
> > If you have any sources or example it will be better..
> > Thank You
> >
>
>
Re: How to use Raw sql query in ofbiz
Posted by Chandrabhan Pal <ch...@vnnogile.in>.
Hello Madhi,
You can try external connection to database ,find below reference code.
try{
Class.forName("org.postgresql.Driver");
Connection connection =
DriverManager.getConnection("jdbc:postgresql://127.0.0.1/"+"DB_Name","username","password");
//Below I have written sql on order_item entity
String rawSQLQuery= "select
product_id,count(product_id),item_description, sum(quantity*unit_price)
as item_value from order_item"+
" where created_stamp between 'startDate' and
'endDate'"+
"group by item_description, product_id"+
" order by item_value desc limit 10";
String rawSQLQuery1 = rawSQLQuery;
PreparedStatement ps =
connection.prepareStatement(rawSQLQuery1);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
//put you logic here with resultSet data
}
catch(Exception e){
//Handle Exception
}
On 9/20/2018 1:18 PM, Madhi Krishnan wrote:
> Hey all can anyone please tell me how to use raw sql query in ofbiz..
> If you have any sources or example it will be better..
> Thank You
>