Přeskočit na hlavní obsah

Jak na Excel 4 - JDBC driver

Když už jsem v úvodu seriálu zmínil možnost přístupu k Excel souborům přes JDBC, měl bych teď přidat více detailů. Ale nečekejte žádné zázraky. Osobně jsem tímto způsobem s excelem nikdy nepracoval a ani samotná myšlenka se mi moc nelíbí, nicméně je to také jedna z cest. Narazil jsem pouze na dvě knihovny, které poskytovaly JDBC rozhraní pro Excel:

HXTT JDBC driver jsem nezkoumal a xlSQL mě zklamal - chybí dokumentace, vývoj zamrzl v roce 2004 a příklady použití aby člověk hledal "kupou v lupce sena" :-).

Pro zpracování xls dokumentů používá xlSQL knihovnu projektu JExcelApi, ke které se snad dostaneme v některém z příštích dílů. Zpracování SQL dotazů probíhá v režii knihoven z MySQL connectoru a HSQLDB.

A abych zůstal věrný tradici a netrápil vás pouze šedou teorií, tak tady je příklad kódu jak na excel přes JDBC:

Class.forName("com.nilostep.xlsql.jdbc.xlDriver");
String url = "jdbc:nilostep:excel:" + System.getProperty("user.dir");
Connection con = DriverManager.getConnection(url);
Statement stm = con.createStatement();

String sql = "DROP TABLE \"demo.xlsqly7\" IF EXISTS;"
             + "CREATE TABLE \"demo.xlsqly7\" (v varchar);";
stm.execute(sql);

// some inserts
for (int i = 0; i < 7000; i++) {
    sql = "INSERT INTO \"demo.xlsqly7\" VALUES ('xlSQL Y7 - NiLOSTEP');";
    stm.execute(sql);
}
//query count
sql = "select count(*) from \"demo.xlsqly7\"";
ResultSet rs = stm.executeQuery(sql);

//print resutl
while (rs.next()) {
    System.out.println("Sheet xlsqly7 has " + rs.getString(1)
                       + " rows.");
}

// close connection
con.close();

Komentáře

Populární příspěvky z tohoto blogu

Three ways to redirect HTTP requests to HTTPs in WildFly and JBoss EAP

WildFly application server (and JBoss EAP) supports several simple ways how to redirect the communication from plain HTTP to TLS protected HTTPs. This article presents 3 ways. Two are on the application level and the last one is on the server level valid for requests to all deployments. 1. Request confidentiality in the deployment descriptor The first way is based on the Servlet specification. You need to specify which URLs should be protected in the web.xml deployment descriptor. It's the same approach as the one used for specifying which URLs require authentication/authorization. Just instead of requesting an assigned role, you request a transport-guarantee . Sample content of the WEB-INF/web.xml <web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd" version="3.1

Ignore the boring SSH error message - Host identification has changed!

The problem If you work with virtual machines in clouds, or you run an SSH server in Docker containers, then you've probably met the following error message during making ssh connection: (I'm connecting through SSH to a docker container) ~$ ssh -p 8822 root@localhost @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @ WARNING: REMOTE HOST IDENTIFICATION HAS CHANGED! @ @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ IT IS POSSIBLE THAT SOMEONE IS DOING SOMETHING NASTY! Someone could be eavesdropping on you right now (man-in-the-middle attack)! It is also possible that a host key has just been changed. The fingerprint for the ECDSA key sent by the remote host is SHA256:smYv5yA0n9/YrBgJMUCk5dYPWGj7bTpU40M9aFBQ72Y. Please contact your system administrator. Add correct host key in /home/jcacek/.ssh/known_hosts to get rid of this message. Offending ECDSA key in /home/jcacek/.ssh/known_hosts:107 remove with: ssh-keygen -f "/home/jcacek/.ssh/know

Enable Elytron in WildFly

Steps to enable Elytron in WildFly nightly builds. There is an ongoing effort to bring a new security subsystem Elytron to WildFly and JBoss EAP. For some time a custom server profile named standalone-elytron.xml  existed beside other profiles in standalone/configuration directory. It was possible to use it for playing with Elytron. The custom Elytron profile was removed now.  The Elytron subsystem is newly introduced to all standard server profiles. The thing is, the Elytron is not used by default and users have to enable it in the subsystems themselves. Let's look into how you can enable it. Get WildFly nightly build # Download WildFly nightly build wget --user=guest --password=guest https://ci.wildfly.org/httpAuth/repository/downloadAll/WF_Nightly/.lastSuccessful/artifacts.zip # unzip build artifacts zip. It contains WildFly distribution ZIP unzip artifacts.zip # get the WildFly distribution ZIP name as property WILDFLY_DIST_ZIP=$(ls wildfly-*-SNAPSHOT.zip) # un