sqlite的使用

1、导入sqlite包

<!-- 我使用的是springboot 自动集成 -->
<dependency>
   <groupId>org.xerial</groupId>
   <artifactId>sqlite-jdbc</artifactId>
</dependency>

2、创建db文件

 public synchronized static Connection getConnection() throws SQLException {
        //connection 我设置成了全局变量
        if (connection == null) {
            try {
                String driverClass = "org.sqlite.JDBC";
                Class.forName(driverClass);
            } catch (Exception e) {
                e.printStackTrace();
            }
            //lizhi.db 文件名称以及后缀  如果想放到D盘 直接放路径 如：jdbc:sqlite:E:\\lizhi.db
            connection=DriverManager.getConnection("jdbc:sqlite:lizhi.db");
            return  connection;
        } else {
            return connection;
        }
    }

3、创建表

public static final String DB_USER = "create table login (" +
            "  id text(50) PRIMARY KEY," +
            "  username text(11) not null," +
            "  password text(20)," +
            "  token text(100)," +
            "  topict text(40)," +
            "  organname text(40)," +
            "  type integer(1)," +
            "  is_pwd integer(1)," +
            "  is_login integer(1)" +
            ");";
public static final String DB_IPPORT = "create table ipport (" +
            "  id INTEGER PRIMARY KEY," +
            "  ip text(11) not null," +
            "  port text(20) not null" +
            ");";
public static void createDatabases() throws SQLException, IOException {
        Connection connection = getConnection();
        Statement statement = connection.createStatement();
        //创建表  也就是执行sql
        statement.execute(DB_USER);
        statement.execute(DB_IPPORT );
        //初始化一条数据
        connection.createStatement().executeUpdate("insert into ipport(ip,port) values('127.0.0.1','8087')")
    }

4、新增数据

/**
     * 查询 添加
     * Login 是自定义的一个包装类
     * @return
     */
public static void insert(Login login) {
		// 此处定义的是一个查询方法
        List<Login> collectionBoxData = getCollectionBoxData(login.getUsername(), login.getPassword());
        if (collectionBoxData.size()!=0){
            login(login);
        }else{
            try {
            // 获得连接后执行添加的sql语句
                getConnection().createStatement().executeUpdate("insert into login(id,username, password, token,topict,organname,type,is_pwd,is_login) " +
                        "values ('"+login.getId()+"','"+login.getUsername()+"','"+login.getPassword()+"','"+login.getToken()+"','"+login.getOrgCode()
                        +"','"+login.getOrganname()+"',"+login.getType()+","+login.getIsPwd()+","+login.getIsLogin()+");");
            } catch (SQLException ex) {
                Logger.getLogger(Login.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

5、查询

 public static List<Login> getCollectionBoxData(String username,String password) {
        List<Login> result = new ArrayList<>();
        try {
            ResultSet rs = getConnection().createStatement().executeQuery("select * from main.login where username='"+username+"' and password='"+password+"'");
            while (rs.next()) {
                Login l = new Login();
                l.setUsername(rs.getString("username"));
                l.setPassword(rs.getString("password"));
                l.setToken(rs.getString("token"));
                l.setOrgCode(rs.getString("topict"));
                l.setType(rs.getInt("type"));
                l.setOrganname(rs.getString("organname"));
                result.add(l);
            }
        } catch (SQLException ex) {
            Logger.getLogger(Login.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

6、修改

public static void updatePheConfig(String ip,String port){
        try {
            getConnection().createStatement().executeUpdate("update ipport set ip= '"+ip+"',port='"+port+"' where id = 2");
        } catch (SQLException ex) {
            Logger.getLogger(Login.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

7、删除

 public static void delete(String id){
        try {
            getConnection().createStatement().executeUpdate("delete from login where id = '"+id+"'");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }