Java 通过 JDBC (Java Database Connectivity) 提供数据库连接和操作能力。
JDBC 基础操作
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class JDBCExample {
private static final String URL = "jdbc:mysql://localhost:3306/testdb";
private static final String USERNAME = "root";
private static final String PASSWORD = "password";
static {
try {
// 加载数据库驱动
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
// 获取数据库连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(URL, USERNAME, PASSWORD);
}
// 创建用户表
public static void createUserTable() {
String sql = "CREATE TABLE IF NOT EXISTS users (" +
"id INT AUTO_INCREMENT PRIMARY KEY, " +
"username VARCHAR(50) NOT NULL UNIQUE, " +
"password VARCHAR(100) NOT NULL, " +
"email VARCHAR(100), " +
"created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP" +
")";
try (Connection conn = getConnection();
Statement stmt = conn.createStatement()) {
stmt.execute(sql);
System.out.println("用户表创建成功");
} catch (SQLException e) {
e.printStackTrace();
}
}
// 插入用户
public static boolean insertUser(User user) {
String sql = "INSERT INTO users (username, password, email) VALUES (?, ?, ?)";
try (Connection conn = getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, user.getUsername());
pstmt.setString(2, user.getPassword());
pstmt.setString(3, user.getEmail());
int rows = pstmt.executeUpdate();
return rows > 0;
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
// 查询所有用户
public static List getAllUsers() {
List users = new ArrayList<>();
String sql = "SELECT * FROM users";
try (Connection conn = getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
while (rs.next()) {
User user = new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
user.setEmail(rs.getString("email"));
user.setCreatedAt(rs.getTimestamp("created_at"));
users.add(user);
}
} catch (SQLException e) {
e.printStackTrace();
}
return users;
}
// 更新用户
public static boolean updateUser(User user) {
String sql = "UPDATE users SET username = ?, password = ?, email = ? WHERE id = ?";
try (Connection conn = getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, user.getUsername());
pstmt.setString(2, user.getPassword());
pstmt.setString(3, user.getEmail());
pstmt.setInt(4, user.getId());
int rows = pstmt.executeUpdate();
return rows > 0;
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
// 删除用户
public static boolean deleteUser(int userId) {
String sql = "DELETE FROM users WHERE id = ?";
try (Connection conn = getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setInt(1, userId);
int rows = pstmt.executeUpdate();
return rows > 0;
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
}
class User {
private int id;
private String username;
private String password;
private String email;
private Timestamp createdAt;
// 构造方法、getter 和 setter 方法
public User() {}
public User(String username, String password, String email) {
this.username = username;
this.password = password;
this.email = email;
}
// getter 和 setter 方法...
public int getId() { return id; }
public void setId(int id) { this.id = id; }
public String getUsername() { return username; }
public void setUsername(String username) { this.username = username; }
public String getPassword() { return password; }
public void setPassword(String password) { this.password = password; }
public String getEmail() { return email; }
public void setEmail(String email) { this.email = email; }
public Timestamp getCreatedAt() { return createdAt; }
public void setCreatedAt(Timestamp createdAt) { this.createdAt = createdAt; }
}
事务处理
import java.sql.*;
public class TransactionExample {
public static boolean transferMoney(int fromAccountId, int toAccountId, double amount) {
Connection conn = null;
try {
conn = JDBCExample.getConnection();
// 关闭自动提交，开启事务
conn.setAutoCommit(false);
// 检查转出账户余额是否足够
String checkBalanceSQL = "SELECT balance FROM accounts WHERE id = ?";
PreparedStatement checkStmt = conn.prepareStatement(checkBalanceSQL);
checkStmt.setInt(1, fromAccountId);
ResultSet rs = checkStmt.executeQuery();
if (rs.next()) {
double currentBalance = rs.getDouble("balance");
if (currentBalance < amount) {
System.out.println("余额不足");
conn.rollback();
return false;
}
}
// 扣除转出账户金额
String deductSQL = "UPDATE accounts SET balance = balance - ? WHERE id = ?";
PreparedStatement deductStmt = conn.prepareStatement(deductSQL);
deductStmt.setDouble(1, amount);
deductStmt.setInt(2, fromAccountId);
deductStmt.executeUpdate();
// 增加转入账户金额
String addSQL = "UPDATE accounts SET balance = balance + ? WHERE id = ?";
PreparedStatement addStmt = conn.prepareStatement(addSQL);
addStmt.setDouble(1, amount);
addStmt.setInt(2, toAccountId);
addStmt.executeUpdate();
// 提交事务
conn.commit();
System.out.println("转账成功");
return true;
} catch (SQLException e) {
try {
if (conn != null) {
conn.rollback();
}
} catch (SQLException ex) {
ex.printStackTrace();
}
e.printStackTrace();
return false;
} finally {
try {
if (conn != null) {
conn.setAutoCommit(true);
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
提示： 这是一个重要的概念，需要特别注意理解和掌握。
注意： 这是一个常见的错误点，请避免犯同样的错误。