• Java如何连接SQLServer，并实现查询、修改、删除方法

    场景：Ａ:在UI自动化时，删除数据时候，在界面UI提示“该XX已被使用，无法删除”。 这时候我们有需要做数据初始化的操作，需要把历史数据做删除，来确脚本运行的重复执行，和稳定性质。

    B: 在做新增操作时候，需要校验数据是否存在后台。需要校验后台数据。

    实现思路：

    1.把数据库的连接地址、用户名、密码。 配置在框架配置文件处。

    public class Const {
    public static final String DB_URL = "XXXX";
    public static final String DB_DatabaseName = "XXX";
    public static final String DB_UserName = "XXX";
    public static final String DB_Password = "XXX";
}

    2.DBHelper 方法

     1 package com.pensee.utils;
 2 
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.ResultSet;
 6 import java.sql.SQLException;
 7 import java.sql.Statement;
 8 import com.pensee.config.Const;
 9 
10 public class DBHelper {
11     static String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
12     static String url = "jdbc:sqlserver://"+ Const.DB_URL +";DatabaseName="+ Const.DB_DatabaseName +"";
13     static Connection con = null;
14     static Statement st = null;
15     static ResultSet res = null;
16     
17 
18     public static void dataBase() {
19         try {
20             Class.forName(driver);
21             con = DriverManager.getConnection(url, ""+ Const.DB_UserName +"", ""+ Const.DB_Password +"");
22         } catch (ClassNotFoundException e) {
23             System.err.println("装载 JDBC/ODBC 驱动程序失败。");
24             e.printStackTrace();
25         } catch (SQLException e) {
26             System.err.println("无法连接数据库");
27             e.printStackTrace();
28         }
29     }
30     
31     /**
32      * 查询SQL方法
33      * @param sql
34      * @return
35      * @throws SQLException
36      */
37     public static ResultSet find(String sql) throws SQLException{//对数据库进行数据查询
38         //获得连接
39         dataBase();
40         st=con.createStatement();
41         try {
42             res=st.executeQuery(sql);
43             return res;
44         } catch (SQLException e) {
45             e.printStackTrace();
46             return null;
47         }
48         
49     }
50     /**
51      * SQL删除修改
52      * @param sql
53      * @return
54      * @throws SQLException
55      */
56     public static boolean update(String sql) throws SQLException{//对增删改
57         //获得连接
58         dataBase();
59         st = con.createStatement();
60         try {
61             st.executeUpdate(sql);
62             return true;
63         } catch (SQLException e) {
64             e.printStackTrace();
65             return false;
66         }
67     }
68 
69 }


    3. 如何调用DB

    String sql = "update hr_staff_policy set HolidayPolicy = 3  where staffno ='0092'";
