sqlite数据库放在java工程的“根目录”下(直接复制进项目里)。
需要sqlitejdbc.jar,sqljdbc4.jar支持。
1.连接sqlite数据库
package sqliteDataBase;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class sqlDbConnection {
public sqlDbConnection() throws ClassNotFoundException {
Class.forName("org.sqlite.JDBC");
}
public Connection getConnection() throws SQLException {
Connection conn = DriverManager.getConnection("jdbc:sqlite:a.db");
return conn;
}
}
2.操作sqlite数据库
package sqliteDataBase;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import entity.Entity;
public class sqlDbDao {
sqlDbConnection sqlConnection = null;
Connection conn = null;
ResultSet rs = null;
Statement stat = null;
public sqlDbDao() throws ClassNotFoundException {
sqlConnection = new sqlDbConnection();
}
// 从sqlite中读取数据
public ArrayList<Entity> getsqlDbMessage() {
ArrayList<Entity> list = new ArrayList<Entity>();
try {
conn = sqlConnection.getConnection();
stat = conn.createStatement();
rs = stat.executeQuery("select txtp from te_user where sheetid= '6D544447-42A9-4AAB-B1D7-97D663ECE76D'");
while (rs.next()) {
rs.getInt(0);
// Entity entity = new Entity();
// entity.id = rs.getString("id");
// entity.image = rs.getString("image");
// list.add(entity);
}
if (rs != null)
rs.close();
if (stat != null)
stat.close();
System.err.println("从sqlite中读取成功");
} catch (SQLException e) {
System.err.println("从sqlite中读取失败");
e.printStackTrace();
} finally {
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
System.err.println("sqlite中的连接抛异常");
e.printStackTrace();
}
}
return list;
}
// 数据插入到sqlite
public void addSqliteDb(Entity entity) {
try {
conn = sqlConnection.getConnection();
stat = conn.createStatement();
PreparedStatement prep = conn.prepareStatement("insert into image values (?, ?);");
prep.setString(1, "3");
prep.setString(2, entity.image);
prep.addBatch();
conn.setAutoCommit(false);
prep.executeBatch();
conn.setAutoCommit(true);
System.err.println("数据插入到sqlite成功");
if (stat != null)
stat.close();
} catch (SQLException e) {
System.err.println("数据插入到sqlite失败");
e.printStackTrace();
} finally {
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}