Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog PolarDB-X Practice Series – Part 2: Use PolarDB-X to Develop Applications

PolarDB-X Practice Series – Part 2: Use PolarDB-X to Develop Applications

This section of the PolarDB-X Practice Series describes how PolarDB-X works with Spring Boot and WordPress to develop applications, as well as best pr.

Based on the installed and deployed PolarDB-X, this article describes how PolarDB-X works with Spring Boot and WordPress to develop applications, as well as best practices for PolarDB-X connection pools and transparent distribution.

Description

The experimental operations in this section are mainly performed using the Alibaba Cloud Lab on the Alibaba Cloud official website.

PolarDB-X Practice Series – Part 1: How to Deploy Open-Source PolarDB-X: https://www.alibabacloud.com/blog/polardb-x-practice-series-part-1-how-to-deploy-open-source-polardb-x_599590

Install PolarDB-X and JDK

Step 1: Install PolarDB-X

Procedure:

1.  Install and start Docker

i.  Run the following command to install Docker:

curl -fsSL https://get.docker.com | bash -s docker --mirror Aliyun

ii.  Run the following command to start Docker:

systemctl start docker

2.  Run the following command to install PolarDB-X:

docker run -d --name some-polardb-x -p 8527:8527 polardbx/polardb-x:2.1.0

Step 2: Log in to PolarDB-X

PolarDB-X allows you to connect using the MySQL command-line client, third-party clients, and third-party program code that complies with the MySQL interaction protocol. This section describes how to connect to the PolarDB-X database using the MySQL command-line client.

Procedure:

1.  Run the following command to install MySQL:

yum install mysql -y

2.  Run the following command to check the version of MySQL:

mysql -V

The following result is returned, indicating you have successfully installed MySQL:

1

3.  Run the following command to log in to the PolarDB-X database:

mysql -h127.0.0.1 -P8527 -upolardbx_root -p123456

Description:

  • The username and password of the PolarDB-X database in this experiment are preset. You can use the preceding command to log in to the database.
  • If you encounter an error:
mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

Please wait a minute and execute the login command again.

The following result is returned, indicating you have successfully logged in to the PolarDB-X database.

2

4.  Enter exit to exit the database:

3

Step 3: Install JDK

Procedure:

1.  Run the following command to install JDK 1.8 using yum:

yum -y install java-1.8.0-openjdk*

2.  Run the following command to check whether the JDK is successfully installed:

java -version

The following result is returned, indicating you have successfully installed JDK 1.8.

4

Experience Spring Boot + PolarDB-X to Develop Applications

This section describes how to download and edit the Spring Boot sample project and connect it to the PolarDB-X database.

Prerequisites

Make sure that the PolarDB-X and JDK are installed. Please see the Install PolarDB-X and JDK section above for details.

Procedure

Description:

Please see Spring Boot Sample Tutorial for more information about Spring Boot.

1.  Run the following command to install Git:

yum -y install git

2.  Download the Spring Boot sample project:

i.  Run the following command to download the Spring Boot sample project:

git clone https://github.com/spring-guides/gs-accessing-data-mysql.git

ii.  Run the following command to go to the initial directory:

cd gs-accessing-data-mysql/initial
git checkout b8408e3a1e05008811d542b706107d45160556ac

iii.  Run the following command to view the sample code:

ls

5

3.  Create a database:

i.  Run the following command to log in to the PolarDB-X database:

mysql -h127.0.0.1 -P8527 -upolardbx_root -p123456

ii.  Run the following SQL statement to create the database, db_example:

create database db_example;

iii.  Run the following SQL statement to create the user, springuser:

create user 'springuser'@'%' identified by 'ThePassword';

iv.  Run the following SQL statement to grant permissions to the springuser:

grant all on db_example.* to 'springuser'@'%';

v.  Exit the database:

exit

4.  Configure the application.properties file and connect the database to the Spring Boot sample project.

i.  Run the following command to open the application.properties configuration file:

vim src/main/resources/application.properties

ii.  Press i to enter the edit mode, find the parameter spring.datasource.url, and change the port number of the parameter value to 8527:

spring.datasource.url=jdbc:mysql://${MYSQL_HOST:localhost}:8527/db_example

iii.  The following figure shows the content of the modified file. After you press Esc, enter :wq, and press Enter to save and exit.

6

5.  Create the Entity Model.

i.  Run the following command to create a User class:

vim src/main/java/com/example/accessingdatamysql/User.java

ii.  Copy and paste the following code into the User class:

package com.example.accessingdatamysql;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
@Entity // This tells Hibernate to make a table out of this class
public class User {
  @Id
  @GeneratedValue(strategy=GenerationType.AUTO)
  private Integer id;
  private String name;
  private String email;
  public Integer getId() {
    return id;
  }
  public void setId(Integer id) {
    this.id = id;
  }
  public String getName() {
    return name;
  }
  public void setName(String name) {
    this.name = name;
  }
  public String getEmail() {
    return email;
  }
  public void setEmail(String email) {
    this.email = email;
  }
}

iii.  The following figure shows the content of the modified file. After you press Esc, enter :wq, and press Enter to save and exit.

7

6.  Create a repository and save user records

i.  Run the following command to create a UserRepository class:

vim src/main/java/com/example/accessingdatamysql/UserRepository.java

ii.  Copy and paste the following code into the UserRepository class

package com.example.accessingdatamysql;
import org.springframework.data.repository.CrudRepository;
import com.example.accessingdatamysql.User;
// This will be AUTO IMPLEMENTED by Spring into a Bean called userRepository
// CRUD refers Create, Read, Update, Delete
public interface UserRepository extends CrudRepository<User, Integer> {
}