boolean result = DBHelper.update(sql);
Assert.isTrue(result);
System.out.println("SQL执行结果为：" +DBHelper.update(sql));

    4. 效果图：

    框架如何增加JDBC

    

    <dependency>
        <groupId>com.microsoft.sqlserver</groupId>
        <artifactId>sqljdbc4</artifactId>
        <version>4.0</version>
    </dependency>
    

     
    

    


     进入sqljdbc4.jar包所在的位置，cmd运行以下命令（即在我们的project 的lib路径下）

    mvn install:install-file -Dfile=sqljdbc4.jar -Dpackaging=jar -DgroupId=com.microsoft.sqlserver -DartifactId=sqljdbc4 -Dversion=4.0

    安装成功之后就可以在pom中引用sqljdbc依赖了。（已经加好上传了）


    

    

    五、 优化后的代码
    

     

    

      1 import java.sql.Connection;
  2 import java.sql.DriverManager;
  3 import java.sql.ResultSet;
  4 import java.sql.ResultSetMetaData;
  5 import java.sql.SQLException;
  6 import java.sql.Statement;
  7 import java.util.ArrayList;
  8 
  9 import org.apache.logging.log4j.LogManager;
 10 import org.apache.logging.log4j.Logger;
 11 
 12 import com.pensee.config.Const;
 13 
 14 public class DBHelper {
 15     private static final Logger logger = LogManager.getLogger(DBHelper.class);
 16     
 17     static String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
 18     static String url = "jdbc:sqlserver://"+ Const.DB_URL +";DatabaseName="+ Const.DB_DatabaseName +"";
 19      
 20     private static Connection getDBConnection() {
 21         Connection con = null;
 22         try {
 23             Class.forName(driver);
 24             con = DriverManager.getConnection(url, ""+ Const.DB_UserName + "", ""+ Const.DB_Password +"");
 25         } catch (ClassNotFoundException e) {
 26             logger.error("装载 JDBC/ODBC 驱动程序失败。");
 27         } catch (SQLException e) {
 28             logger.error("无法连接数据库");
 29         }
 30         return con;
 31     }
 32     
 33     private static void closeConnection(Connection con) {
 34         try {
 35             con.close();
 36         } catch (SQLException e) {
 37             ;
 38         }
 39     }
 40     
 41     /**
 42      * 查询SQL方法
 43      * @param sql
 44      * @return
 45      * @throws SQLException
 46      */
 47     public static ArrayList<String> query(String sql) throws SQLException{//对数据库进行数据查询
 48         //获得连接
 49         Connection con = getDBConnection();
 50         Statement st = con.createStatement();
 51         ResultSet res;
 52         try {
 53             res = st.executeQuery(sql);
 54         } catch (SQLException e) {
 55             throw new RuntimeException("查询失败： " + sql);
 56         }  
 57         ArrayList<String> result = new ArrayList<String>();
 58         while(res.next()) {
 59             ResultSetMetaData rsmd = res.getMetaData();
 60             int columnCount = rsmd.getColumnCount();
 61             for (int i=0;i<columnCount;i++) {
 62                 result.add(res.getString(i+1));
 63             }
 64         }
 65         closeConnection(con);
 66         return result;        
 67     }
 68     /**
 69      * SQL删除修改
 70      * @param sql
 71      * @return
 72      * @throws SQLException
 73      */
 74     public static void updateDB(String sql) throws SQLException{//对增删改
 75         //获得连接
 76         Connection con = getDBConnection();
 77         Statement st = con.createStatement();
 78         int recordsNo = st.executeUpdate(sql);
 79         if(recordsNo == 0) {
 80             throw new RuntimeException("执行失败： " + sql);
 81         } else if(recordsNo == 1){
 82             logger.info("更新成功1条： " + sql);
 83         } else {
 84             logger.info("更新成功条数： " + recordsNo);
 85         }
 86         closeConnection(con);
 87     }
 88     
 89     //存在数据的时候更新，不存在的时候不需要更新，影响数据行数为0或者1
 90     public static void updateDBIfExist(String sql) throws SQLException{//对增删改
 91         //获得连接
 92         Connection con = getDBConnection();
 93         Statement st = con.createStatement();
 94         int recordsNo = st.executeUpdate(sql);
 95         logger.info("更新成功的record数量 " + recordsNo);   
 96         closeConnection(con);
 97     }
 98     
 99     public static void deleteVacationBalance(String code, String staffId) throws SQLException {
100         String sql = "DELETE lb FROM Leave_Balance lb LEFT JOIN Leave_Code lc ON lc.id = lb.LeaveCode_id "
101                 + "WHERE lc.LeaveBenefitCode ='" + code + "' and taffno ='" + staffId + "'";
102         updateDB(sql);
103     } 
104     
105     /**
106      * 删除历史组织架构
107      * @param code
108      * @param staffId
109      * @throws SQLException
110      */
111     public static void deleteHistoryOrganzationalStructure(String hisname) throws SQLException {
112         String sql = "DELETE FROM Org_DataType WHERE NAME='" + hisname + "'";
113         updateDB(sql);
114     } 
115     
116     /**
117      * 取得员工工号，该员工没有卡，用于case E-653
118      * @return
119      * @throws SQLException
120      */
121     public static String getStaffNoWithoutCard() throws SQLException {
122         String sql = "SELECT ac.staffNo FROM At_Card ac INNER JOIN hr_Staff hs ON ac.StaffNo = hs.StaffNo "
123                 + "WHERE ac.No = ''  AND hs.StaffType  = 'Active'";
124         String id = query(sql).get(0);
125         String sqlDelete = String.format("DELETE ap  FROM At_PunchClockInfo ap INNER JOIN At_Card ac ON ap.At_Card_id = ac.Id "
126                 + "WHERE ac.StaffNo ='%s'", id);
127         updateDB(sqlDelete);
128         return id;
129     }
130 }
    

    


    六： jenkins配置增加JDBC
    

    

    

    

    
    
          
    • 

          
  • 

            相关阅读:
    
            
              loj#6433. 「PKUSC2018」最大前缀和（状压dp）
                
    PKUWC2019游记
                
    10. Regular Expression Matching
                
    9. Palindrome Number
                
    8. String to Integer (atoi)
                
    7. Reverse Integer
                
    6. ZigZag Conversion
                
    5. Longest Palindromic Substring
                
    4. Median of Two Sorted Arrays
                
    3. Longest Substring Without Repeating Characters
                
                    
          
    • 
          
  • 
            原文地址：https://www.cnblogs.com/Shanghai-vame/p/7895303.html
          
    • 

        

      



      
      

        

        

      

    

  


  

  
Copyright © 2020-2023 
  润新知