java maplisthandler_java学习笔记

packagecom.lyra.chapter2.helper;importcom.lyra.chapter2.util.CollectionUtil;importcom.lyra.chapter2.util.PropsUtil;importorg.apache.commons.dbcp2.BasicDataSource;importorg.apache.commons.dbutils.QueryRunner;importorg.apache.commons.dbutils.handlers.BeanHandler;importorg.apache.commons.dbutils.handlers.BeanListHandler;importorg.apache.commons.dbutils.handlers.MapListHandler;importorg.slf4j.Logger;importorg.slf4j.LoggerFactory;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.SQLException;importjava.util.ArrayList;importjava.util.List;importjava.util.Map;importjava.util.Properties;/*** Created by qiyin.gan on 2016/5/18.*/

public final classDatabaseHelper {private static final Logger LOGGER= LoggerFactory.getLogger(DatabaseHelper.class);private static final ThreadLocalCONNECTION_THREAD_LOCAL;private static finalQueryRunner QUERY_RUNNER;private static finalBasicDataSource DATA_SOURCE;/*private static final String DRIVER;

private static final String URL;

private static final String USERNAME;

private static final String PASSWORD;*/

static{

CONNECTION_THREAD_LOCAL=new ThreadLocal();

QUERY_RUNNER=newQueryRunner();

Properties config= PropsUtil.loadProps("config.properties");/*DRIVER=config.getProperty("jdbc.driver");

URL=config.getProperty("jdbc.url");

USERNAME=config.getProperty("jdbc.username");

PASSWORD=config.getProperty("jdbc.password");

try{

Class.forName(DRIVER);

}

catch (ClassNotFoundException ex)

{

LOGGER.error("can not load jdbc driver",ex);

}*/String driver=config.getProperty("jdbc.driver");

String url=config.getProperty("jdbc.url");

String username=config.getProperty("jdbc.username");

String password=config.getProperty("jdbc.password");

DATA_SOURCE=newBasicDataSource();

DATA_SOURCE.setDriverClassName(driver);

DATA_SOURCE.setUrl(url);

DATA_SOURCE.setUsername(username);

DATA_SOURCE.setPassword(password);

}/*** 查询实体列表*/

public static List queryEntityList(ClassentityClass,Connection connection,String sql,Object... params)

{

ListentityList;try{

entityList=QUERY_RUNNER.query(connection,sql,new BeanListHandler(entityClass),params);

}catch(SQLException ex)

{

LOGGER.error("query entity list failure",ex);throw newRuntimeException(ex);

}finally{//closeConnection(connection);

}returnentityList;

}/*** 查询实体列表*/

public static List queryEntityList(ClassentityClass,String sql,Object... params)

{

Connection connection=CONNECTION_THREAD_LOCAL.get();

ListentityList;try{

entityList=QUERY_RUNNER.query(connection,sql,new BeanListHandler(entityClass),params);

}catch(SQLException ex)

{

LOGGER.error("query entity list failure",ex);throw newRuntimeException(ex);

}finally{//closeConnection();

}returnentityList;

}/*** 查询实体*/

public static T queryEntity(ClassentityClass,String sql,Object... params)

{

Connection connection=CONNECTION_THREAD_LOCAL.get();

T entity;try{

entity=QUERY_RUNNER.query(connection,sql,new BeanHandler(entityClass),params);

}catch(SQLException ex)

{

LOGGER.error("query entity failure",ex);throw newRuntimeException(ex);

}finally{//closeConnection();

}returnentity;

}/*** 查询实体列表*/

public static List>executeQuery(String sql, Object... params)

{

Connection connection=CONNECTION_THREAD_LOCAL.get();

List>result;try{

result=QUERY_RUNNER.query(connection,sql,newMapListHandler(),params);

}catch(SQLException ex)

{

LOGGER.error("execute query failure",ex);throw newRuntimeException(ex);

}finally{//closeConnection();

}returnresult;

}/*** 执行更新语句(包括insert ,update,delete)

*@paramsql

*@paramparams

*@return

*/

public static intexecuteUpdate(String sql,Object... params)

{int rows=0;try{

Connection connection=getConnection();

rows=QUERY_RUNNER.update(connection,sql,params);

}catch(SQLException ex)

{

LOGGER.error("execute update failure",ex);throw newRuntimeException(ex);

}finally{//closeConnection();

}returnrows;

}/*** 插入实体

*@paramentityClass

*@paramfieldMap

*@param

*@return

*/

public static boolean insertEntity(Class entityClass,MapfieldMap){if(CollectionUtil.isEmpty(fieldMap))

{

LOGGER.error("can not insert entity: fieldMap is empty");return false;

}

String sql="INSERT INTO "+getTableName(entityClass);

StringBuilder columns=new StringBuilder("(");

StringBuilder values=new StringBuilder("(");for(String fieldName :fieldMap.keySet())

{

columns.append(fieldName).append(", ");

values.append("?, ");

}

columns.replace(columns.lastIndexOf(", "),columns.length(),")");

values.replace(columns.lastIndexOf(", "),columns.length(),")");

sql+=columns+" VALUES "+values;

Object[] params=fieldMap.values().toArray();return executeUpdate(sql,params)==1;

}/*** 更新实体

*@paramentityClass

*@paramid

*@paramfieldMap

*@param

*@return

*/

public static boolean updateEntity(Class entityClass,long id,MapfieldMap)

{if(CollectionUtil.isEmpty(fieldMap))

{

LOGGER.error("can not update entity: fieldMap is empty");return false;

}

String sql="UPDATE "+getTableName(entityClass)+" SET ";

StringBuilder columns=new StringBuilder("(");for(String fieldName :fieldMap.keySet())

{

columns.append(fieldName).append("=?, ");

}

sql+=columns.substring(0,columns.lastIndexOf(", "))+" WHERE id=?";

List paramList=new ArrayList();

paramList.addAll(fieldMap.values());

paramList.add(id);

Object[] params=paramList.toArray();return executeUpdate(sql,params)==1;

}/*** 删除实体

*@paramentityClass

*@paramid

*@param

*@return

*/

public static boolean deleteEntity(Class entityClass,longid)

{

String sql="DELETE FROM "+getTableName(entityClass)+" WHERE id=?";return executeUpdate(sql,id)==1;

}private static String getTableName(Class>entityClass)

{returnentityClass.getSimpleName();

}/*** 获取数据库连接

*@return

*/

public staticConnection getConnection(){//原始写法

/*Connection connection=null;

try {

connection= DriverManager.getConnection(URL,USERNAME,PASSWORD);

}

catch (SQLException ex)

{

LOGGER.error("get conncetion failure",ex);

}

return connection;*/

//线程池写法

Connection connection=CONNECTION_THREAD_LOCAL.get();if(connection==null)

{try{//connection= DriverManager.getConnection(URL,USERNAME,PASSWORD);

connection=DATA_SOURCE.getConnection();

}catch(SQLException ex)

{

LOGGER.error("get conncetion failure",ex);throw newRuntimeException(ex);

}finally{

CONNECTION_THREAD_LOCAL.set(connection);

}

}returnconnection;

}/*** 关闭数据库连接

*@paramconnection*/

public static voidcloseConnection(Connection connection)

{if(connection!=null)

{try{

connection.close();

}catch(SQLException ex) {

LOGGER.error("close connection failure", ex);

}

}

}/*** 关闭数据库连接*/

public static voidcloseConnection()

{//线程池写法

Connection connection=CONNECTION_THREAD_LOCAL.get();if(connection!=null)

{try{

connection.close();

}catch(SQLException ex) {

LOGGER.error("close connection failure", ex);throw newRuntimeException(ex);

}finally{

CONNECTION_THREAD_LOCAL.remove();

}

}

}

}