Java访问SQLServer数据库

ゞ浴缸里的玫瑰 2022-04-04 03:58 261阅读 0赞

## 安装jar包 ##

由于maven仓库不支持sqljdbc4的下载，所以我们手动下载sqljdbc4.jar，然后在添加到构建路径  
![在这里插入图片描述][20181214181545114.png]

如果用了Jenkins做持续集成，最好在pom.xml里引用下sqljdbc4.jar，如下

<dependency>
                <groupId>com.microsoft.sqlserver</groupId>
                <artifactId>sqljdbc4</artifactId>
                <version>4.0</version>
                <scope>system</scope>
                <systemPath>${basedir}/libs/sqljdbc4.jar</systemPath>
    </dependency>

## 数据库工具类 ##

DBUtil.java

package test.java.utils;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    
    public class DBUtil {
    
        //设置数据库名称
        private final static String URL = "jdbc:sqlserver://192.168.93.131:8157;DatabaseName=Star-Notify";
        private static final String USER="username";
        private static final String PASSWORD="password";
    
        private static Connection conn=null;
        //静态代码块（将加载驱动、连接数据库放入静态块中）
        static{
            try {
                //1.加载驱动程序
                Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
                //2.获得数据库的连接
                conn=(Connection)DriverManager.getConnection(URL,USER,PASSWORD);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    
        //对外提供一个方法来获取数据库连接
        public  Connection getConnection(){
            return conn;
        }
    }

## 查询数据库例子 ##

import test.java.utils.DBUtil;
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    /**
     * Author: 灵枢
     * Date: 2018/11/15
     * Time: 14:18
     * Description:数据库操作
     */
    public class Dao {
        private static Connection conn = null;
        private static Statement stmt = null;
        
        public static String getEmailAddress(String email){
            String sql = "SELECT Receiver FROM EmailReceiver WHERE Receiver='"+email+"';";
            DBUtil dbUtil = new DBUtil();
            String result="";
            try {
                conn = dbUtil.getConnection();
                stmt = conn.createStatement();
                ResultSet set = stmt.executeQuery(sql);
                if(set.next()){
                    result = set.getString(1);
                }
                conn.close();
                stmt.close();
                return result;
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return "";
        }
    }

[20181214181545114.png]: /images/20220404/ab049d313a3d474094a203fe63f3554f.png