You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@metamodel.apache.org by Tim Kingsbury <Ti...@sas.com> on 2018/01/10 18:15:25 UTC
INSERT with a SELECT subquery
Hello,
Using Metamodel, is it possible to build an INSERT statement that is paired with a SELECT subquery? For example:
INSERT TABLE `us_south_es_etg`
SELECT
`lon`,
`lat`,
`number`,
`street`,
`unit`,
`city`,
`district`,
`region`,
`postcode`,
`id`,
`hash`
FROM us_south;
Any suggestions would be greatly appreciated!
Tim
Re: INSERT with a SELECT subquery
Posted by "gowri.tn" <go...@nic.in>.
Sir,
The following is our code: Still we are not able to succeed in insert values into table. Please advice.
With regards,
Gowri S
package com.nic.audit;
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Arrays;
import org.apache.metamodel.DataContext;
import org.apache.metamodel.MetaModelException;
import org.apache.metamodel.UpdateCallback;
import org.apache.metamodel.insert.RowInsertable;
import javax.security.auth.callback.Callback;
import org.apache.metamodel.UpdateScript;
import org.apache.metamodel.UpdateableDataContext;
import org.apache.metamodel.data.DataSet;
import org.apache.metamodel.DataContext;
import org.apache.metamodel.excel.ExcelConfiguration;
import org.apache.metamodel.excel.ExcelDataContext;
import org.apache.metamodel.query.FromItem;
/*import org.apache.metamodel.jdbc.JdbcDataContext;*/
import org.apache.metamodel.query.Query;
import org.apache.metamodel.schema.ColumnType;
import org.apache.metamodel.schema.Schema;
import org.apache.metamodel.schema.Table;
import org.apache.poi.ss.formula.functions.Column;
import org.datacleaner.connection.Datastore;
import org.datacleaner.connection.DatastoreConnection;
public class InsertQuery {
private static final String CONNECTION_STRING = "jdbc:postgresql://164.100.167.57:5432/testing";
private static final String USERNAME = "postgres";
private static final String PASSWORD = "postgres";
/**
* @param args
*/
public static void main(String[] args) throws Exception {
Connection connection = null;
Table table = null;
//DataSet ds;
try {
Class.forName("org.postgresql.Driver");
connection = DriverManager.getConnection("jdbc:postgresql://164.100.167.57:5432/testing","postgres","postgres");
System.out.println("started1:"+connection);
JdbcDataContext dc = new JdbcDataContext(connection);
System.out.println("started2:"+dc);
//UpdateableDataContext excel = new ExcelDataContext(new File("F:/CDC3_TN12_Cleaning report.xls"));
final DataContext dataContext = new ExcelDataContext(new File("C:/Users/user/Desktop/student1.xlsx"),
new ExcelConfiguration(
ExcelConfiguration.NO_COLUMN_NAME_LINE, false, false));
System.out.println("New DC:"+dataContext);
final Schema schema = dataContext.getDefaultSchema();
System.out.println("started3:"+schema);
Table[] tables = schema.getTables();
//Table[] table = schema.getTables();
//tables = schema.getTable(arg0)
System.out.println("Table--->:"+tables.length);
for(int i = 0;i<tables.length;i++)
{
table = schema.getTables()[i];
System.out.println("Table Name:"+table.getName());
Query q = new Query();
q.from(table);
System.out.println("Table Name:---->" + table.getName());
q.select(table.getColumns());
System.out.println("Columns Name:" + Arrays.toString(table.getColumnNames()));
//System.out.println("Columns: " + Arrays.toString(table.getColumnNames()));
Table table1 = dataContext.getTableByQualifiedLabel("test");
System.out.println("---->>"+table1);
DataSet ds = dataContext.query().from(table).select(table.getColumns()).execute();
System.out.println("hello--->"+ds);
try
{
dc.executeUpdate(new UpdateScript() {
@Override
public void run(UpdateCallback callback) {
System.out.println("hi");
}
});
while(ds.next()) {
System.out.println(">>> " + Arrays.toString(ds.getRow().getValues()));
>
> callback.insertInto(table1).values(ds.getRow()).execute();
>
}
}
catch (Exception e) {
// TODO: handle exception
System.out.println(e);
e.printStackTrace();
}
}
}
catch(Exception e) {
e.printStackTrace();
}
}
}
Output:
started1:org.postgresql.jdbc.PgConnection@134b60d
started2:com.nic.audit.JdbcDataContext@1a77460
New DC:org.apache.metamodel.excel.ExcelDataContext@d9c638
started3:Schema[name=student1.xlsx]
Table--->:3
Table Name:selvan
Table Name:---->selvan
Columns Name:[A, B, C, D]
hello--->DataSet[selectItems=[selvan.A, selvan.B, selvan.C, selvan.D]]
>>> [id, name, gender, age]
>>> [1, abi, female, 21]
>>> [2, banu, female, 16]
>>> [3, cibi, female, 18]
>>> [4, divi, female, 20]
>>> [5, elakiya, female, 21]
>>> [6, fahir, true, 25]
>>> [7, goki, male, 21]
>>> [8, deep, male, 19]
>>> [9, false, female, raja]
Table Name:raja
Table Name:---->raja
Columns Name:[A, B, C, D]
hello--->DataSet[selectItems=[raja.A, raja.B, raja.C, raja.D]]
>>> [id, name, gender, age]
>>> [1, Raja, male, 25]
>>> [2, ravi, male, 25]
>>> [3, selva, male, 25]
>>> [4, sara, male, 98]
>>> [5, praveena, male, 105]
>>> [6, jeevitha, female, 31]
Table Name:dtv
Table Name:---->dtv
Columns Name:[A, B, C, D, E, F]
hello--->DataSet[selectItems=[dtv.A, dtv.B, dtv.C, dtv.D, dtv.E, dtv.F]]
>>> [jio, sdf, sdfs, sdfsd, sdfsdf, sdfsdf]
>>> [1, 2, 3, 4, 5, 6]
>>> [2, 2, 3, 4, 5, 6]
>>> [3, 2, 3, 4, 5, 6]
>>> [4, 2, 3, 4, 5, 6]
>>> [5, 2, 3, 4, 5, 6]
>>> [6, 2, 3, 4, 5, 6]
>>> [7, 2, 3, 4, 5, 6]
>>> [8, 2, 3, 4, 5, 6]
>>> [9, 2, 3, 4, 5, 6]
>>> [10, 2, 3, 4, 5, 6]
>>> [11, 2, 3, 4, 5, 6]
>>> [12, 2, 3, 4, 5, 6]
>>> [13, 2, 3, 4, 5, 6]
>>> [14, 2, 3, 4, 5, 6]
>>> [15, 2, 3, 4, 5, 6]
>>> [16, 2, 3, 4, 5, 6]
>>> [17, 2, 3, 4, 5, 6]
>>> [18, 2, 3, 4, 5, 6]
>>> [19, 2, 3, 4, 5, 6]
>>> [20, 2, 3, 4, 5, 6]
>
>
>
Re: INSERT with a SELECT subquery
Posted by Kasper Sørensen <i....@gmail.com>.
I don't know what you're doing here since your formatting is very strange
looking.
I do see that you're dropping a table, which I don't understand why. Is it
failing because you're trying to insert into the table that was just
dropped?
Regardless, if you want help fixing your issues, please also provide an
exception stack trace.
2018-01-17 1:55 GMT-08:00 gowri.tn <go...@nic.in>:
>
>
> Table table = dataContext.getTableByQualifiedLabel("test");
>
>
> dc.executeUpdate(new UpdateScript() {
>
> @Override
>
> public void run(UpdateCallback callback) {
>
> callback.dropTable(schema.getTableByName("test")).execute();
>
> }
> });
>
>
> while(ds.next()) {
>
> >
> > System.out.println(">>> " + Arrays.toString(ds.getRow().getValues()));
> >
>
>
> >
> > callback.insertInto(table).values(ds.getRow()).execute();
> >
>
> >
>
>
>
> }
>
> OUTPUT:
>
> Excel sheet:(ds.getRow())
> >>> [1, abi, female, 21]
> >>> [2, banu, female, 16]
> >>> [3, cibi, female, 18]
> >>> [4, divi, female, 20]
> >>> [5, elakiya, female, 21]
> >>> [6, fahir, true, 25]
> >>> [7, goki, male, 21]
> >>> [8, deep, male, 19]
> >>> [9, false, female, raja]
>
> >
>
> Here is our output for the previous code for getting rows from Excel
> Datasheet. We are not able to insert the values into a Postgresql Table
> with the code given by your last mail The method, public void
> run(UpdateCallback callback) is not working Please help us in this regard.
>
> With regards,
>
> Gowri
>
>
>
>
> On 01/11/18 09:36 AM, Kasper Sørensen <i....@gmail.com>
> wrote:
> >
> > Hi there,
> >
> > No that's not available. But you do get something quite equivalent with
> > this:
> >
> > Table table = dataContext.getTableByName("us_south_es_etg");
> > dataContext.executeUpdate(callback -> {
> > try (DataSet ds =
> > dataContext.query().from("us_south").selectAll().execute()) {
> > while (ds.next()) {
> > callback.insertInto(table).values(ds.getRow()).execute();
> > }
> > }
> > });
> >
> >
>
> >
> >
> > 2018-01-10 10:15 GMT-08:00 Tim Kingsbury <Ti...@sas.com>:
> >
> > > Hello,
> > >
> > > Using Metamodel, is it possible to build an INSERT statement that is
> > > paired with a SELECT subquery? For example:
> > >
> > > INSERT TABLE `us_south_es_etg`
> > > SELECT
> > > `lon`,
> > > `lat`,
> > > `number`,
> > > `street`,
> > > `unit`,
> > > `city`,
> > > `district`,
> > > `region`,
> > > `postcode`,
> > > `id`,
> > > `hash`
> > > FROM us_south;
> > >
> > >
> > > Any suggestions would be greatly appreciated!
> > >
> > >
> > > Tim
> > >
> > >
> >
>
Re: INSERT with a SELECT subquery
Posted by "gowri.tn" <go...@nic.in>.
Table table = dataContext.getTableByQualifiedLabel("test");
dc.executeUpdate(new UpdateScript() {
@Override
public void run(UpdateCallback callback) {
callback.dropTable(schema.getTableByName("test")).execute();
}
});
while(ds.next()) {
>
> System.out.println(">>> " + Arrays.toString(ds.getRow().getValues()));
>
>
> callback.insertInto(table).values(ds.getRow()).execute();
>
>
}
OUTPUT:
Excel sheet:(ds.getRow())
>>> [1, abi, female, 21]
>>> [2, banu, female, 16]
>>> [3, cibi, female, 18]
>>> [4, divi, female, 20]
>>> [5, elakiya, female, 21]
>>> [6, fahir, true, 25]
>>> [7, goki, male, 21]
>>> [8, deep, male, 19]
>>> [9, false, female, raja]
>
Here is our output for the previous code for getting rows from Excel Datasheet. We are not able to insert the values into a Postgresql Table with the code given by your last mail The method, public void run(UpdateCallback callback) is not working Please help us in this regard.
With regards,
Gowri
On 01/11/18 09:36 AM, Kasper Sørensen <i....@gmail.com> wrote:
>
> Hi there,
>
> No that's not available. But you do get something quite equivalent with
> this:
>
> Table table = dataContext.getTableByName("us_south_es_etg");
> dataContext.executeUpdate(callback -> {
> try (DataSet ds =
> dataContext.query().from("us_south").selectAll().execute()) {
> while (ds.next()) {
> callback.insertInto(table).values(ds.getRow()).execute();
> }
> }
> });
>
>
>
>
> 2018-01-10 10:15 GMT-08:00 Tim Kingsbury <Ti...@sas.com>:
>
> > Hello,
> >
> > Using Metamodel, is it possible to build an INSERT statement that is
> > paired with a SELECT subquery? For example:
> >
> > INSERT TABLE `us_south_es_etg`
> > SELECT
> > `lon`,
> > `lat`,
> > `number`,
> > `street`,
> > `unit`,
> > `city`,
> > `district`,
> > `region`,
> > `postcode`,
> > `id`,
> > `hash`
> > FROM us_south;
> >
> >
> > Any suggestions would be greatly appreciated!
> >
> >
> > Tim
> >
> >
>
RE: INSERT with a SELECT subquery
Posted by Tim Kingsbury <Ti...@sas.com>.
Excellent. Thank you!!
Tim
-----Original Message-----
From: Kasper Sørensen [mailto:i.am.kasper.sorensen@gmail.com]
Sent: Wednesday, January 10, 2018 11:07 PM
To: dev@metamodel.apache.org
Subject: Re: INSERT with a SELECT subquery
EXTERNAL
Hi there,
No that's not available. But you do get something quite equivalent with
this:
Table table = dataContext.getTableByName("us_south_es_etg");
dataContext.executeUpdate(callback -> {
try (DataSet ds =
dataContext.query().from("us_south").selectAll().execute()) {
while (ds.next()) {
callback.insertInto(table).values(ds.getRow()).execute();
}
}
});
2018-01-10 10:15 GMT-08:00 Tim Kingsbury <Ti...@sas.com>:
> Hello,
>
> Using Metamodel, is it possible to build an INSERT statement that is
> paired with a SELECT subquery? For example:
>
> INSERT TABLE `us_south_es_etg`
> SELECT
> `lon`,
> `lat`,
> `number`,
> `street`,
> `unit`,
> `city`,
> `district`,
> `region`,
> `postcode`,
> `id`,
> `hash`
> FROM us_south;
>
>
> Any suggestions would be greatly appreciated!
>
>
> Tim
>
>
Re: INSERT with a SELECT subquery
Posted by Kasper Sørensen <i....@gmail.com>.
Hi there,
No that's not available. But you do get something quite equivalent with
this:
Table table = dataContext.getTableByName("us_south_es_etg");
dataContext.executeUpdate(callback -> {
try (DataSet ds =
dataContext.query().from("us_south").selectAll().execute()) {
while (ds.next()) {
callback.insertInto(table).values(ds.getRow()).execute();
}
}
});
2018-01-10 10:15 GMT-08:00 Tim Kingsbury <Ti...@sas.com>:
> Hello,
>
> Using Metamodel, is it possible to build an INSERT statement that is
> paired with a SELECT subquery? For example:
>
> INSERT TABLE `us_south_es_etg`
> SELECT
> `lon`,
> `lat`,
> `number`,
> `street`,
> `unit`,
> `city`,
> `district`,
> `region`,
> `postcode`,
> `id`,
> `hash`
> FROM us_south;
>
>
> Any suggestions would be greatly appreciated!
>
>
> Tim
>
>