Vaadin connecting to MS SQL

General
1

Hey,

I’ve just started with Vaadin and done the vaadin tutorial and working on my first solo project as a test. Trying to create a simple form where you can input data and save it to a database. Problem is I have no clue on how to connect to a database with Vaadin. Any helpful link where I could read about it ? Specifically I want to connect to a MS SQL 2005 database server.

2

Connecting to database is just like you do it with any basic servlet. You can use plain JDBC or plain JPA inside your web application (WAR).

Example with jdbc you do it like this:

Propertis  p = new Properties();
p.put("user", username);
p.put("password", password);
p.put("port", 1433);
p.put("password", password);
DriverManager.registerDriver(new SQLServerDriver());
Connection connection= DriverManager.getConnection(url, p);

Query example:

PreparedStatement ps = connection.prepareStatement("INSERT INTO FormInput (field1, field2, user_id) values (?,?,?)");
ps.setString(1, value);
ps.setString(2, value2);
ps.setInt(3, user.getId());
ps.executeUpdate();
connection.commit();

JDBC might be the quickest way to test few queries, but if your doing anything more than that I recommend JPA. Tutorial setting up web project with eclipslink as JPA provider:
http://wiki.eclipse.org/EclipseLink/Examples/JPA/JSF_Tutorial

3

I’ve got a connection up and running and was able to execute queries.

Thanks for pointing me at the right direction. I’m a bit rusty with Java in general haven’t used it for a few years.

Specifically I’m using MS SQL 2005 server and maybe in the future 2008 or whatever comes after that. I ended up setting it up using:
Microsoft SQL Server JDBC Driver http://msdn.microsoft.com/en-us/data/aa937724.aspx with the documentation that came with the driver I got things up and running.

Any reason why you suggest JPA ? Should I try in the future use that instead of JDBC ? I’m afraid I don’t understand enough to compare them or even enough if the two can be compared.

I guess in short what do I gain by using JPA ?

4

Using Object Relational Mapping you get things done a lot faster. It has performance issues is used wrong (example fetching too much stuff too early or using inefficient queries) but so does plain sql through jdbc.

As a basic example inserting new “row” to person-table using JPA:

Person p = new Person();
p.setName("name");
entityManager.persist(p);

Example: you need to get persons jobs:

person.getJobs();

And jpa-provider does all the sql’s in the background.

Mapping Person using annotations:

@Entity
public class Person {
	@Id
	@GeneratedValue(strategy = GenerationType.TABLE)
	private String id;

	private String name;
 
	@OneToMany
	private List<Job> jobList = new ArrayList<Job>();

	public String getId() {
		return id;
	}

	public void setId(String Id) {
		this.id = Id;
	}

	public String geName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public List<Job> getJobs() {
		return this.jobs;
	}

	public void setJobs(List<Job> jobs) {
		this.jobs = jobs;
	}

}