JDBC Database General DAO

1. Database Connection Pool
Package org.dave.common.database;
Import java.sql.Connection;
Import java.sql.SQLException;
Import Java.util.ResourceBundle;
Import Org.slf4j.Logger;
Import Org.slf4j.LoggerFactory;
Import Com.jolbox.bonecp.BoneCP;
Import Com.jolbox.bonecp.BoneCPConfig;
* Database Connection Pool
* @author David Day
Public final class Databaseconnectionpool {
private static final Logger LOG = Loggerfactory.getlogger (Databaseconnectionpool.class);
private static final ResourceBundle BUNDLE = resourcebundle.getbundle ("Connection");
private static final String DRIVER = "DRIVER";
Private static final String url = "url";
private static final String USERNAME = "USERNAME";
private static final String PASSWORD = "PASSWORD";
private static final String max_connection = "Max_connection";
private static BONECP pool;
* Open Connection Pool
public static void Startup () {
try {
Class.forName (bundle.getstring (DRIVER));
Bonecpconfig config = new Bonecpconfig ();
Config.setjdbcurl (bundle.getstring (URL));
Config.setusername (bundle.getstring (USERNAME));
Config.setpassword (bundle.getstring (PASSWORD));
Config.setmaxconnectionsperpartition (Integer.parseint (bundle.getstring (max_connection)));
Pool = new BONECP (config);
} catch (Exception e) {
E.printstacktrace ();
Log.error (E.getmessage (), E);
throw new Databaseexception (e);
* Close Connection Pool
public static void shutdown () {
Pool.shutdown ();
* @return Database connection
public static Connection getconnection () {
try {
return Pool.getconnection ();
} catch (SQLException e) {
E.printstacktrace ();
Log.error (E.getmessage (), E);
throw new Databaseexception (e);
2. Database exceptions
Package org.dave.common.database;
* Database Exceptions
* @author David Day
@SuppressWarnings ("Serial")
public class Databaseexception extends RuntimeException {
Public databaseexception (Throwable cause) {
Super (cause);
3. Transaction control
Package org.dave.common.database;
Import java.sql.Connection;
Import Org.slf4j.Logger;
Import Org.slf4j.LoggerFactory;
* Database Transactions
* @author David Day
public class Databasetransaction {
* Log Tool
*/
private static final Logger LOG = Loggerfactory.getlogger (Databasetransaction.class);
* Database connection
Private Connection Conn;
* Instantiation of a default connected transaction
Public Databasetransaction () {
This (databaseconnectionpool.getconnection ());
* Instantiation of a default connected transaction
* @param Isopentrans whether to open transaction
*/
Public Databasetransaction (Boolean Isopentrans) throws Databaseexception {
This (Databaseconnectionpool.getconnection (), Isopentrans);
* Instantiation of a default connected transaction
* @param Conn Database connection
*/
Public Databasetransaction (Connection conn) {
This.conn = conn;
* Instantiation of a default connected transaction
* @param Conn Database connection
* @param Isopentrans whether to open transaction
*/
Public Databasetransaction (Connection conn, Boolean Isopentrans) throws Databaseexception {
This.conn = conn;
Setautocommit (!isopentrans);
* @return Database connection
*/
Public Connection getconnection () {
Return conn;
* Set whether to submit automatically
* @param autocommit Automatic submission
* @throws databaseexception
*/
private void Setautocommit (Boolean autocommit) throws Databaseexception {
try {
Conn.setautocommit (autocommit);
} catch (SQLException e) {
E.printstacktrace ();
Log.error (E.getmessage (), E);
throw new Databaseexception (e);
* Start a transaction
* @throws databaseexception
*/
public void Begin () throws Databaseexception {
Setautocommit (FALSE);
* @return whether to open a transaction
* @throws databaseexception
*/HTTP://WWW.HUIYI8.COM/CSS3/CSS3 effects
public Boolean Isbegin () throws Databaseexception {
try {
return!conn.getautocommit ();
} catch (SQLException e) {
E.printstacktrace ();
Log.error (E.getmessage (), E);
throw new Databaseexception (e);
/**
* Submit
* @throws databaseexception
*/
public void Commit () throws Databaseexception {
try {
Conn.commit ();
} catch (SQLException e) {
E.printstacktrace ();
Log.error (E.getmessage (), E);
throw new Databaseexception (e);
* Roll Back
* @throws databaseexception
*/
public void rollback () throws Databaseexception {
try {
Conn.rollback ();
} catch (SQLException e) {
E.printstacktrace ();
Log.error (E.getmessage (), E);
throw new Databaseexception (e)
* Close Connection
* @throws databaseexception
*/
public void Close () throws Databaseexception {
try {
Conn.close ();
} catch (SQLException e) {
E.printstacktrace ();
Log.error (E.getmessage (), E);
throw new Databaseexception (e);
* @return The connection is off
* @throws databaseexception
*/
public Boolean isclose () throws Databaseexception {
try {
return conn.isclosed ();
} catch (SQLException e) {
E.printstacktrace ();
Log.error (E.getmessage (), E);
throw new Databaseexception (e);
4. Common Data Model
Package org.dave.common.database;
Import java.io.Serializable;
@SuppressWarnings ("Serial")
Public abstract class Datamodel implements Serializable {}
5. Result Converter
Package Org.dave.common.database.convert;
Import Java.sql.ResultSet;
Import java.sql.SQLException;
* Result Mapper
* @author David Day
Public interface Resultconverter<t> {
* Map
* @param RS Result set
* @return Mapping results
* @throws SQLException
*/
Public T-Convert (ResultSet rs) throws SQLException;
6. General DAO
Package org.dave.common.database.access;
Import java.sql.Connection;
Import java.sql.PreparedStatement;
Import Java.sql.ResultSet;
Import java.sql.SQLException;
Import java.sql.Statement;
Import java.util.ArrayList;
Import java.util.List;
Import Org.dave.common.database.convert.ResultConverter;
Import Org.slf4j.Logger;
Import Org.slf4j.LoggerFactory;
* Data Access Class
* @author David Day
*/
Public abstract class DataAccess {
* Log Tool
*/
private static final Logger LOG = Loggerfactory.getlogger (Dataaccess.class);
* Database connection
Private Connection Conn;
* @param Conn Database connection
Protected DataAccess (Connection conn) {
This.conn = conn;
* Insert Data
* @param sql
* @param generatedkeysconverter PRIMARY Key mapping
* @param params
* @return PRIMARY key
* @throws DataAccessException
*/
Protected <T> T Insert (String sql, resultconverter<t> generatedkeysconverter, Object ... params) throws DataAccessException {
try {
PreparedStatement pstmt = conn.preparestatement (sql, Statement.return_generated_keys);
Setparameters (pstmt, params);
Executeupdate (PSTMT);
ResultSet rs = Pstmt.getgeneratedkeys ();
NextResult (RS);
Return ConvertResult (RS, generatedkeysconverter);
} catch (SQLException e) {
E.printstacktrace ();
Log.error (E.getmessage (), E);
throw new DataAccessException (e);
* Update Data
* @param sql
* @param params
* @return affect the number of rows
* @throws DataAccessException
*/
protected int update (String sql, Object ... params) throws DataAccessException {
Return executeupdate (getpreparedstatement (SQL, params));
* Query Single results
* @param <T>
* @param sql
* @param converter
* @param params
* @return
Protected <T> T queryforobject (String sql, resultconverter<t> Converter, Object ... params) {
ResultSet rs = executeQuery (sql, params);
if (NextResult (RS)) {
Return ConvertResult (RS, Converter);
} else {
return null;
* Query Result List
* @param <T>
* @param sql
* @param converter
* @param params
* @return
Protected <T> list<t> queryforlist (String sql, resultconverter<t> Converter, Object ... params) {
ResultSet rs = executeQuery (sql, params);
list<t> list = new arraylist<t> ();
while (NextResult (RS)) {
List.add (ConvertResult (RS, Converter));
return list;
* @param SQL SQL statements
* @return Precompiled Declaration
Private PreparedStatement getpreparedstatement (String sql, Object ... params) throws DataAccessException {
PreparedStatement pstmt = getpreparedstatement (sql);
Setparameters (pstmt, params);
return pstmt;
* @param SQL SQL statements
* @return Precompiled Declaration
*/
Private PreparedStatement getpreparedstatement (String sql) throws DataAccessException {
try {
return conn.preparestatement (SQL);
} catch (SQLException e) {
E.printstacktrace ();
Log.error (E.getmessage (), E);
throw new DataAccessException (e);
* Incoming parameters for pre-compilation declarations
* @param pstmt Pre-compilation declaration
* @param params parameter
* @throws DataAccessException
private void Setparameters (PreparedStatement pstmt, Object ... params) throws DataAccessException {
try {
for (int i = 0; i < params.length; i++) {
Pstmt.setobject (i + 1, params[i]);
} catch (SQLException e) {
E.printstacktrace ();
Log.error (E.getmessage (), E);
throw new DataAccessException (e);
* Perform update operations
* @param pstmt
* @return affect the number of rows
* @throws DataAccessException
private int executeupdate (PreparedStatement pstmt) throws DataAccessException {
try {
return Pstmt.executeupdate ();
} catch (SQLException e) {
E.printstacktrace ();
Log.error (E.getmessage (), E);
throw new DataAccessException (e);
* Perform query operations
* @param pstmt Pre-compilation declaration
* @return Result set
* @throws DataAccessException
Private ResultSet ExecuteQuery (PreparedStatement pstmt) throws DataAccessException {
try {
return Pstmt.executequery ();
} catch (SQLException e) {
E.printstacktrace ();
Log.error (E.getmessage (), E);
throw new DataAccessException (e);
* Perform query operations
* @param SQL SQL statements
* @param params parameter
* @return Result set
* @throws DataAccessException
Private ResultSet ExecuteQuery (String sql, Object ... params) throws DataAccessException {
Return executeQuery (getpreparedstatement (SQL, params));
* Move to the next line of records
* @param RS Result set
* @return If there is a next line of records
* @throws DataAccessException
*/
Private Boolean NextResult (ResultSet rs) throws DataAccessException {
try {
return Rs.next ();
} catch (SQLException e) {
E.printstacktrace ();
Log.error (E.getmessage (), E);
throw new DataAccessException (e);
* Map
* @param RS Result set
* @return Mapping results
* @throws DataAccessException
*/
Private <T> T ConvertResult (ResultSet RS, resultconverter<t> Converter) throws DataAccessException {
try {
Return Converter.convert (RS);
} catch (SQLException e) {
E.printstacktrace ();
Log.error (E.getmessage (), E);
throw new DataAccessException (e);
7. Database Access Exceptions
Package org.dave.common.database.access;
* Database Access exception
* @author David Day
@SuppressWarnings ("Serial")
public class DataAccessException extends RuntimeException {
Public DataAccessException (Throwable cause) {
Super (cause);