Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

Sqlite jdbc integration

Avatar

Level 2

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

 
1 Accepted Solution

Avatar

Correct answer by
Community Advisor

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

View solution in original post

2 Replies

Avatar

Correct answer by
Community Advisor

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

Avatar

Level 2

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 !