Crouch
Crouch

Reputation: 896

Create MySQL Table in JSP

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

Answers (1)

Ravinder Reddy
Ravinder Reddy

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

Related Questions