JAVA & SQL Automation

I have 5 Quiries which will pull the data from the oracle database and show the results

Now I am planning to work on a project like as below

1. JAVA program to use the Quries and pull those information and
2. Write into a spreadsheet with individual sheet names and
3. Finally send out an mail to the team with the link where I have copied the sheets.

Please share your thoughts

Thanks
Venkatesh.
venkatesh SarivisettySenior Software EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Geert GOracle dbaCommented:
why not create a spreadsheet with a query per sheet ?

when they hit refresh in the sheet, the data gets reloaded.
Seems way easier
0
venkatesh SarivisettySenior Software EngineerAuthor Commented:
How to do that , As I am fresher in JAVA and SQL DB combination . Please share any samples
0
venkatesh SarivisettySenior Software EngineerAuthor Commented:
Also the final should be it should work as automated,, every day the team should the mail notification with the output what they are seeing in the spreadsheet.. Hope you have understood the req..
Let me know if you need more details.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Geert GOracle dbaCommented:
there are many examples of how to connect excel to an oracle database

sample via lmgtfy:
http://bfy.tw/Grwa
0
Geert GOracle dbaCommented:
there is no point for the mail
just open the spreadsheet and hit refresh
0
venkatesh SarivisettySenior Software EngineerAuthor Commented:
it is asking user name and password. Datasorce and Location
from where I can get this information..
0
Geert GOracle dbaCommented:
from the same place you run your query ?
0
venkatesh SarivisettySenior Software EngineerAuthor Commented:
No this is not my requirment. Thanks for your help!!!
I am looking for a java script which can be helpful to write into the spreadsheet and send the mail to the team

Thanks
Venkatesh.
0
venkatesh SarivisettySenior Software EngineerAuthor Commented:
Here is my javascript desinged to write the records into a excel sheet for one query and one sheet1

How I can add 2,3,4,5,6 queries and add in sheet 2,3,4,5,6 in excel . Please add in the below code and share to me

public class tableCheck
{
      public static void main(String[] args) {
            OnlyIDCheck sqlExcel = new OnlyIDCheck();
            String sqlQuery = "select * from table";
            
            ResultSet rs = sqlExcel.getResultsetFromSql(sqlQuery);
            sqlExcel.generateExcel(sqlExcel.processResultSet(rs), "tableCheck");
      }

      /**
       * This method returns a database connection given the necessary parameters
       * to create the connection.
       */
      public Connection getDBConnection() {
            Connection conn = null;
            try {
                  System.out.println("DB Connection got eshtablished..");
                  Class.forName("oracle.jdbc.driver.OracleDriver");
                  conn = DriverManager.getConnection(
                              "jdbc:oracle:thin:@//localhost:xe/config",
                              "username", "password");
                  System.out.println("DB Connection eshtablished successfully!!");
            } catch (SQLException e) {
                  e.printStackTrace();
            } catch (ClassNotFoundException e) {
                  e.printStackTrace();
            }
            return conn;
      }

      /**
       * This method returns a ResultSet for the given sql query.
       */
      public ResultSet getResultsetFromSql(String sql) {
            Connection conn = getDBConnection();
            ResultSet rs = null;
            try {
                  System.out.println("Executing the Query");
                  Statement stmt = conn.createStatement();
                  /** System.out.println("Sql"+sql);*/
                  rs = stmt.executeQuery(sql);

            } catch (SQLException e) {
                  e.printStackTrace();
            }
            return rs;
      }

      /**
       * This method returns a Map with keys as row numbers and values as another
       * LinkedHashMap containing key as column name and value as column value ,
       * present in the ResulSet. We have used LinkedHashMap because it maintains
       * the order in which the values are put in the Map.
       */
      public Map<String, LinkedHashMap<String, String>> processResultSet(
                  ResultSet rs) {
            LinkedHashMap<String, String> rowDetails = new LinkedHashMap<String, String>();
            Map<String, LinkedHashMap<String, String>> resultMap = new LinkedHashMap<String, LinkedHashMap<String, String>>();
            ResultSetMetaData rsm = null;

            try {
                  if (rs != null)
                        rsm = (ResultSetMetaData) rs.getMetaData();

                  int rowCount = 1;
                  while (rs.next()) {
                        System.out.println("Records are Started Loading from DB to File");
                        for (int i = 1; i <= rsm.getColumnCount(); i++) {
                              rowDetails.put(rsm.getColumnName(i), rs.getString(i));
                              /** System.out.println(rsm.getColumnName(i) + ":"
                                          + rs.getString(i));*/
                        }
                        resultMap.put(new Integer(rowCount).toString(), rowDetails);
                        rowCount++;
                        rowDetails = new LinkedHashMap<String, String>();
                  }
            } catch (SQLException e) {
                  e.printStackTrace();
            }
            return resultMap;
      }

