Java JDBC Basics

First, JDBC Common interface, Class introduction

JDBC provides a unified, database-independent API to execute SQL commands. The APIs commonly used classes and interfaces are as follows:

DriverManager

Management of the JDBC-driven service class, mainly through its access to connection database links, commonly used methods are as follows:

public static synchronized Connection getconnection (string url, string user, String password) throws Exception;

This method obtains the connection of the database corresponding to the URL.

Connection Common database operation methods:

Statement Createstatement throws SQLException: The method returns a Statement object.

PreparedStatement preparestatement (String sql) throws SQLException; The method returns a precompiled statement object.

    The SQL statement is submitted to the database for precompilation.

CallableStatement preparecall (String sql) throws SQLException: This method returns the CallableStatement object,

    This object is used for calls to stored procedures.

The above three methods return the statement object that executes the SQL statement, PreparedStatement, CallableStatement object is the subclass of statement,

SQL statements can be executed only after you have obtained statement.

About the connection control transaction method:

SavePoint setsavepoint (): Create a Save point

SavePoint setsavepoint (String name): Create a save point with a name

void settransactionisolation (int level): SET TRANSACTION isolation Levels

void rollback (): ROLLBACK TRANSACTION

void rollback (SavePoint savepoint): Rollback to the specified save point

void Setautocommit (Boolean autocommit): Turn off autocommit, open transaction

void commit (): Commit Transaction

Statement

An API interface for executing SQL statements that can execute DDL, DCL statements, or DML statements.

You can also execute SQL query statements, which are commonly used when executing a query statement that returns a result set:

ResultSet executeQuery (String sql) throws SQLException: This method is used to execute a query statement and return the ResultSet object corresponding to the query result.

        This method is used only for query statements.

int executeupdate (String sql) throws SQLException: This method is used to execute DML statements and returns the number of rows affected;

        The method can also execute DDL, which performs DDL return 0;

Boolean execute (String sql) throws SQLException: The method can execute any SQL statement, if the first result is a ResultSet object after execution,

        Returns true if the first result after execution is the number of rows affected or there is no result, false is returned;

PreparedStatement

Precompiled statement Object, PreparedStatement is a sub-interface of statement, which allows the database to precompile SQL (usually referred to as parameter SQL) statements,

It is better to change only the SQL command parameters at a later time to prevent the database from compiling SQL statements every time. and relative to statement,

When you execute an SQL statement using PreparedStatement, you do not have to re-pass the SQL statement because it has precompiled the SQL statement.

However, PreparedStatement needs to pass in the parameter value for the compiled SQL statement, so it is compared to the following method:

void setxxx (int index, value) requires different methods depending on the type of parameter values passed in by the method.

The type of value passed in depends on the parameters of the SQL statement passed in.

ResultSet

void Close () throws SQLException: Release, close ResultSet object

Boolean absolute (int row): Moves the result set to the first row and, if the row is a negative number, moves to the penultimate line.

        The method returns true if the record pointer that you moved to points to a valid record;

void Beforefisrt (): The resultset record pointer is positioned before the first row, which is the resultset result set record pointer

        Initial state: The starting position of the record pointer is before the first row.

Boolean first (): Positions the ResultSet record pointer to the top row. If the moved record pointer points to a valid record, the method returns True.

Boolean previous (): Positions the ResultSet record pointer to the previous row, which returns true if the moved record pointer points to a valid record.

Boolean next (): Positions the ResultSet record pointer to the next row. Returns true if the moved record pointer points to a valid record.

Boolean last (): Positions the ResultSet record pointer to the final row. Returns true if the moved record pointer points to a valid record.

void Afterlast (): Positions the ResultSet's record pointer after the last row.

Note: Only next moves are supported before JDK1.4, and one location is moved at a time. To JDK1.5 can be arbitrary positioning.

Ii. JDBC Programming steps

The following JDBC programming steps are outlined below:

1. Load Database Driver

Class.forName (Driverclass)
The above dirverclass is the class path string corresponding to the database driver class, depending on the different database vendors to provide a different driver.

2. Get the link to the database via DriverManager

Drivermanager.getconnection (string url, stirng user, String pass)
When using DriverManager to get a link, you need to pass in three parameters: the URL of the data volume, the user name, and the password.

3, create statement object through Connection object, connection Create statement method as follows three:

