Hibernate补充

Hibernate调用SQL语句

try {
   session = getSession();
   con = session.connection();
   con.setAutoCommit(true);
   stmt = con.createStatement();
   
   String sql = "SELECT systemName, pair FROM TblSystemPerformance GROUP BY systemName, pair" +
     " ORDER BY systemName, pair";
   
   rs = stmt.executeQuery(sql);

   rs = stmt.getResultSet();
   if (rs == null) {
    throw new Exception("No system pair found in TblSystemPerformance");
   }

   while (rs.next()) {
    String systemName = rs.getString("systemName");
    String pair = rs.getString("pair");
    result.add(systemName + "," + pair);
   }
  } catch (Exception e) {
   System.out.println(e.getMessage());
   e.printStackTrace();
   throw e;
  } finally {
   closeConn(con, stmt, rs);
   session.close();
  }

 

Hibernate调用存储过程

 

 

try {
   session = getSession();
   con = session.connection();
   con.setAutoCommit(true);
   String sql = "{call SPSearchSystemPerformance"
    + "(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";
   stmt = con.prepareCall(sql);
   stmt.setString("_systemName", pcdto.getSystemName());
   stmt.setString("_pair", pcdto.getPair());
   stmt.setDouble("_grossPlMax", pcdto.getGrossPlMax());
   stmt.setDouble("_grossPlMin", pcdto.getGrossPlMin());
   ...
   if (pcdto.getCalcMonth() != null) {
    stmt.setDate("_calcDate",  new java.sql.Date(pcdto.getCalcMonth().getTime()));
   } else {
    stmt.setDate("_calcDate", null);
   }
   
   stmt.execute();

   rs = stmt.getResultSet();
   if (rs == null) {
    return result;
   }

   while (rs.next()) {
    TblSystemPerformance strategy = new TblSystemPerformance();
    strategy.setSystemId(rs.getLong("systemID"));
    strategy.setSystemName(rs.getString("systemName"));
    strategy.setPair(rs.getString("pair"));
    ...
    strategy.setCalcStartDate(rs.getDate("calcStartDate"));
    result.add(strategy);
   }
  } catch (Exception e) {
   System.out.println(e.getMessage());
   e.printStackTrace();
   throw e;
  } finally {
   closeConn(con, stmt, rs);
   session.close();
  }

 

 

DELIMITER $$

DROP PROCEDURE IF EXISTS `fxmonkey`.`SPSearchSystemPerformance` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `SPSearchSystemPerformance`(
   _systemName VARCHAR(30),
   _pair  VARCHAR(20),
   _grossPlMax   DOUBLE ,
 _grossPlMin   DOUBLE ,
 ...
   _calcDate DATE
)
BEGIN

DECLARE _sql VARCHAR(2000);

SET @sql = '
SELECT systemID, systemName,pair, grossPL, pl,
       numTrades, maxDD, profitFactor, pipsTrade, creationDate,
       avgTradeTime, maxPos, riskAdjust, winPercent, po,
       otherAPT, otherALT, otherLWT, otherLLT, otherDIS,
       calcStartDate, calcEndDate
FROM TblSystemPerformance
WHERE 1 = 1
';

SET @sql = CONCAT(@sql, ' AND grossPL >= ' , _grossPlMin ,
                        ' AND grossPL <= ' , _grossPlMax , ' ');
SET @sql = CONCAT(@sql, ' AND pl >= ' , _plMin ,
                        ' AND pl <= ' , _plMax , ' ');
...
IF (_calcDate IS NOT NULL) THEN
SET @sql = CONCAT(@sql, ' AND YEAR(calcStartDate) = ', YEAR(_calcDate) ,
                        ' AND MONTH(calcStartDate) = ', MONTH(_calcDate), ' ');
END IF;
IF (_creationDate IS NOT NULL) THEN
SET @sql = CONCAT(@sql, ' AND creationDate >= ', _creationDate ,' ');
END IF;
IF (_systemName IS NOT NULL) THEN
SET @sql = CONCAT(@sql, ' AND systemName = ''', _systemName , ''' ');
END IF;
IF (_pair IS NOT NULL) THEN
SET @sql = CONCAT(@sql, ' AND pair LIKE ''%', _pair , '%'' ');
END IF;

PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

 

END $$

DELIMITER ;