EJB and Dynamic/Ad-hoc Queries
I'm fairly new to EJB and would like suggestions on a project that I wanted to start.
Let me give you the scenario and I抣l build my questions from there.
We need to build an Ad-hoc Reporting Application, which allows users to create their own query and run that query. We have two types of users, general and experts. The general users generate their reports against views, while the experts are allowed to join any tables in the database or whatever tables are available to them. This will be mainly read-only process; unless, in the future we have a need for update, insert, delete, and etc to the database.
My question is what is the best solution/design to this scenario?
Should I use:
Session Bean to DAO?
Session Bean to Entity Bean (CMP or BMP)?
Or just straight JDBC/DAO?
If this a repeat question, I apologies, just guide me to the thread if this was already posted somewhere in the forum.
[960 byte] By [
amy1ima] at [2007-9-19]
Hi,
IMO, Using EJB will not be much helpful. They are configured to work with tables at the deployment time. So of the three option the best will be
session ejb and DAO
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Thanks
Ashwani Kalra
http://www.geocities.com/ashwani_kalra/
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Actually, this would work great as a stateless session bean. You can setup a method such as
ResultSet RunSQL(String strSQL) and simply pass it your SQL parameter. Or what I did in my code was to also allow for sending it only the WHERE parameter because I always wanted to get back everything. So my call is
ResultSet rs;
rs = myejb.RunSQL("WHERE FNAME='john'")
Works like a charm!
Here's an example
public static ResultSet runEmpDemogSQL(String strSQL)
{
if (strSQL == "")
{
return null;
}
try
{
Driver myDriver = (java.sql.Driver)Class.forName "weblogic.jdbc.mssqlserver4.Driver").newInstance();
Properties props = new Properties();
props.put("user","username");
props.put("password", "password");
props.put("db","database");
props.put("server","server");
props.put("port","1400");
Connection conn = myDriver.connect("jdbc:weblogic:mssqlserver4", props);
Statement stmt = conn.createStatement();
strSQL = strSQL.toUpperCase();
if (strSQL.indexOf("WHERE") == 0)
{
strSQL = "select * from mytable" + strSQL;
}
ResultSet rs = stmt.executeQuery(strSQL);
return rs;
}
catch (Exception e)
{
System.out.println("
Error with MS-SQL connection
");
return(null);
}
}
Hi! Guys,
thank you for your suggestions... I finally decided to work with Session Bean to DAO...but please keep your suggestions coming.
To wbraken:
I'm actually using Webfocus and it is what I'm trying to replace (obcourse not all it's features). My problem with 3rd party tools is that we tend to reach the dead-ends all the time and not to mention the hefty price. I've had so many problems with Webfocus in terms of Dates, Date-time in Oracle, Large text, display formats(pdf/excel/doc), and etc. But, don't get me wrong it's a good tool, but it's hard to costomized or accommodate different user needs, or at least for our users.
Thanks,
Amy
