Hibernate/JDBC 调用数据库过程、函数

Hibernate可执行原生SQL，使用SQLQuery

在Dao层

@Override
      //date为参数...，用List<Map<String, Object>>接受过程/函数返回的结果
    public List<Map<String, Object>> getResult(String date) { 
        //若为过程
        //{call过程名}， ?个数为参数个数
        String sql = "{call sn_pm_sys.Pr_Get_EmpPunch_Summary_Dynamic(?)}";  
        //若为函数
        /*select函数名，?个数同样为参数个数
        String sql = "SELECT sn_pm_sys.fu_get_working_place(?,?)";
        */
        String res = null;
        Session session = getSessionFactory().getCurrentSession();
        SQLQuery query = session.createSQLQuery(sql);
        query.setParameter(0, date); /*赋参数，第一个为0。query.setParameter(1, ...);
                                                         query.setParameter(2, ...);*/
        query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map<String, Object>> list = query.list(); //接受过程/函数的结果
        return list;

    }

JDBC

先连接数据库，如mysql

//取名为LinkDB 
public class LinkDB {
	
	public static Connection getMySqlConnection() {
		Connection connection = null;
		//输入自己的数据库信息
		String url = "jdbc:mysql://192.168.1.142:3306/sn_pm_sys?useUnicode=true&characterEncoding=utf-8";
		String user = "sa";
		String pwd = "sa010203";
		String driverName = "com.mysql.jdbc.Driver"; //8.0为 com.mysql.cj.jdbc.Driver
		try {
			Class.forName(driverName);
			connection = (Connection) DriverManager.getConnection(url, user, pwd);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		return connection;
	}
 
	/**
	 * 关闭释放所有的资源
	 * 
	 * @author 
	 */
	public static void close(Connection con, PreparedStatement ps, ResultSet rs) {
		//System.out.printf("close");
		if (rs != null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (ps != null){
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (con != null){
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public static List convertList(ResultSet rs) throws SQLException {
		List list = new ArrayList();
		ResultSetMetaData md = rs.getMetaData();//获取键名
		int columnCount = md.getColumnCount();//获取行的数量
		while (rs.next()) {
			Map rowData = new HashMap();//声明Map
			for (int i = 1; i <= columnCount; i++) {
				rowData.put(md.getColumnName(i), rs.getObject(i));//获取键名及值
			}
			list.add(rowData);
		}
		return list;
	}

	public static List<Map<String,Object>> convertListMap(ResultSet rs) throws SQLException {
		List list = new ArrayList();
		ResultSetMetaData md = rs.getMetaData();//获取键名
		int columnCount = md.getColumnCount();//获取行的数量
		while (rs.next()) {
			Map<String, Object> map = new HashMap<>();
			for (int i = 1; i <= columnCount; i++) {// 遍历获取对当前行的每一列的键值对，put到map中
				// rs.getObject(i) 获得当前行某一列字段的值
				map.put(md.getColumnName(i).toLowerCase(), rs.getObject(i));
			}
			list.add(map);
		}
		return list;
	}

}

//写一个主函数测试
//不加throws SQLException,记得用try catch
	public static void main(String args[]) throws SQLException {
		Connection connection = LinkDB.getMySqlConnection(); //获取链接
		//过程
		String procStr = "{call sn_pm_sys.Pr_Test_Procedure_Call(?,?)}"; 
		//函数
		/*
		String procStr ="select fu_is_working_day(?)";
         */
		CallableStatement callableStatement = (CallableStatement) connection.prepareCall(procStr);
		callableStatement.setString(1, "2020-08-06"); //赋参数
		callableStatement.setString(2, "2022-10-06"); //赋参数
		
		callableStatement.execute();
		ResultSet resultSet = callableStatement.getResultSet();

		while (resultSet.next()) { //遍历结果
			
			String d1=resultSet.getString("Dates"); //通过列名获取结果
			String d2=resultSet.getString("IsHoliday");
			/*String d1=resultSet.getString(1);	//直接获取结果
			String d2=resultSet.getString(2);*/
			System.out.println("date：" + d1+" "+d2);
		}
		
		LinkDB.close(connection, callableStatement, resultSet);


		
	}