bNHG5tNV

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        try (Connection conn = DriverManager.getConnection(dbURL);
59                Statement stmt = conn.createStatement()) {
60            stmt.execute(sql);
61        } catch (SQLException e) {
62            System.out.println(e.getMessage());
63        }
64    }
65    
66    public static void insert(String name, double capacity) {
67        String sql = "INSERT INTO warehouses(name,capacity) VALUES(?,?)";
68 
69        try (Connection conn = connect();
70        PreparedStatement pstmt = conn.prepareStatement(sql)) {
71            pstmt.setString(1, name);
72            pstmt.setDouble(2, capacity);
73            pstmt.executeUpdate();
74        System.out.println("...| Insertado ." + name);            
75        } catch (SQLException e) {
76            System.out.println(e.getMessage());
77        }
78    }
79    
80    public static void selectAll(){
81        String sql = "SELECT id, name, capacity FROM warehouses";
82        
83        try (Connection conn = connect();
84             Statement stmt  = conn.createStatement();
85             ResultSet rs    = stmt.executeQuery(sql)){
86            System.out.println("...| Consultando ." + sql);            
87            while (rs.next()) {
88                System.out.println(rs.getInt("id") +  "\t" + 
89                                   rs.getString("name") + "\t" +
90                                   rs.getDouble("capacity"));
91            }
92        } catch (SQLException e) {
93            System.out.println(e.getMessage());
94        }
95    }
96    
97    public static void testdb() throws ClassNotFoundException, SQLException{
98        initializeDatabase();
99        createNewTable();
100        insert("Martillo", 100); 
101        insert("Pinza", 101); 
102        insert("Clavos", 102); 
103        selectAll();
104    }
105}