Solved

dynamic query builder in java

Posted on 2009-04-13
5
1,716 Views
Last Modified: 2013-11-23
hi
i am new to java,i have a task for which i need some assistance from experts.
i want to design a query builder framework which could return queries just by passage of few parameters. like i pass a list of column names to the method and it returns a generic insert query for the table . i need it such a way that a single ethod works for any table. that is all info is passed through list of column names. please help me.

this is the sample code i have written so far
public StringBuffer LoadMetadataQuery(String TableName,ArrayList Tabledata,String PrimaryKey)

	{

	

	StringBuffer InsertQuery =new StringBuffer("Insert into" +TableName + "(");

	

	String colName="";

	String colValueInsert="";

	String colnameQry =  "";

	String colValue = "";

	List<String> TableCols = new ArrayList <String>();

	

	try

	{

		

		for(int i=0;i< TableCols.size();i++)

		{

		

			colName = TableCols.get(i);

			String delimiter =",";

			

			colnameQry.concat( TableCols.get(i) + delimiter);

			colValue.concat("?" + delimiter);

			

		}

		

		InsertQuery.append(colName +",") ;

		

		

	} catch(Exception e){

		

		//to do

	}

	

	return InsertQuery;

	}

Open in new window

0
Comment
Question by:manojvashisht
  • 3
  • 2
5 Comments
 
LVL 5

Expert Comment

by:avya2k
ID: 24129541
yes you can do this
I had build dynamic query but for select

In your case you can use either ResultSetMetaData to get list of columns and their names
if you dont want to pass list of columns. for this you have to execute select * from <table> query which will return you all columns

See the code attached
	public static String loadMetadataQuery(Connection con, String tableName,

			ArrayList tableData, String primaryKey) throws SQLException {
 

		StringBuffer insertQuery = new StringBuffer("INSERT INTO " + tableName

				+ " (");

		StringBuffer insQueryValues = new StringBuffer("VALUES (");

		String pk=null;

		Statement st = null;

		ResultSet rs=null;

		

		try {

			st=con.createStatement();

			rs = st.executeQuery("SELECT * FROM " + tableName);

			ResultSetMetaData rsm = rs.getMetaData();

			

			for (int i = 1; i <= rsm.getColumnCount(); i++) {				

				if(rsm.isAutoIncrement(i)) continue;//Skip auto increment columns

			

				insertQuery = insertQuery.append(rsm.getColumnName(i));

				insQueryValues = insQueryValues.append("?");

				if (i < rsm.getColumnCount()) {

					insertQuery = insertQuery.append(",");

					insQueryValues = insQueryValues.append(",");

				}

			}

			insertQuery = insertQuery.append(") " + insQueryValues + ")");

		} finally {

			rs.close();

			st.close();

		}

		return insertQuery.toString();

	}

Open in new window

0
 
LVL 5

Expert Comment

by:avya2k
ID: 24129547
I dont know the purpose of tableData and primaryKey
but you can use them at appropriate place
0
 

Author Comment

by:manojvashisht
ID: 24129647
hi
thanks for your response but what would you suggest if i keep on iterating it for list of columns passed as parameter,because as such i am getting that list from a Xml and ot by querying the DB. in that case i think a minor changes are required in the method and i have to get away with rs thing and iterate for list.

please if you could help in the change logic in loop.

and also suggest what needs to be done if a similar method is to be designed for update query at runtime.
0
 

Author Comment

by:manojvashisht
ID: 24129709
i have changed the code like this,do u feel this is the right way to do
public static String loadMetadataQuery(Connection con, String tableName,ArrayList tableData, String primaryKey) throws SQLException 
 

{
 

            StringBuffer insertQuery = new StringBuffer("INSERT INTO " + tableName + " (");

            StringBuffer insQueryValues = new StringBuffer("VALUES (");

            String pk=null;

            Statement st = null;

            ResultSet rs=null;

            

            try {

//                    st=con.createStatement();

//                    rs = st.executeQuery("SELECT * FROM " + tableName);

//                    ResultSetMetaData rsm = rs.getMetaData();

                    

                    for (int i = 1; i <= tableData.size(); i++) {                               

                            

                    

                            insertQuery = insertQuery.append(tableData.get(i));

                            insQueryValues = insQueryValues.append("?");

                            if (i < tableData.size()) {

                                    insertQuery = insertQuery.append(",");

                                    insQueryValues = insQueryValues.append(",");

                            }

                    }

                    insertQuery = insertQuery.append(") " + insQueryValues + ")");

            } catch (Exception e) {

				// TODO: handle exception

			}

            

            return insertQuery.toString();

    }

Open in new window

0
 
LVL 5

Accepted Solution

by:
avya2k earned 250 total points
ID: 24134945
That can be ok if table structure is final, it may happen that during maintainance and enhancements some of the tables get modified but their PK remains same
So better to read from MetaData Only

Further if you are sure that your table is not going to change or if that table can be extended in another table then you can go with passing parameter

In your code con is also not needed

For update query you need to pass the list of columns to be updated and columns in where clause separately you can iterate both the collections and generate the query
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now