vert-x-uses JDBC connections asynchronously Sql_java

In this article, we'll see how to use Hsql in vert.x applications, as well as using arbitrary JDBC, and the asynchronous API provided by Vertx-jdbc-client, the code for this article is GitHub.

Asynchronous?

A very important feature of Vert.x is its asynchronous nature. Using an asynchronous API, you do not need to wait for results to return, and Vert.x will actively notify you when a result returns. To illustrate this, let's look at a simple example.

Let's assume there's an Add method. In general, it will be used like int r = Add (1, 1). This is a synchronized API, so you have to wait until the result is returned. The asynchronous API would be this way: Add (1, 1, r-> {/*do something with the result*/}). In this release, you pass in a handler, which is invoked when the result is calculated. This method does not return anything and is implemented as follows:

public void Add (int a, int b, handler<integer> resulthandler) {
int r = a + B;
Resulthandler.handle (R);
}

To avoid confusing concepts, asynchronous APIs are not multi-threaded. As we see in the Add example, multithreading is not involved.

Asynchronous JDBC

Looking at some basic asynchronous APIs, now understand the next vertx-jdbc-client. This component allows us to interact with the database through JDBC driver. These interactions are asynchronous, as before:

String sql = "SELECT * FROM Products";
ResultSet rs = stmt.executequery (SQL);

Now to do this:

Connection.query ("SELECT * FROM Products", result-> {
//does something with the result
});

This model is more efficient when the results come out and vert.x notifications, avoiding waiting results.

Increase maven Dependencies

Add two Maven dependencies to the Pom.xml file

<dependency>
<groupId>io.vertx</groupId>
<artifactid>vertx-jdbc-client</ artifactid>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>org.hsqldb</groupId>
<artifactId>hsqldb</artifactId>
<version >2.3.3</version>
</dependency>

The first dependency provides the vertx-jdbc-client, and the second provides the hsql JDBC driver. If you want to use another database, modify this dependency, and you will also need to modify the JDBC URL and JDBC driver name.

Initializing JDBC Client

To create a JDBC client (clients):

In the Myfirstverticle class, declare a new variable jdbcclient jdbc and add it in the Start method:

JDBC = jdbcclient.createshared (vertx, config (), "my-whisky-collection");

A JDBC client instance is created and the JDBC client is configured with the Verticle configuration file. This profile requires that the following configuration be provided to allow JDBC client to function correctly:

Url-jdbc URL, for example: Jdbc:hsqldb:mem:db?shutdown=true
_driver Class-jdbc Drive, for example: Org.hsqldb.jdbcDriver

With client, the next step is to connect to the database. The connection database is implemented by using jdbc.getconnection, and jdbc.getconnection needs to pass in a handler<asyncresult<sqlconnection>> parameter. We have a deep understanding of this type. First of all, this is a handler, so it will be invoked when the result is ready. This result is an example of asyncresult<sqlconnection>. AsyncResult is a structure provided by vert.x that can be used to know that the operation of the connection database was successful or failed. If it succeeds, it will provide a result, where the result is an SqlConnection instance.

When you receive an instance of a asyncresult, the code is usually:

