package dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import Model.Person;
import Model.Phone;
public class DAO_H2 implements DAO
{
private Statement statement = null;
private Connection connection = null;
private String query="";
private ResultSet result = null;
public void Connect()
{
try
{
Class.forName("org.h2.Driver");
connection = DriverManager.getConnection("jdbc:h2:tcp://localhost/~/test", "sa", "");
}
catch (Exception e)
{
e.printStackTrace();
}
}
public void Disconnect()
{
try
{
connection.close();
}
catch (Exception e)
{
e.printStackTrace();
}
}
@Override
public void Create(ArrayList<Person> list) throws SQLException
{
statement = connection.createStatement();
if (list!=null)
{
for (Person data : list)
{
query="INSERT INTO PERSONS VALUES ("+data.getId()+", '"+
data.getFistname()+"', '"+data.getSurname()+
"', "+data.getAge()+");";
statement.execute(query);
if (data.getPhone()!=null)
{
for (Phone phone : data.getPhone())
{
query="INSERT INTO PHONES VALUES ( NULL, '"+
phone.getPhone()+"', "+data.getId()+");";
statement.execute(query);
}
}
}
}
statement.close();
}
@Override
public void Update(Person person) throws SQLException
{
statement = connection.createStatement();
if (person!=null)
{
query="UPDATE PERSONS SET "+
"FISTNAME='"+person.getFistname()+"', SURNAME='"+
person.getSurname()+"', AGE="+person.getAge()+
" WHERE ID="+person.getId()+";";
statement.execute(query);
query="DELETE FROM PHONES WHERE PERSONID="+person.getId()+";";
statement.execute(query);
for (Phone phone : person.getPhone())
{
query="INSERT INTO PHONES VALUES ( NULL, '"+
phone.getPhone()+"', "+person.getId()+");";
statement.execute(query);
}
}
statement.close();
}
@Override
public void Delete(int id) throws SQLException
{
statement = connection.createStatement();
query="DELETE FROM PHONES WHERE PERSONID="+id+";";
statement.execute(query);
query="DELETE FROM PERSONS WHERE ID="+id+";";
statement.execute(query);
statement.close();
}
@Override
public int MaxId() throws SQLException
{
int id=1;
statement = connection.createStatement();
query="SELECT MAX(ID) FROM PERSONS;";
statement.execute(query);
result = statement.executeQuery(query);
result.next();
id = result.getInt("MAX(ID)");
statement.close();
return id;
}
@Override
public void DeleteAll() throws SQLException
{
statement = connection.createStatement();
query="DELETE FROM PERSONS;";
statement.execute(query);
query="DELETE FROM PHONES;";
statement.execute(query);
statement.close();
}
@Override
public Person Read(int id) throws SQLException
{
statement = connection.createStatement();
Person person=null;
ArrayList<Phone> phones = null;
query="SELECT * FROM PERSONS WHERE ID="+id+";";
result = statement.executeQuery(query);
result.next();
if (result.getRow()==1)
{
person = new Person();
person.setId(result.getInt("ID"));
person.setFistname(result.getNString("FISTNAME"));
person.setSurname(result.getNString("SURNAME"));
person.setAge(result.getInt("AGE"));
Statement statementPhone = connection.createStatement();
query="SELECT * FROM PHONES WHERE PERSONID="+id+";";
ResultSet resultPhone = statementPhone.executeQuery(query);
phones = new ArrayList<Phone>();
while (resultPhone.next())
{
Phone phone = new Phone();
phone.setPhone(resultPhone.getNString("PHONE"));
phones.add(phone);
}
statementPhone.close();
person.setPhone(phones);
}
statement.close();
return person;
}
@Override
public ArrayList<Person> ReadAll() throws SQLException
{
statement = connection.createStatement();
ArrayList<Person> list = new ArrayList<Person>();
ArrayList<Phone> phones;
Person person;
query="SELECT * FROM PERSONS;";
result = statement.executeQuery(query);
while ( result.next() )
{
person = new Person();
person.setId(result.getInt("ID"));
person.setFistname(result.getNString("FISTNAME"));
person.setSurname(result.getNString("SURNAME"));
person.setAge(result.getInt("AGE"));
phones = new ArrayList<Phone>();
query="SELECT * FROM PHONES WHERE PERSONID="+person.getId()+";";
Statement statementPhone = connection.createStatement();
ResultSet resultPhone = statementPhone.executeQuery(query);
while (resultPhone.next())
{
Phone phone = new Phone();
phone.setPhone(resultPhone.getNString("PHONE"));
phones.add(phone);
}
resultPhone.close();
person.setPhone(phones);
list.add(person);
}
statement.close();
return list;
}
}