在使用mysql进行开发的时候,经常会遇到如何控制mysql连接数的问题。比较好的解决方案就是使用一个连接池,控制连接数。org.apache.commons.pool2中提供了对象池管理的框架,可以很方便的解决这个问题。
具体实现
import java.sql.{Connection, DriverManager}
import org.apache.commons.pool2.impl.{DefaultPooledObject, GenericObjectPool}
import org.apache.commons.pool2.{BasePooledObjectFactory, PooledObject}
object MysqlConnectionPool {
def apply(url: String, userName: String, password: String, className: String): GenericObjectPool[Connection] = {
new GenericObjectPool[Connection](new MysqlConnectionFactory(url, userName, password, className))
}
}
class MysqlConnectionFactory(url: String, userName: String, password: String, className: String) extends BasePooledObjectFactory[Connection]{
override def create(): Connection = {
Class.forName(className)
DriverManager.getConnection(url, userName, password)
}
override def wrap(conn: Connection): PooledObject[Connection] = new DefaultPooledObject[Connection](conn)
override def validateObject(pObj: PooledObject[Connection]) = !pObj.getObject.isClosed
override def destroyObject(pObj: PooledObject[Connection]) = pObj.getObject.close()
}
测试
import java.sql.{Connection, ResultSet, Statement}
import org.apache.commons.pool2.impl.{GenericObjectPool, GenericObjectPoolConfig}
object RunApp {
def getData(pool: GenericObjectPool[Connection]): Unit ={
var stmt: Statement = null
var rs: ResultSet = null
try {
val conn = pool.borrowObject()
stmt = conn.createStatement
rs = stmt.executeQuery("SELECT * FROM user")
for (index <- 1 to rs.getMetaData.getColumnCount){
println(rs.getMetaData.getColumnName(index))
}
Thread.sleep(10000)
pool.returnObject(conn)
} catch {
case ex: Exception =>
// handle any errors
System.out.println("SQLException: " + ex.getMessage)
}
}
def main(args: Array[String]): Unit = {
val conf = new GenericObjectPoolConfig
conf.setMaxTotal(2)
conf.setMaxIdle(1)
conf.setMaxWaitMillis(20000)
val pool = MysqlConnectionPool("jdbc:mysql://localhost:3306/tutorials", "root", "root", "com.mysql.jdbc.Driver")
pool.setConfig(conf)
for (_ <- 1 to 3){
new Thread(new Runnable {
override def run(): Unit = {
getData(pool)
}
}).start()
}
}
}
测试代码中通过conf.setMaxTotal(2)
设置最大使用的连接数为2,也就是说只能同时有两个对象连接mysql,其他对象等待,并且等待的超时时间为conf.setMaxWaitMillis(20000)
。那么在main
函数中启动了3个线程同时对数据库进行查询操作,并且每次查询都会sleeep 10秒。程序运行的时候可以通过show full processlist;
查看mysql的连接数,会发现只有2个对象会同时连接mysql,只有等其中一个运行结束后pool.returnObject(conn)
将连接返还给连接池,第三个线程才会得到资源连接mysql。
conf.setMaxIdle(1)
的作用是最多处于idle状态的对象的个数,如果超过这个数量,会自动调用MysqlConnectionFactory
中的destroyObject
方法销毁对象。
总结
通过本文介绍的方式可以有效的控制mysql的连接数。
网友评论