Sqlite 在java中的使用

前言

SQLite 是一个软件库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。SQLite 是在世界上最广泛部署的 SQL 数据库引擎。SQLite 源代码不受版权限制。
支持 java 、python 等多种语言。

详细介绍可以看这里: https://www.runoob.com/sqlite/sqlite-java.html.

本文主要举例Sqlite 在java中的使用。概念性的东西就不说太多了。

官网英文好的同学可以看这个哟：https://www.sqlite.org/index.html

本文参考：https://www.yiibai.com/sqlite/java-with-sqlite.html

https://www.cnblogs.com/ngxianyu/archive/2013/06/08/3126543.html

下载jar

http://mvnrepository.com/artifact/org.xerial/sqlite-jdbc/3.18.0

或者

https://www.sqlite.org/download.html

Sqlite 在java中的使用

• 以一个 SqlliteUtils.java 带你简单使用 sqlite

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;




public class SqliteUtils  {
	
	
	/**
	 * 如果有就返回连接,没有就创建数据库
	 * 
	 * @param fileName
	 */
    public static Connection createNewDatabase(String databaseName) {

        String url = "jdbc:sqlite:" + databaseName;
        Connection conn = null;
        try {
             conn = DriverManager.getConnection(url);
             conn.setAutoCommit(false);
            if (conn != null) {
                DatabaseMetaData meta = conn.getMetaData();
               
                System.out.println("A new database has been created.");
            }

        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return conn;
    }
    
    
    public static void createNewTable(Connection conn,String sql) {

        try {
            Statement stmt = conn.createStatement();
            stmt.execute(sql);
            System.out.println("Create table finished.");
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }
    
    
    public static void  insert( Connection conn ,String keywords, Integer channel_id,String text) {
       
    	 String insertSql = "INSERT INTO RANKDATA(KEYWORDS, channel_id, text) VALUES(?,?,?)";
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(insertSql);
            pstmt.setString(1, keywords);
            pstmt.setDouble(2, channel_id);
            pstmt.setString(3, text);
            pstmt.executeUpdate();
            
            
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }finally {
        	try {
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
    }
    
    public static void  insert( Connection conn ,String insertSql) {
        
       PreparedStatement pstmt = null;
       try {
           pstmt = conn.prepareStatement(insertSql);
           pstmt.executeUpdate();
           
       } catch (SQLException e) {
           System.out.println(e.getMessage());
       }finally {
       	try {
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
   }
    
    public static void  delete( Connection conn ,String deleteSql) {

        try {
            PreparedStatement pstmt = conn.prepareStatement(deleteSql);
 
            pstmt.executeUpdate();
            conn.commit();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }
    
    public static void  update( Connection conn , String updateSql) {

        try {
            PreparedStatement pstmt = conn.prepareStatement(updateSql);
         
            pstmt.executeUpdate();
            conn.commit();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }
    
    public static void select( Connection conn ,String sql) {
   
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);

            // loop through the result set
            while (rs.next()) {
                System.out.println(rs.getInt("id") + "\t" + rs.getString("KEYWORDS") + "\t" + rs.getString("text"));
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }



   
	public static void main(String[] args) {
		String property = System.getProperty("user.dir");
		System.out.println(property+"/create-testt-db.db");
		
		Connection conn = createNewDatabase( property+"/create-db.db" );
		
		//创建表，如果不存在的话、
		String createTableSql = "CREATE TABLE IF NOT EXISTS RANKDATA " +
                "(ID INTEGER   PRIMARY KEY    AUTOINCREMENT," +
                " KEYWORDS       CHAR(50) 	  NOT NULL, " + 
                " CHANNEL_ID     INTEGER      NOT NULL, " + 
                " TEXT        	 TEXT)"; 
		
		createNewTable(conn, createTableSql);
		
		
		insert(conn, "大家好", 1, "大家好");
		
		String selectSql = "select * from RANKDATA where channel_id =1 ";
		String selectSql1 = "select * from RANKDATA ";
		
//		select(conn,selectSql);
		select(conn,selectSql1);

	}

}

• 使用连接池