Shulou(Shulou.com)05/31 Report--

This article introduces the "Java access database specific steps:" the relevant knowledge, in the actual case of the operation process, many people will encounter such a dilemma, then let the editor lead you to learn how to deal with these situations! I hope you can read it carefully and be able to achieve something!

1: specific steps for Java to access the database:

1 load (register) the database

Driver loading is to load the API provided by each database to access the database, load the JDBC driver, and register it in DriverManager. The database driver provided by each database is different. When you load the driver, you need to add the jar package to the lib folder. Take a look at the JDBC driver addition registration code of 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 ()

2 establish a link

Establishing the connection between databases is a necessary condition to access the database, just like the South-to-North Water transfer Project, if you want to transfer water, you must first connect the communicating rivers. Establishing a connection is also different for different databases. Let's take a look at the different ways for some mainstream databases to establish database connections and obtain Connection objects:

/ / 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)

3. Execute SQL statement

After the database connection is established, the next step is to prepare and execute the sql statement. The preparatory work is to establish the Statement object PreparedStatement object, such as:

/ / 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")

When you are ready to execute the sql statement, execute the sql statement:

String sql= "select * from users"

ResultSet rs=stmt.executeQuery (sql)

/ / execute dynamic SQL query

ResultSet rs=pstmt.executeQuery ()

/ / execute statements such as insert update delete, and define sql first

Stmt.executeUpdate (sql)

4 processing result set

Access the result recordset ResultSet object. For example:

While (rs.next)

{

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

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

}

5 close the database

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

Rs.close ()

Stmt.clost ()

Pstmt.close ()

Con.close ()

Two: JDBC transaction

What is a transaction:

First of all, let's talk about what kind of business. I think a transaction is a set of actions that operate on the database.

Transaction is one of the core concepts in modern database theory. If a set of processing steps either occurs or is not performed at all, we call the set of processing steps a transaction. When all the steps are executed as complete as an operation, we say that the transaction is committed. Because some or more of these steps fail, so that no steps are committed, the transaction must be rolled back to the original system state.

Transactions must follow the ACID principles established by ISO/IEC. ACID is an abbreviation for atomicity, consistency, isolation, and durability. The atomicity of the transaction means that any failure in the execution of the transaction will invalidate any changes made by the transaction. Consistency means that when a transaction fails, all data affected by the transaction should be restored to the state it was before it was executed. Isolation means that data is modified during the execution of a transaction and is not visible to other transactions until the transaction is committed. Persistence means ensuring that updates to committed transactions are not lost in the event of a system or media failure. Persistence is guaranteed by database backup and recovery.

JDBC transactions are controlled with Connection objects. The JDBC Connection interface (java.sql.Connection) provides two transaction modes: autocommit and manual commit. Java.sql.Connection provides the following ways to control transactions:

Public void setAutoCommit (boolean)

Public boolean getAutoCommit ()

Public void commit ()

Public void rollback ()

When using 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 database connection. A JDBC transaction cannot span multiple databases.

Three: java operates database connection pool

A good article was found in summarizing the database connection pool for java operations, so instead of making a specific summary, go directly to the address:

Http://www.blogjava.net/chunkyo/archive/2007/01/16/94266.html

Finally, a more classic code is attached:

[java]

View plaincopyprint?

Import java.sql.Connection

Import java.sql.DatabaseMetaData

Import java.sql.Driver

Import java.sql.DriverManager

Import java.sql.SQLException

Import java.sql.Statement

Import java.util.Enumeration

Import java.util.Vector

