使用Druid连接mysql数据

1.先导入maven

<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>5.1.47</version>
</dependency>
<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>druid</artifactId>
	<version>1.1.9</version>
</dependency>
<dependency>
	<groupId>commons-dbutils</groupId>
	<artifactId>commons-dbutils</artifactId>
	<version>1.6</version>
</dependency>

2.创建加载ProPerties配置文件工具类

public class ProPertiesUtil {

    /**
     * @param fileName 文件名称
     */
    public static Properties getProperties(String fileName) {
        Properties properties = new Properties();
        // 使用ClassLoader加载properties配置文件生成对应的输入流
        InputStream in = ProPertiesUtil.class.getClassLoader().getResourceAsStream(fileName);
        // 使用properties对象加载输入流
        try {
            properties.load(in);
        } catch (IOException e) {
            e.printStackTrace();
        }
        //返回Properties对象
        return properties;
    }
}

3.创建mysql.properties

driverClassName=com.mysql.jdbc.Driver
username=root
password=******
url=jdbc:mysql://localhost:3306/test
initialSiz=5
maxActive=15
minIdle=5

4.创建连接数据库的工具类

public class JDBCUtil {
    private static DruidDataSource dataSource;

    static {
        Properties properties = ProPertiesUtil.getProperties("mysql.properties");
        // 使用德鲁伊数据库连接池工厂类
        try {
            dataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取连接对象（从数据库连接池中获取）
     *
     * @return 连接对象
     */
    public static Connection getConnection() {
        Connection connection = null;
        try {
            connection = dataSource.getConnection();
        } catch (Exception e) {
            return null;
        }
        return connection;
    }

    /**
     * 关闭jdbc资源对象
     *
     * @param connection 连接对象
     */
    public static void close(Connection connection) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

5.scala操作数据库工具类

import java.sql.{Connection, PreparedStatement, ResultSet, ResultSetMetaData, Statement}

import scala.collection.mutable
import scala.collection.mutable.ListBuffer

object SqlProxy {

    /**
      * 查询结果并返回
      *
      * @param connection 连接对象
      * @param sql        sql语句
      * @param objects    参数
      */
    def executeSelectByBack(connection: Connection, sql: String, back: (ResultSet) => Unit, objects: Any*): Unit = {
        val statement: PreparedStatement = connection.prepareStatement(sql)
        //判断参数是否为空
        if (objects != null && objects.nonEmpty) {
            //将参数设置进去
            for (i <- objects.indices) {
                //参数索引从1开始
                statement.setObject(i + 1, objects(i))
            }
        }
        //执行查询
        val resultSet: ResultSet = statement.executeQuery()
        back(resultSet)
        //关闭资源
        resultSet.close()
        statement.close()
    }

    /**
      * 查询一条结果，返回map集合
      *
      * @param connection 连接对象
      * @param sql        sql语句
      * @param objects    参数
      * @return 返回map
      */
    def executeSelectOne(connection: Connection, sql: String, objects: Any*): mutable.HashMap[String, Any] = {
        //设置sql语句
        val preparedStatement: PreparedStatement = connection.prepareStatement(sql)
        //判断参数是否为空
        if (objects != null && objects.nonEmpty) {
            //将参数设置进去
            for (i <- objects.indices) {
                //参数索引从1开始
                preparedStatement.setObject(i + 1, objects(i))
            }
        }
        //执行查询
        val resultSet: ResultSet = preparedStatement.executeQuery()
        //定义一个hashMap
        val resultMap = new mutable.HashMap[String, Any]()
        //获取列名信息
        val metaData: ResultSetMetaData = resultSet.getMetaData
        //获取一共有几列
        val rowCount: Int = metaData.getColumnCount
        if (resultSet.next()) {
            for (i <- 1 to rowCount) {
                resultMap.put(metaData.getColumnName(i), resultSet.getObject(i))
            }
        }
        //关闭资源
        resultSet.close()
        preparedStatement.close()
        //返回
        resultMap
    }

    /**
      * 返回多条结果
      *
      * @param connection 连接对象
      * @param sql        sql语句
      * @param objects    参数
      * @return List[Map]
      */
    def executeSelectAll(connection: Connection, sql: String, objects: Any*): ListBuffer[mutable.HashMap[String, Any]] = {
        //设置sql语句
        val preparedStatement: PreparedStatement = connection.prepareStatement(sql)
        //判断参数是否为空
        if (objects != null && objects.nonEmpty) {
            //将参数设置进去
            for (i <- objects.indices) {
                //参数索引从1开始
                preparedStatement.setObject(i + 1, objects(i))
            }
        }
        //执行查询
        val resultSet: ResultSet = preparedStatement.executeQuery()
        //定义一个hashMap
        val resultList = new ListBuffer[mutable.HashMap[String, Any]]()
        //获取列名信息
        val metaData: ResultSetMetaData = resultSet.getMetaData
        //获取一共有几列
        val rowCount: Int = metaData.getColumnCount
        while (resultSet.next()) {
            val hashMap = new mutable.HashMap[String, Any]()
            for (i <- 1 to rowCount) {
                hashMap.put(metaData.getColumnName(i), resultSet.getObject(i))
            }
            resultList.append(hashMap)
        }
        //关闭资源
        resultSet.close()
        preparedStatement.close()
        //返回
        resultList
    }

    /**
      * 添加或者修改信息
      *
      * @param connection 连接对象
      * @param sql        sql语句
      * @param objects    参数
      */
    def executeUpdateOne(connection: Connection, sql: String, objects: Any*): Int = {
        //设置sql语句
        val preparedStatement: PreparedStatement = connection.prepareStatement(sql)
        //判断参数是否为空
        if (objects != null && objects.nonEmpty) {
            //将参数设置进去
            for (i <- objects.indices) {
                //参数索引从1开始
                preparedStatement.setObject(i + 1, objects(i))
            }
        }
        //执行
        val resultCount: Int = preparedStatement.executeUpdate()
        //关闭
        preparedStatement.close()
        //返回
        resultCount
    }

    /**
      * 批量添加或者修改信息
      *
      * @param connection 连接对象
      * @param sql        sql语句
      * @param objectList 参数数组
      */
    def executeUpdateAll(connection: Connection, sql: String, objectList: List[Array[Any]]): Array[Int] = {
        //设置sql语句
        val preparedStatement: PreparedStatement = connection.prepareStatement(sql)
        //判断参数是否为空
        if (objectList != null && objectList.nonEmpty) {
            //将参数设置进去
            objectList.foreach(arr => {
                for (i <- arr.indices) {
                    //参数索引从1开始
                    preparedStatement.setObject(i + 1, arr(i))
                }
                preparedStatement.addBatch()
            })
        }
        //执行
        val resultCountList: Array[Int] = preparedStatement.executeBatch()
        //关闭
        preparedStatement.close()
        //返回
        resultCountList
    }

    /**
      * 添加信息并返回主键
      *
      * @param connection 连接对象
      * @param sql        sql语句
      * @param objects    参数
      */
    def executeInsertOne(connection: Connection, sql: String, objects: Any*): Option[Long] = {
        //设置sql语句
        val preparedStatement: PreparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)
        //判断参数是否为空
        if (objects != null && objects.nonEmpty) {
            //将参数设置进去
            for (i <- objects.indices) {
                //参数索引从1开始
                preparedStatement.setObject(i + 1, objects(i))
            }
        }
        //执行
        preparedStatement.executeUpdate()
        //获取自增结果集
        val generatedKeys: ResultSet = preparedStatement.getGeneratedKeys
        var keyNum: Option[Long] = None
        //判断是否有数据
        if (generatedKeys.next()) {
            keyNum = Some(generatedKeys.getLong(1))
        }
        //关闭
        generatedKeys.close()
        preparedStatement.close()
        //返回
        keyNum
    }

    /**
      * 批量添加信息,并返回主键
      *
      * @param connection 连接对象
      * @param sql        sql语句
      * @param objectList 参数数组
      */
    def executeInsertAll(connection: Connection, sql: String, objectList: List[Array[Any]]): ListBuffer[Long] = {
        //设置sql语句
        val preparedStatement: PreparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)
        //判断参数是否为空
        if (objectList != null && objectList.nonEmpty) {
            //将参数设置进去
            objectList.foreach(arr => {
                for (i <- arr.indices) {
                    //参数索引从1开始
                    preparedStatement.setObject(i + 1, arr(i))
                }
                preparedStatement.addBatch()
            })
        }
        //执行
        preparedStatement.executeBatch()
        //获取自增结果集
        val generatedKeys: ResultSet = preparedStatement.getGeneratedKeys
        var keyNumList = new ListBuffer[Long]()
        //判断是否有数据
        while (generatedKeys.next()) {
            keyNumList.append(generatedKeys.getLong(1))
        }
        //关闭
        generatedKeys.close()
        preparedStatement.close()
        //返回
        keyNumList
    }
}

6.使用dbutils进行增删改查

	/**
     * 查询一条记录
     */
    public static void selectOne() {
        //创建QueryRunner 对象
        QueryRunner queryRunner = new QueryRunner();
        //获取Connection连接
        Connection connection = JDBCUtil.getConnection();
        //定义sql语句
        String sql = "select * from user where id=?";
        try {
            assert connection != null;
            //这条记录返回Map集合
            Map<String, Object> objectMap = queryRunner.query(connection, sql, new MapHandler(), 2);
            System.out.println(objectMap);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(connection);
        }
    }

    /**
     * 查询多条记录
     */
    public static void selectList() {
        //创建QueryRunner 对象
        QueryRunner queryRunner = new QueryRunner();
        //获取Connection连接
        Connection connection = JDBCUtil.getConnection();
        //定义sql语句
        String sql = "select * from user";
        try {
            assert connection != null;
            //这条记录返回Map集合
            List<Map<String, Object>> mapList = queryRunner.query(connection, sql, new MapListHandler());
            System.out.println(mapList);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(connection);
        }
    }

    /**
     * 修改数据
     */
    public static void update() {
        //创建QueryRunner 对象
        QueryRunner queryRunner = new QueryRunner();
        //获取Connection连接
        Connection connection = JDBCUtil.getConnection();
        //定义sql语句
        String sql = "update user set name=?,age=? where id=?";
        try {
            assert connection != null;
            queryRunner.update(connection, sql, "zhangsan", 30, 1);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(connection);
        }
    }