Createstatement () Creates a basic statement object.
Preparestatement (String sql): Creates a precompiled statement object based on the incoming SQL statement.
Preparecall (String sql): Create CallableStatement object based on incoming SQL statement

4, Statement Execute SQL statement, statement has three methods to execute SQL statement:

Execute: Can execute any SQL statement, single more troublesome

Executeupdate: DML, DDL statements can be executed. The execution DML returns the number of rows affected by the SQL statement, and the execution DDL returns 0;

ExecuteQuery: Only query statements are executed, and ResultSet objects representing query results are returned after execution.

5, operation result set, for resultset

Main move pointer and get value

Next, previous, first, last, beforefrist, afterlast, Absolute and so on move the pointer method.

GetXXX gets the value of the move pointer to the row, specific column, index. Using the column name as the parameter to get the value is good readability, using the index as the parameter to get good performance.

III. JDBC Execution SQL statement

1. Executeupdate executing DDL, DML statements

Statement provides execute, Executeupdate, executequery Three methods of execution, the following with the executeupdate to execute DDL, DML statements,

The executeupdate execution DDL return value is 0, and the number of records after which DML is returned is affected.

2. Execute Execute SQL statement

When we know that the SQL statement is the completion of the modification statement, we know to use the Executeupdate statement to complete the operation;

If the SQL statement is the completion of the query operation, we will use ExecuteQuery to complete.

If we don't know what the SQL statement is doing, we can use the Execute method to do it.

When we execute the SQL statement using the Execute method of the Statement object, the return is a Boolean value, which indicates whether the statement can return the ResultSet object.

So, how do you tell if it is a ResultSet object? Here's how:

Getresultset (): Gets the ResultSet object returned by the statement execution query statement

Getupdatecount (): Gets the number of rows affected by the statement execution modification statement

3. preparestatement Execute SQL statement

When we execute a SQL statement when we operate the database. Only its arguments are different, and the SQL statements are the same.

We can use placeholders to set our parameter information, which is the placeholder in preparestatement? Use? The position of the substitution parameter.

Insert into table values (?, ' abc ',?);

Placeholders support only preparestatement, and statement do not support placeholders. Preparestatement is a precompiled SQL statement,

The placeholder is then replaced with a parameter. and statement can't do it.

The Preparestatement object also has the three methods of Execute, executeupdate, ExecuteQuery, but none of the three methods are required to pass a parameter.

Just use Preparestatement to set the parameters of the placeholder, by using SETXXXX (index, value) to set the parameter information.

The efficiency of preparestatement is higher than that of statement.

The preparestatement parameter can be set without stitching strings, while statement setting parameter information requires manual concatenation of strings.

Splicing strings easy to operate program errors, reduce readability, maintenance, and program performance degradation. and preparestatement directly set the parameters

Information reduces the complexity of programming. And it can be placed in SQL injection. Because it is set parameter information through the Setxxx method,

And statement is a concatenation of strings that can easily cause SQL injection.

To sum up, preparestatement than statement have the following advantages:

Precompiled SQL statements for better performance

Easy programming without the need to splice SQL statements

Prevents SQL statement injection and provides better security

4. CallableStatement Call Stored Procedure

A call to a stored procedure can create a CallableStatement object by CallableStatement The Preparecall method of the Connection object.

Then pass in the SQL statement of the stored procedure and call the stored procedure in the following format:

{Call Proc_name (?,?,?)}

The above? is a placeholder that represents the passed parameter.

Stored procedures have incoming parameters, outgoing parameters. The incoming parameter is a parameter that the procedure must pass in, and the parameter value can be set by the Setxxx method.

The outgoing parameter needs to be set by the program, and the Registeroutparameter method of the CallableStatement object can be used to

Register output parameters, Cs.registeroutparameter (3, types.string);

After the setup is complete, when you call the stored procedure to get the output parameter values, you can do so by using the GetXXX method.

Iv. operation result set (ResultSet)

JDBC uses resultset to manage the result set, and the action resultset can point to different row records by moving its pointer, and then take out the current record. And resultset can complete the update record, also provides the resultsetmetadata to obtain the object related information.

1, removable, updatable resultset

The related methods of resultset are described earlier, and a series of methods can be used to move the record pointers.

such as: Absolute, Previous, next, first, last, Beforefirst, Afterlast and other methods.

