How do I compress both this postgresql query into one statement in java?

Question

package database;


    import java.io.IOException;
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    import database.Dbconnect;

    public class CreateQuery {
        Connection conn;

        public CreateQuery() throws ClassNotFoundException, SQLException, IOException {
            conn=new Dbconnect().returnDatabaseConnection();
        }
        public int addNewLayertoDB(String feature_name,String shape,int Latitude , int Longitude , int feature_geom , String feature_details){
            try {
                PreparedStatement statement = null;
                String table_name = feature_name + "_" + shape; 
                String query = "CREATE TABLE EtherMap "+table_name+" ("+ feature_name+" (20))";
                statement = conn.prepareStatement(query);
                statement.setString(1, feature_name);
                statement.execute();
                String squery = "ALTER TABLE EtherMap" +table_name+"  ADD COLUMN geom int , ADD COLUMN shape character(10)";
                return 1;
                } catch (SQLException ex) {
                return 0;
            }
        }




        public void closeConn() throws SQLException {
            if (conn != null) {
                this.conn.close();
            }
        }

    }

Can I club both squery and query variable into one query ? If yes , then how ?

Could you show **only** the SQL Statements in question? That would make it a lot easier for someone trying to help you. — , Mar 14 '11 at 15:28
That call to `PreparedStatement.setString` isn't going to work without a query parameter in the text itself. You should be passing in something like `"CREATE TABLE ? (? VARCHAR(20), geom int, shape char(10))"`, with ? for each value you want to set dynamically. — Vance Maverick, Mar 14 '11 at 15:29

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

1

Maybe this will help you:

Parameters cannot be used to parameterize the table, or parameterize any database objects. They're mostly used for parameterizing WHERE/HAVING clauses.

To do what you want, you'll need to do the substitution yourself and create a regular statement as needed.

edited Jun 20 '20 at 09:12

Community

1
1

answered Mar 14 '11 at 15:37

David Weiser

5,190
4
28
35

Jeremy · Answer 2 · 2011-03-14T15:32:46.803

0

You can simply follow the CREATE TABLE syntax to write a single query.

String query = "create table "+table_name+" ( geom int, shape character(10) );";

edited Mar 14 '11 at 15:32

answered Mar 14 '11 at 15:26

Jeremy

22,188
4
68
81

Won't work. You cannot pass the table name into a PreparedStatement – Mar 14 '11 at 15:31
@a_horse_with_no_name: That's unfortunate. – Jeremy Mar 14 '11 at 15:32

score 0 · Answer 3 · answered Mar 14 '11 at 15:28

0

Why don't you add all the columns to the table while creating it. That way you avoid the second ALTER TABLE query.

String query = "CREATE TABLE EtherMap "+table_name+" ("+ feature_name+" character(20), geom int , shape character(10)";

answered Mar 14 '11 at 15:28

Nishan

2,821
4
27
36

How do I compress both this postgresql query into one statement in java?

3 Answers3