    /**
     * 删除记录
     */
    public static void delete() {
        //创建QueryRunner 对象
        QueryRunner queryRunner = new QueryRunner();
        //获取Connection连接
        Connection connection = JDBCUtil.getConnection();
        //定义sql语句
        String sql = "delete from user where id=?";
        try {
            assert connection != null;
            queryRunner.update(connection, sql, 1);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(connection);
        }
    }

    /**
     * 添加一条记录
     */
    public static void insertOne() {
        //创建QueryRunner 对象
        QueryRunner queryRunner = new QueryRunner();
        //获取Connection连接
        Connection connection = JDBCUtil.getConnection();
        //定义sql语句
        String sql = "insert into user(name,age) values(?,?)";
        try {
            assert connection != null;
            int update = queryRunner.update(connection, sql, "zhangsan", 20);
            System.out.println(update);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(connection);
        }
    }

    /**
     * 添加多条记录
     */
    public static void insertBach() {
        //创建QueryRunner 对象
        QueryRunner queryRunner = new QueryRunner();
        //获取Connection连接
        Connection connection = JDBCUtil.getConnection();
        //定义sql语句
        String sql = "insert into user(name,age) values(?,?)";
        try {
            assert connection != null;
            Object[][] list = new Object[5][2];
            list[0] = new Object[]{"zhangsan", 20};
            list[1] = new Object[]{"zhangsan", 21};
            list[2] = new Object[]{"zhangsan", 22};
            list[3] = new Object[]{"zhangsan", 23};
            list[4] = new Object[]{"zhangsan", 24};
            queryRunner.batch(connection, sql, list);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(connection);
        }
    }

    /**
     * 添加一条记录，返回自增主键
     */
    public static void insetOne() {
        //创建QueryRunner 对象
        QueryRunner queryRunner = new QueryRunner();
        //获取Connection连接
        Connection connection = JDBCUtil.getConnection();
        //定义sql语句
        String sql = "insert into user(name,age) values(?,?)";
        try {
            assert connection != null;
            Long id = queryRunner.insert(connection, sql, new ScalarHandler<Long>(), "zhangsan", 20);
            System.out.println(id);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(connection);
        }
    }

小知识点：(数据库插入数据时存在则替换)

create table `user_info`(
   `uid` int(11) not null auto_increment primary key,
   `last_name` char(20) not null,
   `first_name` char(20) not null,
   unique key `uid_unique` (`uid`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

1.当数据存在时，不插入数据

insert ignore into user_info(uid,last_name,first_name) values (1,'x','y');

2.当数据存在时，更新数据

insert into user_info(uid,last_name,first_name) values(1,'x','y') on duplicate key update last_name='x',first_name='x';

replace into user_info(uid,last_name,first_name) values (1,'x','y');