Clojure Functional Programming & JVM Backend Development · 课时

执行 CRUD 操作

掌握如何从 Clojure 操作关系型数据库中的数据，包括创建、读取、更新和删除。

执行 CRUD 操作 是 CoddyKit 上的免费 Clojure Functional Programming & JVM Backend Development 课时。这是第 2 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 Clojure Functional Programming & JVM Backend Development 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 Clojure Functional Programming & JVM Backend Development 课程共包含 4 节课。

本课时的部分内容尚未翻译，以英文显示。

What is CRUD?

When you build applications, you'll constantly interact with databases. The fundamental operations for managing data are often summarized by the acronym CRUD.

Create: Adding new data.
Read: Retrieving existing data.
Update: Modifying existing data.
Delete: Removing data.

In this lesson, we'll master how to perform these essential operations using Clojure's next.jdbc library.

Database Connection Setup

Before we perform CRUD operations, we need a database connection. We'll use next.jdbc with an in-memory SQLite database for our examples.

Remember from the previous lesson, we define a database specification (db-spec) and obtain a data source (ds) or a direct connection.

We'll create a simple users table for our examples.

Create: Inserting New Records

To add new data, we use the next.jdbc.sql/insert! function. It takes a connection, table name, and a map of column-value pairs.

Try running this example to create a users table and insert your first user:

(ns coddykit.core
  (:require [next.jdbc :as jdbc]
            [next.jdbc.sql :as sql]))

(defn -main
  "Insert a new user into the database."
  [& args]
  (let [db-spec {:dbtype "sqlite" :dbname ":memory:"}]
    (with-open [conn (jdbc/get-connection db-spec)]
      (jdbc/execute! conn ["DROP TABLE IF EXISTS users;"])
      (jdbc/execute! conn ["CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)"])

      (println "Inserting new user...")
      (let [new-user {:name "Alice" :email "alice@example.com"}
            result (sql/insert! conn :users new-user)]
        (println "Insert result:" result))

      (println "\nUsers in database:")
      (doseq [user (sql/select! conn ["SELECT * FROM users"])]
        (println user)))))

Create: Inserting Multiple Records

What if you need to add many records at once? The next.jdbc.sql/insert-multi! function is perfect for this. It takes a sequence of maps, each representing a row to insert.

It's more efficient than calling insert! repeatedly in a loop.

(ns coddykit.core
  (:require [next.jdbc :as jdbc]
            [next.jdbc.sql :as sql]))

(defn -main
  "Insert multiple users into the database."
  [& args]
  (let [db-spec {:dbtype "sqlite" :dbname ":memory:"}]
    (with-open [conn (jdbc/get-connection db-spec)]
      (jdbc/execute! conn ["DROP TABLE IF EXISTS users;"])
      (jdbc/execute! conn ["CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)"])

      (println "Inserting multiple users...")
      (let [new-users [{:name "Bob" :email "bob@example.com"}
                       {:name "Charlie" :email "charlie@example.com"}]
            result (sql/insert-multi! conn :users new-users)]
        (println "Insert result:" result))

      (println "\nUsers in database:")
      (doseq [user (sql/select! conn ["SELECT * FROM users"])]
        (println user)))))

Read: Fetching All Data

To retrieve all records from a table, use next.jdbc.sql/select-all!. It takes a connection and the table name (as a keyword or string).

The function returns a sequence of maps, where each map represents a row.

(ns coddykit.core
  (:require [next.jdbc :as jdbc]
            [next.jdbc.sql :as sql]))

(defn -main
  "Select all users from the database."
  [& args]
  (let [db-spec {:dbtype "sqlite" :dbname ":memory:"}]
    (with-open [conn (jdbc/get-connection db-spec)]
      (jdbc/execute! conn ["DROP TABLE IF EXISTS users;"])
      (jdbc/execute! conn ["CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)"])
      (sql/insert! conn :users {:name "Alice" :email "alice@example.com"})
      (sql/insert! conn :users {:name "Bob" :email "bob@example.com"})

      (println "Fetching all users...")
      (let [all-users (sql/select-all! conn :users)]
        (doseq [user all-users]
          (println user))))))

Read: Filtering Data with `WHERE`

Often, you only want specific records. Use next.jdbc.sql/select! with a WHERE clause to filter results. The WHERE clause is provided as a map.

For example, to find users with a specific name or email:

(ns coddykit.core
  (:require [next.jdbc :as jdbc]
            [next.jdbc.sql :as sql]))

(defn -main
  "Select users with a specific name."
  [& args]
  (let [db-spec {:dbtype "sqlite" :dbname ":memory:"}]
    (with-open [conn (jdbc/get-connection db-spec)]
      (jdbc/execute! conn ["DROP TABLE IF EXISTS users;"])
      (jdbc/execute! conn ["CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)"])
      (sql/insert! conn :users {:name "Alice" :email "alice@example.com"})
      (sql/insert! conn :users {:name "Bob" :email "bob@example.com"})
      (sql/insert! conn :users {:name "Alice" :email "alice2@example.com"})

      (println "Fetching users named Alice...")
      (let [alices (sql/select! conn :users {:name "Alice"})]
        (doseq [user alices]
          (println user))))))

Update: Modifying Existing Data

To change existing records, use next.jdbc.sql/update!. It takes a connection, table name, a map of columns to update, and a WHERE clause map to specify which records to modify.

