在Java上以表格形式将数据从数据库输出到用户 [英] Output the data from the database to the user in the form on Java
本文介绍了在Java上以表格形式将数据从数据库输出到用户的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我最近开始学习Java。
我需要用Java编写一个Web应用程序，用户可以从下拉列表中的home html-page中的表单中选择他所需的产品。产品列表存储在数据库的表中（使用MySQL）。然后，所选产品应写在订单历史表中。
如何将数据库从数据库输出到下拉列表？
如何为用户实现必要产品的选择？
我该如何开始？有没有人得到一个小例子？
解决方案
Vaadin
这是使用Java和
用户首先从下拉列表中选择行星产品，然后单击订购按钮。
在这个数据输入区域下方，您会看到一对 Grid 小部件作为数据库的后门。左边是产品列表，不会改变。右侧是所有订单的列表，每次用户使用订单按钮下订单时，订单都会更新。
请注意，数据库是内存中的，而不是持久的，因为这只是一个小小的演示。因此，每次启动应用程序时，都会从头开始重建数据库。每次运行时订单_ 表开始为空。
最后，这绝不是生产就绪代码，只是一个展示可能性的例子。刚刚拍摄截图时我发现了一个与根本不选择产品有关的错误。 C'est la vie。
有关详细信息，请参阅 Vaadin 手册的以下部分：
- 按钮 - 按钮小部件概述。
- NativeSelect - 此下拉列表小部件的快速演示
- 选择组件 - 讨论小部件如
NativeSelect的工作原理在Vaadin - 网格 - 如何使用这个功能强大的数据网格小部件。
主应用程序类
基本上是样板，使Vaadin运行。重要的是中间的一对行， ProductPickerLayout 。
package com.basilbourque.example;
import javax.servlet.annotation.WebServlet;
import com.vaadin.annotations.Theme;
import com.vaadin.annotations.VaadinServletConfiguration;
import com.vaadin.server.VaadinRequest;
import com.vaadin.server.VaadinServlet;
import com.vaadin.ui。*;
/ **
*此UI是应用程序入口点。 UI可以表示浏览器窗口
*（或制表符）或嵌入了Vaadin应用程序的HTML页面的某些部分。
*< p>
*使用{@link #init（VaadinRequest）}初始化UI。此方法旨在被
*重写，以将组件添加到用户界面并初始化非组件功能。
* /
@Theme（mytheme）
公共类MyUI扩展UI {
@Override
protected void init（VaadinRequest vaadinRequest）{
final布局布局= new ProductPickerLayout（）;
this.setContent（layout）;
}
@WebServlet（urlPatterns =/ *，name =MyUIServlet，asyncSupported = true）
@VaadinServletConfiguration（ui = MyUI.class，productionMode = false）
公共静态类MyUIServlet扩展VaadinServlet {
}
}
Servlet上下文侦听器
对Web应用程序启动和退出做出反应。
package com.basilbourque.example;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import javax.servlet.annotation.WebListener;
import java.time.Instant;
@WebListener
公共类MyServletContextListener实现ServletContextListener {
MyDatabaseService databaseService;
@Override
public void contextInitialized（ServletContextEvent servletContextEvent）{
System.out.println（TRACE - contextInitialized+ Instant.now（））;
//数据库。
MyDatabaseService db = new MyDatabaseService（）;
db.establishDatabase（）;
}
@Override
public void contextDestroyed（ServletContextEvent servletContextEvent）{
//此方法故意留空。
}
}
数据库服务类
定义并预加载数据库。提供插入和查询，以便将数据移入和移出数据库。
package com.basilbourque.example;
import java.sql。*;
import java.time.Instant;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
公共类MyDatabaseService {
// --------- |成员| ------------------------------------
static final private String driverName =org .h2.Driver;
static final private String catalogName =ProdPop;
static final private String jdbcPath =jdbc：h2：mem：+ MyDatabaseService.catalogName +; DB_CLOSE_DELAY = -1; //jdbc：h2：mem：autogrid; //设置延迟以便在最后一次连接关闭后保留内存数据库。
静态最终私有字符串productTableName =product_;
static final private String orderTableName =order_;
public void establishDatabase（）{
//验证JDBC驱动程序。
try {
Class.forName（MyDatabaseService.driverName）;
} catch（ClassNotFoundException e）{
e.printStackTrace（）;
}
//连接并创建数据库。
try（Connection conn = DriverManager.getConnection（MyDatabaseService.jdbcPath）;
）{
String sql = null;
//创建product_ table。
//列：pkey_ name_
try（语句stmt = conn.createStatement（）;）{
sql =CREATE TABLE+ productTableName +（\ n+
pkey_ IDENTITY PRIMARY KEY，\ n+
name_ VARCHAR（80）NOT NULL \ n+
）; \ n;
System.out.println（TRACE - SQL：\ n+ sql）;
stmt.execute（sql）;
}
System.out.println（TRACE - Created table product_。）;
//创建order_ table。
//列：pkey_ fkey_product_ when_ordered_
try（语句stmt = conn.createStatement（）;）{
sql =CREATE TABLE+ orderTableName +（\ n+
pkey_ IDENTITY PRIMARY KEY，\ n+
fkey_product_ LONG NOT NULL，\ n+
when_ordered_ TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP \ n+
）; \ n+
ALTER TABLE+ orderTableName +ADD FOREIGN KEY（fkey_product_）REFERENCES product_（pkey_）; \ n
;
System.out.println（TRACE - SQL：\ n+ sql）;
stmt.execute（sql）;
}
//列出表
DatabaseMetaData md = conn.getMetaData（）;
try（ResultSet rs = md.getTables（null，null，null，null））{
while（rs.next（））{
System.out.println（rs.getString（ 3））;
}
}
//列出`product_`表的列。
try（ResultSet rs = md.getColumns（null，null，productTableName.toUpperCase（Locale.US），null））{
System.out.println（table of column：+ productTableName）;
while（rs.next（））{
System.out.println（rs.getString（4）+|+ rs.getString（5）+|+ rs.getString（6 ））; // COLUMN_NAME，DATA_TYPE，TYPE_NAME。
}
}
//列出`order_`表的列。
try（ResultSet rs = md.getColumns（null，null，orderTableName.toUpperCase（Locale.US），null））{
System.out.println（table of column：+ orderTableName）;
while（rs.next（））{
System.out.println（rs.getString（4）+|+ rs.getString（5）+|+ rs.getString（6 ））; // COLUMN_NAME，DATA_TYPE，TYPE_NAME。
}
}
//添加行
sql =INSERT INTO product_（name_）\ n+
VALUES（？） +
;;
列表<字符串>行星= List.of（水星，维纳斯，地球，火星，谷神星，木星，土星，天王星，海王星，冥王星）;
try（PreparedStatement ps = conn.prepareStatement（sql）;）{
for（String planet：planets）{
ps.setString（1，planet）;
ps.executeUpdate（）;
}
}
System.out.println（TRACE - 将表转换为初始状态。+ Instant.now（））;
this.dumpTableToConsole（MyDatabaseService.productTableName）;
this.dumpTableToConsole（MyDatabaseService.orderTableName）;
} catch（SQLException e）{
e.printStackTrace（）;
}
}
public void dumpTableToConsole（String tableName）{
try（Connection conn = DriverManager.getConnection（MyDatabaseService.jdbcPath）;
）{
System.out.println（TRACE - «+ tableName +»表转储到+ Instant.now（）的控制台;
String sql =SELECT * FROM+ tableName +;;
try（Statement stmt = conn.createStatement（）;
ResultSet rs = stmt.executeQuery（sql）;）{
ResultSetMetaData meta = rs.getMetaData（）;
int colCount = meta.getColumnCount（）;
int rowCount = 0;
while（rs.next（））{
rowCount ++;
System.out.print（Row＃+ rowCount +：）;
for（int col = 1; col< = colCount; col ++）{
System.out.print（meta.getColumnLabel（col）+=）;
Object o = rs.getObject（col）;
if（null！= o）{
System.out.print（o.toString（）+）;
}
}
System.out.println（）; // 新队。
}
System.out.println（«fin de+ tableName +»）;
}
} catch（SQLException e）{
e.printStackTrace（）;
}
}
public List<产品> fetchAllProducts（）{
System.out.println（TRACE MyDatabaseService :: fetchAllOrders at+ Instant.now（））;
列表<产品> products = new ArrayList<>（）;
//查询。循环ResultSet，实例化对象和收集。
try（Connection conn = DriverManager.getConnection（MyDatabaseService.jdbcPath）;
）{
System.out.println（TRACE - fetchAllProducts at+ Instant.now（））;
String sql =SELECT * FROM+ productTableName +;;
try（
语句stmt = conn.createStatement（）;
ResultSet rs = stmt.executeQuery（sql）;
）{
int rowCount = 0;
while（rs.next（））{
Long pkey = rs.getLong（pkey_）;
String name = rs.getString（name_）;
//从列值创建对象。
产品p =新产品（pkey，name）;
products.add（p）; //收集从数据库中检索到的每个`Order`对象。
}
}
} catch（SQLException e）{
e.printStackTrace（）;
}
退货产品;
}
public List<订单> fetchAllOrders（）{
System.out.println（TRACE MyDatabaseService :: fetchAllOrders at+ Instant.now（））;
列表<订单> orders = new ArrayList<>（）;
//查询。循环ResultSet，实例化对象和收集。
try（Connection conn = DriverManager.getConnection（MyDatabaseService.jdbcPath）;
）{
String sql =SELECT * FROM+ orderTableName +\ n ORDER BY pkey_ DESC \ n ;;
try（
语句stmt = conn.createStatement（）;
ResultSet rs = stmt.executeQuery（sql）;
）{
int rowCount = 0;
while（rs.next（））{
Long pkey = rs.getLong（pkey_）;
Long fkey_product = rs.getLong（fkey_product_）;
Instant when_ordered = rs.getObject（when_ordered_，Instant.class）;
//从列值创建对象。
订单o =新订单（pkey，fkey_product，when_ordered）;
orders.add（o）; //收集从数据库中检索到的每个`Order`对象。
}
}
} catch（SQLException e）{
e.printStackTrace（）;
}
退货订单;
}
public void insertOrder（Long fkeyProduct）{
System.out.println（TRACE - MyDatabaseService :: insertOrder at+ Instant.now（））;
try（Connection conn = DriverManager.getConnection（MyDatabaseService.jdbcPath）;
）{
String sql =INSERT INTO+ orderTableName +（fkey_product_）\ n+VALUES（ ？）; \ n;
PreparedStatement ps = conn.prepareStatement（sql）;
ps.setLong（1，fkeyProduct）;
ps.executeUpdate（）;
} catch（SQLException e）{
e.printStackTrace（）;
}
}
}
内容布局
显示我们的小部件： NativeSelect 产品下拉列表，订单按钮和一对 Grid data-grid。用作视图和控制器，用于与我们的用户交互。
package com.basilbourque.example;
import com.vaadin.data.HasValue;
import com.vaadin.data.HasValue.ValueChangeEvent;
import com.vaadin.ui。*;
import java.time.Instant;
import java.util.List;
公共类ProductPickerLayout扩展VerticalLayout {
Label prodPopLabel;
NativeSelect<产品> productSelect;
Button orderButton;
网格<产品> productsGrid;
Grid<订单> ordersGrid;
//构造函数
public ProductPickerLayout（）{
this.widgetsMake（）;
this.widgetsArrange（）;
}
private void widgetsMake（）{
//创建选择组件
this.prodPopLabel = new Label（Products：）;
this.productSelect = new NativeSelect<>（）;
//添加一些项目
List<产品> products = new MyDatabaseService（）。fetchAllProducts（）;
this.productSelect.setItems（products）;
this.productSelect.setItemCaptionGenerator（Product :: getName）;
//如果有更多
// select.setRows（3），则显示5个项目和滚动条;
productSelect.addValueChangeListener（new HasValue.ValueChangeListener< Product>（）{
@Override
public void valueChange（ValueChangeEvent< Product> valueChangeEvent）{
Product p = valueChangeEvent.getValue（）;
orderButton.setEnabled（null！= p）;
Notification.show（Selected：+ p.name）;
}
} ）;
this.orderButton = new Button（Order）;
this.orderButton.setEnabled（this.productSelect.getValue（）！= null）;
this.orderButton.addClickListener（（Button.ClickEvent e） - > {
this.placeOrder（）;
this.ordersGrid.setItems（new MyDatabaseService（）。fetchAllOrders（））;
}）;
MyDatabaseService db = new MyDatabaseService（）;
this.productsGrid = new Grid<>（Product.class）;
this.productsGrid.setItems（products）;
this.productsGrid.setCaption（Products）;
this.ordersGrid = new Grid<>（Order.class）;
列表<订单> orders = db.fetchAllOrders（）;
this.ordersGrid.setItems（orders）;
this.ordersGrid.setCaption（Orders）;
}
private void widgetsArrange（）{
HorizontalLayout orderBar = new HorizontalLayout（）;
orderBar.setSpacing（true）;
orderBar.addComponents（this.prodPopLabel，this.productSelect，this.orderButton）;
orderBar.setComponentAlignment（this.prodPopLabel，Alignment.MIDDLE_CENTER）;
orderBar.setComponentAlignment（this.productSelect，Alignment.MIDDLE_CENTER）;
orderBar.setComponentAlignment（this.orderButton，Alignment.MIDDLE_CENTER）;
HorizontalLayout gridsBar = new HorizontalLayout（）;
gridsBar.setSpacing（true）;
gridsBar.addComponents（this.productsGrid，this.ordersGrid）;
this.addComponents（orderBar，gridsBar）;
this.setExpandRatio（gridsBar，1.0F）;
}
private void placeOrder（）{
//获取当前所选产品的pkey。
产品p = this.productSelect.getValue（）;
if（null == p）{
throw new IllegalStateException（`productSelect` NativeSelect没有当前值。）;
}
长fkeyProduct = p.pkey;
//将行插入表格。
new MyDatabaseService（）。insertOrder（fkeyProduct）;
this.updateOrdersGrid（）;
}
private void updateOrdersGrid（）{
}
}
模型类 - 产品& 订单
Vaadin会自动检测并使用getter / setter访问器方法来显示<$ c中的数据$ c> Grid 数据网格小部件。当然，您可以改为手动配置。
我们的产品类。
package com.basilbourque.example;
公共类产品{
Long pkey;
字符串名称;
公共产品（长pkey，字符串名称）{
this.pkey = pkey;
this.name = name;
}
@Override
public String toString（）{
returnProduct {+
pkey =+ pkey +
| name ='+ name +
};
}
// -----------访问者| -----------------
public Long getPkey（）{
return pkey;
}
public void setPkey（Long pkey）{
this.pkey = pkey;
}
public String getName（）{
return name;
}
public void setName（String name）{
this.name = name;
}
}
我们的订单 class。
package com.basilbourque.example;
import java.time.Instant;
公共类订单{
Long pkey; //此订单的标识符。
Long fkeyProduct; //订购产品的标识符。
订货时即时; //下订单的那一刻
公共秩序（Long pkey，Long fkeyProduct，Instant whenOrdered）{
this.pkey = pkey;
this.fkeyProduct = fkeyProduct;
this.whenOrdered = whenOrdered;
}
@Override
public String toString（）{
returnOrder {+
pkey =+ pkey +
| fkeyProduct =+ fkeyProduct +
| whenOrdered =+ whenOrdered +
};
}
// -----------访问者| -----------------
public Long getPkey（）{
return pkey;
}
public void setPkey（Long pkey）{
this.pkey = pkey;
}
public Long getFkeyProduct（）{
return fkeyProduct;
}
public void setFkeyProduct（Long fkeyProduct）{
this.fkeyProduct = fkeyProduct;
}
public Instant getWhenOrdered（）{
returnOrdered;
}
public void setWhenOrdered（Instant whenOrdered）{
this.whenOrdered = whenOrdered;
}
}
Maven POM
pom.xml 文件，控制 Maven 用于加载依赖项（库）以及用于构建/运行Web应用程序。
<？xml version =1.0encoding =UTF-8？>
< project xmlns =http://maven.apache.org/POM/4.0.0xmlns：xsi =http://www.w3.org/2001/XMLSchema-instance
xsi：schemaLocation =http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd\">
< modelVersion> 4.0.0< / modelVersion>
< groupId> com.basilbourque.example< / groupId>
< artifactId> prodpop< / artifactId>
< packaging> war< / packaging>
< version> 1.0-SNAPSHOT< / version>
< name> prodpop< / name>
<先决条件>
< maven> 3< / maven>
< / prerequisites>
< properties>
< vaadin.version> 8.5.2< /vaadin.version>
< vaadin.plugin.version> 8.5.2< /vaadin.plugin.version>
< jetty.plugin.version> 9.4.12.v20180830< /jetty.plugin.version>
< project.build.sourceEncoding> UTF-8< /project.build.sourceEncoding>
< maven.compiler.source> 10< /maven.compiler.source>
< maven.compiler.target> 10< /maven.compiler.target>
<！ - 如果没有本地自定义，这也可以是fetch或cdn - >
< vaadin.widgetset.mode> local< /vaadin.widgetset.mode>
< / properties>
< repositories>
< repository>
< id> vaadin-addons< / id>
< url> http：//maven.vaadin.com/vaadin-addons< / url>
< / repository>
< / repositories>
< dependencyManagement>
< dependencies>
< dependency>
< groupId> com.vaadin< / groupId>
< artifactId> vaadin-bom< / artifactId>
< version> $ {vaadin.version}< / version>
< type> pom< / type>
< scope> import< / scope>
< / dependency>
< / dependencies>
< / dependencyManagement>
< dependencies>
< dependency>
< groupId> javax.servlet< / groupId>
< artifactId> javax.servlet-api< / artifactId>
< version> 3.1.0< / version>
< scope>提供< / scope>
< / dependency>
< dependency>
< groupId> com.vaadin< / groupId>
< artifactId> vaadin-server< / artifactId>
< / dependency>
< dependency>
< groupId> com.vaadin< / groupId>
< artifactId> vaadin-push< / artifactId>
< / dependency>
< dependency>
< groupId> com.vaadin< / groupId>
< artifactId> vaadin-client-compiled< / artifactId>
< / dependency>
< dependency>
< groupId> com.vaadin< / groupId>
< artifactId> vaadin-themes< / artifactId>
< / dependency>
<！ - Basil - >
< dependency>
< groupId> com.h2database< / groupId>
< artifactId> h2< / artifactId>
< version> 1.4.197< / version>
< / dependency>
< / dependencies>
< build>
< plugins>
< plugin>
< groupId> org.apache.maven.plugins< / groupId>
< artifactId> maven-war-plugin< / artifactId>
< version> 3.2.2< / version>
< configuration>
< failOnMissingWebXml> false< / failOnMissingWebXml>
<！ - 排除GWT编译器生成的不必要文件。 - >
< packagingExcludes> WEB-INF / classes / VAADIN / widgetsets / WEB-INF / **< / packagingExcludes>
< / configuration>
< / plugin>
< plugin>
< groupId> com.vaadin< / groupId>
< artifactId> vaadin-maven-plugin< / artifactId>
< version> $ {vaadin.plugin.version}< / version>
< executions>
< execution>
< goals>
< goal> update-theme< / goal>
< goal> update-widgetset< / goal>
< goal> compile< / goal>
<！ - 注释编译主题目标以使用即时主题编译 - >
< goal> compile-theme< / goal>
< / goals>
< / execution>
< / executions>
< / plugin>
< plugin>
< groupId> org.apache.maven.plugins< / groupId>
< artifactId> maven-clean-plugin< / artifactId>
< version> 3.1.0< / version>
<！ - 清理任何预编译的主题 - >
< configuration>
< filesets>
< fileset>
< directory> src / main / webapp / VAADIN / themes< / directory>
< includes>
< include> ** / styles.css< / include>
< include> ** / styles.scss.cache< / include>
< / includes>
< / fileset>
< / filesets>
< / configuration>
< / plugin>
<！ - Jetty插件允许我们通过
运行jetty轻松测试开发构建：在命令行上运行。 - >
< plugin>
< groupId> org.eclipse.jetty< / groupId>
< artifactId> jetty-maven-plugin< / artifactId>
< version> $ {jetty.plugin.version}< / version>
< configuration>
< scanIntervalSeconds> 2< / scanIntervalSeconds>
< / configuration>
< / plugin>
< / plugins>
< / build>
< profiles>
< profile>
<！ - Vaadin pre-release repositories - >
< id> vaadin-prerelease< / id>
< activation>
< activeByDefault> false< / activeByDefault>
< / activation>
< repositories>
< repository>
< id> vaadin-prereleases< / id>
< url> http：//maven.vaadin.com/vaadin-prereleases< / url>
< / repository>
< repository>
< id> vaadin-snapshots< / id>
< url> https://oss.sonatype.org/content/repositories/vaadin-snapshots/< / url>
< releases>
< enabled> false< / enabled>
< / releases>
< snapshots>
<enabled>true</enabled>
</snapshots>
</repository>
</repositories>
<pluginRepositories>
<pluginRepository>
<id>vaadin-prereleases</id>
<url>http://maven.vaadin.com/vaadin-prereleases</url>
</pluginRepository>
<pluginRepository>
<id>vaadin-snapshots</id>
<url>https://oss.sonatype.org/content/repositories/vaadin-snapshots/</url>
<releases>
<enabled>false</enabled>
</releases>
<snapshots>
<enabled>true</enabled>
</snapshots>
</pluginRepository>
</pluginRepositories>
< / profile>
</profiles>
< / project>
By the way, in the macOS world, a drop-down list is known as a pop-up menu or a popup.
I am recently start learning Java. I need to write a web-application in Java, where the user can select the product necessary for him from the form on the home html-page from the drop-down list. The list of products is stored in a table in the database (using MySQL). Then the selected product should be written in the "order history" table. How to output the database from the database to the drop-down list? How to realize the choice of the necessary product for user? How can I start? Have anyone got a small example?
解决方案
Vaadin
Here is a complete working example using Java with the Vaadin Framework version 8.5.2 to create a web app where an database run by H2 Database Engine tracks a list of products (10 planets of the solar system). A NativeSelect widget in Vaadin is populated from a List of Product objects loaded from a product_ table in that database. Each time the user clicks the Order button, an order is recorded as a row in the order_ table.
Here is a simple ERD diagram of the two tables in the database.
The user first selects a planet product from the drop-down list, then clicks the "Order" button.
Below this data-entry area you see pair of Grid widgets as a back-door peek into the database. On the left is the list of products, which does not change. On the right is a list of all orders, which updates every time the user places an order with that "Order" button.
Note that the database is in-memory, not persistent, as this is just a little demo. So every time you launch the app, the database is rebuilt from scratch. The order_ table starts out empty each run.
Lastly, this is by no means production-ready code, just an example to show possibilities. Just now while shooting that screenshot I found a bug related to choosing no product at all. C’est la vie.
For more info, see these sections of the Vaadin manual:
- Button – Overview of the button widget.
- NativeSelect — Quick demo of this drop-down list widget
- Selection Components — Discussion of how widgets such as
NativeSelectwork in Vaadin - Grid — How to use this powerful data-grid widget.
Main app class
Basically boilerplate, to make Vaadin run. What matters is the pair of lines in the middle, for ProductPickerLayout.
package com.basilbourque.example;
import javax.servlet.annotation.WebServlet;
import com.vaadin.annotations.Theme;
import com.vaadin.annotations.VaadinServletConfiguration;
import com.vaadin.server.VaadinRequest;
import com.vaadin.server.VaadinServlet;
import com.vaadin.ui.*;
/**
* This UI is the application entry point. A UI may either represent a browser window
* (or tab) or some part of an HTML page where a Vaadin application is embedded.
* <p>
* The UI is initialized using {@link #init(VaadinRequest)}. This method is intended to be
* overridden to add component to the user interface and initialize non-component functionality.
*/
@Theme ( "mytheme" )
public class MyUI extends UI {
@Override
protected void init ( VaadinRequest vaadinRequest ) {
final Layout layout = new ProductPickerLayout();
this.setContent( layout );
}
@WebServlet ( urlPatterns = "/*", name = "MyUIServlet", asyncSupported = true )
@VaadinServletConfiguration ( ui = MyUI.class, productionMode = false )
public static class MyUIServlet extends VaadinServlet {
}
}
Servlet context listener
Reacts to web app launching and exiting.
package com.basilbourque.example;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import javax.servlet.annotation.WebListener;
import java.time.Instant;
@WebListener
public class MyServletContextListener implements ServletContextListener {
MyDatabaseService databaseService;
@Override
public void contextInitialized ( ServletContextEvent servletContextEvent ) {
System.out.println( "TRACE - contextInitialized " + Instant.now() );
// Database.
MyDatabaseService db = new MyDatabaseService();
db.establishDatabase();
}
@Override
public void contextDestroyed ( ServletContextEvent servletContextEvent ) {
// This method intentionally left blank.
}
}
Database service class
Defines and preloads the database. Provides inserts and queries, to move data in and out of database.
package com.basilbourque.example;
import java.sql.*;
import java.time.Instant;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
public class MyDatabaseService {
// ---------| Members |------------------------------------
static final private String driverName = "org.h2.Driver";
static final private String catalogName = "ProdPop";
static final private String jdbcPath = "jdbc:h2:mem:" + MyDatabaseService.catalogName + ";DB_CLOSE_DELAY=-1"; // "jdbc:h2:mem:autogrid"; // Set delay to keep in-memory database even after last connection closed.
static final private String productTableName = "product_";
static final private String orderTableName = "order_";
public void establishDatabase () {
// Verify JDBC driver.
try {
Class.forName( MyDatabaseService.driverName );
} catch ( ClassNotFoundException e ) {
e.printStackTrace();
}
// Connect, and create database.
try ( Connection conn = DriverManager.getConnection( MyDatabaseService.jdbcPath ) ;
) {
String sql = null;
// Create product_ table.
// Columns: pkey_ name_
try ( Statement stmt = conn.createStatement() ; ) {
sql = "CREATE TABLE " + productTableName + " ( \n" +
" pkey_ IDENTITY PRIMARY KEY , \n" +
" name_ VARCHAR ( 80 ) NOT NULL \n" +
") ; \n";
System.out.println( "TRACE - SQL:\n" + sql );
stmt.execute( sql );
}
System.out.println( "TRACE - Created table product_." );
// Create order_ table.
// Columns: pkey_ fkey_product_ when_ordered_
try ( Statement stmt = conn.createStatement() ; ) {
sql = "CREATE TABLE " + orderTableName + " ( \n" +
" pkey_ IDENTITY PRIMARY KEY , \n" +
" fkey_product_ LONG NOT NULL , \n" +
" when_ordered_ TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP \n" +
") ; \n" +
"ALTER TABLE " + orderTableName + " ADD FOREIGN KEY ( fkey_product_ ) REFERENCES product_ ( pkey_ ) ; \n "
;
System.out.println( "TRACE - SQL:\n" + sql );
stmt.execute( sql );
}
// List tables
DatabaseMetaData md = conn.getMetaData();
try ( ResultSet rs = md.getTables( null , null , null , null ) ) {
while ( rs.next() ) {
System.out.println( rs.getString( 3 ) );
}
}
// List columns of `product_` table.
try ( ResultSet rs = md.getColumns( null , null , productTableName.toUpperCase( Locale.US ) , null ) ) {
System.out.println( "Columns of table: " + productTableName );
while ( rs.next() ) {
System.out.println( rs.getString( 4 ) + " | " + rs.getString( 5 ) + " | " + rs.getString( 6 ) ); // COLUMN_NAME, DATA_TYPE , TYPE_NAME.
}
}
// List columns of `order_` table.
try ( ResultSet rs = md.getColumns( null , null , orderTableName.toUpperCase( Locale.US ) , null ) ) {
System.out.println( "Columns of table: " + orderTableName );
while ( rs.next() ) {
System.out.println( rs.getString( 4 ) + " | " + rs.getString( 5 ) + " | " + rs.getString( 6 ) ); // COLUMN_NAME, DATA_TYPE , TYPE_NAME.
}
}
// Add rows
sql = "INSERT INTO product_ ( name_ ) \n" +
"VALUES ( ? ) " +
"; ";
List< String > planets = List.of( "Mercury" , "Venus" , "Earth" , "Mars" , "Ceres" , "Jupiter" , "Saturn" , "Uranus" , "Neptune" , "Pluto" );
try ( PreparedStatement ps = conn.prepareStatement( sql ) ; ) {
for ( String planet : planets ) {
ps.setString( 1 , planet );
ps.executeUpdate();
}
}
System.out.println( "TRACE - Dumping tables in their initial state. " + Instant.now() );
this.dumpTableToConsole( MyDatabaseService.productTableName );
this.dumpTableToConsole( MyDatabaseService.orderTableName );
} catch ( SQLException e ) {
e.printStackTrace();
}
}
public void dumpTableToConsole ( String tableName ) {
try ( Connection conn = DriverManager.getConnection( MyDatabaseService.jdbcPath ) ;
) {
System.out.println( "TRACE - « " + tableName + " » table dump to console at " + Instant.now() );
String sql = "SELECT * FROM " + tableName + " ;";
try ( Statement stmt = conn.createStatement() ;
ResultSet rs = stmt.executeQuery( sql ) ; ) {
ResultSetMetaData meta = rs.getMetaData();
int colCount = meta.getColumnCount();
int rowCount = 0;
while ( rs.next() ) {
rowCount++;
System.out.print( "Row # " + rowCount + ": " );
for ( int col = 1 ; col <= colCount ; col++ ) {
System.out.print( meta.getColumnLabel( col ) + "=" );
Object o = rs.getObject( col );
if ( null != o ) {
System.out.print( o.toString() + " " );
}
}
System.out.println( "" ); // Newline.
}
System.out.println( "« fin de " + tableName + " »" );
}
} catch ( SQLException e ) {
e.printStackTrace();
}
}
public List< Product > fetchAllProducts () {
System.out.println( "TRACE MyDatabaseService::fetchAllOrders at " + Instant.now() );
List< Product > products = new ArrayList<>();
// Query. Loop ResultSet, instantiating object, and collecting.
try ( Connection conn = DriverManager.getConnection( MyDatabaseService.jdbcPath ) ;
) {
System.out.println( "TRACE - fetchAllProducts at " + Instant.now() );
String sql = "SELECT * FROM " + productTableName + " ;";
try (
Statement stmt = conn.createStatement() ;
ResultSet rs = stmt.executeQuery( sql ) ;
) {
int rowCount = 0;
while ( rs.next() ) {
Long pkey = rs.getLong( "pkey_" );
String name = rs.getString( "name_" );
// Make object from column values.
Product p = new Product( pkey , name );
products.add( p ); // Collect each `Order` object retrieved from database.
}
}
} catch ( SQLException e ) {
e.printStackTrace();
}
return products;
}
public List< Order > fetchAllOrders () {
System.out.println( "TRACE MyDatabaseService::fetchAllOrders at " + Instant.now() );
List< Order > orders = new ArrayList<>();
// Query. Loop ResultSet, instantiating object, and collecting.
try ( Connection conn = DriverManager.getConnection( MyDatabaseService.jdbcPath ) ;
) {
String sql = "SELECT * FROM " + orderTableName + " \n ORDER BY pkey_ DESC \n ;";
try (
Statement stmt = conn.createStatement() ;
ResultSet rs = stmt.executeQuery( sql ) ;
) {
int rowCount = 0;
while ( rs.next() ) {
Long pkey = rs.getLong( "pkey_" );
Long fkey_product = rs.getLong( "fkey_product_" );
Instant when_ordered = rs.getObject( "when_ordered_" , Instant.class );
// Make object from column values.
Order o = new Order( pkey , fkey_product , when_ordered );
orders.add( o ); // Collect each `Order` object retrieved from database.
}
}
} catch ( SQLException e ) {
e.printStackTrace();
}
return orders;
}
public void insertOrder ( Long fkeyProduct ) {
System.out.println( "TRACE - MyDatabaseService::insertOrder at " + Instant.now() );
try ( Connection conn = DriverManager.getConnection( MyDatabaseService.jdbcPath ) ;
) {
String sql = "INSERT INTO " + orderTableName + "( fkey_product_ )\n" + " VALUES ( ? ) ;\n";
PreparedStatement ps = conn.prepareStatement( sql );
ps.setLong( 1 , fkeyProduct );
ps.executeUpdate();
} catch ( SQLException e ) {
e.printStackTrace();
}
}
}
Content layout
Displays our widgets: the NativeSelect drop-down list of products, order button, and pair of Grid data-grids. Serves as the view and the controller, for interacting with our user.
package com.basilbourque.example;
import com.vaadin.data.HasValue;
import com.vaadin.data.HasValue.ValueChangeEvent;
import com.vaadin.ui.*;
import java.time.Instant;
import java.util.List;
public class ProductPickerLayout extends VerticalLayout {
Label prodPopLabel;
NativeSelect< Product > productSelect;
Button orderButton;
Grid< Product > productsGrid;
Grid< Order > ordersGrid;
// Constructor
public ProductPickerLayout () {
this.widgetsMake();
this.widgetsArrange();
}
private void widgetsMake () {
// Create the selection component
this.prodPopLabel = new Label( "Products: " );
this.productSelect = new NativeSelect<>();
// Add some items
List< Product > products = new MyDatabaseService().fetchAllProducts();
this.productSelect.setItems( products );
this.productSelect.setItemCaptionGenerator( Product :: getName );
// Show 5 items and a scrollbar if there are more
// select.setRows( 3 );
productSelect.addValueChangeListener( new HasValue.ValueChangeListener< Product >() {
@Override
public void valueChange ( ValueChangeEvent< Product > valueChangeEvent ) {
Product p = valueChangeEvent.getValue();
orderButton.setEnabled( null != p );
Notification.show( "Selected: " + p.name );
}
} );
this.orderButton = new Button( "Order" );
this.orderButton.setEnabled( this.productSelect.getValue() != null );
this.orderButton.addClickListener( ( Button.ClickEvent e ) -> {
this.placeOrder();
this.ordersGrid.setItems( new MyDatabaseService().fetchAllOrders() );
} );
MyDatabaseService db = new MyDatabaseService();
this.productsGrid = new Grid<>( Product.class );
this.productsGrid.setItems( products );
this.productsGrid.setCaption( "Products" );
this.ordersGrid = new Grid<>( Order.class );
List< Order > orders = db.fetchAllOrders();
this.ordersGrid.setItems( orders );
this.ordersGrid.setCaption( "Orders" );
}
private void widgetsArrange () {
HorizontalLayout orderBar = new HorizontalLayout();
orderBar.setSpacing( true );
orderBar.addComponents( this.prodPopLabel , this.productSelect , this.orderButton );
orderBar.setComponentAlignment( this.prodPopLabel , Alignment.MIDDLE_CENTER );
orderBar.setComponentAlignment( this.productSelect , Alignment.MIDDLE_CENTER );
orderBar.setComponentAlignment( this.orderButton , Alignment.MIDDLE_CENTER );
HorizontalLayout gridsBar = new HorizontalLayout();
gridsBar.setSpacing( true );
gridsBar.addComponents( this.productsGrid , this.ordersGrid );
this.addComponents( orderBar , gridsBar );
this.setExpandRatio( gridsBar , 1.0F );
}
private void placeOrder () {
// Get pkey of the currently selected product.
Product p = this.productSelect.getValue();
if ( null == p ) {
throw new IllegalStateException( "The `productSelect` NativeSelect does not have a current value." );
}
Long fkeyProduct = p.pkey;
// Insert row into table.
new MyDatabaseService().insertOrder( fkeyProduct );
this.updateOrdersGrid();
}
private void updateOrdersGrid () {
}
}
Model classes – Product & Order
The getter/setter accessor methods are automatically detected and used by Vaadin to show data in the Grid data-grid widgets. Of course, you could instead do manual configuration.
Our Product class.
package com.basilbourque.example;
public class Product {
Long pkey;
String name;
public Product ( Long pkey , String name ) {
this.pkey = pkey;
this.name = name;
}
@Override
public String toString () {
return "Product{ " +
"pkey=" + pkey +
"| name='" + name +
" }";
}
// -----------| Accessors |-----------------
public Long getPkey () {
return pkey;
}
public void setPkey ( Long pkey ) {
this.pkey = pkey;
}
public String getName () {
return name;
}
public void setName ( String name ) {
this.name = name;
}
}
Our Order class.
package com.basilbourque.example;
import java.time.Instant;
public class Order {
Long pkey; // Identifier of this order.
Long fkeyProduct; // Identifier of the product being ordered.
Instant whenOrdered; // The moment the order was placed.
public Order ( Long pkey , Long fkeyProduct , Instant whenOrdered ) {
this.pkey = pkey;
this.fkeyProduct = fkeyProduct;
this.whenOrdered = whenOrdered;
}
@Override
public String toString () {
return "Order{ " +
"pkey=" + pkey +
"| fkeyProduct=" + fkeyProduct +
"| whenOrdered=" + whenOrdered +
" }";
}
// -----------| Accessors |-----------------
public Long getPkey () {
return pkey;
}
public void setPkey ( Long pkey ) {
this.pkey = pkey;
}
public Long getFkeyProduct () {
return fkeyProduct;
}
public void setFkeyProduct ( Long fkeyProduct ) {
this.fkeyProduct = fkeyProduct;
}
public Instant getWhenOrdered () {
return whenOrdered;
}
public void setWhenOrdered ( Instant whenOrdered ) {
this.whenOrdered = whenOrdered;
}
}
Maven POM
The pom.xml file, controlling Maven for loading dependencies (libraries) and for building/running the web app.
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.basilbourque.example</groupId>
<artifactId>prodpop</artifactId>
<packaging>war</packaging>
<version>1.0-SNAPSHOT</version>
<name>prodpop</name>
<prerequisites>
<maven>3</maven>
</prerequisites>
<properties>
<vaadin.version>8.5.2</vaadin.version>
<vaadin.plugin.version>8.5.2</vaadin.plugin.version>
<jetty.plugin.version>9.4.12.v20180830</jetty.plugin.version>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>10</maven.compiler.source>
<maven.compiler.target>10</maven.compiler.target>
<!-- If there are no local customizations, this can also be "fetch" or "cdn" -->
<vaadin.widgetset.mode>local</vaadin.widgetset.mode>
</properties>
<repositories>
<repository>
<id>vaadin-addons</id>
<url>http://maven.vaadin.com/vaadin-addons</url>
</repository>
</repositories>
<dependencyManagement>
<dependencies>
<dependency>
<groupId>com.vaadin</groupId>
<artifactId>vaadin-bom</artifactId>
<version>${vaadin.version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
<dependencies>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>3.1.0</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>com.vaadin</groupId>
<artifactId>vaadin-server</artifactId>
</dependency>
<dependency>
<groupId>com.vaadin</groupId>
<artifactId>vaadin-push</artifactId>
</dependency>
<dependency>
<groupId>com.vaadin</groupId>
<artifactId>vaadin-client-compiled</artifactId>
</dependency>
<dependency>
<groupId>com.vaadin</groupId>
<artifactId>vaadin-themes</artifactId>
</dependency>
<!--Basil-->
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>1.4.197</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-war-plugin</artifactId>
<version>3.2.2</version>
<configuration>
<failOnMissingWebXml>false</failOnMissingWebXml>
<!-- Exclude an unnecessary file generated by the GWT compiler. -->
<packagingExcludes>WEB-INF/classes/VAADIN/widgetsets/WEB-INF/**</packagingExcludes>
</configuration>
</plugin>
<plugin>
<groupId>com.vaadin</groupId>
<artifactId>vaadin-maven-plugin</artifactId>
<version>${vaadin.plugin.version}</version>
<executions>
<execution>
<goals>
<goal>update-theme</goal>
<goal>update-widgetset</goal>
<goal>compile</goal>
<!-- Comment out compile-theme goal to use on-the-fly theme compilation -->
<goal>compile-theme</goal>
</goals>
</execution>
</executions>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-clean-plugin</artifactId>
<version>3.1.0</version>
<!-- Clean up also any pre-compiled themes -->
<configuration>
<filesets>
<fileset>
<directory>src/main/webapp/VAADIN/themes</directory>
<includes>
<include>**/styles.css</include>
<include>**/styles.scss.cache</include>
</includes>
</fileset>
</filesets>
</configuration>
</plugin>
<!-- The Jetty plugin allows us to easily test the development build by
running jetty:run on the command line. -->
<plugin>
<groupId>org.eclipse.jetty</groupId>
<artifactId>jetty-maven-plugin</artifactId>
<version>${jetty.plugin.version}</version>
<configuration>
<scanIntervalSeconds>2</scanIntervalSeconds>
</configuration>
</plugin>
</plugins>
</build>
<profiles>
<profile>
<!-- Vaadin pre-release repositories -->
<id>vaadin-prerelease</id>
<activation>
<activeByDefault>false</activeByDefault>
</activation>
<repositories>
<repository>
<id>vaadin-prereleases</id>
<url>http://maven.vaadin.com/vaadin-prereleases</url>
</repository>
<repository>
<id>vaadin-snapshots</id>
<url>https://oss.sonatype.org/content/repositories/vaadin-snapshots/</url>
<releases>
<enabled>false</enabled>
</releases>
<snapshots>
<enabled>true</enabled>
</snapshots>
</repository>
</repositories>
<pluginRepositories>
<pluginRepository>
<id>vaadin-prereleases</id>
<url>http://maven.vaadin.com/vaadin-prereleases</url>
</pluginRepository>
<pluginRepository>
<id>vaadin-snapshots</id>
<url>https://oss.sonatype.org/content/repositories/vaadin-snapshots/</url>
<releases>
<enabled>false</enabled>
</releases>
<snapshots>
<enabled>true</enabled>
</snapshots>
</pluginRepository>
</pluginRepositories>
</profile>
</profiles>
</project>
By the way, in the macOS world, a "drop-down list" is known as a "pop-up menu" or a "popup".
这篇关于在Java上以表格形式将数据从数据库输出到用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
