Hello All,
I am using oracle 10g express edition, and the Address Book + SQLContainer Demo application
I have the following problem, for example when i want to modify the first name for a record in the person list, and click save, i get the following error message
“ORA-00001: unique constraint (HR.PERSONADDRESS_PK) violated”
The problem happen when the changes are being committed to the database
app.getDbHelp().getPersonContainer().commit();i created the same used schema in SQLContainer Demo application, if someone can help me to figure out how can i solve this problem,
here it is the DatebaseHelper class for oracle,
package com.vaadin.addon.sqlcontainer.demo.addressbook.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;
import com.vaadin.addon.sqlcontainer.query.generator.OracleGenerator;
@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() {
String driverName = "oracle.jdbc.driver.OracleDriver";
String connectionUri = "jdbc:oracle:thin:@127.0.0.1:1521:XE";
String userName = "hr";
String password = "HRPASSWORD";
try {
connectionPool = new SimpleJDBCConnectionPool(driverName,
connectionUri, userName, password);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private void initDatabase() {
Connection conn = null;
Statement statement = null;
try {
try {
conn = connectionPool.reserveConnection();
statement = conn.createStatement();
statement.executeQuery("SELECT * FROM PERSONADDRESS");
statement.executeQuery("SELECT * FROM CITY");
} catch (SQLException e) {
statement.execute("create table city(id INTEGER , name varchar2(60) , version INTEGER default '0' NOT NULL ,CONSTRAINT id_PK PRIMARY KEY (id))");
statement.execute("CREATE SEQUENCE city_sequence START WITH 1 INCREMENT BY 1");
statement.execute("CREATE OR REPLACE TRIGGER city_trigger BEFORE INSERT ON city REFERENCING NEW AS NEW FOR EACH ROW BEGIN SELECT city_sequence.nextval INTO :NEW.id FROM dual; END;");
statement.execute("CREATE TABLE personaddress (id INTEGER , firstname VARCHAR2(64), lastname VARCHAR2(64), email VARCHAR2(64), phonenumber VARCHAR2(64), streetaddress VARCHAR2(128), postalcode INTEGER, cityId INTEGER NOT NULL, version INTEGER DEFAULT '0' NOT NULL , CONSTRAINT fk_city FOREIGN KEY (cityId) REFERENCES city(id),CONSTRAINT personaddress_pk PRIMARY KEY (id))");
statement.execute("CREATE SEQUENCE personaddress_sequence START WITH 1 INCREMENT BY 1");
statement.execute("CREATE OR REPLACE TRIGGER personaddress_trigger BEFORE INSERT ON personaddress REFERENCING NEW AS NEW FOR EACH ROW BEGIN SELECT personaddress_sequence.nextval INTO :NEW.id FROM dual; END;");
}
statement.close();
conn.commit();
connectionPool.releaseConnection(conn);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private void initContainers() {
try {
TableQuery q1 = new TableQuery("personaddress",connectionPool, new OracleGenerator());
q1.setVersionColumn("VERSION");
q1.setDebug(debugMode);
personContainer = new SQLContainer(q1);
personContainer.setDebugMode(debugMode);
TableQuery q2 = new TableQuery("city", connectionPool,new OracleGenerator());
q2.setVersionColumn("VERSION");
q2.setDebug(debugMode);
cityContainer = new SQLContainer(q2);
cityContainer.setDebugMode(debugMode);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private void fillContainers(){
final String cities[] = { "[no city]
", "Amsterdam", "Berlin",
"Helsinki", "Hong Kong", "London", "Luxemburg", "New York",
"Oslo", "Paris", "Rome", "Stockholm", "Tokyo", "Turku" };
Random r = new Random(0);
if(personContainer.size()==0 && cityContainer.size()==0){
for(int i=0; i<cities.length;i++){
Object id = cityContainer.addItem();
cityContainer.getContainerProperty(id, "NAME").setValue(cities[i]
);
}
try {
cityContainer.commit();
} catch (UnsupportedOperationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
final String[] fnames = { "YAYA", "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" };
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);
int randomNum = 1 + (int)(Math.random() * ((cities.length-1) + 1));
personContainer.getContainerProperty(id, "CITYID")
.setValue(randomNum);
System.out.println(randomNum);
}
} 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;
}
}
Regards,
Mohamed Eldib