java 中使用 sqlite

使用 maven 引入 jar 文件：

 <dependency>
          <groupId>org.xerial</groupId>
          <artifactId>sqlite-jdbc</artifactId>
          <version>3.7.2</version>
      </dependency>

1>sqliteUtils

package com.hjy.sqlite.utils;


import java.sql.*;

public class SqliteDbUtils
{
  private static Connection connection = null;
  private static String URL = "jdbc:sqlite:/Users/UserName/test/sqlite/catalog.db";
  static
  {

      try
      {
        Class.forName("org.sqlite.JDBC");
      }
      catch(Exception ex)
      {
          ex.printStackTrace();
      }
  }
  public static Connection getConnection()
  {
      try
      {
          connection = DriverManager.getConnection(URL);
      }
      catch(Exception ex)
      {
          ex.printStackTrace();
      }
      return connection;
  }
  public static void close(Connection conn,PreparedStatement ps,ResultSet rs)
  {
      if(null != conn)
      {
          try
          {
              conn.close();
          } catch (SQLException e)
          {
              // TODO Auto-generated catch block
              e.printStackTrace();
          }
      }
      if(null != ps)
      {
          try
          {
              ps.close();
          } catch (SQLException e)
          {
              // TODO Auto-generated catch block
              e.printStackTrace();
          }
      }
      if(null != rs)
      {
          try
          {
              rs.close();
          } catch (SQLException e)
          {
              // TODO Auto-generated catch block
              e.printStackTrace();
          }
      }
  }
    public static void close(Connection conn,Statement stmt,ResultSet rs)
    {
        if(null != conn)
        {
            try
            {
                conn.close();
            } catch (SQLException e)
            {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if(null != stmt)
        {
            try
            {
                stmt.close();
            } catch (SQLException e)
            {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if(null != rs)
        {
            try
            {
                rs.close();
            } catch (SQLException e)
            {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
}

2》 实现增删查改：

package com.hjy.sqlite;


import com.hjy.sqlite.utils.SqliteDbUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;


public class SqliteCreateTable
{
   public static void main(String[] args)
   {
     //  createTable();
     //  insertSql();
     //  selectTable();
    //   updateTable();
    //    deleteTable();
   }
   public static void createTable()
   {
       Connection conn = null;
       Statement stmt = null;
       String sql = "CREATE TABLE company(ID INT PRIMARY KEY NOT NULL,NAME TEXT NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(50),SALARY REAL)";
       try
       {
           conn = SqliteDbUtils.getConnection();
           stmt = conn.createStatement();
           stmt.executeUpdate(sql);
       }
       catch(Exception ex)
       {
         ex.printStackTrace();
       }
       finally {
           SqliteDbUtils.close(conn,stmt,null);
       }

   }
   public static void insertSql()
   {
       Connection connection = null;
       Statement stmt = null;
       try
       {
           connection = SqliteDbUtils.getConnection();
           connection.setAutoCommit(false);
           stmt = connection.createStatement();
           String sql = "INSERT INTO company(ID,NAME,AGE,ADDRESS,SALARY) VALUES(1,'Paul',32,'California',200000.0)";
           stmt.executeUpdate(sql);
           sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
                   "VALUES (2, 'Allen', 25, 'Texas', 15000.00 );";
           stmt.executeUpdate(sql);

           sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
                   "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );";
           stmt.executeUpdate(sql);

           sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
                   "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";
           stmt.executeUpdate(sql);
           connection.commit();
       }
       catch(Exception ex)
       {
           ex.printStackTrace();
       }
       finally {
           SqliteDbUtils.close(connection,stmt,null);
       }
   }

   public static void selectTable()
   {
       Connection conn = null;
       Statement stmt = null;
       ResultSet rs = null;
       try
       {
           conn = SqliteDbUtils.getConnection();
           stmt = conn.createStatement();
           conn.setAutoCommit(false);
           rs = stmt.executeQuery("SELECT * FROM COMPANY;");
           while(rs.next())
           {
               int id = rs.getInt("id");
               String name = rs.getString("name");
               int age = rs.getInt("age");
               String address = rs.getString("address");
               float salary = rs.getFloat("salary");
               System.out.println("id--->"+id);
               System.out.println("name--->"+name);
               System.out.println("age--->"+age);
               System.out.println("address--->"+address);
               System.out.println("salary--->"+salary);
           }
       }
       catch(Exception ex)
       {
           ex.printStackTrace();
       }
       finally {
           SqliteDbUtils.close(conn,stmt,rs);
       }
   }
   public static void updateTable()
   {
       Connection connection = null;
       Statement stmt = null;
       ResultSet rs = null;
       try
       {
           connection = SqliteDbUtils.getConnection();
           stmt = connection.createStatement();
           connection.setAutoCommit(false);
           String sql = "UPDATE COMPANY SET SALARY = 100.00 WHERE id = 1;";
           stmt.executeUpdate(sql);
           connection.commit();

           rs = stmt.executeQuery("SELECT * FROM COMPANY;");
           while(rs.next())
           {
               int id = rs.getInt("id");
               String name = rs.getString("name");
               int age = rs.getInt("age");
               String address = rs.getString("address");
               float salary = rs.getFloat("salary");
               System.out.println("id--->"+id);
               System.out.println("name--->"+name);
               System.out.println("age--->"+age);
               System.out.println("address--->"+address);
               System.out.println("salary--->"+salary);
           }
       }
       catch(Exception ex)
       {
           ex.printStackTrace();
       }
       finally {
           SqliteDbUtils.close(connection,stmt,rs);
       }
   }
   public static void deleteTable()
   {
       Connection connection = null;
       Statement stmt = null;
       ResultSet rs = null;
       try
       {
           connection = SqliteDbUtils.getConnection();
           stmt = connection.createStatement();
           connection.setAutoCommit(false);
           String sql = "DELETE FROM COMPANY WHERE id = 2;";
           stmt.executeUpdate(sql);
           connection.commit();

           rs = stmt.executeQuery("SELECT * FROM COMPANY;");
           while(rs.next())
           {
               int id = rs.getInt("id");
               String name = rs.getString("name");
               int age = rs.getInt("age");
               String address = rs.getString("address");
               float salary = rs.getFloat("salary");
               System.out.println("id--->"+id);
               System.out.println("name--->"+name);
               System.out.println("age--->"+age);
               System.out.println("address--->"+address);
               System.out.println("salary--->"+salary);
           }
       }
       catch(Exception ex)
       {
           ex.printStackTrace();
       }
       finally {
           SqliteDbUtils.close(connection,stmt,rs);
       }
   }

}