Best way to create File for each Letter from SQL

I am trying to create an xml file for each letter of the alphabet + special characters with results from a query that I wrote.:

Select * from people; (random example)

example:

hello_a.xml
hello_b.xml
hello_spec.xml

I have the query done, which returns all the rows at the moment. I just want to know the best way to do this. Do I make a list of Strings containing each variable and do a where table_column LIKE A% ? or is there a better way to do it. Right now I have the List im开发者_StackOverflow中文版plementation, but i cannot seem to find the SQL section for the special characters.

When I say special characters I mean "&", "%", or numeric values.

Edit: Using JAXB to write xml to the file.

---

It's not entirely clear so I just guess you want to write the results of this query:

SELECT * FROM people

divided into 27 separate files by what the first character of a certain column is.

The following is not Java, only pseudocode:

filenamePrefix = "hello_"
filenamePostfix = ".xml"

letters = "abc...xyz"

for letter in letters
   upper = Uppercase(letter)
   sqlquery = "SELECT * FROM people WHERE column LIKE '" + letter + "%' 
                                       OR column LIKE '" + upper  + "%' ;"
   results = runquery( sqlquery )
   filename = filenamePrefix + letter + filenamePostfix
   saveToFile( filename , results )

sqlquery = "SELECT * FROM people
            WHERE NOT REGEXP_LIKE(column, '^[[:alpha:]]', 'i') ;" 
                    /* 'i' stands for case Insensitive */ 
results = runquery( sqlquery )
filename = filenamePrefix + "spec" + filenamePostfix
saveToFile( filename , results )

---

You could change your query to something like this:

select substr(name, 1, 1) as alpha, * from people order by alpha

Then you could iterate over the result set and create file for each new character in alpha column of the result set.

This is sample code in Java that illustrates the approach:

Statement s = connection.createStatement();
String q = "select substr(name, 1, 1) as alpha, * from people order by alpha"
ResultSet rs = st.executeQuery(q);

FileWriter fw = null;
char alpha = null;
while(rs.next()) {
    char newAlpha = rs.getString("alpha").charAt(0);
    if(newAlpha != alpha) {
        alpha = newAlpha;
        if(fw != null) {
            fw.close();
        }
        String fn = String.format("hello_%s.xml",
            Character.isLetterOrDigit(alpha) ? alpha : "spec");
        fw = new FileWriter(new File(fn));
    }
    //write record to open file writer
    //your JAXB code goes here
    //you should not iterate result set
}
fw.close();

Bear in mind that the code hasn't been tested so may not work nor compile straightaway, but I hope it quite well explain the approach.

---

The way I see it, it would be much easier to not try and do it all with SQL. Use the query you have and order by name (or whatever field you are using). Here is some psuedo-code that will do the grouping for you.

records = SQL_RESUT;
specialCharResults = new List();
currentCharResults = new List();
currentChar = "A";
for (record : records) {
  if (record.name starts with special) {
    specialCharResults.add(record);
  } else {
    if (record.name does not starts with currentChar) {
      write out currentCharResults;
      currentCharResults = new List();
      currentChar = record.name first char;
    } else {
      currentCharResults.add(record);
    }
 }
 write out currentCharResults; //currentChar never changes away from "Z"
 write out specialCharResults;