private void fillContainers() in AddrressBook

Hi Experts,

I'm new to Vaadin. Appreciate if you can help me in my problem running the AddressBook codes.
I' m using MS SQL Server 2008 database. When I run the AddressBook codes, it displays the following exception:

Below also my connection to MS SQL server 2008 and the whole codes of DatabaseHelper.

Thanks.
Donald

javax.servlet.ServletException: java.lang.NullPointerException
com.vaadin.terminal.gwt.server.AbstractApplicationServlet.handleServiceException(AbstractApplicationServlet.java:1010)
com.vaadin.terminal.gwt.server.AbstractApplicationServlet.service(AbstractApplicationServlet.java:548)
javax.servlet.http.HttpServlet.service(HttpServlet.java:722)

root cause

java.lang.NullPointerException
com.example.agencyservicesdbank.data.DatabaseHelper.fillContainers(DatabaseHelper.java:118)
com.example.agencyservicesdbank.data.DatabaseHelper.<init>(DatabaseHelper.java:49)
com.example.agencyservicesdbank.AgencyservicesdbankApplication.<init>(AgencyservicesdbankApplication.java:61)
sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
java.lang.reflect.Constructor.newInstance(Unknown Source)
java.lang.Class.newInstance0(Unknown Source)
java.lang.Class.newInstance(Unknown Source)
com.vaadin.terminal.gwt.server.ApplicationServlet.getNewApplication(ApplicationServlet.java:82)
com.vaadin.terminal.gwt.server.AbstractApplicationServlet.createApplication(AbstractApplicationServlet.java:984)
com.vaadin.terminal.gwt.server.AbstractApplicationServlet.findApplicationInstance(AbstractApplicationServlet.java:807)
com.vaadin.terminal.gwt.server.AbstractApplicationServlet.service(AbstractApplicationServlet.java:456)
javax.servlet.http.HttpServlet.service(HttpServlet.java:722)

MS SQL server 2008 Connection:

connectionPool = new SimpleJDBCConnectionPool(
"com.microsoft.sqlserver.jdbc.SQLServerDriver",
"jdbc:sqlserver://127.0.0.1:1433;databaseName=dbAgency;integratedSecurity=true", "", "");

package com.example.agencyservicesdbank.data;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Random;

import com.vaadin.addon.sqlcontainer.SQLContainer;
import com.vaadin.addon.sqlcontainer.connection.JDBCConnectionPool;
import com.vaadin.addon.sqlcontainer.connection.SimpleJDBCConnectionPool;
import com.vaadin.addon.sqlcontainer.query.TableQuery;