ResultSet is not supported by default, and if you want resultset to complete the update operation, you must pass in some parameters when creating statement or Preparestatement.

The connection object can pass two parameters when creating a statement or preparestatement:

A, ResultsetType: Controls the type of resultset, the parameter has the following three values:

    A, resultset.type_forward_only the constant control record pointer can only move forward. Default value for Jdk1.4

    B, resultset.type_scroll_insensitive: This constant controls the free movement of the record pointer (scrollable result set),

        But the underlying data changes do not affect the contents of the result set resultset

    C, resultset.type_scroll_sensitive: This constant controls the free movement of the record pointer, but the impact of the underlying data changes the contents of the result set ResultSet

B, resultSetConcurrency: Controls the concurrency type of the resultset, which can receive the following two values:

    A, Resultset.concur_read_only: This constant indicates that ResultSet is read-only concurrency mode

    B, resultset.concur_updatable: This constant indicates that ResultSet is updating the concurrency pattern

Create scrollable, updatable resultset by setting parameter settings at Preparestatement, statement,

Then through the RS Updatexxx method to complete a column update value settings, through the Updaterow to commit the changes.

2. Binary BLOB data processing in resultset

Blob types are typically used to store files, such as pictures, audio, and video files. Convert files to binary saved in the database,

The binary data can be restored to a file when it is removed.

If you want to insert a picture into a database, you obviously cannot directly set the SQL parameter stitching string to insert. Because binary constants cannot be represented.

However, inserting BLOB type data into data can be done with preparestatement, through the setbinarystatement of the Preparestatement object

The GetBytes method passes parameters to the binary input stream, or the data can be fetched directly using the Blob object's method.

3, using ResultSetMetaData operation ResultSet result set

In the result set we query the data return, we don't know the data type, the number of data columns that the result set holds.

Then we can use ResultSetMetaData to read the resultset information.

The ResultSetMetaData object can be obtained by ResultSet's GetMetaData () method.

The resultset can then be manipulated using the ResultSetMetaData object method, which is commonly used as follows:

int getColumnCount (): Returns the number of column names for resultset

int getcolumntype (int column): Returns the type of the specified index

String getcolumnname (int column): Returns the column name for the specified index

V. JDBC Transaction

1. Introduction of affairs

A transaction is a logical execution unit that consists of one or more steps that make up the sequence of operations, which either executes all or discards execution.

Four characteristics of a transaction: atomicity (atomicity), consistency (consistency), isolation (isoiation), and persistence (durability)

atomicity ( atomicity ): The transaction applies the smallest execution unit and cannot be divided. Is the smallest logical execution that cannot be divided in a transaction.

Consistency ( Consistency ): The execution of a transaction results in a state where the database must be changed from one consistent state to another.

Isolation Line ( isoiation ): The execution of each transaction does not interfere with each other, and the internal operations of any one transaction are isolated to other concurrent transactions. That is, the concurrent execution of a transaction cannot see the middle state of the other, and the concurrently executed transactions cannot affect each other.

Persistence ( Durability ): Continuity is also known as persistence (persistence), which means that once a transaction is committed, any changes made to the data are recorded in the permanent memory, usually in the physical database.

Typically, the transactions of a database involve statements such as:

A set of DML (data munipulation, Language) statements that will maintain good consistency after the set of DML statements are modified;

    Statement of the operation table, such as inserting, modifying, deleting, etc.;

A DDL (data definition Language) statement that operates on the language of the data object, with Create, alter, drop.

A DCL (Data Control Language) statement with the GRANT and REVOKE statements in its main language.

DDL and DCL statements can have at most one, because they all result in immediate commit of the transaction.

When all of the database operations that the office contains are executed successfully, the transaction should be committed and the modifications will be made permanent.

There are two ways to commit a transaction: Show commit and Auto commit.

Show submissions: Commit with Commit

Auto Commit: Executes DLL or DCL, or program exits normally

When any of the database operations contained in a transaction fails to execute, the transaction should be rolled back (rollback) so that all modifications made in the transaction are invalidated.

There are two ways to rollback a transaction: show rollback and automatic rollback.

Show rollback: Using rollback

Automatic rollback: System error or forced exit

2. Support for JDBC Things

JDBC's connection also supports things, and connection turns on autocommit by default, which is closing things.