iii.  The following figure shows the content of the modified file. After you press Esc, enter :wq, and press Enter to save and exit.

8

7.  Create a Controller class to handle HTTP requests to applications

i.  Run the following command to create a MainController class:

vim src/main/java/com/example/accessingdatamysql/MainController.java

ii.  Copy and paste the following code into the MainController class:

package com.example.accessingdatamysql;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
@Controller // This means that this class is a Controller
@RequestMapping(path="/demo") // This means URL's start with /demo (after Application path)
public class MainController {
  @Autowired // This means to get the bean called userRepository
         // Which is auto-generated by Spring, we will use it to handle the data
  private UserRepository userRepository;
  @PostMapping(path="/add") // Map ONLY POST Requests
  public @ResponseBody String addNewUser (@RequestParam String name
      , @RequestParam String email) {
    // @ResponseBody means the returned String is the response, not a view name
    // @RequestParam means it is a parameter from the GET or POST request
    User n = new User();
    n.setName(name);
    n.setEmail(email);
    userRepository.save(n);
    return "Saved";
  }
  @GetMapping(path="/all")
  public @ResponseBody Iterable<User> getAllUsers() {
    // This returns a JSON or XML with the users
    return userRepository.findAll();
  }
}

iii.  The following figure shows the content of the modified file. After you press Esc, enter :wq, and press Enter to save and exit.

9

8.  (Optional) Create an application

Description:

AccessingDataMysqlApplication class has been created in the Spring Boot sample project. You can skip this step.

i.  Run the following command to create a AccessingDataMysqlApplication class.

vim src/main/java/com/example/accessingdatamysql/AccessingDataMysqlApplication.java

ii.  Press i to enter the edit mode and copy and paste the following code into the User class:

package com.example.accessingdatamysql;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class AccessingDataMysqlApplication {
  public static void main(String[] args) {
    SpringApplication.run(AccessingDataMysqlApplication.class, args);
  }
}

iii.  The following figure shows the content of the modified file. After you press Esc, enter :wq, and press Enter to save and exit.

10

9.  Run the following command to run the Spring Boot sample project:

./gradlew bootRun

Please wait for about two minutes. The following result is returned, indicating the system is running successfully.

11

10.  Test

i.  On the experiment page, click the 12 icon in the upper-right corner to create a new terminal window.

13

ii.  In the new terminal window, run the following command to add a record:

curl localhost:8080/demo/add -d name=First -d email=someemail@someemailprovider.com

The following result is returned, indicating you successfully added a record.

14

iii.  Run the following command to query records:

curl 'localhost:8080/demo/all'

The following result is returned. You can query the information of the newly added record.

15

iv.  Run the following command to log in to the PolarDB-X database:

mysql -h127.0.0.1 -P8527 -upolardbx_root -p123456

v.  Run the following SQL statement to use the database:

use db_example;

vi.  Run the following SQL statement to query the user table:

select * from user;

The following result is returned. You can query the newly added record in the user table.

16

vii.  Exit the database:

Exit

Experience WordPress + PolarDB-X to Deploy Blog Site

This section describes how to build a blog site using WordPress Docker image and PolarDB-X.

Prerequisites

Make sure that you have installed PolarDB-X. Please see Install PolarDB-X and Log in to PolarDB-X above for more information.

Procedure

Description:

WordPress provides the Docker image for quick installation. Please see Docker Hub for more information.

1.  Run the following command to install WordPress:

docker run --name some-wordpress -p 9090:80 -d wordpress

2.  Create a WordPress database:

i.  Run the following command to log in to the PolarDB-X database:

mysql -h127.0.0.1 -P8527 -upolardbx_root -p123456

ii.  Run the following SQL statement to create a database, WordPress:

create database wordpress;

iii.  Exit the database:

exit

3.  Configure WordPress:

i.  In your local browser, open a new tab and visit http:// IP of ECS>:9090.

Description:

You need to replace the with the Elastic IP of ECS in the cloud product resource list.

17

ii.  On the initialization page, select Simplified Chinese and click Continue:

18

iii.  On the prepare page, click Start Now:

19

iv.  On the database configuration page, configure the database according to the description and click Submit.

Database Name
The default database name is wordpress.

Username
Enter polardbx_root

Password
Enter 123456

Database Host
Enter :8527. You need to replace the with the Elastic IP of ECS in the cloud product resource list.

Table Prefix
The default table prefix is wp_.

20

v.  On the database configuration complete page, click Run Installer:

21

vi.  On the information configuration page, configure related information according to the description and click Install WordPress.

Site Title
Enter a site title (such as myblog)

Username
Enter a username (such as admin)

Password
Enter the password

Your Email Address
Enter your email address. You are advised to use a valid email address.

If you do not have an email address, enter a virtual one, such as admin@admin.com, but you will not be able to receive information.

22

vii.  On the success page, click Login:

23

viii.  On the login page, enter your username and password and then click Login:

24

Docker WordPress Database POLARDB Tutorials Spring Boot JDK PolarDB-X Alibaba Cloud Lab
0 1 0
Share on

Read previous post:

[Infographic] Highlights | Database New Feature in January 2023

Read next post:

Performance Optimization of RDS AliSQL for Binlog – Extreme IO Optimization

ApsaraDB

376 posts | 57 followers

You may also like

Comments

ApsaraDB

376 posts | 57 followers

Related Products

More Posts by ApsaraDB

See All