java—如何使用jdbc访问tomcat中的sqlite数据库？引发了不满意的链接错误

wgx48brx  于 2021-07-04  发布在  Java
关注(0)|答案(1)|浏览(444)

我正在用servlet、jsp、jstl技术开发web-java应用程序。我使用sqlite3数据库，在intellij idea中开发，用maven编译项目，用Tomcat9.0进行测试。
我可以直接访问sqlite数据库（使用命令行和sqlite3下载库：屏幕截图）。
当我在intellij idea:screenshot中运行sqlite数据库时，我也可以通过jdbc作为一个普通的javase应用程序来访问它。
但是当我在tomcat上启动我的web项目时，在web页面上，servletexception是由unsatifiedLinkError引发的：

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:\Dropbox\apache-tomcat-9.0.22\temp\sqlite-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;
    }
}

正是这一行在servlet的异常中写为：model.dao.getrooms（dao。java:28)

connection = ds.getConnection();

但是，我在没有tomcat的情况下用于sqlite访问检查的代码运行良好。所以这让我觉得这完全是“假猫的一面”

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();
    }
}

为了让tomcat找到我的数据库，我在web.xml中创建了resource ref

<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>

还向tomcat dir\conf\catalina\localhost\testproject\u war\u exploded.xml中添加了上下文标记，其中testproject\u war\u exploded是我的应用程序的名称。

<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>

我还尝试用相同的代码将具有相同上下文标记的context.xml创建到/meta-inf/context.xml中。
或者我尝试在tomcat dir/conf/server.xml中的globalnamingresources标记中添加context标记——我知道这不推荐，但仍然没有结果。
我确信tomcat会找到rooms.db文件，因为如果我更改路径，就会得到sqlexception和一个“cannotgetconnection”。不正确的url“写入我的日志文件。
我确信tomcat会加载sqlite3jar库，因为我们从org.sqlite.core包中抛出了异常。
尽管我很困惑，如果捕获到任何异常，为什么我的日志文件中没有来自这个getrooms（）方法的记录。但是如果我捕获（throwable），那么我有一个日志文件记录-这意味着抛出了一个错误。
在sqlite文档中，我找到了一个变量来告诉sqlite是使用临时文件夹还是内存。我可以通过命令行更改它，但tomcat使用sqlite3jar库来打开我的数据库。我应该换些jar里的东西吗？我不知道为什么tomcat中的sqlite3试图访问这个/temp/。。。文件夹。
这是idea在目录中创建的web项目结构。
这是我的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>

以及处理索引页的servlet类。

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;
    }

}
Javajdbcsqlitetomcat

1条答案

按热度按时间
8wigbo56

8wigbo561#

更可能的情况是您缺少本机库，这是 sqlite-jdbc .
https://github.com/xerial/sqlite-jdbc/blob/master/src/main/java/org/sqlite/sqlitejdbcloader.java#l305
努力提供 org.sqlite.lib.path 属性指向指向sqlite共享库位置的jvm。

赞(0）分享  回复(0）举报  2021-07-04
我来回答

相关问题

    查看更多
    微信公众号

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com