How to use Raw sql query in ofbiz

[Madhi Krishnan]

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

[Nicolas Malin-2]

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
>

[Chandrabhan Pal]

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
>

[Aditya Sharma]

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 <[hidden email]>
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
> >
>
>

[Madhi Krishnan]

Thank you guys...
That helped me a lot..

On Thu, Sep 20, 2018 at 2:59 PM Aditya Sharma <
[hidden email]> 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 <
> [hidden email]>
> 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
> > >
> >
> >
>