注册 X
我已阅读并同意  服务条款
首页 > IT技术笔记 > 查看笔记

java使用sqlite数据库

如果是maven项目,在pom.xml引入依赖

<!-- https://mvnrepository.com/artifact/org.xerial/sqlite-jdbc -->

<dependency>

    <groupId>org.xerial</groupId>

    <artifactId>sqlite-jdbc</artifactId>

    <version>3.7.2</version>

</dependency>


非maven项目jar下载:


蓝奏云


sqlite-jdbc-3.7.2.zip


package com.explorer.controller;


import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;


/**

 * 使用sqlite数据库

 * @author jspanjsp

 *

 */

public class OpSqliteDB {

    

    private static final String Class_Name = "org.sqlite.JDBC";

    //定义sqlite数据库存放位置

    private static final String DB_URL = "jdbc:sqlite:"+System.getProperty("user.dir")+"/config/"+"database.db";

    //private static final String DB_URL = "jdbc:sqlite:D:xxxdatabase.db";

    //String dir = System.getProperty("user.dir")+"/config/"+"database.db";

    

    public static void main(String args[]) {

        Connection connection = null;

        try {

            connection = createConnection();

            initdb(connection);

            System.out.println(IsTableExist(connection));

            //func1(connection);

        }  catch (SQLException e) {

            System.err.println(e.getMessage());

        } catch(Exception e) {

            e.printStackTrace();

        } finally{

            try {

                if (connection != null)

                    connection.close();

            } catch (SQLException e) {

                System.err.println(e);

            }

        }

    }

    

    //往sqlite数据库增加数据

    public static boolean add(String name,String password) {

    boolean b = false;

        Connection connection = null;

        try {

            connection = createConnection();

            

            

            

        }catch (Exception e) {

            System.err.println(e.getMessage());

        }finally{

            try {

                if (connection != null)

                    connection.close();

            } catch (SQLException e) {

                System.err.println(e);

            }

        }

   

    return b;

    } 

    

    // 创建Sqlite数据库连接

    public static Connection createConnection() throws SQLException, ClassNotFoundException {

        Class.forName(Class_Name);

        return DriverManager.getConnection(DB_URL);

    }

    

  //判断表是否存在

  private static boolean IsTableExist(Connection connection) throws SQLException {

  boolean isTableExist=false;

  int num = 0;

      Statement statement = connection.createStatement();

          

      String sql = "SELECT count(*) as c FROM sqlite_master WHERE type='table' AND name='user'";

      ResultSet rs = statement.executeQuery(sql);

      while (rs.next()) {

      num = Integer.parseInt(rs.getString("c"));

      }

      if (num>=1) {

      isTableExist = true;

      }

  return isTableExist;

  }    

    

    //数据库不存在,初始化数据库

    public static void initdb(Connection connection) throws SQLException {

    //建表语句

        String creatsql = "CREATE TABLE user("

              + "name varchar(20) not null,"

              + "password varchar(50) not null"

              + ");";  

    Statement statement = connection.createStatement();

    statement.executeUpdate(creatsql);

    if(statement != null) {

    statement.close();

    }

   

    }


    public static void func1(Connection connection) throws SQLException {

        Statement statement = connection.createStatement();

        Statement statement1 = connection.createStatement();

        statement.setQueryTimeout(30); // set timeout to 30 sec.

        

        String creatsql = "CREATE TABLE test("

                      + "name varchar(10) not null,"

                      + "age int(4) not null"

                      + ");";    

        //statement.executeUpdate(creatsql);


        //statement1.executeUpdate("insert into test(name,age) values('" + "张三'," +12 + ")");

        

        // 执行查询语句

        ResultSet rs = statement.executeQuery("select * from test");

        while (rs.next()) {

            String col1 = rs.getString("name");

            String col2 = rs.getString("age");

            System.out.println("col1 = " + col1 + "  col2 = " + col2);

            

            System.out.println();

            // 执行插入语句操作


            // 执行更新语句

            //statement1.executeUpdate("update test set name=" +  "张三" + " where id'" +  1 + "'");

        }

    }

}



 打赏        分享



评论