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)
