内存数据库 HSQLDB

骑猪看日落 2022-08-23 12:55 140阅读 0赞

应用

在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();
    	}
    }