JDBC连接MySQL与Oracle

JDBC连接MySQL 、JDBC连接Oracle （跳转）

JDBC连接MySQL

import org.junit.Test;

import java.sql.*;

/**
 * JDBC连接MySQL
 */
public class JDBCConnMysql {
    /**
     * 连接驱动
     */
    public static final String DRIVER = "com.mysql.jdbc.Driver";

    /**
     * 连接数据库URL
     * 旧版本5.7以下: jdbc:mysql://127.0.0.1:3306/<database>?useUnicode=true&characterEnocding=utf-8
     * 5.7-6版本: jdbc:mysql://127.0.0.1:3306/<database>?useUnicode=true&characterEnocding=utf-8&useSSL=false
     * 6版本以上:jdbc:mysql://127.0.0.1:3306/<database>?useUnicode=true&characterEnocding=utf-8&useSSL=false&serverTimezone=UTC
     */
    public static final String CONNECT_URL = "jdbc:mysql://<host>:<port>/<database>?useUnicode=true&characterEnocding=utf-8&useSSL=false";

    /**
     * 连接用户名
     */
    public static final String USERNAME = "<username>";

    /**
     * 连接密码
     */
    public static final String PASSWORD = "<password>";

    /**
     * 查询sql语句
     */
    private static String SQL = null;

    /**
     * 创建一个数据库连接
     */
    Connection connection = null;

    /**
     * 创建预编译语句对象
     */
    PreparedStatement preparedStatement = null;

    CallableStatement callableStatement = null;

    /**
     * 创建一个结果集对象
     */
    ResultSet resultSet = null;

