Database connection
There are two ways to obtain database connection, one is through the DriverManager class, and the other is using the DataSource interface. Both methods provide a getConnection method that users can call to return the database connection after processing them in the program.
DriverManager class
Data source interface
Connecting interface
JDBC website
JDBC:& lt； Sub-agreement & gt:& ltsubname & gt
Driver registration method
(1) calls the Class.forName method.
(2) Set the jdbc.drivers system attribute.
Driver manager method
All methods in the DriverManager class are static methods, so when using the methods of the DriverManager class, there is no need to generate an instance.
Driver manager
GetConnection method
The function is to get a database connection, and the prototype is as follows:
Public static connection getConnection (string url)
Throwing SQLException
Public static connection getConnection (string url, string user, string password)
Throwing SQLException
Public static connection getConnection (string url, attribute information)
Throwing SQLException
Use the getConnetion method of DriverManager.
class . forname(" sun . JDBC . odbc . JDBC odbc driver ")；
connection conn = driver manager . get connection
(“jdbc:odbc:sqlserver”、“sa”、“sa”)；
Using the method of setting jdbc.drivers system properties
Java-djdbc . drivers = sun . JDBC . odbc . jdbcodbc driver test.java
Data source interface
……
//Find the data source from the context and get the database connection.
context CTX = new initial context()；
data source ds =(data source)CTX . lookup(" SQL server ")；
connection conn = ds . getconnection()；
//Query all records in the database.
statement stmt = conn . create statement()；
ResultSet RS = stmt . execute query(" SELECT * FROM student ")；
……
Connecting interface
The connection interface represents the established database connection and is the core content of the whole JDBC. According to the function, the methods in the connection interface can be divided into three categories:
Generate database statements
Manage database transactions
Get database information
Generate database statements
JDBC divides database statements into three types:
Generate statement statement:
Connection.createStatement()
Generate PreparedStatement statement:
Contact. Prepare statement ()
Generate callable statements:
Contact. prepareCall()
Manage database transactions
By default, JDBC treats database statements as complete transactions. You can turn off the default transaction management:
Public void set autocommit (Boolean autocommit) throws SQLException.
Setting the value of autoCommit to false will turn off automatic transaction management mode.
After the transaction is executed, the transaction should be committed:
Public void commit () throws SQLException.
You can cancel the transaction:
PublicoVoidRollback () throws SQLException.
The second lecture the fourth part
Database statement
Database statement
There are three types of JDBC database statements * * *:
Statement:
Statement statement is mainly used to embed common SQL statements, including query, update, insert and delete.
Prepare the report:
A PreparedStatement is called a prepared statement, which temporarily specifies some parameters in the SQL statement, but it is unified when executed.
Callable statement:
CallableStatement is used to execute the stored procedures of the database.
Statement statement
ExecuteQuery method
ExecuteUpdate method
Execution method
Closing method
ExecuteQuery method
ExecuteQuery method is mainly used to execute SQL query statements (QL) that produce a single result set, that is, SELECT statements. The prototype of the executeQuery method is as follows:
The public result set executeQuery(String sql) throws SQLException.
ExecuteUpdate method
The executeUpdate method is mainly used to execute INSERT, Update and DELETE statements, that is, data manipulation statements (DML) of SQL.
The executeUpdate method can also execute SQL data definition language (DDL) statements similar to the CREATE TABLE and DROP TABLE statements.
The return value of the executeUpdate method is an integer indicating the number of rows affected (that is, the update count). For statements that do not operate on specific rows, such as CREATE TABLE or DROP TABLE, the return value of executeUpdate is always zero.
Execution method
The execute method is used to perform:
Returns multiple result sets
Multiple update count
Or a combination of the two.
Execution method
Returns multiple result sets: first call the getResultSet method to get the first result set, and then call the appropriate getter method to get the values in it. To get the second result set, you need to call the getMoreResults method first, and then call the getResultSet method.
Returns multiple update counts: first, call the getUpdateCount method to get the first update count. Then call getMoreResults and call getUpdateCount again to get the later update count.
I don't know what to return: if the result is a ResultSet object, the execute method returns true；; If the result is an int type, it means that the result is an update count or the statement executed is a DDL command.
Execution method
To illustrate the results returned by the execute method, the following is a code example:
Stmt.execute (query);
while (true) {
int row = stmt . getupdatecount()；
//If it is an update count
if(row & gt； 0) {
System.out.println ("The number of rows updated is"+row ");
stmt . getmoresults()；
Continue;
}
Execution method
//If it is DDL command or 0 update
if (row == 0) {
System.out.println ("Do not update, otherwise the SQL statement is DDL statement!" );
stmt . getmoresults()；
Continue;
}
//If it is a result set
ResultSet rs = stmt.getResultSet
If (rs! = null) {
while (rs.next()) {
//Processing result set
. . .
}
stmt . getmoresults()；
Continue;
}
Break;
}
Prepared report statement
When logging in to a website or forum:
Use statement statement
statement stmt = conn . create statement()；
ResultSet rs = stmt.executeQuery
("Select password from user information.
Where id = userid ");
Using the PreparedStatement statement
prepared statement pstmt = conn . prepare statement
("Select password from user information.
Where id=? ");
pstmt.setString( 1，userId)；
Prepared report statement
Commonly used setter method
Public void set Boolean (int parameter index, boolean x) throws SQLException.
Public void set byte (int parameter index, byte x) throws SQLException.
Public void set short (int parameter index, short x) throws SQLException.
Public void set int (int parameter index, int x) throws SQLException.
Public void set long (int parameter index, long x) throws SQLException.
Public void set float (int parameter index, float x) throws SQLException.
Public void set double (int parameter index, double x) throws SQLException.
Public void set bigdecimal (int parameter index, BigDecimal x) throws SQLException.
Public void setstring (int parameter index, String x) throws SQLException.
Public void set bytes (int parameter index, byte[] x) throws SQLException.
Public void set date (int parameter index, Date x) throws SQLException.
public void setTime(int parameter index，Time x)hrows SQLException；
Public void set timestamp (int parameter index, Timestamp x) throws SQLException.
Prepared report statement
The PreparedStatement interface is an extension of the Statement interface, which rewrites the executeQuery method, executeUpdate method and execute method.
The public result set executeQuery () throws SQLException.
Public int executeUpdate () throws SQLException.
The public boolean execute () throws SQLException.
Callable statement statement
CallableStatement statement is created by the prepareCall method of the connection interface, and string parameters need to be passed in when it is created. These parameters are in the following form:
{Call procedure name [(? , ? ,...)]}
{? = call procedure_name[(？ , ? ,...)]}
{Call Process Name}
Callable statement statement
Question marks are placeholders for parameters, and there are two types of parameters * * *:
Write parameter
Output parameter
The IN parameter is set using the setter method.
Use the registerOutParameter method to set the OUT parameter.
Callable statement statement
callable statement cstmt = con . prepare call
("{Call getTestData (? , ? )}");
cstmt.registerOutParameter
( 1，Java . SQL . types . tinyint)；
cstmt.registerOutParameter
(2，java.sql.Types.DECIMAL，3)；
cs TMT . execute query()；
byte x = cstmt . get byte( 1)；
java.math.BigDecimal n =
cstmt.getBigDecimal(2，3)；
The second lecture the fifth part
Result set
Result set
In order to facilitate the processing of query results, JDBC has specially defined an interface, which is the ResultSet interface. The ResultSet interface provides a way to access the results of database queries, and the objects pointed to by this interface are usually called result sets.
There are two ways to get the result set. One is to directly execute the query statement and store the results on the result set object. The other is not to store the returned results, but to call the getResultSet method of the database statement to return the result set when necessary.
Result set
Result set pointer
Because the returned ResultSet may contain multiple data records, the ResultSet interface provides a way to poll all data records of the result set. The result set automatically maintains a pointer to the current data record, which initially points to the previous position of the first row. The next method is to move the pointer forward.
Result set
Result set attribute
By default, the result set is not updatable, and the pointer of the result set can only move forward. That is to say, after getting a result set, the user can only read backwards from the first record to the last record, but can't jump to any record or return to the previous record. Moreover, this polling of the result set can only be done once, and the pointer cannot be reset to the initial position for multiple polling.
Result set
Result set attribute
type
be complicated by
efficiency
When generating a database statement, the properties are set by passing the corresponding parameters to the generation method, but when the result set has been returned, its properties cannot be changed.
There are three ways to generate statements from the result set.
The public statement createStatement () throws SQLException.
Public statement creation statement
(int resultSetType，int resultSetConcurrency)
Throwing SQLException
Public statement creation statement
(int resultSetType，int resultSetConcurrency，
int resultSetHoldability)
Throwing SQLException
Result set
There are six ways to generate a PreparedStatement * * *.
Public prepared statement prepared statement (String SQL) throws SQLException.
public prepared statement prepare statement(String SQL，int autoGeneratedKeys)
Throwing SQLException
public prepared statement prepare statement(String SQL，int[] columnIndexes)
Throwing SQLException
public prepared statement prepare statement(String SQL，int resultSetType，
int resultSetConcurrency)
Throwing SQLException
public prepared statement prepare statement(String SQL，int resultSetType，
int resultSetConcurrency，
int resultSetHoldability)
Throwing SQLException
Public prepared statement prepared statement (string sql. String[] column name)
Throwing SQLException
Result set
There are three ways to generate CallableStatement statement * * *.
Public callable statement prepared call (string sql)
Throwing SQLException
Public callable statement ready to call
(string sql, int resultSetType,
int resultSetConcurrency)
Throwing SQLException
Public callable statement ready to call
(string sql, int resultSetType,
int resultSetConcurrency，
int resultSetHoldability)
Throwing SQLException
Result set
Result set type
There are three types of result sets * * *. The result set of TYPE_FORWARD_ONLY can only move the pointer forward, and the result sets of type _ scroll _ inductive and TYPE_SCROLL_SENSITIVE can move the pointer at will. The difference between the latter two types is that the former is not sensitive to changes elsewhere (static), while the latter is sensitive to changes elsewhere (dynamic view).
Result set
Result set type
For the result set that can move the pointer at will, the methods that can be used to move the pointer are:
Next and previous:
Absolute and Relative: The parameter can be positive or negative.
AfterLast，beforeFirst，last and first:
Result set
Result set concurrency
There are two kinds of concurrency of result sets. The result set of CONCUR_READ_ONLY is read-only and cannot be updated. The result set of CONCUR_UPDATABLE can be updated by the update method.
The ResultSet interface provides a set of update methods to update the data in the result set. These methods are the same as the setter methods defined in the PreparedStatement interface, and also correspond to types. All update methods start with Update.
All update methods have two parameters. The first parameter is used to specify the updated column, which can be the column name or the column serial number. The second parameter indicates that the value of the column will be updated.
Result set
Result set concurrency
Statement stmt = conn.createStatement
(result set. TYPE_SCROLL_SENSITIVE，ResultSet。 CONCUR _ updatable);
ResultSet RS = stmt . execute query(" SELECT * FROM student "+
"Grade =2, Math & gt60 and Physics & gt60 and"+
"Chemistry & gt60 and English & gt60 and Chinese & gt60");
while(rs.next()){
rs.updateString("grade "，" 3 ")；
RS . update row()；
}
Result set
Result set validity
The validity of the result set refers to whether the result set is automatically closed after calling the commit method of the connection interface. So it has only two optional values, namely, HOLD_CURSORS_OVER_COMMIT and CLOSE_CURSORS_AT_COMMIT. The former means that the result set will not be closed after calling the commit method; The latter means closing the result set.
Result result set
Getter method of result set
The ResultSet interface also provides a set of getter methods to return the property values of the current record. They all start with get, followed by the data type. For example, if you want to return a column value of type float, you should call the getFloat method. Each type of getter method has two forms, with the same name but different parameters. Both forms of getter methods have only one parameter. The parameter of the first form of the getter method is a String type, which is used to specify the name of the column. Another form of getter method parameter is int type, which is used to specify the serial number of the column.