Encapsulate database operations to JavaBean

Encapsulation | data | Database encapsulates database operations in order to hide the class in the java.sql package and remove the core database operation code from the encoding. In order to prevent the direct database operation easy to bring the resource not release problem. It also reduces the amount of coding for database operations.

But many netizens in the encapsulation, but like to return the result set (ResultSet object), then this encapsulation is meaningless.
1. It is also a direct operation of the core database class, with almost no changes before the package.
2. The result set always relies on the connection (Connection) object it uses. So when the connection object is closed within the method, the resultset you return is useless.

If you really want to get the result set of the query database, dump all the data in the result set object into the list object that is the map element.
Of course, this way, can not adapt to large amount of data query, but if you really encounter large data query, which package is not good, or direct database operations. :)))

The following is a simple database operation JavaBean Code

Dbwrapper.java
Import java.sql.*;
Import java.util.*;
public class Dbwrapper
{
Defining the connection pool object as a static variable will persist until the working directory is closed.
private static DataSource ds = NULL;
1. Connect by Connection Pool
This method is recommended if you are not a long database program
Related: Configuring connection pooling in the Tomcat management interface
public static Connection OpenConnection () throws Exception
{
Only need to initialize 1 times
if (ds = null)
{
Context Initcontext = new InitialContext ();
Context Envcontext = (context) initcontext.lookup ("java:/comp/env");
DataSource ds = (DataSource) envcontext.lookup ("Jdbc/mydatasource");
}
return Ds.getconnection ();
}

2. Use JDBC driver to get the connection
Related content: JSP database connection Encyclopedia
public static Connection OpenConnection (
String driver,
String URL,
String username,
String password)
Throws Exception
{
Class.forName (Driver). newinstance ();
Return drivermanager.getconnection (URL, username, password);
}

public static void CloseConnection (Connection conn) throws Exception
{
IF (conn!= null)
{
Conn.close ();
}
}
public static int executeupdate (String sql) throws Exception
{
int count = 0;

Connection conn = null;
Statement stmt = null;

Try
{
conn = OpenConnection ();
stmt = Conn.createstatement ();

Count = stmt.executeupdate (SQL);
}
catch (Exception e)
{
Throw e;
}
Finally
{
CloseConnection (conn);
}

return count;
}

public static List executequery (String sql) throws Exception
{
List List = new ArrayList ();

Connection conn = null;
Statement stmt = null;
ResultSet rs = null;

Try
{
conn = OpenConnection ();
stmt = Conn.createstatement ();
rs = stmt.executequery (SQL);

ResultSetMetaData RSMD = Rs.getmetadata ();

while (Rs.next ())
{
Map map = new HashMap ();

for (int i = 1; I <= rsmd.getcolumncount (); i++)
{
Map.put (Rsmd.getcolumnname (i), rs.getobject (i));
}

List.add (map);
} }
catch (Exception e)
{
E.printstacktrace ();
}
Finally
{
if (Rs!= null) rs.close ();
CloseConnection (conn);
}

return list; }
}



Use examples:
1. For INSERT, UPDATE, DELETE statement int count = dbwrapper.executeupdate (SQL);
2. For Selete statements
Java.util.List List = dbwrapper.executequery (sql);
Method one: By name value, note that the case is strictly distinguished
for (int i = 0; i < list.size (); i++)
{
Java.util.Map Map = (java.util.Map) list.get (i);
Out.println (Mag.get ("column_name"). toString ());
}
Method Two: Traverse to take value
for (int i = 0; i < list.size (); i++)
{
Java.util.Map Map = (java.util.Map) list.get (i);
for (Java.util.Iterator it = Map.keyset (). iterator (); It.hasnext ();)
{
String column_name = It.next (). toString ()); Note NULL judgment when taking value
Out.println (column_name + "=" + map.get (column_name) = = null? "": Map.get (column_name). toString ());
}
}