利用java.sql包--访问和处理数据库数据

• java.sql

The java.sql package in Java provides the API for interacting with relational databases using JDBC (Java Database Connectivity). JDBC is a standard Java API that allows Java programs to connect to and interact with various database management systems (DBMS) using SQL (Structured Query Language).
The java.sql package contains several important interfaces and classes that facilitate database connectivity and operations. Here are some of the key classes and interfaces in the java.sql package:
Connection: Represents a connection to a specific database. It provides methods for creating statements, managing transactions, and controlling connection properties.
Statement: Represents a simple SQL statement that is sent to the database for execution. It provides methods for executing SQL queries, updates, and other operations.
PreparedStatement: A subinterface of Statement that represents a precompiled SQL statement with placeholders for input parameters. It allows efficient execution of parameterized queries and provides protection against SQL injection.
CallableStatement : a subinterface of PreparedStatement that specifically handles calling stored procedures or functions.
ResultSet: Represents a table of data resulting from an SQL query. It provides methods to navigate through the result set and retrieve data from the columns.
ResultSetMetaData: Provides information about the columns in a ResultSet, such as column names, types, and properties.
DriverManager: A utility class that manages the available JDBC drivers. It provides methods for registering and retrieving JDBC drivers, as well as establishing database connections.
SQLException: The exception class used by JDBC to report errors related to database operations.
These are just a few examples of the classes and interfaces in the java.sql package. By utilizing these classes and interfaces, you can establish connections to databases, execute SQL statements, retrieve and process query results, and handle exceptions that may occur during database operations.
It's important to note that the java.sql package provides the core JDBC functionality, but the actual database-specific drivers, such as the Microsoft SQL Server JDBC driver (com.microsoft.sqlserver.jdbc.SQLServerDriver), need to be obtained from the respective database vendors and added to the classpath to connect to specific databases.

// Use the connection to execute SQL statements
Once you have established a connection using the com.microsoft.sqlserver.jdbc.SQLServerDriver, you can use the connection object to execute SQL statements. Here's an example of executing a simple SQL query:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SQLServerExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:sqlserver://localhost:1433;databaseName=mydatabase;";
        String username = "myusername";
        String password = "mypassword";

        try {
            // Load the SQL Server JDBC driver
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

            // Establish a connection
            Connection connection = DriverManager.getConnection(jdbcUrl, username, password);

            // Create a statement
            Statement statement = connection.createStatement();

            // Execute a query
            String sql = "SELECT * FROM employees";
            ResultSet resultSet = statement.executeQuery(sql);

            // Process the query results
            while (resultSet.next()) {
                String employeeName = resultSet.getString("name");
                int employeeAge = resultSet.getInt("age");
                System.out.println("Name: " + employeeName + ", Age: " + employeeAge);
            }

            // Close the result set, statement, and connection
            resultSet.close();
            statement.close();
            connection.close();
        } catch (ClassNotFoundException e) {
            System.out.println("SQL Server JDBC driver not found");
        } catch (SQLException e) {
            System.out.println("Failed to connect to the database: " + e.getMessage());
        }
    }
}
In the example above, after establishing the connection, we create a Statement object using the connection.createStatement() method. With the Statement object, we execute a SQL query using the executeQuery() method and store the results in a ResultSet. Then, we iterate over the ResultSet to retrieve and process the query results.
Remember to handle exceptions appropriately, such as catching SQLException and ClassNotFoundException, and closing the result set, statement, and connection after you're done with them to release any resources they hold.

• Statement 

In Java, the Statement interface is part of the JDBC API and is used to execute SQL statements and interact with a database. It provides methods to execute SQL queries, updates, and other database operations. The Statement interface represents a simple SQL statement that doesn't include any parameters.
Here are some key methods provided by the Statement interface:
executeQuery(String sql): Executes an SQL SELECT query and returns a ResultSet object containing the query results.
executeUpdate(String sql): Executes an SQL INSERT, UPDATE, or DELETE statement and returns the number of affected rows.
execute(String sql): Executes any SQL statement and returns a boolean value indicating the type of the first result (true if it is a ResultSet, false if it is an update count or there are no results).
addBatch(String sql): Adds an SQL statement to the current batch for batch processing.
executeBatch(): Executes the current batch of statements and returns an array of update counts for each statement.
close(): Releases any resources associated with the Statement object.
Here's an example that demonstrates the usage of Statement to execute a simple SQL query:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class StatementExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:sqlserver://localhost:1433;databaseName=mydatabase;";
        String username = "myusername";
        String password = "mypassword";

        try {
            // Load the SQL Server JDBC driver
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

            // Establish a connection
            Connection connection = DriverManager.getConnection(jdbcUrl, username, password);

            // Create a statement
            Statement statement = connection.createStatement();

            // Execute a query
            String sql = "SELECT * FROM employees";
            ResultSet resultSet = statement.executeQuery(sql);

            // Process the query results
            while (resultSet.next()) {
                String employeeName = resultSet.getString("name");
                int employeeAge = resultSet.getInt("age");
                System.out.println("Name: " + employeeName + ", Age: " + employeeAge);
            }

            // Close the result set, statement, and connection
            resultSet.close();
            statement.close();
            connection.close();
        } catch (ClassNotFoundException e) {
            System.out.println("SQL Server JDBC driver not found");
        } catch (SQLException e) {
            System.out.println("Failed to connect to the database: " + e.getMessage());
        }
    }
}
In the example, after creating a Statement object using connection.createStatement(), we execute an SQL query by calling executeQuery() with the SQL statement. The query results are retrieved as a ResultSet, and we iterate over the result set to process the data.
Remember to handle exceptions appropriately, such as catching SQLException and ClassNotFoundException, and close the result set, statement, and connection after you're done using them to release any resources they hold.

