#java #sqlite #tomcat #jdbc
#java #sqlite #tomcat #jdbc
Вопрос:
Я разрабатываю веб-приложение Java с использованием технологий Servlet, JSP, JSTL. Я использую базу данных SQLite3, разрабатываю в IDE IntelliJ Idea, компилирую проект с помощью Maven и тестирую в Tomcat 9.0.
Я могу получить доступ к базе данных SQLite напрямую (используя командную строку и загруженную библиотеку sqlite3: скриншот).
Я также могу получить доступ к базе данных SQLite через JDBC как простое приложение JavaSE, когда я запускаю его в intellij idea: скриншот.
Но когда я запускаю свой веб-проект на Tomcat, на веб-странице возникает исключение ServletException, вызываемое 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.
В окне командной строки tomcat startup.bat я вижу это:
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:Dropboxapache-tomcat-9.0.22tempsqlite-3.21.0.1-02fa308c-7c4d-4cfb-93cb-3e46dbaa56a1-sqlitejdbc.dll.lck (Системе не удается найти указанный путь)
(Система не может найти указанный путь)
Это метод, который выдает исключение:
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-dirconfCatalinalocalhosttestProject_war_exploded.xml
where testProject_war_exploded is the name of my app.
<Context reloadable="true" antiJARLocking="true" path="/" docBase="C:UsersnativIdeaProjectstestProjectoutartifactstestProject_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.
Это структура моего веб-проекта, которую idea создает в каталоге out.
Это мой 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>
И класс сервлета, который обрабатывает индексную страницу.
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;
}
}
Комментарии:
1. Что происходит при замене
connection = ds.getConnection();наconnection = DriverManager.getConnection("jdbc:sqlite:rooms.db");? Это должно работать при условии, что уWEB-INF/libесть jar-файл драйвера.2. Jar находился в папке WEB-INF / lib, и все равно он выдает мне то же исключение. Я также поместил sqilte jar непосредственно в папку tomcat / lib, но все то же сообщение о not found temp … папка
Ответ №1:
Скорее всего, вам не хватает собственных библиотек, которые требуются sqlite-jdbc .
Попробуйте предоставить org.sqlite.lib.path свойство jvm, указывающее на расположение разделяемых библиотек sqlite.
Комментарии:
1. Я поместил sqlite jar непосредственно в папку tomcat / lib и перед кодом доступа к базе данных написал свойство set следующим образом:
System.setProperty("org.sqlite.lib.path", "${catalina.home}" "/lib/sqlite-jdbc-3.21.0.1.jar");Но когда я решил сначала посмотреть, что это за свойство, getProperty() выдал мне именно ту строку, которую я настраивал. Сервлет по-прежнему выдает ту же ошибку, но при запуске cmd не выдает «not found /temp /… dir». Вместо этого он пишет, что не удалось отменить ссылку на драйвер JDBC Означает ли это, что драйвер JDBC зарегистрирован, но не незарегистрирован из-за ошибки?
Ответ №2:
SQLite создаст файл БД при создании соединения в корневой папке IDE, чтобы обработать это и указать на вашу целевую БД, просто используйте ее полный путь, вот шаги:
- Поместите файл sqlite-jdbc jar в папку Tomcat lib
- Поместите файл вашей базы данных [MyDB.db Или MyDB.sqlite] в папку Tomcat lib или создайте другую папку «db» и укажите на нее, используя ее полный путь при создании объекта Connection.