Spark database Connection pool Java DAO Factory __ Database

In general Java projects and now particularly hot data analysis projects, the use of database and database resource pool connection is a commonplace. Today it's a simple way to sort through the process:

We look at the code in order of dispatch:

Comment, we are from the spark data analysis to do demo expansion:

First, suppose that the read-write database must be emitted from the business level, then there should be the following code

This is the last step in many of our code, write data to the database code, write the last generated data to the database, assuming that the database type is not now, requires us to provide configurable functionality, the most impotent code I marked in red and bold

Javapairrdd<string, tuple2<string, row>> Sessiondetailrdd =
      Top10sessionrdd.join ( SESSIONID2DETAILRDD);
Sessiondetailrdd.foreach (New voidfunction<tuple2<string,tuple2<string,row>>> () {

   private Static final Long serialversionuid = 1L;

   @Override public
   void Call (Tuple2<string, tuple2<string, row>> tuple) throws Exception {
      row row = Tu ple._2._2;

      Sessiondetail sessiondetail = new Sessiondetail ();
      Sessiondetail.settaskid (taskid);
      Sessiondetail.setuserid (Row.getlong (1));
      ,//reomve some contents 
      Isessiondetaildao Sessiondetaildao = Daofactory.getsessiondetaildao ();//
      Sessiondetaildao.insert (sessiondetail);
   }
);

public static Isessiondetaildao Getsessiondetaildao () {return
   new Sessiondetaildaoimpl ();
}

Second, the above code comes from the daofactory of the IMPL implementation of the fixed idea

Next, let's see how the Impl code is implemented.

The data in the third insert is the data we insert into DB, where we want to convert the parameters to the SQL.

public class Sessiondetaildaoimpl implements Isessiondetaildao {

   /**
    * Insert a data
    * @param sessiondetail 
    * /Public
   void Insert (Sessiondetail sessiondetail) {
      String sql = ' INSERT INTO table_name values ' (?,?,?,?,?,?,?,?, ?,?,?,?)";  
      
      object[] params = new Object[]{sessiondetail.gettaskid (),
            Sessiondetail.getuserid (),
            Sessiondetail.getsessionid (),
            
      
      jdbchelper jdbchelper = Jdbchelper.getinstance ();
      Jdbchelper.executeupdate (sql, params);
   

Four, introduce the next jdbchelper is how to achieve, this step needs to go to the database resource pool to get the DB link

Import Com.zkys.spark.conf.ConfigurationManager;

Import com.zkys.spark.constant.Constants;
Import java.sql.Connection;
Import Java.sql.DriverManager;
Import java.sql.PreparedStatement;
Import Java.sql.ResultSet;
Import java.util.LinkedList;

Import java.util.List; public class Jdbchelper {static {try {String driver = Configurationmanager.getproperty (Constants .
         Jdbc_driver);
      Class.forName (driver);  
      catch (Exception e) {e.printstacktrace ();

   }} private static jdbchelper instance = NULL; public static Jdbchelper getinstance () {if (instance = null) {synchronized (jdbchelper.class) {//synchro Nized http://www.cnblogs.com/GnagWang/archive/2011/02/27/1966606.html if (instance = = null) {in
   stance = new Jdbchelper ()//Call Private parameterless construction method, you need to implement the link in the construction method of the database}} return instance; }//database connection pool private linkedlist<connection> DataSource =New Linkedlist<connection> ();

   Add and Remove,linedlist are more advantageous for new and delete operations because ArrayList want to move the data. Private Jdbchelper () {int datasourcesize = Configurationmanager.getinteger (constants.jdbc_datasource_
      SIZE); for (int i = 0; i < Datasourcesize i++) {Boolean local = Configurationmanager.getboolean (Constants.spark_loca
         L);
         String URL = null;
         String user = null;
            String password = null;
            url = configurationmanager.getproperty (Constants.jdbc_url);
            user = Configurationmanager.getproperty (constants.jdbc_user);
         Password = configurationmanager.getproperty (Constants.jdbc_password); try {//After looping creates 10 database links in this area and puts the connection in the linklist Connection conn = drivermanager.getconnection (URL
            , user, password);  
         Datasource.push (conn); 
         catch (Exception e) {e.printstacktrace (); }} public synchronized Connection GetConnectIon () {while (datasource.size () = 0) {try {thread.sleep (10);
         catch (Interruptedexception e) {e.printstacktrace ();
   } return Datasource.poll ();
      public int executeupdate (String sql, object[] params) {int RTN = 0;
      Connection conn = null;
      
      PreparedStatement pstmt = null;
         try {conn = getconnection ();  
         
         Conn.setautocommit (FALSE);
         
         pstmt = conn.preparestatement (sql);
               if (params!= null && params.length > 0) {for (int i = 0; i < params.length; i++) {  
            Pstmt.setobject (i + 1, params[i]);
         
         } RTN = Pstmt.executeupdate ();
      Conn.commit ();  
      catch (Exception e) {e.printstacktrace ();  
         finally {if (conn!= null) {Datasource.push (conn);
   } return RTN;

}   public void executequery (String sql, object[] params, Querycallback callback) {Connection = null;
      PreparedStatement pstmt = null;
      
      ResultSet rs = null;
         try {conn = getconnection ();
         
         pstmt = conn.preparestatement (sql);
               if (params!= null && params.length > 0) {for (int i = 0; i < params.length; i++) {   
            Pstmt.setobject (i + 1, params[i]);
         
         rs = Pstmt.executequery ();  
      Callback.process (RS);
      catch (Exception e) {e.printstacktrace ();  
         finally {if (conn!= null) {Datasource.push (conn);
      }} public int[] ExecuteBatch (String sql, list<object[]> paramslist) {int[] Rtn = null;
      Connection conn = null;
      
      PreparedStatement pstmt = null;
         
         try {conn = getconnection (); The first step: Using connectionObject, Cancel Autocommit conn.setautocommit (false);
         
         pstmt = conn.preparestatement (sql);
            Step two: Use the Preparedstatement.addbatch () method to add the batch SQL parameter if (paramslist!= null && paramslist.size () > 0) {
                  For (object[] params:paramslist) {for (int i = 0; i < params.length; i++) {  
               Pstmt.setobject (i + 1, params[i]);
            } pstmt.addbatch (); }//Step three: Use the Preparedstatement.executebatch () method to execute the batch of SQL statements RTN = Pstmt.executebatch ()
         
         ;
      Last step: Use Connection object, submit batch SQL statement conn.commit ();  
      catch (Exception e) {e.printstacktrace ();  
         finally {if (conn!= null) {Datasource.push (conn);
   } return RTN;
      
      /** * Static internal class: Query Callback interface * @author Administrator */public static interface Querycallback { /**
       * Processing Query Results * @param RS * @throws Exception/void Process (ResultSet rs) throws Exception;
 }
   
}

Five, because it is configurable, you need to add more elements

Import Java.io.InputStream;

Import java.util.Properties;
   public class ConfigurationManager {private static properties prop = new properties (); static {try {InputStream in = Configurationmanager.class. getClassLoader (). getres
         Ourceasstream ("My.properties");  
      Prop.load (in);  
      catch (Exception e) {e.printstacktrace ();
   } public static string GetProperty (String key) {return Prop.getproperty (key);
      public static Integer Getinteger (String key) {String value = GetProperty (key);
      try {return integer.valueof (value);
      catch (Exception e) {e.printstacktrace ();
   return 0;
      public static Boolean Getboolean (String key) {String value = GetProperty (key);
      try {return boolean.valueof (value);
      catch (Exception e) {e.printstacktrace ();
   return false; } public static Long Getlong (String key) {String value = GetProperty (key);
      try {return long.valueof (value);
      catch (Exception e) {e.printstacktrace ();
   return 0L;
 }
   
}