Android Learning Note: Java Development SQLite Program

As mentioned in the previous article, SQLite is used in embedded development, but sometimes in order to make it easier to edit database files, we often need to develop SQLite programs running on the computer . This happens often, for example, when we need to insert data from a large number of txt files into a database.

Fortunately this is very simple, so this article we learn how to use Java to develop the SQLite program.

(1) preparatory work

Download the sqlite-jdbc- version number . jar file, and put it in the jre\lib\ext folder. As my path is C:\Program files\java\jre1.8.0_77\lib\ext.

(2) A test code, you can run the feeling under the specific content of the details below.

Import java.sql.*;          public class Sqlitetest {public static void main (string[] args) {try {//Connect SQLite JDBC                  Class.forName ("Org.sqlite.JDBC");                   Establish a connection to the database name test.db, if it does not exist in the current directory created Connection mconnection = drivermanager.getconnection ("jdbc:sqlite:test.db3");                  Statement state = Mconnection.createstatement ();          CREATE TABLE State.executeupdate ("CREATE table TB1 (name varchar (), grade int);");         Insert Data state.executeupdate ("INSERT into TB1 values (' Name 1 ', 90);");         State.executeupdate ("INSERT into TB1 values (' Name 2 ', 58);");         State.executeupdate ("INSERT into TB1 values (' Name 3 ', 69);");          State.executeupdate ("INSERT into TB1 values (' Name 4 ', 85);");          Query data ResultSet result = State.executequery ("select * from TB1;");             Print query results while (Result.next ()) {System.out.print (result.getstring ("name") + ""); SYstem.out.println (result.getstring ("grade"));         } result.close (); Mconnection.close ();        End connection to database} catch (Exception e) {e.printstacktrace ();  }    }}

Output Result:

Name 1

Name 2

Name 3

Name 4

(3) connect to the database

We need to connect to the available JDBC drivers. Then we need to establish a connection to a database, and if the database does not exist, the database file will be created in the specified location. and create a Statement instance from the connection .

Connect to SQLite's JDBC

Class. forname ("Org.sqlite.JDBC");

Create a connection to the database name Test.db if it does not exist in the current directory

Connection mconnection = DriverManager. getconnection ("Jdbc:sqlite:test.db3");

Statement state =mconnection. createstatement ();

(4) operation of the database:

For SQL statements that do not need to get the return data , we call the Statement executeupdate (str) method to execute the corresponding SQL statement. Include build table, insert, delete and so on. For SQL query statements that need to get the return data , we need to call the executeQuery (str) method and return the result as a ResultSet object. Just like its name, this is a collection of query results, we can manipulate the collection's row pointers with its next () method, and use the getXxx ("column Name") method to get the value of a column of a row.

Note that each SQLite statement needs to be "; "End."

A more special case is that the INSERT statement needs to be written in the form of an incoming parameter, which saves you a lot of trouble stitching the string. At this point we need to use the preparedstatement class, and the steps are as follows:

<1> define specifications for inserting data:

PreparedStatement prs =mconnection. Preparestatement ("INSERT into TB1 values (?,?);");

<2> Incoming parameters:

The first argument is the number of columns, and the second parameter is the value of the column.

PRs. setString (1,"name 5");

PRs. setString (2,"the");

<3> EXECUTE statement:

Call the following method to actually execute the INSERT statement.

PRs. Addbatch ();

mconnection. Setautocommit (false);

PRs. ExecuteBatch ();

mconnection. Setautocommit (true);

We modify the test file above to insert the data in the form of a parameter:

Import java.sql.*;          public class Sqlitetest {public static void main (string[] args) {try {//Connect SQLite JDBC                  Class.forName ("Org.sqlite.JDBC");                   Establish a connection to the database name test.db, if it does not exist in the current directory created Connection mconnection = drivermanager.getconnection ("jdbc:sqlite:test.db3");                  Statement state = Mconnection.createstatement ();                  CREATE TABLE State.executeupdate ("CREATE table TB1 (name varchar (), grade int);");         Insert Data state.executeupdate ("INSERT into TB1 values (' Name 1 ', 90);");         State.executeupdate ("INSERT into TB1 values (' Name 2 ', 58);");         State.executeupdate ("INSERT into TB1 values (' Name 3 ', 69);");          State.executeupdate ("INSERT into TB1 values (' Name 4 ', 85);");         Inserting data in the form of a parameter PreparedStatement PRS = mconnection.preparestatement ("INSERT into TB1 values (?,?);");         Prs.setstring (1, "name 5");         Prs.setstring (2, "83"); Prs.addbatch ();         Mconnection.setautocommit (FALSE);         Prs.executebatch ();                  Mconnection.setautocommit (TRUE);          Query data ResultSet result = State.executequery ("select * from TB1;");             Print query results while (Result.next ()) {System.out.print (result.getstring ("name") + "");         System.out.println (result.getstring ("grade"));         } result.close (); Mconnection.close ();        End connection to database} catch (Exception e) {e.printstacktrace ();    }    }  }

(5) Closing the result set, disconnecting from the database

result. Close ();

mconnection. Close ();

Android Learning Note: Java Development SQLite Program