SQLite Basic (Instance FileOrganizer2013.5.12)

Tool with SQLite Dev data type: 1.NULL: null value. 2.INTEGER: A signed integer, depending on the size of the range in which the number is deposited. 3.REAL: Floating point number, stored as 8-byte IEEE floating-point numbers. 4.TEXT: string literal. 5.BLOB: Binary object. =======================Add Sqlite-jdbc-3.7.2.jar Connection

import org.sqlite.JDBC;

    Connection conn = null;
    Statement stat;
    ResultSet rs;


// Connect to the database
    void connect () {
        try {
            // JDBC connected to SQLite
            Class.forName ("org.sqlite.JDBC");
            // Establish a connection with the database name zieckey.db.
            conn = DriverManager.getConnection ("jdbc: sqlite: test.db");
            stat = conn.createStatement ();
        } catch (Exception e) {
            e.printStackTrace ();
        }
        //JOptionPane.showMessageDialog(null,"Connected database ");
        if (null == conn) {
            JOptionPane.showMessageDialog (null, "Failed to connect to the database");
            System.exit (1);
        }
            
    }

    // Disconnect
    void disconnect () {
        try {
            // rs.close ();
            conn.close (); // End the database connection
        } catch (SQLException e) {
            e.printStackTrace ();
        }
        //JOptionPane.showMessageDialog(null, "Disconnected database");
    } 

Reading meta data and data

private void showMetaData () {// Show the data table header to test the database connection
        ResultSetMetaData rsmd; // Get metadata
        try {
            rs = stat.executeQuery ("Select * From test");
            rsmd = rs.getMetaData ();
            int ColumnCount = rsmd.getColumnCount ();
            for (int j = 1; j <= ColumnCount; j ++) {
                System.out.println (rsmd.getColumnName (j));
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace ();
        }
        
    }

    private void showDataInTable () {// show data
        // define the data model of the table
        DefaultTableModel dtm = (DefaultTableModel) jTable1.getModel ();
        try {
            while (rs.next ()) {
                Vector newRow = new Vector ();
                int c = 1, numberOfColumns;
                numberOfColumns = rs.getMetaData (). getColumnCount ();
                while (c <= numberOfColumns) {
                    newRow.addElement (rs.getString (c));
                    c ++;
                }
                dtm.addRow (newRow);
            }
        } catch (Exception e) {
            e.printStackTrace ();
        }
    }

Querying a specified row

SELECT * from MAIN. [Test] where rowid = 10;

Delete

String updateSql="delete from test where RecID =‘"+(String)jTable1.getValueAt(jTable1.getSelectedRow(),0)+"‘"; try {
            stat.executeUpdate(updateSql);
            showDataInTable();
            System.out.println("已delete");
        } catch (SQLException ex) {
            ex.printStackTrace();
        } 

Update

 
int row = jTable1.getSelectedRow();
         String updateSql="update test "// + "set Dept=‘sorry2" + "set Dept=‘"+jTextField2.getText() + "‘, SendID=‘"+jTextField3.getText() + "‘, Title=‘"+jTextField4.getText() + "‘, RecDate=‘"+jTextField5.getText() + "‘, Link=‘"+jTextField6.getText() + "‘ where RecID =‘"+(String)jTable1.getValueAt(row,0)+"‘"; try {
            stat.executeUpdate(updateSql);
            showDataInTable();
            System.out.println("已save");
        } catch (SQLException ex) {
            ex.printStackTrace();
        } 

Insert

String updateSql="insert into test VALUES("
                 + "‘"+jTextField1.getText() + "‘,‘"+jTextField2.getText() + "‘,‘"+jTextField3.getText() + "‘,‘"+jTextField4.getText() + "‘,‘"+jTextField5.getText() + "‘,‘"+jTextField6.getText() +"‘)"; try {
            stat.executeUpdate(updateSql);
            showDataInTable();
}