{"id":140,"date":"2016-01-25T16:48:28","date_gmt":"2016-01-25T16:48:28","guid":{"rendered":"http:\/\/kaizen-koka.com\/?p=140"},"modified":"2018-11-04T01:26:07","modified_gmt":"2018-11-04T01:26:07","slug":"jdbc","status":"publish","type":"post","link":"https:\/\/kaizen-koka.com\/?p=140","title":{"rendered":"JDBC"},"content":{"rendered":"

\n\tPackage : java.sql.*<\/strong><\/span><\/span>\n<\/p>\n

\n\thttps:\/\/jdbc.postgresql.org\/download.html<\/a><\/strong><\/span>\n<\/p>\n

\n\thttps:\/\/www.enterprisedb.com\/downloads\/postgres-postgresql-downloads<\/a><\/strong><\/span><\/span>\n<\/p>\n

\n\tSteps to implement JDBC program <\/span><\/span><\/strong>\n<\/p>\n

\n\t1. Load the driver class
\n\t     Class.forName("org.postgresql.Driver");<\/span>
\n\t2. Establish the connection
\n\t     con = DriverManager.getConnection("jdbc:postgresql:\/\/127.0.0.1:5432\/postgres", "postgres", "abcd12345");<\/span>
\n\t3. Create a statement
\n\t     Statement stmt = con.createStatement();<\/span>
\n\t4. Prepare the Query and Execute it
\n\t     String sqlQuery = "select username, password from register where username='abcd'"
\n\t     ResultSet rs = stmt.executeQuery(sqlQuery);<\/span>
\n\t5. Process the results
\n\t     while(rs.next()){
\n\t                user = rs.getString("username");
\n\t                pass = rs.getString("password");
\n\t        }<\/span>
\n\t6. Close the statement and Connection
\n\t     stmt.close();
\n\t     con.close();<\/span>\n<\/p>\n

\n\t \n<\/p>\n

\n\tTypes of Statements:<\/span><\/strong>\n<\/p>\n

\n\t 1) Statement<\/strong><\/span>
\n\t    Statement stmt = con.createStatement();<\/span>
\n\t        While creating the statement, we're not passing anyQuery params<\/span>
\n\t    ResultSet rs = stmt.executeQuery(sqlQuery);<\/span>
\n\t        sqlQuery is passed while executing execute(), executeUpdate(), executeQuery(). The following things happen everytime,
\n\t        .query submission
\n\t        .query complilation
\n\t        .query execution <\/span>\n<\/p>\n

\n\t 2) PreparedStatement<\/span><\/strong>
\n\t    PreparedStatment ps = con.prepareStatement(sqlQuery);<\/span>
\n\t        <\/span>.query submission
\n\t          .query compilation
\n\t    ResultSet rs = ps.executeQuery();<\/span>
\n\t         <\/span>.query execution
\n\t       When ur creating the preparestatement by passing query as a query parameter, then query will be submitted to the sql engine 7 will be compiled. This compiled query will be stored in the database memory not in JVM memory.<\/span>\n<\/p>\n

\n\t 3) CallableStatement<\/strong>
\n\t     Used to execute the stored procedures avaliable in database.<\/span>
\n\t     String getDBProcedures = "{call getDBProcedure()}";
\n\t     callableStatement = dbConnection.prepareCall(getDBProcedures);<\/span>\n<\/p>\n

\n\t
\n\t Difference between execute(), executeUpdate() and executeQuery()<\/span><\/strong>\n<\/p>\n

\n\t  .boolean execute()<\/strong>
\n\t     return true when the query is executed successfully returns false otherwise.<\/span>
\n\t  .int executeUpdate()<\/strong>
\n\t     returns number of rows affected with the given query<\/span>
\n\t  .ResultSet executeQuery()<\/strong>
\n\t     use this method, for the select stmts when u execute this, number of rows will be returned and will be stored in the resultSet objects. We can iterate the ResultSet object with the following methods.<\/span>
\n\t     rs = ps.executeQuery();
\n\t            while(rs.next()){
\n\t                user = rs.getString("username");
\n\t                pass = rs.getString("password");
\n\t            }<\/span>
\n\t   
\n\t \n<\/p>\n

