FilesExpand file tree

 master

/

h2-db.md

Copy path

Blame

More file actions

Blame

More file actions

Latest commit

 

History

History

History

152 lines (121 loc) · 4.78 KB

 master

/

h2-db.md

Copy path

Top

File metadata and controls

• Code
• Blame

152 lines (121 loc) · 4.78 KB

Raw

Copy raw file

Download raw file

Open symbols panel

Edit and raw actions

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

H2是一个开源的嵌入式（非嵌入式设备）数据库引擎，它是一个用Java开发的类库，可直接嵌入到应用程序中，与应用程序一起打包发布，不受平台限制。

H2与Derby、HSQLDB、MySQL、PostgreSQL等开源数据库相比，H2的优势为：

* Java开发，不受平台限制；

* H2只有一个jar包，占用空间小，适合嵌入式数据库；

* 有web控制台，用于管管理数据库。

接下来介绍Spring+Mybatis+H2的数据库访问实践，参考：https://blog.csdn.net/xktxoo/article/details/78014739

添加H2数据库依赖：

```

<dependency>

<groupId>com.h2database</groupId>

<artifactId>h2</artifactId>

<version>1.4.190</version>

</dependency>

```

H2数据库属性文件配置如下，本文采用内存模式访问H2数据库：

```

driver=org.h2.Driver

# 内存模式

url=jdbc:h2:mem:testdb;MODE=MYSQL;DB_CLOSE_DELAY=-1

# 持久化模式

#url= jdbc:h2:tcp://localhost/~/test1;MODE=MYSQL;DB_CLOSE_DELAY=-1

```

H2数据库访问的Spring配置文件为：

```

<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xmlns="http://www.springframework.org/schema/beans"

xmlns:tx="http://www.springframework.org/schema/tx"

xmlns:jdbc="http://www.springframework.org/schema/jdbc"

xsi:schemaLocation="

http://www.springframework.org/schema/beans

http://www.springframework.org/schema/beans/spring-beans-4.0.xsd

http://www.springframework.org/schema/tx

http://www.springframework.org/schema/tx/spring-tx-4.0.xsd

http://www.springframework.org/schema/jdbc

http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd">

<!-- 引入属性文件 -->

<bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">

<property name="locations">

<list>

<value>classpath:config.properties</value>

</list>

</property>

</bean>

<!-- 自动扫描DAO -->

<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">

<property name="basePackage" value="com.xiaofan.test" />

</bean>

<!-- 配置Mybatis sqlSessionFactory -->

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">

<property name="dataSource" ref="dataSource"/>

<property name="configLocation" value="classpath:mybatis_config.xml"/>

<property name="mapperLocations" value="classpath:user_mapper.xml"/>

</bean>

<!-- 配置数据源 -->

<bean id="dataSource"

class="org.springframework.jdbc.datasource.DriverManagerDataSource">

<property name="driverClassName" value="${driver}" />

<property name="url" value="${url}" />

<!--<property name="username" value="sa" />-->

<!--<property name="password" value="123" />-->

</bean>

<!-- 初始化数据库 -->

<jdbc:initialize-database data-source="dataSource" ignore-failures="DROPS">

<jdbc:script location="classpath:sql/ddl.sql" />

<jdbc:script location="classpath:sql/dml.sql" />

</jdbc:initialize-database>

<!-- 配置事务管理 -->

<tx:annotation-driven transaction-manager="transactionManager" proxy-target-class="true"/>

<bean id="transactionManager"

class="org.springframework.jdbc.datasource.DataSourceTransactionManager">

<property name="dataSource" ref="dataSource"/>

</bean>

</beans>

```

初始化数据库的DDL语句文件为：

```

CREATE TABLE `user` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(100) NOT NULL,

`age` int(11) NOT NULL,

PRIMARY KEY (`id`)

);

```

初始化数据库的DML语句文件为：

```

insert into `user` (`id`,`name`,`age`) values (1, 'Jerry', 27);

insert into `user` (`id`,`name`,`age`) values (2, 'Angel', 25);

```

编写测试文件，如下：

```java

/**

* Created by Jerry on 17/7/30.

*/

@ContextConfiguration(locations = {"classpath:config.xml"})

@RunWith(SpringJUnit4ClassRunner.class)

public class Test extends AbstractJUnit4SpringContextTests{

@Resource

UserDAO userDAO;

@org.junit.Test

public void testInsert() {

int result = userDAO.insert(new User(null, "LiLei", 27));

Assert.assertTrue(result > 0);

}

@org.junit.Test

public void testUpdate() {

int result = userDAO.update(new User(2L, "Jerry update", 28));

Assert.assertTrue(result > 0);

}

@org.junit.Test

public void testSelect() {

User result = userDAO.findByName(new User(null, "Jerry", null));

Assert.assertTrue(result.getAge() != null);

}

@org.junit.Test

public void testDelete() {

int result = userDAO.delete("Jerry");

Assert.assertTrue(result > 0);

}

}

```