java sqlite使用小记

Sqlite教程： http://www.runoob.com/sqlite/sqlite-tutorial.html

加载sqlite jdbc驱动：

 static{

  try {

   Class.forName("org.sqlite.JDBC");

  } catch (ClassNotFoundException e) {

   e.printStackTrace();

  }

 }

获取数据库连接：

private Connection conn = getConn();

 public Connection getConn(){

  if(conn==null){

   try {

    conn = DriverManager.getConnection("jdbc:sqlite:message.db");

   } catch (SQLException e) {

    // TODO Auto-generated catch block

    e.printStackTrace();

   }

  }

  return conn;

 

 }

创建表：

/**

  * 创建message表

  *

  * @author cjianquan

  * 2015-8-4

  */

 public void createTableMsg(){

  try {

   Statement stat = conn.createStatement();

   StringBuilder createSql = new StringBuilder();

   createSql.append("create table message( msg_id text primary key not null,")

   .append("msg_type text ,")

   .append("msg_direction text ,")

   .append("msg_from text,")

   .append("msg_to text,")

   .append("msg_msg text,")

   .append("msg_url text,")

   .append("msg_sound text,")

   .append("msg_time text );");

   stat.executeUpdate(createSql.toString());

   stat.close();

  } catch (SQLException e) {

   // TODO Auto-generated catch block

   e.printStackTrace();

  }

 }

插入表数据：

/**

  *  判断是否存在表message

  * @return

  * @author cjianquan

  * 2015-8-4

  */

 public boolean isTableExists(){

  boolean flag = false;

  try {

   Statement stat = conn.createStatement();

   String sql = "select count(1) from message ";

   stat.executeQuery(sql);

   flag = true;

   stat.close();

  } catch (SQLException e) {

   flag = false;

  }

  return flag;

 }

 public void insertMessage(Message msg){

  boolean flag = isTableExists();

  if(flag){

   try {

    StringBuilder insertSql = new StringBuilder();

    insertSql.append("insert into message(msg_id,")

    .append("msg_type,msg_direction,msg_from,")

    .append("msg_to,msg_msg,msg_url,msg_sound,msg_time) ")

    .append("values (?,?,?,?,?,?,?,?,?);");

    PreparedStatement stat = conn.prepareStatement(insertSql.toString());

   

    stat.setString(1, msg.getId());

    stat.setString(2, msg.getType());

    stat.setString(3, msg.getDirection());

    stat.setString(4, msg.getFrom());

    stat.setString(5, msg.getTo());

    stat.setString(6, msg.getMsg());

    stat.setString(7, msg.getMsgUrl());

    stat.setString(8, msg.getSound());

    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    if(msg.getTime()==null || "".equals(msg.getTime())){

     msg.setTime(sdf.format(new Date()));

    }

    stat.setString(9, msg.getTime());

   

    stat.execute();

    stat.close();

    //conn.commit();

    conn.close();

   } catch (SQLException e) {

    // TODO Auto-generated catch block

    e.printStackTrace();

   }

   

  }else{

   createTableMsg();

   insertMessage(msg);

  }

 }

查询表数据：

 public List<Message> queryMessage(Page page){

  boolean flag = isTableExists();

  List<Message> rtnList = new ArrayList<Message>();

  if(flag){

   int offset = (page.getCurPage()-1)*page.getPageSize();

   

   String querySql = "select * from ( select * from message order by msg_time desc) limit "+page.getPageSize()+" offset "+offset+" ;";

   try {

    Statement statement = conn.createStatement();

    ResultSet rs = statement.executeQuery(querySql);

    while(rs.next()){

     Message msg = new Message();

     msg.setId(rs.getString("msg_id"));

     msg.setType(rs.getString("msg_type"));

     msg.setDirection(rs.getString("msg_direction"));

     msg.setFrom(rs.getString("msg_from"));

     msg.setTo(rs.getString("msg_to"));

     msg.setMsg(rs.getString("msg_msg"));

     msg.setMsgUrl(rs.getString("msg_url"));

     msg.setSound(rs.getString("msg_sound"));

     msg.setTime(rs.getString("msg_time"));

     rtnList.add(msg);

    }

   

    rs = null;

    rs = statement.executeQuery("select count(*) cnt from message");

    if(rs.next()){

     page.setTotal(Integer.parseInt(rs.getString("cnt")));

    }

   

    rs.close();

    statement.close();

    conn.close();

   } catch (SQLException e) {

    e.printStackTrace();

   }

  }else{

   createTableMsg();

  }

  return rtnList;

 }