· 10 years ago · Apr 12, 2016, 04:36 PM
1package com.swtstuff;
2
3import java.sql.Connection;
4import java.sql.DriverManager;
5import java.sql.ResultSet;
6import java.sql.Statement;
7import java.util.ArrayList;
8
9public class PersonalInfoDatabaseHelper {
10
11 private static final String DATABASE_NAME = "pi.db";
12 private static final int DATABASE_VERSION = 1;
13
14 private String name;
15 private int version;
16
17 ArrayList<PersonalInfoDatabase> ptypes = null;
18
19
20 PersonalInfoDatabaseHelper() {
21 this.name = DATABASE_NAME;
22 this.version = DATABASE_VERSION;
23 }
24
25 public Connection getDB() {
26 Connection db = null;
27
28 // to test if a table exists:
29 // SELECT count(*) FROM sqlite_master WHERE type='table' AND name='table_name';
30 Statement stmt = null;
31 try {
32 db = DriverManager.getConnection("jdbc:sqlite:" + DATABASE_NAME);
33 stmt = db.createStatement();
34 stmt.setQueryTimeout(30); // set timeout to 30 sec.
35
36 ResultSet rs = stmt.executeQuery( "SELECT count(*) FROM sqlite_master WHERE type='table' AND name='" +
37 PersonalInfoDatabase.PI_TABLE_NAME + "';" );
38 while(rs.next())
39 {
40 //System.out.println(rs.getString(1));
41 if (rs.getString(1).compareTo("0") == 0) { // this table does NOT exist
42 //System.out.println("calling onCreate()");
43 onCreate(db);
44 }
45 }
46 rs.close();
47 stmt.close();
48 } catch (Exception e) {
49 System.err.println( e.getClass().getName() + ": " + e.getMessage() );
50 throw new AssertionError();
51 //System.exit(0);
52 }
53 System.out.println("Operation (getDB) done successfully");
54 return db;
55 }
56
57 public void onCreate(Connection db) {
58 Statement stmt = null;
59 try {
60 if (db.isClosed()) {
61 db = DriverManager.getConnection("jdbc:sqlite:" + DATABASE_NAME);
62 }
63
64 db.setAutoCommit(false);
65 stmt = db.createStatement();
66 stmt.setQueryTimeout(30); // set timeout to 30 sec.
67
68 // Create the Personal Info Type table
69 stmt.executeUpdate("CREATE TABLE " + PersonalInfoDatabase.PI_TABLE_NAME + " ("
70 + PersonalInfoDatabase._ID + " INTEGER PRIMARY KEY AUTOINCREMENT ,"
71 + PersonalInfoDatabase.PI_NAME + " TEXT"
72 + ");");
73
74 stmt.close();
75 db.commit();
76 } catch (Exception e) {
77 System.err.println( e.getClass().getName() + ": " + e.getMessage() );
78 throw new AssertionError();
79 //System.exit(0);
80 }
81 System.out.println("Operation (onCreate) done successfully");
82 }
83
84 public void addPetName(Connection db, String piName) {
85 Statement stmt = null;
86 try {
87 if (db.isClosed()) {
88 db = DriverManager.getConnection("jdbc:sqlite:" + DATABASE_NAME);
89 }
90
91 db.setAutoCommit(false);
92 stmt = db.createStatement();
93 String sql = "INSERT INTO " + PersonalInfoDatabase.PI_TABLE_NAME +
94 " (" + PersonalInfoDatabase.PI_NAME + ") " +
95 "VALUES ('" + piName + "');";
96 stmt.executeUpdate(sql);
97
98 stmt.close();
99 db.commit(); // commit this insert/change
100 } catch (Exception e) {
101 System.err.println( e.getClass().getName() + ": " + e.getMessage() );
102 throw new AssertionError();
103 //System.exit(0);
104 }
105 System.out.println("Record created successfully");
106 }
107}