DaoManager

Download Drivers JDBC sqlite

package fr.ema.lgi2p.emacs.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;

import fr.ema.lgi2p.emacs.metier.Application;
import fr.ema.lgi2p.emacs.metier.Todo;

public class DaoManager {

private static DaoManager instance = null;
private Connection connection = null;

private static final Logger log = Logger.getLogger("BoostrapSample");

private DaoManager() {

try {
  Class.forName("org.sqlite.JDBC");
  connection = DriverManager.getConnection("jdbc:sqlite:c:/Developpement/todo.db");
} catch (Exception e) {
   System.err.println(e.getClass().getName() + ": " + e.getMessage());
   System.exit(0);
}
   System.out.println("Opened database successfully");
  log.info("Opened database successfully");

   createDatabase();
}

public static DaoManager getInstance() {
  if (instance == null) {
    instance = new DaoManager();
  }
  return instance;
}

private void createDatabase() {
  try {
    Statement stmt = connection.createStatement();
    String sql = "CREATE TABLE IF NOT EXISTS TODO ( ID INTEGER PRIMARY KEY AUTOINCREMENT, TEXTE TEXT NOT NULL, ACTIF BOOLEAN NOT NULL)";

    stmt.executeUpdate(sql);
    stmt.close();

   } catch (Exception e) {
       log.severe( e.getClass().getName() + ": " + e.getMessage() );
   }

    addTodo( new Todo("lait" , false ) );
    addTodo( new Todo("pain" , false ) );
}

public void addTodo(Todo todo){

  try {
    PreparedStatement preparedStatment = connection.prepareStatement("insert into TODO(TEXTE,ACTIF) values( ? , ? )" );

    preparedStatment.setString(1, todo.getTexte() );
    preparedStatment.setBoolean(2, todo.isActif() );

    preparedStatment.execute();
    preparedStatment.close();

  } catch (Exception e) {
     log.severe( e.getClass().getName() + ": " + e.getMessage() );
   }
}



public List<Todo> getAllTodo(){
   List<Todo> returnListTodo = new ArrayList<Todo>();
   try {
      Statement statement = connection.createStatement();

      if ( statement.execute( "Select TEXTE,ACTIF FROM TODO " ) ){
        ResultSet resultSet = statement.getResultSet();
        while ( resultSet.next() ) {
          String texte = resultSet.getString("TEXTE");
          boolean actif = resultSet.getBoolean("ACTIF");

          returnListTodo.add( new Todo( texte , actif ));
        }
       }
       statement.close();

    } catch (Exception e) {
        log.severe( e.getClass().getName() + ": " + e.getMessage() );
    }
     return returnListTodo 
  }

}