Reputation: 896
Is there a way to create a MySQL table in JSP dynamically? Ideally it would be for use in a forum the user types the name of a thread and it would create a table with that name in the database.
I'm thinking small for the time being though I can't see if it's actually do-able i've given it a go but it didn't work, in fairness I didn't expect it to.
This is what I've tried,
<sql:query var="dbData1">
CREATE TABLE test (id int NOT NULL)
</sql:query>
Upvotes: 2
Views: 1866
Reputation: 23992
You need a dataSource
defined and the same is referred in the query
statement.
Example 1:
Define DataSource like this:
<sql:setDataSource var="dsn" driver="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost/my_database_name_here"
user="userName" password="pass_code_here"/>
And, refer the same DSN in the query constructor like this:
<sql:query dataSource="${dsn}" var="dbData1">
CREATE TABLE test (id int NOT NULL);
</sql:query>
This kind of dsn
definition in jsp
is not advised to be practised.
In the above example you can see that the Database access credentials are exposed open.
You better define the DSN in your server config file (for example server.xml for Tomcat) and use it.
Example 2:
If you are using Tomcat, then define the data source in Server.xml, like this:
<Resource
name="jdbc/dsn_on_my_db"
auth="Container"
type="javax.sql.DataSource"
username="yourusername"
password="yourpassword"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/my_db_name_here"
maxActive="15"
maxIdle="7"
validationQuery="Select 1" />
And in your jsp page refer to the DSN like this:
<sql:setDataSource dataSource="jdbc/dsn_on_my_db" />
And, use the query as you defined in your posting:
<sql:query var="dbData1">
CREATE TABLE test (id int NOT NULL);
</sql:query>
Alternatively,
<sql:query var="dbData1"
dataSource="${applicationScope.dsn_on_my_db}">
Upvotes: 2