5qnGuvAq

1package com.libreria;
2
3import java.sql.Connection;
4import java.sql.DatabaseMetaData;
5import java.sql.DriverManager;
6import java.sql.PreparedStatement;
7import java.sql.ResultSet;
8import java.sql.SQLException;
9import java.sql.Statement;
10
11public class SQLLiteConnection {
12    
13    private static String dbURL = "jdbc:sqlite:C:/sqlite/db/libreria.db";
14    public static Connection initializeDatabase() 
15        throws SQLException, ClassNotFoundException 
16    { 
17        System.out.println("...| initializeDatabase().");
18        Connection conn = null;
19        try {
20            Class.forName("org.sqlite.JDBC");
21            conn = DriverManager.getConnection(dbURL);
22            System.out.println("...| Conectado a la base de datos.");
23             if (conn != null) {
24                DatabaseMetaData meta = conn.getMetaData();
25                System.out.println("...| Nombre del driver " + meta.getDriverName());
26                System.out.println("...| Se ha creado uina base de datos.");
27            }
28        } catch (SQLException e) {
29            System.out.println(e.getMessage());
30        } finally {
31            try {
32                if (conn != null) {
33                    conn.close();
34                }
35            } catch (SQLException ex) {
36                System.out.println(ex.getMessage());
37            }
38        }
39        return conn;
40    } 
41    
42    private static Connection connect() {
43        Connection conn = null;
44        try {
45            conn = DriverManager.getConnection(dbURL);
46        } catch (SQLException e) {
47            System.out.println(e.getMessage());
48        }
49        return conn;
50    }
51    
52    public static void createNewTable() {
53        String sql = "CREATE TABLE IF NOT EXISTS warehouses (\n"
54                + "    id integer PRIMARY KEY,\n"
55                + "    name text NOT NULL,\n"
56                + "    capacity real\n"
57                + ");";
58        
59        String sql2 = "CREATE TABLE IF NOT EXISTS libro (\n" +
60            "	id integer PRIMARY KEY,\n" +
61            "	nombre text not null,\n" +
62            "	existencia integer\n" +
63            ");";
64        try (Connection conn = DriverManager.getConnection(dbURL);
65                Statement stmt = conn.createStatement()) {
66            stmt.execute(sql);
67            stmt.execute(sql2);
68        } catch (SQLException e) {
69            System.out.println(e.getMessage());
70        }
71    }
72    
73    public static void insert(String name, double capacity) {
74        String sql = "INSERT INTO warehouses(name,capacity) VALUES(?,?)";
75 
76        try (Connection conn = connect();
77        PreparedStatement pstmt = conn.prepareStatement(sql)) {
78            pstmt.setString(1, name);
79            pstmt.setDouble(2, capacity);
80            pstmt.executeUpdate();
81        System.out.println("...| Insertado ." + name);            
82        } catch (SQLException e) {
83            System.out.println(e.getMessage());
84        }
85    }
86    
87    public static void selectAll(){
88        String sql = "SELECT id, name, capacity FROM warehouses";
89        
90        try (Connection conn = connect();
91             Statement stmt  = conn.createStatement();
92             ResultSet rs    = stmt.executeQuery(sql)){
93            System.out.println("...| Consultando ." + sql);            
94            while (rs.next()) {
95                System.out.println(rs.getInt("id") +  "\t" + 
96                                   rs.getString("name") + "\t" +
97                                   rs.getDouble("capacity"));
98            }
99        } catch (SQLException e) {
100            System.out.println(e.getMessage());
101        }
102    }
103    
104    public static void testdb() throws ClassNotFoundException, SQLException{
105        initializeDatabase();
106        createNewTable();
107        insert("Martillo", 100); 
108        insert("Pinza", 101); 
109        insert("Clavos", 102); 
110        selectAll();
111    }
112}