Common database Connection management class __ Database

1. Database Connection Class

This class can provide database connectivity for all JDBC-based applications. Supports both physical direct and data sources, while the thread of the connection object is secure so that all objects invoked within the same request range can share a connection to improve program performance. Connection the creation and destruction process, no client program is responsible. When you need to use a connection, you call the Getconnection () method directly, and the shutdown process is done by configuring the filter in Web.xml. The following are detailed code and sample code

Code 1:dbutils database Connection management class

/** * filename:dbutils * * filetype:final Class * * date:2008 year April 13 * Author: A Little Red * * email:cpucpa@163.com * * DESC Ription: Universal Database Connection Processing Class * * */package com.qhit.system.db.conn; Import Java.io.FileOutputStream; Import Java.io.PrintWriter; Import java.sql.Connection; Import Java.sql.DriverManager; Import java.sql.PreparedStatement; Import Java.sql.ResultSet; Import java.sql.Statement; Import Java.util.HashMap; Import Java.util.Map; Import Java.util.ResourceBundle; Import Javax.naming.InitialContext; Import Javax.sql.DataSource; Public final class Dbutils {//resource file name final private static String option_file_name= "dboption";//connection Source Tag (true-datasource or false drivermanager.getconnection ()) private static Boolean user_datasource=false; Data source object private static DataSource datasource=null; Connection string private static string Url=null; Drive string private static string Driver=null; User name private static String user=null; Password private static String password=null; Record connection open and close private static printwriter log; PRivate final static threadlocal<connection> threadlocal=new ThreadLocal (); Private final static Map tem=new HashMap (); static {try {//Generate resource file resolves object resourcebundle res=resourcebundle.getbundle (option_file_name);//Whether the user uses the data source if ( Res.getstring ("User_datasource"). Trim (). Equalsignorecase ("true") {//Connection Source tag update true user_datasource=true;// Gets the data source name String datasource_name=res.getstring ("Datasource_name") from the resource file. Trim (); Initializes the context environment InitialContext ict=new InitialContext (); Generate data source objects through Jndi datasource= (DataSource) ict.lookup (Datasource_name); else {//By reading the resource file initial database information url=res.getstring ("url"). Trim (); Driver=res.getstring ("Driver"). Trim (); user= Res.getstring ("USERNAME"). Trim (); Password=res.getstring ("password"); Load Database driver Class.forName (driver); //Generate user connection using log file log = new PrintWriter (New FileOutputStream ("D://connuse.txt", True), true); catch (Exception e) {e.printstacktrace ();}} public static Connection getconnection () throws Exception {Connection conn=threadlocal.get (); if (Conn==null | | conn. isclosed ()) {if (User_datasource) {//re-create connection conn=datasource.getconnection ();} else {//re-create connection conn= Drivermanager.getconnection (URL, user, password); //Bind the connection to the current thread threadlocal.set (conn); Log.println ("OPEN:" + sun.reflect.Reflection.getCallerClass (3) + "---" + sun.reflect.Reflection.getCallerClass (2) + "C" Onn: "+ conn.tostring ());} Return conn; /** * Compiles SQL statements, generates default result sets and * @param SQL * @return * @throws Exception/public static PreparedStatement PreparedStatement (S Tring sql) throws Exception {return dbutils.getconnection (). preparestatement (SQL);/** * Compile SQL, generate cursor result set * @param SQL * @r Eturn * @throws Exception */public static PreparedStatement preparedstatementconcur (String sql) throws Exception {return Dbutils.getconnection (). Preparestatement (Sql,resultset.type_scroll_sensitive, resultset.concur_read_only); /** * COMMIT TRANSACTION * @throws Exception/public static void Commit () throws Exception {dbutils.getconnection (). commit ();}/** * ROLLBACK TRANSACTION * @throws Exception */public static void RoLlback () throws Exception {dbutils.getconnection (). Rollback (),/** * SET Transaction Commit property * @param bl * @throws Exception/Public static void Setautocommit (Boolean bl) throws Exception {dbutils.getconnection (). Setautocommit (BL);} public static void C Lose (ResultSet Rs) {try {if (rs!=null) {rs.close ();} catch (Exception ex) {ex.printstacktrace ();}} public static Vo ID Close (Statement pstm) {try {if (pstm!=null) {pstm.close ();} "catch (Exception ex) {ex.printstacktrace ();}} public static void Close () {try {Connection conn= (Connection) threadlocal.get (); if (Conn!=null &&!conn.isclosed ()) { Unbind the conn from the current thread threadlocal.set (null); Conn.close (); Log.println ("Close:" + sun.reflect.Reflection.getCallerClass (3) + "---" + sun.reflect.Reflection.getCallerClass (2) + " CONN: "+ conn.tostring ());}} catch (Exception ex) {ex.printstacktrace ();}} public static void Main (string[] args) {Connection conn=null; try {conn=dbutils.getconnection ();//system.out.println (c Onn); Dbutils.cLose (); catch (Exception e) {//TODO auto-generated catch block E.printstacktrace ();}} }

Code 2-configuration file [Dboption.properties], during development, the file is located under SRC

User_datasource=false datasource_name=java:comp/env/jdbc/oraconn Url=jdbc:oracle:thin: @localhost: 1521:qhit DRIVER =oracle.jdbc.driver.oracledriver username=wangxg password=wangxg1234 #URL =jdbc:jtds:sqlserver://localhost:1433/ qhit02 #DRIVER =net.sourceforge.jtds.jdbc.driver #USERNAME =sa #PASSWORD =sa1234

Code 3. Database Connection Filter

/** * Filename:connfilter * * filetype:final Class * * date:2008 year April 13 * Author: A Little Red * * email:cpucpa@163.com * * D Escription: Database Connection Filter * * */package com.qhit.system.tools; Import java.io.IOException; Import Javax.servlet.Filter; Import Javax.servlet.FilterChain; Import Javax.servlet.FilterConfig; Import javax.servlet.ServletException; Import Javax.servlet.ServletRequest; Import Javax.servlet.ServletResponse; Import Javax.servlet.http.HttpServlet; Import Com.qhit.system.db.conn.DBUtils; public class Connfilter extends HttpServlet implements Filter {public void Dofilter (ServletRequest request, Servletrespon SE response, Filterchain filterchain) throws IOException, Servletexception {try {filterchain.dofilter (Request, response ); finally {dbutils.close ();}} public void init (Filterconfig arg0) throws Servletexception {//TODO auto-generated method stub}}

Code 4. Sample Code

Package com.qhit.services; Import java.sql.*; Import java.util.*; Import com.qhit.dao.supers.BasicDAOInterfaces; Import Com.qhit.system.db.conn.DBUtils; Import Com.qhit.dao.factory.PublicFactory; Public final class A1010business {private map dto=null; private basicdaointerfaces dao=null; public a1010business (Map dto {this.dto=dto} public Map FindByID (String id) throws Exception {this.dao=publicfactory.factory ("A1010"); Ueryformap (Integer.parseint (id)); Public List Getonpage () throws Exception {this.dao=publicfactory.factory ("A1010"), Return This.dao.queryForList ( THIS.DTO); /** * * @param URL * @return/public string getpageoption (string url) {return this.dao.getPageOption (URL);}/** * System Payment * @param dto * @return * @throws Exception/public boolean Add () throws Exception {PreparedStatement pstm=null; try { StringBuilder sql=new StringBuilder (). Append ("INSERT INTO IC01 ("). Append ("ica001,ica002,ica003,ica004,ica005,"). Append ("Ica006,ica007,ica008,ica009,ica010, "). Append (" ica011,ica012 "). Append (") "). Append (" VALUES ("). Append (" S1001.nextval,?,?,?,?, "). Append ("?,?, Sysdate,null,null, "). Append (" Null,null "). Append (") "); Pstm=dbutils.preparedstatement (Sql.tostring ()); Rows1 pstm.setobject (1, Dto.get ("ica002")); Pstm.setobject (2, Dto.get ("ica003")); Pstm.setobject (3, Dto.get ("ica004")); Pstm.setobject (4, "1"); Rows2 Pstm.setobject (5, "1"); Pstm.setobject (6, "a Little Red"); Return Pstm.executeupdate () >0; finally {dbutils.close (pstm);}} }