Install MySQL And Create Database (Local & Remote Server)
How To Develop And Deploy Your Web App (Angular + Tomcat Server + MySQL) — Part 4
Part 1: Develop Frontend Page With Angular
Part 2: Install Tomcat On Server (Mac & Linux)
Part 3: Develop Java API Running On Tomcat Server
Part 4: Install MySQL And Create Database (Local & Remote Server)
Part 5: Connect To MySQL Database in Java API and Deploy It On Tomcat Server (Local Server & Remote Server)
Install MySQL And Create Database (Local & Remote Server)
Install MySQL On Server
When you finish the previous code, I will introduce how to connect to MySQL database in this program. Before we start, we need to install MySQL first.
Since I’ve already installed on both local and remote server long time ago without capturing pictures and I forgot the detailed steps, please refer to the following tutorials:
Notes: I recommend to use brew install command to install MySQL on Mac.
To check whether you’ve installed successfully:
// Mac (my local server)
$ mysql -u root// Linux (my remote server)
$ mysql
Notes: There’s an error when I use mysql. I guess it’s related to the MySQL password config, but not sure. Will solve that problem later. If someone knows why, feel free to share your ideas with me. Thanks! 😁
Create Database
In this part, I’m gonna use MySQL Workbench on my local computer to manage the local & remote database because it has fewer commands and more user friendly. If you don’t have MySQL Workbench, please download here.
Double click the DMG file to open and follow the instructions to install.
Connect Local/Remote Database
Connect to local databse and create a new schema with MySQL Workbench. If you don’t know how to use MySQL Workbench, follow instructions here.
Once you successfully connect to MySQL database on your remote server, you can create/insert data into database. This is the database I create:
In the following steps, I will show you how to deploy and run this Java API program on server. You will see how we deploy on a local server, then on a remote one.
Connect MySQL Database in Java API and Deploy It On Tomcat Server (Local Server)
Key Code In Java API Program
Create db package and a DBConnect.java class under db package.
Modify DBConnect.java class:
package db;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;public class DBConnect {
public String test = "";public void ConnectDB() {
String sql = "select * from student"; // SQL query
Connection conn = null; // connect to db
Statement st = null; // database statement
ResultSet rs = null; // database resulttry {
// register jdbc Driver
Class.forName("com.mysql.cj.jdbc.Driver");conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/tomcat_test", "root", "");
// Create statement object
st = conn.createStatement();
rs = st.executeQuery(sql);
// Iterate the ResultSet
while (rs.next()) {
test = rs.getString("student_name") + " " + rs.getString("student_gender");
}} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
} finally {
try {
rs.close();
} catch (Exception e2) {
// TODO: handle exception
}try {
st.close();
} catch (Exception e3) {
// TODO: handle exception
}try {
conn.close();
} catch (Exception e4) {
// TODO: handle exception
}
}
}
}
Modify sayHtmlHello() function and display the data from dbConnect:
Add jdbc Driver dependency in pom.xml file. Make sure to click the maven button to load dependency.
Deploy On Local Server
Open Project Structure settings and config the Tomcat file.
Select all maven libraries and Put into /WEB-INF/lib, then click OK.
Notes: If the war file doesn’t include these libraries, it may have 500 error when connecting database.
Finish the config, then Build Artifacts.
When it’s done, there’s out directory.
Start the program by clicking Run.
Notes: Make sure you’ve stop Tomcat using shutdown.sh command mentioned above on your local server. Otherwise, the program can not start correctly.
Provide API For Angular To Request JSON Data
Create A Student Class
Notes: If you want to access Student data directly in DBConnect class, you need to set variables as public or create some get methods. Otherwise, Tomcat will prompt error.
Solve CORS Problem
Create CORSFilter class to handle CORS.
Add dependency in pom.xml, and press maven button to reload dependency.
Add class as <param-value> in web.xml.
Build Artifacts and Run.
Test your api request in Postman:
Once it returns the data, we can work on next big step — Using this request to display data on frontend.
Request Data In Angular
Go back to our AngularWeb program introduced in Part 1. Create API service in Angular:
// Api is the service name
$ ng generate service ApiModify app.module.ts file:
Modify api.service.ts file:
Call API in the app.component.ts file:
Save and Run ng service --open command in terminal:
In this tutorial, I just show how to request data through API. And next time, I will show how to display data on the HTML page.
More…
The following parts could be found in my other articles.
- Part 1: Develop First Web Page With Angular
- Part 2: Install Tomcat On Server (Mac & Linux Server)
- Part 3: Develop Java API Program Running On Tomcat Server
- Part 4: Install MySQL And Create Database (Local & Remote Server)
- Part 5: Connect To MySQL Database in Java API and Deploy It On Tomcat Server (Local Server & Remote Server)
