如何实现一个侦听器，用于访问Java中的sqlite数据库的事件 [英] How to implement a listener for access events of a sqlite database in Java

问题描述

我想在我的Java应用程式中显示「不确定的JProgressBar 」每次访问SQLite数据库进行读/写。我认为最重要的是从数据库实现触发信号。然后对此触发器实现一个监听器以显示进度条。

我使用SQLite与JDK 1.7的Xerial JDBC驱动程序。应用程序是独立。 SQLite数据库在hdd。我对Java有一个很好的基本了解，但我不是专家。 Hibernate / Spring等方法对我来说太多了。

到目前为止，我发现到互联网搜索（点击标题链接的详细信息） p>

建议1（手动方法）：

每次需要时手动启动进度栏。然后执行Swingworker。使用其覆盖的done（）方法停止进度条。

Pro：

• 非常简单的方法






li>

Idea 2（覆写方法）：

覆盖提交（）以及 java.sql.Connection 的 close（）

• 非常简单的方法


• 无需额外的编码


• 数据库访问的准确启动和停止触发


• >
  

Con：

• 解析必要的提交如果读取或写入权限，则输出

Idea 3（中间的人）：

使用jdbctools作为中间层进行监控。使用 JDBCLogger 或 JDBCCallFilter 可检测访问。

数据库访问的准确开始和停止触发器

<

Con：

• 
  
• 过期（2008）？

Idea 4（Pooling）：

使用 ConnectionEventListener 从 javax.sql 来监视数据库连接。

Pro：

• 内置Java SQL功能

Con：

• 需要一个PooledConnection对象， / li>
  
• 没有连接开始的触发

Idea 5（使用SQLite C介面）：

使用用于用例的SQLite C接口的查询进度回调。

Pro：

• 本机SQLite功能

Con：

• 因为它在C中，必须在JDBC驱动程序中实现才能使用此功能。

总结：
我倾向于尝试想法2，但我想听听专家们先说什么：你喜欢哪种方法，或者如何实现这种触发器信号？

解决方案

首先：

• 构思2 （覆盖方法）无效，因为方法是静态的，无法重写。



• Idea 5 （使用SQLite C接口）需要一个用于查询进度回调的Java实现。已经有一些工作，但它有
  没有被合并到JDBC驱动程序的Xerial还。有关详情，请参阅
  此处。



• Idea 3& 4 似乎是可行的，但是使我的项目太复杂，难以维护。

1：

1. 进度条的最佳触发器是打开和关闭数据库连接的时刻。


2. 这些触发器可以触发事件侦听器捕获的事件，该事件侦听器将打开一个带有进度条的对话框。

首先，你需要为事件机制创建一些类。您可以找到有关EventObjects概念的详细信息此处和此处。

Db_Status.java ：

  public class Db_Status 
 {
 public final static Db_Status CONNECTED = new Db_Status（connected）; 
 public final static Db_Status PENDING = new Db_Status（pending）; 
 public final static Db_Status CLOSED = new Db_Status（closed）; //关闭数据库连接
 public final static Db_Status IDLE = new Db_Status（idle）; //应用程序启动后数据库的初始状态
 public final static Db_Status ERROR = new Db_Status（error）; //访问/关闭数据库时出错
 
 private String status; 
 
 public String toString（）{
 return status; 
} 
 
 private Db_Status（String statusstring）{
 status = statusstring; 
} 
 
} 
  

Db_ActionEvent.java：

  public class Db_ActionEvent extends EventObject 
 {
 private Db_Status status; 
 
 public Db_ActionEvent（Object source，Db_Status DBstate）{
 super（source）; 
 status = DBstate; 
} 
 public Db_Status Status（）{
 return status; 
} 
 
} 
  

Db_ActionListener.java：

  public interface Db_ActionListener {
 
 public void DbActionReceived（Db_ActionEvent event）; 
 
} 
  

然后必须实现侦听器。这是侦听器捕获数据库事件后的反应场所，这里：ouput到控制台：

  public class UI_DbProgressbar implements Db_ActionListener {
 public void DbActionReceived（Db_ActionEvent event）{
 if（event.Status（）== Db_Status.PENDING）
 {
 System.out.println ！）; 
} 
 else if（event.Status（）== Db_Status.CLOSED）
 {
 System.out.println（DB connection is closed！ 
} 
 else if（event.Status（）== Db_Status.CONNECTED）
 {
 System.out.println（DB is connected！ 
} 
 else 
 {
 System.out.println（DB idle！）; 
} 
} 
} 
  

机制。您可能有一个中央类来处理所有数据库访问。您必须

• 创建两种中央方法来打开和关闭连接


• 添加一些方法

public class Db_DataExchange {

  // 
 //修改构造函数
 // 
 
 public Db_DataExchange（）
 {
 this.DBStateListeners = new ArrayList（）; 
 
 //这里的构造函数代码... 
 
 UI_DbProgressbar listener = new UI_DbProgressbar（）; 
 this.addDbActionListener（listener）; 
} 
 
