|
| 1 | +import java.sql.*; |
| 2 | +import java.util.*; |
| 3 | + |
| 4 | +public class JdbcHelper{ |
| 5 | + |
| 6 | + /** |
| 7 | + * File 2/4 of jdbc series |
| 8 | + * This is a services file of jdbc CL application |
| 9 | + * Variables having sql table url and login credentials ( Different for each user) |
| 10 | + */ |
| 11 | + |
| 12 | + String url = "jdbc:mysql://localhost:3306/stock"; |
| 13 | + String user = "root"; |
| 14 | + String password="admin"; |
| 15 | + |
| 16 | + Connection con = null; |
| 17 | + Statement st = null; |
| 18 | + ResultSet rs; |
| 19 | + |
| 20 | + |
| 21 | + /** |
| 22 | + * Method to establish a connection b/w java and sql returns a Statement object |
| 23 | + */ |
| 24 | + |
| 25 | + public Statement getStatement(){ |
| 26 | + try{ |
| 27 | + con = DriverManager.getConnection(url,user,password); |
| 28 | + st = con.createStatement(); |
| 29 | + if(st == null) |
| 30 | + System.out.println("st is null"); |
| 31 | + |
| 32 | + |
| 33 | + } |
| 34 | + catch(Exception e){ |
| 35 | + System.out.println("in gs"+e.toString()); |
| 36 | + } |
| 37 | + return st; |
| 38 | + } |
| 39 | + |
| 40 | + /** |
| 41 | + * Method to fetch all data from sql table |
| 42 | + * Returns ArrayList a collection framework datatype (returns a Product class object) |
| 43 | + * Executes the query |
| 44 | + */ |
| 45 | + |
| 46 | + public ArrayList<Product> getProducts(){ |
| 47 | + |
| 48 | + ArrayList<Product> plist = new ArrayList<Product>(); |
| 49 | + try{ |
| 50 | + System.out.println("calling get products"); |
| 51 | + String sql = "select * from product"; |
| 52 | + rs = st.executeQuery(sql); |
| 53 | + |
| 54 | + while(rs.next()){ |
| 55 | + Product p = new Product(); |
| 56 | + p.setName( rs.getString("name") ); |
| 57 | + p.setId(rs.getInt("id")); |
| 58 | + p.setPrice(rs.getInt("price") ); |
| 59 | + |
| 60 | + plist.add(p); |
| 61 | + } |
| 62 | + } |
| 63 | + |
| 64 | + catch(Exception e){ |
| 65 | + System.out.println("error in get pro"+e.toString()); |
| 66 | + } |
| 67 | + |
| 68 | + return plist; |
| 69 | + } |
| 70 | + |
| 71 | + /** |
| 72 | + * Method to insert a tuple in the sql table |
| 73 | + */ |
| 74 | + |
| 75 | + public void insert(String name, int price){ |
| 76 | + |
| 77 | + try{ |
| 78 | + System.out.println("calling insert"); |
| 79 | + String sql = "insert into product (name,price) values('"+name+"',"+price+")"; |
| 80 | + int x = st.executeUpdate(sql); |
| 81 | + |
| 82 | + System.out.println(x); |
| 83 | + } |
| 84 | + catch(Exception e){ |
| 85 | + System.out.println("in insert "+e.toString()); |
| 86 | + } |
| 87 | + |
| 88 | + } |
| 89 | + JdbcHelper(){ |
| 90 | + System.out.println("jdbc helper const"); |
| 91 | + this.st=getStatement(); |
| 92 | + if(this.st == null) |
| 93 | + System.out.println("Statement not created"); |
| 94 | + } |
| 95 | +} |
0 commit comments