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