I want to store some properties in a database table. I'm using Apache Commons Configuration 2.1. I found this, but I can't make it to work...
I've googled around, but I haven't found any documentation or examples but the link I've written above.
(Edit: code is now fixed)
This is the database structure (postgresql):
-- Table: public.propcalendars
-- DROP TABLE public.propcalendars;
CREATE TABLE public.propcalendars
(
name character varying NOT NULL,
key character varying NOT NULL,
value character varying,
CONSTRAINT pk PRIMARY KEY (name, key)
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.propcalendars
OWNER TO postgres;
And the following data is stored:
name key value
diasemana SV2 0000010
fechafinal SV2 31/08
fechainicial SV2 01/07
This is my code:
public class PropertiesConfiguration {
/*
* Atributos
*/
private static PropertiesConfiguration INSTANCE = null;
private DatabaseConfiguration configFechaInicioAnual;
private DatabaseConfiguration configFechaFinAnual;
private DatabaseConfiguration configDiasSemana;
/*
* Métodos
*/
private PropertiesConfiguration() throws ConfigurationException {
configure();
}
private void configure() throws ConfigurationException {
PGSimpleDataSource postgres = new PGSimpleDataSource();
postgres.setServerName("ip");
postgres.setDatabaseName("database");
postgres.setPortNumber(5433);
postgres.setUser("user");
postgres.setPassword("***");
postgres.setCurrentSchema("public");
BasicConfigurationBuilder<DatabaseConfiguration> builder = new BasicConfigurationBuilder<DatabaseConfiguration>(DatabaseConfiguration.class);
builder.configure(
new Parameters()
.database()
.setDataSource(postgres)
.setTable("propcalendars")
.setKeyColumn("key")
.setValueColumn("value")
.setConfigurationNameColumn("name")
.setConfigurationName("fechainicial")
);
configFechaInicioAnual = new DatabaseConfiguration();
configFechaInicioAnual.setDataSource(postgres);
configFechaInicioAnual.setKeyColumn("key");
configFechaInicioAnual.setValueColumn("value");
configFechaInicioAnual.setConfigurationName("fechainicial");
}
public static PropertiesConfiguration getInstance() throws ConfigurationException{
if(INSTANCE == null){
INSTANCE = new PropertiesConfiguration();
}
return INSTANCE;
}
public DatosCalendario getInfoCalendario(String pClaveCalendario, String pInfoCalendario){
DatosCalendario dc = new DatosCalendario();
dc.descripcion = pInfoCalendario;
dc.inicio = configFechaInicioAnual.getString(pClaveCalendario);
dc.fin = "";//configFechaInicioAnual.getString(pClaveCalendario);
dc.diasSemana = 1;//configFechaInicioAnual.getInt(pClaveCalendario);
return dc;
}
public class DatosCalendario{
public String descripcion;
public String inicio;
public String fin;
public Integer diasSemana;
@Override
public String toString(){
return " DatosCalendario [ descripcion=" + descripcion + ", inicio=" + inicio + ", fin=" + fin + ", diasSemana=" + diasSemana + "]";
}
}
}
And this is the exception thrown when getting 'SV2' with 'fechainicial':
java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'null WHERE key ='SV2'' at line 1 at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:686) ~[mysql-connector-java-6.0.3.jar:6.0.3] at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:663) ~[mysql-connector-java-6.0.3.jar:6.0.3] at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:653) ~[mysql-connector-java-6.0.3.jar:6.0.3] at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:115) ~[mysql-connector-java-6.0.3.jar:6.0.3] at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2041) ~[mysql-connector-java-6.0.3.jar:6.0.3] at com.mysql.cj.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1827) ~[mysql-connector-java-6.0.3.jar:6.0.3] at com.mysql.cj.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1930) ~[mysql-connector-java-6.0.3.jar:6.0.3] at org.apache.commons.configuration2.DatabaseConfiguration$JdbcOperation.openResultSet(DatabaseConfiguration.java:847) ~[commons-configuration2-2.1.jar:2.1] at org.apache.commons.configuration2.DatabaseConfiguration$1.performOperation(DatabaseConfiguration.java:333) ~[commons-configuration2-2.1.jar:2.1] at org.apache.commons.configuration2.DatabaseConfiguration$JdbcOperation.execute(DatabaseConfiguration.java:742) [commons-configuration2-2.1.jar:2.1] at org.apache.commons.configuration2.DatabaseConfiguration.getPropertyInternal(DatabaseConfiguration.java:359) [commons-configuration2-2.1.jar:2.1] at org.apache.commons.configuration2.AbstractConfiguration.getProperty(AbstractConfiguration.java:992) [commons-configuration2-2.1.jar:2.1] at org.apache.commons.configuration2.AbstractConfiguration.getAndConvertProperty(AbstractConfiguration.java:1749) [commons-configuration2-2.1.jar:2.1] at org.apache.commons.configuration2.AbstractConfiguration.convert(AbstractConfiguration.java:1785) [commons-configuration2-2.1.jar:2.1] at org.apache.commons.configuration2.AbstractConfiguration.getString(AbstractConfiguration.java:1344) [commons-configuration2-2.1.jar:2.1] at com.ingartek.gtfs.bizkaibus.configuration.PropertiesConfiguration.getInfoCalendario(PropertiesConfiguration.java:83) [classes/:?] at com.ingartek.gtfs.bizkaibus.main.GenerarGtfs.main(GenerarGtfs.java:43) [classes/:?] 13:55:15.120 [main] DEBUG com.ingartek.gtfs.bizkaibus.main.GenerarGtfs - DatosCalendario [ descripcion=Sabados verano Julio Agosto, inicio=null, fin=, diasSemana=1]
What am I doing wrong? Could somebody provide an additional tutorial or documentation?
SOLUTION
As @Thilo told me, the solution was to use a table name without containing a "hyphen" ("-"). Unfortunately, it's working only in PostgreSQL and in MySQL I'm getting an exception:
java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key ='SV2' AND name='fechainicial'' at line 1
