Java Code Examples for java.sql.Connection#isClosed()

private void protectedRollback() throws SQLException
{
    SQLException failedRollback = null;
    Connection underlyingCon = this.getUnderlyingConnection();
    if (!underlyingCon.isClosed())
    {
        try
        {
            underlyingCon.rollback();
        }
        catch (SQLException e)
        {
            failedRollback = e;
        }
    }
    safeClose(underlyingCon);
    if (failedRollback != null) throw failedRollback;
}
 

public List<String> connectionStatus() {
    List<String> connectionStrs = new ArrayList<>();
    for (Map.Entry<String,List<Connection>> entry : this.userConnections.entrySet()) {
        int open = 0;
        int closed = 0;
        int problem = 0;
        for (Connection connection : entry.getValue()) {
            try {
                if (connection.isClosed()) {
                    ++closed;
                } else {
                    ++open;
                }
            } catch (SQLException e) {
                ++problem;
            }
        }
        connectionStrs.add(entry.getKey() + " Open: "+ open + " Closed: "+closed+ " Problem: "+problem);
    }
    return connectionStrs;
}
 

/**
 * Cleans up common state for the all tests in this class.
 */
@AfterClass
public static void shardMapManagerTestsCleanup() throws SQLException {
    Connection conn = null;
    try {
        conn = DriverManager.getConnection(Globals.SHARD_MAP_MANAGER_TEST_CONN_STRING);
        // Create ShardMapManager database
        try (Statement stmt = conn.createStatement()) {
            String query = String.format(Globals.DROP_DATABASE_QUERY, Globals.SHARD_MAP_MANAGER_DATABASE_NAME);
            stmt.executeUpdate(query);
        }
        catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
    catch (Exception e) {
        System.out.printf("Failed to connect to SQL database with connection string: " + e.getMessage());
    }
    finally {
        if (conn != null && !conn.isClosed()) {
            conn.close();
        }
    }
}
 

public void closeConnection(Connection conn) throws HibernateException {
	if ( log.isDebugEnabled() ) {
		log.debug(
				"closing JDBC connection" +
				preparedStatementCountsToString() +
				resultSetCountsToString()
			);
	}

	try {
		if ( !conn.isClosed() ) {
			JDBCExceptionReporter.logAndClearWarnings(conn);
		}
		factory.getConnectionProvider().closeConnection(conn);
	}
	catch (SQLException sqle) {
		throw JDBCExceptionHelper.convert(
				factory.getSQLExceptionConverter(),
		        sqle,
		        "Cannot close connection"
			);
	}
}
 

/**
 * Determines whether the connection is still open.
 * 
 * @return <code>true</code> if the connection is still open
 */
public boolean isConnectionOpen()
{
    if (_resultSet == null)
    {
        return false;
    }
    try
    {
        Statement  stmt = _resultSet.getStatement();
        Connection conn = stmt.getConnection();

        return !conn.isClosed();
    }
    catch (SQLException ex)
    {
        return false;
    }
}
 

@Override
public void run() {
	Connection con = null;
	try {

		List<Map<String, String>> batch = getNextBatch();
		while (!batch.isEmpty()) {
			try {
				if (con == null || con.isClosed()) {
					con = conPool.getConnection();
					con.setAutoCommit(false);
				}

				insert(con, batch);
				finshiedCount.addAndGet(batch.size());
			} catch (Exception e) {
				e.printStackTrace();
				try {
					con.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
					e1.printStackTrace();
				}
				failedCount.addAndGet(batch.size());
			}
			batch = getNextBatch();
		}
	} finally {
		if (con != null) {
			this.conPool.returnCon(con);
		}
	}

}
 

private Connection getExecutorConnection(String id, Executor executor)
    throws SQLException, ClassNotFoundException {
  Connection conn;
  if (executor instanceof EMRDb) {
    if (this.connectionCache.asMap().containsKey(id)) {
      conn = this.connectionCache.getIfPresent(id);
      if (conn.isClosed()) {
        conn = ((EMRDb) executor).getConnectionExec();
        this.connectionCache.put(id, conn);
      }
    } else {
      conn = ((EMRDb) executor).getConnectionExec();
      this.connectionCache.put(id, conn);
    }
  } else {
    if (this.connectionCache.asMap().containsKey(id)) {
      conn = this.connectionCache.getIfPresent(id);
      if (conn.isClosed()) {
        conn = ((JdbcDB) executor).getConnection();
        this.connectionCache.put(id, conn);
      }
    } else {
      conn = ((JdbcDB) executor).getConnection();
      this.connectionCache.put(id, conn);
    }
  }
  return conn;
}
 

public void close(Connection conn) {
	try {
		if (conn != null && !conn.isClosed()) {
			conn.close();
		}
	}
	catch (SQLException e) {
	}
}
 

@Override
public void run() {
    Connection con = null;
    try {

        List<Map<String, String>> batch = getNextBatch();
        while (!batch.isEmpty()) {
            try {
                if (con == null || con.isClosed()) {
                    con = conPool.getConnection();
                    con.setAutoCommit(true);
                }

                update(con, batch);
                finshiedCount.addAndGet(batch.size());
            } catch (Exception e) {
                failedCount.addAndGet(batch.size());
                e.printStackTrace();
            }
            batch = getNextBatch();
        }
    } finally {
        if (con != null) {
            this.conPool.returnCon(con);
        }
    }
}
 

public static int runSQLScript(Connection conn, String sqlFilePath, int maxSecs, boolean failOnError, boolean logOutput) {
  InputStream sqlScriptStream = ClassLoader.getSystemResourceAsStream(sqlFilePath);
  if (sqlScriptStream == null) {
    throw new TestException("Could not find " + sqlFilePath + " under classpath "
        + System.getProperty("java.class.path"));
  }
  ByteArrayOutputStream sqlOutStream = new ByteArrayOutputStream(20 * 1024);
  int returnStatus = 0;
  try {
    Log.getLogWriter().info("about to run " + sqlFilePath + " on connection " + conn.toString());
    returnStatus = com.pivotal.gemfirexd.internal.tools.ij.runScript(conn, sqlScriptStream, "US-ASCII",
        sqlOutStream, "US-ASCII");
    Log.getLogWriter().info("done running " + sqlFilePath + " with returnStatus=" + returnStatus);
    if (!conn.isClosed() && !conn.getAutoCommit())
      conn.commit();

    sqlScriptStream.close();
  } catch (UnsupportedEncodingException uee) {
    throw new TestException("Test Exception:", uee);
  } catch (IOException ioe) {
    throw new TestException("Test Exception:", ioe);
  } catch (SQLException sqle) {
    throw new TestException("SQL Exception in " + sqlFilePath + ":" + sqlOutStream.toString(), sqle);
  }
  if (logOutput || returnStatus != 0) {
    Log.getLogWriter().info("sql output: " + sqlOutStream.toString());
  }
  if (returnStatus != 0 && failOnError) {
    throw new TestException("SQL Exception on " + sqlFilePath + " " + sqlOutStream.toString());
  }
  return returnStatus;
}
 

private static void connFinally(Connection conn) {
    try {
        if (conn != null && !conn.isClosed()) {
            conn.close();
        }
        else {
            ConsoleUtils.writeWarning("Returned Connection was either null or already closed.");
        }
    }
    catch (SQLException ex) {
        ex.printStackTrace();
    }
}
 

@AfterClass
public static void tearDown() throws SQLException {
    Connection connection = executor.getConnection();
    if (connection != null && !connection.isClosed()) {
        connection.close();
    }
}
 

/**
 * 通过连接池对象返回数据库连接
 *
 * @return Connection 连接对象
 * @throws SQLException 数据库错误
 */
public static Connection getConnection() throws SQLException {
    // 从threadLocal获得连接对象
    Connection conn = threadLocal.get();

    // 如果连接对象不存在或者是已经被关闭的，就从连接池取出一个连接对象返回，如果已经存在，就直接返回
    if (conn == null || conn.isClosed()) {
        conn = dataSource.getConnection();
        // 设置到threadLocal中
        threadLocal.set(conn);
    }
    return conn;
}
 

public void closeConnection(RuntimeContainer container, Connection conn)
        throws SQLException {
    String refComponentId = getConnectionProperties().getReferencedComponentId();
    if ((refComponentId == null || container == null) && (conn != null && !conn.isClosed())) {
        conn.close();
    }
}
 

private static List<Pair<Integer, List<Pair<String, Integer>>>> updatePlayerRankingFromDB(int worldid) {
    List<Pair<Integer, List<Pair<String, Integer>>>> rankSystem = new ArrayList<>();
    List<Pair<String, Integer>> rankUpdate = new ArrayList<>(0);
    
    PreparedStatement ps = null;
    ResultSet rs = null;
    Connection con = null;
    try {
        con = DatabaseConnection.getConnection();
        
        String worldQuery;
        if (!YamlConfig.config.server.USE_WHOLE_SERVER_RANKING) {
            if(worldid >= 0) {
                worldQuery = (" AND `characters`.`world` = " + worldid);
            } else {
                worldQuery = (" AND `characters`.`world` >= 0 AND `characters`.`world` <= " + -worldid);
            }
        } else {
            worldQuery = (" AND `characters`.`world` >= 0 AND `characters`.`world` <= " + Math.abs(worldid));
        }
        
        ps = con.prepareStatement("SELECT `characters`.`name`, `characters`.`level`, `characters`.`world` FROM `characters` LEFT JOIN accounts ON accounts.id = characters.accountid WHERE `characters`.`gm` < 2 AND `accounts`.`banned` = '0'" + worldQuery + " ORDER BY " + (!YamlConfig.config.server.USE_WHOLE_SERVER_RANKING ? "world, " : "") + "level DESC, exp DESC, lastExpGainTime ASC LIMIT 50");
        rs = ps.executeQuery();
        
        if (!YamlConfig.config.server.USE_WHOLE_SERVER_RANKING) {
            int currentWorld = -1;
            while(rs.next()) {
                int rsWorld = rs.getInt("world");
                if(currentWorld < rsWorld) {
                    currentWorld = rsWorld;
                    rankUpdate = new ArrayList<>(50);
                    rankSystem.add(new Pair<>(rsWorld, rankUpdate));
                }

                rankUpdate.add(new Pair<>(rs.getString("name"), rs.getInt("level")));
            }
        } else {
            rankUpdate = new ArrayList<>(50);
            rankSystem.add(new Pair<>(0, rankUpdate));
            
            while(rs.next()) {
                rankUpdate.add(new Pair<>(rs.getString("name"), rs.getInt("level")));
            }
        }
        
        ps.close();
        rs.close();
        con.close();
    } catch(SQLException ex) {
        ex.printStackTrace();
    } finally {
        try {
            if(ps != null && !ps.isClosed()) {
                ps.close();
            }
            if(rs != null && !rs.isClosed()) {
                rs.close();
            }
            if(con != null && !con.isClosed()) {
                con.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    return rankSystem;
}
 

/**
 * Checks implementation of 'dontTrackOpenResources' property.
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testDontTrackOpenResources() throws Exception {
    Properties props = new Properties();

    props.setProperty("dontTrackOpenResources", "true");
    Connection noTrackConn = null;
    Statement noTrackStatement = null;
    PreparedStatement noTrackPstmt = null;
    ResultSet rs2 = null;

    try {
        noTrackConn = getConnectionWithProps(props);
        noTrackStatement = noTrackConn.createStatement();
        noTrackPstmt = noTrackConn.prepareStatement("SELECT 1");
        rs2 = noTrackPstmt.executeQuery();
        rs2.next();

        this.rs = noTrackStatement.executeQuery("SELECT 1");
        this.rs.next();

        noTrackConn.close();

        // Under 'strict' JDBC requirements, these calls should fail
        // (and _do_ if dontTrackOpenResources == false)

        this.rs.getString(1);
        rs2.getString(1);
    } finally {
        if (rs2 != null) {
            rs2.close();
        }

        if (noTrackStatement != null) {
            noTrackStatement.close();
        }

        if (noTrackConn != null && !noTrackConn.isClosed()) {
            noTrackConn.close();
        }
    }
}
 

/** 
 * @description:set tempate data valid
 * @date:2014-8-7 下午5:11:02
 * @version:v1.0
 * @param templateId
 * @param isValid
 * @return
 */
public boolean setValidDataOfTemplate(UUID templateId, boolean isValid) {
	if(templateId == null){
		return false;
	}
	
	String tableName = TableRuleManager.getInstance().getDataTableName(templateId);
	String logTableName = TableRuleManager.getInstance().getDataLogTableName(templateId);
	
	Connection conn = null;
	PreparedStatement pstm = null;
	try {
		conn = DbPoolConnection.getInstance().getConnection();
		conn.setAutoCommit(false);
		//更新data表
		
		pstm = conn.prepareStatement("update " + tableName + " set is_valid = ? where templateId = ?");
		pstm.setBoolean(1, isValid);
		pstm.setString(2, templateId.getValue());
		pstm.executeUpdate();
		
		//更新log表
		pstm = conn.prepareStatement("update " + logTableName + " set is_valid = ? where templateId = ?");
		pstm.setBoolean(1, isValid);
		pstm.setString(2, templateId.getValue());
		pstm.executeUpdate();
		
		conn.commit();
		conn.setAutoCommit(true);
		return true;
	} catch (Exception e) {
		try {
			if (!conn.isClosed()) {
				conn.rollback();
				conn.setAutoCommit(true);
			}
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		e.printStackTrace();
		return false;
	} finally{
		DbPoolConnection.getInstance().closeAll(pstm, conn);
	}
}
 

public static void close(Connection conn)throws SQLException{
	if(conn != null && !conn.isClosed()){
		conn.close();
	}
}
 

public CashShop(int accountId, int characterId, int jobType) throws SQLException {
    this.accountId = accountId;
    this.characterId = characterId;

    if (!YamlConfig.config.server.USE_JOINT_CASHSHOP_INVENTORY) {
        if (jobType == 0) {
            factory = ItemFactory.CASH_EXPLORER;
        } else if (jobType == 1) {
            factory = ItemFactory.CASH_CYGNUS;
        } else if (jobType == 2) {
            factory = ItemFactory.CASH_ARAN;
        }
    } else {
        factory = ItemFactory.CASH_OVERALL;
    }

    Connection con = DatabaseConnection.getConnection();
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        ps = con.prepareStatement("SELECT `nxCredit`, `maplePoint`, `nxPrepaid` FROM `accounts` WHERE `id` = ?");
        ps.setInt(1, accountId);
        rs = ps.executeQuery();

        if (rs.next()) {
            this.nxCredit = rs.getInt("nxCredit");
            this.maplePoint = rs.getInt("maplePoint");
            this.nxPrepaid = rs.getInt("nxPrepaid");
        }

        rs.close();
        ps.close();

        for (Pair<Item, MapleInventoryType> item : factory.loadItems(accountId, false)) {
            inventory.add(item.getLeft());
        }

        ps = con.prepareStatement("SELECT `sn` FROM `wishlists` WHERE `charid` = ?");
        ps.setInt(1, characterId);
        rs = ps.executeQuery();

        while (rs.next()) {
            wishList.add(rs.getInt("sn"));
        }

        rs.close();
        ps.close();
        con.close();
    } finally {
        if (ps != null && !ps.isClosed()) ps.close();
        if (rs != null && !rs.isClosed()) rs.close();
        if (con != null && !con.isClosed()) con.close();
    }
}
 

/**
 * Processes requests for both HTTP <code>GET</code> and <code>POST</code>
 * methods.
 *
 * @param request servlet request
 * @param response servlet response
 * @throws ServletException if a servlet-specific error occurs
 * @throws IOException if an I/O error occurs
 */
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    
   
   String user=request.getParameter("username").trim();
      String pass=request.getParameter("password").trim();
       try
         {
             Connection con=new DBConnect().connect(getServletContext().getRealPath("/WEB-INF/config.properties"));
                if(con!=null && !con.isClosed())
                           {
                               ResultSet rs=null;
                               Statement stmt = con.createStatement();  
                               rs=stmt.executeQuery("select * from users where username='"+user+"' and password='"+pass+"'");
                               if(rs != null && rs.next()){
                               HttpSession session=request.getSession();
                               session.setAttribute("isLoggedIn", "1");
                               session.setAttribute("userid", rs.getString("id"));
                               session.setAttribute("user", rs.getString("username"));
                               session.setAttribute("avatar", rs.getString("avatar"));
                               Cookie privilege=new Cookie("privilege","user");
                               response.addCookie(privilege);
                               if(request.getParameter("RememberMe")!=null)
                               {
                                   Cookie username=new Cookie("username",user);
                                   Cookie password=new Cookie("password",pass);
                                   response.addCookie(username);
                                    response.addCookie(password);
                               }
                               response.sendRedirect(response.encodeURL("ForwardMe?location=/index.jsp"));
                               }
                               else
                               {
                                      response.sendRedirect("ForwardMe?location=/login.jsp&err=Invalid Username or Password");
                               }
                                
                           }
            }
           catch(Exception ex)
            {
                       response.sendRedirect("login.jsp?err=something went wrong");
             }
    
}