java 中使用 sqlite

来源：互联网发布：sqlserver大数据处理编辑：程序博客网时间：2024/06/05 16:23

使用 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);       }   }}

0 0