JDBCUtil
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; }*/ }
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; } }
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()来判断）
public static Double getDoubleNull(ResultSet rs ,String key) throws Exception{ Double d = rs.getDouble(key); if (rs.wasNull()) { d=null; } return d; }
####### 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
