Java DbUtils.close方法代碼示例

import org.apache.commons.dbutils.DbUtils; //導入方法依賴的package包/類
public static void main(String[] args) throws SQLException {

		final String url = "jdbc:h2:./target/test;AUTO_SERVER=TRUE";
		final String driver = "org.h2.Driver";
		final String usr = "sa";
		final String pwd = "";

		QueryRunner run = new QueryRunner();

		DbUtils.loadDriver(driver);
		Connection conn = DriverManager.getConnection(url, usr, pwd);
		// -----------------------------------------------------------------------------------

		try {
			List<Map<String, Object>> maps = run.query(conn,
					"SELECT * FROM employee", new MapListHandler());
			System.out.println(maps);
		} finally {
			DbUtils.close(conn);
		}

	}
 

import org.apache.commons.dbutils.DbUtils; //導入方法依賴的package包/類
public static void main(String[] args) throws SQLException {

		final String url = "jdbc:h2:./target/test;AUTO_SERVER=TRUE";
		final String driver = "org.h2.Driver";
		final String usr = "sa";
		final String pwd = "";

		QueryRunner run = new QueryRunner();

		DbUtils.loadDriver(driver);
		Connection conn = DriverManager.getConnection(url, usr, pwd);
		// -----------------------------------------------------------------------------------
		ResultSetHandler<Employee> resultHandler = new BeanHandler<Employee>(
				Employee.class);

		try {
			Employee emp = run.query(conn,
					"SELECT * FROM employee WHERE employeename=?",
					resultHandler, "Jose");
			System.out.println(emp.getEmployeeId());
		} finally {
			DbUtils.close(conn);
		}

	}
 

import org.apache.commons.dbutils.DbUtils; //導入方法依賴的package包/類
private void markMigration(DatabaseInfo dbInfo, String version) throws Exception {
	String[] migrations = this.getNewMigrations(dbInfo);
	boolean exists = false;
	List<String> migrationToMark = new ArrayList<String>();
	for (String m : migrations) {
		migrationToMark.add(m);
		if (m.startsWith("m" + version)) {
			exists = true;
			break;
		}
	}
	if (exists) {
		Connection conn = this.getConnection(dbInfo);
		QueryRunner runner = new QueryRunner();
		for (String migration : migrationToMark) {
			runner.update(conn, RECORD_VERSION, migration);
		}
		DbUtils.close(conn);
		log.info("Set migration history at " + version + " successfully");
	} else {
		log.error("Error version of migration");
		log.error("");
	}
}
 

import org.apache.commons.dbutils.DbUtils; //導入方法依賴的package包/類
@Override
public void runScript(File scriptFile, DatabaseInfo dbInfo)
		throws Exception {
	Connection conn = new MigrateService().getConnection(dbInfo);
	try {
		QueryRunner runner = new QueryRunner();
		conn.setAutoCommit(false);
		List<String> queries = this.getQueries(scriptFile.getCanonicalPath());
		for (String query : queries) {
			runner.update(conn, query);
		}
		conn.commit();
	} catch (Exception e) {
		conn.rollback();
		throw e;
	} finally {
		DbUtils.close(conn);
	}
}
 

import org.apache.commons.dbutils.DbUtils; //導入方法依賴的package包/類
public static void main(String[] args) throws SQLException {

		final String url = "jdbc:h2:./target/test;AUTO_SERVER=TRUE";
		final String driver = "org.h2.Driver";
		final String usr = "sa";
		final String pwd = "";

		QueryRunner run = new QueryRunner();

		DbUtils.loadDriver(driver);
		Connection conn = DriverManager.getConnection(url, usr, pwd);
		// -----------------------------------------------------------------------------------

		try {
			List<Object[]> query = run.query(conn, "SELECT * FROM employee",
					new ArrayListHandler());
			for (Object[] objects : query) {
				System.out.println(Arrays.toString(objects));
			}
		} finally {
			DbUtils.close(conn);
		}

	}
 

import org.apache.commons.dbutils.DbUtils; //導入方法依賴的package包/類
public static void main(String[] args) throws SQLException {

		final String url = "jdbc:h2:./target/test;AUTO_SERVER=TRUE";
		final String driver = "org.h2.Driver";
		final String usr = "sa";
		final String pwd = "";

		QueryRunner run = new QueryRunner();

		DbUtils.loadDriver(driver);

		Connection conn = DriverManager.getConnection(url, usr, pwd);
		// -----------------------------------------------------------------------------------
		ResultSetHandler<List<Employee>> resultListHandler = new BeanListHandler<Employee>(
				Employee.class);

		try {
			List<Employee> empList = run.query(conn, "SELECT * FROM employee",
					resultListHandler);
			System.out.println(empList);
		} finally {
			DbUtils.close(conn);
		}

	}
 

