java - Accessing sqlite db from JDBC driver

Question

I am trying to integrate sqlite db into my tomcat spring based web application. I use JDBC driver to get the connection to db as follows in DAO class.

 Class.forName("org.sqlite.JDBC");

        con = DriverManager.getConnection("jdbc:sqlite:mydb.db");

But I cannot connect to my database. Here is how I put my db and DAO classes.

DAO CLASS = src->main->java->mypackage->DAO.java

DB file = src->main->resources->mypackage->mydb.db

when deployed both class file and .db file resides in same location in tomcat. But if I give absolute pathto mydb.db it works perfectly. What am I doing wrong here any help is really appreciated.

Edit 1: I get the table not found exception. When I give the full path it updates the database

java.sql.SQLException: no such table: product
at org.sqlite.NativeDB.throwex(NativeDB.java:210)
at org.sqlite.NativeDB._exec(Native Method)
at org.sqlite.Stmt.executeUpdate(Stmt.java:152)
at com.directfn.releasemanagementsystem.dataAccess.ProductDAO.persistProduct(ProductDAO.java:43)
at com.directfn.releasemanagementsystem.controllers.ReleaseDataUploadController.processRegistration(ReleaseDataUploadController.java:51)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:176)
at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:439)
at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:427)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:925)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:915)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:822)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:644)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:796)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:725)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:503)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:136)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:610)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:526)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1078)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:655)
at org.apache.coyote.http11.Http11NioProtocol$Http11ConnectionHandler.process(Http11NioProtocol.java:222)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1566)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1523)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:722)

Thank You!

Answer 1

Change your database url to:

DriverManager.getConnection("jdbc:sqlite::resource:mypackage/mydb.db");

Adding the ::resource will allow for the database file to be located the way you want. Note this works in jar, and war applications

Detailed Information

What is happening is that you probably have the mydb.db on your file system somewhere and you have an even put a product table into that database. However, your code is not locating that instance of the database. Rather it has created a new one, which is an empty database. If you look around in your project you may see this newly created database file. If you were to add the product table to that file and insert some data into it it would start working. The real issue is where on your file system will this database be installed so that you can pass the correct location to the database into your JDBC code to create the connection to it.

Example web application that loads sqlite db successfully:

https://github.com/bryancjacobs/sqlite-web

Please note I didn't get this working with spring but this does prove that what you want to do is possible.