Shulou(Shulou.com)05/31 Report--

How to use Streams in Java to query the database? in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Sample database

The sample database we use is Sakila. It has Film (film), Actor (actor), Category (category) and other forms, download address: https://dev.mysql.com/doc/index-other.html.

Step 1: connect to the database

We use Speedment Initializer to configure the pom.xml file, and when we click download, we will get a project folder with automatically generated Main.java files.

Then, extract the folder zip., to open the command line, and then go to the location of the pO.xml file. Enter the following command:

Mvn speedment:tool

Speedment will be launched and you will be prompted for an authorization code. Select "Start Free" and you will get a free license. Then you can connect to the database and start using it.

Step 2: generate the code

When the database starts loading schema data, you can click "Generate" to generate the complete Java domain model.

Step 3: write the application code

A generator for Speedment is also automatically generated in step 2. Open the Main.java file and replace the code in the main () method with the following code:

SakilaApplication app = new SakilaApplicationBuilder () .withpassword ("sakila-password") / / Replace with your own password .build ()

Next, we will write an application that prints out all the movies. Of course, this is just a Mini Program, and we need to improve it.

/ / Obtains a FilmManager that allows us to// work with the "film" tableFilmManager films = app.getOrThrow (FilmManager.class); / / Create a stream of all films and print// each and every filmfilms.stream () .forEach (System.out::println)

At run time, Java stream automatically generates SQL. To view the SQL code, you need to modify the Application Builder and turn on logging using the STREAM log type.

SakilaApplication app = new SakilaApplicationBuilder () .withPassword ("sakila-password") .withLogging (ApplicationBuilder.LogType.STREAM) .build ()

The following is the SQL code when the application is run:

SELECT `film_ id`, `title`, `promotion`, `release_ year`, `rental_ id`, `original_language_ id`, `rental_ duration`, `rental_ rate`, `bearth`, `replacement_ cost`, `rating`, `special_ Secretures`, `last_ update`FROM `sakila`.`room`, values: []

The SQL code varies depending on the type of database you choose (for example, MySQL,MariaDB,PostgreSQL,Oracle,MS SQL Server,DB2,AS400, etc.), and these changes are automatic.

The above code will produce the following output (concise)

FilmImpl {filmId = 1, title = ACADEMY DINOSAUR,..., length = 86,...} FilmImpl {filmId = 2, title = ACE GOLDFINGER,..., length = 48,...} FilmImpl {filmId = 3, title = ADAPTATION HOLES,..., length = 50,...}.

Step 4: use the filter

All stream operations for Speedment streams, including filters. Suppose we only want to filter out movies that take more than 60 minutes, which can be done through the following code:

Films.stream () .filter (Film.LENGTH.greaterThan (60)) .forEach (System.out::println)

Generate SQL:

SELECT `film_ id`, `title`, `promotion`, `rental_ year`, `language_ id`, `original_language_ id`, `rental_ duration`, `rental_ rate`, `bearth`, `replacement_ cost`, `rating`, `special_ roomures`, `last_ update`FROM`sakila`.`room`where (`roomth` >?), values: [60]

Generate output:

FilmImpl {filmId = 1, title = ACADEMY DINOSAUR,..., length = 86,...} FilmImpl {filmId = 4, title = AFFAIR PREJUDICE,..., length = 117,...} FilmImpl {filmId = 5, title = AFRICAN EGG,. Length = 130,130,...}

You can create more complex expressions by combining filters, as follows:

Films.stream () .filter (Film.LENGTH.greaterThan (60) .or (Film.LENGTH.lessThan (30)) .forEach (System.out::println)

This will recycle films that take less than 30 minutes or more than an hour. At this point, check your log file, and you will find that the stream has generated SQL.

Step 5: define the order of the elements

By default, elements that appear in the flow are undefined. To define a particular order, you need to apply the SORTED () operation to such a flow:

Films.stream () .filter (Film.LENGTH.greaterThan (60)) .sorted (Film.TITLE) .forEach (System.out::println)

Generate SQL

SELECT `film_ id`, `title`, `promotion`, `release_ year`, `language_ id`, `original_language_ id`, `rental_ duration`, `rental_ rate`, `bearth`, `replacement_ cost`, `rating`, `special_ roomures`, `last_ update`FROM `sakila`.`room`where (`roomth` >?) ORDER BY `bearth` ASC,values: [60]

Generate output:

FilmImpl {filmId = 77, title = BIRDS PERDITION,..., length = 61...} FilmImpl {filmId = 106, title = BULWORTH COMMANDMENTS,..., length = 61,} FilmImpl {filmId = 114, title = CAMELOT VACATION,..., length = 61.

You can also combine multiple classifiers to define primary order, secondary order, and so on.

Films.stream () .filter (Film.LENGTH.greaterThan (60)) .sorted (Film.LENGTH.thenComparing (Film.TITLE.reversed () .forEach (System.out::println)

This sorts movie elements in LENGTH order (ascending order) and TITLE order (descending order). You can make any combination of quantity fields.

Note: if you form two or more fields in ascending order, you should use .comparator (). I.e. Field method. For example: sorted (Film.LENGTH.thenComparing (Film.TITLE.comparator ().

Step 6: avoid large object blocks (Large Object Chunks)

People generally paginate the results to avoid using unnecessary large object blocks (Large Object Chunks). Suppose we want to see 50 elements per page, which we can do with the following code:

Private static final int PAGE_SIZE = 50th public static Stream page (Manager manager, Predicate)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.