import org.apache.commons.dbutils.DbUtils; //導入方法依賴的package包/類
@Test
public void testQueryRunner() throws Exception {

    QueryRunner runner = new QueryRunner();
    String sql = "SELECT * FROM biz_pay_order where id = ?";

    ResultSetHandler<Object[]> rsh = new ResultSetHandler<Object[]>() {
        @Override
        public Object[] handle(ResultSet rs) throws SQLException {
            if (!rs.next()) {
                return null;
            }

            ResultSetMetaData metaData = rs.getMetaData();
            int cols = metaData.getColumnCount();
            Object[] result = new Object[cols];
            for (int i = 0; i < cols; i++) {
                result[i] = rs.getObject(i + 1);
            }

            return result;
        }
    };

    Object[] ret = runner.query(conn, sql, rsh, 3);

    for (int i = 0; i < ret.length; i++) {
        System.out.print(ret[i] + "  ");
    }

    DbUtils.close(conn);
}
 

import org.apache.commons.dbutils.DbUtils; //導入方法依賴的package包/類
@Override
public synchronized void close() throws IOException {
    try {
        DbUtils.close(getConnection());
    } catch (SQLException e) {
        throw new IOException("Error closing DB connection", e);
    }
}
 

import org.apache.commons.dbutils.DbUtils; //導入方法依賴的package包/類
/**
 * This is called in two places. If there is a rollback and we need to get rid of
 * the new session and once a successful population has been made. Removing the old
 * session.
 * @param sessionId
 * @return
 * @throws SQLException
 */
public boolean removeMetadataBySessionId(String sessionId) throws SQLException {
	
	// Remove the meatadata items
	PreparedStatement removeMetadataSessionDataPstmt=null;
	try {
		removeMetadataSessionDataPstmt = dbConnection.prepareStatement(
				REMOVE_METADATA_SESSION_DATA_STMT);
		removeMetadataSessionDataPstmt.setString(1, this.workspace.harvestRequest.getSetSpec());
		removeMetadataSessionDataPstmt.setString(2, sessionId);
		removeMetadataSessionDataPstmt.execute();
		
	} 
	finally
	{
		DbUtils.close(removeMetadataSessionDataPstmt);
	}
	
	// Remove the resource items
	PreparedStatement removeResourceDataPstmt=null;
	try {
		removeResourceDataPstmt = this.dbConnection.prepareStatement(
				REMOVE_RESOURCE_SESSION_DATA_STMT);
	
		removeResourceDataPstmt.setString(1, this.workspace.harvestRequest.getSetSpec());
		removeResourceDataPstmt.setString(2, sessionId);
		removeResourceDataPstmt.execute();
		
	} finally
	{
		DbUtils.close(removeResourceDataPstmt);
	}
	return true;
}
 

import org.apache.commons.dbutils.DbUtils; //導入方法依賴的package包/類
public void close() {
        Connection conn = getConnection();
        try {
            if (conn != null && !conn.isClosed()) {
                DbUtils.close(conn);
            }
        } catch (SQLException e) {
//            ExceptionUtils.thrwoAppException(this.getClass().getName() + ".executeAndClose", "SQL", "數據庫操作異常", e);
        }
        connections.remove();
    }
 

import org.apache.commons.dbutils.DbUtils; //導入方法依賴的package包/類
public <T> T executeAndClose(SqlExecutor<T> executor) {
        Connection conn = null;
        T result = null;
        try {
            conn = getConnection();
            result = execute(executor);
        } finally {
            try {
                DbUtils.close(conn);
            } catch (SQLException e) {
//                ExceptionUtils.thrwoAppException(this.getClass().getName() + ".executeAndClose", "SQL", "數據庫操作異常", e);
            }
        }
        return result;
    }
 

import org.apache.commons.dbutils.DbUtils; //導入方法依賴的package包/類
@Override
public void destroyObject(PooledObject<Connection> p) throws Exception {
    Connection conn = p.getObject();
    DbUtils.close(conn);
}
 

import org.apache.commons.dbutils.DbUtils; //導入方法依賴的package包/類
private void validateTuples()
{
  try {

    /*
     * Initialization of database connection.
     */
    Class.forName(DB_DRIVER).newInstance();
    Connection conn = DriverManager.getConnection(URL);

    String inputSql = "select * FROM " + TABLE_NAME;
    String outputSql = "select * FROM " + OUTPUT_TABLE_NAME;
    ResultSetHandler inputResultSet = new ArrayListHandler();
    ResultSetHandler outputResultSet = new ArrayListHandler();
    QueryRunner run = new QueryRunner();
    List<Object[]> inputTuples = (ArrayList<Object[]>)run.query(conn, inputSql, inputResultSet);
    List<Object[]> ouputTuples = (ArrayList<Object[]>)run.query(conn, outputSql, outputResultSet);

    /*
     * Validate number of tuples of input and output operators.
     */
    Assert.assertEquals("Number of rows mismatch", inputTuples.size(), ouputTuples.size());

    /*
     * Validate tuple contents of input and output operators.
     */
    Comparator comparator = new Comparator<Object[]>()
    {
      @Override
      public int compare(Object[] o1, Object[] o2)
      {
        return ((Integer)o1[0]).compareTo((Integer)o2[0]);
      }
    };
    Collections.sort(inputTuples,comparator);
    Collections.sort(ouputTuples,comparator);

    for (int i = 0; i < inputTuples.size(); i++) {
      Assert.assertEquals("Row mismatch", inputTuples.get(i)[0], ouputTuples.get(i)[0]);
    }
    DbUtils.close(conn);
  } catch (Exception e) {
    throw new RuntimeException(e);
  }
}
 

