AnyLogic存取数据
功能简述
在anylogic中创建好工程并与数据库完成连接后。通过编写Java代码读取数据库中的数据。
步骤
- 连接数据库
在模型中配置好收据库连接信息
方式一：同上篇文章所描述拖出一个组件，做好连接的配置即可。
方式二：编写一个数据库连接与操作的类。可以解决多数据源的情况，避免在工程中建过多的DateBase组件，亦可保证基本代码的复用。示例代码如下：
数据库连接池
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.StrUtil;
import java.io.Serializable;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.concurrent.ConcurrentHashMap;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
* DataBaseConnection
*/
public class DataBaseConnection implements Serializable {
/**
* 数据库模式名称
*/
private static List<String> connNameList = new ArrayList<>();
/**
* 最大连接数
*/
private static final int MAX_POOL_SIZE = 10;
private static final int DEFAULT_POOL_SIZE = 5;
private static final Object lock = new Object();
/**
* 数据库模式--数据库连接池
*/
public static Map<String, List<java.sql.Connection>> connectionMap = new ConcurrentHashMap<>();
static {
//获取所有模式名称
Field[] fields = DBNameConstant.class.getFields();
for (int i = 0; i < fields.length; i++) {
try {
String connName = (String) fields[i].get(null);
if (null != connName) {
connNameList.add(connName);
}
} catch (Exception e) {
System.out.println("获取数据库模式名称失败!");
e.printStackTrace();
}
}
if (CollectionUtil.isNotEmpty(connNameList)) {
foundConnectionList();
}
}
private static void foundConnectionList() {
for (String schemaName : connNameList) {
Optional<DataBaseParam> param = DataBaseParam.dataBaseParamList.stream().filter(t -> t.getSchemaName().equals(schemaName)).findFirst();
if (param.isPresent()) {
DataBaseParam baseParam = param.get();
createConnectionList(schemaName, baseParam);
}
}
}
/**
* 创建连接集合
*
* @param schemaName
* @param baseParam
*/
private static void createConnectionList(String schemaName, DataBaseParam baseParam) {
List<java.sql.Connection> connections = new ArrayList<>();
try {
for (int i = 0; i < DEFAULT_POOL_SIZE; i++) {
java.sql.Connection connection = DriverManager.getConnection(baseParam.getUrl(), baseParam.getUserName(), baseParam.getPassword());
if (null != connection) {
connections.add(connection);
}
}
if (CollectionUtil.isNotEmpty(connections)) {
connectionMap.put(schemaName, connections);
}
} catch (Exception e) {
System.out.println("初始化创建数据库连接集合失败!");
e.printStackTrace();
}
}
/**
* 获取连接
*
* @param schemaName
* @return
*/
public static java.sql.Connection getConnection(String schemaName) {
synchronized (lock) {
if (StrUtil.isBlank(schemaName)) {
return null;
}
if (CollectionUtil.isEmpty(connectionMap)) {
foundConnectionList();
List<java.sql.Connection> connectionList = connectionMap.get(schemaName);
if (CollectionUtil.isNotEmpty(connectionList)) {
return connectionList.remove(connectionList.size() - 1);
}
} else {
List<java.sql.Connection> connections = connectionMap.get(schemaName);
if (CollectionUtil.isNotEmpty(connections)) {
return connections.remove(connections.size() - 1);
} else {
try {
Optional<DataBaseParam> baseParam = DataBaseParam.dataBaseParamList.stream().filter(t -> t.getSchemaName().equals(schemaName)).findFirst();
if (baseParam.isPresent()) {
DataBaseParam param = baseParam.get();
createConnectionList(schemaName, param);
List<java.sql.Connection> connectionList = connectionMap.get(schemaName);
if (CollectionUtil.isNotEmpty(connectionList)) {
return connectionList.remove(connectionList.size() - 1);
}
}
} catch (Exception e) {
System.out.println("获取数据库连接失败!");
e.printStackTrace();
return null;
}
}
}
}
return null;
}
/**
* 回收连接
*
* @param connection
* @param schemaName
*/
public static void releaseConnection(java.sql.Connection connection, String schemaName) {
synchronized (lock) {
if (null == connection || StrUtil.isBlank(schemaName)) {
return;
}
List<java.sql.Connection> connections = connectionMap.get(schemaName);
if (connections.size() < MAX_POOL_SIZE) {
connections.add(connection);
return;
}
try {
connection.close();
} catch (SQLException e) {
System.out.println("关闭数据库连接失败!");
e.printStackTrace();
}
}
}
/**
* 默认构造器
*/
public DataBaseConnection() {
}
@Override
public String toString() {
return super.toString();
}
/**
* 这个数字在这里是出于模型快照存储的目的<br>
* 当这个类改变时需要改变它
*/
private static final long serialVersionUID = 1L;
}
数据库工具类
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.StrUtil;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
*
* 数据库执行工具类
* 需要设置 database 或者 datList 数据库配置 *
* DataBaseService
*/
public class DataBaseService implements Serializable {
/**
* 查询
* @param schemaName
* @param sql
* @return
*/
public static List<Map<String, String>> queryData(String schemaName, String sql) {
java.sql.Connection con = null;
java.sql.Statement sta = null;
java.sql.ResultSet res = null;
List<Map<String, String>> resultList = new ArrayList<Map<String, String>>();
try {
con = carryFlow(schemaName, sql);
if (null == con) {
return null;
}
if(!con.getAutoCommit()){
con.setAutoCommit(true);
}
sta = con.createStatement();
if(null == sta){
return null;
}
res = sta.executeQuery(sql);
resultList = convertResult(res);
} catch (Exception e){
e.printStackTrace();
return null;
}finally {
closeResultSet(res);
closeAssets(con, sta, schemaName);
}
return resultList;
}
/**
* 转换结果
* @param resultSet
* @return
*/
private static List<Map<String, String>> convertResult(java.sql.ResultSet resultSet){
List<Map<String, String>> resultList = new ArrayList<Map<String, String>>();
if(null == resultSet){
return resultList;
}
try {
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
while (resultSet.next()) {
Map<String, String> resultMap = new HashMap<>();
for (int i = 1; i <= columnCount; i++) {
String key = metaData.getColumnName(i);
Object value = resultSet.getObject(i);
resultMap.put(key, StrUtil.toString(value));
}
if(CollectionUtil.isNotEmpty(resultMap)){
resultList.add(resultMap);
}
}
} catch (SQLException e) {
System.out.println("转换结果集失败！");
e.printStackTrace();
}
return resultList;
}
/**
* 更新
*/
public static void updateData(String schemaName, String sql) {
java.sql.Connection con = null;
java.sql.Statement sta = null;
try {
con = carryFlow(schemaName, sql);
if(null == con){
return;
}
//取消自动提交
con.setAutoCommit(false);
sta = con.createStatement();
if(null == sta){
return;
}
sta.executeUpdate(sql);
//手动提交
con.commit();
} catch (Exception e) {
connRollback(con);
e.printStackTrace();
}finally {
closeAssets(con, sta, schemaName);
}
}
/**
* 删除
*/
public static void deleteData(String schemaName, String sql) {
java.sql.Connection con = null;
java.sql.Statement sta = null;
try {
con = carryFlow(schemaName, sql);
if(null == con){
return;
}
//取消自动提交
con.setAutoCommit(false);
sta = con.createStatement();
if(null == sta){
return;
}
sta.execute(sql);
con.commit();
} catch (Exception e) {
connRollback(con);
e.printStackTrace();
}finally {
closeAssets(con, sta, schemaName);
}
}
/**
* 添加
*/
public static void addData(String schemaName, String sql) {
java.sql.Connection con = null;
java.sql.Statement sta = null;
try {
con = carryFlow(schemaName, sql);
if(null == con){
return;
}
//取消自动提交
con.setAutoCommit(false);
sta = con.createStatement();
if(null == sta){
return;
}
sta.execute(sql);
con.commit();
} catch (Exception e) {
connRollback(con);
e.printStackTrace();
}finally {
closeAssets(con, sta, schemaName);
}
}
/**
* 添加
*/
public static void addBatch(String schemaName, String sql) {
java.sql.Connection con = null;
java.sql.Statement sta = null;
try {
con = carryFlow(schemaName, sql);
if(null == con){
return;
}
//取消自动提交
con.setAutoCommit(false);
sta = con.createStatement();
if(null == sta){
return;
}
//对sql语句进行分割
if (sql.contains(";")) {
String[] str = sql.split(";");
for (String s : str) {
sta.addBatch(s);
}
} else {
sta.addBatch(sql);
}
//进行批量提交
sta.executeBatch();
con.commit();
} catch (Exception e) {
connRollback(con);
e.printStackTrace();
}finally {
closeAssets(con, sta, schemaName);
}
}
/**
* 异常回滚
*/
private static void connRollback(java.sql.Connection conn){
if(null == conn){
return;
}
try{
conn.rollback();
}catch(Exception e){
e.printStackTrace();
}
}
/**
* 关闭ResultSet
* @param resultSet
*/
public static void closeResultSet(java.sql.ResultSet resultSet){
if(null == resultSet){
return;
}
try {
resultSet.close();
} catch (SQLException e) {
System.out.println("关闭ResultSet失败！");
e.printStackTrace();
}
}
/**
* 关闭资源
* @param con
* @param sta
*/
private static void closeAssets(java.sql.Connection con, java.sql.Statement sta, String schemaName) {
try {
if (null != sta) {
sta.close();
}
} catch (SQLException e) {
System.out.println("关闭Statement失败！");
e.printStackTrace();
}
try {
if (null != con) {
DataBaseConnection.releaseConnection(con, schemaName);
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 获取数据库连接
* @param schemaName
* @param sql
* @return
* @throws Exception
*/
private static java.sql.Connection carryFlow(String schemaName, String sql) throws Exception {
if(StrUtil.isBlank(schemaName) || StrUtil.isBlank(sql)){
throw new Exception("数据库模式名称或SQL语句不能为空");
}
java.sql.Connection conn = null;
try{
conn = DataBaseConnection.getConnection(schemaName);
}catch (Exception e){
e.printStackTrace();
}
return conn;
}
/**
* 数据库连接获取
* @param schemaName
* @param sql
* @return
* @throws Exception
*/
public static java.sql.Connection getConnection(String schemaName) throws Exception {
if(StrUtil.isBlank(schemaName)){
throw new Exception("数据库模式名称不能为空");
}
java.sql.Connection conn = null;
try{
conn = DataBaseConnection.getConnection(schemaName);
}catch (Exception e){
e.printStackTrace();
}
return conn;
}
/**
* 默认构造器
*/
public DataBaseService() {
}
@Override
public String toString() {
return super.toString();
}
/**
* 这个数字在这里是出于模型快照存储的目的<br>
* 当这个类改变时需要改变它
*/
private static final long serialVersionUID = 1L;
}
- 收集数据/组织查询条件
编写读取数据库的方法
读取数据代码示例：
其中数据库模式名与表名用常量代替了
public static ArrayList<ObjTable> getObjTableList(String param) {
ArrayList<ObjTable> objTableList = new ArrayList<>();
try (java.sql.Connection conn = DataBaseService.getConnection(DBNameConstant.BASIC_DATA;
java.sql.PreparedStatement ps = conn.prepareStatement("SELECT * FROM " + DBNameConstant.BASIC_DATA.concat(".").concat(DBTableNameConstant.OBJ_TABLE) + " WHERE param = ?")) {
ps.setString(1, param);
try (java.sql.ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
ObjTable objTable = new ObjTable();
objTable.setId(rs.getLong("id"));
objTable.setParam(rs.getString("param"));
objTableList.add(objTable);
}
}
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
return objTableList;
}
- 存取数据
根据收集的数据向数据库表中插入，根据查询条件查询数据库表。
代码示例：
public static void objTableOut(List<ObjTable> objTableList) {
try {
java.sql.Connection conn = null;
java.sql.PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = DataBaseService.getConnection(DBNameConstant.BASIC_DATA);
String sql = "insert into " + DBNameConstant.BASIC_DATA.concat(".").concat(DBTableNameConstant.OBJ_TABLE)
+ "(id, param) VALUES (?,?)";
ps = conn.prepareStatement(sql);
conn.setAutoCommit(false);
for (int i = 0; i < objTableList.size(); i++) {
ps.setInt(1, objTableList.get(i).getId());
ps.setString(2, objTableList.get(i).getParam());
ps.addBatch();
if (i % 1000 == 0) {
ps.executeBatch();
ps.clearBatch();
}
}
// 最后一次
ps.executeBatch();
ps.clearBatch();
// 执行sql语句
conn.commit();
// 关闭数据库连接对象
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
} catch (Exception e) {
}
}
对于上述代码编写完成后，只需在需要使用的时候在定时任务或者时间表中调用即可。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
