CODEWITHSUNDEEP
javapostgresqlspring-bootjdbc
mohsen
mohsen

Reputation: 614

Changing database from MySQL to PostgreSQL in java

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:

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)

Upvotes: 1

Views: 1072

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 108939

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.

Upvotes: 1

Related Questions