import org.apache.commons.dbutils.DbUtils; //導入方法依賴的package包/類
private void compare() throws Exception
{
  try {
    String[] inputLines = lines;

    /*
     * Initialization of database connection.
     */
    Class.forName(DB_DRIVER).newInstance();
    Connection conn = DriverManager.getConnection(URL);
    Statement stmt = conn.createStatement();

    String outputSql = "select * FROM " + OUTPUT_TABLE_NAME;
    ResultSetHandler outputResultSet = new ArrayListHandler();
    QueryRunner run = new QueryRunner();
    List<Object[]> ouputTuples = (ArrayList<Object[]>)run.query(conn, outputSql, outputResultSet);

    /*
     * Validate number of tuples of input and output operators.
     */
    Assert.assertTrue("Number of rows mismatch", inputLines.length == ouputTuples.size());

    /*
     * Validate tuple contents of input and output operators.
     */
    Comparator comparator = new Comparator<Object[]>()
    {
      @Override
      public int compare(Object[] o1, Object[] o2)
      {
        return ((Integer)o1[0]).compareTo((Integer)o2[0]);
      }
    };
    Collections.sort(ouputTuples, comparator);

    for (int i = 0; i < inputLines.length; i++) {
      String[] fields = inputLines[i].split("\\|");
      Assert.assertEquals(Integer.parseInt(fields[0]), ouputTuples.get(i)[0]);
      Assert.assertEquals(fields[1], ouputTuples.get(i)[1]);
      Assert.assertEquals(Integer.parseInt(fields[2]), ouputTuples.get(i)[2]);
    }
    DbUtils.close(conn);
  } catch (Exception e) {
    throw new RuntimeException(e);
  }
}
 

import org.apache.commons.dbutils.DbUtils; //導入方法依賴的package包/類
/**
 * Update the session table, Care is taken here because there are a lot
 * of different cases that can come through.
 * 1) its the first successful harvests so no sessions exist yet
 * 2) A session exists and we are replacing it
 * 3) A session exists and we replaced it but now we want to rollback
 * @param currentSessionId
 * @param newSessionId
 * @param isRollBack
 * @return
 * @throws SQLException
 */
private boolean updateSessionTable(String currentSessionId, 
		String newSessionId, boolean isRollBack) throws SQLException {
	String setSpec = this.workspace.harvestRequest.getSetSpec();
	boolean returnValue = false;
	if(currentSessionId!=null)
	{
		// in the cases where there is a current session in there. In this
		// case just replace the session id
		PreparedStatement updateSessionTablePstmt=null;
		try {
			updateSessionTablePstmt = this.dbConnection.prepareStatement(
					SESSION_UPDATE_STMT);
			if(isRollBack)
				updateSessionTablePstmt.setString(1, currentSessionId);
			else
				updateSessionTablePstmt.setString(1, newSessionId);
			updateSessionTablePstmt.setString(2, setSpec);
			returnValue = updateSessionTablePstmt.execute();
			updateSessionTablePstmt.close();
		} 
		finally
		{
			DbUtils.close(updateSessionTablePstmt);
		}
	}
	else
	{
		// Else this is a first time. 
		
		// If its a rollback we just remove the session 
		if(isRollBack)
		{
			PreparedStatement deleteSessionTablePstmt=null;
			try {
				deleteSessionTablePstmt = this.dbConnection.prepareStatement(
						SESSION_DELETE_STMT);
				deleteSessionTablePstmt.setString(1, setSpec);
				returnValue = deleteSessionTablePstmt.execute();
				
			} 
			finally
			{
				DbUtils.close(deleteSessionTablePstmt);
			}
		}
		else
		{
			// else add this new session and setspec to the table
			PreparedStatement insertSessionTablePstmt=null;
			try {
				insertSessionTablePstmt = this.dbConnection.prepareStatement(
						SESSION_INSERT_STMT);
				insertSessionTablePstmt.setString(1, newSessionId);
				insertSessionTablePstmt.setString(2, setSpec);
				
				returnValue = insertSessionTablePstmt.execute();
			}
			finally
			{
				DbUtils.close(insertSessionTablePstmt);
			}
			
		}
	}
	return returnValue;
}