JSP details-Java Web Database Operations

JSP details-Java Web Database Operations
Introduction to JDBC technology for Java Web Database Operations

JDBC is an API of the Java program caozu database and a technology used to interact with the database. JDBC is the specification for java database operations. It consists of a group of classes and interfaces written in Java. It provides basic methods for database operations. To operate a database using JDBC, the database vendor must provide the database driver.

JDBC serves as a bridge between Java programs and databases. With JDBC, you can easily interact with various databases without having to specify a special access program for a specific database.

JDBC database connection process

Development Process of JDBC operands:

A. register the database driver

Before connecting to the database, you need to register the database driver class provided by the database vendor to the JDBC driver manager. Generally, you can load the database driver class to the JVM.

Example:

Class. forName ("com. mysql. jdbc. Driver ");

B. Construct the database connection URL

To establish a database connection, you need to build a database connection URL, which is specified by the database vendor. Different databases have different URLs, but all of them comply with a basic format. That is, "JDBC protocol + IP address or domain name + port + database name ". For example, the URL string of the MySQL database connection is "jdbc: mysql: // localhost: 3306/test"

C. Get the Connection object

After registering the database driver and building the database URL, you can use the driver manager to obtain the Connection of the database. the Connection object is a JDBC-encapsulated database Connection object. You can perform operations on the database only after the object is created. The method is as follows:

DriverManager. getConnection (url, username, password );

The DriverManager object is used to obtain the Connection object. The gerConnection () method of DriverManager creates the Connection object through the database Connection URL, database user name, and database Connection password.

Example:

Connect to the MySQL database through JDBC

Create the homepage index. jsp in the program, load the database driver in the page, and create a database connection. The key code is as follows:

<

