将数据库中的数据以Java上的形式输出给用户 [英] Output the data from the database to the user in the form on Java
本文介绍了将数据库中的数据以Java上的形式输出给用户的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我最近开始学习 Java.我需要用 Java 编写一个 Web 应用程序，用户可以在其中从下拉列表的主页 html 页面上的表单中选择他需要的产品.产品列表存储在数据库的表中(使用 MySQL).然后应将所选产品写入订单历史"表中.如何从数据库中输出数据库到下拉列表?如何实现用户对所需产品的选择?我该如何开始?有没有人举个小例子?
解决方案
Vaadin
这是一个完整的工作示例，使用 Java 和
用户首先从下拉列表中选择一个星球产品，然后点击订购"按钮.
在此数据输入区域下方，您会看到一对 Grid 小部件，作为数据库的后门窥视.左边是产品列表，没有变化.右侧是所有订单的列表，每次用户使用订单"按钮下订单时都会更新.
请注意，数据库是在内存中的，不是持久的，因为这只是一个小演示.因此，每次启动应用程序时，都会从头开始重建数据库.order_ 表在每次运行时都是空的.
最后，这绝不是可用于生产的代码，只是一个展示可能性的示例.刚才在拍摄该屏幕截图时，我发现了一个与根本不选择任何产品有关的错误.C'est la vie.
有关详细信息，请参阅 Vaadin 手册的以下部分:
- 按钮 –按钮小部件概览.
- NativeSelect —此下拉列表小部件的快速演示
- 选择组件 — 讨论诸如
NativeSelect之类的小部件如何在 Vaadin 中工作 - 网格 —如何使用这个强大的数据网格小部件.
主应用类
基本上是样板文件，让 Vaadin 运行.重要的是中间的那对线，用于 ProductPickerLayout.
package com.basilbourque.example;导入 javax.servlet.annotation.WebServlet;导入 com.vaadin.annotations.Theme;导入 com.vaadin.annotations.VaadinServletConfiguration;导入 com.vaadin.server.VaadinRequest;导入 com.vaadin.server.VaadinServlet;导入 com.vaadin.ui.*;/*** 此 UI 是应用程序入口点.UI 可以代表一个浏览器窗口*(或选项卡)或嵌入 Vaadin 应用程序的 HTML 页面的某些部分.* <p>* UI 使用 {@link #init(VaadinRequest)} 初始化.这种方法旨在* 重写以将组件添加到用户界面并初始化非组件功能.*/@Theme ( "mytheme" )公共类 MyUI 扩展了 UI {@覆盖受保护的无效初始化(VaadinRequest vaadinRequest){最终布局布局 = 新的 ProductPickerLayout();this.setContent( 布局 );}@WebServlet ( urlPatterns = "/*", name = "MyUIServlet", asyncSupported = true )@VaadinServletConfiguration ( ui = MyUI.class, productionMode = false )公共静态类 MyUIServlet 扩展了 VaadinServlet {}}Servlet 上下文监听器
对网络应用启动和退出做出反应.
package com.basilbourque.example;导入 javax.servlet.ServletContextEvent;导入 javax.servlet.ServletContextListener;导入 javax.servlet.annotation.WebListener;导入 java.time.Instant;@WebListener公共类 MyServletContextListener 实现 ServletContextListener {MyDatabaseService 数据库服务；@覆盖public void contextInitialized ( ServletContextEvent servletContextEvent ) {System.out.println( "TRACE - contextInitialized " + Instant.now());//数据库.MyDatabaseService db = new MyDatabaseService();db.建立数据库()；}@覆盖public void contextDestroyed ( ServletContextEvent servletContextEvent ) {//此方法有意留空.}}数据库服务类
定义并预加载数据库.提供插入和查询，将数据移入和移出数据库.
package com.basilbourque.example;导入 java.sql.*;导入 java.time.Instant;导入 java.util.ArrayList;导入 java.util.List;导入 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";//设置延迟以保持内存数据库，即使在上次连接关闭后也是如此.static final private String productTableName = "product_";static final private String orderTableName = "order_";公共无效建立数据库(){//验证 JDBC 驱动程序.尝试 {Class.forName( MyDatabaseService.driverName );} catch ( ClassNotFoundException e ) {e.printStackTrace();}//连接，并创建数据库.尝试(连接连接 = DriverManager.getConnection(MyDatabaseService.jdbcPath)；){字符串 sql = null;//创建 product_ 表.//列:pkey_name_尝试(语句 stmt = conn.createStatement() ; ) {sql = "CREATE TABLE " + productTableName + " (
" +" pkey_ IDENTITY PRIMARY KEY ,
" +" name_ VARCHAR ( 80 ) NOT NULL
" +");
";System.out.println( "TRACE - SQL:
" + sql );stmt.execute(sql);}System.out.println( "TRACE - 创建的表 product_." );//创建 order_ 表.//列: pkey_ fkey_product_ when_ordered_尝试(语句 stmt = conn.createStatement() ; ) {sql = "CREATE TABLE " + orderTableName + " (
" +" pkey_ IDENTITY PRIMARY KEY ,
" +" fkey_product_ LONG NOT NULL ,
" +" when_ordered_ TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
" +") ;
" +"ALTER TABLE" + orderTableName + " ADD FOREIGN KEY ( fkey_product_ ) REFERENCES product_ ( pkey_ ) ;
";System.out.println( "TRACE - SQL:
" + sql );stmt.execute(sql);}//列出表格DatabaseMetaData md = conn.getMetaData();尝试(结果集 rs = md.getTables(空，空，空，空)){而(rs.next()){System.out.println( rs.getString( 3 ) );}}//列出 `product_` 表的列.尝试 ( ResultSet rs = md.getColumns( null , null , productTableName.toUpperCase( Locale.US ) , null ) ) {System.out.println("表的列数:" + productTableName );而(rs.next()){System.out.println( rs.getString( 4 ) + " | " + rs.getString( 5 ) + " | " + rs.getString( 6 ) );//列名称、数据类型、类型名称.}}//列出 `order_` 表的列.尝试 ( ResultSet rs = md.getColumns( null , null , orderTableName.toUpperCase( Locale.US ) , null ) ) {System.out.println("表的列数:" + orderTableName );而(rs.next()){System.out.println( rs.getString( 4 ) + " | " + rs.getString( 5 ) + " | " + rs.getString( 6 ) );//列名称、数据类型、类型名称.}}//添加行sql =插入产品_(名称_)
"+值(?)" +"; ";列表<字符串 >行星 = List.of( "Mercury", "Venus", "Earth", "Mars", "Ceres", "Jupiter", "Saturn", "Uranus", "Neptune", "Pluto");尝试 ( PreparedStatement ps = conn.prepareStatement( sql ) ; ) {对于(字符串行星:行星){ps.setString( 1 , 行星 );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 ) {尝试(连接连接 = DriverManager.getConnection(MyDatabaseService.jdbcPath)；){System.out.println("TRACE - « " + tableName + " » 表转储到控制台在 " + Instant.now() );String sql = "SELECT * FROM " + tableName + ";";尝试 ( 语句 stmt = conn.createStatement() ;结果集 rs = stmt.executeQuery(sql);){结果集元数据元 = rs.getMetaData();int colCount = meta.getColumnCount();int rowCount = 0;而(rs.next()){行数++；System.out.print( "Row # " + rowCount + ": " );for ( int col = 1 ; col <= colCount ; col++ ) {System.out.print( meta.getColumnLabel( col ) + "=" );对象 o = rs.getObject( col );如果(空！= o){System.out.print(o.toString() + " ");}}System.out.println("");//新队.}System.out.println("« fin de " + tableName + " »" );}} catch ( SQLException e ) {e.printStackTrace();}}公共列表<产品 >fetchAllProducts () {System.out.println( "TRACE MyDatabaseService::fetchAllOrders at " + Instant.now() );列表<产品 >产品 = 新的 ArrayList();//询问.循环 ResultSet，实例化对象，并收集.尝试(连接连接 = DriverManager.getConnection(MyDatabaseService.jdbcPath)；){System.out.println( "TRACE - fetchAllProducts at " + Instant.now() );String sql = "SELECT * FROM " + productTableName + ";";尝试 (语句 stmt = conn.createStatement() ;结果集 rs = stmt.executeQuery(sql);){int rowCount = 0;而(rs.next()){Long pkey = rs.getLong("pkey_");String name = rs.getString("name_");//从列值创建对象.Product p = new Product( pkey , name );产品添加( p );//收集从数据库中检索到的每个 `Order` 对象.}}} catch ( SQLException e ) {e.printStackTrace();}退货产品；}公共列表<订单 >fetchAllOrders () {System.out.println( "TRACE MyDatabaseService::fetchAllOrders at " + Instant.now() );列表<订单 >订单 = 新的 ArrayList<>();//询问.循环 ResultSet，实例化对象，并收集.尝试(连接连接 = DriverManager.getConnection(MyDatabaseService.jdbcPath)；){String sql = "SELECT * FROM " + orderTableName + "
ORDER BY pkey_ DESC
;";尝试 (语句 stmt = conn.createStatement() ;结果集 rs = stmt.executeQuery(sql);){int rowCount = 0;而(rs.next()){Long pkey = rs.getLong("pkey_");Long fkey_product = rs.getLong( "fkey_product_" );即时 when_ordered = rs.getObject( "when_ordered_" , Instant.class );//从列值创建对象.Order o = new Order( pkey , fkey_product , when_ordered );订单.add(o);//收集从数据库中检索到的每个 `Order` 对象.}}} catch ( SQLException e ) {e.printStackTrace();}退货单；}公共无效插入订单(长 fkeyProduct){System.out.println( "TRACE - MyDatabaseService::insertOrder at " + Instant.now() );尝试(连接连接 = DriverManager.getConnection(MyDatabaseService.jdbcPath)；){String sql = "INSERT INTO " + orderTableName + "( fkey_product_ )
" + " VALUES ( ? ) ;
";PreparedStatement ps = conn.prepareStatement(sql);ps.setLong( 1 , fkeyProduct );ps.executeUpdate();} catch ( SQLException e ) {e.printStackTrace();}}}内容布局
显示我们的小部件:NativeSelect 产品下拉列表、订购按钮和一对 Grid 数据网格.用作视图和控制器，用于与我们的用户进行交互.
package com.basilbourque.example;导入 com.vaadin.data.HasValue;导入 com.vaadin.data.HasValue.ValueChangeEvent;导入 com.vaadin.ui.*;导入 java.time.Instant;导入 java.util.List;公共类 ProductPickerLayout 扩展 VerticalLayout {标签 prodPopLabel;本机选择<产品 >产品选择；按钮订单按钮；网格<产品 >产品网格；网格<订单 >订单网格；//构造函数公共产品选择器布局(){this.widgetsMake();this.widgetsArrange();}私有无效小部件Make(){//创建选择组件this.prodPopLabel = new Label("产品:");this.productSelect = new NativeSelect<>();//添加一些项目列表<产品 >products = new MyDatabaseService().fetchAllProducts();this.productSelect.setItems(产品);this.productSelect.setItemCaptionGenerator(Product::getName);//如果有更多，则显示 5 个项目和一个滚动条//select.setRows( 3 );productSelect.addValueChangeListener(new HasValue.ValueChangeListener(){@覆盖public void valueChange (ValueChangeEvent< Product > valueChangeEvent ) {产品 p = valueChangeEvent.getValue();orderButton.setEnabled( null != p );Notification.show("选择:" + p.name );}});this.orderButton = new Button("订单");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(产品);this.productsGrid.setCaption("产品");this.ordersGrid = new Grid<>(Order.class);列表<订单 >订单 = db.fetchAllOrders();this.ordersGrid.setItems(订单);this.ordersGrid.setCaption("订单");}私有无效小部件Arrange(){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 );}私人无效地方订单(){//获取当前选中产品的pkey.产品 p = this.productSelect.getValue();如果(空== p){throw new IllegalStateException("`productSelect` NativeSelect 没有当前值.");}长 fkeyProduct = p.pkey;//插入行到表中.新的 MyDatabaseService().insertOrder( fkeyProduct );this.updateOrdersGrid();}私有无效updateOrdersGrid(){}} 模型类 - Product &订单
Vaadin 会自动检测并使用 getter/setter 访问器方法来在 Grid 数据网格小部件中显示数据.当然，您也可以改为手动配置.
我们的 Product 类.
package com.basilbourque.example;公共类产品{长密钥；字符串名称；公共产品(长 pkey ，字符串名称){this.pkey = pkey;this.name = 名称；}@覆盖公共字符串 toString() {返回产品{" +"pkey=" + pkey +|姓名="+姓名+" }";}//-----------|配件|-----------------公共长getPkey(){返回密钥；}公共无效setPkey(长pkey){this.pkey = pkey;}公共字符串getName(){返回名称；}public void setName(字符串名称){this.name = 名称；}}我们的Order类.
package com.basilbourque.example;导入 java.time.Instant;公共类订单{长密钥；//此订单的标识符.长 fkeyProduct;//所订购产品的标识符.立即订购；//下单的那一刻.公共订单(Long pkey，Long fkeyProduct，Instant whenOrdered){this.pkey = pkey;this.fkeyProduct = fkeyProduct;this.whenOrdered = whenOrdered;}@覆盖公共字符串 toString() {返回订单{" +"pkey=" + pkey +"| fkeyProduct=" + fkeyProduct +"| whenOrdered=" + whenOrdered +" }";}//-----------|配件|-----------------公共长getPkey(){返回密钥；}公共无效setPkey(长pkey){this.pkey = pkey;}公共长getFkeyProduct(){返回 fkeyProduct;}public void setFkeyProduct (Long fkeyProduct) {this.fkeyProduct = fkeyProduct;}公共即时getWhenOrdered(){下单时退货；}public void setWhenOrdered(即时whenOrdered){this.whenOrdered = whenOrdered;}}Maven POM
pom.xml 文件，控制 Maven 进行加载依赖项(库)和用于构建/运行网络应用.
<modelVersion>4.0.0</modelVersion><groupId>com.basilbourque.example</groupId><artifactId>prodpop</artifactId><包装>战争</包装><version>1.0-SNAPSHOT</version><name>prodpop</name><先决条件><maven>3</maven></先决条件><属性><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>本地</vaadin.widgetset.mode></属性><存储库><存储库><id>vaadin-addons</id><url>http://maven.vaadin.com/vaadin-addons</url></repository></repositories><依赖管理><依赖项><依赖><groupId>com.vaadin</groupId><artifactId>vaadin-bom</artifactId><version>${vaadin.version}</version><type>pom</type><范围>导入</范围></依赖></依赖项></dependencyManagement><依赖项><依赖><groupId>javax.servlet</groupId><artifactId>javax.servlet-api</artifactId><version>3.1.0</version><范围>提供</范围></依赖><依赖><groupId>com.vaadin</groupId><artifactId>vaadin-server</artifactId></依赖><依赖><groupId>com.vaadin</groupId><artifactId>vaadin-push</artifactId></依赖><依赖><groupId>com.vaadin</groupId><artifactId>vaadin-client-compiled</artifactId></依赖><依赖><groupId>com.vaadin</groupId><artifactId>vaadin-themes</artifactId></依赖><!--罗勒--><依赖><groupId>com.h2database</groupId><artifactId>h2</artifactId><version>1.4.197</version></依赖></依赖项><构建><插件><插件><groupId>org.apache.maven.plugins</groupId><artifactId>maven-war-plugin</artifactId><version>3.2.2</version><配置><failOnMissingWebXml>false</failOnMissingWebXml><!-- 排除 GWT 编译器生成的不需要的文件.--><packagingExcludes>WEB-INF/classes/VAADIN/widgetsets/WEB-INF/**</packagingExcludes></配置></插件><插件><groupId>com.vaadin</groupId><artifactId>vaadin-maven-plugin</artifactId><version>${vaadin.plugin.version}</version><执行><执行><目标><目标>更新主题</目标><goal>update-widgetset</goal><目标>编译</目标><!-- 注释掉 compile-theme 目标以使用动态主题编译 --><目标>编译主题</目标></目标></执行></执行></插件><插件><groupId>org.apache.maven.plugins</groupId><artifactId>maven-clean-plugin</artifactId><version>3.1.0</version><!-- 清理任何预编译的主题--><配置><文件集><文件集><目录>src/main/webapp/VAADIN/themes</directory><包括><include>**/styles.css</include><include>**/styles.scss.cache</include></包括></文件集></文件集></配置></插件><!-- Jetty 插件允许我们通过以下方式轻松测试开发构建运行 jetty:在命令行上运行.--><插件><groupId>org.eclipse.jetty</groupId><artifactId>jetty-maven-plugin</artifactId><version>${jetty.plugin.version}</version><配置><scanIntervalSeconds>2</scanIntervalSeconds></配置></插件></插件></build><个人资料><个人资料><!-- Vaadin 预发布存储库--><id>vaadin-prerelease</id><激活><activeByDefault>false</activeByDefault></激活><存储库><存储库><id>vaadin-prereleases</id><url>http://maven.vaadin.com/vaadin-prereleases</url></repository><存储库><id>vaadin-snapshots</id><url>https://oss.sonatype.org/content/repositories/vaadin-snapshots/</url><发布><启用>假</启用></发布><快照><启用>真</启用></快照></repository></repositories><pluginRepositories><插件库><id>vaadin-prereleases</id><url>http://maven.vaadin.com/vaadin-prereleases</url></pluginRepository><插件库><id>vaadin-snapshots</id><url>https://oss.sonatype.org/content/repositories/vaadin-snapshots/</url><发布><启用>假</启用></发布><快照><启用>真</启用></快照></pluginRepository></pluginRepositories></个人资料></个人资料></项目><小时>
顺便说一下，在 macOS 世界中，下拉列表"被称为弹出菜单"或弹出菜单".
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 + " (
" +
" pkey_ IDENTITY PRIMARY KEY ,
" +
" name_ VARCHAR ( 80 ) NOT NULL
" +
") ;
";
System.out.println( "TRACE - SQL:
" + 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 + " (
" +
" pkey_ IDENTITY PRIMARY KEY ,
" +
" fkey_product_ LONG NOT NULL ,
" +
" when_ordered_ TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
" +
") ;
" +
"ALTER TABLE " + orderTableName + " ADD FOREIGN KEY ( fkey_product_ ) REFERENCES product_ ( pkey_ ) ;
"
;
System.out.println( "TRACE - SQL:
" + 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_ )
" +
"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 + "
ORDER BY pkey_ DESC
;";
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_ )
" + " VALUES ( ? ) ;
";
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屋！
查看全文
