Java连接SQL Server创建表时IF条件判断异常问题

问题原因 #

SQL Server的JDBC驱动执行CREATE TABLE这类DDL（数据定义语言）语句时，executeUpdate()的返回值固定为0——因为DDL操作不涉及表中行数据的增删改，没有受影响的行数，所以返回值无法用来判断表是否创建成功。只要代码没有抛出异常，就说明表已经成功创建。

修正后的代码 #

package DataBaseConnectivity;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Scanner;

public class DataBaseConnectivity {
    public static void main(String args[]) {
        String url = "jdbc:sqlserver://localhost:1433;databaseName=SchoolMgt;encrypt=true;trustServerCertificate=true";
        String user = "sa";
        String pass = "root";

        Scanner scanner = new Scanner(System.in);
        String tableCreationsql = " create table  Students ( id integer PRIMARY KEY,fname VARCHAR (8),birthdate date,lname VARCHAR (8))";

        Connection con = null;

        try {
            Driver driver = (Driver) Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
            DriverManager.registerDriver(driver);

            con = DriverManager.getConnection(url, user, pass);
            Statement st = con.createStatement();

            // 执行DDL语句，只要不抛异常就是成功
            st.executeUpdate(tableCreationsql);
            System.out.println("Created Successfully : " + tableCreationsql);

            con.close();
        } catch (Exception ex) {
            System.err.println("Creation Is Failed");
            ex.printStackTrace();
        }
    }
}

额外优化建议 #

• 可以在创建表前先检查表是否存在，避免重复创建抛出异常，修改SQL语句为：

  IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='Students' AND xtype='U')
  CREATE TABLE Students ( id integer PRIMARY KEY,fname VARCHAR (8),birthdate date,lname VARCHAR (8))
  

• 推荐使用try-with-resources语法自动关闭连接和Statement，避免资源泄漏：

  try (Connection con = DriverManager.getConnection(url, user, pass);
       Statement st = con.createStatement()) {
      st.executeUpdate(tableCreationsql);
      System.out.println("Created Successfully : " + tableCreationsql);
  } catch (Exception ex) {
      System.err.println("Creation Is Failed");
      ex.printStackTrace();
  }
  

内容的提问来源于stack exchange，提问作者R4wdha19