CentOS7下JSP连接Mysql-白红宇

CentOS7下JSP连接Mysql

阅读量：5036 次

发布时间：2019-06-12

本文共 4302 字，大约阅读时间需要 14 分钟。

　　实验环境如下：

youxi1　　CentOS7.5　　Tomcat8.5.35　　192.168.5.101

youxi2　　CentOS7.5　　Mysql5.7.20　　192.168.5.102

(1).下载Mysql官方提供的连接包

　　网址：，选择Platform Independent（平台独立，就是与平台无关）。我这里下载的是mysql-connector-java-8.0.13.tar.gz。

　　下载完成后上传到Tomcat服务器上，我这里是youix1。

(2).在mysql服务器上创建测试数据，并授权

[root@youxi2 ~]# mysql -uroot -p123456mysql> create database test_db; Query OK, 1 row affected (0.00 sec)mysql> use test_db;Database changedmysql> create table java_tb(id int,name varchar(20));Query OK, 0 rows affected (0.02 sec)mysql> insert into java_tb values(1,'zhangsan'),(2,'lisi');Query OK, 2 rows affected (0.06 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> select * from java_tb;+------+----------+| id   | name     |+------+----------+|    1 | zhangsan ||    2 | lisi     |+------+----------+2 rows in set (0.00 sec)mysql> grant all on test_db.* to 'tomcat_user'@'192.168.5.101' identified by 'tomcatpassword';Query OK, 0 rows affected, 1 warning (0.02 sec)mysql> flush privileges;　　//刷新Query OK, 0 rows affected (0.00 sec)

(3).解压文件，将jar包复制到tomcat安装目录下的lib文件夹中，最后重启tomcat

[root@youxi1 ~]# tar zxf mysql-connector-java-8.0.13.tar.gz -C /usr/local/src/　　//解压 [root@youxi1 ~]# cp /usr/local/src/mysql-connector-java-8.0.13/mysql-connector-java-8.0.13.jar /usr/local/tomcat8.5/lib/　　//复制jar包[root@youxi1 ~]# ps aux | grep /usr/local/tomcat8.5/root 2559 0.2 10.0 2325180 99932 ? Sl 12:32 0:21 /usr/local/jdk1.8.0_191/bin/java -Djava.util.logging.config.file=/usr/local/tomcat8.5/conf/logging.properties  -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager -Djdk.tls.ephemeralDHKeySize=2048 -Djava.protocol.handler.pkgs=org.apache.catalina.webresources  -Dorg.apache.catalina.security.SecurityListener.UMASK=0027 -Dignore.endorsed.dirs= -classpath /usr/local/tomcat8.5/bin/bootstrap.jar:/usr/local/tomcat8.5/bin/tomcat-juli.jar  -Dcatalina.base=/usr/local/tomcat8.5 -Dcatalina.home=/usr/local/tomcat8.5 -Djava.io.tmpdir=/usr/local/tomcat8.5/temp org.apache.catalina.startup.Bootstrap startroot 2982 0.0 0.0 112720 992 pts/0 S+ 14:33 0:00 grep --color=auto /usr/local/tomcat8.5/[root@youxi1 ~]# kill -9 2559　　//杀死tomcat[root@youxi1 ~]# !ps　　//调用最近一次ps开头的命令ps aux | grep /usr/local/tomcat8.5/　　//显示完整命令root 2985 0.0 0.0 112720 992 pts/0 R+ 14:33 0:00 grep --color=auto /usr/local/tomcat8.5/[root@youxi1 ~]# /usr/local/tomcat8.5/bin/startup.shUsing CATALINA_BASE: /usr/local/tomcat8.5Using CATALINA_HOME: /usr/local/tomcat8.5Using CATALINA_TMPDIR: /usr/local/tomcat8.5/tempUsing JRE_HOME: /usr/local/jdk1.8.0_191Using CLASSPATH: /usr/local/tomcat8.5/bin/bootstrap.jar:/usr/local/tomcat8.5/bin/tomcat-juli.jarTomcat started.[root@youxi1 ~]# !psps aux | grep /usr/local/tomcat8.5/root 2995 109 6.1 2263640 60904 pts/0 Sl 14:33 0:03 /usr/local/jdk1.8.0_191/bin/java -Djava.util.logging.config.file=/usr/local/tomcat8.5/conf/logging.properties  -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager -Djdk.tls.ephemeralDHKeySize=2048 -Djava.protocol.handler.pkgs=org.apache.catalina.webresources  -Dorg.apache.catalina.security.SecurityListener.UMASK=0027 -Dignore.endorsed.dirs= -classpath /usr/local/tomcat8.5/bin/bootstrap.jar:/usr/local/tomcat8.5/bin/tomcat-juli.jar  -Dcatalina.base=/usr/local/tomcat8.5 -Dcatalina.home=/usr/local/tomcat8.5 -Djava.io.tmpdir=/usr/local/tomcat8.5/temp org.apache.catalina.startup.Bootstrap startroot 3015 0.0 0.0 112720 992 pts/0 R+ 14:33 0:00 grep --color=auto /usr/local/tomcat8.5/

(4).创建测试网页

[root@youxi1 ~]# vim /usr/local/tomcat8.5/webapps/ROOT/mysql.jsp<%@ page import="java.sql.*"%><%Class.forName("com.mysql.cj.jdbc.Driver").newInstance(); //url包含了Mysql的IP地址(192.168.5.102)，端口号(3306)，数据库(test_db)，连接用户名和密码(tomcat_user和tomcatpassword)，使用的字符集(utf-8)String url = "jdbc:mysql://192.168.5.102:3306/test_db?user=tomcat_user&password=tomcatpassword&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 java_tb";ResultSet rs=stmt.executeQuery(sql);while(rs.next()){%>id:<%=rs.getString(1)%>name:<%=rs.getString(2)%>
<%}%><% out.print("Congratulations!!! JSP connect MYSQL IS OK!!"); %><%rs.close();stmt.close();conn.close();%>

　　字符集需要和数据库端相同。

　　在Windows上查看