Java Database programming

1. Basic process of database programming

(1) Load the database vendor driver

For example: for MySQL database, class.forname ("Com.mysql.jdbc.Driver");

(2) Get database Connection object

Connection Connection = drivermanager.getconnection (Url,username,password);

(3) Creating a Database Execution statement object

Statement Statement = Connection.createstatement ();

(4) Execute SQL statement to get result set object

ResultSet result = Statement.executequery ("SELECT * from Tb_test");

(5) Closing the database connection

After you use the ResultSet, statement, and connection objects, you need to call their close methods in time to free up resources. When the statement object is closed, all ResultSet objects obtained by it are automatically freed, and the statement object obtained by the close Connection object is automatically released. Therefore, the Close method that calls connection can release all resources associated with it.

Because the database connection must be closed after it is used, the Close method is typically called in the finally clause.

2. JDBC Gets the self-increment primary key

Many times, we insert a record where the primary key is the self-increment column, and we may need to obtain this primary key shortly thereafter, and JDBC provides a unified interface:

Statement.executeupdate ("INSERT ....", Statement.return_generated_keys);

ResultSet result = Statement.getgeneratedkeys ();

Where the parameter Statement.return_generated_keys specifies to return the key that was just inserted, and then calls the Getgeneratedkeys method to return the keys.

3. Execution of transactions

A transaction represents a combination of atomic SQL statements that either execute successfully or are not executed at all.

By default, the connection object we create is in autocommit mode, that is, an SQL UPDATE statement is executed, and the update database is immediately committed. When executing a transaction, we need to set the connection object to not be automatically committed: Connection.setautocommit (false);

Then we can execute multiple SQL UPDATE statements, and these updates to the database will not take effect until the commit method of the connection object is called, and if an error occurs in the execution of these SQL statements, the Rollback method is called to roll back the database.

4. Database Connection Pool

When you are programming the database in a multithreaded environment, you need to pay attention to the problem of synchronization.

Using a global Connection object without synchronization can lead to various concurrency problems, while synchronizing this global connection is often inefficient. We can use a local connection object, create a new connection each time the database task is executed, shut down immediately after use, which can lead to frequent creation of close database connections, and less efficient.

A better way to manage database connections using database connection pooling, database connection pooling can effectively take advantage of idle database connections. When you want to connect to a database, request a connection object from the database connection pool, which may be newly created, or it may have been previously created. When you close a connection object, it is not really disconnected from the database, but instead is placed in an idle connection to the database connection pool.

One of the most important issues in implementing a database connection pool is how to not actually close the database connection when the Close method of calling connection is resolved. We can use the Java Dynamic Agent to solve this problem.

Since connection is an interface, and the specific type of connection returned by the Drivermanager.getconnection method is not known, we cannot implement the new Close method directly through overrides. We can, of course, implement the Conneciton interface ourselves, but then we need to implement many of the interface methods, but in fact we just need to change the implementation of the Close method.

The proxy class in the Java Reflection Library provides us with the ability to construct a class that implements some interfaces and to specify a call processor (implementing the Invocationhandler Interface) to intercept calls to all interface methods.

Here is an easy to implement database connection pool:

 Public classdbconnectionpool{PrivateString Jdbcurl; PrivateString username; PrivateString password; PrivateLinkedlist<connection>Pool; //Save idle Connection Objects  PublicDbconnectionpool (String driver,string jdbcurl)throwsClassNotFoundException { This. Jdbcurl =Jdbcurl;          Class.forName (driver); Pool=NewLinkedlist<connection>(); }         PublicDbconnectionpool (String driver,string jdbcurl,string username,string password)throwsClassNotFoundException { This. Jdbcurl =Jdbcurl;  This. Username =username;  This. Password =password;       Class.forName (driver); Pool=NewLinkedlist<connection>(); }
    //In order to be able to use in multi-threaded environment, some methods use synchronized keyword synchronization  Public synchronizedConnection getconnection ()throwsSQLException {Connection conn =NULL; if(Pool.size () > 0) Conn=Pool.poll (); Else        {         FinalConnection Connection =drivermanager.getconnection (jdbcurl, username, password);
         //Create proxy object and cast to connection interface type, Invocationhandler interface represents call processor Conn= (Connection) proxy.newproxyinstance (NULL,NewClass[]{connection.class},NewInvocationhandler () { PublicObject Invoke (Object proxy, Method method, object[] args)throwsThrowable {if(Method.getname () = = "Close" && method.getparametertypes (). length = = 0) {Pool.add (Connection) proxy); //When executing the Close method, replace the logic of the original method return NULL; }                Else //When executing other methods, keep the logic of the original method                   returnMethod.invoke (Connection,args);        }                      }); }                          returnConn; }     Public synchronizedConnection getconnection (string Username, string password)throwsSQLException {Connection conn =NULL; if(Pool.size () > 0) Conn=Pool.poll (); Else        {            FinalConnection Connection =drivermanager.getconnection (jdbcurl, username, password); Conn= (Connection) proxy.newproxyinstance (NULL,NewClass[]{connection.class},NewInvocationhandler () { PublicObject Invoke (Object proxy, Method method, object[] args)throwsThrowable {if(Method.getname () = = "Close" && method.getparametertypes (). length = = 0) {Pool.add (Connection) proxy); return NULL; }                    Else                      returnMethod.invoke (Connection,args);        }                              }); }                returnConn; }         Public synchronized voidReduce ()throwsSQLException {intSize =pool.size ();  while(Pool.size () > (SIZE/2) ) Pool.poll (). Close (); }         Public synchronized voidClose ()throwsSQLException { while(Pool.size () > 0) Pool.poll (). Close (); }}

Java Database programming