import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* @Author chenrd
* @Date 2022-9-16 上午9:01:23
* @Version 1.0 业务说明：
*
*/
public class JDBCUtil {
protected static Logger logger = LoggerFactory.getLogger(JDBCUtil.class);
private static String URL;
private static String USER;
private static String PASSWORD;
private static String DRIVER;
static {
try {
Properties prop =getDataSourceInf();
URL = prop.getProperty("jdbc_YJEMS.url","jdbc:dm://127.0.0.1:5236/mdb");
USER = prop.getProperty("jdbc_YJEMS.username","sysdb");
PASSWORD = prop.getProperty("jdbc_YJEMS.password","xxx");
DRIVER = prop.getProperty("jdbc_YJEMS.driverClassName","dm.jdbc.driver.DmDriver");
// 注册驱动
Class.forName(DRIVER);
logger.info("注册驱动 成功！！！");
} catch (Exception e) {
logger.error("注册驱 失败:",e);
e.printStackTrace();
}
}
/**
* @return
* @throws Exception
* Properties 资源文件app.properties在根目录的conf目录中
*
*/
public static Properties getDataSourceInf() throws Exception {
Properties p = new Properties();
String dirPath = JDBCUtil.class.getClassLoader().getResource("/").getPath() + File.separator + "conf";
p.load(new FileInputStream(dirPath + File.separator + "app.properties"));
return p;
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(URL, USER, PASSWORD);
}
public static void releaseSource(Statement stmt, Connection conn) {
if (null != stmt) {
try {
stmt.close();
} catch (SQLException e) {
logger.error("Statement关闭失败:",e);
e.printStackTrace();
}
}
if (null != conn) {
try {
conn.close();
} catch (SQLException e) {
logger.error("Connection关闭失败:",e);
e.printStackTrace();
}
}
}
public static void releaseSource(ResultSet res, Statement stmt, Connection conn) {
if (null != res) {
try {
res.close();
} catch (SQLException e) {
logger.error("ResultSet关闭失败:",e);
e.printStackTrace();
}
}
releaseSource(stmt, conn);
}
/**
* 增加、删除、修改
* @param sql sql语句
* @param obj 参数
* @return
*/
public static boolean getDML(String sql,Object... obj){
Connection conn = null;
PreparedStatement ps = null;
try{
conn = getConnection();
ps = conn.prepareStatement(sql);
for (int i = 1; i <= obj.length; i++) {
ps.setObject(i, obj[i-1]);
}
logger.info("sql="+sql);
int update = ps.executeUpdate();
if (update > 0) {
return true;
}
}catch(Exception e){
logger.error("增加、删除、修改 报错",e);
}finally{
releaseSource(ps, conn);
}
return false;
}
/**
* @param sql
* @param obj
* @return //查询总记录数
* Integer
*
*/
public static Integer getCount(String sql, Object... obj) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
// 1.获取连接
conn = getConnection();
// 2.获取预处理对象
ps = conn.prepareStatement(sql);
// 循环参数，如果没有就不走这里
for (int i = 1; i <= obj.length; i++) {
// 注意：数组下标从0开始，预处理参数设置从1开始
ps.setObject(i, obj[i - 1]);
}
// 3.执行SQL语句
logger.info("sql=" + sql);
rs = ps.executeQuery();
// 开始遍历结果集
if (rs.next()) {
return rs.getInt(1);
}
// 5.关闭连接
} catch (Exception e) {
logger.error("查询总记录数 报错",e);
} finally {
releaseSource(rs, ps, conn);
}
return null;
}
/**
* @param sql
* @param obj
* @return
* Double
*
*/
public static Double getDouble(String sql, Object... obj) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
// 1.获取连接
conn = getConnection();
// 2.获取预处理对象
ps = conn.prepareStatement(sql);
// 循环参数，如果没有就不走这里
for (int i = 1; i <= obj.length; i++) {
// 注意：数组下标从0开始，预处理参数设置从1开始
ps.setObject(i, obj[i - 1]);
}
// 3.执行SQL语句
logger.info("sql=" + sql);
rs = ps.executeQuery();
// 开始遍历结果集
if (rs.next()) {
return rs.getDouble(1);
}
// 5.关闭连接
} catch (Exception e) {
logger.error("查询getDouble 报错",e);
} finally {
releaseSource(rs, ps, conn);
}
return null;
}
/**
* @param sql
* @param obj
* @return
* Double
*
*/
public static Double getDouble(String sql) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
// 1.获取连接
conn = getConnection();
// 2.获取预处理对象
ps = conn.prepareStatement(sql);
// 3.执行SQL语句
logger.info("sql=" + sql);
rs = ps.executeQuery();
// 开始遍历结果集
if (rs.next()) {
return rs.getDouble(1);
}
// 5.关闭连接
} catch (Exception e) {
logger.error("查询getDouble 报错",e);
} finally {
releaseSource(rs, ps, conn);
}
return null;
}
public static String getStr(String sql) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
// 1.获取连接
conn = getConnection();
// 2.获取预处理对象
ps = conn.prepareStatement(sql);
// 3.执行SQL语句
logger.info("sql=" + sql);
rs = ps.executeQuery();
// 开始遍历结果集
if (rs.next()) {
return rs.getString(1);
}
// 5.关闭连接
} catch (Exception e) {
logger.error("查询getString 报错",e);
} finally {
releaseSource(rs, ps, conn);
}
return null;
}
public static List<String> getStrList(String sql) {
List<String> list = new ArrayList<String>();
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
// 1.获取连接
conn = getConnection();
// 2.获取预处理对象
ps = conn.prepareStatement(sql);
// 3.执行SQL语句
logger.info("sql=" + sql);
rs = ps.executeQuery();
// 开始遍历结果集
if (rs.next()) {
list.add(rs.getString(1));
}
// 5.关闭连接
} catch (Exception e) {
logger.error("查询getStrList 报错",e);
} finally {
releaseSource(rs, ps, conn);
}
return list;
}
/**
* @param sql
* @param obj
* @return
* Double
*
*/
/* public static List<Ddrb> getOneNinetySixValList(String sql) {
List<Ddrb> list = new java.util.ArrayList<Ddrb>();
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
try {
// 1.获取连接
conn = getConnection();
// 2.获取预处理对象
ps = conn.prepareStatement(sql);
// 3.执行SQL语句
rs = ps.executeQuery();
// 开始遍历结果集
if (rs.next()) {
Ddrb e=new Ddrb();
Double d = rs.getDouble("qsnzgfh");
if (d!=null) {
e.setQsnzgfh(d+"");
}
Date date = rs.getDate("qsnzgfhdsj");
if (date!=null) {
e.setQsnzgfhdsj(sdf.format(date));
}
list.add(e);
}
// 5.关闭连接
} catch (Exception e) {
logger.error("查询getOneNinetySixValList 报错",e);
} finally {
releaseSource(rs, ps, conn);
}
return list;
}
public static List<Ddrb> getAllNinetySixValList(String sql) {
List<Ddrb> list = new java.util.ArrayList<Ddrb>();
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
try {
// 1.获取连接
conn = getConnection();
// 2.获取预处理对象
ps = conn.prepareStatement(sql);
// 3.执行SQL语句
rs = ps.executeQuery();
// 开始遍历结果集
if (rs.next()) {
Ddrb e=new Ddrb();
String d = rs.getString("tq");
e.setTq(d);
String xq = rs.getString("xq");
e.setTq(xq);
list.add(e);
}
// 5.关闭连接
} catch (Exception e) {
logger.error("查询getAllNinetySixValList 报错",e);
} finally {
releaseSource(rs, ps, conn);
}
return list;
}*/
}
JDBCUtil
import java.util.ArrayList;
import java.util.Map;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
*@Author chenrd
*@Date 2022-9-21 下午3:36:08
*@Version 1.0
*业务说明：
*
*/
public class YjJdbcSql {
protected static Logger logger = LoggerFactory.getLogger(YjJdbcSql.class);
/* <select resultType="Double" parameterType="Map">
select ${formula}(${colName})
from <![CDATA[${tableName}]]>
where
occur_time >= to_date(#{kssj}, 'yyyy-MM-dd') and
occur_time <![CDATA[<]]>
to_date(#{jssj}, 'yyyy-MM-dd')
</select>
*/
public static String getFormulaValSql(Map<String, String> map) {
String s=null;
s="select "+map.get("formula")+"("+map.get("colName")+") "+
" from " +map.get("tableName")+
" where occur_time >= " +
" to_date('"+map.get("kssj")+"', 'yyyy-MM-dd') " +
" and occur_time < to_date('"+map.get("jssj")+"', 'yyyy-MM-dd') ";
logger.info("getFormulaValSql sql=" + s);
return s;
}
/*
<select resultType="Double" parameterType="Map">
select ${formula}(${colName})
from <![CDATA[${tableName}]]>
where
occur_time >= to_date(#{kssj}, 'yyyy-MM-dd hh24:mi:ss') and
occur_time <![CDATA[<]]>
to_date(#{jssj}, 'yyyy-MM-dd hh24:mi:ss')
</select>
*/
public static String getFormulaVal20Sql(Map<String, String> map) {
String s=null;
s="select "+map.get("formula")+"("+map.get("colName")+") "+
" from " +map.get("tableName")+
" where occur_time >= " +
" to_date('"+map.get("kssj")+"', 'yyyy-MM-dd hh24:mi:ss') " +
" and occur_time < to_date('"+map.get("jssj")+"', 'yyyy-MM-dd hh24:mi:ss') ";
logger.info("getFormulaVal20Sql sql=" + s);
return s;
}
/*
<select resultType="String" parameterType="Map">
select to_char(occur_time, 'yyyy-MM-dd hh24:mi:ss')
from <![CDATA[${tableName}]]>
where
${colName} = ${val}
order by occur_time desc
</select> */
public static String getFormulaValTimeSql(Map<String, String> map) {
String s=null;
s="select to_char(occur_time, 'yyyy-MM-dd hh24:mi:ss')"+
" from " +map.get("tableName")+
" where " +map.get("colName")+ "=" +map.get("val")+
" order by occur_time desc ";
logger.info("getFormulaValTimeSql sql=" + s);
return s;
}
/*
<select resultType="String" parameterType="Map">
select to_char(occur_time, 'yyyy-MM-dd hh24:mi:ss')
from <![CDATA[${tableName}]]>
where
${colName} =
(
select ${formula}(${colName})
from <![CDATA[${tableName}]]>
where
occur_time >= to_date(#{kssj}, 'yyyy-MM-dd hh24:mi:ss') and
occur_time <![CDATA[<]]>
to_date(#{jssj}, 'yyyy-MM-dd hh24:mi:ss')
)
and
occur_time >= to_date(#{kssj}, 'yyyy-MM-dd hh24:mi:ss') and
occur_time <![CDATA[<]]>
to_date(#{jssj}, 'yyyy-MM-dd hh24:mi:ss')
order by occur_time desc
</select>
*/
public static String getFormulaValTimeTwoSql(Map<String, String> map) {
String s=null;
s="select to_char(occur_time, 'yyyy-MM-dd hh24:mi:ss') "+
" from " +map.get("tableName")+
" where "+map.get("colName")+" = ( ";
String s2=" select "+map.get("formula")+"("+map.get("colName")+") "+
" from " +map.get("tableName")+
" where " +
" occur_time >= to_date('"+map.get("kssj")+"', 'yyyy-MM-dd hh24:mi:ss') "+
" and occur_time < to_date('"+map.get("jssj")+"', 'yyyy-MM-dd hh24:mi:ss') ";
String s3=" ) and occur_time >= to_date('"+map.get("kssj")+"', 'yyyy-MM-dd hh24:mi:ss') "+
" and occur_time < to_date('"+map.get("jssj")+"', 'yyyy-MM-dd hh24:mi:ss') " +
" order by occur_time desc ";
logger.info("getFormulaValTimeTwoSql sql=" + s+s2+s3);
return s+s2+s3;
}
/*
<select resultType="java.lang.Double" parameterType="Map">
select ${colName}
from <![CDATA[${tableName}]]>
where
occur_time = to_date(#{tjrqTimeStamp}, 'yyyy-MM-dd hh24:mi:ss')
</select>*/
public static String getSingleDataByTimeSql(Map<String, String> map) {
String s=null;
s="select " +map.get("colName")+
" from " +map.get("tableName")+
" where occur_time = to_date('"+map.get("tjrqTimeStamp")+"', 'yyyy-MM-dd hh24:mi:ss') ";
logger.info("getSingleDataByTimeSql sql=" + s);
return s;
}
}
拼sql用
new HashMap<String, String>();
map.put("rqs", rqs);
map.put("tableName", "hisdb.tabxxx");
map.put("colName", "colxxx");
map.put("avg", "ddfddl");
map.put("tjrq", tjrq);
Double ddfddl_avgV =JDBCUtil.getDouble(YjJdbcSql.getAvgValSql(map));
调用