public static Connection getConnection() { // 用于获得数据库连接的工具方法
try {
DbUtils.loadDriver(DRIVER); // 加载驱动
conn = DriverManager.getConnection(URL, USERNAME, PASSWORD); // 建立连接
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public String loadSchema() {
save();
loadSchemaResult = false;
Connection conn = null;
String url =
"jdbc:mysql://"
+ dbconfig.getHost()
+ ":"
+ dbconfig.getPort()
+ "/"
+ dbconfig.getDatabase_name();
String sql_tables =
String.format(
"select TABLE_SCHEMA, TABLE_NAME, TABLE_COMMENT from information_schema.tables where TABLE_SCHEMA=\"%s\";",
dbconfig.getDatabase_name());
String sql_columns =
String.format(
"select TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, COLUMN_KEY, COLUMN_COMMENT from information_schema.columns where TABLE_SCHEMA=\"%s\";",
dbconfig.getDatabase_name());
String sql_keys =
String.format(
"select CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME from information_schema.KEY_COLUMN_USAGE where TABLE_SCHEMA=\"%s\";",
dbconfig.getDatabase_name());
try {
DbUtils.loadDriver(driver);
conn = DriverManager.getConnection(url, dbconfig.getUsername(), dbconfig.getPassword());
QueryRunner qr = new QueryRunner();
List<Map<String, Object>> tables = qr.query(conn, sql_tables, new MapListHandler());
List<Map<String, Object>> columns = qr.query(conn, sql_columns, new MapListHandler());
List<Map<String, Object>> keys = qr.query(conn, sql_keys, new MapListHandler());
dbconfig.setTables_json(JSONUtil.serialize(tables, false));
dbconfig.setColumns_json(JSONUtil.serialize(columns, false));
dbconfig.setKeys_json(JSONUtil.serialize(keys, false));
DbUtils.closeQuietly(conn);
} catch (Exception ex) {
return ERROR;
}
loadSchemaResult = true;
SaveLog.saveLog(
new Users((long) 1),
"11",
"数据库映射",
new Date(),
"数据库-企业数据库-模板映射—导入数据库配置方案",
Constant.DATABASE_OPERATION,
this.getClass().getName());
return SUCCESS;
}
/** **数据库连接*** */
public static Connection getConnection() {
if (conn == null) {
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost/test?useUnicode=true&characterEncoding=utf-8";
DbUtils.loadDriver(driver);
try {
conn = DriverManager.getConnection(url, "root", "devpassword123");
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return conn;
}
public static Connection getConnection() {
String url =
"jdbc:mysql://localhost:3306/sshe?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull";
String driverClassName = "com.mysql.jdbc.Driver";
String username = "******";
String password = "******";
Connection conn = null;
DbUtils.loadDriver(driverClassName);
try {
conn = DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public void doJob() {
String url = "jdbc:mysql://192.168.51.145:3306/product";
String jdbcDriver = "com.mysql.jdbc.Driver";
String user = "******";
String password = "******";
DbUtils.loadDriver(jdbcDriver);
Connection conn = null;
try {
conn = (Connection) DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
System.out.println("连接MySQL失败,url=" + url);
Tool.exitSystem();
}
String sql =
"select asset_id,asset_name,borrower_uid,borrower_name,total_amount,"
+ "annual_rate,add_rate,phase_total,phase_mode,repay_mode,"
+ "full_time "
+ "from product.t_assets where asset_id=%d";
QueryRunner qr = new QueryRunner();
ResultSetHandler<List<AssetInfo>> handler = new BeanListHandler<AssetInfo>(AssetInfo.class);
List<AssetInfo> assets = null;
try {
assets = qr.query(conn, sql, handler);
} catch (SQLException e) {
e.printStackTrace();
System.out.println("SQL查询错误,sql=" + sql);
Tool.exitSystem();
}
// System.out.println(loans);
// Tool.initUidMapFromLoans(assets);
for (AssetInfo asset : assets) {
long capital = asset.total_amount;
long annualRate = asset.annual_rate;
long periods = asset.phase_total;
long periodType = asset.phase_mode;
String valueDate = "2016-09-21 00:00:00";
int repaymentMode = asset.repay_mode;
RepaymentSchedule rs =
new RepaymentSchedule(capital, annualRate, periods, periodType, valueDate);
rs.calRepaymentSchedule(repaymentMode);
List<Repayment> repayments = rs.getRepaymentList();
checkRepayments(conn, asset, repayments);
}
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
System.out.println("关闭MySQL连接失败");
Tool.exitSystem();
}
saveFile();
}