if (ar.failed ()) {
System.err.println ("The operation has failed ...:"
+ ar.cause (). GetMessage ());
else {
//use of: Result
= Ar.result ();
}

You need to get to the SqlConnection and then start the rest application. Because it becomes asynchronous, this requires changing the way the application is started. Therefore, if you divide the start sequence into multiple blocks:

Startbackend ((
connection)-> Createsomedata (Connection,
(Nothing)-> Startwebapp (
(HTTP)-> Completestartup (HTTP, fut)
), fut
), fut);

Startbackend-Gets the SqlConnection object, and then calls the next
createsomedata-initializes the database and inserts the data. When finished, call next
startwebapp-Launch Web application
completestartup-finally complete the boot
Fut is passed in by Vert.x and notifies you of problems that have been encountered during startup or startup.

Startbackend Method:

private void Startbackend (handler<asyncresult<sqlconnection>> Next, future<void> fut) {
Jdbc.getconnection (ar-> {
if (ar.failed ()) {
fut.fail (Ar.cause ());
} else {
next.handle ( Future.succeededfuture (Ar.result ()));
}

This method gets a SqlConnection object that checks whether the operation is complete. If successful, the next step is called. Fails, an error is reported. Other methods follow the same pattern:

Check if the previous operation was successful

Working with business logic

Call Next

Sql

The

Client is ready to write SQL now. Starting with the Createsomedata method, this method is also part of the startup order:

 private void Createsomedata (asyncresult<sqlconnection> result, handler< Asyncresult<void>> Next, future<void> fut) {if (result.failed ()) {Fut.fail (Result.cause ());} else {SQLC
onnection connection = Result.result (); Connection.Execute ("CREATE TABLE IF not EXISTS whisky (id INTEGER IDENTITY, name varchar (MB)," + "origin varchar (100))" , AR-> {if (ar.failed ()) {Fut.fail (Ar.cause ()) connection.close (); return;} connection.query ("SELECT * from Whisky
", select-> {if (select.failed ()) {Fut.fail (Ar.cause ());
Connection.close ();
Return
} if (Select.result (). Getnumrows () = 0) {Insert (New whisky ("Bowmore Years Laimrig", "Scotland, Islay"), connection, (v)-> insert (New whisky ("Talisker 57°north", "Scotland, Island"), connection, (R)-> {Next.handle (future.<void
>succeededfuture ());
Connection.close (); 
}));
else {Next.handle (future.<void>succeededfuture ()); Connection.close ();}});
}); }
}

This method checks to see if SqlConnection is available, and then executes some SQL statements. First, create a table if the table does not exist. Look at the following code:

Connection.Execute (
SQL statement handler called when the statement has been
)

Handler receive Asyncresult<void>, for example: only notifications, no actual return results.

Close connection

When the operation is complete, do not forget to close the SQL link. This connection is put into the connection pool and can be reused.

In this handler code, check that the statement is properly executed, and if it is correct, we then check whether the table contains data, and if not, insert the data using the Insert method:

private void Insert (Whisky whisky, SqlConnection connection, handler<asyncresult<whisky>> next) {
String sql = "INSERT into whisky (name, origin) VALUES?,?";
Connection.updatewithparams (SQL,
new Jsonarray (). Add (Whisky.getname ()). Add (Whisky.getorigin ()),
(AR)- > {
if (ar.failed ()) {Next.handle (Future.failedfuture ()) (
ar.cause ());
return;
}
Updateresult result = Ar.result ();
Build a new whisky instance with the generated ID.
Whisky w = new Whisky (Result.getkeys (). Getinteger (0), Whisky.getname (), Whisky.getorigin ());
Next.handle (Future.succeededfuture (w));}
);

This method uses the Upatewithparams method with the Insert (INSERT) statement (Declaration), and the value is passed in. This method avoids SQL injection. Once statement is executed (when the database is created without this data), a new whisky object is created and the ID is automatically generated.

Rest with Database (SQL)

The above methods are all part of the boot sequence. But what about the way to invoke the rest API? Take the GetAll method as an example. This method is called by the Web application Front-End and retrieves all the stored products:

private void GetAll (Routingcontext routingcontext) {
jdbc.getconnection (ar-> {SqlConnection
= Ar.result ();
Connection.query (' select * from whisky ', result-> {
list<whisky> whiskies = Result.result (). GetRows (). Stream (). Map (whisky::new). Collect (Collectors.tolist ());
Routingcontext.response ()
. Putheader ("Content-type", "Application/json; Charset=utf-8 ")
. End (json.encodeprettily (whiskies));
Connection.close (); Close the Connection 
});
}

This method obtains a SqlConnection object and then issues a query. Once you get the result of the query, it writes the HTTP response like the previous method. The GetOne, Deleteone, Updateone and AddOne methods are the same. Note that after response, you need to turn off the SQL connection.

Look at the results provided by the handler passed into the query method. Gets a resultset that contains the results of the query. Each row is a jsonobject, so if you have a data object that uses Jsonobject as the only parameter, then creating this object is simple.

Test

Need a little update to test the program to increase the configuration jdbcclient. In the Myfirstverticletest class, modify the Deploymentoption object created in the Setup method to:

Deploymentoptions options = new Deploymentoptions ()
. Setconfig (New Jsonobject ()
. put ("Http.port", port)
. put ("url", "Jdbc:hsqldb:mem:test?shutdown=true")
. Put ("Driver_class", "Org.hsqldb.jdbcDriver")
);

In addition to Http.port, the JDBC URL and JDBC driver are configured. When you test, you are using a memory database. The same changes should be made in the Src/test/resources/my-it-config.json file.

{
"Http.port": ${http.port},
"url": "Jdbc:hsqldb:mem:it-test?shutdown=true",
"Driver_class": " Org.hsqldb.jdbcDriver "
}

The Src/main/conf/my-application-conf.json file also needs to be modified, not for testing, but for running the application:

{
"Http.port": 8082,
"url": "Jdbc:hsqldb:file:db/whiskies",
"Driver_class": "Org.hsqldb.jdbcDriver "
}

This JDBC URL here is somewhat different from the previous file because the database needs to be stored on the hard disk.

Show Time!

To start the build program:

MVN Clean Package

Without modifying the API (without changing the published Java file and Rest interface), the test should be run smoothly.

Start Application:

Java-jar target/my-first-app-1.0-snapshot-fat.jar-conf Src/main/conf/my-application-conf.json

Visit http://localhost:8082/assets/index.html, and then you can see that the application is using a database. This time, even if the application is restarted, the data is still there because the storage product is persisted to the hard drive.

Summarize

In this article, you know how to use the JDBC database in Vert.x, and there's not a lot of complicated stuff. It may be surprising to begin with this asynchronous development model, but once you start using it, it's hard to go back.

Next time, we'll see how the application uses MongoDB to replace Hsql.

Welcome to <a href= "http://quanke.name/" rel= "nofollow" ></a>

Exchange Group: 231419585

Reprint please indicate the source, thank you