?
Solved

want to make a java string

Posted on 2012-08-19
32
Medium Priority
?
449 Views
Last Modified: 2012-10-08
Is there any tool/tricks which can help me to make a java String quickly from a long database sql query ?

my database sql query is 28 line....something like this ..

Select
  blah,
  blah,
  blah
From
  table1 inner join table 2 ...blah blah
Where
  blah blah
Group By
  blah blah


I want to make a java String from this query ..... I can use '+'  for concatenation and make a java string manually...but would appreciate if there is a tool /tricks which can make it very quickly.

is there any time saving approach ?
0
Comment
Question by:cofactor
  • 16
  • 13
  • 3
32 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 38309459
In these cases i usually put it in a file. You could make it a Formatter style format string if you need placeholders in it.

http://technojeeves.com/joomla/index.php/free/93-file-to-string-in-java
0
 

Author Comment

by:cofactor
ID: 38309471
>>In these cases i usually put it in a file.

I dont want to  read files and build String



Is it not possible to format it to a String variable using editplus / textpad or using a text editor  by applying some tricks ?
0
 

Author Comment

by:cofactor
ID: 38309493
>>> >>In these cases i usually put it in a file.

I will give this a try ....let me check if this works out.

But I'm worried because  I will  have  more than 30 sql database queries ...for all of them I want to make java String variable.....do I need to make separate  file for each query  then ?

If  this can be done using one  file only...then how do I manage multiple  database queries using a single file ?

Please comment.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:cofactor
ID: 38309494
>>>You could make it a Formatter style format string if you need placeholders in it.

did not get this part.  

you know  my  database queries  will have '?'  in it ....Do I need to use Formatter style ? Not clear what you meant here.
0
 

Author Comment

by:cofactor
ID: 38309860
comments please
0
 
LVL 7

Expert Comment

by:humanonomics
ID: 38310479
AFAIK, There is no direct way of doing what u desire.

you will have to manually build up the string if you don't want to use the workaround specified by Cehj.

You can create your own method, that would parse any resultset and return you the string representation of all the rows present in the resultset, to save on resource creation I would suggest usage of StringBuffer or StringBuilder (whatever suits your design), as they are mutable as against String which are mutable and has eat up the resources.

Thanks.

H.
0
 

Author Comment

by:cofactor
ID: 38310568
>>>if you don't want to use the workaround specified by Cehj.

chej's approach  does not address  multiple queries ?  For single query that is fine.

I have many database queries .  Does that mean I  have to create separate file for each query  in Checj's  approach ?
0
 
LVL 7

Expert Comment

by:humanonomics
ID: 38310586
correction to my earlier post 'String' is immutable in Java
0
 
LVL 7

Expert Comment

by:humanonomics
ID: 38310590
you are fetching the data with your Java application? How are you pushing your data in to the file?
0
 

Author Comment

by:cofactor
ID: 38310592
>>>>How are you pushing your data in to the file?

write the query  in TOAD  and copy query and paste in to the file  :)
0
 
LVL 7

Expert Comment

by:humanonomics
ID: 38310594
And how do you intend to use the output, in Java or in a file?
0
 
LVL 7

Expert Comment

by:humanonomics
ID: 38310597
ignore my previous comment.
0
 
LVL 7

Expert Comment

by:humanonomics
ID: 38310599
What do you do with your results in String? are they needed in specific format? if the format in which you want string representation is same for all the queries, then you can paste all your data in the same file :)
0
 

Author Comment

by:cofactor
ID: 38310610
>>>What do you do with your results in String?
what results ?  I did not get your point.


keep it simple ....let me rephrase my query again .... I have many big sql  queries ...and I want to make  java String's  out of them so that I can use these query Strings in PreparedStatement.  

