如何在Java中为SQL Server创建其他数据库的Schema且不切换当前库

Got it, I totally get where you're coming from—being able to create databases and tables across databases without switching contexts is great, but schemas throw a tiny wrench in the works because the usual GO trick doesn't play nice with JDBC. Let's break down how to fix this.

Why GO Doesn't Work in Java #

First, a quick clarification: GO isn't actually a T-SQL command recognized by SQL Server itself. It's a batch separator used by tools like SSMS or sqlcmd to split scripts into multiple execution batches. When you send a query with GO via Java's JDBC driver, the server will throw a syntax error because it has no idea what GO means.

The Solution: Execute Schema Creation in the Target Database Context #

Instead of relying on GO, we can use SQL Server's system stored procedure sp_executesql to run the CREATE SCHEMA command directly in the target database—no context switching required. Here's how:

Step-by-Step Code Example

Assuming you already have a valid JDBC connection to an existing database (like master or any other database you're connected to):

import java.sql.Connection;
import java.sql.Statement;
import java.sql.SQLException;

public class SqlServerSchemaSetup {
    public static void main(String[] args) {
        // Replace with your own connection logic
        try (Connection conn = YourConnectionProvider.getSqlServerConnection()) {
            try (Statement stmt = conn.createStatement()) {
                // 1. Create the target database
                stmt.execute("CREATE DATABASE NEW_DATABASE");
                System.out.println("Database NEW_DATABASE created.");

                // 2. Create schema in NEW_DATABASE without switching context
                String createSchemaSql = "EXEC NEW_DATABASE.dbo.sp_executesql N'CREATE SCHEMA NEW_SCHEMA'";
                stmt.execute(createSchemaSql);
                System.out.println("Schema NEW_SCHEMA created in NEW_DATABASE.");

                // 3. Create table with the new schema (your original approach works here!)
                String createTableSql = "CREATE TABLE NEW_DATABASE.NEW_SCHEMA.NEW_TABLE (" +
                                       "ID INT PRIMARY KEY IDENTITY(1,1)," +
                                       "ProductName VARCHAR(100) NOT NULL," +
                                       "Price DECIMAL(10,2) NOT NULL," +
                                       "CreatedAt DATETIME DEFAULT GETDATE())";
                stmt.execute(createTableSql);
                System.out.println("Table NEW_DATABASE.NEW_SCHEMA.NEW_TABLE created.");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

How This Works #

• sp_executesql is a built-in stored procedure that executes dynamic SQL statements. By prefixing it with NEW_DATABASE.dbo., we tell SQL Server to run the enclosed CREATE SCHEMA command within the context of NEW_DATABASE, not your current connected database.
• This keeps your original connection's database context intact, so you don't have to switch away from your initial database.

Alternative: Using USE (But Not Recommended for Your Use Case) #

If you didn't mind switching contexts, you could split the USE and CREATE SCHEMA into two separate execute() calls:

// Not recommended if you want to stay in your original database
stmt.execute("USE NEW_DATABASE");
stmt.execute("CREATE SCHEMA NEW_SCHEMA");

But this changes your connection's active database, which you explicitly said you wanted to avoid—so the sp_executesql method is the way to go.

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