SQLite初识

基础文档参考菜鸟教程：https://www.runoob.com/sqlite/sqlite-tutorial.html

数据库操作：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


public class SqliteHelper {

    final static Logger logger = LoggerFactory.getLogger(SqliteHelper.class);

    final static String dbFilePath="jdbc:sqlite:Mq.db";//数据库链接名称
    private static SqliteHelper instance;


    private  SqliteHelper(){}

    public static SqliteHelper getInstance() {
        if (instance == null) {
            instance = new SqliteHelper();
        }
        return instance;
    }


    /**
     * 插入/更新/删除(执行sql)
     * @param sql
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public void executeUpdate(String sql) throws Exception{
        Class.forName("org.sqlite.JDBC");
        Connection connection=DriverManager.getConnection(dbFilePath);
        Statement statement=connection.createStatement();
        try {
            statement.executeUpdate(sql);
        }catch (Exception e){
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            destroyed(null,statement,connection);
        }
    }

    /**
     * 查询
     * @param sql
     * @param rm
     * @param <T>
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public <T> List<T> executeQuery(String sql, RowMapper<T> rm) throws Exception{
        Class.forName("org.sqlite.JDBC");
        Connection connection=DriverManager.getConnection(dbFilePath);
        Statement statement=connection.createStatement();
        ResultSet resultSet=null;
        List<T> rsList = new ArrayList<T>();
        try {
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                rsList.add(rm.mapRow(resultSet, resultSet.getRow()));
            }
        }catch (Exception e){
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            destroyed(resultSet,statement,connection);
        }
        return rsList;
    }

    /**
     * 数据库资源关闭和释放
     */
    public void destroyed(ResultSet resultSet,Statement statement,Connection connection) {
        try {
            if (null != resultSet) {
                resultSet.close();
            }
            if (null != statement) {
                statement.close();
            }
            if (null != connection) {
                connection.close();
            }
        } catch (SQLException e) {
            logger.error("Sqlite数据库关闭时异常", e);
        }
    }

}

import lombok.extern.slf4j.Slf4j;import java.sql.*;
import java.util.List;

@Slf4j
public class SqliteTest {

    final static String table= "CREATE TABLE Test " +
            "(ID INTEGER PRIMARY KEY AUTOINCREMENT    NOT NULL," +
            " NAME           TEXT    NOT NULL, " +
            " AGE            INT     NOT NULL, " +
            " ADDRESS        CHAR(50), " +
            " SALARY         REAL)";
    final static String Test1= "INSERT INTO Test (ID,NAME,AGE,ADDRESS,SALARY) " +
            "VALUES (1, 'Allen', 25, 'Texas', 15000.00 );";
    final static String Test2= "INSERT INTO Test (ID,NAME,AGE,ADDRESS,SALARY) " +
            "VALUES (2, 'Teddy', 23, 'Norway', 20000.00 );";
    final static String Test_select= "SELECT * FROM InvokeCmd8004;";

    /**
     * 测试
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static void TestSq(){
        try {
            String sql = Test_select;
            //sqliteHelper.executeUpdate(sql);
            List<String> sList =SqliteHelper.getInstance().executeQuery(sql,new RowMapper<String>() {
                @Override
                public String mapRow(ResultSet rs, int index)
                        throws SQLException {
                    String aa=rs.getString("ID");
                    //System.out.println(aa);
                    return rs.getString("ID");
                }
            });
            log.warn(sList.get(0));
        }catch (Exception e){
            e.printStackTrace();
        }
    }

　　public static void main(String[] args){
        TestSq();
    }
}

可视化工具：sqlitebrowser.exe

其他功能性SQL：

[root@localhost /]# ALTER TABLE UserInfo ADD `disable_status` INTEGER  //表中添加字段