Public class ConnectionPool {

Private String jdbcDriver = ""; / / Database driver

Private String dbUrl = ""; / / data URL

Private String dbUsername = ""; / / Database user name

Private String dbPassword = ""; / / Database user password

Private String testTable = ""; / / the name of the test table to test whether the connection is available. There is no test table by default.

Private int initialConnections = 10; / / initial size of the connection pool

Private int incrementalConnections = 5 position / auto-increased size of the connection pool

Private int maxConnections = 50; / / maximum size of the connection pool

Private Vector connections = null; / / the vector that stores the database connection in the connection pool, initially null

/ / the objects stored in it are of PooledConnection type

/ * *

* Constructor

*

* @ param jdbcDriver String JDBC driver class string

* @ param dbUrl String Database URL

* @ param dbUsername String connection database user name

* @ param dbPassword String password of the user connecting to the database

*

, /

Public ConnectionPool (String jdbcDriver,String dbUrl,String dbUsername,String dbPassword) {

This.jdbcDriver = jdbcDriver

This.dbUrl = dbUrl

This.dbUsername = dbUsername

This.dbPassword = dbPassword

}

/ * *

* returns the initial size of the connection pool

*

* @ return the number of connections available in the initial connection pool

, /

Public int getInitialConnections () {

Return this.initialConnections

}

/ * *

* set the initial size of the connection pool

*

* @ param is used to set the number of connections in the initial connection pool

, /

Public void setInitialConnections (int initialConnections) {

This.initialConnections = initialConnections

}

/ * *

* returns the automatically increased size of the connection pool,

*

* @ return the automatically increased size of the connection pool

, /

Public int getIncrementalConnections () {

Return this.incrementalConnections

}

/ * *

* set the size of the connection pool to increase automatically

* @ param the automatically increased size of the connection pool

, /

Public void setIncrementalConnections (int incrementalConnections) {

This.incrementalConnections = incrementalConnections

}

/ * *

* returns the maximum number of available connections in the connection pool

* @ return maximum number of available connections in the connection pool

, /

Public int getMaxConnections () {

Return this.maxConnections

}

/ * *

* set the maximum number of connections available in the connection pool

*

* @ param sets the maximum number of connections available in the connection pool

, /

Public void setMaxConnections (int maxConnections) {

This.maxConnections = maxConnections

}

/ * *

* get the name of the test database table

*

* @ return name of the test database table

, /

Public String getTestTable () {

Return this.testTable

}

/ * *

* set the name of the test table

The name of the * @ param testTable String test table

, /

Public void setTestTable (String testTable) {

This.testTable = testTable

}

/ * *

*

* create a database connection pool with class members as the number of available connections in the connection pool

* values set in initialConnections

, /

Public synchronized void createPool () throws Exception {

/ / make sure the connection pool is not created

/ / if the connection pool has been created, the vector connections that saves the connection will not be empty

If (connections! = null) {

Return; / / if it has been created, returns

}

/ / instantiate the driver class instance specified in JDBC Driver

Driver driver = (Driver) (Class.forName (this.jdbcDriver) .newInstance ()

DriverManager.registerDriver (driver); / / Register the JDBC driver

/ / create a vector to save the connection, with 0 elements initially

Connections = new Vector ()

/ / create a connection according to the value set in initialConnections.

CreateConnections (this.initialConnections)

System.out.println ("Database connection pool created successfully!")

}

/ * *

* create the number of database connections specified by numConnections and put these connections

* put it into the connections vector

*

* @ param numConnections the number of database connections to create

, /

@ SuppressWarnings ("unchecked")

Private void createConnections (int numConnections) throws SQLException {

/ / cycle to create a specified number of database connections

For (int x = 0; x

< numConnections; x++) { // 是否连接池中的数据库连接的数量己经达到最大？最大值由类成员 maxConnections // 指出，如果 maxConnections 为 0 或负数，表示连接数量没有限制。 // 如果连接数己经达到最大，即退出。 if (this.maxConnections >

0 & & this.connections.size () > = this.maxConnections) {

Break

}

/ / add a new PooledConnection object to connections vector

/ / add a connection to the connection pool (in vector connections)

Try {

Connections.addElement (new PooledConnection (newConnection ()

} catch (SQLException e) {

System.out.println ("failed to create database connection!" + e.getMessage ())

Throw new SQLException ()

}

System.out.println ("Database connection has been created.")

}

}

/ * *

* create a new database connection and return it

*

* @ return returns a newly created database connection

, /

Private Connection newConnection () throws SQLException {

/ / create a database connection

Connection conn = DriverManager.getConnection (dbUrl, dbUsername, dbPassword)

/ / if this is the first time to create a database connection, check the database for what the database is allowed to support

/ / maximum number of customer connections

/ / connections.size () = = 0 indicates that no connection has been created

If (connections.size () = = 0) {

DatabaseMetaData metaData = conn.getMetaData ()

Int driverMaxConnections = metaData.getMaxConnections ()

/ / if the driverMaxConnections returned by the database is 0, the database does not have a maximum

/ / connection limit, or the maximum connection limit of the database is unknown

/ / driverMaxConnections is an integer returned, indicating the number of customer connections allowed by this database

/ / if the maximum number of connections set in the connection pool is greater than the number of connections allowed in the database, set the maximum number of connection pools

/ / the number of connections is the maximum number allowed by the database

If (driverMaxConnections > 0 & & this.maxConnections > driverMaxConnections) {

This.maxConnections = driverMaxConnections

}

}

Return conn; / / returns the new database connection created

}

/ * *

* return an available database connection by calling the getFreeConnection () function

* if no database connections are currently available, and more database connections cannot be created

* build (for example, a limit on the size of the connection pool). This function waits for a while before trying to obtain it.

*

* @ return returns an available database connection object

, /

Public synchronized Connection getConnection () throws SQLException {

/ / make sure the connection pool has been created

If (connections = = null) {

If return null; / / connection pool has not been created yet, null is returned.

}

Connection conn = getFreeConnection (); / / get an available database connection

/ / if no connections are currently available, that is, all connections are in use

While (conn = = null) {

/ / try again later

Wait (250)

Conn = getFreeConnection (); / / try again until an available connection is available, if

/ / getFreeConnection () returns null

/ / indicates that available connections cannot be obtained after creating a batch of connections.

}

Return conn;// returns available connections obtained

}

/ * *

* this function returns an available database connection from the connection pool vector connections, if

* No database connection is currently available. This function is based on the incrementalConnections setting.

* creates several database connections and places them in the connection pool.

* if all connections are still in use after creation, return null

* @ return returns an available database connection

, /

Private Connection getFreeConnection () throws SQLException {

/ / get an available database connection from the connection pool

Connection conn = findFreeConnection ()

If (conn = = null) {

/ / if there are no available connections in the current connection pool

/ / create some connections

CreateConnections (incrementalConnections)

/ / re-check the pool to see if there are available connections

Conn = findFreeConnection ()

If (conn = = null) {

/ / if no available connection is available after creating the connection, return null

Return null

}

}

Return conn

}

/ * *

* find all connections in the connection pool and find an available database connection

* if no connection is available, return null

*

* @ return returns an available database connection

, /

Private Connection findFreeConnection () throws SQLException {

Connection conn = null

PooledConnection pConn = null

/ / get all the objects in the connection pool vector

Enumeration enumerate = connections.elements ()

/ / iterate through all the objects to see if there are available connections

While (enumerate.hasMoreElements ()) {

PConn = (PooledConnection) enumerate.nextElement ()

If (! pConn.isBusy ()) {

/ / if this object is not busy, get its database connection and set it to busy

Conn = pConn.getConnection ()

PConn.setBusy (true)

/ / Test whether this connection is available

If (! testConnection (conn)) {

/ / if this connection is no longer available, create a new connection

/ / and replace the connection object that is not available. If creation fails, return null

Try {

Conn = newConnection ()

} catch (SQLException e) {

System.out.println ("failed to create database connection!" + e.getMessage ())

Return null

}

PConn.setConnection (conn)

}

Break; / / an available connection has been found, exit

}

}

Return conn;// returns the available connection found

}

/ * *

* Test whether a connection is available, if not, turn it off and return false

* otherwise, return true is available.

*

* @ param conn database connection to be tested

* @ return returns true to indicate that the connection is available, and false to indicate that it is not available

, /

Private boolean testConnection (Connection conn) {

Try {

/ / determine whether the test table exists

If (testTable.equals ("")) {

/ / if the test table is empty, try using the setAutoCommit () method of this connection

/ / to determine whether the connection is available (this method is only available in some databases, if not

/ / throw an exception). Note: the method of using test tables is more reliable

Conn.setAutoCommit (true)

} else {/ / use a test table to test when there is a test table

/ / check if this connection is valid

Statement stmt = conn.createStatement ()

Stmt.execute ("select count (*) from" + testTable)

}

} catch (SQLException e) {

/ / the above throws an exception. The connection is no longer available. Close it and return false.

CloseConnection (conn)

Return false

}

/ / connection is available. Return true

Return true

}

/ * *

* this function returns a database connection to the connection pool and makes the connection idle.

* all database connections obtained using connection pooling should be returned when this connection is not used.

*

* @ param needs to return to the connection object in the connection pool

, /

Public void returnConnection (Connection conn) {

/ / make sure the connection pool exists. If the connection is not created (does not exist), return it directly.

If (connections = = null) {

System.out.println ("connection pool does not exist, cannot return this connection to connection pool!")

Return

}

PooledConnection pConn = null

Enumeration enumerate = connections.elements ()

/ / iterate through all the connections in the connection pool to find the connection object to be returned

While (enumerate.hasMoreElements ()) {

PConn = (PooledConnection) enumerate.nextElement ()

/ / first find the connection object to be returned in the connection pool

If (conn = = pConn.getConnection ()) {

/ / found, set this connection to idle

PConn.setBusy (false)

Break

}

}

}

/ * *

* refresh all connection objects in the connection pool

*

, /

Public synchronized void refreshConnections () throws SQLException {

/ / ensure that the connection pool has been innovated

If (connections = = null) {

System.out.println ("connection pool does not exist and cannot be refreshed!")

Return

}

PooledConnection pConn = null

Enumeration enumerate = connections.elements ()

While (enumerate.hasMoreElements ()) {

/ / get a connection object

PConn = (PooledConnection) enumerate.nextElement ()

/ / wait 5 seconds if the object is busy, then refresh directly after 5 seconds

If (pConn.isBusy ()) {

Wait (5000); / / wait 5 seconds

}

/ / close this connection and replace it with a new connection.

CloseConnection (pConn.getConnection ())

PConn.setConnection (newConnection ())

PConn.setBusy (false)

}

}

/ * *

* close all connections in the connection pool and empty the connection pool.

, /

Public synchronized void closeConnectionPool () throws SQLException {

/ / make sure the connection pool exists. If it does not exist, return

If (connections = = null) {

System.out.println ("connection pool does not exist and cannot be closed!")

Return

}

PooledConnection pConn = null

Enumeration enumerate = connections.elements ()

While (enumerate.hasMoreElements ()) {

PConn = (PooledConnection) enumerate.nextElement ()

/ / if you are busy, wait 5 seconds

If (pConn.isBusy ()) {

Wait (5000); / / wait 5 seconds

}

/ / close it directly after 5 seconds

CloseConnection (pConn.getConnection ())

/ / remove it from the connection pool vector

Connections.removeElement (pConn)

}

/ / leave the connection pool empty

Connections = null

}

/ * *

* close a database connection

*

* @ param database connection to be closed

, /

Private void closeConnection (Connection conn) {

Try {

Conn.close ()

} catch (SQLException e) {

System.out.println ("error closing database connection:" + e.getMessage ())

}

}

/ * *

* make the program wait for a given number of milliseconds

*

* @ param given number of milliseconds

, /

Private void wait (int mSeconds) {

Try {

Thread.sleep (mSeconds)

} catch (InterruptedException e) {

}

}

/ * *

*

* classes used internally to save connection objects in the connection pool

* there are two members in this class, one is the connection to the database, and the other indicates whether the connection is

* logo in use.

, /

Class PooledConnection {

Connection connection = null;// database connection

Boolean busy = false; / / the flag of whether this connection is in use. It is not in use by default.

/ / Constructor, which tells a PooledConnection object based on a Connection

Public PooledConnection (Connection connection) {

This.connection = connection

}

/ / returns the connection in this object

Public Connection getConnection () {

Return connection

}

/ / set the connection of this object

Public void setConnection (Connection connection) {

This.connection = connection

}

/ / whether the object connection is busy

Public boolean isBusy () {

Return busy

}

/ / the connection of the setting object is busy.

Public void setBusy (boolean busy) {

This.busy = busy

}

}

}

= =

This example is based on the POSTGRESQL database.

Please adjust according to the actual database when using it.

The calling method is as follows:

① ConnectionPool connPool

= new ConnectionPool ("org.postgresql.Driver"

, "jdbc:postgresql://dbURI:5432/DBName"

, "postgre"

, "postgre")

② connPool .createPool ()

Connection conn = connPool .getConnection ()

This is the end of the content of "specific steps for Java to access the database:" Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.