\r\npackage com.bellinfo.advanced.jdbc;\r\n\r\nimport java.sql.*;\r\nimport java.util.Properties;\r\n\r\npublic class DepartmentRepository {\r\n\r\n    private static final String CREATE_SQL = "CREATE TABLE Department ( deptid int, depname varchar, location varchar)";\r\n    private static final String INSERT_SQL = "INSERT INTO Department VALUES(?,?,?)";\r\n    private static final String SELECT_SQL = "select * from Department";\r\n    private static final String UPDATE_SQL = "Update Department set location=? where depname=?";\r\n    private static final String VALIDATE_SQL= "select exists (select 1 from pg_tables where schemaname='public' and tablename='department')";\r\n\r\n    private static final String URL = "jdbc:postgresql:\/\/:5432\/belljavasep";\r\n    Connection con = null;\r\n\r\n    void getConnection(){\r\n\r\n        try {\r\n            Class.forName("org.postgresql.Driver");\r\n            Properties prop = new Properties();\r\n            prop.setProperty("user","postgres");\r\n            prop.setProperty("password","");\r\n            con = DriverManager.getConnection(URL,prop);\r\n           \/\/ return con;\r\n        } catch (ClassNotFoundException e) {\r\n            e.printStackTrace();\r\n        } catch (SQLException e) {\r\n            e.printStackTrace();\r\n        }\r\n    }\r\n\r\n\r\n    void createDepartment() throws SQLException {\r\n        getConnection();\r\n        PreparedStatement psValidate = con.prepareStatement(VALIDATE_SQL);\r\n        ResultSet rs = psValidate.executeQuery();\r\n        boolean isTableExsist = false;\r\n        while(rs.next()){\r\n            isTableExsist = rs.getBoolean(1);\r\n        }\r\n        if(isTableExsist){\r\n            System.out.println("Hey ..Table already created in your schema, so...skiping it");\r\n        }else{\r\n            PreparedStatement ps = con.prepareStatement(CREATE_SQL);\r\n            boolean isCreated = ps.execute();\r\n            System.out.println("Hey Table has been created");\r\n        }\r\n    }\r\n\r\n    void insertDepartmentRecord(Department d){\r\n        getConnection();\r\n        try {\r\n            PreparedStatement ps = con.prepareStatement(INSERT_SQL);\r\n            ps.setInt(1,d.getId());\r\n            ps.setString(2,d.getName());\r\n            ps.setString(3,d.getLoc());\r\n\r\n            ps.executeUpdate();\r\n            System.out.println("Record created");\r\n        } catch (SQLException e) {\r\n            e.printStackTrace();\r\n        }\r\n    }\r\n\r\n    void updateDeparmentRecord(String loc, String name){\r\n        getConnection();\r\n        try {\r\n            PreparedStatement ps = con.prepareStatement(UPDATE_SQL);\r\n            ps.setString(1,loc);\r\n            ps.setString(2,name);\r\n            int count = ps.executeUpdate();\r\n            System.out.println("Records Updated count "+count);\r\n        } catch (SQLException e) {\r\n            e.printStackTrace();\r\n        }\r\n\r\n    }\r\n\r\n    void fetchRecords() throws SQLException {\r\n        getConnection();\r\n        PreparedStatement ps = con.prepareStatement(SELECT_SQL);\r\n        ResultSet rs = ps.executeQuery();\r\n        while(rs.next()){\r\n            System.out.println(rs.getInt("deptid") + "  "+rs.getString("depname") + "    "+rs.getString("location") );\r\n        }\r\n    }\r\n\r\n}\r\n<\/pre>\n
\n\tDemo Class\n<\/p>\n
\r\npackage com.bellinfo.advanced.jdbc;\r\n\r\nimport java.sql.SQLException;\r\nimport java.util.Scanner;\r\n\r\npublic class DepartmentDemo {\r\n\r\n    enum OPERATIONS{ INSERT, UPDATE};\r\n\r\n    public static void main(String[] args) throws SQLException {\r\n\r\n        DepartmentRepository dr = new DepartmentRepository();\r\n        dr.createDepartment();\r\n        Scanner scan = new Scanner(System.in);\r\n\r\n        System.out.println("Hey what do you want do");\r\n        String oper= scan.next();\r\n        if(oper.equalsIgnoreCase(OPERATIONS.INSERT.name())){\r\n            System.out.println("How many records");\r\n            int count = scan.nextInt();\r\n            Department d =null;\r\n            for(int i=0;i<count;i++){\r\n                d = new Department();\r\n                System.out.println("id");\r\n                d.setId(scan.nextInt());\r\n                System.out.println("name");\r\n                d.setName(scan.next());\r\n                System.out.println("location");\r\n                d.setLoc(scan.next());\r\n                dr.insertDepartmentRecord(d);\r\n            }\r\n\r\n        }else if(oper.equalsIgnoreCase(OPERATIONS.UPDATE.name())){\r\n            System.out.println("which deptarment you want to update");\r\n            String dept = scan.next();\r\n            System.out.println("what is the new location");\r\n            String loc = scan.next();\r\n            dr.updateDeparmentRecord(loc,dept);\r\n\r\n        }\r\n        System.out.println("After requested operation, records in DB");\r\n        dr.fetchRecords();\r\n\r\n\r\n\r\n    }\r\n}\r\n<\/pre>\n
\n\tDepartment.java\n<\/p>\n
\r\npackage com.bellinfo.advanced.jdbc;\r\n\r\nimport java.util.Objects;\r\n\r\npublic class Department {\r\n\r\n    private int id;\r\n    private String name;\r\n    private String loc;\r\n\r\n    public Department() {\r\n    }\r\n\r\n    public Department(int id, String name, String loc) {\r\n        this.id = id;\r\n        this.name = name;\r\n        this.loc = loc;\r\n    }\r\n\r\n    public int getId() {\r\n        return id;\r\n    }\r\n\r\n    public void setId(int id) {\r\n        this.id = id;\r\n    }\r\n\r\n    public String getName() {\r\n        return name;\r\n    }\r\n\r\n    public void setName(String name) {\r\n        this.name = name;\r\n    }\r\n\r\n    public String getLoc() {\r\n        return loc;\r\n    }\r\n\r\n    public void setLoc(String loc) {\r\n        this.loc = loc;\r\n    }\r\n\r\n    @Override\r\n    public boolean equals(Object o) {\r\n        if (this == o) return true;\r\n        if (o == null || getClass() != o.getClass()) return false;\r\n        Department that = (Department) o;\r\n        return id == that.id &&\r\n                Objects.equals(name, that.name) &&\r\n                Objects.equals(loc, that.loc);\r\n    }\r\n\r\n    @Override\r\n    public int hashCode() {\r\n\r\n        return Objects.hash(id, name, loc);\r\n    }\r\n\r\n    @Override\r\n    public String toString() {\r\n        return "Department{" +\r\n                "id=" + id +\r\n                ", name='" + name + '\\'' +\r\n                ", loc='" + loc + '\\'' +\r\n                '}';\r\n    }\r\n}\r\n<\/pre>\n
\n\t <\/p>\n","protected":false},"excerpt":{"rendered":"
Package : java.sql.* https:\/\/jdbc.postgresql.org\/download.html https:\/\/www.enterprisedb.com\/downloads\/postgres-postgresql-downloads Steps to implement JDBC program  1. Load the driver class      Class.forName("org.postgresql.Driver"); 2. Establish the connection      con = DriverManager.getConnection("jdbc:postgresql:\/\/127.0.0.1:5432\/postgres", "postgres", "abcd12345"); 3. […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","enabled":false},"version":2}},"categories":[9,2],"tags":[],"class_list":["post-140","post","type-post","status-publish","format-standard","hentry","category-advance","category-technology"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/s70lnf-jdbc","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/kaizen-koka.com\/index.php?rest_route=\/wp\/v2\/posts\/140","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kaizen-koka.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kaizen-koka.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kaizen-koka.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kaizen-koka.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=140"}],"version-history":[{"count":5,"href":"https:\/\/kaizen-koka.com\/index.php?rest_route=\/wp\/v2\/posts\/140\/revisions"}],"predecessor-version":[{"id":560,"href":"https:\/\/kaizen-koka.com\/index.php?rest_route=\/wp\/v2\/posts\/140\/revisions\/560"}],"wp:attachment":[{"href":"https:\/\/kaizen-koka.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=140"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kaizen-koka.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=140"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kaizen-koka.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=140"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}