      /**
       * This method generates an excel sheet containing data from the given Map.
       * The name of the excel sheet will be the String passed as a parameter.
       */
      @SuppressWarnings("deprecation")
      public void generateExcel(
                  Map<String, LinkedHashMap<String, String>> resultMap, String name) {
            FileOutputStream fileOut = null;

            try {

                  try {
                        Calendar cal = Calendar.getInstance();
                        cal.add(Calendar.DATE, 0);
                        SimpleDateFormat format1 = new SimpleDateFormat(
                                    "dd-MM-YYYY HH-MM");
                        String formatted = format1.format(cal.getTime());
                        fileOut = new FileOutputStream(
                                    "Path//Registrations_"
                                                + formatted + ".xls");
                  } catch (FileNotFoundException e) {
                        e.printStackTrace();
                  }

                  HSSFWorkbook hwb = new HSSFWorkbook();
                  HSSFSheet sheet = hwb.createSheet(name);

                  // sheet.createRow((short)0);

                  Map<String, LinkedHashMap<String, String>> rMap = resultMap;
                  Map<String, String> columnDetails = rMap.get("1");

                  Set<String> s = columnDetails.keySet();
                  int cellNo = 0;
                  HSSFRow row = sheet.createRow(cellNo);
                  HSSFCellStyle cellStyle = hwb.createCellStyle();
                  cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
                  cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                  HSSFFont font = hwb.createFont();
                  font.setBold(true);
                  cellStyle.setFont(font);
                  for (String s1 : s) {
                        HSSFCell cell = row.createCell((short) cellNo);
                        cell.setCellValue(s1);
                        cell.setCellStyle(cellStyle);
                        cellNo++;
                  }
                  if (rMap.size() > 0) {
                        System.out.println("Records loading got completed");
                  }
                  for (int i = 1; i <= rMap.size(); i++) {
                        columnDetails = rMap.get(new Integer(i).toString());
                        HSSFRow nextrow = sheet.createRow(i);
                        Set<String> set = columnDetails.keySet();
                        int cellNum = 0;
                        for (String s2 : set) {

                              nextrow.createCell((short) cellNum).setCellValue(
                                          columnDetails.get(s2));

                              cellNum++;
                        }
                  }

                  hwb.write(fileOut);
                  fileOut.flush();
                  fileOut.close();
            } catch (FileNotFoundException fe) {
                  fe.printStackTrace();
            } catch (IOException e) {
                  e.printStackTrace();
            } finally {
                  try {
                        fileOut.flush();
                        fileOut.close();
                  } catch (IOException e) {
                        e.printStackTrace();
                  }
            }
      }
}
0
slightwv (䄆 Netminder) Commented:
I'm not a Java person but if you can create a spreadsheet with one query, it should be a simple matter to add a loop to run through several queries and create a new sheet on each loop.

Since you say you are new to Java, where did the Java requirement come from?  Do you not have Java developers that work with you that can help?

I posted some .Net code back in 2013 that shows how to create a spreadsheet from table data.

Hopefully it can give you some ideas:
https://www.experts-exchange.com/questions/28225475/Best-way-to-export-to-excel-from-OracledataReader-in-c.html#a39450396
0
venkatesh SarivisettySenior Software EngineerAuthor Commented:
Thank you NetMinder, But I am keenly looking for Javascript . becoz I can add this code into a scheduler by adding a mail to the code..  Hope you have understood. thanks much for sharing your thoughts.

Thanks
Venkatesh.
0
slightwv (䄆 Netminder) Commented:
Java and JavaScript are two different things.

Not fully understanding but that is OK.  I don't have to.  You can schedule and email from just about any language.
0
venkatesh SarivisettySenior Software EngineerAuthor Commented:
If who can able to help on my requirent can help me to add the Mail notification at the end with the path where I am placing the output file is really helpful

Thanks
Venkatesh
0
venkatesh SarivisettySenior Software EngineerAuthor Commented:
I am still looking for any sample programs. Could you please help me out,
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
If possible, please post your code and accept that so others may benefit from it.
0
venkatesh SarivisettySenior Software EngineerAuthor Commented:
As my own solution worked here rather than the suggestiong shared by the members
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.