2019独角兽企业重金招聘Python工程师标准>>> 
Sqlite教程： http://www.runoob.com/sqlite/sqlite-tutorial.html
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;
}
