1. db(mysql)
설치
apt update
apt install mysql-server -y접속, User생성, 권한 부여
my -u root -p
CREATE USER 'testuser'@'%' IDENTIFIED BY 'qwer1234';
# '%' 의 의미는 외부에서의 접근을 허용
GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'%;
# 권한을 모두 주었다. 그런다음에 testuser 로그인한 후에 확인해 보자
select user, host from mysql.user;
database, table 생성
creaete database test_db
show datab;ases;
use test_db;
# 사용할 database 변경
CREATE TABLE family (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
role VARCHAR(100));
INSERT INTO test_db.family (name, role)
VALUES ("봉미선", "엄마"), ("신형만", "아빠"), ("신짱구", "아들"), ("신짱아", "딸"), ("흰둥이", "강아지");
select * from family;
2. was(tomcat)
- jsp 파일을 만들어 확인
vi /var/lib/tomcat9/webapps/ROOT/test.jsp<%@ page language="java" contentType="text/html; charset=EUC-KR"
pageEncoding="EUC-KR"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>Insert title here</title>
</head>
<body>
<h1> Hello Universe !</h1>
</body>
</html>
jsp -> db 접속
vi /var/lib/tomcat9/webapps/ROOT/db.jsp<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8" import="java.sql.*"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>DB Connection Test</title>
</head>
<body>
<%
String DB_URL = "jdbc:mysql://172.16.200.7:3306/test_db";
String DB_USER = "testuser";
String DB_PASSWORD = "qwer1234";
Connection conn;
Statement stmt;
PreparedStatement ps;
ResultSet rs;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
stmt = conn.createStatement();
String query = "SELECT * FROM family";
ResultSet res = stmt.executeQuery(query);
out. println("<pre><h3>\t\tID\tNAME\tROLE</h3></pre>");
while(res.next()){
out.print("<pre><h3>\t\t" + res.getString(1)); // member.id
out.print("\t" + res.getString(2)); // member .name
out.print("\t" + res.getString(3) +"</h3></pre>"); // member.role
}
conn.close();
out.println("<br><br>");
out.println("MySQL JDBC Driver Connection Test Success!!!");
}
catch (Exception e) {
out.println(e.getMessage());
}
%>
</body>
</html>
- mysql에서 데이터를 가져오기 위해서는 .jar 파일이 필요
wget https://downloads.mysql.com/archives/get/p/3/file/mysql-connector-java-5.1.49.tar.gz
tar zxvf mysql-connector-java-5.1.49.tar.gz
cp mysql-connector-java-5.1.49/mysql-connector-java-5.1.49.jar /usr/share/tomcat9/lib/
# 사용하고자하는 경로로 이동
systemctl restart tomcat9
3. was(tomcat) - db(mysql)
was(tomcat)
- was에서 mysql에 접근
mysql -u testuser -h 172.16.200.7 -p
# db서버 IP
apt install mysql-client-core-8.0 -y
- db에서 접속을 오픈
db(mysql)
vi /etc/mysql/mysql.conf.d/mysqld.cnf
bind-address = 172.16.200.7 (자기자신)
mysqlx-bind-address = 172.16.200.7 (자기자신)
systemctl restart mysql.servicewas(tomcat)
mysql -u testuser -h 172.16.200.7 -p
4. 확인
참고 자료
싱숭생숭늉