Try {

Class. forName ("com.. mysql. jdbc. Driver); // load the database Driver and register it with the Driver manager.

String url = "jdbc: mysql: // localhost: 3306/test"; // database connection String

String username = "root"; // database username

String password = "111"; // Database password

Connection conn = DriverManager. getConnection (url, username, password); // create a Connection

If (conn! = Null) {// determines whether the database connection is null.

Out. println ("database connection successful"); // output connection information

Conn. close ();/close the database connection

} Else {

Out. println ("database connection failed ");

}

} Catch (ClassNotFoundException e ){

E. printStackTrace ();

} Catch (SQLException e ){

E. printStackTrace ();

}

JDBC APIConnection Interface

The Connection interface is located in the java. SQL package. It is a Connection session with a specific database. Only the connected objects of a specific database can access the database and operate data tables, views, and stored procedures in the database. The method declaration and description of the Connection interface are as follows:

Method Declaration	Description
Void close () throws SQLException	Immediately releases the JDBC resources occupied by the database Connection of the Connection object. This method is called immediately after the database is operated.
Void commit () throws SQLException	Commit the transaction and release all the database locks currently held by the Connectio object. when the transaction is set to manual commit mode, you need to call this method to submit the transaction.
Statement createStatement () throws SQLException	Create a Statement object to send SQL statements to the database. This method returns the Statement object.
Boolean getAutoCommit () throws SQLException	Used to determine whether the Connecction object is set to the automatic submission mode. This method returns a Boolean value.
DatabaseMetaData getMetaData () throws SQLException	Obtain the metadata DatabaseMetaData object of the database connected to the Connection object. The metadata includes information about the database table, supported SQL syntax, stored procedure, and Connection function.
Int getTransactionIsolation () throws SQLException	Obtains the current transaction isolation level of the Connection object.
Boolean isClosed () throws SQLException	Determine whether the Connection object is disconnected from the database. This method returns a Boolean value. Note that if the Connection object is disconnected from the database, you cannot operate the database through the Connection object.
Boolean isReadOnly () throws SQLException	Judge whether the Connection object hi is in read-only mode. This method returns a Boolean value.
PreparedStatement preparedStatement (String SQL) throws SQLException	Precompile and store parameterized SQL statements in the PreparedStatement object, and return the created PreparedStatement object.
Void releaseSavePoint (SavaPoint savepoint) throws SQLException	Remove the specified Savepoint and subsequent SavePoint objects from the current transaction.
Void rollback () throws SQLException	Roll back the transaction, for changes after the Savepoint object
Void setAutoCommit (boolean autoCommit) throws SQLException	Sets the submission mode of the Connection object. If the value of autoCommit is set to true, the Connection object is set to the automatic submission mode. If the value of the autoCommit object is set to false, the Connection object is in manual submission mode.
Void setReadOnly (boolean readOnly) throws SQLException	Set the Connection mode of the Connection object to read-only. This method is used to optimize the database.
Savepoint setSavepoint () throows SQLException	Create an untitled reservation point in the current transaction and return this reservation point object
Savepoint setSavepoint (String name) throws SQLException	Create a reserved vertex with the specified name in the current transaction and return the reserved vertex object.
Void setTransactionIsolation (int level) throws SQLException	Sets the transaction isolation level of the Connection object.

DriverManager class

The DriverManager class is mainly used between users and drivers. It is the management layer in JDBC. The DriverManager class can be used to manage drivers provided by database vendors and create new applications and connections between databases. The method declaration and description are as follows:

Method Declaration	Description
Public static void deregisterDriver (Driver driver) throws SQLException	Delete a driver from the DriverManager management list. The driver parameter is the driver object to be deleted.
Public static Connection getConnection (String url) throws SQLException	Establish a connection to the database based on the specified database connection URL. The parameter URL is the database connection URL.
Public static Connection getConnection (String url, Properties info) throws SQLException	Creates a database Connection based on the specified database Connection URL and database Connection attributes. The parameter url is the database connection URL, and the parameter info is the database connection attribute.
Public static Connection getConnection (String url, String user, String password) throws SQLException	Creates a database connection based on the specified database connection URL, database user name, and password.
Public static Enumeration GetDrivers ()	Obtains all drivers loaded in the current DriverManager. The returned value is Enumeration.
Public static void registerDriver (Driver driver) throws SQLException	Register a driver object with DriverManager. The parameter driver is the driver to be registered.

Statement Interface

After creating a database connection, you can use a program to call SQL statements to operate the database. In JDBC, the Statement interface encapsulates these operations. The Statement interface provides basic methods for executing statements and obtaining query results. The method declaration and description are as follows:

Method Declaration	Description
Void addBatch (String SQL) throws SQLException	Add an SQL Statement to the current command list of the Statement object. This method is used for SQL command batch processing.
Void clearBatch () throws SQLException	Clear the command list in the Statement object
Void close () throws SQLException	Release the database and JDBC resources of the Statement object immediately, instead of waiting for the object to be automatically closed.
Boolean execute (String SQL) throws SQLException	Execute the specified SQL statement. If the SQL statement returns the result, this method returns true; otherwise, false is returned.
Int [] executeBatch () throws SQLException	Submit a batch of SQL commands to the database for execution and return an array composed of update counts.
ResultSet executeQuery (String SQL) theows SQLException	Execute an SQL statement of the query type. This method returns the ResultSet object obtained by the query.
ExecuteUpdate int executeUpdate (String SQL) throws SQLException	Execute the SQL statement of DML type (insert, update, delete) in the SQL statement, and return the number of rows affected by the update.
Connection getConnection () throws SQLException	Obtain the Connection object of the generated Statement object
Boolean isClosed () throws SQLException	Determines whether the Statement object has been closed. If it is closed, the Statement object cannot be called to execute an SQL Statement. This method returns a Boolean value.

PreparedStatement Interface

The Statement interface encapsulates the method for JDBC to execute SQL statements. It can perform SQL Statement execution in Java, but in actual development, in SQL statements, variables in the program are often used as query condition parameters. Using the Statement interface is too cumbersome and has security defects. To solve this problem, the jdbc api encapsulates the Statement extension interface PreparedStaement.

The PreparedStatement interface inherits the methods in the Statement interface, and the PreparedStatement interface extends the execution of SQL statements with parameters. The placeholder "?" can be used for SQL statements used in the PreparedStatement Interface To replace the parameters in SQL statements, and then assign values to them. The method declaration and description of the PreparedStatement interface are as follows:

Method Declaration	Description
Void serBinaryStream (int parameterIndex, InputStream x) throws SQLException	Use streaming x as the parameter value in the SQL statement, and parameterIndex as the index of the parameter location.
Void setBoolean (int parameterIndex, boolean x) throws SQLException	Use the Boolean value x as the parameter in the SQL statement, and parameterInex as the index at the parameter location
Void setByte (int parameterIndex, byte x) throws SQLException	Use byte value x as the parameter value in the SQL statement, and parameterIndex as the index at the parameter location
Void setDate (int parameterIndex, Date x) throws SQLException	Use the java. SQL. Date value as the parameter value in the SQL statement. parameterIndex is the index of the parameter location.
Void setDouble (int parameterIndex, double x) throws SQLException	Take the double value as the food option in the SQL statement. parameterIndex is the index of the parameter location.
Void setFloat (int parameterIndex, float x) throws SQLException	Use float value x as the parameter value in the SQL statement, and paramterIndex as the index at the parameter location
Void setInt (int parameterIndex, int x) throws SQLException	Use the long value as the parameter value in the SQL statement. parameterIndex is the index at the parameter location.
Void setObject (int parameterIndex, Object x) throws SQLException	The Object x is used as the parameter value in the SQL statement, and parameterIndex is the index of the parameter location.
Void setShort (int parameterIndex, short x) throws SQLException	Use short value x as the parameter value in the SQL statement, and parameterIndex as the index at the parameter location
Void serString (int parameterIndex, String x) throws SQLException	Use String value x as the parameter value in the SQL statement, and parameterIndex as the index of the parameter location
Void setTimestamp (int parameterIndex, Timestamp x) throws SQLException	Use Timestamp value x as the parameter value in the SQL statement, and parameterIndex as the index of the parameter location

ResultSet Interface

The ResultSet interface is located in the java. SQL package and encapsulates the result set of the database query. The ResultSet object contains all rows that comply with SQL statements. It provides a set of getXXX () methods for data types in Java. These methods can be used to obtain data in each row. In addition, the ResultSet also provides the cursor function, allowing you to freely locate data in each row through the cursor. The methods are described as follows:

Method Declaration	Description
Boolean absolute (int row) throws SQLException	Move the cursor to the specified row number of the ResultSet object. The row parameter is the row number.
Void afterLast () throws SQLException	After moving the cursor to the last row of the ResultSet object, the method is invalid if the result set does not contain any rows.
Void beforeFirst () throws SQLException	Release the database and JDBC resources of the ResultSet object immediately
Void deleteRow () throws SQLException	Delete the current row from ResultSet and underlying database
Boolean first () throws SQLException	Move the cursor to the first line of the ResultSet object
InputStream getBinaryStream (String columnLable) throws SQLException	Obtains the value of the specified column in the current row of the ResultSet object as a byte stream. The parameter name columnLable is the column name.
Date getDate (String columnLable) throws SQLException	Obtain the value of the column specified in the current row of the ResultSet object using java. SQL. Date. The columnLable parameter is the column name.
Double getDouble (String columnLable) throws SQLException	Obtains the value of the specified column in the current row of the ResultSet object in double mode. The columnLable parameter is the column name.
Float getFloat (String columnLable) throws SQLException	Obtains the value of the specified column in the current row of the ResultSet object in float mode. The columnLable parameter is the column name.
Int getInt (String columnLable) throws SQLException	Obtains the name of the specified column in the current row of the ResultSet object in int mode. The columnLable parameter is the column name.
String getString (String columnLable) throws SQLException	Obtain the value of the specified column in the current row of the ResultSet object in String mode. The colnmnLable parameter is the column name.
Boolean isClosed () throws SQLException	Determines whether the current ResultSet object is closed
Boolean last () throws SQLException	Move the cursor to the last row of the ResultSet object
Booolean next () throws SQLException	Move the cursor One Line Backward
Boolean previous () throws SQLException	Move the cursor forward a row