内存数据库 HSQLDB

应用

    在JUnit单元测试中，由于，很多时候没有真实数据库环境，所以，我们很自然的会借助内存数据库HSQLDB。内存数据库非常轻量级，当内存数据库关闭后，内存中的数据也随之消失。代码虽然简单，但还是贴出来，呵呵，供以后复制粘贴方便。

jdbcDriver in pom.xml of Maven:

		<dependency>
			<groupId>org.hsqldb</groupId>
			<artifactId>hsqldb</artifactId>
			<version>2.3.2</version>
		</dependency>

package shuai.study.memorydb;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MemoryDB {
	private static Connection connection = null;
	private static Statement statement = null;
	private static PreparedStatement preparedStatement = null;
	private static ResultSet resultSet = null;

	// Load jdbcDriver
	static {
		try {
			Class.forName("org.hsqldb.jdbcDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	// Get DB connection
	public static Connection getConnection() {
		if (connection == null) {
			try {
				connection = DriverManager.getConnection("jdbc:hsqldb:mem:mdb", "shuai", "123");
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return connection;
	}

	// Get DB statement
	public static Statement getStatement() {
		if (connection == null) {
			getConnection();
		}

		if (statement == null) {
			try {
				statement = connection.createStatement();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return statement;
	}

	// Execute SQL
	public static void executeSQL(String sql) {
		if (statement == null) {
			getStatement();
		}

		try {
			statement.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	// Get resultSet
	public static ResultSet executeQuerySQL(String sql) {
		if (connection == null) {
			getConnection();
		}

		try {
			preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return resultSet;
	}

	// Print resultSet
	public static void resultPrint(ResultSet resultSet) {
		try {
			int columnCount = resultSet.getMetaData().getColumnCount();
			String columnValue = null;

			while (resultSet.next()) {
				for (int n = 1; n <= columnCount; n++) {
					columnValue = resultSet.getString(n);

					if (n == columnCount) {
						System.out.println(columnValue);
					} else {
						System.out.print(columnValue + "\t");
					}

				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	// Close DB
	public static void closeDB() {
		try {
			if (resultSet != null) {
				resultSet.close();
			}

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

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

			if (connection != null) {
				connection.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) {
		MemoryDB.executeSQL("CREATE TABLE EMPLOYEE(ID INTEGER, NAME VARCHAR(20), SEX VARCHAR(10), TEAM VARCHAR(20))");

		MemoryDB.executeSQL("INSERT INTO EMPLOYEE VALUES(1,'shuai', 'male', 'FMC')");
		MemoryDB.executeSQL("INSERT INTO EMPLOYEE VALUES(2,'hellen', 'female', 'MUS')");
		MemoryDB.executeSQL("INSERT INTO EMPLOYEE VALUES(3,'mery', 'female', 'SDM')");

		MemoryDB.executeSQL("COMMIT");

		ResultSet resultSet = MemoryDB.executeQuerySQL("SELECT * FROM EMPLOYEE");
		MemoryDB.resultPrint(resultSet);

		MemoryDB.closeDB();
	}
}