900字范文 > 如何使用Java与Mysql进行数据交互

如何使用Java与Mysql进行数据交互

时间：2019-05-30 15:08:07

ArthurSlog

SLog-5

Year·1

Guangzhou·China

July 11th

微信扫描二维码，关注我的公众号

做到每个敲下的按键、每次鼠标的点击、每次手机屏幕的触碰，都能产生价值

开发环境MacOS(High Sierra 10.13.5)

Java为了开发应用级的程序，整个开发链比较繁琐复杂，所以会需要更多的时间来掌握。

老规则，让我们明确一下需要的信息Mysql官方手册

打开命令行，测试一下java，如果提示没有安装JDK，移步JavaJDK下载

安装完JDK，测试一下安装是否完整

java -version

java version "10.0.1" -04-17Java(TM) SE Runtime Environment 18.3 (build 10.0.1+10)Java HotSpot(TM) 64-Bit Server VM 18.3 (build 10.0.1+10, mixed mode)复制代码

javac -version

javac 10.0.1复制代码

在macOS中，JDK安装路径是/Library/Java/JavaVirtualMachines/jdk-10.jdk/Contents/Home在macOS中，JRE安装路径是/Library/Internet Plug-Ins/JavaAppletPlugin.plugin/Contents/Home

下载Connector / J，选择platform Independent，选择Platform Independent (Architecture Independent), ZIP Archive下载

下载好Connector / J后解压文件得到文件夹，配置 CLASSPATH

export CLASSPATH=~/Downloads/mysql-connector-java-8.0.11.jar:$CLASSPATH

下面是完整代码

LoadDriver.java

import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.Statement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;// assume that conn is an already created JDBC connection (see previous examples)public class LoadDriver {public static void main(String[] args) {try {// The newInstance() call is a work around for some// broken Java implementationsClass.forName("com.mysql.cj.jdbc.Driver").newInstance();Connection conn = null;try {conn =DriverManager.getConnection("jdbc:mysql://localhost/my_db?" +"user=root&password=88888888");// Do something with the ConnectionStatement stmt = null;ResultSet rs = null;try {stmt = conn.createStatement();rs = stmt.executeQuery("SELECT * FROM Account");// or alternatively, if you don't know ahead of time that// the query will be a SELECT...if (stmt.execute("SELECT * FROM Account")) {rs = stmt.getResultSet();}// Now do something with the ResultSet ....ResultSetMetaData rsmd = rs.getMetaData();int columnsNumber = rsmd.getColumnCount();while (rs.next()) {for (int i = 1; i <= columnsNumber; i++) {if (i > 1) System.out.print(", ");String columnValue = rs.getString(i);System.out.print(columnValue + " " + rsmd.getColumnName(i));}System.out.println("");}}catch (SQLException ex){// handle any errorsSystem.out.println("SQLException: " + ex.getMessage());System.out.println("SQLState: " + ex.getSQLState());System.out.println("VendorError: " + ex.getErrorCode());}finally {// it is a good idea to release// resources in a finally{} block// in reverse-order of their creation// if they are no-longer neededif (rs != null) {try {rs.close();} catch (SQLException sqlEx) { } // ignorers = null;}if (stmt != null) {try {stmt.close();} catch (SQLException sqlEx) { } // ignorestmt = null;}}} catch (SQLException ex) {// handle any errorsSystem.out.println("SQLException: " + ex.getMessage());System.out.println("SQLState: " + ex.getSQLState());System.out.println("VendorError: " + ex.getErrorCode());}} catch (Exception ex) {// handle the errorSystem.out.println("Exception: " + ex.getMessage());}}}复制代码

执行流程如下：

用java应用程序注册你的mysql jdbc驱动程序，参考Mysql/JDBC执行SQL手册

Class.forName("com.mysql.cj.jdbc.Driver").newInstance()复制代码

连接数据库

conn =DriverManager.getConnection("jdbc:mysql://localhost/my_db?" + "user=root&password=88888888")复制代码

获得连接对象，并执行SQL指令

Statement stmt = null;ResultSet rs = null;stmt = conn.createStatement();rs = stmt.executeQuery("SELECT * FROM Account");复制代码

获得SQL执行结果，对结果进行打印

ResultSetMetaData rsmd = rs.getMetaData();int columnsNumber = rsmd.getColumnCount();while (rs.next()) {for (int i = 1; i <= columnsNumber; i++) {if (i > 1) System.out.print(", ");String columnValue = rs.getString(i);System.out.print(columnValue + " " + rsmd.getColumnName(i));}System.out.println("");}复制代码

打印SQL结果

ResultSet resultSet = statement.executeQuery("SELECT * from foo");ResultSetMetaData rsmd = resultSet.getMetaData();int columnsNumber = rsmd.getColumnCount();while (resultSet.next()) {for (int i = 1; i <= columnsNumber; i++) {if (i > 1) System.out.print(", ");String columnValue = resultSet.getString(i);System.out.print(columnValue + " " + rsmd.getColumnName(i));}System.out.println("");}复制代码

切换到LoadDriver.java文件路径下，编译LoadDriver.java文件