'How to access SQLite database in Tomcat using JDBC? UnsatisfiedLinkError thrown
I'm developing web Java application with Servlet, JSP, JSTL technologies. I use SQLite3 database, develop in IntelliJ Idea IDE, compile project with Maven and test in Tomcat 9.0.
I can access SQLite database directly (using command line and sqlite3 downloaded library: screenshot).
I also can access SQLite database via JDBC as a plain JavaSE application when I run it in intellij idea: screenshot.
But when I start my web project on Tomcat, on the web page ServletException is thrown cased by UnsatisfiedLinkError:
HTTP Status 500 – Internal Server Error
Type Exception Report
Message Servlet execution threw an exception
Description The server encountered an unexpected condition that prevented it from fulfilling the request.
Exception
javax.servlet.ServletException: Servlet execution threw an exception
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
Root Cause
java.lang.UnsatisfiedLinkError: 'void org.sqlite.core.NativeDB._open_utf8(byte[], int)'
org.sqlite.core.NativeDB._open_utf8(Native Method)
org.sqlite.core.NativeDB._open(NativeDB.java:71)
org.sqlite.core.DB.open(DB.java:174)
org.sqlite.core.CoreConnection.open(CoreConnection.java:220)
org.sqlite.core.CoreConnection.<init>(CoreConnection.java:76)
org.sqlite.jdbc3.JDBC3Connection.<init>(JDBC3Connection.java:25)
org.sqlite.jdbc4.JDBC4Connection.<init>(JDBC4Connection.java:24)
org.sqlite.SQLiteConnection.<init>(SQLiteConnection.java:45)
org.sqlite.JDBC.createConnection(JDBC.java:114)
org.sqlite.JDBC.connect(JDBC.java:88)
org.apache.tomcat.dbcp.dbcp2.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:53)
org.apache.tomcat.dbcp.dbcp2.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:355)
org.apache.tomcat.dbcp.dbcp2.BasicDataSource.validateConnectionFactory(BasicDataSource.java:116)
org.apache.tomcat.dbcp.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:731)
org.apache.tomcat.dbcp.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:605)
org.apache.tomcat.dbcp.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:809)
model.DAO.getRooms(DAO.java:29)
servlets.HomepageServlet.doGet(HomepageServlet.java:55)
javax.servlet.http.HttpServlet.service(HttpServlet.java:634)
javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
Note The full stack trace of the root cause is available in the server logs.
In a tomcat's startup.bat command line window I see this:
06-Sep-2020 21:11:37.875 INFO [http-nio-8080-exec-32] org.apache.catalina.core.StandardContext.reload Reloading Context with name [/testProject_war_exploded] has started
06-Sep-2020 21:11:38.352 INFO [http-nio-8080-exec-32] org.apache.jasper.servlet.TldScanner.scanJars At least one JAR was scanned for TLDs yet contained no TLDs. Enable debug logging for this logger for a complete list of JARs that were scanned but no TLDs were found in them. Skipping unneeded JARs during scanning can improve startup time and JSP compilation time.
06-Sep-2020 21:11:38.375 INFO [http-nio-8080-exec-32] org.apache.catalina.core.StandardContext.reload Reloading Context with name [/testProject_war_exploded] is completed
C:\Dropbox\apache-tomcat-9.0.22\temp\sqlite-3.21.0.1-02fa308c-7c4d-4cfb-93cb-3e46dbaa56a1-sqlitejdbc.dll.lck (Системе не удается найти указанный путь)
(System cannot find the path specified)
This is the method which throws an exception:
package model;
import org.apache.log4j.Logger;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class DAO {
private final Logger log = Logger.getLogger(this.getClass());
private final String driverName = "org.sqlite.JDBC";
public List<Room> getRooms() {
List<Room> rooms = new ArrayList<Room>();
Connection connection = null;
try {
Class.forName(driverName);
Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/rooms");
connection = ds.getConnection();
} catch (ClassNotFoundException e) {
log.debug("Can't get class. No driver found");
e.printStackTrace();
} catch (SQLException e) {
log.debug("Can't get connection. Incorrect URL");
e.printStackTrace();
} catch (NamingException e) {
log.debug("Can't get Context or Datasource");
e.printStackTrace();
} catch (Exception e) {
log.debug("Some other exception");
e.printStackTrace();
}
try {
String sql = "SELECT * FROM room";
Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery(sql);
Room room;
while (rs.next()) {
room = new Room();
room.setName(rs.getString("name"));
room.setCountryCode(rs.getString("country_code"));
room.setLightOn(rs.getInt("light_status"));
rooms.add(room);
log.debug(room.getName() + " " + room.getCountryCode() + "" + room.isLightOn());
}
} catch (SQLException e) {
log.debug("SQL Exception thrown during select statement");
e.printStackTrace();
}
try {
connection.close();
} catch (SQLException e) {
log.debug("Can't close connection");
e.printStackTrace();
}
return rooms;
}
}
Precisely this row is written in Servlet's exception as : model.DAO.getRooms(DAO.java:28)
connection = ds.getConnection();
However this code I used for SQLite access check without Tomcat works fine. So it makes me think it's completely on "tomcat side"
import model.Room;
import org.apache.log4j.Logger;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class Main {
private final Logger log = Logger.getLogger(this.getClass());
private final String driverName = "org.sqlite.JDBC";
private final String connectionString = "jdbc:sqlite:rooms.db";
public void run() {
List<Room> rooms = new ArrayList<Room>();
Connection connection = null;
try {
Class.forName(driverName);
connection = DriverManager.getConnection(connectionString);
} catch (ClassNotFoundException e) {
log.debug("Can't get class. No driver found");
e.printStackTrace();
} catch (SQLException e) {
log.debug("Can't get connection. Incorrect URL");
e.printStackTrace();
}
try {
String sql = "SELECT * FROM room";
Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery(sql);
Room room;
while (rs.next()) {
room = new Room();
room.setName(rs.getString("name"));
room.setCountryCode(rs.getString("country_code"));
room.setLightOn(rs.getInt("light_status"));
rooms.add(room);
System.out.println(room.getName() + " " + room.getCountryCode() + " " + room.isLightOn());
}
} catch (SQLException e) {
log.debug("SQL Exception thrown during select statement");
e.printStackTrace();
}
try {
connection.close();
} catch (SQLException e) {
log.debug("Can't close connection");
e.printStackTrace();
}
}
public static void main(String[] args) {
Main app = new Main();
app.run();
}
}
In order for Tomcat to find my database I created resource-ref in web.xml
<resource-ref>
<description>Rooms Database</description>
<res-ref-name>jdbc/rooms</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
And also added Context tag into tomcat-dir\conf\Catalina\localhost\testProject_war_exploded.xml where testProject_war_exploded is the name of my app.
<Context reloadable="true" antiJARLocking="true" path="/" docBase="C:\Users\nativ\IdeaProjects\testProject\out\artifacts\testProject_war_exploded\">
<Resource name="jdbc/rooms"
auth="Container"
type="javax.sql.DataSource"
driverClassName="org.sqlite.JDBC"
url="jdbc:sqlite:rooms.db">
</Resource>
</Context>
I also tried instead creating context.xml with the same Context tag into /META-INF/context.xml with the same code.
Or I tried adding Context tag into GlobalNamingResources tag in tomcat-dir/conf/server.xml - which is not recommended, I know, but still no result.
I am sure tomcat finds that rooms.db file because if I change path - I get SQLException and a "Can't get connection. Incorrect URL" written into my log file.
I am sure tomcat load sqlite3 jar library because we have exception thrown from org.sqlite.core package.
Although I am confused why I have no record in my log file from this getRooms() method if I catch any Exception. But if I catch (Throwable), then I have a log file record - that means an Error is thrown.
In SQLite documentation I found a variable to tell sqlite whether to use temp folder or memory. I can change it via command line, but Tomcat uses sqlite3 jar library to open my database. Should I change something in that jar? I have no idea why sqlite3 in tomcat tries to access this /temp/... folder.
This is my web project structure which idea creates in \out directory.
This is my pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.example</groupId>
<artifactId>testProject</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.release>11</maven.compiler.release>
<junit.jupiter.version>5.6.2</junit.jupiter.version>
</properties>
<dependencies>
<!-- https://mvnrepository.com/artifact/javax.servlet/javax.servlet-api -->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>4.0.1</version>
<scope>provided</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/javax.servlet.jsp/javax.servlet.jsp-api -->
<dependency>
<groupId>javax.servlet.jsp</groupId>
<artifactId>javax.servlet.jsp-api</artifactId>
<version>2.3.3</version>
<scope>provided</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/jstl/jstl -->
<dependency>
<groupId>jstl</groupId>
<artifactId>jstl</artifactId>
<version>1.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.junit.jupiter/junit-jupiter-api -->
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter-api</artifactId>
<version>${junit.jupiter.version}</version>
<scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/org.junit.jupiter/junit-jupiter-engine -->
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter-engine</artifactId>
<version>${junit.jupiter.version}</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter</artifactId>
<version>${junit.jupiter.version}</version>
<scope>test</scope>
</dependency>
<!-- API for countries' names and codes list -->
<dependency>
<groupId>com.neovisionaries</groupId>
<artifactId>nv-i18n</artifactId>
<version>1.22</version>
</dependency>
<dependency>
<groupId>com.maxmind.geoip2</groupId>
<artifactId>geoip2</artifactId>
<version>2.14.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/log4j/log4j -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.xerial/sqlite-jdbc -->
<dependency>
<groupId>org.xerial</groupId>
<artifactId>sqlite-jdbc</artifactId>
<version>3.21.0.1</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.1</version>
<configuration>
<release>11</release>
</configuration>
</plugin>
<plugin>
<artifactId>maven-surefire-plugin</artifactId>
<version>2.22.2</version>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-resources-plugin</artifactId>
<version>3.2.0</version>
<configuration>
<nonFilteredFileExtensions>
<nonFilteredFileExtension>exe</nonFilteredFileExtension>
</nonFilteredFileExtensions>
</configuration>
</plugin>
</plugins>
</build>
</project>
And Servlet class that handles index page.
package servlets;
import com.maxmind.geoip2.record.Country;
import com.neovisionaries.i18n.CountryCode;
import controller.LocationHelper;
import model.DAO;
import model.Room;
import org.apache.log4j.Logger;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.*;
public class HomepageServlet extends HttpServlet {
private LocationHelper locationHelper = new LocationHelper();
private DAO dao = new DAO();
private final Logger log = Logger.getLogger(this.getClass());
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String roomName = request.getParameter("roomName");
log.debug("roomName = " + roomName);
String selectedCountry = request.getParameter("countryList");
log.debug("selectedCountry = " + selectedCountry);
doGet(request, response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
RequestDispatcher requestDispatcher = request.getRequestDispatcher("index.jsp");
String ipStr = LocationHelper.getClientIpAddr(request);
request.setAttribute("ipAddress", ipStr);
String countryStr = "not determined";
String countrycode = "no code";
Country country = locationHelper.getCountry(getServletContext());
if (country != null) {
countryStr = country.getName();
countrycode = country.getIsoCode();
}
request.setAttribute("country", countryStr);
request.setAttribute("code", countrycode);
List<String> countries = getSortedCountriesList();
request.setAttribute("countriesList", countries);
List<Room> rooms = dao.getRooms();
request.setAttribute("roomsList", rooms);
requestDispatcher.forward(request, response);
}
/**
*
* @return Sorted countries list to be shown in a dropdown list.
*/
private List<String> getSortedCountriesList() {
List<String> countriesList = new ArrayList<>();
for (CountryCode code : CountryCode.values()) {
countriesList.add(code.getName());
}
Collections.sort(countriesList, (s1, s2) -> s1.compareToIgnoreCase(s2));
return countriesList;
}
}
Solution 1:[1]
More likely you are missing the native libraries, which are required by sqlite-jdbc.
Try to provide org.sqlite.lib.path property to jvm pointing to location of sqlite shared libraries.
Solution 2:[2]
SQLite will create a DB file when the connection is created at the IDE root folder, To handle this and point to your targeted DB, Just use its full path, Here are the steps:
- Put the sqlite-jdbc jar file in Tomcat\lib folder
- Put your database file [MyDB.db OR MyDB.sqlite] in Tomcat\lib folder or create another folder "db" and point to it using its full path while creating the Connection object.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | andreoss |
| Solution 2 | Saad Jareer |