1

I was running my java program with MySQL before and everything was alright. Now I am connecting the program to PostgreSQL, but it gives me some errors and I don't know where should I find the problem and how to fix it. If anyone could help I will be very appreciated.

I am using:

  • Java 15
  • Spring Boot
  • Tomcat 9
  • PostgreSQL 13
  • PostgreSQL JDBC 42.2.23

For running SQL queries and creating tables of my database, I am using a mapper repository which has an abstract class with interface and I customized mappers for each of my classes.

This is my connection pool for connecting to the database:

import org.apache.commons.dbcp.BasicDataSource;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class ConnectionPool {
    private static BasicDataSource ds = new BasicDataSource();
    private final static String dbURL = "jdbc:postgresql://...:.../SomeThing? 
    characterEncoding=utf8";
    private final static String dbUserName = "...";

    private ConnectionPool() {
    }

    static {
        ds.setDriverClassName("org.postgresql.Driver");
        // remote db
        ds.setUrl(dbURL);
        ds.setUsername(dbUserName);
        ds.setPassword(dbPassword);
        ds.setMinIdle(1);
        ds.setMaxIdle(2000);
        ds.setMaxOpenPreparedStatements(2000);
        setEncoding();
    }

    public static Connection getConnection() throws SQLException {
        try {
            return ds.getConnection();
        }catch (Exception e){
            ds.setPassword(dbPassword);
            return ds.getConnection();
        }
    }

    public static void setEncoding(){
        try {
            Connection connection = getConnection();
            Statement statement = connection.createStatement();
            statement.execute("ALTER DATABASE ... CHARACTER SET utf8mb4 COLLATE 
            utf8mb4_unicode_ci;");
            connection.close();
            statement.close();
        }
        catch (SQLException e)
        {
        System.out.println(e.getMessage());
        }
    }
}

This is an example of my course mapper class:

public class CourseMapper extends Mapper<Offering, CustomPair> implements ICourseMapper {

    private static final String COLUMNS = " code, classCode, name, units, type, instructor, capacity, signedUp, start, end, time ";
    private static final String TABLE_NAME = "COURSES";

    public CourseMapper(Boolean doManage) throws SQLException {
        if (doManage) {
            Connection con = ConnectionPool.getConnection();
            Statement st = con.createStatement();
            st.executeUpdate(String.format("DROP TABLE IF EXISTS %s", TABLE_NAME));
            st.executeUpdate(String.format(
                    "create table %s (\n" +
                            "    code varchar(255) not null,\n" +
                            "    classCode varchar(255) not null,\n" +
                            "    name tinytext not null,\n" +
                            "    units int not null,\n" +
                            "    type tinytext not null,\n" +
                            "    instructor tinytext not null,\n" +
                            "    capacity int not null,\n" +
                            "    signedUp int not null default 0,\n" +
                            "    start tinytext not null,\n" +
                            "    end tinytext not null,\n" +
                            "    time tinytext not null,\n" +
                            "    primary key(code, classCode)\n" +
                            ");",
                    TABLE_NAME));
            st.close();
            con.close();
        }
    }

    public CourseMapper() throws SQLException {
    }

    @Override
    protected String getFindStatement(CustomPair id) {
        return String.format("select * from %s where %s.%s = %s and %s.%s = %s", TABLE_NAME, TABLE_NAME, "code",
                StringUtils.quoteWrapper(id.getArgs().get(0)), TABLE_NAME, "classCode", StringUtils.quoteWrapper(id.getArgs().get(1)));
    }

With this, I was able to create tables in a MySQL database and implement queries (find,insert,delete...)

However, now that I want to use a PostgreSQL database, by running the project it gives me these errors:

org.postgresql.util.PSQLException: ERROR: syntax error at or near "end"
      Position: 304
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2552)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2284)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:322)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:322)
    at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:308)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:284)
    at org.postgresql.jdbc.PgStatement.executeUpdate(PgStatement.java:258)
    at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
    at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
    at com.internet.engineering.IECA5.repository.Course.CourseMapper.<init>(CourseMapper.java:24)
    at com.internet.engineering.IECA5.repository.MzRepository.createAllTables(MzRepository.java:35)
    at com.internet.engineering.IECA5.IeCa5Application.main(IeCa5Application.java:18)
org.postgresql.util.PSQLException: ERROR: type "tinytext" does not exist
      Position: 67
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2552)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2284)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:322)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:322)
    at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:308)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:284)
    at org.postgresql.jdbc.PgStatement.executeUpdate(PgStatement.java:258)
    at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
    at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
    at com.internet.engineering.IECA5.repository.Student.StudentMapper.<init>(StudentMapper.java:22)
mohsen
  • 524
  • 5
  • 14
  • Does this answer your question? [Escaping keyword-like column names in Postgres](https://stackoverflow.com/questions/7651417/escaping-keyword-like-column-names-in-postgres) – tgdavies Jul 25 '21 at 10:15
  • @tgdavies could you tell me how it may help? – mohsen Jul 25 '21 at 10:25
  • 2
    MySql and postgresql, and indeed all makes of table server, use different dialects of the SQL language. If you remove the verbose tracebacks from the error messages you gave us, you'll see two syntax errors generated by postgresql. It doesn't like the `tinytext` data type, nor does it like the column you named `end`. Try running your SQL in an interactive client program before embedding it in your Java program: debugging it is easier that way. – O. Jones Jul 25 '21 at 11:07
  • 3
    Postgres and mysql have slightly different sql flavors, SQL queries that are valid in mysql may be not valid in postgres and vice versa. When you write your queries like this it makes code db-vendor dependent. Try to use JPA based implementation in order to reduce db vendor dependency. – Monsieur Merso Jul 25 '21 at 11:26
  • What about using `varchar(255)` instead of `tinytext`? – dan1st Jul 25 '21 at 13:39
  • Where [in the manual](https://www.postgresql.org/docs/current/datatype.html) did you find the `tinytext` data type? –  Jul 25 '21 at 14:38
  • It may be useful for you to take a look at [Migrate your mindset](https://blog.sql-workbench.eu/post/migrate-your-mindset/). – Belayer Jul 25 '21 at 20:30

1 Answers1

1

Although all mainstream relational database systems speak SQL, they each have their own dialects and other quirks that deviate from the ISO-9075 SQL standard. You cannot just lift-and-shift a project from MySQL to PostgreSQL and expect everything to work, especially with data types.

The stacktraces show two different problems:

  1. You have a column called end, and end is a reserved word in PostgreSQL (and in the SQL standard), so it must be quoted to be usable as a column name: "end", or within a Java String \"end\".

  2. You use a datatype called tinytext which is specific to MySQL, and does not exist in PostgreSQL. Judging by the MySQL documentation, this allows a maximum of 255 characters, so VARCHAR(255) can be used as a replacement (though given the names of the columns, it looks like some of them are for duration, time or datetime values, so you may want to consider using a more suitable datatype).

Keep in mind that you may run into other related problems elsewhere in your program. MySQL is infamous for deviating far away from the SQL standard (though it has improved in recent years), and sometimes MySQL allows things the standard and most SQL dialects do not allow.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197