daily pastebin goal
30%
SHARE
TWEET

Untitled

a guest Jun 29th, 2018 87 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. public class DatabaseBean {
  2.  
  3. private static final Logger logger = Logger.getLogger(DatabaseBean.class);
  4.  
  5. private Connection conn;
  6. private PreparedStatement prepStmt;
  7.  
  8. /**
  9.  * Zero argument constructor
  10.  * Setup generic databse connection in here to avoid redundancy
  11.  * The connection details are in /META-INF/context.xml
  12.  */
  13. public DatabaseBean() {
  14.     try {
  15.         InitialContext initContext = new InitialContext();
  16.         DataSource ds = (DataSource) initContext.lookup("java:/comp/env/jdbc/mysite");
  17.         conn = ds.getConnection();
  18.     }
  19.     catch (SQLException SQLEx) {
  20.         logger.fatal("There was a problem with the database connection.");
  21.         logger.fatal(SQLEx);
  22.         logger.fatal(SQLEx.getCause());
  23.     }
  24.     catch (NamingException nameEx) {
  25.         logger.fatal("There was a naming exception");
  26.         logger.fatal(nameEx);
  27.         logger.fatal(nameEx.getCause());
  28.     }
  29. }
  30.  
  31. /**
  32.  * Execute a query. Do not use for statements (update delete insert etc).
  33.  *
  34.  * @return A ResultSet of the execute query. A set of size zero if no results were returned. It is never null.
  35.  * @see #executeUpdate() for running update, insert delete etc.
  36.  */
  37.  
  38. public ResultSet executeQuery() {
  39.     ResultSet result = null;
  40.     try {
  41.         result = prepStmt.executeQuery();
  42.         logger.debug(prepStmt.toString());
  43.     }
  44.     catch (SQLException SQLEx) {
  45.         logger.fatal("There was an error running a query");
  46.         logger.fatal(SQLEx);
  47.     }
  48.     return result;
  49. }
  50.    
  51. public void close() {
  52.     try {
  53.         prepStmt.close();
  54.         prepStmt = null;
  55.  
  56.         conn.close();
  57.         conn = null;
  58.     } catch (SQLException SQLEx) {
  59.         logger.warn("There was an error closing the database connection.");
  60.     }
  61. }
  62. }
  63.    
  64. public LinkedList<ImportantNoticeBean> getImportantNotices() {
  65.  
  66.     DatabaseBean noticesDBBean = new DatabaseBean();
  67.     LinkedList<ImportantNoticeBean> listOfNotices = new LinkedList<ImportantNoticeBean>();
  68.  
  69.     try {
  70.         PreparedStatement preStmt = noticesDBBean.getConn().prepareStatement("SELECT pseudonym, message, date_to, date_from " +
  71.                 "FROM importantnotices, users " +
  72.                 "WHERE importantnotices.username = users.username " +
  73.                 "AND NOW() >= date_from AND NOW() <= date_to;");
  74.  
  75.         noticesDBBean.setPrepStmt(preStmt);
  76.         ResultSet result = noticesDBBean.executeQuery();
  77.  
  78.         while (result.next()) {
  79.             ImportantNoticeBean noticeBean = new ImportantNoticeBean();
  80.  
  81.             noticeBean.setAuthor(result.getString("pseudonym"));
  82.             noticeBean.setMessage(result.getString("message"));
  83.             noticeBean.setDateTo(result.getDate("date_to"));
  84.             noticeBean.setDateFrom(result.getDate("date_from"));
  85.  
  86.             listOfNotices.add(noticeBean);
  87.         }
  88.  
  89.         result.close();
  90.  
  91.     } catch (SQLException SQLEx) {
  92.         logger.error("There was an error in ImportantNoticesBean.getImportantNotices()");
  93.         logger.error(SQLEx);
  94.     } finally {
  95.         noticesDBBean.close();
  96.     }
  97.     return listOfNotices;
  98. }
  99.  
  100. <Context reloadable="true">
  101.  
  102.     <Resource name="jdbc/mysite"
  103.               auth="Container"
  104.               type="javax.sql.DataSource"
  105.               username="user"
  106.               password="password"
  107.               driverClassName="com.mysql.jdbc.Driver"
  108.               url="jdbc:mysql://localhost:3306/mysite"
  109.               maxActive="10"
  110.               maxIdle="5"
  111.               maxWait="6000"
  112.               removeAbandoned="true"
  113.               logAbandoned="false"
  114.               removeAbandonedTimeout="20"
  115.             />
  116. </Context>
  117.    
  118. * Jakarta-Commons DBCP
  119. * Jakarta-Commons Collections
  120. * Jakarta-Commons Pool
  121.    
  122. testWhileIdle=true
  123. timeBetweenEvictionRunsMillis=300000
  124.    
  125. *SNIP*
  126. maxActive="10"
  127. maxIdle="5"
  128. maxWait="7000"
  129. removeAbandoned="true"
  130. logAbandoned="false"
  131. removeAbandonedTimeout="3"
  132. *SNIP*
  133.    
  134. AbandonedObjectPool is used (org.apache.tomcat.dbcp.dbcp.AbandonedObjectPool@cd70f7)
  135.    
  136. defaultAutoCommit="false"
  137.         defaultTransactionIsolation="REPEATABLE_READ"
  138.         auth="Container"
  139.         type="javax.sql.DataSource"
  140.         logAbandoned="true"
  141.           removeAbandoned="true"
  142.         removeAbandonedTimeout="300"
  143.         maxActive="-1"
  144.         initialSize="15"
  145.         maxIdle="10"
  146.         maxWait="10000"
  147.         username="youruser"
  148.         password="youruserpassword"
  149.         driverClassName="com.mysql.jdbc.Driver"
  150.         url="jdbc:mysql://yourhost/yourdatabase"/>
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand