H2 DataBase入门+整合hibernate+整合mybatis+整合springboot
H2 是一个用 Java 编写的可嵌入的关系型数据库,H2的主要特点是:
非常快,开源,JDBC API
嵌入式和服务器模式;内存数据库
基于浏览器的控制台应用程序
占用空间小:大约 2.5 MB jar 文件大小
H2数据库可以安装服务server到本机,也可以不安装,执行jdbc连接时根据url的路径自动创建db文件。可以在开发中小型应用时免去安装mysql等,也可以用于javafx客户端应用的开发和安卓等等。。。
一、入门
引入依赖,这个是几天前更新的版本,距离上一次更新是2019年了,现在是2021-12-1,中途停更了两年,这个最新版本修复了许多bug。
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>2.0.202</version>
</dependency>
下面是一段比较简单的jdbc操作:
import java.sql.*;
public class MyDemo {
public static void main(String[] args) throws Exception{
Class.forName("org.h2.Driver");
// 用户名和密码都是自定义的,第一次为创建.db文件,也可以不设置密码
Connection conn = DriverManager.getConnection("jdbc:h2:c:/myh2.db", "lingkang", "123456");
Statement stat = conn.createStatement();
// 创建一个表 注意 user是一个专有名词,需要使用`` 或者更改其他名字
stat.executeUpdate("drop table if exists `user`");
// 创建表
stat.executeUpdate("create table `user` (name varchar(20), nickname varchar(20))");
PreparedStatement prep = conn.prepareStatement(
"insert into `user` values (?, ?);");
prep.setString(1, "lk");
prep.setString(2, "阿萨德");
prep.addBatch();
prep.setString(1, "123");
prep.setString(2, "啊是电饭锅");
prep.addBatch();
// 执行
prep.executeBatch();
ResultSet result = stat.executeQuery("select * from `user`;");
while (result.next()) {
System.out.println("name=" + result.getString("name") + ", " +
"nickname=" + result.getString("nickname"));
}
result.close();
conn.close();
}
}
运行效果:
二、查看数据库
使用navicat什么的是无法open查看的,可以使用dbeaver
下载地址:https://dbeaver.io/download/dbeaver
这个工具社区版是开源的,功能强大,支持非常多数据库,比找navicat破解好多了,我用社区版,社区版功能够啦,配置一下h2驱动即可打开数据库。
三、整合hibernate自动生成表
Maven
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-core</artifactId>
<version>5.5.8.Final</version>
</dependency>
编写代码
import entity.User;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.boot.registry.StandardServiceRegistry;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cfg.Configuration;
import java.util.List;
import java.util.Properties;
public class MyHi {
public static void main(String[] args) {
String url = "jdbc:h2:c:/myh2.db";
// 配置属性:https://docs.jboss.org/hibernate/orm/5.6/userguide/html_single/Hibernate_User_Guide.html#configurations
Properties properties = new Properties();
properties.put("hibernate.connection.driver_class", "org.h2.Driver");
properties.put("hibernate.connection.url", url);
properties.put("hibernate.connection.username", "lingkang");
properties.put("hibernate.connection.password", "123456");
properties.put("hibernate.hbm2ddl.auto", "update");// 采用自动更新模式
properties.put("hibernate.show_sql", "true");
properties.put("hibernate.connection.autocommit", "true");
properties.put("connection.pool_size", 10);
properties.put("hibernate.dialect", "org.hibernate.dialect.H2Dialect");
properties.put("hibernate.current_session_context_class", "thread");
Configuration configuration = new Configuration();
configuration.setProperties(properties);
// 这里可以编写一个工具类将包下所有的class加载到配置
/*List<Class<?>> tableClass = getTableClass("top.oneit.jdownload.entity");
for (Class clazz:tableClass){
configuration.addAnnotatedClass(clazz);
}*/
configuration.addAnnotatedClass(User.class);
StandardServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder()
.applySettings(properties)
.build();
try {
// 会话工厂
SessionFactory sessionFactory = configuration.buildSessionFactory(serviceRegistry);
// hibernate 插入时需要自己手动管理事务,比较尴尬,整合spring则spring管理事务
Session inSession = sessionFactory.openSession();
inSession.beginTransaction();
User user = new User();
user.setId(System.currentTimeMillis());
user.setUsername("lingkang");
user.setPassword("123456");
user.setNickname("凌康");
inSession.save(user);
inSession.getTransaction().commit();// 这里我们手动提交事务
// 从会话工厂中获取一个会话
Session session = sessionFactory.openSession();
// user 是h2 db的关键词,表名需要改下
List users = session.createQuery("from User").list();
System.out.println(users);
} catch (Exception e) {
e.printStackTrace();
StandardServiceRegistryBuilder.destroy(serviceRegistry);
}
}
/* private static List<Class<?>> getTableClass(String packageName) {
Set<Class<?>> classes = ClassUtil.scanPackage(packageName);
List<Class<?>> list = new ArrayList<>();
for (Class<?> clazz : classes) {
if (clazz.getAnnotation(Table.class) != null) {
list.add(clazz);
}
}
return list;
}*/
}
实体类:
package entity;
import org.hibernate.annotations.CreationTimestamp;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import java.util.Date;
// user 是h2 db的关键词,这里的表名需要改下
@Table(name = "t_user")
@Entity
public class User {
@Id
private Long id;
@Column(length = 20)
private String username;
@Column(length = 20)
private String password;
@Column(length = 20)
private String nickname;
@CreationTimestamp// 创建时添加
@Column//我认为不需要遵循什么阿里开发规范,直接使用这个字段名即可
private Date createTime;
// getset tostring...
效果:
四、整合mybatis
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.7</version>
</dependency>
配置
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 配置环境-->
<environments default="h2">
<!--配置mysql的环境-->
<environment id="h2">
<!--配置事务的类型-->
<transactionManager type="JDBC"></transactionManager>
<!--配置连接池-->
<dataSource type="POOLED">
<!--配置连接数据库的4个基本信息-->
<property name="driver" value="org.h2.Driver"/>
<property name="url" value="jdbc:h2:c:/myh2.db"/>
<property name="username" value="lingkang"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<!--指定映射配置文件的位置,映射配置文件指的是每个dao独立的配置文件-->
<mappers>
<mapper resource="mapper/user.xml"/>
</mappers>
</configuration>
映射mapper/user.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- namespace:命名空间,用于分层sql -->
<mapper namespace="user">
<select id="findAll" resultType="entity.User">
select * from t_user
</select>
</mapper>
代码
import entity.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
import java.util.List;
public class DemoMybatis {
public static void main(String[] args) throws Exception {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
List<User> users = sqlSession.selectList("user.findAll");
System.out.println(users);
}
}
效果
五、整合springboot
这里我使用spring-data-jdbc来操作,你也可以整合mybatis、hibernate等来操作数据库
Maven
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>2.0.202</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
配置文件
spring.datasource.url=jdbc:h2:c:/myh2.db
#如果从官网下载的spring boot没有加入mybtis就不需要注释下面链接驱动
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.username=lingkang
spring.datasource.password=123456
代码
import java.util.List;
import java.util.Map;
@Controller
public class WebController {
@Autowired
private JdbcTemplate jdbcTemplate;
@GetMapping("")
@ResponseBody
public Object index() {
List<Map<String, Object>> maps = jdbcTemplate.queryForList("select * from t_user");
System.out.println(maps);
return maps;
}
}
效果