Shulou(Shulou.com)06/01 Report--

The original text comes from the brick-moving worker. Please indicate the source if you need to reprint it.

We commonly use Java JDBC, so here is an example of JDBC to summarize some of the common Java database operations.

I. Database driver loading

First of all, we know that JDBC is based on java database driver as the main implementation mechanism for database operations, each type of database has a different database driver. Common database drivers are loaded as follows: (Drivers can be downloaded from the official website)

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

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

2. mysql database

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

DB2 database

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

4. SQL Server database

Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver"); //Sql Server7.0/2000 Database Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); //Sql Server2005/2008 Database

II. Establishing connections

Database operations need to establish a connection with the database first, and then read the data we need from the database.

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

String url="jdbc:oracle:thin:@localhost:1521:orcl"; String user="root"; String password="root"; Connection conn=DriverManager.getConnection(url,user,password);

2. mysql database

String url="jdbc:mysql://localhost:3306/testDB? user=root&password=root&useUnicode=true&characterEncoding=gb2312"; Connection conn=DriverManager.getConnection(url);

DB2 database

String url="jdbc:db2://localhost:5000/sample"; String user="amdin" String password=-""; Connection conn=DriverManager.getConnection(url,user,password);

4. SQL Server database

String url="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=pubs"; String user="sa"; String password=""; Connection conn=DriverManager.getConnection(url,user,password);

III. Executing SQL statements

Database operations based on conditions

//Create Statement object Statement stmt=conn.createStatement(); //Create PreparedStatement object String sql="select * from user where userName=? and password=? "; PreparedStatement pstmt=Conn.prepareStatement(sql); pstmt.setString(1,"root"); pstmt.setString(2,"root"); //After you are ready, you can execute sql statements, execute sql statements: sql String ="select * from users"; ResultSet rs=stmt.executeQuery(sql); //Execute dynamic SQL query ResultSet rs=pstmt.executeQuery(); //Execute insert update delete and other statements, first define sql stmt.executeUpdate(sql);

IV. Processing result sets

Processing the data from the database to get what we need

while(rs.next) { System.out.println("Your first field content is: "+rs.getString("Name")); System.out.println("Your second field content is: "+rs.getString(2)); }

5. Close the database connection

After operating the database, you must remember to close the database connection, otherwise it will not cause waste of resources. When a certain number of connections are reached, the speed of reading the database will decrease.

Close ResultSet, Statement, PreparedStatement, and Connection objects in turn to release occupied resources.

rs.close(); stmt.clost(); pstmt.close(); con.close();

VI. Database services

Transaction is one of the core concepts in modern database theory. A set of processing steps is said to be a transaction if either all or none of them occur. When all the steps are executed as one operation, we say the transaction is committed. Because one or more of these steps fails, resulting in no step being committed, the transaction must roll back to the original system state.

The transaction must comply with the ACID principles established by ISO/IEC.

What is ACID Principle?

ACID stands for atomicity, consistency, isolation, and durability.

Atomicity of a transaction means that any failure in the execution of the transaction will invalidate any modifications made by the transaction.

2. Consistency means that when a transaction fails, all data affected by the transaction should be restored to its pre-transaction state.

3. Isolation means that modifications to data during transaction execution are invisible to other transactions until the transaction commits.

4. Persistence means ensuring that updates to committed transactions are not lost when a system or media failure occurs. Persistence is ensured by 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()

With JDBC transaction demarcation, you can combine multiple SQL statements into a single transaction. One disadvantage of JDBC transactions is that the scope of the transaction is limited to a single database connection. A JDBC transaction cannot span multiple databases.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.