Java和Scala中怎么使用数据库进行增删查改操作

在Java和Scala中，使用数据库进行增删查改（CRUD）操作是非常常见的任务。本文将介绍如何在Java和Scala中使用JDBC和Slick库来进行这些操作。

1. Java中使用JDBC进行CRUD操作

1.1 连接数据库

首先，我们需要加载数据库驱动并建立连接。以MySQL为例：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DatabaseConnection {
    private static final String URL = "jdbc:mysql://localhost:3306/mydatabase";
    private static final String USER = "root";
    private static final String PASSWORD = "password";

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }
}

1.2 插入数据

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class InsertExample {
    public static void main(String[] args) {
        String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, "John Doe");
            pstmt.setString(2, "john.doe@example.com");
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

1.3 查询数据

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class SelectExample {
    public static void main(String[] args) {
        String sql = "SELECT * FROM users";
        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs.next()) {
                System.out.println("ID: " + rs.getInt("id") + ", Name: " + rs.getString("name") + ", Email: " + rs.getString("email"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

1.4 更新数据

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class UpdateExample {
    public static void main(String[] args) {
        String sql = "UPDATE users SET email = ? WHERE id = ?";
        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, "john.new@example.com");
            pstmt.setInt(2, 1);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

1.5 删除数据

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class DeleteExample {
    public static void main(String[] args) {
        String sql = "DELETE FROM users WHERE id = ?";
        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, 1);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

2. Scala中使用Slick进行CRUD操作

2.1 添加依赖

在build.sbt中添加Slick依赖：

libraryDependencies ++= Seq(
  "com.typesafe.slick" %% "slick" % "3.3.3",
  "org.postgresql" % "postgresql" % "42.2.5",
  "com.typesafe.slick" %% "slick-hikaricp" % "3.3.3"
)

2.2 定义表结构

import slick.jdbc.PostgresProfile.api._

case class User(id: Int, name: String, email: String)

class Users(tag: Tag) extends Table[User](tag, "users") {
  def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
  def name = column[String]("name")
  def email = column[String]("email")
  def * = (id, name, email) <> (User.tupled, User.unapply)
}

val users = TableQuery[Users]

2.3 插入数据

import scala.concurrent.ExecutionContext.Implicits.global
import scala.concurrent.Await
import scala.concurrent.duration.Duration

val db = Database.forConfig("mydb")

val insertAction = users += User(0, "John Doe", "john.doe@example.com")
val future = db.run(insertAction)
Await.result(future, Duration.Inf)

2.4 查询数据

val query = users.filter(_.name === "John Doe")
val future = db.run(query.result)
val result = Await.result(future, Duration.Inf)
result.foreach(println)

2.5 更新数据

val updateAction = users.filter(_.id === 1).map(_.email).update("john.new@example.com")
val future = db.run(updateAction)
Await.result(future, Duration.Inf)

2.6 删除数据

val deleteAction = users.filter(_.id === 1).delete
val future = db.run(deleteAction)
Await.result(future, Duration.Inf)

结论

在Java中，使用JDBC可以方便地进行数据库操作，但需要手动管理连接和资源。而在Scala中，使用Slick可以更简洁地进行数据库操作，并且支持类型安全的查询。根据项目需求和个人偏好，可以选择适合的工具来进行数据库操作。