用于 SQL 更新语句的 Java 单工作线程 [英] Java single worker thread for SQL update statements

问题描述

我正在一个基于 Java 的服务器上工作，其中我将有多个线程(每个连接的用户一个线程 + 一些额外的线程).将涉及一些数据库连接，所以我在想每次服务器对数据库进行 SELECT 查询时，它都会为此启动一个新线程，以防止当前线程阻塞.我打算为此使用连接池，我想我知道该怎么做.(我研究过C3P0)但是，会有很多UPDATE 语句也涉及到，但是直接运行这些语句并不重要，这里有延迟也没关系.由于可能有很多 UPDATE 语句，我正在考虑为所有 UPDATE 语句设置一个工作线程.正如我所看到的，这将具有能够重用 PreparedStatement-objects

I'm working on a Java-based server in which I will have multiple threads (one thread for each connected user + some extra). There will be some database connection involved, so I was thinking that each time the server makes a SELECT query to the database it will start a new thread for this, to prevent blocking from the current thread. I'm planning on using a connection pool for this and I think I know how to do that. (I've looked into C3P0) However, there will be a lot of UPDATE statements involved also, but it's not important that these are ran directly, it's ok with a delay here. And since there might be a lot of UPDATE statements, I'm thinking of having a single worker thread for all UPDATE statements. As I see it, this will have the advantage of being able to re-use PreparedStatement-objects

问题:我怎样才能从其他线程告诉 UPDATE-worker 线程运行一些语句?我知道多线程以及如何使用同步块使线程相互对话"，但是涉及到数据库时，它突然感觉更复杂了.我读过准备好的语句和连接不应该在线程之间共享.

The question: How can I, from the other threads, tell the UPDATE-worker thread to run some statements? I know about multithreading and how to make threads "talk" to each other using synchronized blocks, but with the database involved it suddenly feels more complex. I have read that prepared statements and connections should not be shared between threads.

我现在关于如何解决它的想法:(感觉不是一个好的解决方案)

The idea I have right now on how to solve it: (doesn't feel like a good solution)

使用自定义类的 LinkedBlockingQueue(或另一种队列)，其中包含有关调用哪种 UPDATE 语句以及发送哪些参数的信息.然后工作线程将在收到通知时从该队列中读取(当将某些内容添加到队列时)并在那里运行适当的方法，该方法将使用适当的准备好的语句，设置参数并调用它.

Use a LinkedBlockingQueue (or another kind of Queue) of a custom class with information about which kind of UPDATE statement to call and which parameters to send it. And then the worker thread will read from this queue when it's notified (which it will be when something is added to the queue) and there it will run the appropriate method which will use the appropriate prepared statement, set the params, and call it.

我认为自己使用这种方法的一个不好的想法是参数可能是整数、字符串、双精度或其他.如何将它们存储在自定义类中?将它们全部存储为字符串感觉不太好.

A bad think that I see myself with this approach is that the params might be ints, String, double, or whatever. How to store them in the custom class? Doesn't feel good to store them all as String.

我是在正确的轨道上还是有更好的方法来解决这个问题?

Am I on the right track here or is there a better way to solve this?

推荐答案

不需要显式阻塞队列.您可以拥有一个由 ExecutorService.至于值，你可以使用泛型:

No need for an explicit blocking queue. You can have a worker thread and a work queue encapsulated by an ExecutorService. As for the values, you can use generics:

class ThreadTask<T> implements Runnable {
    private T value;

    public ThreadTask(T value) {
        this.value = value;
    }

    public void run() {
        // update based on value
    }
}

...

ExecutorService exec = Executors.newSingleThreadExecutor();
exec.submit(new ThreadTask<String>("asdf"));
exec.submit(new ThreadTask<Integer>(1));

单线程执行器只是一个在队列中等待并按顺序执行提交的任务的工作线程.无需其他显式管理.

The single thread executor is simply a worker thread waiting on a queue and executing the submitted tasks in order. No need for other explicit management.

这篇关于用于 SQL 更新语句的 Java 单工作线程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！