Printing a Database Query

[Jorge Oliveira]

hello, i'm a student and i'm have problems with a work i must do to school...I'm doing a database query to a SQL database (Northwind) and making a query of select *from (table)....and i'm trying to do a print and print preview of the result...but have many problems...
The code the query is:

  public class Reporting extends JFrame 
{
  private Connection connection;
  private JTable table;
  public Reporting() 
  {
// Liga��o � BD
String url = "jdbc :-o	 dbc:Northwind";
String username = "";
String password = "";
try
{
    Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
    connection = DriverManager.getConnection( url, username, password );
}
catch ( ClassNotFoundException cnfex )
{
    System.err.println("Problemas c/ ligacao!" );
    cnfex.printStackTrace();
    System.exit( 1 ); // termina Programa
}
catch ( SQLException sqlex )
{
    System.err.println( "Falha de Coneccao");
    sqlex.printStackTrace();
}
getTable();
        setSize( 600, 200 );
        show();
}
private void getTable()
{
Statement statement;
ResultSet resultSet;
try
{
// String a Procurar

    String query = "SELECT * FROM Orders";
    statement = connection.createStatement();
    resultSet = statement.executeQuery( query );
displayResultSet( resultSet );
        
    statement.close();
}
catch ( SQLException sqlex )
{
    sqlex.printStackTrace();
}
}
private void displayResultSet( ResultSet rs )
throws SQLException
{
// Posiciona-se no primeiro registo
boolean moreRecords = rs.next();
// Se n�o houver registos
if ( ! moreRecords )
{
    JOptionPane.showMessageDialog( this,"A query solicitada n/ possui registos !");
    setTitle( "N�o h� Registos" );
    return;
}
Vector columnHeads = new Vector();
Vector rows = new Vector();
try
        {
        ResultSetMetaData rsmd = rs.getMetaData();
for ( int i = 1; i <= rsmd.getColumnCount(); ++i )
columnHeads.addElement( rsmd.getColumnName( i ) );
// Dados da Linha

while(rs.next()) 
{ 
    rows.addElement(getNextRow(rs, rsmd));
}

table = new JTable( rows, columnHeads );
JScrollPane scroller = new JScrollPane( table );
getContentPane().add( scroller, BorderLayout.CENTER );
validate();
}
catch ( SQLException sqlex ) 
{
sqlex.printStackTrace();
}
}
private Vector getNextRow( ResultSet rs, ResultSetMetaData rsmd ) throws SQLException
{
Vector currentRow = new Vector();
for(int i = 1; i <=rsmd.getColumnCount(); ++i)
switch(rsmd.getColumnType(i))
{
case Types.LONGVARCHAR:currentRow.addElement(rs.getString(i));
case Types.VARCHAR:currentRow.addElement(rs.getString(i));

break;
case Types.INTEGER:currentRow.addElement(new Long(rs.getLong(i)));
break;

}
return currentRow;
}
public void shutDown()
{
try {
connection.close();
}
catch ( SQLException sqlex ) 
{
System.err.println( "Erro ao disconnect" );
sqlex.printStackTrace();
}
}
public static void main( String args[] )
{
    final Reporting app = new Reporting();
 app.addWindowListener(new WindowAdapter()
 {
public void windowClosing( WindowEvent e )
{
app.shutDown();
System.exit( 0 );
}
}
);
  }
}

can somebody can help me ??
[e-mail]

[Jorge Oliveira]

