Forum:

JDBC and Relational Databases

Get results from user defined query

Greenhorn

Posts: 10

posted 12 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

hi,
i am new to servlets and i am doing a small jdbc application, in that i want query to be fired from textarea(html) and only those columns and values to be showed in the table on the same page. my problem is how can i get the column name that are affected by query ... and also insertion and deletion,update queries to be excuted ....

Rob Spoor

Sheriff

Posts: 22784

131

I like...

posted 12 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

One way I have used to get all results from a statement, whether that is a result set, update count or a mixture of the two, is this:

If you have a ResultSet, you can get its matching ResultSetMetaData object. With this you can retrieve the column names, types, etc. Just remember that columns start with 1, not 0.

SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6 - OCEJPAD 6
How To Ask Questions How To Answer Questions

Tim Moores

Saloon Keeper

Posts: 7590

177

posted 12 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

my problem is how can i get the column name that are affected by query

Through the ResultSetMetaData (which you can obtain from the ResultSet).

Paddy Joshi

Greenhorn

Posts: 10

posted 12 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

hi,
statement.execute();
this statement does not execute ...error : The method execute(String) in the type Statement is not applicable for the arguments ()

the query works for selection purpose only my table has columns id,first,last,dob,phone,email,address ....
select id from employee; gives error column 'first ' not found

how can i differentiate between selection ,delete or update queries ... i am using eclipse javaee
please help me with the code if you can ... my code is ...

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
	{
		// TODO Auto-generated method stub
		response.setContentType("text/html");
		PrintWriter out = response.getWriter();
		
		String query = request.getParameter("query"); 
		boolean done = false;  
		try 
		{
			statement.execute(); // this won't work         but          this works:  statement.execute(query); 
			int columnCount = 0;	
			 // universal execute method; ignore return value  
			while (!done)  
			{  
			    resultSet = statement.getResultSet();			    
			    if (resultSet == null)  
			    {  
			        int count = statement.getUpdateCount();  
			        if (count == -1)  
			        {  
			            // no result set or update count so all results are handled  
			            done = true;  
			        }  
			        else  
			        {  
			            // count indicates the number of inserted, updated or deleted records; or 0 for other statements  
			            // handle  
			        	columnCount = count;
			        	
			        }  
			    }  
			    else  
			    {  
			        // handle rs  
			    	out.print("<html><body><table border = 2 align = center>");
		        	
		        	
		        	metaData = resultSet.getMetaData();
		        	out.print("<tr>");
		        	for(int i = 1; i <= columnCount; i++ )
		        	{
		        		out.print("<b><th>"+metaData.getColumnName(i)+"</th></b>");
		        	}
		        	out.print("</tr>");
		        	
		        	while(resultSet.next())
		        	{
		        		out.print("<tr>");
		        		
		        		out.print(
		        				"<td>"+resultSet.getString("emp_id")+"</td>"+
		        				"<td>"+resultSet.getString("first_name")+"</td>"+
		        				"<td>"+resultSet.getString("last_name")+"</td>"+
		        				"<td>"+resultSet.getString("birth_date")+"</td>"+
		        				"<td>"+resultSet.getString("phone")+"</td>"+
		        				"<td>"+resultSet.getString("email")+"</td>"+
		        				"<td>"+resultSet.getString("address")+"</td>");        		
		        		out.print("<tr>");
		        	}
		        	out.print("</table></body></html>");
			    } 			   
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}

[Edit - added code tags - see UseCodeTags for details]

Rob Spoor

Sheriff

Posts: 22784

131

I like...

posted 12 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Paddy Joshi wrote:statement.execute();
this statement does not execute ...error : The method execute(String) in the type Statement is not applicable for the arguments ()

Sorry, my bad. I've worked with PreparedStatement so often I forgot that Statement doesn't have the execute() method, only execute(String). So in my code, just put the query inside the brackets.

One word of caution though. If you don't validate the queries users will be able to execute anything; that includes deleting records, dropping and creating tables, etc.

SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6 - OCEJPAD 6
How To Ask Questions How To Answer Questions

Wendy L Gibbons

Bartender

Posts: 1111

I like...

posted 12 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

if your user can go across tables, this question has been extensivly discussed in this thread (warning major spoilers)

https://coderanch.com/t/565876/JDBC/java/generate-queries-random-joins

Martin Vashko

Sheriff

Posts: 3837

I like...

posted 12 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Paddy Joshi wrote:hi,
i am new to servlets and i am doing a small jdbc application, in that i want query to be fired from textarea(html) and only those columns and values to be showed in the table on the same page. my problem is how can i get the column name that are affected by query ... and also insertion and deletion,update queries to be excuted ....

I'd say this is not going to be a "small JDBC application", to the contrary, this is going to be a large one, comparable to Toad or SqlDeveloper.

Actually, if you're on Oracle, the iSQL*Plus might cover your needs (though I don't have any experience with it), other databases might have a web client too.

Rob Spoor

Sheriff

Posts: 22784

131

I like...

posted 12 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Martin Vajsar wrote:

Paddy Joshi wrote:hi,
i am new to servlets and i am doing a small jdbc application, in that i want query to be fired from textarea(html) and only those columns and values to be showed in the table on the same page. my problem is how can i get the column name that are affected by query ... and also insertion and deletion,update queries to be excuted ....

I'd say this is not going to be a "small JDBC application", to the contrary, this is going to be a large one, comparable to Toad or SqlDeveloper.

Not necessarily. I've created a similar application, which allowed me to execute any query on nearly any database connection. It had just two parts (apart from logging in) - an editor part and a results part. The latter would just contain text areas and tables, nothing else.

SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6 - OCEJPAD 6
How To Ask Questions How To Answer Questions

Don't get me started about those stupid light bulbs.