sample for working with jdbc connection directly

[lifemichael]

package com.example.addressbookdb;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import com.vaadin.data.util.sqlcontainer.SQLContainer;

import com.vaadin.data.util.sqlcontainer.connection.SimpleJDBCConnectionPool;

import com.vaadin.data.util.sqlcontainer.query.TableQuery;

import com.vaadin.server.VaadinRequest;

import com.vaadin.ui.Table;

import com.vaadin.ui.UI;

import com.vaadin.ui.VerticalLayout;

public class AddressbookdbUI extends UI {

static SimpleJDBCConnectionPool connectionPool;

static {

try {

connectionPool = new SimpleJDBCConnectionPool(

"com.mysql.jdbc.Driver",

"jdbc:mysql://server.zindell.com:3306/vaadin", "vaadin",

"michael", 2, 5);

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

private static final long serialVersionUID = 1L;

// creating a table for showing all data on screen

private Table contactList = new Table();

// creating a simple SQLContainer that represents

// a table on our database

SQLContainer contactContainer = createMySQLContainer();

// creating a simple layout

VerticalLayout layout = new VerticalLayout();

private static final String[] fieldNames = new String[] { "first", "last",

"company", "mobile", "work", "email", "street", "city", "state",

"country", "zip", "id", "facebook", "linkedin" };

protected void init(VaadinRequest request)

{

initLayout();

initContactList();

}

private void initLayout() {

// setting the main layout

setContent(layout);

// adding a table on out layout

layout.addComponent(contactList);

}

private void initContactList() {

// setting the table data source

contactList.setContainerDataSource(contactContainer);

// setting the visible coloumns to be just three

contactList.setVisibleColumns(new String[] { "first", "last", "email",

"company" });

// set the table to respond immediately when it changes

contactList.setImmediate(true);

}

private static SQLContainer createMySQLContainer() {

TableQuery query = null;

SQLContainer temp = null;

try {

System.out.println("inside createMySQLContainer");

Connection connection = connectionPool.reserveConnection();

Statement statement = connection.createStatement();

ResultSet result = statement

.executeQuery("select * from customers");

while (result.next()) {

System.out.println("firstname=" + result.getString("first")

+ " id=" + result.getString("id"));

}

connectionPool.releaseConnection(connection);

query = new TableQuery("customers", connectionPool);

query.setVersionColumn("id");

temp = new SQLContainer(query);

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return temp;

}

}