Create postgresql Table in java code

Question

I have a code in java that connects with postgreSQL database.

now, I want that when it connects to the database, i will also create database table.

but my problem is that, it will not create the database, and I don't know what is the problem.

here is my code:

Statement st = null;
        ResultSet rs = null;
        try{
            Class.forName("org.postgresql.Driver");
            System.out.println("connect");
            } catch (ClassNotFoundException cnfe){
              System.out.println("Could not find the JDBC driver!");
              System.exit(1);
            }
        Connection conn = null;
        try {
            //database location, database user, database password
            conn = DriverManager.getConnection
                           ("jdbc:postgresql:"POS_DB","postgres", "123456");
            st = conn.createStatement();
            String qs = "CREATE TABLE IF NOT EXISTS user(user_id SERIAL NOT NULL PRIMARY KEY,username varchar(225) NOT NULL UNIQUE,password varchar(225),islogged varchar(10))";
            String qs1 = "SELECT * FROM test";
        rs = st.executeQuery(qs);

            System.out.println("connect");
             } catch (SQLException sqle) {
               System.out.println("Could not connect");
               System.exit(1);
             }

I am sure that my sql statement in creating the table is correct. but when I run this, it will not create the table. If I replace the string into select sql, there is no problem.

does anyone have an idea about my case?

thanks in advance ..

score 5 · Answer 1 · answered Jul 24 '12 at 08:26

you probably want to use a PreparedStatement like so:

PreparedStatement ps = connection.prepareStatement("CREATE TABLE IF NOT EXISTS user(user_id SERIAL NOT NULL PRIMARY KEY,username varchar(225) NOT NULL UNIQUE,password varchar(225),islogged varchar(10))");
ps.executeUpdate();
ps.close();

You need executeUpdate for DML and DDL statements.

Also, make sure your DDL statement is correct, i don't know the exact "if not exists" syntax for PostgreSQL

score 2 · Accepted Answer · answered Jul 24 '12 at 08:33

2

This line :

conn = DriverManager.getConnection
                       ("jdbc:postgresql:"POS_DB","postgres", "123456");

is invalid.

You have a " before POS_DB that should'nt be here.

Too see some example check the documentation.

answered Jul 24 '12 at 08:33

alain.janinm

19,951
10
65
112

score 0 · Answer 3 · answered Dec 26 '18 at 07:20

0

postgresql 10 - 'user' is a keyword, thus not a good choice for a table. You will need a name that is not a reserved word.

answered Dec 26 '18 at 07:20

John Masiello

149
1
4

Create postgresql Table in java code

3 Answers3

Linked