I forgot myself to say that I have the code that allows print and print me preview. the problem that I have is that I do not know how to insert the result of query in the code that I have....

  
public class StocksTable extends JFrameimplements Printable 
{// NEW
protected JTable m_table;
protected StockTableData m_data;
protected JLabel m_title;
protected int m_maxNumPage = 1;// NEW
public StocksTable() {
super("Stocks Table");
setSize(600, 300);
UIManager.put("Table.focusCellHighlightBorder",
new LineBorder(Color.black, 0));
m_data = new StockTableData();
m_title = new JLabel(m_data.getTitle(),
new ImageIcon("money.gif"), SwingConstants.CENTER);
m_title.setFont(new Font("Helvetica",Font.PLAIN,24));
getContentPane().add(m_title, BorderLayout.NORTH);
m_table = new JTable();
m_table.setAutoCreateColumnsFromModel(false);
m_table.setModel(m_data);
for (int k = 0; k < m_data.getColumnCount(); k++) {
DefaultTableCellRenderer renderer = new
ColoredTableCellRenderer();
renderer.setHorizontalAlignment(
StockTableData.m_columns[k].m_alignment);
TableColumn column = new TableColumn(k,
StockTableData.m_columns[k].m_width, renderer, null);
column.setHeaderRenderer(createDefaultRenderer());
m_table.addColumn(column);
}
JTableHeader header = m_table.getTableHeader();
header.setUpdateTableInRealTime(true);
header.addMouseListener(new ColumnListener());
header.setReorderingAllowed(true);
m_table.getColumnModel().addColumnModelListener(m_data);
setJMenuBar(createMenuBar());
JScrollPane ps = new JScrollPane();
ps.getViewport().setBackground(m_table.getBackground());
ps.getViewport().add(m_table);
getContentPane().add(ps, BorderLayout.CENTER);
}
protected TableCellRenderer createDefaultRenderer() {
DefaultTableCellRenderer label = new DefaultTableCellRenderer() {
public Component getTableCellRendererComponent(JTable table, Object value,
 boolean isSelected, boolean hasFocus, int row, int column) {
if (table != null) {
JTableHeader header = table.getTableHeader();
if (header != null) {
setForeground(header.getForeground());
setBackground(header.getBackground());
setFont(header.getFont());
}
}
setText((value == null) ? "" : value.toString());
setBorder(UIManager.getBorder("TableHeader.cellBorder"));
return this;
}
};
label.setHorizontalAlignment(JLabel.CENTER);
return label;
}
protected JMenuBar createMenuBar() {
JMenuBar menuBar = new JMenuBar();
JMenu mFile = new JMenu("File");
mFile.setMnemonic('f');
JMenuItem mData = new JMenuItem("Retrieve Data...");
mData.setMnemonic('r');
ActionListener lstData = new ActionListener() {
public void actionPerformed(ActionEvent e) {
retrieveData();
}
};
mData.addActionListener(lstData);
mFile.add(mData);
mFile.addSeparator();
// NEW
JMenuItem mPrint = new JMenuItem("Print...");
mPrint.setMnemonic('p');
ActionListener lstPrint = new ActionListener() {
public void actionPerformed(ActionEvent e) {
Thread runner = new Thread() {
public void run() {
printData();
}
};
runner.start();
}
};
mPrint.addActionListener(lstPrint);
mFile.add(mPrint);
JMenuItem mPreview = new JMenuItem("Print Preview");
mPreview.setMnemonic('v');
ActionListener lstPreview = new ActionListener() {
public void actionPerformed(ActionEvent e) {
Thread runner = new Thread() {
public void run() {
setCursor(Cursor.getPredefinedCursor(
Cursor.WAIT_CURSOR));
PrintPreview preview = new PrintPreview(
StocksTable.this,
m_title.getText()+" Preview");
preview.setVisible(true);
setCursor(Cursor.getPredefinedCursor(
Cursor.DEFAULT_CURSOR));
}
};
runner.start();
}
};
mPreview.addActionListener(lstPreview);
mFile.add(mPreview);
mFile.addSeparator();
JMenuItem mExit = new JMenuItem("Exit");
mExit.setMnemonic('x');
ActionListener lstExit = new ActionListener() {
public void actionPerformed(ActionEvent e) {
System.exit(0);
}
};
mExit.addActionListener(lstExit);
mFile.add(mExit);
menuBar.add(mFile);
JMenu mView = new JMenu("View");
mView.setMnemonic('v');
TableColumnModel model = m_table.getColumnModel();
for (int k = 0; k < m_data.getColumnCount(); k++) {
JCheckBoxMenuItem item = new JCheckBoxMenuItem(
StockTableData.m_columns[k].m_title);
item.setSelected(true);
TableColumn column = model.getColumn(k);
item.addActionListener(new ColumnKeeper(column,
StockTableData.m_columns[k]));
mView.add(item);
}
menuBar.add(mView);
return menuBar;
}
// NEW
public void printData() {
try {
PrinterJob prnJob = PrinterJob.getPrinterJob();
prnJob.setPrintable(this);
if (!prnJob.printDialog())
return;
m_maxNumPage = 1;
prnJob.print();
}
catch (PrinterException e) {
e.printStackTrace();
}
}
// NEW
public int print(Graphics pg, PageFormat pageFormat,
 int pageIndex) throws PrinterException {
if (pageIndex >= m_maxNumPage)
return NO_SUCH_PAGE;
pg.translate((int)pageFormat.getImageableX(),
(int)pageFormat.getImageableY());
int wPage = 0;
int hPage = 0;
if (pageFormat.getOrientation() == pageFormat.PORTRAIT) {
wPage = (int)pageFormat.getImageableWidth();
hPage = (int)pageFormat.getImageableHeight();
}
else {
wPage = (int)pageFormat.getImageableWidth();
wPage += wPage/2;
hPage = (int)pageFormat.getImageableHeight();
pg.setClip(0,0,wPage,hPage);
}
int y = 0;
pg.setFont(m_title.getFont());
pg.setColor(Color.black);
Font fn = pg.getFont();
FontMetrics fm = pg.getFontMetrics();
y += fm.getAscent();
pg.drawString(m_title.getText(), 0, y);
y += 20; // space between title and table headers
Font headerFont = m_table.getFont().deriveFont(Font.BOLD);
pg.setFont(headerFont);
fm = pg.getFontMetrics();
TableColumnModel colModel = m_table.getColumnModel();
int nColumns = colModel.getColumnCount();
int x[] = new int[nColumns];
x[0] = 0;
int h = fm.getAscent();
y += h; // add ascent of header font because of baseline
// positioning (see figure 2.10)
int nRow, nCol;
for (nCol=0; nCol<nColumns; nCol++) {
TableColumn tk = colModel.getColumn(nCol);
int width = tk.getWidth();
if (x[nCol] + width > wPage) {
nColumns = nCol;
break;
}
if (nCol+1<nColumns)
x[nCol+1] = x[nCol] + width;
String title = (String)tk.getIdentifier();
pg.drawString(title, x[nCol], y);
}
pg.setFont(m_table.getFont());
fm = pg.getFontMetrics();
int header = y;
h = fm.getHeight();
int rowH = Math.max((int)(h*1.5), 10);
int rowPerPage = (hPage-header)/rowH;
m_maxNumPage = Math.max((int)Math.ceil(m_table.getRowCount()/
(double)rowPerPage), 1);
TableModel tblModel = m_table.getModel();
int iniRow = pageIndex*rowPerPage;
int endRow = Math.min(m_table.getRowCount(),
iniRow+rowPerPage);
for (nRow=iniRow; nRow<endRow; nRow++) {
y += h;
for (nCol=0; nCol<nColumns; nCol++) {
int col = m_table.getColumnModel().getColumn(nCol).getModelIndex();
Object obj = m_data.getValueAt(nRow, col);
String str = obj.toString();
if (obj instanceof ColorData)
pg.setColor(((ColorData)obj).m_color);
else
pg.setColor(Color.black);
pg.drawString(str, x[nCol], y);
}
}
System.gc();
return PAGE_EXISTS;
}

and

  
class StockTableData extends AbstractTableModel
implements TableColumnModelListener{
static final public ColumnData m_columns[] = {
new ColumnData( "Symbol", 100, JLabel.LEFT ),
new ColumnData( "Name", 160, JLabel.LEFT ),
new ColumnData( "Last", 100, JLabel.RIGHT ),
new ColumnData( "Open", 100, JLabel.RIGHT ),
new ColumnData( "Change", 100, JLabel.RIGHT ),
new ColumnData( "Change %", 100, JLabel.RIGHT ),
new ColumnData( "Volume", 100, JLabel.RIGHT )
};
public static ImageIcon COLUMN_UP = new ImageIcon("SortUp.gif");
public static ImageIcon COLUMN_DOWN = new ImageIcon("SortDown.gif");
protected SimpleDateFormat m_frm;
protected NumberFormat m_volumeFormat;
protected Vector m_vector;
protected Date m_date;
protected int m_columnsCount = m_columns.length;
public intm_sortCol = 0;
public boolean m_sortAsc = true;
public StockTableData() {
m_frm = new SimpleDateFormat("MM/dd/yyyy");
m_volumeFormat = NumberFormat.getInstance();
m_volumeFormat.setGroupingUsed(true);
m_volumeFormat.setMaximumFractionDigits(0);
m_vector = new Vector();
setDefaultData();
}
public void setDefaultData() {
try {
m_date = m_frm.parse("12/18/2004");
}
catch (java.text.ParseException ex) {
m_date = null;
}
m_vector.removeAllElements();
m_vector.addElement(new StockData("ORCL", "Oracle Corp.",
23.6875, 25.375, -1.6875, -6.42, 24976600));
m_vector.addElement(new StockData("EGGS", "Egghead.com",
17.25, 17.4375, -0.1875, -1.43, 2146400));
m_vector.addElement(new StockData("T", "AT&T",
65.1875, 66, -0.8125, -0.10, 554000));
m_vector.addElement(new StockData("LU", "Lucent Technology",
64.625, 59.9375, 4.6875, 9.65, 29856300));
m_vector.addElement(new StockData("FON", "Sprint",
104.5625, 106.375, -1.8125, -1.82, 1135100));
m_vector.addElement(new StockData("ENML", "Enamelon Inc.",
4.875, 5, -0.125, 0, 35900));
m_vector.addElement(new StockData("CPQ", "Compaq Computers",
30.875, 31.25, -0.375, -2.18, 11853900));
m_vector.addElement(new StockData("MSFT", "Microsoft Corp.",
94.0625, 95.1875, -1.125, -0.92, 19836900));
m_vector.addElement(new StockData("DELL", "Dell Computers",
46.1875, 44.5, 1.6875, 6.24, 47310000));
m_vector.addElement(new StockData("SUNW", "Sun Microsystems",
140.625, 130.9375, 10, 10.625, 17734600));
m_vector.addElement(new StockData("IBM", "Intl. Bus. Machines",
183, 183.125, -0.125, -0.51, 4371400));
m_vector.addElement(new StockData("HWP", "Hewlett-Packard",
70, 71.0625, -1.4375, -2.01, 2410700));
m_vector.addElement(new StockData("UIS", "Unisys Corp.",
28.25, 29, -0.75, -2.59, 2576200));
m_vector.addElement(new StockData("SNE", "Sony Corp.",
96.1875, 95.625, 1.125, 1.18, 330600));
m_vector.addElement(new StockData("NOVL", "Novell Inc.",
24.0625, 24.375, -0.3125, -3.02, 6047900));
m_vector.addElement(new StockData("HIT", "Hitachi, Ltd.",
78.5, 77.625, 0.875, 1.12, 49400));
sortData();
}
public int getRowCount() {
return m_vector==null ? 0 : m_vector.size();
}
public int getColumnCount() {
return m_columnsCount;
}
public String getColumnName(int column) {
return m_columns[column].m_title;
}
public Icon getColumnIcon(int column) {
if (column==m_sortCol)
return m_sortAsc ? COLUMN_UP : COLUMN_DOWN;
return null;
}
public boolean isCellEditable(int nRow, int nCol) {
return false;
}
public Object getValueAt(int nRow, int nCol) {
if (nRow < 0 || nRow>=getRowCount())
return "";
StockData row = (StockData)m_vector.elementAt(nRow);
switch (nCol) {
case 0: return row.m_symbol;
case 1: return row.m_name;
case 2: return row.m_last;
case 3: return row.m_open;
case 4: return row.m_change;
case 5: return row.m_changePr;
case 6: return m_volumeFormat.format(row.m_volume);
}
return "";
}
public String getTitle() {
if (m_date==null)
return "Stock Quotes";
return "Stock Quotes at "+m_frm.format(m_date);
}

[Peter den Haan]

Originally posted by Jorge Oliveira:
[...] i'm trying to do a print and print preview of the result...but have many problems...

That's a fair chunk of code, Jorge. What problems do you have exactly? The choice of forum suggests that it's the database side you have problems with, rather than the previewing/printing, but you don't say what.
- Peter

[Jorge Oliveira]

the problem is that i need an aplications or apllet that make a query(database select) select *from <table> and show an Jtable with the result of the query...(see code of REPORTING)...
but i needed that jtable (Reporting) can printed and print preview...
something like seemed in:
http://developer.java.sun.com/developer/onlineTraining/Programming/JDCBook/advprint.html#sales
can you help me ?