 // 
 //添加一些方法来控制侦听器
 // 
 
 private Db_Status DBAccessStatus = Db_Status.IDLE; 
 private List DBStateListeners; 
 
 public synchronized void addDbActionListener（Db_ActionListener l）{
 DBStateListeners.add（l）; 
} 
 
 public synchronized void removeDbActionListener（Db_ActionListener l）{
 DBStateListeners.remove（l）; 
} 
 
私有同步void fireDbActionEvent（Db_Status AccessStatus）
 {
 DBAccessStatus = AccessStatus; 
 Db_ActionEvent status = new Db_ActionEvent（this，DBAccessStatus）; 
迭代器侦听器= DBStateListeners.iterator（）; 
 while（listeners.hasNext（））{
（（Db_ActionListener）listeners.next（））DbActionReceived（status）; 
} 
} 
 
 // 
 //创建两个中央方法来打开和关闭连接
 // 
 
 / ** 
 *建立jdbc数据库连接的方法
 * / 
 public Connection EstablishDBConnection（String ConnectionURL）{
 Connection c = null; 
 
 try {
 //载入JDBC sqlite驱动程序
 Class.forName（this.DBDriverName）; 
 
 // url  - 一个数据库url，格式为jdbc：subprotocol：subname 
 c = DriverManager.getConnection（ConnectionURL）; 
 
 c.setAutoCommit（false）; 
 
 System.out.println（Database connected successfully！）; 
 //这是重要的触发器：
 this.fireDbActionEvent（Db_Status.CONNECTED）; 
 
返回c; 
 
} catch（Exception e）{
 System.err.println（e.getClass（）。getName（）+：+ e.getMessage（））; 
 this.fireDbActionEvent（Db_Status.ERROR）; 
 return c; 
} 
 
} 
 
 / ** 
 *关闭jdbc数据库连接的方法
 * / 
 public boolean CloseDBConnection（Connection c）{
 try 
 {
 c.close（）; // finally close 
 //这是重要的触发器：
 this.fireDbActionEvent（Db_Status.CLOSED）; 
 
 return true; 
 
} catch（Exception e）
 {
 //System.err.println（e.getClass（）。getName（）+：+ e.getMessage ）; 
 logger.error（{}中的错误：{}，e.getClass（）。getName（），e.​​getMessage（））; 
 this.fireDbActionEvent（Db_Status.ERROR）; 
 
 return false; 
} 
} 
  

}

$ b

I'd like to display a 'indeterminate JProgressBar' in my Java application each time it accesses the SQLite database for reading / writing. I think the most important thing is to realize a trigger signal from the database. Then implement a listener to this trigger to display the progress bar.

I use SQLite with the Xerial JDBC driver with JDK 1.7. The application is 'stand alone'. The SQLite database is on the hdd. I have a good basic understanding of Java, but i'm no expert. Approaches with Hibernate / Spring etc. are way too much for me.

What i've found so far from an internet search (click header links for details):

Idea 1 (Manual approach):

Manually start the progress bar each time needed. Then execute Swingworker. Use its overridden done() method to stop the progress bar.

Pro:

• Very easy approach

Con:

• A lot of coding necessary for every event you need the progress bar

Idea 2 (Overriding methods):

Override the commit() and the close() method of java.sql.Connection and send a trigger signal everytime these methods are executed.

Pro:

• Very easy approach
• No extra coding necessary
• Exact start and stop trigger for database access
• JDBC approach

Con:

• Parsing of commits necessary to find out if read or write access

Idea 3 (Man in the middle):

Use jdbctools as a middle layer for monitoring. Use JDBCLogger or JDBCCallFilter to detect accesses.

Pro:

• No extra coding necessary
• Exact start and stop trigger for database access

Con:

• Little documentation / no examples
• Outdated (2008)?

Idea 4 (Pooling):

Use ConnectionEventListener from javax.sqlto monitor database connection.

Pro:

• Built-in Java SQL functionality

Con:

• Needs a PooledConnection object which is a bit over the top for the task
• No trigger for connection start

Idea 5 (use SQLite C interface):

Use 'Query Progress Callbacks' from SQLite C Interface which is intended for the use case.

Pro:

• Native SQLite functionality

Con:

• Because it is in 'C' it must be implemented in the JDBC driver to use this functionality. The Xerial driver doesn't seem to have it implemented
• Limited to SQLite database

Summary: I'd tend to try 'idea 2' but i'd like to hear what the experts are saying first: Which approach would you prefer or how would you implement that kind of trigger signal?

解决方案

At first:

• Idea 2 (Overriding methods) didn't work because the methods are static and cannot be overridden.

• Idea 5 (use SQLite C interface) needs a Java implementation for 'Query Progress Callbacks'. There's already some work done but it has not been merged into the JDBC driver by Xerial yet. For more details see here.

