目录 [隐藏]
- 0.1 前言:
- 0.2 界面
- 0.3 Maven 环境
- 0.4 项目结构
- 0.5 整合 Hibernate
- 0.6 项目初始化连接数据库自动建表:
- 0.7 JFoenix 界面开发
- 1 JFoenix 表格 TreeTable
–
前言:
在开发 JavaFX 应用总是避免不了数据存储的,如果仅仅是一个简单的配置数据,那么不需要数据库即可实现,那么如果要面对几十万等大数据量的持久化存储,那免不了要和数据库和JDBC框架打交道了。
数据库该怎么选呢? 首先考虑我比较熟的 MySql,可是要使用MySql,你就必须要去官网下载MySql的安装包,还要进行账号密码等配置,如果这软件是面向大众的,用户要使用总不能还要先装数据库,再看半天安装教程吧?
这不行,那么我之前有接触过两个嵌入式数据库,一个是H2,一个就是开发Android 时接触的Sqlite。
H2 我事先考察了一下,觉得资料并不是很多,远没有 Sqlite 使用广泛,而且 Sqlite 是 Android 官方内置的数据库,我还去看了 Sqlite 最大数据存储等测试文章,亿级的数据量下还能保持性能,这才放心使用。
界面
Maven 环境
<span class="hljs-tag"><<span class="hljs-name">dependencies>
<span class="hljs-tag"><<span class="hljs-name">dependency>
<span class="hljs-tag"><<span class="hljs-name">groupId>junit<span class="hljs-tag">groupId>
<span class="hljs-tag"><<span class="hljs-name">artifactId>junit<span class="hljs-tag">artifactId>
<span class="hljs-tag"><<span class="hljs-name">version>3.8.1<span class="hljs-tag">version>
<span class="hljs-tag"><<span class="hljs-name">scope>test<span class="hljs-tag">scope>
<span class="hljs-tag">dependency>
<span class="hljs-tag"><<span class="hljs-name">dependency>
<span class="hljs-tag"><<span class="hljs-name">groupId>com.jfoenix<span class="hljs-tag">groupId>
<span class="hljs-tag"><<span class="hljs-name">artifactId>jfoenix<span class="hljs-tag">artifactId>
<span class="hljs-tag"><<span class="hljs-name">version>8.0.8<span class="hljs-tag">version>
<span class="hljs-tag">dependency>
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
项目结构
整合 Hibernate
Hibernate 并不支持 Sqlite,但只是缺少一个数据库方言代码而已,这个在网上有很多,copy 一份在hibernate配置文件中引入就可以了。
SQLiteDialect.java 数据库方言代码
<span class="hljs-keyword">package util;
<span class="hljs-keyword">import org.hibernate.dialect.Dialect;
<span class="hljs-keyword">import org.hibernate.dialect.function.SQLFunctionTemplate;
<span class="hljs-keyword">import org.hibernate.dialect.function.StandardSQLFunction;
<span class="hljs-keyword">import org.hibernate.dialect.function.VarArgsSQLFunction;
<span class="hljs-keyword">import org.hibernate.type.StandardBasicTypes;
<span class="hljs-keyword">import java.sql.Types;
<span class="hljs-keyword">public <span class="hljs-class"><span class="hljs-keyword">class <span class="hljs-title">SQLiteDialect <span class="hljs-keyword">extends <span class="hljs-title">Dialect {
<span class="hljs-function"><span class="hljs-keyword">public <span class="hljs-title">SQLiteDialect<span class="hljs-params">() {
<span class="hljs-keyword">super();
registerColumnType(Types.BIT, <span class="hljs-string">"integer");
registerColumnType(Types.TINYINT, <span class="hljs-string">"tinyint");
registerColumnType(Types.SMALLINT, <span class="hljs-string">"smallint");
registerColumnType(Types.INTEGER, <span class="hljs-string">"integer");
registerColumnType(Types.BIGINT, <span class="hljs-string">"bigint");
registerColumnType(Types.FLOAT, <span class="hljs-string">"float");
registerColumnType(Types.REAL, <span class="hljs-string">"real");
registerColumnType(Types.DOUBLE, <span class="hljs-string">"double");
registerColumnType(Types.NUMERIC, <span class="hljs-string">"numeric");
registerColumnType(Types.DECIMAL, <span class="hljs-string">"decimal");
registerColumnType(Types.CHAR, <span class="hljs-string">"char");
registerColumnType(Types.VARCHAR, <span class="hljs-string">"varchar");
registerColumnType(Types.LONGVARCHAR, <span class="hljs-string">"longvarchar");
registerColumnType(Types.DATE, <span class="hljs-string">"date");
registerColumnType(Types.TIME, <span class="hljs-string">"time");
registerColumnType(Types.TIMESTAMP, <span class="hljs-string">"timestamp");
registerColumnType(Types.BINARY, <span class="hljs-string">"blob");
registerColumnType(Types.VARBINARY, <span class="hljs-string">"blob");
registerColumnType(Types.LONGVARBINARY, <span class="hljs-string">"blob");
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
hibernate.cfg.xml Hibernate配置文件
这里的 :
是绝对路径,这个是用 idea 自动生成给我改了,可以使用相对路径在项目根目录下创建数据库文件,而 url 只需要 : String url = “jdbc:sqlite:src/main/resources/db/letv.db”;
这样写就可以。
项目初始化连接数据库自动建表:
数据库那肯定不能没有表,而表又不可能让用户去建,所以只能让程序代劳,并不难,用 Navicat 打开数据库建好表,导出 sql 文件,将其中的建表语句提取出来,在项目初次加载时,找到 sqlite 的 db 后缀的数据库文件,如果没有,那么创建,连接数据库,执行建表语句。
<span class="hljs-keyword">public <span class="hljs-class"><span class="hljs-keyword">class <span class="hljs-title">SqliteUtil {
<span class="hljs-keyword">private <span class="hljs-keyword">static Connection connection;
<span class="hljs-function"><span class="hljs-keyword">public <span class="hljs-keyword">synchronized <span class="hljs-keyword">static Connection <span class="hljs-title">getConnection<span class="hljs-params">() <span class="hljs-keyword">throws SQLException {
</span></span></span></span></span></span></span></span></span></span></span></span></span>
这段代码中,getConnection() 方法调用后会连接 Sqlite 数据库,如果没有,则会创建。
<span class="hljs-keyword">public <span class="hljs-keyword">static <span class="hljs-keyword">final String DB_USER = <span class="hljs-string">"create table letv_user (" +
<span class="hljs-string">" letv_user_id integer(11) not null," +
<span class="hljs-string">" letv_user_uid text(11)," +
<span class="hljs-string">" letv_user_link text(40)," +
<span class="hljs-string">" primary key (letv_user_id)" +
<span class="hljs-string">");";
<span class="hljs-function"><span class="hljs-keyword">public <span class="hljs-keyword">static <span class="hljs-keyword">void <span class="hljs-title">createDatabases<span class="hljs-params">() <span class="hljs-keyword">throws SQLException, IOException {
Connection connection = getConnection();
Statement statement = connection.createStatement();
statement.execute(DB_CONFIG);
statement.execute(DB_COOKIE);
statement.execute(DB_LINK);
statement.execute(DB_USER);
close();
}</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
定义建表语句,调用 createDatabases() 则会执行建表语句创建表。
程序初次运行创建数据库和表
<span class="hljs-keyword">if (<span class="hljs-keyword">this.getClass().getResource(<span class="hljs-string">"db/letv.db") == <span class="hljs-literal">null) {
FXMLLoader loader = <span class="hljs-keyword">new FXMLLoader(getClass().getResource(<span class="hljs-string">"/fxml/Loading.fxml"));
AnchorPane pane = loader.load();
Scene scene = <span class="hljs-keyword">new Scene(pane, <span class="hljs-number">500, <span class="hljs-number">300);
PleaseProvideController loadController = loader.getController();
loadController.setInfo(<span class="hljs-string">"正在创建数据库……");
primaryStage.setScene(scene);
primaryStage.initStyle(StageStyle.UNDECORATED);
primaryStage.setAlwaysOnTop(<span class="hljs-literal">true);
primaryStage.getIcons().addAll(<span class="hljs-keyword">new Image(<span class="hljs-string">"file:/resource/logo/logo.ico"));
primaryStage.show();
Platform.runLater(<span class="hljs-function"><span class="hljs-params">() -> {
<span class="hljs-keyword">try {
SqliteUtil.createDatabases();
logger.info(<span class="hljs-string">"数据库创建成功!");
primaryStage.close();
start(<span class="hljs-keyword">new Stage());
} <span class="hljs-keyword">catch (SQLException | IOException e) {
e.printStackTrace();
}
});
}</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
创建数据库需要一些时间,这个时候可以给一个 Loading 界面:
经过测试发现创建数据库和表时间并不长,所以这一步可以省略,当然如果表多那就看情况了。
JFoenix 界面开发
JFoenix 的界面非常好看,Google Material 的设计风格,案例:
这个 UI 库是开源的,非常美观,提供的控件也很丰富,只是文档感觉不是很好,但好在可以再官方提供的 Demo 案例查看控件的使用。
Github 地址 :
https://github.com/jfoenixadmin/JFoenix
官方文档 :
http://www.jfoenix.com/documentation.html
JFoenix 表格 TreeTable
官方案例:
如果觉得 JFoenix 的表格实现代码要比原生的简单,那你就错了,代码量依旧比较大,而且如果需要对表的列绑定字段,字段不是只读,就是如果你需要表的字段可以被编辑操作,那么相应的绑定的字段类型必须是 JavaFX 提供的 Property 类型,JavaFX 为这种类型提供了绑定方法,但是如果是使用这种类型去结合 Hibernate 字段映射,报错没跑了。
所以,我只能将用户映射表的实体类和绑定表的类分开,分为两个类,一个字段类型是原生的,另一个字段类型是 Property 类型。
<span class="hljs-keyword">public <span class="hljs-class"><span class="hljs-keyword">class <span class="hljs-title">LetvCookieTable <span class="hljs-keyword">extends <span class="hljs-title">RecursiveTreeObject<<span class="hljs-title">LetvCookieTable> {
<span class="hljs-keyword">public <span class="hljs-keyword">long cookieId;
<span class="hljs-keyword">public StringProperty cookieKey;
<span class="hljs-keyword">public StringProperty cookieValue;
<span class="hljs-function"><span class="hljs-keyword">public <span class="hljs-title">LetvCookieTable<span class="hljs-params">(<span class="hljs-keyword">long cookieId,String cookieKey, String cookieValue) {
<span class="hljs-keyword">this.cookieId = cookieId;
<span class="hljs-keyword">this.cookieKey = <span class="hljs-keyword">new SimpleStringProperty(cookieKey);
<span class="hljs-keyword">this.cookieValue = <span class="hljs-keyword">new SimpleStringProperty(cookieValue);
}
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
这个就是用来绑定表的实体类,再表格界面加载的时候,查询返回实体类结果集,接着将实体类转换成 Property 类型的类添加到 ObservableList 中。
字段绑定
<span class="hljs-regexp">//column
JFXTreeTableColumn<letvcookietable, string> key = <span class="hljs-keyword">new JFXTreeTableColumn<>(<span class="hljs-string">"Key");
key.setCellValueFactory(<span class="hljs-function"><span class="hljs-params">(TreeTableColumn.CellDataFeatures<letvcookietable, string> param) -> {
<span class="hljs-keyword">if (key.validateValue(param)) {
<span class="hljs-keyword">return param.getValue().getValue().cookieKey;
} <span class="hljs-keyword">else {
<span class="hljs-keyword">return key.getComputedValue(param);
}
});
JFXTreeTableColumn<letvcookietable, string> value = <span class="hljs-keyword">new JFXTreeTableColumn<>(<span class="hljs-string">"Value");
value.setCellValueFactory(<span class="hljs-function"><span class="hljs-params">(TreeTableColumn.CellDataFeatures<letvcookietable, string> param) -> {
<span class="hljs-keyword">if (value.validateValue(param)) {
<span class="hljs-keyword">return param.getValue().getValue().cookieValue;
} <span class="hljs-keyword">else {
<span class="hljs-keyword">return value.getComputedValue(param);
}
});</span></span></span></span></letvcookietable,></span></span></span></span></letvcookietable,></span></span></span></span></letvcookietable,></span></span></span></span></letvcookietable,></span>
TreeTable 绑定删除按钮
现在需要一个删除的列,提供删除按钮,点击后删除这一行的数据。
代码和 TableView 大体上是一样的,但在取值上有点小差异。
JFXTreeTableColumn<letvcookietable, <span class="hljs-built_in">String> options = <span class="hljs-keyword">new JFXTreeTableColumn<>(<span class="hljs-string">"options");
options.setCellFactory(<span class="hljs-keyword">new Callback<treetablecolumn<letvcookietable, <span class="hljs-built_in">String>, TreeTableCell<letvcookietable, <span class="hljs-built_in">String>>() {
@Override
public TreeTableCell<letvcookietable, <span class="hljs-built_in">String> call(TreeTableColumn<letvcookietable, <span class="hljs-built_in">String> param) {
JFXButton button = <span class="hljs-keyword">new JFXButton();
button.setText(<span class="hljs-string">"删除");
<span class="hljs-keyword">return <span class="hljs-keyword">new TreeTableCell<letvcookietable, <span class="hljs-built_in">String>() {
JFXButton delBtn = button;
@Override
protected <span class="hljs-keyword">void updateItem(<span class="hljs-built_in">String item, boolean empty) {
<span class="hljs-keyword">super.updateItem(item, empty);
<span class="hljs-keyword">if (empty) {
setGraphic(<span class="hljs-literal">null);
setText(<span class="hljs-literal">null);
} <span class="hljs-keyword">else {
delBtn.setOnMouseClicked(event -> {
Transaction transaction = session.beginTransaction();
logger.info(<span class="hljs-string">"删除:" + getIndex());
LetvCookieTable table = getTreeTableView().getTreeItem(getIndex()).getValue();
session.doWork(connection -> {
Statement st;
logger.info(<span class="hljs-string">"id:" + table.cookieId);
<span class="hljs-built_in">String sql = <span class="hljs-string">"delete from letv_cookie where cookie_id = " + table.cookieId;
st = connection.createStatement();
st.executeUpdate(sql);
st.close();
});
NotificationUtil.notification(<span class="hljs-string">"信息", <span class="hljs-string">"删除成功", <span class="hljs-string">"info");
transaction.commit();
observableCookie.remove(getIndex());
});
setGraphic(button);
setText(<span class="hljs-literal">null);
}
}
};
}
});</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></letvcookietable,></span></span></span></span></letvcookietable,></letvcookietable,></letvcookietable,></treetablecolumn<letvcookietable,></span></span></span></letvcookietable,>
JavaFX 获取当前行 :
<span class="hljs-selector-tag">getTableView()<span class="hljs-selector-class">.getItems()<span class="hljs-selector-class">.get(<span class="hljs-selector-tag">getIndex())</span></span></span></span>
JFoenix :
<span class="hljs-selector-tag">getTreeTableView()<span class="hljs-selector-class">.getTreeItem(<span class="hljs-selector-tag">getIndex())<span class="hljs-selector-class">.getValue()</span></span></span></span>
TreeTable 可编辑
key.setCellFactory(<span class="hljs-function"><span class="hljs-params">(TreeTableColumn<letvcookietable, string> param) -> <span class="hljs-keyword">new GenericEditableTreeTableCell<>(
<span class="hljs-keyword">new TextFieldEditorBuilder()));
key.setOnEditCommit(<span class="hljs-function"><span class="hljs-params">(TreeTableColumn.CellEditEvent<letvcookietable, string> t) -> {
LetvCookieTable table = t.getTreeTableView().getTreeItem(t.getTreeTablePosition()
.getRow())
.getValue();
table.cookieKey.set(t.getNewValue());
Transaction transaction = session.beginTransaction();
Query updateLink = session.createQuery(<span class="hljs-string">"update db.LetvCookieEntity set cookieKey = :newVal where cookieId=" + table.cookieId);
updateLink.setParameter(<span class="hljs-string">"newVal", t.getNewValue());
updateLink.executeUpdate();
transaction.commit();
session.clear();
NotificationUtil.notification(<span class="hljs-string">"信息",<span class="hljs-string">"更新成功!",<span class="hljs-string">"info");
});
</span></span></span></span></span></letvcookietable,></span></span></span></span></letvcookietable,></span></span>
未完待续 ……
Original: https://www.cnblogs.com/yangchaojie/p/11312477.html
Author: 杨超杰
Title: JavaFX 集成 Sqlite 和 Hibernate 开发爬虫应用
原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/593217/
转载文章受原作者版权保护。转载请注明原作者出处!