[Reprinted] java database operations, reprinted java Database

[Reprinted] java database operations, reprinted java Database

Knowledge is required for accessing almost every slightly formed program in the database. How to access the database efficiently is also a key point of our learning. Today's task is to summarize the methods for accessing the database using java and related APIs, JDBC is the main method for java to access the database. It is the application interface used in java to regulate how client programs access the database. It provides methods such as querying and updating data in the database, the following is a summary.JDBC

I. Detailed steps for accessing the database using Java:

1. Load (Register) The database

Driver loading is to load the database access API provided by each database into our program, load the JDBC driver, and register it into DriverManager. The database drivers provided by each database are different, when loading the driver, add the jar package to the lib folder. Let's take a look at the code for registration of JDBC drivers in some mainstream databases:

// Oracle8/8i/9iO database (thin Mode)

Class. forName ("oracle. jdbc. driver. OracleDriver"). newInstance ();

// SQL Server7.0/2000 database Class. forName ("com. microsoft. jdbc. sqlserver. SQLServerDriver ");

// SQL Server2005/2008 database Class. forName ("com. microsoft. sqlserver. jdbc. SQLServerDriver ");

// DB2 database

Class. froName ("com. ibm. db2.jdbc. app. DB2Driver"). newInstance ();

// MySQL database Class. forName ("com. mysql. jdbc. Driver"). newInstance ();

// PostgreSQL database Class. forName ("com. postgresql. jdbc. Driver"). newInstance ();

2Create Link　

Establishing a connection between databases is a necessary condition for accessing the database. Just like water diversion from South-to-North Water Diversion, the first thing to do is to connect the communication rivers. Establishing a Connection is different for different databases. The following describes how to establish a database Connection in some mainstream databases to obtain a Connection object:

// Oracle8/8i/9i Database (thin Mode)

String url = "jdbc: oracle: thin :@ localhost: 1521: orcl ";

String user = "scott ";

String password = "tiger ";

Connection conn = DriverManager. getConnection (url, user, password );

// SQL Server7.0/2000/2005/2008 database

String url = "jdbc: microsoft: sqlserver: // localhost: 1433; DatabaseName = pubs ";

String user = "sa ";

String password = "";

Connection conn = DriverManager. getConnection (url, user, password );

// DB2 database

String url = "jdbc: db2: // localhost: 5000/sample ";

String user = "amdin"

String password = -"";

Connection conn = DriverManager. getConnection (url, user, password );

// MySQL database

String url = "jdbc: mysql: // localhost: 3306/testDB? User = root & password = root & useUnicode = true & characterEncoding = gb2312 ";

Connection conn = DriverManager. getConnection (url );

 

 

// PostgreSQL database

String url = "jdbc: postgresql: // localhost: 5432/postgres ";

String user = "s"

String password = "s ";

Connection conn = DriverManager. getConnection (url, user, password );

 

3. Execute SQL statements

After the database connection is established, some preparation work and SQL statements are executed. The preparation work is to create the Statement object PreparedStatement, for example:

// Create a Statement object

Statement stmt = conn. createStatement ();

// Create a PreparedStatement object

String SQL = "select * from user where userName =? And password =? ";

PreparedStatement pstmt = Conn. prepareStatement (SQL );

Pstmt. setString (1, "admin ");

Pstmt. setString (2, "liubin ");

After the preparation is complete, you can execute the SQL statement:

String SQL = "select * from users ";

ResultSet rs1_stmt.exe cuteQuery (SQL );

// Execute dynamic SQL query

ResultSet rs1_pstmt.exe cuteQuery ();

// Execute insert update delete and other statements to define the SQL

Stmt.exe cuteUpdate (SQL );

4. processing result set

Access result record set ResultSet object. For example:

While (rs. next)

{

Out. println ("your first field content is:" + rs. getString ("Name "));

Out. println ("your second field content is:" + rs. getString (2 ));

}

5. Shut down the database

Close the ResultSet, Statement, PreparedStatement, and Connection objects in sequence to release the resources occupied. For example:

Rs. close ();

Stmt. clost ();

Pstmt. close ();

Con. close ();

Ii. JDBC transactions

What is a transaction:

First, let's talk about what transactions. In my opinion, transactions are a set of database operations.

Transactions are one of the core concepts in modern database theory. If a group of processing steps either occur or are not executed, we call the group processing steps as a transaction. When all the steps are fully executed like a single operation, we call the transaction committed. Because some or more of the steps fail to be committed, the transaction must roll back to the initial system status.

Transactions must follow the ACID principles established by ISO/IEC. ACID is short for atomicity, consistency, isolation, and durability. The atomicity of a transaction indicates that any failure during the transaction execution will invalidate any modification made by the transaction. Consistency indicates that when the transaction fails, all data affected by the transaction should be restored to the status before the transaction is executed. Isolation indicates the modification of data during transaction execution, which is invisible to other transactions before the transaction is committed. Durability indicates that when the system or media fails, the updates of committed transactions cannot be lost. Durability is ensured through database backup and recovery.

JDBC transactions are controlled by Connection objects. The JDBC Connection interface (java. SQL. Connection) provides two transaction modes: Automatic commit and manual commit. Java. SQL. Connection provides the following methods to control transactions:
Public void setAutoCommit (boolean)
Public boolean getAutoCommit ()
Public void commit ()
Public void rollback ()
When you use the JDBC transaction interface, you can combine multiple SQL statements into one transaction. One disadvantage of JDBC transactions is that the transaction scope is limited to a database connection. A jdbc transaction cannot span multiple databases.