Java操作Sqlite資料庫

標籤：

Java操作Sqlite資料庫步驟：

1. 匯入Sqlite jdbc

本文使用sqlite-jdbc-3.7.2.jar, http://pan.baidu.com/s/1kVHAGdD

2. 編寫jdbc串連代碼

public class OpSqliteDB {        private static final String Class_Name = "org.sqlite.JDBC";    private static final String CallMarkDB_URL = "jdbc:sqlite:F:\\xxxdatabase.db";    public static void main(String args[]) {        // load the sqlite-JDBC driver using the current class loader        Connection connection = null;        try {            connection = createConnection();            func1(connection);            System.out.println("Success!");        }  catch (SQLException e) {            System.err.println(e.getMessage());        } catch(Exception e) {            e.printStackTrace();        } finally{            try {                if (connection != null)                    connection.close();            } catch (SQLException e) {                // connection close failed.                System.err.println(e);            }        }    }        // 建立Sqlite資料庫連接    public static Connection createConnection() throws SQLException, ClassNotFoundException {        Class.forName(Class_Name);        return DriverManager.getConnection(CallMarkDB_URL);    }    public static void func1(Connection connection) throws SQLException {        Statement statement = connection.createStatement();        Statement statement1 = connection.createStatement();        statement.setQueryTimeout(30); // set timeout to 30 sec.        // 執行查詢語句        ResultSet rs = statement.executeQuery("select * from table_name1");        while (rs.next()) {            String col1 = rs.getString("col1_name");            String col2 = rs.getString("col2_name");            System.out.println("col1 = " + col1 + "  col2 = " + col2);                        System.out.println(location);            // 執行插入語句操作            statement1.executeUpdate("insert into table_name2(col2) values(‘" + col2_value + "‘)");            // 執行更新語句            statement1.executeUpdate("update table_name2 set 欄位名1=" +  欄位值1 + " where 欄位名2=‘" +  欄位值2 + "‘");        }    }

 

以下是其他的一些Sql語句，來自網址：http://www.cnblogs.com/linjiqin/archive/2011/05/26/2059182.html （Android上面使用Sqlite）

查詢語句：

select * from 表名 where 條件子句 group by 分組字句 having ... order by 排序子句

如：

select * from person

select * from person order by id desc

select name from person group by name having count(*)>1

分頁SQL與mysql類似，下面SQL語句擷取5條記錄，跳過前面3條記錄

select * from Account limit 5 offset 3 或者 select * from Account limit 3,5

插入語句：

insert into 表名(欄位列表) values(值列表)。如： insert into person(name, age) values(‘傳智’,3)

更新語句：

update 表名 set 欄位名=值 where 條件子句。如：update person set name=‘傳智‘ where id=10

刪除語句：

delete from 表名 where 條件子句。如：delete from person where id=10

 

Java操作Sqlite資料庫