如何在 Scala 中连接到 postgreSQL 数据库？

How can I connect to a postgreSQL database in scala?

提问by Mahesh

I want to know how can I do following things in scala?

我想知道如何在 Scala 中执行以下操作？

1. Connect to a postgreSQL database.
2. Write SQL queries like SELECT , UPDATE etc. to modify a table in that database.

1. 连接到 postgreSQL 数据库。
2. 编写 SQL 查询（如 SELECT 、 UPDATE 等）来修改该数据库中的表。

I know that in python I can do it using PygreSQL but how to do these things in scala?

我知道在 python 中我可以使用 PygreSQL 来做，但是如何在 Scala 中做这些事情？

回答by jedesah

I would recommend having a look at Doobie.

我建议看看Doobie。

Thischapter in the "Book of Doobie" gives a good sense of what your code will look like if you make use of this library.

“Doobie 之书”中的这一章很好地说明了如果您使用这个库，您的代码会是什么样子。

This is the library of choice right now to solve this problem if you are interested in the pure FP side of Scala, i.e. scalaz, scalaz-stream(probably fs2and catssoon) and referential transparency in general.

这是选择的库现在如果你有兴趣在斯卡拉的纯FP侧，即解决这个问题scalaz，scalaz-stream（可能fs2与cats即将推出），并引用透明一般。

It's worth nothing that Doobie is NOT an ORM. At its core, it's simply a nicer, higher-level API over JDBC.

Doobie 不是 ORM 一文不值。从本质上讲，它只是一个基于 JDBC 的更好、更高级别的 API。

回答by Shashank Jain

You need to add dependency "org.postgresql" % "postgresql" % "9.3-1102-jdbc41"in build.sbt and you can modify following code to connect and query database. Replace DB_USER with your db user and DB_NAME as your db name.

需要"org.postgresql" % "postgresql" % "9.3-1102-jdbc41"在build.sbt中添加依赖，可以修改如下代码来连接和查询数据库。将 DB_USER 替换为您的数据库用户并将 DB_NAME 替换为您的数据库名称。

import java.sql.{Connection, DriverManager, ResultSet}

object pgconn extends App {
  println("Postgres connector")

  classOf[org.postgresql.Driver]
  val con_st = "jdbc:postgresql://localhost:5432/DB_NAME?user=DB_USER"
  val conn = DriverManager.getConnection(con_str)
  try {
    val stm = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)

    val rs = stm.executeQuery("SELECT * from Users")

    while(rs.next) {
      println(rs.getString("quote"))
    }
 } finally {
     conn.close()
  }
}

回答by Boris Pavlovi?

Take look at the tutorial "Using Scala with JDBC to connect to MySQL", replace the db url and add the right jdbc library. The link got broken so here's the content of the blog:

看教程“Using Scala with JDBC to connect to MySQL”，替换db url并添加正确的jdbc库。链接失效了，博客内容如下：

Using Scala with JDBC to connect to MySQL

使用 Scala 和 JDBC 连接到 MySQL

A howto on connecting Scala to a MySQL database using JDBC. There are a number of database libraries for Scala, but I ran into a problem getting most of them to work. I attempted to use scala.dbc, scala.dbc2, Scala Query and Querulous but either they aren't supported, have a very limited featured set or abstracts SQL to a weird pseudo language.

使用 JDBC 将 Scala 连接到 MySQL 数据库的方法。Scala 有许多数据库库，但我遇到了一个问题，让它们中的大部分工作。我尝试使用 scala.dbc、scala.dbc2、Scala Query 和 Querulous，但它们要么不受支持，要么功能非常有限，要么将 SQL 抽象为一种奇怪的伪语言。

The Play Framework has a new database library called ANorm which tries to keep the interface to basic SQL but with a slight improved scala interface. The jury is still out for me, only used on one project minimally so far. Also, I've only seen it work within a Play app, does not look like it can be extracted out too easily.

Play 框架有一个名为 ANorm 的新数据库库，它试图保持基本 SQL 的接口，但稍微改进了 Scala 接口。陪审团对我来说仍然没有定论，到目前为止只在一个项目上最少使用过。此外，我只看到它在 Play 应用程序中工作，看起来不太容易提取出来。

So I ended up going with basic Java JDBC connection and it turns out to be a fairly easy solution.

所以我最终选择了基本的 Java JDBC 连接，结果证明这是一个相当简单的解决方案。

Here is the code for accessing a database using Scala and JDBC. You need to change the connection string parameters and modify the query for your database. This example was geared towards MySQL, but any Java JDBC driver should work the same with Scala.

这是使用 Scala 和 JDBC 访问数据库的代码。您需要更改连接字符串参数并修改数据库的查询。此示例面向 MySQL，但任何 Java JDBC 驱动程序都应与 Scala 相同。

Basic Query

基本查询

import java.sql.{Connection, DriverManager, ResultSet};

// Change to Your Database Config
val conn_str = "jdbc:mysql://localhost:3306/DBNAME?user=DBUSER&password=DBPWD"

// Load the driver
classOf[com.mysql.jdbc.Driver]

// Setup the connection
val conn = DriverManager.getConnection(conn_str)
try {
    // Configure to be Read Only
    val statement = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)

    // Execute Query
    val rs = statement.executeQuery("SELECT quote FROM quotes LIMIT 5")

    // Iterate Over ResultSet
    while (rs.next) {
        println(rs.getString("quote"))
    }
}
finally {
    conn.close
}

You will need to download the mysql-connector jar.

您将需要下载 mysql-connector jar。

Or if you are using maven, the pom snippets to load the mysql connector, you'll need to check what the latest version is.

或者，如果您使用 maven，即加载 mysql 连接器的 pom 片段，您需要检查最新版本是什么。

<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>5.1.12</version>
</dependency>

To run the example, save the following to a file (query_test.scala) and run using, the following specifying the classpath to the connector jar:

要运行该示例，请将以下内容保存到文件 (query_test.scala) 中并使用以下内容运行，指定连接器 jar 的类路径：

scala -cp mysql-connector-java-5.1.12.jar:. query_test.scala

Insert, Update and Delete

插入、更新和删除

To perform an insert, update or delete you need to create an updatable statement object. The execute command is slightly different and you will most likely want to use some sort of parameters. Here's an example doing an insert using jdbc and scala with parameters.

要执行插入、更新或删除，您需要创建一个可更新的语句对象。execute 命令略有不同，您很可能希望使用某种参数。这是一个使用 jdbc 和 scala 和参数进行插入的示例。

// create database connection
val dbc = "jdbc:mysql://localhost:3306/DBNAME?user=DBUSER&password=DBPWD"
classOf[com.mysql.jdbc.Driver]
val conn = DriverManager.getConnection(dbc)
val statement = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE)

// do database insert
try {
  val prep = conn.prepareStatement("INSERT INTO quotes (quote, author) VALUES (?, ?) ")
  prep.setString(1, "Nothing great was ever achieved without enthusiasm.")
  prep.setString(2, "Ralph Waldo Emerson")
  prep.executeUpdate
}
finally {
  conn.close
}

回答by Vonn

We are using Squeryl, which is working well so far for us. Depending on your needs it may do the trick.

我们正在使用Squeryl，到目前为止它对我们来说运行良好。根据您的需要，它可能会起作用。

Here is a listof supported DB's and the adapters

这是受支持的数据库和适配器的列表

回答by nilskp

If you want/need to write your own SQL, but hate the JDBC interface, take a look at O/R Broker

如果您想/需要编写自己的 SQL，但讨厌 JDBC 接口，请查看O/R Broker