    /**
     * 查询数据
     */
    @Test
    public void queryAll() {
        try {
            // 1.加载驱动进入内存
            Class.forName(DRIVER);
            // 2.连接数据库
            connection = DriverManager.getConnection(CONNECT_URL, USERNAME, PASSWORD);
            // 3.执行sql语句:可以有效防止两次执行同一sql，提高性能
            SQL = "select * from <table>";
            preparedStatement = connection.prepareStatement(SQL);
            // 4.遍历数据
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                System.out.println(resultSet.getInt("id") + "\t" + resultSet.getString(2));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 5.关闭
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 增加数据
     */
    @Test
    public void add() {
        try {
            // 1.加载驱动进入内存
            Class.forName(DRIVER);
            // 2.连接数据库
            connection = DriverManager.getConnection(CONNECT_URL, USERNAME, PASSWORD);
            // 3.执行sql语句:可以有效防止两次执行同一sql，提高性能
            SQL = "insert into <table>(<column1>,<column2>) values(?,?)";
            preparedStatement = connection.prepareStatement(SQL);
            preparedStatement.setInt(1, <xxx>);
            preparedStatement.setString(2, "<xxx>");
            // 4.增加数据
            preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 5.关闭
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 删除数据
     */
    @Test
    public void del() {
        try {
            // 1.加载驱动进入内存
            Class.forName(DRIVER);
            // 2.连接数据库
            connection = DriverManager.getConnection(CONNECT_URL, USERNAME, PASSWORD);
            // 3.执行sql语句:可以有效防止两次执行同一sql，提高性能
            SQL = "delete from <table> where <row> = ?";
            preparedStatement = connection.prepareStatement(SQL);
            preparedStatement.setInt(1, <xxx>);
            // 4.删除数据
            preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 5.关闭
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 更改数据
     */
    @Test
    public void update() {
        try {
            // 1.加载驱动进入内存
            Class.forName(DRIVER);
            // 2.连接数据库
            connection = DriverManager.getConnection(CONNECT_URL, USERNAME, PASSWORD);
            // 3.执行sql语句:可以有效防止两次执行同一sql，提高性能
            preparedStatement = connection.prepareStatement("update <table> set <column> = ? where <row> = ?");
            preparedStatement.setString(1, "<xxx>");
            preparedStatement.setInt(2, <xxx>);
            // 4.更改数据
            preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 5.关闭
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 执行存储过程
     */
    @Test
    public void executeProcedure() {
        try {
            // 1.加载驱动进入内存
            Class.forName(DRIVER);
            // 2.连接数据库
            connection = DriverManager.getConnection(CONNECT_URL, USERNAME, PASSWORD);
            // 3.执行sql语句:可以有效防止两次执行同一sql，提高性能
            SQL = "{call <procedure>(?,?)}";
            callableStatement = connection.prepareCall(SQL);
            callableStatement.setInt(1, <xxx>);// 输入参数
            callableStatement.registerOutParameter(2, JDBCType.FLOAT);// 输出参数
            // 4.执行存储过程
            boolean flag = callableStatement.execute();// 是否有结果集
            while (flag) {
                System.out.println("总计：" + callableStatement.getFloat(2));// 取得输出参数
                resultSet = callableStatement.getResultSet();// 取得查询结果集
                while (resultSet.next()) {
                    System.out.println(resultSet.getInt("<columnlabel>") + "\t" + resultSet.getFloat("<columnlabel>"));
                }
                flag = callableStatement.getMoreResults();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 5.关闭
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (callableStatement != null) {
                try {
                    callableStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 执行函数
     */
    @Test
    public void executeFunction() {
        try {
            // 1.加载驱动进入内存
            Class.forName(DRIVER);
            // 2.连接数据库
            connection = DriverManager.getConnection(CONNECT_URL, USERNAME, PASSWORD);
            // 3.执行sql语句:可以有效防止两次执行同一sql，提高性能
            SQL = "{?=call <function>(?)}";
            callableStatement = connection.prepareCall(SQL);
            callableStatement.registerOutParameter(1, JDBCType.FLOAT);// 输出参数
            callableStatement.setInt(2, <xxx>);// 输入参数
            // 4.执行函数
            callableStatement.execute();
            System.out.println("总计：" + callableStatement.getFloat(1));// 取得输出参数
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 5.关闭
            if (callableStatement != null) {
                try {
                    callableStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

JDBC连接Oracle

package com.qushihan.JDBC;

import oracle.jdbc.OracleTypes;
import org.junit.Test;

import java.sql.*;

/**
 * JDBC连接Oracle
 */
public class JDBCConnOracle {
    /**
     * 连接驱动
     */
    private static final String DRIVER = "oracle.jdbc.OracleDriver";

    /**
     * 连接数据库URL
     */
    private static final String CONNECT_URL = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";

    /**
     * 连接用户名
     */
    private static final String USERNAME = "scott";

    /**
     * 连接密码
     */
    private static final String PASSWORD = "tiger";

    /**
     * 查询sql语句
     */
    private static String SQL = null;

    /**
     * 创建一个数据库连接
     */
    Connection connection = null;

    /**
     * 创建预编译语句对象
     */
    PreparedStatement preparedStatement = null;

    CallableStatement callableStatement = null;

    /**
     * 创建一个结果集对象
     */
    ResultSet resultSet = null;

    /**
     * 增加数据
     */
    @Test
    public void add() {
        try {
            Class.forName(DRIVER);
            connection = DriverManager.getConnection(CONNECT_URL, USERNAME, PASSWORD);
            SQL = "INSERT INTO student(id, name, age) VALUES (?,?,?)";
            preparedStatement = connection.prepareStatement(SQL);
            preparedStatement.setInt(1, 4);
            preparedStatement.setString(2, "rose");
            preparedStatement.setInt(3, 21);
            preparedStatement.executeUpdate();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 删除数据
     */
    @Test
    public void del() {
        try {
            Class.forName(DRIVER);
            connection = DriverManager.getConnection(CONNECT_URL, USERNAME, PASSWORD);
            SQL = "DELETE student where NAME = ?";
            preparedStatement = connection.prepareStatement(SQL);
            preparedStatement.setString(1, "rose");
            preparedStatement.executeUpdate();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 修改数据
     */
    @Test
    public void update() {
        try {
            Class.forName(DRIVER);
            connection = DriverManager.getConnection(CONNECT_URL, USERNAME, PASSWORD);
            SQL = "UPDATE student SET age = ? WHERE id = ?";
            preparedStatement = connection.prepareStatement(SQL);
            preparedStatement.setInt(1, 22);
            preparedStatement.setInt(2, 4);
            preparedStatement.executeUpdate();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 查询数据
     */
    @Test
    public void queryAll() {
        try {
            Class.forName(DRIVER);
            connection = DriverManager.getConnection(CONNECT_URL, USERNAME, PASSWORD);
            SQL = "SELECT * FROM student";
            preparedStatement = connection.prepareStatement(SQL);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                System.out.println(resultSet.getInt("id") + "\t" + resultSet.getString("name") + "\t" + resultSet.getInt("age"));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 准备要执行的存储过程
     * CREATE OR REPLACE PROCEDURE GET_AGE_BY_NAME(
     *     V_NAME IN STUDENT.NAME%TYPE,
     *     V_AGE OUT STUDENT.AGE%TYPE
     * )
     * IS
     * BEGIN
     *     SELECT AGE INTO V_AGE
     *     FROM STUDENT
     *     WHERE  NAME = V_NAME;
     * END;
     */
    @Test
    public void executeProcedure() {
        try {
            Class.forName(DRIVER);
            connection = DriverManager.getConnection(CONNECT_URL, USERNAME, PASSWORD);
            SQL = "{CALL GET_AGE_BY_NAME(?, ?)}";
            callableStatement = connection.prepareCall(SQL);
            callableStatement.setString(1,"mike");
            callableStatement.registerOutParameter(2, OracleTypes.NUMBER);
            callableStatement.execute();
            int age = callableStatement.getInt(2);
            System.out.println("mike年龄为:" + age + "岁");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (callableStatement != null) {
                try {
                    callableStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 准备执行的存储函数
     * CREATE OR REPLACE FUNCTION COMPUTE_AVERAGE_AGE
     *     RETURN NUMBER
     * AS
     *     V_SUM NUMBER := 0;
     *     V_COUNT BINARY_INTEGER := 0;
     *     CURSOR C IS
     *         SELECT * FROM STUDENT;
     * BEGIN
     *     FOR V_STUDENT IN C LOOP
     *         V_SUM := V_SUM+V_STUDENT.AGE;
     *         V_COUNT := V_COUNT + 1;
     *     END LOOP;
     *     RETURN V_SUM/V_COUNT;
     * END;
     */
    @Test
    public void executeFunction() {
        try {
            Class.forName(DRIVER);
            connection = DriverManager.getConnection(CONNECT_URL, USERNAME, PASSWORD);
            SQL = "{?=call COMPUTE_AVERAGE_AGE()}";
            callableStatement = connection.prepareCall(SQL);
            callableStatement.registerOutParameter(1, OracleTypes.DOUBLE);
            callableStatement.execute();
            double average_age = callableStatement.getDouble(1);
            System.out.println("平均年龄为:" + average_age + "岁");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (callableStatement != null) {
                try {
                    callableStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}