[Mysql]Java轻松驾驭MySQL：从入门到实践，掌握数据库连接与操作技巧

引言MySQL 是一款流行的开源关系型数据库管理系统，广泛应用于各种类型的数据库应用中。Java 作为一种广泛使用的编程语言，与 MySQL 的结合非常紧密。本文将带您从入门到实践，掌握使用 Java...

引言

MySQL 是一款流行的开源关系型数据库管理系统，广泛应用于各种类型的数据库应用中。Java 作为一种广泛使用的编程语言，与 MySQL 的结合非常紧密。本文将带您从入门到实践，掌握使用 Java 连接和操作 MySQL 数据库的技巧。

第1章 MySQL 简介

1.1 MySQL 的特点

• 开源免费
• 易于使用和维护
• 支持多种数据类型和存储引擎
• 高效可靠

1.2 MySQL 的安装与配置

• 下载 MySQL 安装包
• 安装 MySQL
• 配置 MySQL
• 创建数据库

第2章 Java 连接 MySQL

2.1 JDBC 简介

JDBC (Java Database Connectivity) 是 Java 提供的一个用于数据库连接和操作的标准 API。

2.2 连接 MySQL

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class MySQLConnection { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/your_database?useSSL=false"; String user = "root"; String password = "your_password"; Connection conn = null; try { Class.forName("com.mysql.cj.jdbc.Driver"); conn = DriverManager.getConnection(url, user, password); System.out.println("连接成功！"); } catch (ClassNotFoundException e) { System.out.println("找不到驱动类"); e.printStackTrace(); } catch (SQLException e) { System.out.println("数据库连接失败"); e.printStackTrace(); } finally { if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
}

第3章 MySQL 基本操作

3.1 创建表

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class CreateTable { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/your_database?useSSL=false"; String user = "root"; String password = "your_password"; Connection conn = null; Statement stmt = null; try { conn = DriverManager.getConnection(url, user, password); stmt = conn.createStatement(); String sql = "CREATE TABLE IF NOT EXISTS Students (" + "id INT NOT NULL AUTO_INCREMENT," + "name VARCHAR(50) NOT NULL," + "age INT NOT NULL," + "PRIMARY KEY (id)" + ")"; stmt.executeUpdate(sql); System.out.println("表创建成功！"); } catch (SQLException e) { e.printStackTrace(); } finally { if (stmt != null) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
}

3.2 插入数据

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class InsertData { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/your_database?useSSL=false"; String user = "root"; String password = "your_password"; Connection conn = null; PreparedStatement pstmt = null; try { conn = DriverManager.getConnection(url, user, password); String sql = "INSERT INTO Students (name, age) VALUES (?, ?)"; pstmt = conn.prepareStatement(sql); pstmt.setString(1, "Alice"); pstmt.setInt(2, 20); pstmt.executeUpdate(); System.out.println("数据插入成功！"); } catch (SQLException e) { e.printStackTrace(); } finally { if (pstmt != null) { try { pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
}

3.3 查询数据

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class QueryData { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/your_database?useSSL=false"; String user = "root"; String password = "your_password"; Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; try { conn = DriverManager.getConnection(url, user, password); String sql = "SELECT * FROM Students WHERE age > ?"; pstmt = conn.prepareStatement(sql); pstmt.setInt(1, 18); rs = pstmt.executeQuery(); while (rs.next()) { int id = rs.getInt("id"); String name = rs.getString("name"); int age = rs.getInt("age"); System.out.println("ID: " + id + ", Name: " + name + ", Age: " + age); } } catch (SQLException e) { e.printStackTrace(); } finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if (pstmt != null) { try { pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
}

3.4 更新数据

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class UpdateData { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/your_database?useSSL=false"; String user = "root"; String password = "your_password"; Connection conn = null; PreparedStatement pstmt = null; try { conn = DriverManager.getConnection(url, user, password); String sql = "UPDATE Students SET name = ? WHERE id = ?"; pstmt = conn.prepareStatement(sql); pstmt.setString(1, "Alice Smith"); pstmt.setInt(2, 1); pstmt.executeUpdate(); System.out.println("数据更新成功！"); } catch (SQLException e) { e.printStackTrace(); } finally { if (pstmt != null) { try { pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
}

3.5 删除数据

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class DeleteData { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/your_database?useSSL=false"; String user = "root"; String password = "your_password"; Connection conn = null; PreparedStatement pstmt = null; try { conn = DriverManager.getConnection(url, user, password); String sql = "DELETE FROM Students WHERE id = ?"; pstmt = conn.prepareStatement(sql); pstmt.setInt(1, 1); pstmt.executeUpdate(); System.out.println("数据删除成功！"); } catch (SQLException e) { e.printStackTrace(); } finally { if (pstmt != null) { try { pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
}

第4章 高级操作

4.1 事务处理

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class Transaction { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/your_database?useSSL=false"; String user = "root"; String password = "your_password"; Connection conn = null; try { conn = DriverManager.getConnection(url, user, password); conn.setAutoCommit(false); // 禁用自动提交 String sql1 = "UPDATE Students SET age = age + 1 WHERE id = 1"; String sql2 = "UPDATE Students SET age = age + 1 WHERE id = 2"; Statement stmt = conn.createStatement(); stmt.executeUpdate(sql1); stmt.executeUpdate(sql2); conn.commit(); // 提交事务 System.out.println("事务提交成功！"); } catch (SQLException e) { try { if (conn != null) { conn.rollback(); // 回滚事务 } } catch (SQLException ex) { ex.printStackTrace(); } e.printStackTrace(); } finally { if (conn != null) { try { conn.setAutoCommit(true); // 恢复自动提交 conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
}

4.2 批处理

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class BatchProcessing { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/your_database?useSSL=false"; String user = "root"; String password = "your_password"; Connection conn = null; PreparedStatement pstmt = null; try { conn = DriverManager.getConnection(url, user, password); String sql = "INSERT INTO Students (name, age) VALUES (?, ?)"; pstmt = conn.prepareStatement(sql); for (int i = 0; i < 10; i++) { pstmt.setString(1, "Student " + i); pstmt.setInt(2, 20 + i); pstmt.addBatch(); } pstmt.executeBatch(); System.out.println("批处理成功！"); } catch (SQLException e) { e.printStackTrace(); } finally { if (pstmt != null) { try { pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
}

第5章 总结

本文介绍了 Java 连接和操作 MySQL 数据库的基本技巧，包括 JDBC 简介、连接 MySQL、基本操作（创建表、插入数据、查询数据、更新数据、删除数据）以及高级操作（事务处理、批处理）。通过本文的学习，您可以轻松驾驭 MySQL 数据库，将其应用于各种 Java 项目中。