Java 对 SQL Server数据库进行增删查改操作

准备SQLJDBC Driver驱动包，网上有JAR包，我自己上传了一个，最低积分只能到一分了。。。

https://download.csdn.net/download/m0_38059938/10472322

1、

    //我的数据库名字是BIO_DataBase ， 账户使用sa帐户 
    private final static String url = "jdbc:sqlserver://localhost:1433;databaseName=BIO_DataBase";// 数据库连接地址
    private final static String user = "sa"; // 超级管理员
    private final static String password = "1234567890"; // 密码

2、

    private static Connection connection = null;// SQL Connection
    private static java.sql.Statement stmt;

3、

    /**
     * 连接数据库
     */
    private static void connect2SQL() {
        try {    
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");// 加载驱动
        } catch (Exception e1) {
            e1.printStackTrace();
        }
        try {
            connection = DriverManager.getConnection(url, user, password);// 连接数据库
        } catch (Exception e2) {
            e2.printStackTrace();
        }
    }

新建表：

    /**
     * 创建一个新的表，列名ID从1开始每次自增1，列名Value为float型数据
     * @param tableName
     */
    private static void CreateTable(String tableName) throws SQLException {
        String createtable = "create table " + "\"" + tableName + "\"" + " (" + "ID int identity(1,1)," + "Value float)";
        try {
            stmt = connection.createStatement();
            stmt.executeUpdate(createtable);
            System.out.println("Table Created...");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            stmt.close();
        }
    }

插入数据:

    /**
     * 插入数据
     * @param time
     * @param device
     */

    private static void insertTimeData(String time, String device, String TimeSpan) throws SQLException {
        try {
            stmt = connection.createStatement();
            String sql = "insert into TimeDataRecord values('" + time + "','" + device + "','"+TimeSpan+"','"+device+time+"')";
            stmt.executeUpdate(sql);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }finally {
            stmt.close();
        }
    }

删除数据：

    /**
     * Delete Real-time Device
     * @param tableName
     * @param stmt
     * @return
     */
    private static boolean deleteWokingTable(String tableName, Statement stmt){
        String sql = "delete from WorkingList where TableName = '"+tableName+"'";
        try {
           int i = stmt.executeUpdate(sql);
           if(i>0){
               return true;
           }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

查询数据：

    /**
     * 登陆验证
     *
     * @param id
     * @param pwd
     * @return
     */
    private static boolean checkAccount(String id, String pwd) {
        try {
            stmt = connection.createStatement();
            String sqlstmt = "select pwd from UserAccount where id=" + "'" + id + "'";
            ResultSet resultSet = stmt.executeQuery(sqlstmt);
            String passWord = "";
            while (resultSet.next()) {
                passWord = resultSet.getString("pwd").trim();
                if (passWord == pwd || passWord.equals(pwd)) {
                    return true;
                } else return false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return false;
    }

更新数据：

    /**
     * 更新时间表
     * @param newTime
     * @param device
     */
    private static void updateTime(String newTime,String device){
        try {
            stmt=connection.createStatement();
            stmt.executeUpdate("update TimeTable set Time = '"+newTime+"' where device = '"+device+"'");
            stmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }