I've written a code that will be querying data from Excel using odbc
bridge. Now since in java8 there is no more support to odbc
, I'm searching an alternative of the same. I've nearly 32 different programs of suce. i.e. querying from Excel Database. Below is a sample program.
private void getTheDetailedDataForRca(String userName, XSSFWorkbook workbook, XSSFSheet sheet) {
// System.out.println("ph2");
try {
DecimalFormat df2 = new DecimalFormat("#.##");
HashMap<String, HashMap<String, Double>> myArray;
myArray = new HashMap<String, HashMap<String, Double>>();
String user = "%" + userName + "%";
String dburl = path.getDBUrl();
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
myConn = DriverManager.getConnection(dburl);
String queryString = "select Error As Err, Sum(TotalErrors) as Errors from [Quality Sheet$] where Associate like ? group By Error";
PreparedStatement ps = myConn.prepareStatement(queryString);
ps.setString(1, user);
ResultSet rs = ps.executeQuery();
myArray.put(userName, new HashMap<String, Double>());
while (rs.next()) {
double inputValue = rs.getDouble("Errors");
String fin = df2.format(inputValue);
myArray.get(userName).put(rs.getString("Err"), Double.parseDouble(fin));
}
ps.close();
myConn.close();
printMapOne(myArray, userName, workbook, sheet);
} catch (Exception e) {
System.out.println(e + "\t" + "in finals sheet B2");
}
}
And my dburl is
public String getDBUrl() {
return "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\\Users\\" + getSystemId
+ "\\Desktop\\Quality Sheets\\quality_template.xlsx;";
}
I checked it online and to my surprise I found nothing apart from suggesting to use POI
. I tried with UCanAccess
, but this supports only Access database. Can someone please let me know any alternate for odbc
, so that I need not code the entire thing.
I've tried the solution given at Removal of JDBC ODBC bridge in java 8
I got the message as below in cmd.
C:\Users\u0138039\Desktop\sun>jar -cvf jdbc.jar sun
sun : no such file or directory
added manifest
But a jar is created in folder.
I pasted the same jdbc driver in C:\Program Files (x86)\Java\jdk1.8.0_101\jre\lib
and copied jdbcodbc.dll
to C:\Program Files (x86)\Java\jre1.8.0_101\bin
, restarted and to my surprise I get the same java.sql.SQLException: No suitable driver found for jdbc:odbc
Exception.
Thanks