java连接数据库

java连接数据库，基础：

void connectDB() {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        String url = "jdbc:mysql://localhost:3306/school";
        String user = "root";
        String password = "rootpassword";
        try {
            // 加载Driver类
            Class.forName("com.mysql.jdbc.Driver");
            // jdbc:mysql://localhost:3306/WL_DB?useUnicode=true&characterEncoding=gbk"
            String sql = "select name from test where id='001'";
            // 建立连接
            connection = DriverManager.getConnection(url, user, password);
            // statement
            statement = connection.createStatement();
            // 将返回值存入ResultSet中
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                System.out.println(resultSet.getString("name"));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                // 将其设置为null便于垃圾回收
                resultSet = null;
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                statement = null;
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                connection = null;
            }
        }
    }

 

使用PrepareStatement操作返回值：

String sql = "select name from test where id=? and name=?";
// 使用PrepareStatement可以对sql语句预处理
            preparedStatement = connection.prepareStatement(sql);
            // 为sql语句设置参数
            preparedStatement.setString(1, "001");
            preparedStatement.setString(2, "王子");
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                System.out.println(resultSet.getString("name"));
            }

 

使用CallableStatement调用数据库存储过程：

void callableStatement() {
        Connection connection = null;
        CallableStatement callableStatement = null;
        ResultSet resultSet = null;
        String url = "jdbc:mysql://localhost:3306/school";
        String user = "root";
        String password = "rootpassword";
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(url, user, password);
            String sql = "{call pro_test2(?,?)}";
            callableStatement = connection.prepareCall(sql);
            // 设置进程调用的第一个参数
            callableStatement.setInt(1, 2);
            // 如果进程调用的第二个参数是InOut类型，那么既要为第二个参数设置setInt()，又要registerOutParameter()
            callableStatement.setInt(2, 3);
            callableStatement.registerOutParameter(2, Types.TINYINT);
            callableStatement.execute();
            System.out.println(callableStatement.getInt(2));
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                resultSet = null;
            }
            if (callableStatement != null) {
                try {
                    callableStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                callableStatement = null;
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                connection = null;
            }
        }
    }