Java操作Sqlite数据库-jdbc连接

比眉伴天荒 2022-04-01 06:53 531阅读 0赞

[https://www.cnblogs.com/popfisher/p/5497206.html][https_www.cnblogs.com_popfisher_p_5497206.html]

**Java操作Sqlite数据库步骤：**

1. **导入Sqlite jdbc**

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

2. **编写jdbc连接代码**

![复制代码][2018122916544414]

public class OpSqliteDB {
        
        private static final String Class_Name = "org.sqlite.JDBC";
        private static final String DB_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(DB_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 + "'");
            }
        }

![复制代码][2018122916544414]

以下是其他的一些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

[https_www.cnblogs.com_popfisher_p_5497206.html]: https://www.cnblogs.com/popfisher/p/5497206.html
[2018122916544414]: /images/20220401/7b4e57cba1e2428b81f7faff2f8eeb87.png