scala操作mysql数据库

Mysql工具类：

package com.bdsoft.utils

import java.sql.{Connection, DriverManager, ResultSet}
import com.bdsoft.utils.BaseUtil.loadProperties
import scala.collection.mutable
import scala.collection.mutable.ArrayBuffer

object MySql {
  private var connection: Connection = _

  /**
    * 获取数据库连接
    * @return
    */
  def conn(): Connection = {
    if (connection == null || connection.isClosed) {
      loadProperties()
      val driver = System.getProperty("jdbc_driverClassName")
      Class.forName(driver)
      val user = System.getProperty("jdbc_user")
      val password = System.getProperty("jdbc_password")
      val url = System.getProperty("jdbc_url")
      connection = DriverManager.getConnection(url, user, password)
    }
    connection
  }

  /**
    * 执行单条sql语句，可以是增删改
    * @param sql
    * @return
    */
  def exec(sql: String): Int = {
    val statement = conn().createStatement
    statement.executeUpdate(sql)
  }

  /**
    * 执行查询语句
    * @param sql
    * @return
    */
  def select(sql: String): Array[mutable.HashMap[String, String]] = {
    val statement = conn().createStatement
    val res: ResultSet = statement.executeQuery(sql)
    var arr = ArrayBuffer[mutable.HashMap[String, String]]()
    while (res.next) {
      val metaData = res.getMetaData
      val map = new mutable.HashMap[String, String]()
      for (i <- 1 to metaData.getColumnCount) {
        val field = metaData.getColumnName(i)
        map += (field -> res.getString(field))
      }
      arr += map
    }
    arr.toArray
  }

  /**
    * 关闭连接
    */
  def close(): Unit = {
    try {
      if (!connection.isClosed() || connection != null) {
        connection.close()
      }
    } catch {
      case ex: Exception => ex.printStackTrace()
    }
  }
}

基本工具类（用于加载配置文件）：

package com.bdsoft.utils

import java.util.Properties
import java.io.FileInputStream

object BaseUtil {

  private var loaded: Boolean = false

  /**
    * 加载配置文件信息
    * @return
    */
  def loadProperties(): Unit = {
    if (!loaded) {
      val properties = new Properties()
      val path = Thread.currentThread().getContextClassLoader.getResource("my.properties").getPath //文件要放到resource文件夹下
      properties.load(new FileInputStream(path))
      System.setProperties(properties)
      loaded = true
    }
  }
}

配置文件my.properties：

# jdbc
jdbc_driverClassName=com.mysql.jdbc.Driver
#jdbc_driverClassName=com.mysql.cj.jdbc.Driver
jdbc_url=jdbc:mysql://localhost/aaa?characterEncoding=utf-8
jdbc_user=root
jdbc_password=123456

准备mysql表数据：

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', '张三', '25');
INSERT INTO `user` VALUES ('2', '李四', '27');
INSERT INTO `user` VALUES ('3', '王麻子', '24');

测试类：

package com.bdsoft.demo

import com.bdsoft.utils.MySql

object JdbcDemo {

  def main(args: Array[String]): Unit = {

    // 查询
    val arr = MySql.select("select * from user")
    for (a <- arr) println(a)
    // 新增
    val i = MySql.exec("INSERT INTO user (name, age) VALUES ('茂神', '26');")
    println(i)
    // 修改
    val j = MySql.exec("UPDATE user SET name='王五', age='34' WHERE id='3';")
    println(j)
    // 删除
    val k = MySql.exec("delete from  user WHERE id='1';")
    println(k)

    // 最后关闭连接
    MySql.close()
  }
}