0

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!

Improve this question
1
  • 1
    Define 'cannot connect'. Commented Jul 21, 2014 at 3:13

1 Answer 1

Reset to default
1

Solution

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Yes empty database should be created. But it is not available in the tomcat extraction location of the project. Is there any specific location where the file is created or is it created in the memory? Basically I need to access the mydb.db in side my code and it should be bundled with the war file (I cant access it directly from the file system)
Thanks a lot for the explanation. Yes you are correct. It is created inside the tomcat bin folder. Seems like I have to manually replace the db file in side bin. Do you aware of a proper way to access db file which gets when war file is extracted (bundling the db file with the war file and accessing from code)?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.