1-微软数据库连接
JDBC下载地址：https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774
下载jar包之后可以通过下面的maven命令将jar包安装到自己的私服上。（也可以直接导入架包）
mvn install:install-file -Dfile=sqljdbc4.jar -Dpackaging=jar -DgroupId=com.microsoft.sqlserver -DartifactId=sqljdbc4 -Dversion=4.0
安装成功之后就可以在pom中引用sqljdbc依赖了。
<dependency> <groupId>com.microsoft.sqlserver</groupId> <artifactId>sqljdbc4</artifactId> <version>4.0</version> </dependency>
配置文件config.properties
jdbc.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver;
jdbc.url=jdbc:sqlserver://127.0.0.1:1433;DatabaseName=chapter2
jdbc.username=sa
jdbc.password=Passw0rd
2-pom.xml基本配置
<!-- 统一编码格式 --> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> </properties> <build> <plugins> <!-- Compile --> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-compiler-plugin</artifactId> <version>3.3</version> <configuration>
<!-- JDK版本 --> <source>1.8</source> <target>1.8</target> </configuration> </plugin> </plugins> </build>
3-数据库访问帮助类
package com.lyra.chapter2.helper; import com.lyra.chapter2.util.CollectionUtil; import com.lyra.chapter2.util.PropsUtil; import org.apache.commons.dbcp2.BasicDataSource; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.BeanHandler; import org.apache.commons.dbutils.handlers.BeanListHandler; import org.apache.commons.dbutils.handlers.MapListHandler; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import java.util.Map; import java.util.Properties; /** * Created by qiyin.gan on 2016/5/18. */ public final class DatabaseHelper { private static final Logger LOGGER= LoggerFactory.getLogger(DatabaseHelper.class); private static final ThreadLocal<Connection> CONNECTION_THREAD_LOCAL; private static final QueryRunner QUERY_RUNNER; private static final BasicDataSource 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<Connection>(); QUERY_RUNNER=new QueryRunner(); 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=new BasicDataSource(); DATA_SOURCE.setDriverClassName(driver); DATA_SOURCE.setUrl(url); DATA_SOURCE.setUsername(username); DATA_SOURCE.setPassword(password); } /** * 查询实体列表 */ public static <T> List<T> queryEntityList(Class<T> entityClass,Connection connection,String sql,Object... params) { List<T> entityList; try { entityList=QUERY_RUNNER.query(connection,sql,new BeanListHandler<T>(entityClass),params); } catch (SQLException ex) { LOGGER.error("query entity list failure",ex); throw new RuntimeException(ex); } finally { //closeConnection(connection); } return entityList; } /** * 查询实体列表 */ public static <T> List<T> queryEntityList(Class<T> entityClass,String sql,Object... params) { Connection connection=CONNECTION_THREAD_LOCAL.get(); List<T> entityList; try { entityList=QUERY_RUNNER.query(connection,sql,new BeanListHandler<T>(entityClass),params); } catch (SQLException ex) { LOGGER.error("query entity list failure",ex); throw new RuntimeException(ex); } finally { //closeConnection(); } return entityList; } /** * 查询实体 */ public static <T> T queryEntity(Class<T> entityClass,String sql,Object... params) { Connection connection=CONNECTION_THREAD_LOCAL.get(); T entity; try { entity=QUERY_RUNNER.query(connection,sql,new BeanHandler<T>(entityClass),params); } catch (SQLException ex) { LOGGER.error("query entity failure",ex); throw new RuntimeException(ex); } finally { //closeConnection(); } return entity; } /** * 查询实体列表 */ public static List<Map<String,Object>> executeQuery(String sql, Object... params) { Connection connection=CONNECTION_THREAD_LOCAL.get(); List<Map<String,Object>> result; try { result=QUERY_RUNNER.query(connection,sql,new MapListHandler(),params); } catch (SQLException ex) { LOGGER.error("execute query failure",ex); throw new RuntimeException(ex); } finally { //closeConnection(); } return result; } /** * 执行更新语句（包括insert ,update,delete） * @param sql * @param params * @return */ public static int executeUpdate(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 new RuntimeException(ex); } finally { //closeConnection(); } return rows; } /** * 插入实体 * @param entityClass * @param fieldMap * @param <T> * @return */ public static <T> boolean insertEntity(Class<T> entityClass,Map<String,Object> fieldMap){ 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; } /** * 更新实体 * @param entityClass * @param id * @param fieldMap * @param <T> * @return */ public static <T> boolean updateEntity(Class<T> entityClass,long id,Map<String,Object> fieldMap) { 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<Object> paramList=new ArrayList<Object>(); paramList.addAll(fieldMap.values()); paramList.add(id); Object[] params=paramList.toArray(); return executeUpdate(sql,params)==1; } /** * 删除实体 * @param entityClass * @param id * @param <T> * @return */ public static <T> boolean deleteEntity(Class<T> entityClass,long id) { String sql="DELETE FROM "+getTableName(entityClass)+" WHERE id=?"; return executeUpdate(sql,id)==1; } private static String getTableName(Class<?> entityClass) { return entityClass.getSimpleName(); } /** * 获取数据库连接 * @return */ public static Connection 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 new RuntimeException(ex); } finally { CONNECTION_THREAD_LOCAL.set(connection); } } return connection; } /** * 关闭数据库连接 * @param connection */ public static void closeConnection(Connection connection) { if(connection!=null) { try { connection.close(); } catch (SQLException ex) { LOGGER.error("close connection failure", ex); } } } /** * 关闭数据库连接 */ public static void closeConnection() { //线程池写法 Connection connection=CONNECTION_THREAD_LOCAL.get(); if(connection!=null) { try { connection.close(); } catch (SQLException ex) { LOGGER.error("close connection failure", ex); throw new RuntimeException(ex); } finally { CONNECTION_THREAD_LOCAL.remove(); } } } }
