Troubleshooting Java Connection to MySQL

本讲座将解释从Java连接到MySQL工作所需的步骤,并强调可能遇到的潜在问题。它将涵盖所有组件、安装和配置。
About the Author
Rodrigo Trindade
Master in Computer Science by the Rio Grande do Sul Federal University (Brazil). Over 10 years experience as CS Professor. Started as Software Developer then moved to Support Engineering working for Netscape, Sun Microsystems and Oracle. Joined Percona in 2018 as a Service Delivery Manager. Solaris, Java and Weblogic certified.

展开查看详情

1. Troubleshooting Java Connection to MySQL Rodrigo Trindade Service Delivery Manager 1 © 2019 Percona

2.Talk Topics Components Installation (Everything you need) (baby-steps) Configuration Troubleshooting (step-by-step) (what can go wrong) 2 2 © 2019 Percona

3.Timeline History Universities - Companies: • Certifications • Brazil 2 USA 3 3 © 2019 Percona

4. Components Everything you need to get Java connection to MySQL 4 © 2019 Percona

5.Components JRE and JDK Java Source Code Connection Connector/J MySQL Database 5 5 © 2019 Percona

6.OS Sandbox .java .class JRE & JDK MySQL DB Connector .jar 6 6 © 2019 Percona

7. Installation Detailed steps for Components Installation 7 © 2019 Percona

8.Installation 1- Install CentOS VMs https://www.virtualbox.org/ https://www.vagrantup.com/downloads.html 8 8 © 2019 Percona

9.Vagrant file ```# -*- mode: ruby -*- # vi: set ft=ruby : Vagrant.configure(2) do |config| config.vm.define "node-1" do |node| node.vm.box = "centos/7" node.vm.host_name = "node1" node.vm.network "private_network", ip: "192.168.33.10" end end Vagrant.configure(2) do |config| config.vm.define "jvm-1" do |node| node.vm.box = "centos/7" node.vm.host_name = "jvm1" node.vm.network "private_network", ip: "192.168.33.11" end end``` 9 9 © 2019 Percona

10.VMs Installation $ vagrant up Bringing machine 'node-1' up with 'virtualbox' provider... ==> node-1: Checking if box 'centos/7' is up to date... (...) ==> node-1: Booting VM... ==> node-1: Waiting for machine to boot. This may take a few minutes... (...) ==> node-1: Machine booted and ready! (...) ==> node-1: Setting hostname... ==> node-1: Configuring and enabling network interfaces... node-1: SSH address: 127.0.0.1:2222 node-1: SSH username: vagrant node-1: SSH auth method: private key 1 10 © 2019 Percona 0

11.VMs Installation Bringing machine 'jvm-1' up with 'virtualbox' provider... ==> jvm-1: Importing base box 'centos/7'... ==> jvm-1: Matching MAC address for NAT networking... ==> jvm-1: Checking if box 'centos/7' is up to date... ==> jvm-1: Setting the name of the VM: CentOS7_jvm_1542309057193_62910 (...) ==> jvm-1: Booting VM... ==> jvm-1: Waiting for machine to boot. This may take a few minutes... (...) ==> jvm-1: Machine booted and ready! (...) ==> jvm-1: Setting hostname... ==> jvm-1: Configuring and enabling network interfaces... jvm-1: SSH address: 127.0.0.1:2200 jvm-1: SSH username: vagrant jvm-1: SSH auth method: private key 1 11 © 2019 Percona 1

12.Installation 2- Install Percona Server following these steps. After booting VMs successfully, run the following to connect to node-1: $ vagrant ssh node-1 1 2 12 © 2019 Percona

13.Percona Server Installation [root@node1 ~]# yum install http://www.percona.com/downloads/percona- release/redhat/0.1-6/percona-release-0.1-6.noarch.rpm Loaded plugins: fastestmirror (...) Is this ok [y/d/N]: y Downloading packages: (...) Running transaction Installing : percona-release-0.1-6.noarch Verifying : percona-release-0.1-6.noarch Installed: percona-release.noarch 0:0.1-6 Complete! 1 3 13 © 2019 Percona

