Share Java database Connect connector encapsulate JDBC Query Excute etc __c#

A homemade Java connector that encapsulates the creation of connections and deletion connections, reduces the Java EE Development code for database operations, and is used to easily connect to databases, whether it is an EE or a console, or a junit environment.

* * Database Connection class * each connection entity with a connect, the circulation body must pay attention to * Modify Log:<br> * 2016/7/1 increase turn off autocommit and submit function * 2017/2/27 revision of some normative settings, stability improvement * 20

17/4/10 Connect Constructor Machine Abnormal State trigger * */package data;
* * Database Connection class * * Import java.sql.*;
Import javax.sql.*;
Import javax.naming.*;
Import Org.apache.logging.log4j.LogManager;
Import Org.apache.logging.log4j.Logger;

Import cn.sharcom.config.Constant;

  public class Connect {private static final Logger Logger = Logmanager.getlogger (Connect.class);
  PRIVATE Connection conn = null;
  Private Statement stmt = null;
  Private CallableStatement cstmt = null;
  Private PreparedStatement pstmt = null;

  Private ResultSet rs = null;

  Private Boolean connected = false;
  /** * Connection mode: In Java environment, the connection pool mode * * public static int jndi_mode = 100;

  /** * Console environment, URL Connection mode (for JUnit test)/public static int url_mode = 200; /** * Initialization Connection * @param mode * Connection mode: JNDI and URL */public connect (final int mode) throws Sqlexcep tion {//Java Environment connectionPool if (mode = = Jndi_mode) {logger.debug ("Jndi_mode begin ...");
        Connect database try {Context context = new InitialContext ();

        Logger.debug ("InitialContext success");
        DataSource DataSource = (DataSource) context.lookup (constant.jndi_name);

        Logger.debug (String.Format ("DataSource lookup \"%s\ "%s", Constant.jndi_name, "success"));
        conn = Datasource.getconnection ();

        Logger.debug (String.Format ("Connection dataSource%s", "success"));
        stmt = Conn.createstatement ();

        Logger.debug (String.Format ("createstatement%s", "success"));
      Modify the connection state connected = true;
        catch (Namingexception e) {e.printstacktrace ();
        Logger.error (E.getmessage ());
        Close ();
      throw new SQLException ("Connect Fail by JNDI");
        catch (SQLException e) {e.printstacktrace ();
        Logger.error (E.getmessage ());
        Close (); throw new SQlexception ("Connect Fail by JNDI");
        catch (Exception e) {e.printstacktrace ();
        Logger.error (E.getmessage ());
        Close ();
      throw new SQLException ("Connect Fail by JNDI"); The finally {}}///is primarily for scenarios where no connection pool is used for jUnit testing else if (mode = = Url_mode) {Logger.deb
      UG ("Url_mode begin ...");
        try {//Class.forName ("Com.mysql.jdbc.Driver"); Create a connection URL final String url = String.Format ("jdbc:mysql://127.0.0.1:3306/editor?user=%s&password=%s&usess
        L=false "," root "," 123456 "); Connection......
        and get Connection conn = drivermanager.getconnection (URL);
        Statement stmt = Conn.createstatement ();
      Modify the connection state connected = true;
        catch (ClassNotFoundException e) {e.printstacktrace ();
        Logger.error (E.getmessage ());
        Close ();
      throw new SQLException ("Connect Fail by URL");
     } catch (SQLException e) {e.printstacktrace ();
        Logger.error (E.getmessage ());
        Close ();
      throw new SQLException ("Connect Fail by URL");
        catch (Exception e) {e.printstacktrace ();
        Logger.error (E.getmessage ());
        Close ();
      throw new SQLException ("Connect Fail by URL");
    } finally {}}/** * return connection Status * * @return/public boolean isconnected () {
  return connected;
    /** * Check the database connection is valid * * @return/public boolean Test () {//return value Boolean result = false;
    ResultSet ResultSet = null;

      Check try {resultSet = Conn.createstatement (). ExecuteQuery ("SELECT 1");
        if (resultset.first () = = true) {result = true;
      Logger.debug ("Test connect Success");
        } if (ResultSet!= null) {try {resultset.close ();
   catch (SQLException e)     {E.printstacktrace ();
      A catch (SQLException e) {e.printstacktrace ());
      result = false;
    Logger.debug ("Test Connect Fail");
    catch (Exception e) {e.printstacktrace ();
  finally {}//return returns result;
   /** * into a prepared sql<br> * insert into table (' field1 ', ' field2 ') VALUES (?,?); * * @param SQL * @throws SQLException */public void Setpreparedstatementsql (final String sql) throws Sqlexcep
  tion {pstmt = conn.preparestatement (sql);
   /** * The format <br> * SQL Connect.setpreparedstatementsql (SQL);<br> * * To configure the parameters of the type and value <br> * Connect.getpreparedstatement (). Setlong (1, 1);<br> * connect.getpreparedstatement (). SetString (2, "name"); <br> * connect.getpreparedstatement (). setstring (3, NULL);<br> * * Performed <br> * CONNECT.EXECUTEU Pdate (NULL);<br> * * @return/PublIC PreparedStatement getpreparedstatement () {return pstmt;
  /** * Returns Connection * * @return * * public Connection getconnection () {return conn;
  /** * Returns RESULTSET * * @return * * public ResultSet Getresultset () {return rs; /** * Get Statement Execute SQL command * * @return Statement * @throws SQLException/public Statement GETST
    Atement () throws SQLException {//Create statement//MySQL use stmt = conn.createstatement ();

    MSSQL Use//stmt = Conn.createstatement (resultset.type_scroll_sensitive,resultset.concur_updatable);
  return stmt; /** * Gets callablestatement and creates a stored procedure * * @param strcallablestatement * @return CallableStatement * @thr OWS SQLException */Public callablestatement getcallablestatement (String sql) throws SQLException {//STOREDPR
    OC cstmt = conn.preparecall (sql);
  return cstmt; Public ResultSet executequery () throws Sqlexception {return executequery (null); /** * Query SQL * * @param SQL * @return * @throws SQLException/public ResultSet executequery (fi
      NAL String sql) throws SQLException {//init ResultSet if (rs!= null) {rs.close ();
    rs = null;
      }//Normal SQL incoming call if (SQL!= null) {Logger.debug ("connect:stmt.executeQuery (SQL)");
      if (stmt!= null) {rs = stmt.executequery (SQL);
      } else {Logger.debug ("connect:pstmt.executeQuery ()");
      if (pstmt!= null) {rs = Pstmt.executequery ();
  } return RS; /** * Execute SQL * * @param SQL * @return * @throws SQLException/public int executeupdate (final S

    Tring sql) throws SQLException {int result = 0;
    if (sql = = null && pstmt!= null) {result = Pstmt.executeupdate ();
    else {result = Stmt.executeupdate (SQL); Return ResULt /** * PSTMT * * @return * @throws SQLException/public int executeupdate () throws Sqlexcep without parameters
  tion {return executeupdate (null);
    /** * Get Last number * * @return/public Long Getlastinsertid () {long result = NULL;

    ResultSet ResultSet = null;
      try {//query the last record inserted by the number String sql = "Select last_insert_id () as ' id '";
      Call Query ResultSet = stmt.executequery (sql);
        if (resultset.first () = = true) {result = Resultset.getlong ("id");
      Logger.debug ("Getlastinsertid is Success");
      else {logger.debug ("Getlastinsertid is null");
      } catch (SQLException e) {e.printstacktrace ();
    Logger.error ("Getlastinsertid is Fail");
        finally {if (ResultSet!= null) {try {resultset.close ();
        catch (SQLException e) {e.printstacktrace ();
    }  } return result;
   /** * settings are automatically submitted <br> * is true, immediately after the conversion of the data library <br> * When false, all modifications need to invoke Commit () to actually complete.
    * * @param model * @throws SQLException/public void Setautocommit (Boolean model) throws SQLException {
    Logger.debug ("Setautocommit", model);
  Conn.setautocommit (model); /** * If in manual submission, you need to submit * * @throws SQLException/public void Commit () throws SQLException {Conn.
  Commit (); 
    /** * things back * * @throws SQLException/public void Rollback () {try {conn.rollback ();
    catch (SQLException e) {e.printstacktrace ();
    catch (Exception e) {e.printstacktrace ();
    }/** * Closes all connected/public void close () {Logger.debug ("Connect closed All");

    connected = false;
      Close ResultSet if (rs!= null) {try {rs.close (); catch (Exception e) {e.printstacktRace ();
    rs = null;
      }//Close Statement if (stmt!= null) {try {stmt.close ();
      catch (Exception e) {e.printstacktrace ();
    } stmt = null;
      }//StoredProc if (cstmt!= null) {try {cstmt.close ();
      catch (Exception e) {e.printstacktrace ();
    } cstmt = null;
      } if (conn!= null) {try {conn.close ();
      catch (Exception e) {e.printstacktrace ();
    } conn = null;
 }
  }

}

Connector usage: How to execute

Connect connect = new Connect (connect.url_mode);
if (connect.executeupdate (sql) = = 1)
{
  //execution succeeded
}
connect. Close ();

Connector Usage: Query method

Connect connect = new Connect (connect.url_mode);
Connect.executequery (SQL);
while (Connect.getresultset (). Next () = = True)
{
  String val = Connect.getresultset (). getString ("field1");
  System.out.println (val);
}
Connect. Close ();

Instance:

    Connect connect = NULL;
    Try
    {
      connect = new Connect (connect.jndi_mode);

      Connect.executequery (SQL);
      while (Connect.getresultset (). Next () = = True)
      {
        String val = Connect.getresultset (). getString ("field");
      }
    }
    catch (SQLException e)
    {
      e.printstacktrace ();
      result = Constant.db_error;
    }
    catch (Exception e)
    {
      e.printstacktrace ();
      result = Constant.db_error;
    }
    Finally
    {
      if (connect!= null)
      {
        Connect. Close ();
      }
    }

The key points to note are: When you are done with connect, be sure to call Close () to turn off the connection, especially the Java EE Connection Pool environment, otherwise the connection will persist until the connection timeout is automatically closed, which will consume system resources, and the persistent request connection will deplete the connection pool and the new connection will not be able to apply.

This article links blog.csdn.net/joyous/article/details/51103046

Q Group discussion: 236201801

.