• PreparedStatement

PreparedStatement is a subinterface of the Statement interface in the java.sql package. It represents a precompiled SQL statement that can accept input parameters. The use of PreparedStatement is recommended when you need to execute a SQL statement multiple times with different parameter values, as it provides better performance and security compared to regular Statement objects.
Here's an example of how to use PreparedStatement to execute a parameterized SQL query:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class PreparedStatementExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:sqlserver://localhost:1433;databaseName=mydatabase;";
        String username = "myusername";
        String password = "mypassword";

        try {
            // Load the SQL Server JDBC driver
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

            // Establish a connection
            Connection connection = DriverManager.getConnection(jdbcUrl, username, password);

            // Create a PreparedStatement
            String sql = "SELECT * FROM employees WHERE age > ?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);

            // Set parameter values
            int minimumAge = 30;
            preparedStatement.setInt(1, minimumAge);

            // Execute the query
            ResultSet resultSet = preparedStatement.executeQuery();

            // Process the query results
            while (resultSet.next()) {
                String employeeName = resultSet.getString("name");
                int employeeAge = resultSet.getInt("age");
                System.out.println("Name: " + employeeName + ", Age: " + employeeAge);
            }

            // Close the result set, statement, and connection
            resultSet.close();
            preparedStatement.close();
            connection.close();
        } catch (ClassNotFoundException e) {
            System.out.println("SQL Server JDBC driver not found");
        } catch (SQLException e) {
            System.out.println("Failed to connect to the database: " + e.getMessage());
        }
    }
}
In the example, we create a PreparedStatement by calling connection.prepareStatement(sql), where sql is the parameterized SQL query. The SQL query contains a placeholder (?) for the parameter value.
We then set the value for the parameter using preparedStatement.setInt(index, value), where index is the position of the parameter in the query (starting from 1) and value is the actual value.
Finally, we execute the query by calling preparedStatement.executeQuery(), retrieve the query results using a ResultSet, and process the data as needed.
By using a PreparedStatement, you can easily reuse the same SQL statement with different parameter values, improving performance and protecting against SQL injection attacks.

• CallableStatement 

CallableStatement is another subinterface of the PreparedStatement interface in the java.sql package. It represents a precompiled SQL statement that can be used to execute stored procedures or functions in a database.
Stored procedures are precompiled database objects that contain one or more SQL statements and are typically used to encapsulate complex database operations. Callable statements allow you to invoke these stored procedures and retrieve the results, if any.
Here's an example of how to use CallableStatement to execute a stored procedure:

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;

