private PGPoolingDataSource createDataSource(String dbName) {
PGPoolingDataSource dataSource;
dataSource = new PGPoolingDataSource();
dataSource.setUser("admin");
dataSource.setPassword("admin");
dataSource.setServerName("localhost");
dataSource.setDatabaseName(dbName);
return dataSource;
}
@Bean(destroyMethod = "close")
DataSource dataSource(Environment env) throws IllegalStateException, SQLException {
PGPoolingDataSource pgPoolingDataSource = new PGPoolingDataSource();
pgPoolingDataSource.setUrl(env.getRequiredProperty("db.url"));
pgPoolingDataSource.setUser(env.getRequiredProperty("db.username"));
pgPoolingDataSource.setPassword(env.getRequiredProperty("db.password"));
pgPoolingDataSource.setInitialConnections(
Integer.parseInt(env.getRequiredProperty("db.pool_size")));
return pgPoolingDataSource;
}
public ResourceState createConfig() {
String server = datasource.getServerName();
int port = datasource.getPortNumber();
String db = datasource.getDatabaseName();
String user = datasource.getUser();
String password = datasource.getPassword();
int maxConnections = datasource.getMaxConnections();
int initialConnections = datasource.getInitialConnections();
log.debug(
"Using PostgreSQL on " + server + ":" + port + ", database: " + db + " with user: "******"server", server);
config.putProperty("port", port);
config.putProperty("db", db);
config.putProperty("user", user);
config.putProperty("password", password);
config.putProperty("max-connections", maxConnections);
config.putProperty("initial-connections", initialConnections);
config.putProperty("schemas", Arrays.asList(new String[] {schema, schema_two}));
config.putProperty("default-schema", schema);
return config;
}
@Bean
public DataSource dataSource() {
final PGPoolingDataSource source = new PGPoolingDataSource();
source.setDataSourceName("cloudoholiq");
source.setServerName("localhost");
source.setDatabaseName("cloudoholiq");
source.setUser("postgres");
source.setPassword("postgres");
source.setMaxConnections(10);
return source;
}
@BeforeClass
public static void initDriver() throws Exception {
Class.forName("org.postgresql.Driver");
PoolingDataSource ds = new PoolingDataSource();
ds.setServerName(System.getProperty("pgsql.server", "localhost"));
ds.setPortNumber(Integer.parseInt(System.getProperty("pgsql.port", "5432")));
ds.setDatabaseName(System.getProperty("pgsql.db", "test"));
ds.setUser(System.getProperty("pgsql.user", "test"));
ds.setPassword(System.getProperty("pgsql.password", "test"));
ds.setMaxConnections(10);
ds.setInitialConnections(1);
try {
ds.initialize();
} catch (Exception e) {
skipTests = true;
System.out.println("Failed to initialize datasource. Tests will be skipped ...");
e.printStackTrace();
return;
}
datasource = ds;
schema = "xlo_test_" + UUID.randomUUID().toString().substring(0, 8);
schema_two = "xlo_test_" + UUID.randomUUID().toString().substring(0, 8);
if (schema.compareTo(schema_two) > 0) {
String tmp = schema;
schema = schema_two;
schema_two = tmp;
}
// first
try {
cleanup();
} catch (Exception e) {
// ignore
}
// create schema for the test
try (Connection c = datasource.getConnection()) {
try (CallableStatement s = c.prepareCall("create schema " + schema)) {
s.execute();
}
try (CallableStatement s = c.prepareCall("create schema " + schema_two)) {
s.execute();
}
}
createTables();
insertData();
}
public static void createTables() throws Exception {
try (Connection c = datasource.getConnection()) {
try (PreparedStatement ps =
c.prepareStatement(
"create table "
+ schema
+ ".addresses ("
+ "address_id integer PRIMARY KEY, "
+ "name varchar (255) NOT NULL, "
+ "street varchar (255) NOT NULL, "
+ "postcode varchar (10), "
+ "city varchar (60) NOT NULL, "
+ "country_iso char(2), "
+ "is_company boolean default false)")) {
ps.execute();
}
try (PreparedStatement ps =
c.prepareStatement(
"create table "
+ schema
+ ".orders ("
+ "order_id varchar (40) PRIMARY KEY, "
+ "create_date timestamp NOT NULL, "
+ "total int8 NOT NULL, "
+ "address_id integer NOT NULL, "
+ "FOREIGN KEY (address_id) REFERENCES "
+ schema
+ ".addresses (address_id))")) {
ps.execute();
}
try (PreparedStatement ps =
c.prepareStatement(
"create table "
+ schema_two
+ ".orders ("
+ "order_id varchar (40) PRIMARY KEY, "
+ "create_date timestamp NOT NULL, "
+ "total int8 NOT NULL, "
+ "address_id integer NOT NULL, "
+ "FOREIGN KEY (address_id) REFERENCES "
+ schema
+ ".addresses (address_id))")) {
ps.execute();
}
}
}
@AfterClass
public static void cleanup() throws SQLException {
if (skipTests()) {
return;
}
// delete schemas for the test
try (Connection c = datasource.getConnection()) {
try (CallableStatement s = c.prepareCall("drop schema " + schema_two + " cascade")) {
s.execute();
}
try (CallableStatement s = c.prepareCall("drop schema " + schema + " cascade")) {
s.execute();
}
}
}
public DBConnection() {
source = new PGPoolingDataSource();
// source.setDataSourceName("A Data Source");
source.setServerName(this.HOST);
source.setPortNumber(this.PORT);
source.setDatabaseName(this.DB);
source.setUser(this.USER);
source.setPassword(this.PASSWORD);
// source.setMaxConnections(20);//Максимальное значение
source.setInitialConnections(1); // Сколько соединений будет сразу открыто
}
public static void insertData() throws Exception {
try (Connection c = datasource.getConnection()) {
try (PreparedStatement ps =
c.prepareStatement("insert into " + schema + ".addresses VALUES (?,?,?,?,?,?,?)")) {
insertAddress(ps, 1, "John F. Doe", "Liveoak street 7", null, "London", "UK", false);
insertAddress(
ps, 2, "Lombaas Inc.", "Liveoak square 1", "94114", "San Francisco", "US", true);
}
try (PreparedStatement ps =
c.prepareStatement("insert into " + schema_two + ".orders VALUES (?,?,?,?)")) {
insertOrder(ps, "014-1003095", sdf.parse("2014-06-07 15:10:15").getTime(), 18990, 1);
insertOrder(ps, "014-2004096", sdf.parse("2014-04-02 11:06:12").getTime(), 43800, 2);
insertOrder(ps, "014-2004345", sdf.parse("2014-06-01 18:06:12").getTime(), 32500, 2);
}
}
}
@Test(timeout = 4000)
public void test18() throws Throwable {
Product product0 = new Product();
PGPoolingDataSource pGPoolingDataSource0 = PGPoolingDataSource.getDataSource("");
SQLDialect sQLDialect0 = SQLDialect.MARIADB;
Settings settings0 = new Settings();
DefaultDSLContext defaultDSLContext0 =
new DefaultDSLContext(pGPoolingDataSource0, sQLDialect0, settings0);
try {
new WorkspaceSnapshot(product0, defaultDSLContext0);
fail("Expecting exception: NullPointerException");
} catch (NullPointerException e) {
//
// no message in exception (getMessage() returned null)
//
assertThrownBy("org.jooq.impl.DataSourceConnectionProvider", e);
}
}
public static DataSource dataSource(Properties properties) throws IOException {
String jdbcUrl = properties.getProperty("revenj.jdbcUrl");
if (jdbcUrl == null) {
throw new IOException("revenj.jdbcUrl is missing from Properties");
}
org.postgresql.ds.PGPoolingDataSource dataSource = new PGPoolingDataSource();
dataSource.setUrl(jdbcUrl);
String user = properties.getProperty("user");
String revUser = properties.getProperty("revenj.user");
if (revUser != null && revUser.length() > 0) {
dataSource.setUser(revUser);
} else if (user != null && user.length() > 0) {
dataSource.setUser(user);
}
String password = properties.getProperty("password");
String revPassword = properties.getProperty("revenj.password");
if (revPassword != null && revPassword.length() > 0) {
dataSource.setPassword(revPassword);
} else if (password != null && password.length() > 0) {
dataSource.setPassword(password);
}
return dataSource;
}
// public JFreeReport makeReport(ReportGenerator generator) throws Exception {
//
// ProductDPanel p = new ProductDPanel();
//
// AUniversalDialog d = new AUniversalDialog(p, null, true);
// d.setTitleIcon(new
// javax.swing.ImageIcon(getClass().getResource("/tradeterminal/icons/srch_32.png")));
// d.setVisible(true); d.dispose();
//
// if (d.getReturnStatus() == ADialog.RET_OK) {
//
// Product1DataAdapter tm = new Product1DataAdapter(Setup.getSource(),
// p.getBeginDate(), p.getEndDate());
//
//
// JFreeReport report =
// generator.parseReport(getClass().getResource("/tradeterminal/reports/product1/product.xml"));
//
// TextElement t1 = LabelElementFactory.createLabelElement(
// "edqwe", new Rectangle2D.Double(10, 80, 200.0, 20.0), Color.black,
// ElementAlignment.LEFT, null,
// "За период с " + df.format(p.getBeginDate()) + " по " +
// df.format(p.getEndDate()) + ".");
//
// t1.setFontSize(10);
// t1.setBold(true);
//
// report.getReportHeader().addElement(t1);
//
// report.setData(tm);
//
// return report;
// } else {
// return null;
// }
//
// }
@Override
public JRViewer makeReport(PGPoolingDataSource source) throws Exception {
TTRProductDPanel p = new TTRProductDPanel();
AUniversalDialog d = new AUniversalDialog(p, null, true);
d.setTitleIcon(
new javax.swing.ImageIcon(
getClass().getResource("/tradeterminal/icons/TT_icons/32X32/reports/12.png")));
d.setVisible(true);
d.dispose();
if (d.getReturnStatus() == ADialog.RET_OK) {
//
// ADBProc proc = new ADBProc("rpt_prod_select_moving");
// proc.addInParametr(
// new ADBProcParametr(Types.DATE,
// p.getBeginDate()));
//
// proc.addInParametr(
// new ADBProcParametr(Types.DATE,
// p.getEndDate()));
//
// //PADBUtils.executeVoidProcedure(Setup.getSource(), proc);
//
// PADBResult result = PADBUtils.getResultSet(source, proc);
PreparedStatement ptmt =
source
.getConnection()
.prepareStatement(
"select "
+ "pg.id as groups_id, "
+ "pg.\"name\" as groups_name, "
+ "p.id as products_id, "
+ "p.\"name\" as products_name, "
+ "p.scod as scod, "
+ "p.list_price as list_price, "
+ "ppl.quantity as ppl_quantity, "
+ "ppl.summ as ppl_summ, "
+ "pmn.quantity as pmn_quantity, "
+ "pmn.summ as pmn_summ, "
+ "psl.quantity as psl_quantity, "
+ "psl.summ as psl_summ, "
+ "prt.quantity as prt_quantity, "
+ "prt.summ as prt_summ ,"
+ "select_products_quantity_for_id(p.id) as products_quantity "
+ "from products_groups pg, (select DISTINCT v.products_id as id , p.\"name\", p.products_groups_id, p.scod as scod, p.list_price as list_price "
+ "from v_operations v, products p "
+ "where products_id is not null AND v.products_id=p.id AND date(order_date) BETWEEN ? and ?) p "
+ "LEFT OUTER JOIN(select operation_type_code,products_id,sum(quantity) as quantity,sum(actual_price * quantity) as summ "
+ "from v_operations where operation_type_code='ppl' AND date(order_date) BETWEEN ? and ? "
+ "group by operation_type_code,products_id ) ppl ON (p.id=ppl.products_id) "
+ "LEFT OUTER JOIN(select operation_type_code,products_id,sum(quantity) as quantity,sum(actual_price * quantity) as summ "
+ "from v_operations where operation_type_code='pmn' AND date(order_date) BETWEEN ? and ? "
+ "group by operation_type_code,products_id ) pmn ON (p.id=pmn.products_id) "
+ "LEFT OUTER JOIN(select operation_type_code,products_id,sum(quantity) as quantity,sum(actual_price * quantity) as summ "
+ "from v_operations where operation_type_code='psl' AND date(order_date) BETWEEN ? and ? "
+ "group by operation_type_code,products_id ) psl ON (p.id=psl.products_id) "
+ "LEFT OUTER JOIN(select operation_type_code,products_id,sum(quantity) as quantity,sum(actual_price * quantity) as summ "
+ "from v_operations where operation_type_code='prt' AND date(order_date) BETWEEN ? and ? "
+ "group by operation_type_code,products_id ) prt ON (p.id=prt.products_id) "
+ "where pg.id = p.products_groups_id;");
ptmt.setObject(1, p.getBeginDate(), Types.DATE);
ptmt.setObject(2, p.getEndDate(), Types.DATE);
ptmt.setObject(3, p.getBeginDate(), Types.DATE);
ptmt.setObject(4, p.getEndDate(), Types.DATE);
ptmt.setObject(5, p.getBeginDate(), Types.DATE);
ptmt.setObject(6, p.getEndDate(), Types.DATE);
ptmt.setObject(7, p.getBeginDate(), Types.DATE);
ptmt.setObject(8, p.getEndDate(), Types.DATE);
ptmt.setObject(9, p.getBeginDate(), Types.DATE);
ptmt.setObject(10, p.getEndDate(), Types.DATE);
ResultSet rs = ptmt.executeQuery();
JasperReport jasperReport =
JasperCompileManager.compileReport(
getClass()
.getResource("/tt/reports/product_movong_a3/prod_obor_report.jrxml")
.openStream());
HashMap map = new HashMap();
map.put(
"TextRepParam",
"За период с " + df.format(p.getBeginDate()) + " по " + df.format(p.getEndDate()) + ".");
JasperPrint jasperPrint =
JasperFillManager.fillReport(jasperReport, map, new JRResultSetDataSource(rs));
rs.close();
ptmt.close();
return new JRViewer(jasperPrint);
} else {
return null;
}
}
public Connection getConnection() throws SQLException {
return source.getConnection();
}
