jdbc mysql 缓存_使用连接池和缓存机制，处理连接数据库操作

因为最近在研究kettle，用到参数转换的功能，领导让用java代码写处理转换的功能，然后用kettle调用，发现如果java代码不做优化，4万多的数据，要6分钟左右才能跑完，以下是我代码的优化处理，处理完后，4万数据，11秒左右就抽取和处理完毕。因为我这现在没什么复杂的处理业务逻辑，大家可以参考看看

1.首先，创建转换的实体类

package entity;

public class Comparison {

private String id;

private String codetype;

private String codename;

private String newcode;

private String newcodename;

private String oldcode;

private String oldcodename;

private String filename;

private String remark;

public String getId() {

return id;

}

public void setId(String id) {

this.id = id;

}

public String getCodetype() {

return codetype;

}

public void setCodetype(String codetype) {

this.codetype = codetype;

}

public String getCodename() {

return codename;

}

public void setCodename(String codename) {

this.codename = codename;

}

public String getNewcode() {

return newcode;

}

public void setNewcode(String newcode) {

this.newcode = newcode;

}

public String getNewcodename() {

return newcodename;

}

public void setNewcodename(String newcodename) {

this.newcodename = newcodename;

}

public String getOldcode() {

return oldcode;

}

public void setOldcode(String oldcode) {

this.oldcode = oldcode;

}

public String getOldcodename() {

return oldcodename;

}

public void setOldcodename(String oldcodename) {

this.oldcodename = oldcodename;

}

public String getFilename() {

return filename;

}

public void setFilename(String filename) {

this.filename = filename;

}

public String getRemark() {

return remark;

}

public void setRemark(String remark) {

this.remark = remark;

}

}

2，创建连接池和数据库的连接

package util;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.util.Vector;

public class JdbcUtil {

private static final Vector pool = new Vector();

private static final int MAX_SIZE = 50;

private static final int MIN_SIZE = 10;

private static Connection createConnection(){

Connection conn = null;

try {

Class.forName("com.mysql.jdbc.Driver");

conn = DriverManager.getConnection(

"jdbc:mysql://192.168.4.143:3306/loan2","root","123456");

} catch (ClassNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return conn;

}

static {

for (int i = 0; i < MIN_SIZE; i++) {

pool.add(createConnection());

}

}

public static synchronized Connection getConnection() {

Connection conn = null;

//conn = createConnection();

if (pool.isEmpty()) {

conn = createConnection();

} else {

int last_idx = pool.size() - 1;

conn = (Connection) pool.get(last_idx);

pool.remove(conn);

}

return conn;

}

public static synchronized void close(Connection conn) {

if (pool.size() < MAX_SIZE) {

pool.add(conn);

} else {

try {

conn.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

}

3，使用缓存，把数据库读到的数据放到缓存里

package transform;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.HashMap;

import java.util.Map;

import java.util.concurrent.locks.Lock;

import util.JdbcUtil;

public class trans {

public static void main(String[] args) {

trans trans = new trans();

String code = trans.transCode("SEXCODE", "");

}

static int num;

static Map> paramTypeKeyValueMap = new HashMap>();

static Lock lock = new java.util.concurrent.locks.ReentrantLock();//同步，需要手动释放资源

public String transCode(String codetype, String oldcode) {

num++;

Thread.currentThread().setName("zhengxin" + num);

if (oldcode == null) {

return null;

}

Map keyValue = paramTypeKeyValueMap.get(codetype);

if (keyValue == null) {

try {

lock.lock();//获取锁

keyValue = paramTypeKeyValueMap.get(codetype);

if (keyValue == null) {

keyValue = load2Cache(codetype);

}

} finally {

lock.unlock();// 释放锁

}

}

if (keyValue.get(oldcode) != null) {

return keyValue.get(oldcode);

} else {

return oldcode;

}

}

private Map load2Cache(String codetype) {

Map keyValue = new HashMap();

Connection conn = JdbcUtil.getConnection();

Statement stmt = null;

try {

stmt = conn.createStatement();

String sql = "select newcode,oldcode from comparison where codetype='" + codetype + "'";

ResultSet rs = stmt.executeQuery(sql);

paramTypeKeyValueMap.put(codetype, keyValue);

while (rs.next()) {

String newcode = rs.getString(1);

String oldc = rs.getString(2);

if (oldc == null) {

continue;

}

String[] codes = oldc.split(",");

for (String s : codes) {

if (s == null) {

continue;

}

keyValue.put(s, newcode);

}

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} finally {

if (stmt != null) {

try {

stmt.close();

} catch (Throwable e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

if (conn != null) {

JdbcUtil.close(conn);

}

}

return keyValue;

}

}