在Java上以表格形式将数据从数据库输出到用户 [英] Output the data from the database to the user in the form on 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
NativeSelect
work 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屋!