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?
 
venkatesh SarivisettyConnect With a Mentor Senior Software EngineerAuthor Commented:
I am still looking for any sample programs. Could you please help me out,
0
 
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
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
 
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 SarivisettyConnect With a Mentor Senior 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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.