In other words, each SQL statement execution is immediately committed to the database and is permanently in effect and cannot be manipulated.

Turn off automatic submission of connection and open things. Connection's Setautocommit method: Connection.setautocommit (FALSE);

Get the pattern of Things by Connection.getautocommit ().

When we open things, the database operations done in the current connection are not immediately committed to the database and need to call connection's Commit method.

If a statement execution fails, you can call rollback to roll back and forth.

Note: If connection encounters an unhandled SqlException exception, the system exits abnormally and the transaction is automatically rolled back.

If the program catches the exception, you need to show the rollback transaction in exception handling.

Connection provides a way to set the intermediate savepoint of a transaction: Setsavepoint, there are 2 ways to set an intermediate point:

SavePoint Setsavepoint (): Creates an unnamed intermediate point in the current transaction and returns the SavePoint object for that intermediate point.

SavePoint setsavepoint (String name): Creates an intermediate point with the specified name in the current transaction and returns the SavePoint object for that intermediate point

Typically Setsavepoint (String name) sets the name of the intermediate point, and the transaction rollback is not rolled back by the name of the intermediate point, but rather by the intermediate point object.

Setting the name is just a better way to differentiate between intermediate point objects, and the connection rollback (SavePoint savepoint) method can be used to roll back to the specified intermediate point.

3. JDBC Batch Update

A batch update is a multi-SQL statement that can be executed simultaneously as a batch of operations and committed simultaneously.

Batch updates need to be backed up by the data base, and you can investigate DatabaseMetaData's Supportsbatchupdates method to see if the underlying database supports bulk updates.

A bulk update also requires the creation of a statement object, which is then gathered together by the Addbatch method of the object to combine multiple SQL statements.

These SQL statements are then executed by the ExecuteBatch of the statement object, with the following code:

Statement sm = conn.createstatement ();

Sm.addbatch (SQL);

Sm.addbatch (SQL2);

Sm.addbatch (SQL3);

...

Execute multiple SQL statements at the same time

Sm.executebatch ();

Execution ExecuteBatch will return an array of int[], because using statement to execute DDL, DML will return the value of an int,

Executing multiple DDL, DML will also return an int array. The SELECT query statement is not allowed in the bulk update, and an exception occurs when the program appears.

If you want to batch update the correct, bulk complete, you need a single transaction, if there is a failure during the bulk update process, you need to roll back to the original state with the transaction.

If you want to achieve this effect, you need to close the automatic commit of the transaction, and when the bulk update is finished committing the transaction, the transaction will be rolled back if an exception occurs.

The connection is then reverted to autocommit mode.

Public int[] ExecuteBatch (string[] sql) throws SQLException {

    int[] result = NULL;

    conn = Dbhelper.getconnection ();

    try {

        Get the current connection commit mode

        Boolean autocommit = Conn.getautocommit ();

        Turn off auto-commit mode

        Conn.setautocommit (FALSE);

        SM = Conn.createstatement ();

        for (String S:sql) {

            Sm.addbatch (s);

        }

        Perform a batch update

        result = Sm.executebatch ();

        Commit a transaction

        Conn.commit ();

        Restore commit Mode

        Conn.setautocommit (autocommit);

    } catch (Exception e) {

        E.printstacktrace ();

        Conn.rollback ();

    } finally {

        if (SM! = null) {

            Sm.close ();

        }

        Dbhelper.close ();

    }

    return result;

}

VI. Analysis of database data

1. Using DatabaseMetaData to analyze database data

JDBC provides databasemetadata to encapsulate database information corresponding to a database connection, and obtains the object through the connection GetMetaData method.

The DatabaseMetaData interface is typically implemented by the database-driven provider, which is used to let the user know the underlying information of the database.

This interface can be used to understand the implementation of the database at the bottom, so as to facilitate switching between multiple databases.

For example, you can use the Supportscorrelatedsubquenes method to see if the database underlying can take advantage of the associated subquery.

or call the Supportsbatchupdates method to see if a batch update is supported.

Most of the DatabaseMetaData are returned as ResultSet objects, which can be obtained by resultset the GetString and Getint of the object.

The DatabaseMetaData method needs to pass a xxxpattern pattern string, which is the filter condition, and the general pass is the content of%, _, etc. in SQL.

If a null is passed, no filtering is made.

Java JDBC Basics