一、部署MySQL-Connector-Java使用jsp连接 MySQL

1、下载MySQL-Connector-Java：

MySQL-Connector-Java：
http://dev.mysql.com/downloads/connector/ #官网下载网站
选择Connector/j 跳转下载页面
image.png
选择“Paltform Independent”，选择tar包后面的“Download”
image.png
选择不用注册，“No thanks，just start my download”
image.png
mysql-connector-java-8.0.13下载地址：
https://cdn.mysql.com//Downloads/Connector-J/mysql-connector-java-8.0.13.tar.gz
官方信息：MySQL Connector/J8.0强烈建议与MySQLServer8.0、5.7、5.6和5.5一起使用。请升级到MySQL连接器/J8.0。

2、安装mysql

[root@tomcat142 ~]# yum install mariadb mariadb-server -y
[root@tomcat142 ~]# systemctl start mariadb

二、测试数据

1、测试jsp链接MySQL

Jsp链接mysql，官方提供了工具：安装mysql-connector

  1. [root@tomcat142 ~]# mysql
  2. mysql> create database tomcat;        #测试数据库，为了和后面方便测试，这里创建tmyomcat
  3. mysql> use tomcat 
  4. mysql> create table tt(id int,name varchar(128));                #创建测试表
  5. mysql> insert into tt values (1,"come on boy"),(2,"come on girl");    #创建测试数据
  6. mysql> create user 'tomcat'@'%' identified by 'tomcat';
  7. mysql> grant all privileges on tomcat.* to 'tomcat'@'%';
  8. mysql> grant all on tomcat.* to tomcat@'%' identified by 'tomcat';            #授权用户
  9. mysql> \q
  10. [root@tomcat142 ~]# mysql -utomcat -ptomcat         #测试tomcat可以登陆（OK）
  11. MariaDB [(none)]> \q
  12. create database tomcat;

2、安装mysql-connector

[root@tomcat142 ~]# tar xf mysql-connector-java-8.0.13.tar.gz -C /usr/local/src/
[root@xuegod63 ~]# cp /usr/local/src/mysql-connector-java-8.0.13/mysql-connector-java-8.0.13.jar /usr/local/tomcat/lib/        #只需要复制到tomcat的lib目录下，重启tomcat就可以生效
[root@tomcat142 ~]# service tomcat stop; service tomcat start

3、测试

[root@tomcat142 ~]# vim /usr/local/tomcat/webapps/ROOT/mysql.jsp #建立测试页面

<%@ page contentType="text/html;charset=utf-8"%>
<%@ page import="java.sql.*"%>
<html>
<body>
<%
Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
String url ="jdbc:mysql://localhost/tomcat?user=tomcat&password=tomcat&useUnicode=true&characterEncoding=utf-8";
Connection conn= DriverManager.getConnection(url);
Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
String sql="select * from tt";
ResultSet rs=stmt.executeQuery(sql);
while(rs.next()){%>
step:<%=rs.getString(1)%>
context:<%=rs.getString(2)%><br><br>
<%}%>
<%out.print("Congratulations!!! JSP connect MYSQL IS OK!!");%>
<%rs.close();
stmt.close();
conn.close();
%>
</body>
</html>

image.png
#代码复制注意核对，由于编码问题，复制到vim中可能会增加多余的%号，记得删除
浏览器访问：http://192.168.8.142:8080/mysql.jsp