(I dont want to do manually string concat  using millions of "+"'s and quotes which is unreadable)
0
 
LVL 7

Expert Comment

by:humanonomics
ID: 38310619
Ok I got it now, I got confused a bit earlier, well in this case, I generally use text editors like, textpad/editplus/notepad++, copy paste the query in these text editors and write macro(s), to change its format and representation and then copy paste it in Java.

The other option would be to copy paste the whole query as a String, and then break it up, if u are using an IDE, u will not need to put '+' manually, hitting enter, where you want to break the query will automatically do it.
0
 

Author Comment

by:cofactor
ID: 38310624
>>> I generally use text editors like, textpad/editplus/notepad++, copy paste the query in these text editors and write macro(s), to change

I  have textpad and editplus.  Do you have macro to check this out ?


>>> if u are using an IDE, u will not need to put '+' manually, hitting enter, where you want to break the query will automatically do it.

No.  It wont work . because ...after every break you need to close the string and then need to use '+' to continue.    I'm using Eclipse  IDE.
0
 
LVL 7

Expert Comment

by:humanonomics
ID: 38310640
nopes, I dont have any handy macros at the moment, will need to create one. But when I do hit enter within a string Eclipse does put the closing " for the current line, opening " for the new line and + in between.
0
 

Author Comment

by:cofactor
ID: 38310644
>>>But when I do hit enter within a string Eclipse does put the closing " for the current line, >>opening " for the new line and + in between.

sorry ..it does.   my mistake.

But you know this may not be good approach ....because if tomorrow I change the database query ..then again I  have to come back to java file  and modify and build it.

However putting query in a separate  file is a good approach ...because anytime you can change the query and you are ready to go .... no need to touch java file ....but I'm stuck because I dont find a workaround for handling multiple queries if put all of them in a single file.

I found an  example in google search : ...see this ...

http://stackoverflow.com/questions/370818/cleanest-way-to-build-an-sql-string-in-java

author has suggested to put all the queries in a property file ...and read queries from property file.

I'm doubtful to this ...because this may not work for queries with multiple lines ...what do you say ?
0
 
LVL 7

Expert Comment

by:humanonomics
ID: 38310657
it may work, because properties work as name value pair, so you can have similar names for one query on multiple lines with names having a number as suffix.
for eg

Firstquery1=Select blah, blah, blah
Firstquery2=blah, blah from table
Firstquery3=where blah=blah;
Secondquery=Select * from table;


The other way would be to to provide delimiters in your file seperating the queries, and then using those delimiters you can do a split on the whole big String, and get multiple strings as the out come.

Say if you input file is as below:

Select blah, blah
blah, blah
from table
where blah=blah;
- - - -
Select * from table;


Then u can read it in one big string using Chej's solution
and the result u get u can perform the following stuff on it:
String queries[] = result.split("----");

Open in new window

0
 

Author Comment

by:cofactor
ID: 38310677
>>Say if you input file is as below:

Select blah, blah
blah, blah
from table
where blah=blah;
- - - -
Select * from table;

Open in new window


This looks good ...but it has few drawbacks.  I  want to give the query a name ..otherwise it would be hard to maintain tomorrow ....without a query name I may not be able to understand which query is for what purpose.

so something like this is a good idea ...just giving some name to queries


GET_ALL_PROJECT_DETAILS=Select blah, blah
blah, blah
from table
where blah=blah;
- - - -
GET_ALL_EMPLOYEE_DETAILS=Select * from table;

Open in new window


but then again I need two splits ...with "-----"  and with "="  ....this looks  bit  ugly here.

Also I may want to add some comment in the file ....but here you can not put comment in a normal text file.

so this does not look like best solution.

Can we improve it or think something better ?
0
 
LVL 7

Expert Comment

by:humanonomics
ID: 38310687
then in that case, I would suggest reading the input file line by line, that way you can ignore comments (specify a special character at the beginning and ignore it in Java, when u read that line, that way you can also have a header/query name specified with different special character.
0
 
LVL 7

Expert Comment

by:humanonomics
ID: 38310690
for eg: the input file somewhat like below:

//This is a comment
:GET_ALL_PROJECT_DETAILS
Select blah, blah
blah, blah
from table
where blah=blah;
- - - -
//This is second comment
:GET_ALL_EMPLOYEE_DETAILS
Select * from table;
0
 

Author Comment

by:cofactor
ID: 38310700
>>>then in that case, I would suggest reading the input file line by line, that way you can ignore comments (specify a special character at the beginning and ignore it in Java, when u read that line,

no ....this is not a good idea  because I may be having many queries .

say you need a query to run  which is at the bottom of the file .... you should not read the entire file to reach to the bottom and pick that.  This approach is complicated and error prone  although can be manipulated in someway.

any other thoughts ?
0
 
LVL 7

Expert Comment

by:humanonomics
ID: 38310708
Another solution is to create a database table, with columns QueryName, Query, Comment

And write a Javacode to fetch to the desired query form the db and execute it.

Easier to read, and maintain.
0
 

Author Comment

by:cofactor
ID: 38310723
>>>> Another solution is to create a database table, with columns QueryName, Query, Comment

Looks decent. I was thinking about it also.

let me try this ...I'll revert back after testing
0
 
LVL 7

Expert Comment

by:humanonomics
ID: 38310756
ok, cool.
0
 

Author Comment

by:cofactor
ID: 38311052
>>> with columns QueryName, Query, Comment

I have made Query as a BLOB field in MYSQL db .  

There  is no resultset.getBlob("Query ")  in java  to get the query string back.

any input ?
0
 
LVL 7

Expert Comment

by:humanonomics
ID: 38311881
There is, what JDK are you using? Whats your Java Version?
0
 
LVL 7

Expert Comment

by:humanonomics
ID: 38311922
Infact getBlob is present since Java 1.2 here is the Javadocs:


getBlob
Blob getBlob(int columnIndex)

             throws SQLException
Retrieves the value of the designated column in the current row of this ResultSet object as a Blob object in the Java programming language.
Parameters:
columnIndex - the first column is 1, the second is 2, ...
Returns:
a Blob object representing the SQL BLOB value in the specified column
Throws:
SQLException - if the columnIndex is not valid; if a database access error occurs or this method is called on a closed result set
SQLFeatureNotSupportedException - if the JDBC driver does not support this method
Since:
1.2


and


getBlob
Blob getBlob(String columnLabel)
             throws SQLException

Retrieves the value of the designated column in the current row of this ResultSet object as a Blob object in the Java programming language.
Parameters:
columnLabel - the label for the column specified with the SQL AS clause. If the SQL AS clause was not specified, then the label is the name of the column
Returns:
a Blob object representing the SQL BLOB value in the specified column
Throws:
SQLException - if the columnLabel is not valid; if a database access error occurs or this method is called on a closed result set
SQLFeatureNotSupportedException - if the JDBC driver does not support this method
Since:
1.2
0
 
LVL 7

Expert Comment

by:humanonomics
ID: 38312227
Try the following:
[i]
                    Blob blob = rs.getBlob("Query");
	            DataInputStream inputStream = null;
	            byte[] dataBuffer = new byte[(int) f.length()];
	            inputStream = new DataInputStream(blob.getBinaryStream());
	            
	            inputStream.readFully(dataBuffer);
	            
	            String blobString = new String(dataBuffer);[/i]

Open in new window

0
 
LVL 86

Accepted Solution

by:
CEHJ earned 1400 total points
ID: 38313559
Using another db table is a good idea. Make sure you use a PreparedStatement to fill it.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 38475603
:)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Suggested Courses
Course of the Month14 days, 6 hours left to enroll

807 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