JDBC和多线程 [英] JDBC and Multithreading
问题描述
我正在尝试使用多线程方法运行少量查询，但是我认为我做错了，因为我的程序大约需要五分钟才能运行像
这样的简单select语句.SELECT * FROM TABLE WHERE ID = 123'
我的实现如下，并且我正在使用一个连接对象.
在我的运行方法中
public void run() {
runQuery(conn, query);
}
runQuery方法
public void runQuery(Connection conn, String queryString){
Statement statement;
try {
statement = conn.createStatement();
ResultSet rs = statement.executeQuery(queryString);
while (rs.next()) {}
} catch (SQLException e) {
e.printStackTrace();
}
}
最后，在main方法中，我使用下面的代码段启动线程.
MyThread bmthread = new MyThread(conn, query);
ArrayList<Thread> allThreads = new ArrayList<>();
double start = System.currentTimeMillis();
int numberOfThreads = 1;
for(int i=0; i<=numberOfThreads; i++){
Thread th = new Thread(bmthread);
th.setName("Thread "+i);
System.out.println("Starting Worker "+th.getName());
th.start();
allThreads.add(th);
}
for(Thread t : allThreads){
try {
t.join();
} catch (InterruptedException e) {
e.printStackTrace();
}
}
double end = System.currentTimeMillis();
double total = end - start;
System.out.println("Time taken to run threads "+ total);
更新:我现在为每个线程使用单独的连接.
ArrayList<Connection> sqlConn = new ArrayList<>();
for(int i =0; i<10; i++){
sqlConn.add(_ut.initiateConnection(windowsAuthURL, driver));
}
loop:
MyThread bmthread = new MyThread(sqlConn.get(i), query);
正如rohivats和Asaph所说，一个连接必须由一个线程使用，并且只有一个线程，即考虑使用数据库连接池.考虑到c3p0和类似的东西几乎被遗弃了，我会使用 HirakiCP ，它确实是快速而可靠的. /p>
如果您想要非常简单的内容，则可以使用线程安全集合(例如LinkedList)实现一个非常简单的连接池，例如:
public class CutrePool{
String connString;
String user;
String pwd;
static final int INITIAL_CAPACITY = 50;
LinkedList<Connection> pool = new LinkedList<Connection>();
public String getConnString() {
return connString;
}
public String getPwd() {
return pwd;
}
public String getUser() {
return user;
}
public CutrePool(String connString, String user, String pwd) throws SQLException {
this.connString = connString;
for (int i = 0; i < INITIAL_CAPACITY; i++) {
pool.add(DriverManager.getConnection(connString, user, pwd));
}
this.user = user;
this.pwd = pwd;
}
public synchronized Connection getConnection() throws SQLException {
if (pool.isEmpty()) {
pool.add(DriverManager.getConnection(connString, user, pwd));
}
return pool.pop();
}
public synchronized void returnConnection(Connection connection) {
pool.push(connection);
}
}
如您所见，getConnection和returnConnection方法被同步以确保线程安全.获得连接(conn = pool.getConnection();)，不要忘了在使用后返回/释放连接(pool.returnConnection(conn);)
I am trying to run few queries using a multithreaded approach, however I think I am doing something wrong because my program takes about five minute to run a simple select statement like
SELECT * FROM TABLE WHERE ID = 123'
My implementation is below and I am using one connection object.
In my run method
public void run() {
runQuery(conn, query);
}
runQuery method
public void runQuery(Connection conn, String queryString){
Statement statement;
try {
statement = conn.createStatement();
ResultSet rs = statement.executeQuery(queryString);
while (rs.next()) {}
} catch (SQLException e) {
e.printStackTrace();
}
}
Finally in the main method, I start the threads using the snippet below.
MyThread bmthread = new MyThread(conn, query);
ArrayList<Thread> allThreads = new ArrayList<>();
double start = System.currentTimeMillis();
int numberOfThreads = 1;
for(int i=0; i<=numberOfThreads; i++){
Thread th = new Thread(bmthread);
th.setName("Thread "+i);
System.out.println("Starting Worker "+th.getName());
th.start();
allThreads.add(th);
}
for(Thread t : allThreads){
try {
t.join();
} catch (InterruptedException e) {
e.printStackTrace();
}
}
double end = System.currentTimeMillis();
double total = end - start;
System.out.println("Time taken to run threads "+ total);
Update : I am now using separate connection for each thread.
ArrayList<Connection> sqlConn = new ArrayList<>();
for(int i =0; i<10; i++){
sqlConn.add(_ut.initiateConnection(windowsAuthURL, driver));
}
loop:
MyThread bmthread = new MyThread(sqlConn.get(i), query);
As rohivats and Asaph said, one connection must be used by one and only one thread, that said, consider using a database connection pool. Taking into account that c3p0 and similars are almost abandoned, I would use HirakiCP which is really fast and reliable.
If you want something very simple you could implement a really simple connection pool using a thread safe collection (such as LinkedList), for example:
public class CutrePool{
String connString;
String user;
String pwd;
static final int INITIAL_CAPACITY = 50;
LinkedList<Connection> pool = new LinkedList<Connection>();
public String getConnString() {
return connString;
}
public String getPwd() {
return pwd;
}
public String getUser() {
return user;
}
public CutrePool(String connString, String user, String pwd) throws SQLException {
this.connString = connString;
for (int i = 0; i < INITIAL_CAPACITY; i++) {
pool.add(DriverManager.getConnection(connString, user, pwd));
}
this.user = user;
this.pwd = pwd;
}
public synchronized Connection getConnection() throws SQLException {
if (pool.isEmpty()) {
pool.add(DriverManager.getConnection(connString, user, pwd));
}
return pool.pop();
}
public synchronized void returnConnection(Connection connection) {
pool.push(connection);
}
}
As you can see getConnection and returnConnection methods are synchronized to be thread safe. Get a connection (conn = pool.getConnection();) and don't forget to return/free a connection after being used (pool.returnConnection(conn);)
这篇关于JDBC和多线程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