@SuppressWarnings("serial")
public class DatabaseHelper implements Serializable {
/**
* Natural property order for SQLContainer linked with the PersonAddress
* database table. Used in tables and forms.
*/
public static final Object[] NATURAL_COL_ORDER = new Object[] {
"firstname", "lastname", "email", "phonenumber", "streetaddress",
"postalcode", "cityId" };

/**
* "Human readable" captions for properties in same order as in
* NATURAL_COL_ORDER.
*/
public static final String[] COL_HEADERS_ENGLISH = new String[] {
"First name", "Last name", "Email", "Phone number",
"Street Address", "Postal Code", "City" };

/**
* JDBC Connection pool and the two SQLContainers connecting to the persons
* and cities DB tables.
*/
private JDBCConnectionPool connectionPool = null;
private SQLContainer personContainer = null;
private SQLContainer cityContainer = null;

/**
* Enable debug mode to output SQL queries to System.out.
*/
private boolean debugMode = false;

public DatabaseHelper() {
initConnectionPool();
initDatabase();
initContainers();
fillContainers();
}

private void initConnectionPool() {
try {
connectionPool = new SimpleJDBCConnectionPool(
"com.microsoft.sqlserver.jdbc.SQLServerDriver",
"jdbc:sqlserver://127.0.0.1:1433;databaseName=dbAgency;integratedSecurity=true", "", "");
} catch (SQLException e) {
e.printStackTrace();
}
}

private void initDatabase() {
try {
Connection conn = connectionPool.reserveConnection();
Statement statement = conn.createStatement();
try {
statement.executeQuery("SELECT * FROM personaddress");
statement.executeQuery("SELECT * FROM city");
} catch (SQLException e) {
/*
* Failed, which means that the database is not yet initialized
* => Create the tables
*/
statement.execute("create table city (id integer generated always as identity, name varchar(64), version integer default 0 not null)");
statement.execute("alter table city add primary key (id)");
statement.execute("create table personaddress "
+ "(id integer generated always as identity, "
+ "firstname varchar(64), lastname varchar(64), "
+ "email varchar(64), phonenumber varchar(64), "
+ "streetaddress varchar(128), postalcode integer, "
+ "cityId integer not null, version integer default 0 not null , "
+ "FOREIGN KEY (cityId) REFERENCES city(id))");
statement.execute("alter table personaddress add primary key (id)");
}
statement.close();
conn.commit();
connectionPool.releaseConnection(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}

private void initContainers() {
try {
/* TableQuery and SQLContainer for personaddress -table */
TableQuery q1 = new TableQuery("personaddress", connectionPool);
q1.setVersionColumn("version");
q1.setDebug(debugMode);
personContainer = new SQLContainer(q1);
personContainer.setDebugMode(debugMode);

/* TableQuery and SQLContainer for city -table */
TableQuery q2 = new TableQuery("city", connectionPool);
q2.setVersionColumn("version");
q2.setDebug(debugMode);
cityContainer = new SQLContainer(q2);
cityContainer.setDebugMode(debugMode);
} catch (SQLException e) {
e.printStackTrace();
}
}

/**
* Method to generate dummy data to the database. Everything is added to the
* database through the SQLContainers.
*/
private void fillContainers() {
if (personContainer.size() == 0 && cityContainer.size() == 0) {
/* Create cities */
final String cities[] = { "NoCity", "Amsterdam", "Berlin",
"Helsinki", "Hong Kong", "London", "Luxemburg", "New York",
"Oslo", "Paris", "Rome", "Stockholm", "Tokyo", "Turku" };
for (int i = 0; i < cities.length; i++) {
Object id = cityContainer.addItem();
cityContainer.getContainerProperty(id, "name").setValue(
cities); } try { cityContainer.commit(); } catch (SQLException e) { e.printStackTrace(); }

final String[] fnames = { "Peter", "Alice", "Joshua", "Mike",
"Olivia", "Nina", "Alex", "Rita", "Dan", "Umberto",
"Henrik", "Rene", "Lisa", "Marge" };
final String[] lnames = { "Smith", "Gordon", "Simpson", "Brown",
"Clavel", "Simons", "Verne", "Scott", "Allison", "Gates",
"Rowling", "Barks", "Ross", "Schneider", "Tate" };

final String streets[] = { "4215 Blandit Av.", "452-8121 Sem Ave",
"279-4475 Tellus Road", "4062 Libero. Av.",
"7081 Pede. Ave", "6800 Aliquet St.",
"P.O. Box 298, 9401 Mauris St.", "161-7279 Augue Ave",
"P.O. Box 496, 1390 Sagittis. Rd.", "448-8295 Mi Avenue",
"6419 Non Av.", "659-2538 Elementum Street",
"2205 Quis St.", "252-5213 Tincidunt St.",
"P.O. Box 175, 4049 Adipiscing Rd.", "3217 Nam Ave",
"P.O. Box 859, 7661 Auctor St.", "2873 Nonummy Av.",
"7342 Mi, Avenue", "539-3914 Dignissim. Rd.",
"539-3675 Magna Avenue", "Ap #357-5640 Pharetra Avenue",
"416-2983 Posuere Rd.", "141-1287 Adipiscing Avenue",
"Ap #781-3145 Gravida St.", "6897 Suscipit Rd.",
"8336 Purus Avenue", "2603 Bibendum. Av.",
"2870 Vestibulum St.", "Ap #722 Aenean Avenue",
"446-968 Augue Ave", "1141 Ultricies Street",
"Ap #992-5769 Nunc Street", "6690 Porttitor Avenue",
"Ap #105-1700 Risus Street",
"P.O. Box 532, 3225 Lacus. Avenue", "736 Metus Street",
"414-1417 Fringilla Street",
"Ap #183-928 Scelerisque Road", "561-9262 Iaculis Avenue" };
Random r = new Random(0);
try {
for (int i = 0; i < 100; i++) {
Object id = personContainer.addItem();
String firstName = fnames[r.nextInt(fnames.length)];
String lastName = lnames[r.nextInt(lnames.length)];
personContainer.getContainerProperty(id, "firstname")
.setValue(firstName);
personContainer.getContainerProperty(id, "lastname")
.setValue(lastName);
personContainer.getContainerProperty(id, "email").setValue(
firstName.toLowerCase() + "."
+ lastName.toLowerCase() + "@vaadin.com");
personContainer.getContainerProperty(id, "phonenumber")
.setValue(
"+358 02 555 " + r.nextInt(10)
+ r.nextInt(10) + r.nextInt(10)
+ r.nextInt(10));
personContainer.getContainerProperty(id, "streetaddress")
.setValue(streets[r.nextInt(streets.length)]);
int n = r.nextInt(100000);
if (n < 10000) {
n += 10000;
}
personContainer.getContainerProperty(id, "postalcode")
.setValue(n);
personContainer.getContainerProperty(id, "cityId")
.setValue(r.nextInt(cities.length));
}
} catch (Exception e) {
e.printStackTrace();
}
try {
personContainer.commit();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

public SQLContainer getPersonContainer() {
return personContainer;
}

public SQLContainer getCityContainer() {
return cityContainer;
}

/**
* Fetches a city name based on its key.
*
* @param cityId
* Key
* @return City name
*/
public String getCityName(int cityId) {
Object cityItemId = cityContainer.getIdByIndex(cityId);
return cityContainer.getItem(cityItemId).getItemProperty("name")
.getValue().toString();
}

/**
* Adds a new city to the container and commits changes to the database.
*
* @param cityName
* Name of the city to add
* @return true if the city was added successfully
*/
public boolean addCity(String cityName) {
cityContainer.getItem(cityContainer.addItem()).getItemProperty("name")
.setValue(cityName);
try {
cityContainer.commit();
return true;
} catch (UnsupportedOperationException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
}

I'm *guessing* - I've never used SQLContainer - but try using upper case for the column names when doing getItem, e.g.

cityContainer.getContainerProperty(id, "NAME"). ...

MS SQL Server tends to return columns and table names in upper case - SQL Server itself is normally case-insensitive, but SQLContainer is probably using a Map somewhere to store the container properties, and hence *is* case sensitive.

Cheers,

Charles

thanks Charles for a quick reply. I will try it. Any alternative how to connect and populate tables using Ms SQL server and vaadin aside from SQLContainer?

I tried already Charles but still not working. Please help.

Thanks Charles. I solved it already. It should be in lowercase all the fields.

Other options to SQLContainer is JPA(Container) and querying yourself. With standard java, you can query a database and convert the result into objects. That can be plugged inside any in-memory container like IndexedContainer or BeanItemContainer.

Hi Donald....

i'm a student and i have task to create an application..and that application using SQL server 2008...
would you shared how to connect that?
I need an example especially codes because i'm a beginner in vaadin...
Thank's for your help..
sorry if my english soo bad...