14.Percona Server Installation [root@node1 ~]# yum install Percona-Server-server-57 Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile (...) Resolving Dependencies --> Running transaction check ---> Package Percona-Server-server-57.x86_64 0:5.7.23-24.1.el7 will be installed (...) Installed: Percona-Server-server-57.x86_64 0:5.7.23-24.1.el7 Percona-Server-shared-compat-57.x86_64 0:5.7.23-24.1.el7 (...) Complete! 1 4 14 © 2019 Percona

15.Percona Server Installation [root@node1 ~]# systemctl start mysql [root@node1 ~]# ps -ef | grep mysqld mysql 23699 1 18 20:55 ? 00:00:01 /usr/sbin/mysqld -- daemonize --pid-file=/var/run/mysqld/mysqld.pid [root@node1 ~]# grep -i 'password' /var/log/mysqld.log 2018-11-15T20:55:36.680897Z 1 [Note] A temporary password is generated for root@localhost: ;s_ZcHwfR5r1 1 5 15 © 2019 Percona

16.Percona Server Installation [root@node1 ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.23-24 (...) mysql> set PASSWORD = 'Passw0rd!'; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye 1 6 16 © 2019 Percona

17.Installation 3- Install Java Open JDK: $ yum install java-1.8.0-openjdk-devel.x86_64 $ javac -version javac 1.8.0_191 1 7 17 © 2019 Percona

18.Installation 4 - Download mysql-connector.jar https://www.mysql.com/products/connector/ JDBC Driver for MySQL (Connector/J) 1 8 18 © 2019 Percona

19. Sandbox Installed (open source) node-1 .java .class jvm-1 JRE & JDK MySQL DB Connector .jar 1 19 © 2019 Percona 9

20.Sandbox Snapshot 2 0 20 © 2019 Percona

21. Configuration Details on Configuration of the Components 21 © 2019 Percona

22.Configuration Java Code $ javac $ java Connector JDK>/jre/lib/ext $ java -cp MySQL Populate DB Grant Privileges 2 22 © 2019 Percona 2

23.MySQL JDBC Connector JDBC provides a standard interface for interacting with any RDBMS. The API consists of the following 4 main components: 1. JDBC Driver 2. Connection 3. Statement 4. ResultSet 2 23 © 2019 Percona 3

24.Java Source code Conn.java import java.sql.*; class Conn { private static String Driver = "com.mysql.jdbc.Driver"; private static String ConnectionURL = "jdbc:mysql://192.168.33.10:3306/plive"; private static String User = "root"; private static String Password = "Passw0rd!"; private static String SQL_Statement = "SELECT * FROM emp"; public static void main(String args[]){ try { Class.forName(Driver); Connection con=DriverManager.getConnection(ConnectionURL,User,Password); Statement stmt=con.createStatement(); ResultSet rs=stmt.executeQuery(SQL_Statement); while(rs.next()) System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3)); con.close(); } catch(Exception e){ System.out.println(e);} } } 2 24 © 2019 Percona 4

25.Java Source code 1. Class.forName(Driver); 2. Connection con=DriverManager.getConnection(ConnectionURL,User,Passw ord); 3. Statement stmt=con.createStatement(); 4. ResultSet rs=stmt.executeQuery(SQL_Statement); 2 25 © 2019 Percona 5

26.Java Source code Where does that come from??? Class.forName(Driver); Class.forName loads a class using Classloader, including running its static initializers. ● The goal is to allow instanciation of a Connection from a static string, potentially from a configuration file. ● Takes a Class or Interface as parameter. 2 26 © 2019 Percona 6

27.MySQL Sample Data Creation mysql> create database plive; mysql> use plive; Database changed mysql> create table emp(id int(10),name varchar(40),age int(3)); mysql> insert into emp(id,name,age) values (1,'Percona Live',10); mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | plive | | sys | +--------------------+ 6 rows in set (0.00 sec) 2 27 © 2019 Percona 7

28.Java MySQL Connection Recap Load Driver Create Statement and and Establish Connection Execute Query Close Connection Process ResultSet and Handle Exceptions 2 8 28 © 2019 Percona

29. Troubleshooting Potential issues that you may encounter 29 © 2019 Percona