Current location - Education and Training Encyclopedia - Education and training - (High score) Urgently seek the source code of JAVA student information management system connecting to the database.
(High score) Urgently seek the source code of JAVA student information management system connecting to the database.
Database connection (connection)

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.