• Idea 3 & 4 seem to be feasable but make my project too complicated and harder to maintain.

So i came across to extend Idea 1:

1. The best triggers for a progress bar are the moments when you open and close a database connection.
2. These triggers can fire events which can be caught by an event listener that will open a dialog with a progress bar

At first you need to create some classes for the event mechanisms. You can find details about the idea of EventObjects here and here.

Db_Status.java:

public class Db_Status 
{
    public final static Db_Status CONNECTED = new Db_Status("connected");
    public final static Db_Status PENDING = new Db_Status("pending");
    public final static Db_Status CLOSED = new Db_Status("closed"); // Closed database connection
    public final static Db_Status IDLE = new Db_Status("idle"); // Initial status of database after app start
    public final static Db_Status ERROR = new Db_Status("error"); // Error while accessing / closing database

    private String status;

    public String toString() {
        return status;
    }

    private Db_Status(String statusstring) {
        status = statusstring;
    }

}

Db_ActionEvent.java:

public class Db_ActionEvent extends EventObject 
{
    private Db_Status status;

    public Db_ActionEvent(Object source, Db_Status DBstate) {
        super(source);
        status = DBstate;
    }
    public Db_Status Status() {
        return status;
    }

}

Db_ActionListener.java:

public interface Db_ActionListener {

    public void DbActionReceived(Db_ActionEvent event );

}

You must then implement the listener. This is the place for the reaction after the listener has caught a database event, here: ouput to console:

public class UI_DbProgressbar implements Db_ActionListener{
    public void DbActionReceived(Db_ActionEvent event) {
        if(event.Status() == Db_Status.PENDING)
        {
            System.out.println("DB is busy!" );
        }
        else if(event.Status() == Db_Status.CLOSED)
        {
            System.out.println("DB connection is closed!");
        }
        else if(event.Status() == Db_Status.CONNECTED)
        {
            System.out.println("DB is connected!");
        }
        else
        {
            System.out.println("DB idle!" );
        }
    }
}

Finally you must establish the trigger mechanism. You may have a central class that handles all of the database accesses. There you will have to

• Create two central methods to open and close connections
• Add some methods to control the listener

• Adapt the constructor to add right from the creation of the class a listener to it.

  public class Db_DataExchange{

  //
  // Adaptions to the constructor
  //
  
  public Db_DataExchange() 
  {
      this.DBStateListeners = new ArrayList();
  
      // Your constructor code here ...
  
      UI_DbProgressbar listener = new UI_DbProgressbar();
      this.addDbActionListener(listener);
  }
  
  //
  // Add some methods to control the listener
  //
  
  private Db_Status DBAccessStatus = Db_Status.IDLE;
  private List DBStateListeners;
  
  public synchronized void addDbActionListener(Db_ActionListener l) {
      DBStateListeners.add( l );
  }
  
  public synchronized void removeDbActionListener(Db_ActionListener l) {
      DBStateListeners.remove( l );
  }
  
  private synchronized void fireDbActionEvent(Db_Status AccessStatus) 
  {
      DBAccessStatus = AccessStatus;
      Db_ActionEvent status = new Db_ActionEvent(this, DBAccessStatus);
      Iterator listeners = DBStateListeners.iterator();
      while(listeners.hasNext()) {
          ((Db_ActionListener) listeners.next()).DbActionReceived(status);
      }
  }
  
  //
  // Create two central methods to open and close connections
  //
  
  /**
   * Your method to establish a jdbc database connection  
   */
  public Connection EstablishDBConnection(String ConnectionURL){
      Connection c = null;
  
      try {
          // Load JDBC sqlite driver
          Class.forName(this.DBDriverName);
  
          // url - a database url of the form jdbc:subprotocol:subname
          c = DriverManager.getConnection(ConnectionURL);
  
          c.setAutoCommit(false);
  
          System.out.println("Database connected successfully!");
          // this ist the important trigger:
          this.fireDbActionEvent(Db_Status.CONNECTED);
  
          return c;
  
      } catch ( Exception e ) {
          System.err.println( e.getClass().getName() + ": " + e.getMessage() );
          this.fireDbActionEvent(Db_Status.ERROR);
          return c;
      }
  
  }
  
  /**
   * Your method to close a jdbc database connection 
   */
  public boolean CloseDBConnection(Connection c){
      try 
      {
          c.close(); // finally close
          // this ist the important trigger:
          this.fireDbActionEvent(Db_Status.CLOSED);
  
          return true;
  
      } catch ( Exception e ) 
      {
          //System.err.println( e.getClass().getName() + ": " + e.getMessage() );
          logger.error("Error in {}: {}",e.getClass().getName(),e.getMessage());
          this.fireDbActionEvent(Db_Status.ERROR);
  
          return false;
      }
  }
  

  }

这篇关于如何实现一个侦听器，用于访问Java中的sqlite数据库的事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！