67,515
社区成员
发帖
与我相关
我的任务
分享
汗,代码忘记拷回来了,明天再说吧 - -
汗,代码忘记拷回来了,明天再说吧 - -
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Random;
import java.util.Set;
import java.util.logging.Logger;
import org.apache.commons.dbutils.DbUtils;
/**
* 性能测试2
* @author shajunxing
*/
public class PerformanceTest2 {
private static final Logger logger = Logger.getLogger(PerformanceTest2.class.getName());
private static final String driver = "XXX";
private static final String url = "XXX";
private static final String user = "XXX";
private static final String password = "XXX";
private static QueryCache cache = new QueryCache(driver, url, user, password, 1000000);
private static Map<String, List<String>> tables = new HashMap<String, List<String>>();
private static List<String> tableNames = new LinkedList<String>();
private static Random rand = new Random(Calendar.getInstance().getTimeInMillis());
private static void getTables() {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
DbUtils.loadDriver(driver);
try {
conn = DriverManager.getConnection(url, user, password);
// 获取所有表名称
logger.info("获取所有表名称");
try {
DatabaseMetaData dbMeta = conn.getMetaData();
rs = dbMeta.getTables(null, null, null, new String[]{"TABLE"});
if (rs != null) {
while (rs.next()) {
tables.put(rs.getString("TABLE_NAME"), new LinkedList<String>());
}
}
} catch (SQLException ex) {
logger.severe(ex.toString());
} finally {
DbUtils.closeQuietly(rs);
}
// 获取所有表的字段名称
Set<String> illegalTables = new HashSet<String>();
for (String tableName : tables.keySet()) {
logger.info(String.format("获取表%s的字段", tableName));
List<String> tableColumns = tables.get(tableName);
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(String.format("select * from %s where rownum=1", tableName));
ResultSetMetaData rsMeta = rs.getMetaData();
for (int i = 1; i <= rsMeta.getColumnCount(); i++) {
String columnName = rsMeta.getColumnName(i);
tableColumns.add(columnName);
}
} catch (SQLException ex) {
logger.severe(String.format("获取表%s的字段失败:%s", tableName, ex.toString()));
illegalTables.add(tableName);
} finally {
DbUtils.closeQuietly(rs);
DbUtils.closeQuietly(stmt);
}
}
// 删除非法的表
for (String illegal : illegalTables) {
tables.remove(illegal);
}
// 表名列表
for (String tableName : tables.keySet()) {
tableNames.add(tableName);
}
} catch (SQLException ex) {
logger.severe(ex.toString());
} finally {
DbUtils.closeQuietly(conn);
}
}
public static void main(String[] args) {
// 获取所有的表和字段名
getTables();
// 打印所有的表和字段名
for (String tableName : tables.keySet()) {
System.out.println(tableName);
for (String columnName : tables.get(tableName)) {
System.out.println(" " + columnName);
}
System.out.println();
}
// 用随机组合的SQL语句测试
long lastVisitedCount = 0;
long lastHitCount = 0;
long lastTime = Calendar.getInstance().getTimeInMillis();
for (int i = 0; i < 100; i++) {
// 每100次循环统计一次
for (int j = 0; j < 100; j++) {
String tableName = tableNames.get(rand.nextInt(tableNames.size()));
List<String> columnNames = tables.get(tableName);
String columnName = columnNames.get(rand.nextInt(columnNames.size()));
String sql = String.format("select \"%s\" from \"%s\"", columnName, tableName);
cache.cachedQuery(sql, true);
}
long visitedCount = cache.getVisitedCount();
long hitCount = cache.getHitCount();
long time = Calendar.getInstance().getTimeInMillis();
System.out.println(String.format("缓存总访问数:%d,总命中数:%d,当前大小:%d,命中率:%.2f%%,耗时:%d毫秒",
visitedCount,
hitCount,
cache.getSize(),
100.0 * (hitCount - lastHitCount) / (visitedCount - lastVisitedCount),
time - lastTime));
lastVisitedCount = visitedCount;
lastHitCount = hitCount;
lastTime = time;
}
// 按任意键继续
System.out.println("按任意键继续...");
try {
new BufferedReader(new InputStreamReader(System.in)).readLine();
} catch (IOException ex) {
logger.severe(ex.toString());
}
// 打印缓存中的键
for (String key : cache.getKeySet()) {
System.out.println(key);
}
}
}
import java.util.logging.Logger;
/**
* 性能测试1
* @author shajunxing
*/
public class PerformanceTest {
private static final Logger logger = Logger.getLogger(PerformanceTest.class.getName());
private static final String driver = "XXX";
private static final String url = "XXX";
private static final String user = "XXX";
private static final String password = "XXX";
private static final String sql = "XXX";
private static final int loopCount = 1000;
private static QueryCache cache = new QueryCache(driver, url, user, password, 1000000);
private static void testQuery() {
for (int i = 0; i < loopCount; i++) {
cache.query(sql, false);
}
}
private static void testPooledQuery() {
for (int i = 0; i < loopCount; i++) {
cache.query(sql, true);
}
}
private static void testCachedQuery() {
for (int i = 0; i < loopCount; i++) {
cache.cachedQuery(sql, false);
}
}
private static void testCachedPooledQuery() {
for (int i = 0; i < loopCount; i++) {
cache.cachedQuery(sql, true);
}
}
private static void testPooledQueryMS() {
Thread[] threads = new Thread[loopCount];
for (int i = 0; i < loopCount; i++) {
Thread thread = new Thread(new Runnable() {
public void run() {
cache.query(sql, true);
}
});
thread.start();
threads[i] = thread;
}
for (Thread thread : threads) {
try {
thread.join();
} catch (InterruptedException ex) {
logger.severe(ex.toString());
}
}
}
private static void testCachedQueryMS() {
Thread[] threads = new Thread[loopCount];
for (int i = 0; i < loopCount; i++) {
Thread thread = new Thread(new Runnable() {
public void run() {
cache.cachedQuery(sql, false);
}
});
thread.start();
threads[i] = thread;
}
for (Thread thread : threads) {
try {
thread.join();
} catch (InterruptedException ex) {
logger.severe(ex.toString());
}
}
}
private static void testCachedPooledQueryMS() {
Thread[] threads = new Thread[loopCount];
for (int i = 0; i < loopCount; i++) {
Thread thread = new Thread(new Runnable() {
public void run() {
cache.cachedQuery(sql, true);
}
});
thread.start();
threads[i] = thread;
}
for (Thread thread : threads) {
try {
thread.join();
} catch (InterruptedException ex) {
logger.severe(ex.toString());
}
}
}
public static void main(String[] args) {
testQuery();
testPooledQuery();
testCachedQuery();
testCachedPooledQuery();
testPooledQueryMS();
testCachedQueryMS();
testCachedPooledQueryMS();
}
}
import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.concurrent.atomic.AtomicLong;
import java.util.logging.Logger;
import org.apache.commons.collections.map.LRUMap;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
/**
* JDBC查询缓存
* @author shajunxing
*/
public class QueryCache {
private static final Logger logger = Logger.getLogger(QueryCache.class.getName());
private Map<String, List<Map<String, Object>>> cache = null;
private String driver;
private String url;
private String user;
private String password;
private ComboPooledDataSource cpds;
private AtomicLong visitedCount = new AtomicLong(0);
private AtomicLong hitCount = new AtomicLong(0);
/**
* 默认构造函数
* @param driver JDBC驱动类
* @param url 连接字符串
* @param user 用户名
* @param password 口令
* @param maxSize 查询缓存大小
*/
public QueryCache(String driver, String url, String user, String password, int maxSize) {
this.driver = driver;
this.url = url;
this.user = user;
this.password = password;
cache = Collections.synchronizedMap(new LRUMap(maxSize));
cpds = new ComboPooledDataSource();
cpds.setJdbcUrl(url);
cpds.setUser(user);
cpds.setPassword(password);
try {
Class.forName(driver);
cpds.setDriverClass(driver);
} catch (ClassNotFoundException ex) {
logger.severe(ex.toString());
} catch (PropertyVetoException ex) {
logger.severe(ex.toString());
}
}
public void clear() {
cache.clear();
}
/**
* 非缓存查询
* @param sql SQL语句
* @param pooled 是否使用连接池
* @return 查询结果
*/
public List<Map<String, Object>> query(String sql, boolean pooled) {
Connection conn = null;
try {
if (pooled) {
conn = cpds.getConnection();
} else {
conn = DriverManager.getConnection(url, user, password);
}
return new QueryRunner().query(conn, sql, new MapListHandler());
} catch (SQLException ex) {
logger.severe(ex.toString());
return null;
} finally {
DbUtils.closeQuietly(conn);
}
}
/**
* 缓存查询
* @param sql SQL语句
* @param pooled 是否使用连接池
* @return 查询结果
*/
public List<Map<String, Object>> cachedQuery(String sql, boolean pooled) {
visitedCount.incrementAndGet();
if (cache.containsKey(sql)) {
hitCount.incrementAndGet();
return cache.get(sql);
} else {
List<Map<String, Object>> result = query(sql, pooled);
cache.put(sql, result);
return result;
}
}
public long getVisitedCount() {
return visitedCount.get();
}
public long getHitCount() {
return hitCount.get();
}
public int getSize() {
return cache.size();
}
public Set<String> getKeySet() {
return cache.keySet();
}
}