博客
关于我
强烈建议你试试无所不能的chatGPT，快点击我
java学习笔记
阅读量：4608 次
发布时间：2019-06-09

本文共 8403 字，大约阅读时间需要 28 分钟。

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依赖了。

         
      
       com.microsoft.sqlserver
          
      
       sqljdbc4
          
      
       4.0

配置文件config.properties

jdbc.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver;jdbc.url=jdbc:sqlserver://127.0.0.1:1433;DatabaseName=chapter2jdbc.username=sajdbc.password=Passw0rd

2-pom.xml基本配置

         
              
      
       UTF-8
          
         
             
                  
                   
                       
        
         org.apache.maven.plugins
                        
        
         maven-compiler-plugin
                        
        
         3.3
                        
                             
                             
         1.8                    
         
          1.8

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_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
      
       ();        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 
       
         List
        
          queryEntityList(Class
         
           entityClass,Connection connection,String sql,Object... params)    {           List
          
            entityList; try { entityList=QUERY_RUNNER.query(connection,sql,new BeanListHandler
           
            (entityClass),params); } catch (SQLException ex) { LOGGER.error("query entity list failure",ex); throw new RuntimeException(ex); } finally { //closeConnection(connection); } return entityList; } /** * 查询实体列表 */ public static 
            
              List
             
               queryEntityList(Class
              
                entityClass,String sql,Object... params) { Connection connection=CONNECTION_THREAD_LOCAL.get(); List
               
                 entityList; try { entityList=QUERY_RUNNER.query(connection,sql,new BeanListHandler
                
                 (entityClass),params); } catch (SQLException ex) { LOGGER.error("query entity list failure",ex); throw new RuntimeException(ex); } finally { //closeConnection(); } return entityList; } /** * 查询实体 */ public static 
                 
                   T queryEntity(Class
                  
                    entityClass,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 new RuntimeException(ex); } finally { //closeConnection(); } return entity; } /** * 查询实体列表 */ public static List
                    
                     
                      > executeQuery(String sql, Object... params) { Connection connection=CONNECTION_THREAD_LOCAL.get(); List
                      
                       
                        > 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 
                        
                          * @return */ public static 
                         
                           boolean insertEntity(Class
                          
                            entityClass,Map
                           
                             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 
                            
                              * @return */ public static 
                             
                               boolean updateEntity(Class
                              
                                entityClass,long id,Map
                               
                                 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
                                 paramList=new ArrayList(); paramList.addAll(fieldMap.values()); paramList.add(id); Object[] params=paramList.toArray(); return executeUpdate(sql,params)==1; } /** * 删除实体 * @param entityClass * @param id * @param 
                                  
                                    * @return */ public static 
                                   
                                     boolean deleteEntity(Class
                                    
                                      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(); } } }}

 

 

 

 

转载于:https://www.cnblogs.com/ganqiyin/p/5505273.html

你可能感兴趣的文章
Docker 版本
查看>>
poj 1753 Flip Game
查看>>
在深信服实习是怎样的体验（研发测试岗）
查看>>
Linux免密码登陆
查看>>
SpringMVC中文件的上传（上传到服务器）和下载问题（二）--------下载
查看>>
Socket & TCP &HTTP
查看>>
osip及eXosip的编译方法
查看>>
Hibernate composite key
查看>>
[CF Round #294 div2] D. A and B and Interesting Substrings 【Map】
查看>>
PHP error_reporting(0)
查看>>
关键字super
查看>>
.NET Core RC2发布在即，我们试着用记事本编写一个ASP.NET Core RC2 MVC程序
查看>>
【前端攻略】：玩转图片Base64编码
查看>>
Ocelot中文文档-路由
查看>>
分布式锁
查看>>
SQLServer约束介绍
查看>>
SQLPROMPT5.3对各种加密对象的解密测试
查看>>
js jquery获取ID值
查看>>
基于邻接矩阵的Dijstra算法-输出路径
查看>>
js获取input file完整路径的方法
查看>>
喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！-- 愿君每日到此一游！
当前时间: 2024-10-19 14:31:07   当前IP: 3.138.122.210   联系邮箱:javaeecc@qq.com     Copyright © 2020 - 2022 baihongyu.com 京ICP备2021015314号-2
强烈建议你试试无所不能的CHAT-GPT，快点击我
强烈建议你试试无所不能的CHAT-GPT，快点击我