import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import org.apache.logging.log4j.LogManager; import org.apache.logging.log4j.Logger; import org.junit.Test; import com.app.config.Const; public class DBHelper_Oracle { private static final Logger logger = LogManager .getLogger(DBHelper_Oracle.class); static String driver = "oracle.jdbc.driver.OracleDriver"; static String url = Const.DBURL; protected static Connection getDBConnection(String url, String userName, String password) { Connection con = null; try { Class.forName(driver); con = DriverManager.getConnection(url, userName, password); } catch (ClassNotFoundException e) { logger.error("装载 JDBC/ODBC 驱动程序失败。"); } catch (SQLException e) { logger.error("无法连接数据库"); } return con; } protected void close(PreparedStatement pstmt, ResultSet rs, Connection conn) { try { if (null != rs) { rs.close(); } if (null != pstmt) { pstmt.close(); } if (null != conn) { conn.close(); } } catch (SQLException e) { e.printStackTrace(); } finally { try { if (null != rs) { rs.close(); } if (null != pstmt) { pstmt.close(); } if (null != conn) { conn.close(); } } catch (SQLException e) { e.printStackTrace(); } } } /** * 测试数据库连接sql * * @return */ public String testConn() { String sql = "select 1 from dual "; Connection conn = getDBConnection(Const.DBURL, Const.DBUSERNAME, Const.DBPASSWORD); PreparedStatement pstmt = null; ResultSet rs = null; try { pstmt = conn.prepareStatement(sql); rs = pstmt.executeQuery(); while (rs.next()) { System.out.println(rs.getString(1)); } this.close(pstmt, rs, conn); } catch (SQLException e) { e.printStackTrace(); return "failure"; } return "success!!!"; } @Test public void test() { DBHelper_Oracle helper = new DBHelper_Oracle(); helper.testConn(); } }
import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import org.junit.Test; public class DbHelper extends DBHelper_Oracle { private Connection conn; public DbHelper(String url, String userName, String password) { this.conn = super.getDBConnection(url, userName, password); } /** * 查询筛选出来的总数 * * @param status 状态 * * @param startDate 开始日期 * * @param endDate 结束日期 * * @return */ public long getXSLselectCount(String status, String startDate, String endDate) { String sql = "select count(*) from XXX WHERE STATUS = ? AND TR_DATE>=to_date( ? , 'yyyy-mm-dd')and TR_DATE<=to_date( ?, 'yyyy-mm-dd')"; try { PreparedStatement pstmt = this.conn.prepareStatement(sql); pstmt.setString(1, status); pstmt.setString(2, startDate); pstmt.setString(3, endDate); ResultSet rs = pstmt.executeQuery(); if (rs.next()) { return rs.getLong(1); } super.close(pstmt, rs, conn); } catch (SQLException e) { e.printStackTrace(); } return 0; } /** * 总条数 * * @return */ public long getPostionHandoverNums() { String sql = "select count(*) from XXX where (HAND_OVER_USER is NOT NULL) AND (RECEIVE_USER IS NOT NULL)"; try { PreparedStatement pstmt = this.conn.prepareStatement(sql); ResultSet rs = pstmt.executeQuery(); if (rs.next()) { return rs.getLong(1); } super.close(pstmt, rs, conn); } catch (SQLException e) { e.printStackTrace(); } return 0; } }