英文:
Creating a dynamic table in SQL Server with Java Spring
问题
我是Java新手。现在我正在尝试从Java在SQL Server数据库中创建一个动态SQL表。我有一个字符串中的表名，一个ArrayList中的列名，并为我想要的所有列放置相同的类型和长度。我的代码如下，但是当我运行它时，我得到了这个错误，我不知道为什么，因为“query”变量打印出了一个“正确”的查询。我测试了使用静态的tableName和tColumnNames，运行得很好...如果有人可以帮助我解决这个问题，我真的会非常感谢。
private void createNewTable(String tableName, List<String> newTableColumns) throws SQLException {
// 将ArrayList转换为以逗号分隔的字符串
String tColumNames = String.join(",", newTableColumns);
Connection connection = dataSource.getConnection();
Statement stmt = connection.createStatement();
String query = "CREATE TABLE " + tableName + "( " + tColumNames + " );";
System.out.println("Consulta" + query);
stmt.executeUpdate(query);
stmt.close();
}
"query" 打印出：
CREATE TABLE Courses(Subject ID VARCHAR(200),Date* VARCHAR(200),Effective Date* VARCHAR(200) );
以下是错误信息：
com.microsoft.sqlserver.jdbc.SQLServerException: 在 'VARCHAR' 附近有语法错误。 at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1655) at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:885) at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:778) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445)
英文:
I am new in Java. Now I am trying to create a dynamic SQL table in a SQL Server Database from java. I have the table Name in a string, the column names in a ArrayList, and put the same type and length for all columns that I want. My code look like this, but when I run It I obtain this error, I don't know why, because the "query" variable is printing a "correct" Query. I test it writing a static tableName and tColumnNames and worked fine...If someone can help me to resolve it, I really will appreciate it. Thanks
private void createNewTable( String tableName, List<String> newTableColumns) throws SQLException {
//ArrayList to string separated by comma
String tColumNames = String.join(",",newTableColumns );
Connection connection = dataSource.getConnection();
Statement stmt = connection.createStatement();
String query = "CREATE TABLE "+tableName+"( "+tColumNames+" );";
System.out.println("Consulta"+query);
stmt.executeUpdate(query);
stmt.close();
}
"query" is printing this:
> CREATE TABLE Courses(Subject ID VARCHAR(200),Date*
> VARCHAR(200),Effective Date* VARCHAR(200) );
And it is the error
> com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near
> 'VARCHAR'. at
> com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
> at
> com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1655)
> at
> com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:885)
> at
> com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:778)
> at
> com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)
> at
> com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445)
答案1
得分: 1
你的Java代码看起来还可以，但是你的SQL中有语法错误。你能控制生成newTableColumns值的代码吗？那是你的问题所在。你的语句应该更像是这样：
CREATE TABLE Courses([Subject ID] VARCHAR(200), [Date*] VARCHAR(200), [Effective Date*] VARCHAR(200));
如果你的字段名中有空格或其他T-SQL保留字符或关键字，你需要用[]括起来。
如果你无法控制newTableColumns的值，那么你需要在生成或生成SQL语句之前对该字符串进行解析，以相应的格式进行格式化。
英文:
Your Java looks ok but there are syntax errors in your SQL. Do you have control of the code that generates the value for newTableColumns? That is where your problem is. Your statement should read something more like this:
CREATE TABLE Courses([Subject ID] VARCHAR(200), [Date*] VARCHAR(200), [Effective Date*] VARCHAR(200) );
If you have spaces or other T-SQL reserved characters or keywords you need to enclose them in [].
If you don't have control of the value of newTableColumns then you will need to parse that string to format it accordingly before or while you generate your SQL statement.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论