public void testExecuteQuery() throws Exception {
if (!isConfigured()) {
return;
}
DataContext dc = new JdbcDataContext(getConnection());
Schema schema = dc.getDefaultSchema();
Table actorTable = schema.getTableByName("actor");
assertEquals(
"[Column[name=actor_id,columnNumber=0,type=SMALLINT,nullable=false,nativeType=SMALLINT UNSIGNED,columnSize=5], Column[name=first_name,columnNumber=1,type=VARCHAR,nullable=false,nativeType=VARCHAR,columnSize=45], Column[name=last_name,columnNumber=2,type=VARCHAR,nullable=false,nativeType=VARCHAR,columnSize=45], Column[name=last_update,columnNumber=3,type=TIMESTAMP,nullable=false,nativeType=TIMESTAMP,columnSize=19]]",
Arrays.toString(actorTable.getColumns()));
Table filmTable = schema.getTableByName("film");
assertEquals(
"[Column[name=film_id,columnNumber=0,type=SMALLINT,nullable=false,nativeType=SMALLINT UNSIGNED,columnSize=5], Column[name=title,columnNumber=1,type=VARCHAR,nullable=false,nativeType=VARCHAR,columnSize=255], Column[name=description,columnNumber=2,type=LONGVARCHAR,nullable=true,nativeType=TEXT,columnSize=65535], Column[name=release_year,columnNumber=3,type=DATE,nullable=true,nativeType=YEAR,columnSize=0], Column[name=language_id,columnNumber=4,type=TINYINT,nullable=false,nativeType=TINYINT UNSIGNED,columnSize=3], Column[name=original_language_id,columnNumber=5,type=TINYINT,nullable=true,nativeType=TINYINT UNSIGNED,columnSize=3], Column[name=rental_duration,columnNumber=6,type=TINYINT,nullable=false,nativeType=TINYINT UNSIGNED,columnSize=3], Column[name=rental_rate,columnNumber=7,type=DECIMAL,nullable=false,nativeType=DECIMAL,columnSize=4], Column[name=length,columnNumber=8,type=SMALLINT,nullable=true,nativeType=SMALLINT UNSIGNED,columnSize=5], Column[name=replacement_cost,columnNumber=9,type=DECIMAL,nullable=false,nativeType=DECIMAL,columnSize=5], Column[name=rating,columnNumber=10,type=CHAR,nullable=true,nativeType=ENUM,columnSize=5], Column[name=special_features,columnNumber=11,type=CHAR,nullable=true,nativeType=SET,columnSize=54], Column[name=last_update,columnNumber=12,type=TIMESTAMP,nullable=false,nativeType=TIMESTAMP,columnSize=19]]",
Arrays.toString(filmTable.getColumns()));
Table filmActorJoinTable = schema.getTableByName("film_actor");
assertEquals(
"[Column[name=actor_id,columnNumber=0,type=SMALLINT,nullable=false,nativeType=SMALLINT UNSIGNED,columnSize=5], "
+ "Column[name=film_id,columnNumber=1,type=SMALLINT,nullable=false,nativeType=SMALLINT UNSIGNED,columnSize=5], "
+ "Column[name=last_update,columnNumber=2,type=TIMESTAMP,nullable=false,nativeType=TIMESTAMP,columnSize=19]]",
Arrays.toString(filmActorJoinTable.getColumns()));
Query q = new Query();
q.from(new FromItem(actorTable).setAlias("a"));
q.select(actorTable.getColumns());
q.getSelectClause().getItem(0).setAlias("foo-bar");
assertEquals(
"SELECT a.`actor_id` AS foo-bar, a.`first_name`, a.`last_name`, a.`last_update` FROM sakila.`actor` a",
q.toString());
FilterItem f1 = new FilterItem(q.getSelectClause().getItem(0), OperatorType.EQUALS_TO, 5);
FilterItem f2 = new FilterItem(q.getSelectClause().getItem(0), OperatorType.EQUALS_TO, 8);
q.where(new FilterItem(f1, f2));
DataSet dataSet = dc.executeQuery(q);
TableModel tableModel = new DataSetTableModel(dataSet);
assertEquals(4, tableModel.getColumnCount());
assertEquals(2, tableModel.getRowCount());
assertEquals("LOLLOBRIGIDA", tableModel.getValueAt(0, 2));
q.setMaxRows(1);
dataSet = dc.executeQuery(q);
tableModel = new DataSetTableModel(dataSet);
assertEquals(4, tableModel.getColumnCount());
assertEquals(1, tableModel.getRowCount());
assertEquals("LOLLOBRIGIDA", tableModel.getValueAt(0, 2));
q.setMaxRows(1);
q.setFirstRow(2);
dataSet = dc.executeQuery(q);
tableModel = new DataSetTableModel(dataSet);
assertEquals(4, tableModel.getColumnCount());
assertEquals(1, tableModel.getRowCount());
assertEquals("JOHANSSON", tableModel.getValueAt(0, 2));
q.getWhereClause().removeItems();
q.setMaxRows(25);
q.setFirstRow(1);
dataSet = dc.executeQuery(q);
tableModel = new DataSetTableModel(dataSet);
assertEquals(4, tableModel.getColumnCount());
assertEquals(25, tableModel.getRowCount());
assertEquals("GUINESS", tableModel.getValueAt(0, 2).toString());
}