Always be careful with your WHERE clause when updating!

(ns coddykit.core
  (:require [next.jdbc :as jdbc]
            [next.jdbc.sql :as sql]))

(defn -main
  "Update a user's email address."
  [& args]
  (let [db-spec {:dbtype "sqlite" :dbname ":memory:"}]
    (with-open [conn (jdbc/get-connection db-spec)]
      (jdbc/execute! conn ["DROP TABLE IF EXISTS users;"])
      (jdbc/execute! conn ["CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)"])
      (sql/insert! conn :users {:name "Alice" :email "alice@example.com"})

      (println "Original user:")
      (println (first (sql/select! conn :users {:name "Alice"})))

      (println "\nUpdating Alice's email...")
      (let [result (sql/update! conn :users {:email "alice.new@example.com"} {:name "Alice"})]
        (println "Update result:" result))

      (println "\nUpdated user:")
      (println (first (sql/select! conn :users {:name "Alice"})))))

Delete: Removing Data

To remove records from a table, use next.jdbc.sql/delete!. Similar to update!, it requires a connection, table name, and a WHERE clause map to specify which records to remove.

A missing or empty WHERE clause can delete ALL records, so use with extreme caution!

(ns coddykit.core
  (:require [next.jdbc :as jdbc]
            [next.jdbc.sql :as sql]))

(defn -main
  "Delete a user from the database."
  [& args]
  (let [db-spec {:dbtype "sqlite" :dbname ":memory:"}]
    (with-open [conn (jdbc/get-connection db-spec)]
      (jdbc/execute! conn ["DROP TABLE IF EXISTS users;"])
      (jdbc/execute! conn ["CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)"])
      (sql/insert! conn :users {:name "Alice" :email "alice@example.com"})
      (sql/insert! conn :users {:name "Bob" :email "bob@example.com"})

      (println "Users before delete:")
      (doseq [user (sql/select-all! conn :users)] (println user))

      (println "\nDeleting Bob...")
      (let [result (sql/delete! conn :users {:name "Bob"})]
        (println "Delete result:" result))

      (println "\nUsers after delete:")
      (doseq [user (sql/select-all! conn :users)] (println user)))))

Atomic Operations with Transactions

Sometimes you need multiple CRUD operations to succeed or fail as a single, atomic unit. This is where transactions come in.

If any operation within a transaction fails, all changes are rolled back, ensuring data consistency. Use next.jdbc/with-transaction for this.

(ns coddykit.core
  (:require [next.jdbc :as jdbc]
            [next.jdbc.sql :as sql]))

(defn -main
  "Demonstrate a database transaction."
  [& args]
  (let [db-spec {:dbtype "sqlite" :dbname ":memory:"}]
    (with-open [conn (jdbc/get-connection db-spec)]
      (jdbc/execute! conn ["DROP TABLE IF EXISTS accounts;"])
      (jdbc/execute! conn ["CREATE TABLE accounts (id INTEGER PRIMARY KEY, name TEXT, balance INTEGER)"])
      (sql/insert! conn :accounts {:name "Alice" :balance 100})
      (sql/insert! conn :accounts {:name "Bob" :balance 50})

      (println "Balances before transaction:")
      (doseq [acc (sql/select-all! conn :accounts)] (println acc))

      (println "\nAttempting to transfer $20 from Alice to Bob...")
      (try
        (jdbc/with-transaction [tx conn]
          (sql/update! tx :accounts {:balance [- :balance 20]} {:name "Alice"})
          (sql/update! tx :accounts {:balance [+ :balance 20]} {:name "Bob"})
          (println "Transfer successful!"))
        (catch Exception e
          (println "Transfer failed:" (.getMessage e))))

      (println "\nBalances after transaction:")
      (doseq [acc (sql/select-all! conn :accounts)] (println acc)))))

CRUD Quick Check

You've learned the core functions for interacting with databases. Let's test your understanding!

Recap: Mastering CRUD

Congratulations! You've mastered the essential CRUD operations for database interaction in Clojure using next.jdbc:

Create: insert! and insert-multi! to add data.
Read: select! and select-all! to retrieve data.
Update: update! to modify existing data.
Delete: delete! to remove data.

You also learned about using transactions with with-transaction for atomic operations, ensuring data integrity. These are the building blocks for almost any data-driven application!

常见问题解答

「执行 CRUD 操作」课时是免费的吗？

是的 — 「执行 CRUD 操作」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 Clojure Functional Programming & JVM Backend Development 课程的其余内容，请升级到 CoddyKit PRO。 Clojure Functional Programming & JVM Backend Development 课程共包含 4 节课。

「执行 CRUD 操作」这节课中我会学到什么？

掌握如何从 Clojure 操作关系型数据库中的数据，包括创建、读取、更新和删除。你通过在浏览器中直接运行的动手代码来练习 Clojure Functional Programming & JVM Backend Development，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 Clojure Functional Programming & JVM Backend Development 需要有经验吗？

无需任何先前经验。CoddyKit 上的 Clojure Functional Programming & JVM Backend Development 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 2 节课，共 4 节。

「执行 CRUD 操作」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 Clojure Functional Programming & JVM Backend Development 课中编写并运行代码吗？

能。每节 Clojure Functional Programming & JVM Backend Development 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

← 返回 Clojure Functional Programming & JVM Backend Development