Question

Developing applications with SQLite and Java

Answer 1

SQLite is widely used by Adobe, Apple, McAfee, Skype, Firefox among others and also due to its size is included in Android, BlackBerry, Windows Phone 8, Google Chrome

SQLite can be downloaded from www.sqlite.org. To manage the database we can use Sqliteman or sqlitestudio or also phpLiteAdmin which is similar to phpmyadmin for Mysql.

We create an example with SQLiteman a database Employees.db and a table used,

We can add data through sql INSERT queries

Then to connect to the database we need a driver, they exist for several languages ​​python, .net, java, c ++, php brings it included. As in this example we will use Java we download the current driver sqlite-jdbc-3.7.2.jar from https: //bitbucket.or...jdbc/downloads.

Next the complete java code, we create a file that is called sqlitetest.java

 package sqlitetest; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; import javax.swing.JFrame; import javax.swing.JTable; import javax.swing.JScrollPane; import javax.swing.table.DefaultTableModel; public class Sqlitetest { public static void main (String [] args) // TODO code application logic here throws Exception { Class.forName ("org.sqlite.JDBC"); String dburl = "jdbc: sqlite: /home/test/sqlitetest/Employees.db"; Connection connection = DriverManager.getConnection (dburl); Statement Query = connection.createStatement (); ResultSet rs = Query.executeQuery ("SELECT * FROM Employee"); DefaultTableModel model = new DefaultTableModel (); JTable table = new JTable (model); // I create 3 columns with their labels // these are the columns of the JTable modelo.addColumn ("CODE"); modelo.addColumn ("NAME"); modelo.addColumn ("DOMICILIO"); while (rs.next ()) { Object [] data = new Object [4]; // Create a vector // to store the ResultSet values data [0] = (rs.getInt (1)); data [1] = (rs.getString (2)); data [2] = (rs.getString (3)); System.out.println (rs.getString (2)); // I add the model to the table model.addRow (data); // data = null; // clean the data from the memory vector } rs.close (); // Close the ResultSet JFrame f = new JFrame (); f.set Bounds (10, 10, 300, 200); f.getContentPane (). add (new JScrollPane (table)); f.setVisible (true); } } 

Also a look at how it looks in Netbeans and the resources we use

The result of executing this program generates a Jframe and a jTable to which we assign a Resultset result of a SQL query.

Important aspect SQLite can only be instantiated once, that is to say if we execute our program and we also try to use Sqliteman, because it will only be able to register and modify the software that has requested the connection first, the others will be able to consult.

This leaves the programmer to close the connection every time a query is made so that another user can connect.

Another operation that we can perform is to add data, for this we must add the components to enter data, in this case a textbox for each field and a button for the event to record data.

Inside the button we will program the Record event

 private void jButtonActionPerformed (java.awt.event.ActionEvent evt) { // TODO code application logic here try { Class.forName ("org.sqlite.JDBC"); String dburl = "jdbc: sqlite: /home/test/sqlitetest/Employees.db"; Connection connection = DriverManager.getConnection (dburl); String id = idtxt.getText (); String name = name.txt.getText (); String address = domicile.getText (); Statement Query = connection.createStatement (); Query.executeUpdate ("INSERT INTO Employee" + "VALUES (11, txtid 'Mr.', 'Springfield', 2001)"); connection.close (); } 

Always remember at the end of each transaction to close the connection to the database as it will be in locked mode.

---