Sqlite jdbc integration

[yoanns60676056]

Hello !

I'm facing some issues with the Sqlite jdbc integration, my bundle doesn't start.

I'm trying to use this dependency:

<dependency>
    <groupId>org.xerial</groupId>
    <artifactId>sqlite-jdbc</artifactId>
    <version>3.8.11.2</version>
</dependency>

But org.ibex.nestedvm -- Cannot be resolved

So I have added 

<dependency>
    <groupId>org.xerial.thirdparty</groupId>
    <artifactId>nestedvm</artifactId>
    <version>1.0</version>
</dependency>

But org.ibex.classgen -- Cannot be resolved

I know that this version of jdbc sqlite doesn't provide nestedvm, but it provides classgen...

I also tried to build "my own" OSGI plugin wrapping these dependencies (this is very very dirty for me and unacceptable for my colleagues, but I had to try), so I had a separated bundle providing jdbc sqlite.

Is there some good practices with the use of jdbc sqlite, could you please give me a step by step to manage to connect and read my .db ?

Thank you in advance !

Yoann Sachot
Web & UX Office

LE GOUVERNEMENT DU GRAND-DUCHÉ DE LUXEMBOURG
Centre des technologies de l’information de l’État

11, rue Notre-Dame . L-2240 Luxembourg
Tél. (+352) 247-86735 . Fax (+352) 92088
E-mail : yoann.sachot@ext.ctie.etat.lu
www.gouvernement.lu . www.luxembourg.lu

Référentiel de normalisation : www.renow.lu
Support Renow : renow.info@ctie.etat.lu

[Peter_Puzanovs · Accepted solution]

Hi Yoann,

Simply make sure your maven bundle plugin has following config:

<Bundle-ClassPath>.,{maven-dependencies}</Bundle-ClassPath>

<Import-Package>org.ibex.nestedvm;resolution:=optional,javax.servlet;version="2.1",javax.servlet.http;version="2.1",*</Import-Package>

As you may notice I do not force OSGi to load nestedvm make it optional.

Following code would register a servlet can be accessed on author access JDBC and perform required operations, when you access http://localhost:4502/bin/jdbc it would log:

12.11.2015 10:19:46.703 *INFO* [0:0:0:0:0:0:0:1 [1447323586696] GET /bin/jdbc HTTP/1.1] JDBC started
12.11.2015 10:19:46.951 *INFO* [0:0:0:0:0:0:0:1 [1447323586696] GET /bin/jdbc HTTP/1.1] name = leo
12.11.2015 10:19:46.951 *INFO* [0:0:0:0:0:0:0:1 [1447323586696] GET /bin/jdbc HTTP/1.1] JDBC id = 1
12.11.2015 10:19:46.951 *INFO* [0:0:0:0:0:0:0:1 [1447323586696] GET /bin/jdbc HTTP/1.1] JDBC name = yui
12.11.2015 10:19:46.952 *INFO* [0:0:0:0:0:0:0:1 [1447323586696] GET /bin/jdbc HTTP/1.1] JDBC id = 2
12.11.2015 10:20:01.309 *INFO* [0:0:0:0:0:0:0:1 [1447323601305] GET /bin/jdbc HTTP/1.1] JDBC started
12.11.2015 10:20:01.352 *INFO* [0:0:0:0:0:0:0:1 [1447323601305] GET /bin/jdbc HTTP/1.1] JDBC name = leo
12.11.2015 10:20:01.352 *INFO* [0:0:0:0:0:0:0:1 [1447323601305] GET /bin/jdbc HTTP/1.1] JDBC id = 1
12.11.2015 10:20:01.352 *INFO* [0:0:0:0:0:0:0:1 [1447323601305] GET /bin/jdbc HTTP/1.1] JDBC name = yui
12.11.2015 10:20:01.352 *INFO* [0:0:0:0:0:0:0:1 [1447323601305] GET /bin/jdbc HTTP/1.1] JDBC id = 2

Code: import org.apache.felix.scr.annotations.Component; import org.apache.felix.scr.annotations.Properties; import org.apache.felix.scr.annotations.Property; import org.apache.felix.scr.annotations.Service; import org.apache.sling.api.SlingHttpServletRequest; import org.apache.sling.api.SlingHttpServletResponse; import org.apache.sling.api.servlets.SlingSafeMethodsServlet; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import javax.servlet.ServletException; import java.io.IOException; import java.sql.*; @Component(metatype = true, label = "JDBC") @Service @Properties({ @Property(name = "sling.servlet.paths", value = "/bin/jdbc"), @Property(name = "sling.servlet.methods", value = "GET") }) public class JDBC  extends SlingSafeMethodsServlet { private static final Logger LOG = LoggerFactory.getLogger(JDBC.class); @Override protected void doGet(final SlingHttpServletRequest request, final SlingHttpServletResponse response) throws ServletException, IOException { LOG.info("started"); // load the sqlite-JDBC driver using the current class loader try { Class.forName("org.sqlite.JDBC"); } catch (ClassNotFoundException e) { LOG.error(" Could not locate JDBC "+e); } Connection connection = null; try { // create a database connection connection = DriverManager.getConnection("jdbc:sqlite:C:/dev/sqllite/test.db"); Statement statement = connection.createStatement(); statement.setQueryTimeout(30);  // set timeout to 30 sec. statement.executeUpdate("drop table if exists person"); statement.executeUpdate("create table person (id integer, name string)"); statement.executeUpdate("insert into person values(1, 'leo')"); statement.executeUpdate("insert into person values(2, 'yui')"); ResultSet rs = statement.executeQuery("select * from person"); while(rs.next()) { // read the result set LOG.info("name = " + rs.getString("name")); LOG.info("id = " + rs.getInt("id")); } } catch(SQLException e) { // if the error message is "out of memory", // it probably means no database file is found LOG.error(""+e); } finally { try { if(connection != null) connection.close(); } catch(SQLException e) { // connection close failed. LOG.error("Something went wrong: "+e); } } } }

Thanks,

Peter

[yoanns60676056]

Thank you very much Peter, I have made good progress thanks to you !

I think I have to study maven a little more ;)

Have a nice day !