Question

Java bean class to insert and retrieve data from database

Answer 1

IntroductionIn this document we will describe how to communicate with a database in a jsp page using Java Beans and the org.w3c.tools.jdbc package.SetupWe need three things before starting the experimentation:

Install JSP in Jigsaw (read the tutorial)Install a database, (eg postgresql) if you don't have one already installed on your system.Get the JDBC drivers relative to your database (you can find them at javasoft)How it works

The system is built with some Java Beans linked to the database via a serializer (JdbcBeanSerializer). Each bean is a "mirror" of a table in the database, its properties are strictly equivalents (same name, same type) to the table colums. The serializer is in charge to build the SQL request depending on the values of the bean properties, it use a JdbcServer to send the request to the database (The JdbcServer manage a pool of persistent connections to the database). The serializer read the result and fill the bean(s) with the collected values. Then the jsp page use the serializer as an iterator and read the bean properties.

Sample applicationTo describe how to use the org.w3c.tools.jdbc package we're going to create a sample application. We want to implement a search engine about movies. So we have a database with three tables: actors, movies and starring, we want to be able to insert and search data in these tables. For that we'll write some jsp pages that use some java beans.The SQL TablesNote: The SQL statements are compatible with postgresql, read the postgresql documentation for more details.

MoviesmovieidTitleDirectorYear112 MonkeysTerry Gilliam1995

CREATE TABLE movies ( movieid SERIAL NOT NULL PRIMARY KEY, title varchar(255) NOT NULL, director varchar(30), year int4 )

ActorsactoridFirstnameLastname1BruceWillis

CREATE TABLE actors ( actorid SERIAL NOT NULL PRIMARY KEY, firstname varchar(255) NOT NULL, lastname varchar(255) NOT NULL ) Starringmovieidactorid11

CREATE TABLE starring ( movieid int4, actorid int4, PRIMARY KEY (movieid, actorid) ) The Java beansFor each table in our database we need to write one Java Bean that extends org.w3c.tools.jdbc.JdbcBeanAdapter or that implements the org.w3c.tools.jdbc.JdbcBeanInterface interface.

Movie Beanpublic class MovieBean extends org.w3c.tools.jdbc.JdbcBeanAdapter { protected int movieid = -1; protected String title = null; protected String director = null; protected int year = -1; public void setMovieid(int movieid) { int oldvalue = this.movieid; this.movieid = movieid; pcs.firePropertyChange("movieid", oldvalue, movieid); } public int getMovieid() { return movieid; } public void setTitle(String title) { String oldvalue = this.title; this.title = title; pcs.firePropertyChange("title", oldvalue, title); } public String getTitle() { return title; } public void setDirector(String director) { String oldvalue = this.director; this.director = director; pcs.firePropertyChange("director", oldvalue, director); } public String getDirector() { return director; } public void setYear(int year) { int oldvalue = this.year; this.year = year; pcs.firePropertyChange("year", oldvalue, year); } public int getYear() { return year; } } Actor Beanpublic class ActorBean extends org.w3c.tools.jdbc.JdbcBeanAdapter { protected int actorid = -1; protected String firstname = null; protected String lastname = null; public void setActorid(int actorid) { int oldvalue = this.actorid; this.actorid = actorid; pcs.firePropertyChange("actorid", oldvalue, actorid); } public int getActorid() { return actorid; } public void setFirstname(String firstname) { String oldvalue = this.firstname; this.firstname = firstname; pcs.firePropertyChange("firstname", oldvalue, firstname); } public String getFirstname() { return firstname; } public void setLastname(String lastname) { String oldvalue = this.lastname; this.lastname = lastname; pcs.firePropertyChange("lastname", oldvalue, lastname); } public String getLastname() { return lastname; } } Starring Beanpublic class StarringBean extends org.w3c.tools.jdbc.JdbcBeanAdapter { protected int movieid = -1; protected int actorid = -1; // related beans protected MovieBean movieBean = null; protected ActorBean actorBean = null; public void setMovieid(int movieid) { int oldvalue = this.movieid; this.movieid = movieid; pcs.firePropertyChange("movieid", oldvalue, movieid); } public int getMovieid() { return movieid; } public void setActorid(int actorid) { int oldvalue = this.actorid; this.actorid = actorid; pcs.firePropertyChange("actorid", oldvalue, actorid); } public int getActorid() { return actorid; } public void setMovieBean(MovieBean movieBean) { Object old = this.movieBean; this.movieBean = movieBean; pcs.firePropertyChange("movieBean", old, movieBean); } public MovieBean getMovieBean() { return movieBean; } public void setActorBean(ActorBean actorBean) { Object old = this.actorBean; this.actorBean = actorBean; pcs.firePropertyChange("actorBean", old, actorBean); } public ActorBean getActorBean() { return actorBean; } }