public class CallableStatementExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:sqlserver://localhost:1433;databaseName=mydatabase;";
        String username = "myusername";
        String password = "mypassword";

        try {
            // Load the SQL Server JDBC driver
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

            // Establish a connection
            Connection connection = DriverManager.getConnection(jdbcUrl, username, password);

            // Prepare the stored procedure call
            String storedProcedure = "{call getEmployeeCount(?)}";
            CallableStatement callableStatement = connection.prepareCall(storedProcedure);

            // Register the output parameter
            callableStatement.registerOutParameter(1, Types.INTEGER);

            // Execute the stored procedure
            callableStatement.execute();

            // Retrieve the output parameter value
            int employeeCount = callableStatement.getInt(1);
            System.out.println("Employee Count: " + employeeCount);

            // Close the statement and connection
            callableStatement.close();
            connection.close();
        } catch (ClassNotFoundException e) {
            System.out.println("SQL Server JDBC driver not found");
        } catch (SQLException e) {
            System.out.println("Failed to connect to the database: " + e.getMessage());
        }
    }
}
In the example above, we prepare a CallableStatement by calling connection.prepareCall(storedProcedure), where storedProcedure is the SQL statement representing the stored procedure call. The SQL statement uses the syntax {call procedure_name(?)}, where procedure_name is the name of the stored procedure, and ? represents an input or output parameter.
We then register the output parameter using callableStatement.registerOutParameter(parameterIndex, sqlType), where parameterIndex is the index of the parameter in the SQL statement (starting from 1), and sqlType is the SQL type of the parameter.
Next, we execute the stored procedure by calling callableStatement.execute(). If the stored procedure has any output parameters, we can retrieve their values using methods such as getInt(), getString(), etc., based on the parameter index.
Finally, we close the CallableStatement and the connection.
By using CallableStatement, you can execute stored procedures and retrieve any output parameters or result sets returned by the stored procedure.
getEmployeeCount
The getEmployeeCount is a hypothetical example of a stored procedure name used in the previous code snippet. It represents a stored procedure that returns the count of employees in a database.
In the example code, the stored procedure is invoked using a CallableStatement object with the SQL statement "{call getEmployeeCount(?)}". The question mark (?) is a placeholder for an output parameter, which will hold the count of employees returned by the stored procedure.
The line callableStatement.registerOutParameter(1, Types.INTEGER) registers the output parameter at index 1 with the SQL type Types.INTEGER, indicating that the expected result is an integer value.
After executing the stored procedure with callableStatement.execute(), the code retrieves the output parameter's value using callableStatement.getInt(1). The getInt(1) method retrieves the value of the output parameter at index 1, which corresponds to the employee count returned by the stored procedure.
In a real-world scenario, the getEmployeeCount stored procedure might be implemented in the database, and the code would execute it to retrieve the employee count. The actual implementation of the stored procedure and the logic for retrieving the employee count would depend on the specific database system and the requirements of the application.
 getEmployeeCount stored procedure
To provide an example of a possible implementation of the getEmployeeCount stored procedure, let's assume a Microsoft SQL Server database. Here's a hypothetical implementation using T-SQL:

CREATE PROCEDURE getEmployeeCount
    @employeeCount INT OUTPUT
AS
BEGIN
    SELECT @employeeCount = COUNT(*) FROM employees;
END
In this example, the getEmployeeCount stored procedure is created with one output parameter @employeeCount. The procedure retrieves the count of rows in the employees table using the COUNT(*) aggregate function and assigns the result to the output parameter.
When executing the stored procedure from Java using CallableStatement, the @employeeCount output parameter is registered as an output parameter using callableStatement.registerOutParameter(1, Types.INTEGER). The stored procedure is then executed using callableStatement.execute(), and the employee count can be retrieved using callableStatement.getInt(1).
Please note that the actual implementation of the getEmployeeCount stored procedure may vary based on your specific database schema, table names, and requirements. The above example provides a general outline of how the stored procedure can be created to retrieve the employee count.

• ResultSetMetaData

The ResultSetMetaData interface in the java.sql package provides information about the columns in a ResultSet object. It allows you to retrieve details such as the column names, types, sizes, and other properties of the result set's columns.
Here's an example of how to use ResultSetMetaData to retrieve column information from a ResultSet:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class ResultSetMetaDataExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:sqlserver://localhost:1433;databaseName=mydatabase;";
        String username = "myusername";
        String password = "mypassword";

        try {
            // Load the SQL Server JDBC driver
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

            // Establish a connection
            Connection connection = DriverManager.getConnection(jdbcUrl, username, password);

            // Create a statement
            Statement statement = connection.createStatement();

            // Execute a query
            String sql = "SELECT * FROM employees";
            ResultSet resultSet = statement.executeQuery(sql);

            // Retrieve the ResultSetMetaData
            ResultSetMetaData metaData = resultSet.getMetaData();

            // Get column count
            int columnCount = metaData.getColumnCount();
            System.out.println("Column Count: " + columnCount);

            // Iterate over columns
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnName(i);
                String columnType = metaData.getColumnTypeName(i);
                int columnSize = metaData.getColumnDisplaySize(i);
                System.out.println("Column Name: " + columnName + ", Type: " + columnType + ", Size: " + columnSize);
            }

            // Close the result set, statement, and connection
            resultSet.close();
            statement.close();
            connection.close();
        } catch (ClassNotFoundException e) {
            System.out.println("SQL Server JDBC driver not found");
        } catch (SQLException e) {
            System.out.println("Failed to connect to the database: " + e.getMessage());
        }
    }
}
In the example above, after executing a query and obtaining a ResultSet, we retrieve the ResultSetMetaData object using resultSet.getMetaData(). This provides us with metadata about the columns in the result set.
We can then use various methods of ResultSetMetaData to retrieve column information. For example, getColumnCount() returns the number of columns in the result set. We can iterate over the columns using a loop, and for each column, retrieve details such as the column name (getColumnName()), column type (getColumnTypeName()), and column size (getColumnDisplaySize()).
Please note that the specific methods available in ResultSetMetaData may vary depending on the JDBC driver and database you are using. The example above demonstrates common methods, but you can refer to the JDBC API documentation for a comprehensive list of methods provided by ResultSetMetaData.