![](https://i-blog.csdnimg.cn/blog_migrate/b6845d85ecbc18168f35685180235dc5.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/23dc9077e2848cfc918575e7dd913934.gif)
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();
// 开始遍历结果集
while(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();
// 开始遍历结果集
while(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();
// 开始遍历结果集
while(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;
}*/
}
![](https://i-blog.csdnimg.cn/blog_migrate/13aad3fdbe7e03afaad4966ab9795cc4.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/01aa0d003fc58154d94908a32055c4d7.gif)
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 id="getFormulaVal" 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 id="getFormulaVal20" 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 id="getFormulaValTime" 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 id="getFormulaValTimeTwo" 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 id="getSingleDataByTime" 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;
}
}
![](https://i-blog.csdnimg.cn/blog_migrate/1390e65cd820e462822cda0e3be01461.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/e3956662c8d9fc0c1a861f638a593fc8.gif)
Map<String, String> map = 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));
解决ResultSet结果为null空时默认返回0或者false的问题(获取值后可以使用rs.wasNull()来判断)
![](https://i-blog.csdnimg.cn/blog_migrate/9e146d8324444d5fbce1e8765b5aa682.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/60f8b1d6ef26b382a25645c6f30afcc4.gif)
public static Double getDoubleNull(ResultSet rs ,String key) throws Exception{
Double d = rs.getDouble(key);
if (rs.wasNull()) {
d=null;
}
return d;
}
![](https://i-blog.csdnimg.cn/blog_migrate/0d0615dce1073b982d2580c0368578ea.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/12c8d39381f01c22bc807d476e118c0f.gif)
####### yj数据源 ########
jdbc_YJEMS.driverClassName=dm.jdbc.driver.DmDriver
jdbc_YJEMS.url=jdbc:dm://127.0.0.1:5236/mdb
jdbc_YJEMS.username=SYSDBA
jdbc_YJEMS.password=ytd123456
####### hy数据源 ########
jdbc_HYEMS.driverClassName=oracle.jdbc.OracleDriver
jdbc_HYEMS.url=jdbc:oracle:thin:@127.0.0.1:1521:ORCL
jdbc_HYEMS.username=dbu
jdbc_HYEMS.password=ytd123456