{"id":238,"date":"2018-07-27T05:57:59","date_gmt":"2018-07-27T05:57:59","guid":{"rendered":"http:\/\/csp12.epgpbooks.inflibnet.ac.in\/?post_type=chapter&p=238"},"modified":"2018-08-02T08:28:14","modified_gmt":"2018-08-02T08:28:14","slug":"jdbc-part-ii","status":"publish","type":"chapter","link":"https:\/\/ebooks.inflibnet.ac.in\/csp12\/chapter\/jdbc-part-ii\/","title":{"rendered":"JDBC \u2013Part II"},"content":{"raw":"\u00a0 \u00a0SQL<\/strong>\r\n\r\n \r\n

It stands for Structured Query Language. SQL means Standardized syntax for \u201cquerying\u201d or accessing a Relational database.<\/p>\r\n \r\n\r\nSQL Syntax<\/strong>\r\n\r\n \r\n

Before we study about working JDBC with SQL, let us have a basic understanding of how to write SQL queries. Some of the SQL statements which would appear in the example programs are given below.<\/p>\r\n \r\n

This command can insert a new record into the named table.<\/p>\r\n

INSERT INTO table name( field1, field2 ) VALUES ( value1, value2 )<\/p>\r\n \r\n

This command can change an existing record or records.<\/p>\r\n

UPDAT E tablename SET( field1 = value1, field2 = value2 ) WHERE condition<\/p>\r\n \r\n

This command can remove all records that match condition.<\/p>\r\n

DELET E FROM tablename WHERE condition<\/p>\r\n \r\n

This command can retrieve all records that matches the condition.<\/p>\r\n

SELECT field1, field2 FROM tablename WHERE condition<\/p>\r\n

Install Mysql<\/p>\r\n \r\n

Let us now see the steps of installing MySQL to work with JDBC.<\/p>\r\n \r\n

Step 1: We need to Install Mysql Database from MYSQL official website dev.mysql.com. Figure 27.1 shows the Google search for Download of MySQL database.<\/p>\r\n \r\n

\"\"<\/p>\r\n \r\n

Step 2: <\/strong>Select the Platform type (i.e. Windows or Mac) and download the Installer. Figure 27.1 shows selecting the platform for Windows OS.<\/p>\r\n \r\n

\"\"<\/p>\r\n \r\n\r\nStep 3: Create Database<\/strong>\r\n\r\n \r\n

Once MySQL is installed in your machine, Open MySQL Comand Line and connect to MySQL server by specifying the user name and password in the command line arguments to mysql<\/em> command.Then create a Database by using the below SQLstatement.<\/p>\r\n \r\n\r\nSyntax<\/strong>\r\n\r\n \r\n\r\nCREAT E DATABASE databasename;\r\n\r\nFigure 27.3 shows creating the database in the MySQL command line using the above statement.\r\n\r\n \r\n

\"\"<\/p>\r\nStep 4: <\/strong>USE Database<\/strong>\r\n\r\n \r\n\r\n \r\n\r\nFrom the list of database created, to use a particular database we use the command 'USE database name'.\r\n\r\n \r\n\r\nSyntax<\/strong>\r\n\r\n \r\n\r\nUSE databasename;\r\n\r\nFigure 27.4 shows how to use the database using the above statement.\r\n\r\n \r\n

\"\"<\/p>\r\n \r\n\r\nStep 5: <\/strong>Create Table<\/strong>\r\n\r\n \r\n\r\nNow we can create Tables in Database using the create statement.\r\n\r\n \r\n\r\nSyntax<\/strong>\r\n\r\n \r\n\r\nCREAT E TABLE tablename(attribute name datatypes);\r\n\r\nFigure 27.5 shows how to create tables using the above statement.\r\n\r\n \r\n

\"\"<\/p>\r\n \r\n\r\nStep 6: <\/strong>Insert Query in Table<\/strong>\r\n\r\n \r\n

Once the table is created we can now Insert the values in the table. Note: <\/em>For varchar data type provide the values within quotes . Syntax<\/p>\r\n \r\n\r\nINSERT INTO tablename VALUES(data);\r\n\r\nFigure 27.6 shows how to insert records into the table using the above statement.\r\n\r\n \r\n

\"\"<\/p>\r\n \r\n\r\nStep 7: <\/strong>Select Query in Table<\/strong>\r\n\r\n \r\n\r\nThe Select statement can be used now to list all the values entered into the Table.\r\n\r\n \r\n\r\nSyntax<\/strong>\r\n\r\n \r\n\r\nSELECT * FROM tablename;\r\n\r\nFigure 27.7 shows how to select the records from the table using the above statement.\r\n\r\n \r\n

\"\"<\/p>\r\n \r\n\r\nStep 8: <\/strong>Delete Query in Table<\/strong>\r\n\r\n \r\n\r\nTo delete a particular Row from the table.\r\n\r\n \r\n\r\nSyntax<\/strong>\r\n\r\n \r\n\r\nDELET E FROM tablename WHERE attributename=\u2018value\u2019;\r\n\r\nFigure 27.8 shows how to delete a record from the table using the above statement.\r\n\r\n \r\n

\"\"<\/p>\r\n \r\n\r\nStep 9: <\/strong>Update Query in Table<\/strong>\r\n\r\n \r\n\r\nTo update values in a Row.\r\n\r\n \r\n\r\nSyntax<\/strong>\r\n\r\n \r\n\r\nUPDAT E tablename SET attributename=newvalue WHERE attributename=value;\r\n\r\nFigure 27.9 shows how to update records in the table using the above statement.\r\n\r\n \r\n

\"\"<\/p>\r\n \r\n\r\nTransactions<\/strong>\r\n\r\n \r\n

A Transaction means more than one statement which must all succeed (or all fail) together. If one fails, the system must reverse all previous actions. Also a transaction cannot leave database in an inconsistent state halfway through a transaction. The operation COMMIT is done for a complete transaction and ROLLBACK is done for an aborted transaction.<\/p>\r\n \r\n\r\nMapping Java Types to SQL Types<\/strong>\r\n\r\n \r\n

\"\"<\/p>\r\n \r\n

\r\n

Table27.1 shows the existence of Java types for each SQL types.<\/p>\r\n \r\n\r\nDatabase Time<\/strong>\r\n\r\n \r\n\r\nTimes in SQL are notoriously not standard.\r\n\r\n \r\n\r\nJava defines three classes to represent Date and Time.\r\n

    \r\n \t
  • java.sql.Date<\/li>\r\n<\/ul>\r\n \r\n\r\nThis class includes year, month, day.\r\n
      \r\n \t
    • java.sql.Time<\/li>\r\n<\/ul>\r\nThis class includes hours, minutes, seconds.\r\n
        \r\n \t
      • java.sql.Timestamp<\/li>\r\n<\/ul>\r\nThis class includes year, month, day, hours, minutes, seconds and nanoseconds.\r\n\r\n<\/div>\r\n\u00a0 \u00a0 \u00a0JDBC Interface classes<\/strong>\r\n
        \r\n\r\n\u00a0 \u00a0 JDBC Interface classes are contained in java.sql.* package. This consists of the following classes.\r\n
          \r\n \t
        • DriverManager<\/li>\r\n \t
        • Connection<\/li>\r\n \t
        • Statement<\/li>\r\n \t
        • CallableStatement<\/li>\r\n \t
        • PreparedStatement<\/li>\r\n \t
        • Resultset<\/li>\r\n \t
        • ResultSetMetaData<\/li>\r\n \t
        • DatabaseMetaData<\/li>\r\n<\/ul>\r\n\u00a0 \u00a0The JDBC Steps<\/strong>\r\n\r\n \r\n\r\n1. Importing Packages.\r\n\r\n2. Registering the JDBC Drivers.\r\n\r\n3. Opening a Connection to a Database.\r\n\r\n4. Creating a Statement Object.\r\n\r\n5. Executing a Query and Returning a Result Set Object.\r\n\r\n6. Processing the Result Set.\r\n\r\n7. Closing the Result Set and Statement Objects.\r\n\r\n8. Closing the Connection.\r\n\r\n \r\n\r\nSimple Example<\/strong>\r\n\r\n \r\n\r\nimport java.sql;\r\n\r\nclass SimpleExam ple {\r\n\r\npublic static void main(String args[]) {\r\n\r\nString url = \u201cjdbc:odbc:jnf\u201d;<\/span>\r\n\r\ntry {<\/span>\r\n\r\nClass.forName(\u201csun.jdbc.odbc.JdbcOdbcDriver\u201d); Connection myConnection = DriverManager.getConnection(url,\u201dIT\u201d,\u201dit@123\u201d); myConnection.close();<\/span>\r\n\r\n}<\/span>\r\n\r\ncatch(Exception e) {<\/span>\r\n\r\ne.printStackTrace();<\/span>\r\n\r\n}<\/span>\r\n\r\n}<\/span>\r\n\r\n}<\/span>\r\n\r\n<\/div>\r\n
          \r\n

          This example code explains about registering for the driver an loading the driver using the URL, user name and password.<\/p>\r\n \r\n\r\nSending SQL statements<\/strong>\r\n\r\n \r\n\r\n\u2026\u2026\r\n\r\n \r\n\r\nString query= \u201cSelect name,id,salary FROM employees ORDER By salary\u201d; Connection myConnection = DriverManager.getConnection(\u2026..);\r\n\r\n \r\n\r\nStatement myStatement = myConnection.createStatement();\r\n\r\nResultSet rs = myStatement.executeQuery(query);\r\n\r\nwhile(rs.next)\r\n\r\n{\r\n\r\nString empName = rs.getString(1);\r\n\r\nString empId = rs.getString(2);\r\n\r\nString empSalary = rs.getString(3);\r\n\r\nSystem.out.println(\u201cEm ployee \u201d + empName + \u201c with id \u201d + empId + \u201c earns \u201d + empSalary);\r\n\r\n}\r\n\r\nmyStatement.close();\r\n\r\nmyConne ction.close ();<\/span>\r\n\r\n\u2026\u2026.<\/span>\r\n\r\n<\/div>\r\n

          \r\n

          \u00a0 \u00a0Once Connection is established, Statement object is created. This Statement object is used to execute the query. The SQL query that has been tried in this example is an 'Select' query. Hence the retrieved records are read sequentially and displayed. Then the connection is closed.<\/p>\r\n \r\n\r\nSimple Program<\/strong>\r\n\r\n \r\n\r\nThe complete executing code for the example is given below.\r\n\r\n \r\n\r\nimport java.sql.*;\r\n\r\npublic class JDBC\r\n\r\n{\r\n\r\npublic static void main(String []args)throws Exception\r\n\r\n{\r\n\r\nClass.forName(\"sun.jdbc.odbc.JdbcOdbcDriver\");\r\n\r\nConnection\r\n\r\ncon=DriverManager.getConnection(\"jdbc:odbc:sample\");\r\n\r\nStatement st=con.createStatement();\r\n\r\nString query=\"select * from Student\";\r\n\r\nResultSet rs=st.executeQuery(query);\r\n\r\nwhile(rs.next())\r\n\r\n{\r\n\r\nSystem.out.println(rs.getString(\"name\"));\r\n\r\nSystem.out.println(rs.getInt(\"rollno\"));\r\n\r\nSystem.out.println(rs.getInt(\"mark1\"));\r\n\r\nSystem.out.println(rs.getInt(\"mark2\"));\r\n\r\n}\r\n\r\ncon.close();\r\n\r\n}\r\n\r\n}\r\n\r\n<\/div>\r\n\u00a0 \u00a0 Example Program<\/strong>\r\n

          \r\n\r\n \r\n

          This is another example program where the user is allowed to do all database operations such as select, insert, update, delete and clearing the screen. A connection is established to the URL jdbc :odbc:jnf2. The database name is jnf2 and the table name is Student. The user is displayed with a menu of operations. Upon selecting the choice, its corresponding function is called. Functions like display(), clrscr(), insert(), modify() and delete() have been coded.<\/p>\r\n \r\n

          In the function display() the method executeQuery() is called with the Statement object by passing the SQL SELECT statement which displays the records from the table.<\/p>\r\n \r\n

          In the function insert() the method executeUpdate() is called with the Statement object by passing the SQL INSERT statement which inserts a record into the table.<\/p>\r\n \r\n

          In the function modify() the method executeUpdate() is called with the Statement object by passing the SQL UPDATE statement which updates a single record in the table.<\/p>\r\n \r\n

          In the function delete() the method executeUpdate() is called with the Statement object by passing the SQL DELETE statement which deletes a record from the table.<\/p>\r\n \r\n

          In the function clrscr(), a for loop is executed which displays blank for 25 lines thereby it clears the screen.<\/p>\r\n \r\n

          Within each function a Connection object is created and closed at the end of the function. This is avoid connection errors while executing.<\/p>\r\n \r\n\r\nimport java.sql.*;\r\n\r\nimport java.io.*;\r\n\r\npublic class JDBC2\r\n\r\n{\r\n\r\npublic static void main(String []args)throws Exception\r\n\r\n{\r\n\r\nint ch;\r\n\r\nClass.forName(\"sun.jdbc.odbc.JdbcOdbcDriver\");\r\n\r\nInputStreamReader i = new InputStreamReader(System.in); BufferedReader b = new BufferedReader(i); try<\/span>\r\n\r\n{<\/span>\r\n\r\nConnection c;<\/span>\r\n\r\nc = DriverManager.getConnection(\"jdbc:odbc:jnf2\");<\/span>\r\n\r\ndo<\/span>\r\n\r\n{<\/span>\r\n\r\nSystem.out.println(\"\\t\\t\\t\\t Menu\");<\/span>\r\n\r\nS<\/span>ystem.out.println(\"\\t\\t\\t\\t 1.View\");<\/span>\r\n\r\nSystem.out.println(\"\\t\\t\\t\\t 2.Clear\");<\/span>\r\n\r\nSystem.out.println(\"\\t\\t\\t\\t 3.Insert\");<\/span>\r\n\r\nSystem.out.println(\"\\t\\t\\t\\t 4.Modify\");<\/span>\r\n\r\nSystem.out.println(\"\\t\\t\\t\\t 5.Delete\");<\/span>\r\n\r\nSystem.out.println(\"\\t\\t\\t\\t 6.Exit\");<\/span>\r\n\r\nSystem.out.println(\"Enter your choice:\");<\/span>\r\n\r\nch= Integer.parseInt(b.readLine());<\/span>\r\n\r\nswitch(ch)<\/span>\r\n\r\n{<\/span>\r\n\r\ncase 1:<\/span>\r\n\r\n{<\/span>\r\n\r\ndisplay();<\/span>\r\n\r\nbreak;<\/span>\r\n\r\n}<\/span>\r\n\r\ncase 2:<\/span>\r\n\r\n{<\/span>\r\n\r\nclrscr();<\/span>\r\n\r\nbreak;<\/span>\r\n\r\n}<\/span>\r\n\r\ncase 3:<\/span>\r\n\r\n{\r\n<\/span>\u00a0 \u00a0insert();<\/span>\r\n\r\ndisplay();<\/span>\r\n\r\nbreak;<\/span>\r\n\r\n}<\/span>\r\n\r\ncase 4:<\/span>\r\n\r\n{<\/span>\r\n\r\nmodify();<\/span>\r\n\r\ndisplay();<\/span>\r\n\r\nbreak;<\/span>\r\n\r\n}<\/span>\r\n\r\ncase 5:<\/span>\r\n\r\n{<\/span>\r\n\r\ndelete();<\/span>\r\n\r\ndisplay();<\/span>\r\n\r\nbreak;<\/span>\r\n\r\n}<\/span>\r\n\r\ncase 6:<\/span>\r\n\r\n{<\/span>\r\n\r\nSystem.exit(0);<\/span>\r\n\r\nbreak;<\/span>\r\n\r\n}<\/span>\r\n\r\ndefault:<\/span>\r\n\r\nSystem.out.println(\"invalid input\");<\/span>\r\n\r\n}}while(ch!=6);}<\/span>\r\n\r\ncatch(Exception e){}<\/span>\r\n\r\n}<\/span>\r\n\r\nstatic void display()<\/span>\r\n\r\n{\u00a0 try<\/span>\r\n\r\n{<\/span>\r\n\r\nResultSet rs;<\/span>\r\n\r\nint row=0;<\/span>\r\n\r\nConnection c1;<\/span>\r\n\r\nc1 = DriverManager.getConnection(\"jdbc:odbc:jnf2\"); Statement st=c1.createStatement();<\/span>\r\n\r\nrs=st.executeQuery(\"select * from student\");<\/span>\r\n\r\nSystem.out.println(\"\\nRegno\\tName\\tClass\\tMark\");<\/span>\r\n\r\nwhile(rs.next())<\/span>\r\n\r\nSystem.out.println(rs.getInt(1)+\"\\t\"<\/span>\r\n\r\n+rs.getString(2)+\"\\t\"+rs.getString(3)+\"\\t\"+rs.getInt(4); c1.close();<\/span>\r\n\r\n}<\/span>\r\n\r\ncatch(Exception e){}<\/span>\r\n\r\n}<\/span>\r\n\r\nstatic void insert()<\/span>\r\n\r\n{<\/span>\r\n\r\ntry<\/span>\r\n\r\n{<\/span>\r\n\r\nint row1;<\/span>\r\n\r\nStatement insst;<\/span>\r\n\r\nConnection c2;<\/span>\r\n\r\nc2 = DriverManager.getConnection(\"jdbc:odbc:jnf2\");<\/span>\r\n\r\ninsst=c2.createStatement();<\/span>\r\n\r\nSystem.out.println(\"9,jnf,vbj,30\");<\/span>\r\n\r\nrow1=insst.executeUpdate(\"insert into Student\" + \" values (9,'jnf','vbj',100)\");<\/span>\r\n\r\nc2.commit();<\/span>\r\n\r\nSystem.out.println(\"No of rows inserted = \" + row1);<\/span>\r\n\r\n}<\/span>\r\n\r\ncatch(SQLException e){System.out.println(\"error\" + e);}<\/span>\r\n\r\n}<\/span>\r\n\r\nstatic void modify()<\/span>\r\n\r\n{<\/span>\r\n\r\ntry<\/span>\r\n\r\n{<\/span>\r\n\r\nSystem.out.println(\"6,jnf,vbj,change 30 to 80\");<\/span>\r\n\r\nint row2;<\/span>\r\n\r\nStatement modst;<\/span>\r\n\r\nConnection c3;<\/span>\r\n\r\nc3 = DriverManager.getConnection(\"jdbc:odbc:jnf2\"); modst=c3.createStatement();<\/span>\r\n\r\nrow2=modst.executeUpdate(\"update student set mark=90 where regno=6\");<\/span>\r\n\r\nc3.commit();<\/span>\r\n\r\nSystem.out.println(\"No of rows update d = \" + row2);<\/span>\r\n\r\n}<\/span>\r\n\r\ncatch(SQLException e){System.out.println(\"error\" + e);}<\/span>\r\n\r\n}<\/span>\r\n\r\nstatic void delete()<\/span>\r\n\r\n{<\/span>\r\n\r\ntry<\/span>\r\n\r\n{<\/span>\r\n\r\nSystem.out.println(\"6,jnf,vbj,90\");<\/span>\r\n\r\nint row3;<\/span>\r\n\r\nStatement delst;<\/span>\r\n\r\nConnection c4;<\/span>\r\n\r\nc4 = DriverManager.getConnection(\"jdbc:odbc:jnf2\");<\/span>\r\n\r\ndelst=c4.createStatement();<\/span>\r\n\r\nrow3=delst.executeUpdate(\"delete from student where regno=6\");<\/span>\r\n\r\nif(row3>0)<\/span>\r\n\r\n{<\/span>\r\n\r\nc4.commit();<\/span>\r\n\r\nSystem.out.println(\"No of rows update d = \" + row3);<\/span>\r\n\r\n}<\/span>\r\n\r\nelse<\/span>\r\n\r\n{ System.out.println(\"record not found\"); }<\/span>\r\n\r\n}<\/span>\r\n\r\ncatch(SQLException e){System.out.println(\"error\" + e);}<\/span>\r\n\r\n}<\/span>\r\n\r\nstatic void clrscr()<\/span>\r\n\r\n{<\/span>\r\n\r\nfor(int k=1;k<25;k++)<\/span>\r\n\r\nSystem.out.println();<\/span>\r\n\r\n}<\/span>\r\n\r\n}<\/span>\r\n\r\nThe output of this code is shown in the below Figure 27.10 and Figure 27.11.<\/span>\r\n\r\n<\/div>\r\n\u00a0 Results<\/strong>\r\n

          \"\"<\/p>\r\n

          \"\"<\/p>\r\n\r\n

          \r\n\r\n\u00a0 \u00a0 Transaction Management<\/strong>\r\n\r\n \r\n

          Transactions are not explicitly opened and closed. Instead, the connection has a state called AutoCommit mode. If AutoCommit is true, then every statement is automatically committed. The default case is true.<\/p>\r\n \r\n\r\nsetAutoCommit<\/strong>\r\n\r\n \r\n\r\nConnection.setAutoCommit(boolean)\r\n\r\n \r\n

          If AutoCommit is false, then every statement is added to an ongoing transaction. It must explicitly commit or rollback the transaction using Connection.commit() and Connection.rollback().<\/p>\r\n \r\n\r\nStatements<\/strong>\r\n\r\n \r\n\r\nThere are three types of statements called,\r\n

            \r\n \t
          • Statement<\/li>\r\n \t
          • PreparedStatement<\/li>\r\n \t
          • CallableStatement<\/li>\r\n<\/ul>\r\n

            The Statement object is used for executing a static SQL statement and obtaining the results produced by it as we have seen earlier.<\/p>\r\n \r\n\r\nPrepared Statements<\/strong>\r\n\r\n \r\n

            Prepared Statements are used for queries that are executed many times. They are parsed (compiled) by the DBMS only once. The column values can be set after compilation. Instead of values, \u2018?\u2019 is used and it can be substituted later with actual values.<\/p>\r\n \r\n\r\nQuerying with PreparedStatement<\/strong>\r\n\r\n \r\n\r\nExample<\/strong>\r\n\r\n \r\n\r\nString queryStr =\r\n\r\n\"SELECT * FROM employee \" +\r\n\r\n\"WHERE id = ? and salary > ?\";\r\n\r\nPreparedStatement pstmt = con.pre pareStatement(queryStr);\r\n\r\npstmt.setString(1, \u201cE2001\");<\/span>\r\n\r\npstmt.setInt(2, 48000);<\/span>\r\n\r\nResultSet rs = pstmt.executeQuery();<\/span>\r\n\r\n<\/div>\r\n

            \u00a0 In this example code, the SELECT statement is given with '?' for the two attributes 'id' and 'salary'. PreparedStatement object is created by passing this query string. Then we use methods like setString() and setInt() to pass values for id and salary. Then the query is executed using executeQuery() method.<\/p>\r\n \r\n\r\nUpdating with PreparedStatement<\/strong>\r\n\r\n \r\n\r\nExample<\/strong>\r\n\r\n \r\n\r\nString deleteStr =\r\n\r\n\u201cDELET E FROM employee \" +\r\n\r\n\"WHERE id = ? and salary > ?\";\r\n\r\nPreparedStatement pstmt = con.pre pareStatement(deleteStr); pstmt.setString(1, \u201cE2001\");\r\n\r\npstmt.setDouble(2, 48000);\r\n\r\nint delnum = pstmt.executeUpdate();\r\n\r\nStatements vs. PreparedStatements<\/strong>\r\n\r\n \r\n\r\nExample<\/strong>\r\n\r\n \r\n\r\nThe difference in syntax is given with an example in the below Table 27.2.\r\n\r\nTable 27.2 Difference between Statement and PreparedStatement<\/strong>\r\n\r\n \r\n\r\n\"\"\r\n\r\n \r\n

            \r\n

            \u00a0 \u00a0 CallableStatement<\/strong><\/p>\r\n \r\n

            A CallableStatement is used to access the database stored procedures. The CallableStatement interface can also accept runtime input parameters.<\/p>\r\n \r\n

            A CallableStatement object is created using the Connection.prepareCall() method.<\/p>\r\n \r\n\r\nExample<\/strong>\r\n\r\n \r\n\r\nCallableStatement cstmt = null;\r\n\r\ntry {\r\n\r\nString SQL = \"{call getEmpName (?, ?)}\";\r\n\r\ncstmt = conn.prepareCall(SQL);\r\n\r\n. . .\r\n\r\n}\r\n\r\ncatch (SQLException e)\u00a0 { . . . }\r\n\r\nfinally { . . . }\r\n\r\n \r\n\r\nResultSet Meta-Data<\/strong>\r\n\r\n \r\n

            A ResultSetMetaData is an object that can be used to get information about the properties of the columns in a ResultSet object.<\/p>\r\n \r\n\r\nExample<\/strong>\r\n\r\n \r\n\r\nResultSetMetaData rsmd = rs.getMetaData();\r\n\r\nint numcols = rsmd.getColumnCount();\r\n\r\nfor (int i = 1 ; i <= numcols; i++)\r\n\r\n{\r\n\r\nSystem.out.print(rsmd.getColumnLabel(i)+\" \");\r\n\r\n}\r\n\r\n \r\n

            The method getMetaData() is used with the ResultSet object which returns a ResultSetMetaData object. With the ResultSetMetaData object, use the method getColumnCount() which returns the number of\u00a0columns in every record. Then we can use a for loop that executes for the number of columns to display the column label using the method getColumnLabel().<\/span><\/p>\r\n \r\n

            Summary<\/strong><\/p>\r\n\r\n<\/div>\r\n

            \u00a0 \u00a0 This module explains about how to work with SQL. It has explored the working of JDBC with simple programming illustrations. This section also discusses about the transaction management in JDBC and its basic operations that can be performed.<\/p>\r\n \r\n\r\nWeb Links<\/strong>\r\n

              \r\n \t
            • https:\/\/pawangkp.files.wordpress.com\/2009\/10\/deawsj-6_ppt<\/strong>_1b.ppt<\/strong><\/em><\/li>\r\n \t
            • https:\/\/www.tutorialspoint.com\/jdbc\/jdbc-statements.htm<\/em><\/li>\r\n<\/ul>\r\n \r\n\r\n \r\n\r\n ","rendered":"

              \u00a0 \u00a0SQL<\/strong><\/p>\n

               <\/p>\n

              It stands for Structured Query Language. SQL means Standardized syntax for \u201cquerying\u201d or accessing a Relational database.<\/p>\n

               <\/p>\n

              SQL Syntax<\/strong><\/p>\n

               <\/p>\n

              Before we study about working JDBC with SQL, let us have a basic understanding of how to write SQL queries. Some of the SQL statements which would appear in the example programs are given below.<\/p>\n

               <\/p>\n

              This command can insert a new record into the named table.<\/p>\n

              INSERT INTO table name( field1, field2 ) VALUES ( value1, value2 )<\/p>\n

               <\/p>\n

              This command can change an existing record or records.<\/p>\n

              UPDAT E tablename SET( field1 = value1, field2 = value2 ) WHERE condition<\/p>\n

               <\/p>\n

              This command can remove all records that match condition.<\/p>\n

              DELET E FROM tablename WHERE condition<\/p>\n

               <\/p>\n

              This command can retrieve all records that matches the condition.<\/p>\n

              SELECT field1, field2 FROM tablename WHERE condition<\/p>\n

              Install Mysql<\/p>\n

               <\/p>\n

              Let us now see the steps of installing MySQL to work with JDBC.<\/p>\n

               <\/p>\n

              Step 1: We need to Install Mysql Database from MYSQL official website dev.mysql.com. Figure 27.1 shows the Google search for Download of MySQL database.<\/p>\n

               <\/p>\n

              \"\"<\/p>\n

               <\/p>\n

              Step 2: <\/strong>Select the Platform type (i.e. Windows or Mac) and download the Installer. Figure 27.1 shows selecting the platform for Windows OS.<\/p>\n

               <\/p>\n

              \"\"<\/p>\n

               <\/p>\n

              Step 3: Create Database<\/strong><\/p>\n

               <\/p>\n

              Once MySQL is installed in your machine, Open MySQL Comand Line and connect to MySQL server by specifying the user name and password in the command line arguments to mysql<\/em> command.Then create a Database by using the below SQLstatement.<\/p>\n

               <\/p>\n

              Syntax<\/strong><\/p>\n

               <\/p>\n

              CREAT E DATABASE databasename;<\/p>\n

              Figure 27.3 shows creating the database in the MySQL command line using the above statement.<\/p>\n

               <\/p>\n

              \"\"<\/p>\n

              Step 4: <\/strong>USE Database<\/strong><\/p>\n

               <\/p>\n

               <\/p>\n

              From the list of database created, to use a particular database we use the command ‘USE database name’.<\/p>\n

               <\/p>\n

              Syntax<\/strong><\/p>\n

               <\/p>\n

              USE databasename;<\/p>\n

              Figure 27.4 shows how to use the database using the above statement.<\/p>\n

               <\/p>\n

              \"\"<\/p>\n

               <\/p>\n

              Step 5: <\/strong>Create Table<\/strong><\/p>\n

               <\/p>\n

              Now we can create Tables in Database using the create statement.<\/p>\n

               <\/p>\n

              Syntax<\/strong><\/p>\n

               <\/p>\n

              CREAT E TABLE tablename(attribute name datatypes);<\/p>\n

              Figure 27.5 shows how to create tables using the above statement.<\/p>\n

               <\/p>\n

              \"\"<\/p>\n

               <\/p>\n

              Step 6: <\/strong>Insert Query in Table<\/strong><\/p>\n

               <\/p>\n

              Once the table is created we can now Insert the values in the table. Note: <\/em>For varchar data type provide the values within quotes . Syntax<\/p>\n

               <\/p>\n

              INSERT INTO tablename VALUES(data);<\/p>\n

              Figure 27.6 shows how to insert records into the table using the above statement.<\/p>\n

               <\/p>\n

              \"\"<\/p>\n

               <\/p>\n

              Step 7: <\/strong>Select Query in Table<\/strong><\/p>\n

               <\/p>\n

              The Select statement can be used now to list all the values entered into the Table.<\/p>\n

               <\/p>\n

              Syntax<\/strong><\/p>\n

               <\/p>\n

              SELECT * FROM tablename;<\/p>\n

              Figure 27.7 shows how to select the records from the table using the above statement.<\/p>\n

               <\/p>\n

              \"\"<\/p>\n

               <\/p>\n

              Step 8: <\/strong>Delete Query in Table<\/strong><\/p>\n

               <\/p>\n

              To delete a particular Row from the table.<\/p>\n

               <\/p>\n

              Syntax<\/strong><\/p>\n

               <\/p>\n

              DELET E FROM tablename WHERE attributename=\u2018value\u2019;<\/p>\n

              Figure 27.8 shows how to delete a record from the table using the above statement.<\/p>\n

               <\/p>\n

              \"\"<\/p>\n

               <\/p>\n

              Step 9: <\/strong>Update Query in Table<\/strong><\/p>\n

               <\/p>\n

              To update values in a Row.<\/p>\n

               <\/p>\n

              Syntax<\/strong><\/p>\n

               <\/p>\n

              UPDAT E tablename SET attributename=newvalue WHERE attributename=value;<\/p>\n

              Figure 27.9 shows how to update records in the table using the above statement.<\/p>\n

               <\/p>\n

              \"\"<\/p>\n

               <\/p>\n

              Transactions<\/strong><\/p>\n

               <\/p>\n

              A Transaction means more than one statement which must all succeed (or all fail) together. If one fails, the system must reverse all previous actions. Also a transaction cannot leave database in an inconsistent state halfway through a transaction. The operation COMMIT is done for a complete transaction and ROLLBACK is done for an aborted transaction.<\/p>\n

               <\/p>\n

              Mapping Java Types to SQL Types<\/strong><\/p>\n

               <\/p>\n

              \"\"<\/p>\n

               <\/p>\n

              \n

              Table27.1 shows the existence of Java types for each SQL types.<\/p>\n

               <\/p>\n

              Database Time<\/strong><\/p>\n

               <\/p>\n

              Times in SQL are notoriously not standard.<\/p>\n

               <\/p>\n

              Java defines three classes to represent Date and Time.<\/p>\n

                \n
              • java.sql.Date<\/li>\n<\/ul>\n

                 <\/p>\n

                This class includes year, month, day.<\/p>\n

                  \n
                • java.sql.Time<\/li>\n<\/ul>\n

                  This class includes hours, minutes, seconds.<\/p>\n

                    \n
                  • java.sql.Timestamp<\/li>\n<\/ul>\n

                    This class includes year, month, day, hours, minutes, seconds and nanoseconds.<\/p>\n<\/div>\n

                    \u00a0 \u00a0 \u00a0JDBC Interface classes<\/strong><\/p>\n

                    \n

                    \u00a0 \u00a0 JDBC Interface classes are contained in java.sql.* package. This consists of the following classes.<\/p>\n

                      \n
                    • DriverManager<\/li>\n
                    • Connection<\/li>\n
                    • Statement<\/li>\n
                    • CallableStatement<\/li>\n
                    • PreparedStatement<\/li>\n
                    • Resultset<\/li>\n
                    • ResultSetMetaData<\/li>\n
                    • DatabaseMetaData<\/li>\n<\/ul>\n

                      \u00a0 \u00a0The JDBC Steps<\/strong><\/p>\n

                       <\/p>\n

                      1. Importing Packages.<\/p>\n

                      2. Registering the JDBC Drivers.<\/p>\n

                      3. Opening a Connection to a Database.<\/p>\n

                      4. Creating a Statement Object.<\/p>\n

                      5. Executing a Query and Returning a Result Set Object.<\/p>\n

                      6. Processing the Result Set.<\/p>\n

                      7. Closing the Result Set and Statement Objects.<\/p>\n

                      8. Closing the Connection.<\/p>\n

                       <\/p>\n

                      Simple Example<\/strong><\/p>\n

                       <\/p>\n

                      import java.sql;<\/p>\n

                      class SimpleExam ple {<\/p>\n

                      public static void main(String args[]) {<\/p>\n

                      String url = \u201cjdbc:odbc:jnf\u201d;<\/span><\/p>\n

                      try {<\/span><\/p>\n

                      Class.forName(\u201csun.jdbc.odbc.JdbcOdbcDriver\u201d); Connection myConnection = DriverManager.getConnection(url,\u201dIT\u201d,\u201dit@123\u201d); myConnection.close();<\/span><\/p>\n

                      }<\/span><\/p>\n

                      catch(Exception e) {<\/span><\/p>\n

                      e.printStackTrace();<\/span><\/p>\n

                      }<\/span><\/p>\n

                      }<\/span><\/p>\n

                      }<\/span><\/p>\n<\/div>\n

                      \n

                      This example code explains about registering for the driver an loading the driver using the URL, user name and password.<\/p>\n

                       <\/p>\n

                      Sending SQL statements<\/strong><\/p>\n

                       <\/p>\n

                      \u2026\u2026<\/p>\n

                       <\/p>\n

                      String query= \u201cSelect name,id,salary FROM employees ORDER By salary\u201d; Connection myConnection = DriverManager.getConnection(\u2026..);<\/p>\n

                       <\/p>\n

                      Statement myStatement = myConnection.createStatement();<\/p>\n

                      ResultSet rs = myStatement.executeQuery(query);<\/p>\n

                      while(rs.next)<\/p>\n

                      {<\/p>\n

                      String empName = rs.getString(1);<\/p>\n

                      String empId = rs.getString(2);<\/p>\n

                      String empSalary = rs.getString(3);<\/p>\n

                      System.out.println(\u201cEm ployee \u201d + empName + \u201c with id \u201d + empId + \u201c earns \u201d + empSalary);<\/p>\n

                      }<\/p>\n

                      myStatement.close();<\/p>\n

                      myConne ction.close ();<\/span><\/p>\n

                      \u2026\u2026.<\/span><\/p>\n<\/div>\n

                      \n

                      \u00a0 \u00a0Once Connection is established, Statement object is created. This Statement object is used to execute the query. The SQL query that has been tried in this example is an ‘Select’ query. Hence the retrieved records are read sequentially and displayed. Then the connection is closed.<\/p>\n

                       <\/p>\n

                      Simple Program<\/strong><\/p>\n

                       <\/p>\n

                      The complete executing code for the example is given below.<\/p>\n

                       <\/p>\n

                      import java.sql.*;<\/p>\n

                      public class JDBC<\/p>\n

                      {<\/p>\n

                      public static void main(String []args)throws Exception<\/p>\n

                      {<\/p>\n

                      Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);<\/p>\n

                      Connection<\/p>\n

                      con=DriverManager.getConnection(“jdbc:odbc:sample”);<\/p>\n

                      Statement st=con.createStatement();<\/p>\n

                      String query=”select * from Student”;<\/p>\n

                      ResultSet rs=st.executeQuery(query);<\/p>\n

                      while(rs.next())<\/p>\n

                      {<\/p>\n

                      System.out.println(rs.getString(“name”));<\/p>\n

                      System.out.println(rs.getInt(“rollno”));<\/p>\n

                      System.out.println(rs.getInt(“mark1”));<\/p>\n

                      System.out.println(rs.getInt(“mark2”));<\/p>\n

                      }<\/p>\n

                      con.close();<\/p>\n

                      }<\/p>\n

                      }<\/p>\n<\/div>\n

                      \u00a0 \u00a0 Example Program<\/strong><\/p>\n

                      \n

                       <\/p>\n

                      This is another example program where the user is allowed to do all database operations such as select, insert, update, delete and clearing the screen. A connection is established to the URL jdbc :odbc:jnf2. The database name is jnf2 and the table name is Student. The user is displayed with a menu of operations. Upon selecting the choice, its corresponding function is called. Functions like display(), clrscr(), insert(), modify() and delete() have been coded.<\/p>\n

                       <\/p>\n

                      In the function display() the method executeQuery() is called with the Statement object by passing the SQL SELECT statement which displays the records from the table.<\/p>\n

                       <\/p>\n

                      In the function insert() the method executeUpdate() is called with the Statement object by passing the SQL INSERT statement which inserts a record into the table.<\/p>\n

                       <\/p>\n

                      In the function modify() the method executeUpdate() is called with the Statement object by passing the SQL UPDATE statement which updates a single record in the table.<\/p>\n

                       <\/p>\n

                      In the function delete() the method executeUpdate() is called with the Statement object by passing the SQL DELETE statement which deletes a record from the table.<\/p>\n

                       <\/p>\n

                      In the function clrscr(), a for loop is executed which displays blank for 25 lines thereby it clears the screen.<\/p>\n

                       <\/p>\n

                      Within each function a Connection object is created and closed at the end of the function. This is avoid connection errors while executing.<\/p>\n

                       <\/p>\n

                      import java.sql.*;<\/p>\n

                      import java.io.*;<\/p>\n

                      public class JDBC2<\/p>\n

                      {<\/p>\n

                      public static void main(String []args)throws Exception<\/p>\n

                      {<\/p>\n

                      int ch;<\/p>\n

                      Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);<\/p>\n

                      InputStreamReader i = new InputStreamReader(System.in); BufferedReader b = new BufferedReader(i); try<\/span><\/p>\n

                      {<\/span><\/p>\n

                      Connection c;<\/span><\/p>\n

                      c = DriverManager.getConnection(“jdbc:odbc:jnf2”);<\/span><\/p>\n

                      do<\/span><\/p>\n

                      {<\/span><\/p>\n

                      System.out.println(“\\t\\t\\t\\t Menu”);<\/span><\/p>\n

                      S<\/span>ystem.out.println(“\\t\\t\\t\\t 1.View”);<\/span><\/p>\n

                      System.out.println(“\\t\\t\\t\\t 2.Clear”);<\/span><\/p>\n

                      System.out.println(“\\t\\t\\t\\t 3.Insert”);<\/span><\/p>\n

                      System.out.println(“\\t\\t\\t\\t 4.Modify”);<\/span><\/p>\n

                      System.out.println(“\\t\\t\\t\\t 5.Delete”);<\/span><\/p>\n

                      System.out.println(“\\t\\t\\t\\t 6.Exit”);<\/span><\/p>\n

                      System.out.println(“Enter your choice:”);<\/span><\/p>\n

                      ch= Integer.parseInt(b.readLine());<\/span><\/p>\n

                      switch(ch)<\/span><\/p>\n

                      {<\/span><\/p>\n

                      case 1:<\/span><\/p>\n

                      {<\/span><\/p>\n

                      display();<\/span><\/p>\n

                      break;<\/span><\/p>\n

                      }<\/span><\/p>\n

                      case 2:<\/span><\/p>\n

                      {<\/span><\/p>\n

                      clrscr();<\/span><\/p>\n

                      break;<\/span><\/p>\n

                      }<\/span><\/p>\n

                      case 3:<\/span><\/p>\n

                      {
                      \n<\/span>\u00a0 \u00a0insert();<\/span><\/p>\n

                      display();<\/span><\/p>\n

                      break;<\/span><\/p>\n

                      }<\/span><\/p>\n

                      case 4:<\/span><\/p>\n

                      {<\/span><\/p>\n

                      modify();<\/span><\/p>\n

                      display();<\/span><\/p>\n

                      break;<\/span><\/p>\n

                      }<\/span><\/p>\n

                      case 5:<\/span><\/p>\n

                      {<\/span><\/p>\n

                      delete();<\/span><\/p>\n

                      display();<\/span><\/p>\n

                      break;<\/span><\/p>\n

                      }<\/span><\/p>\n

                      case 6:<\/span><\/p>\n

                      {<\/span><\/p>\n

                      System.exit(0);<\/span><\/p>\n

                      break;<\/span><\/p>\n

                      }<\/span><\/p>\n

                      default:<\/span><\/p>\n

                      System.out.println(“invalid input”);<\/span><\/p>\n

                      }}while(ch!=6);}<\/span><\/p>\n

                      catch(Exception e){}<\/span><\/p>\n

                      }<\/span><\/p>\n

                      static void display()<\/span><\/p>\n

                      {\u00a0 try<\/span><\/p>\n

                      {<\/span><\/p>\n

                      ResultSet rs;<\/span><\/p>\n

                      int row=0;<\/span><\/p>\n

                      Connection c1;<\/span><\/p>\n

                      c1 = DriverManager.getConnection(“jdbc:odbc:jnf2”); Statement st=c1.createStatement();<\/span><\/p>\n

                      rs=st.executeQuery(“select * from student”);<\/span><\/p>\n

                      System.out.println(“\\nRegno\\tName\\tClass\\tMark”);<\/span><\/p>\n

                      while(rs.next())<\/span><\/p>\n

                      System.out.println(rs.getInt(1)+”\\t”<\/span><\/p>\n

                      +rs.getString(2)+”\\t”+rs.getString(3)+”\\t”+rs.getInt(4); c1.close();<\/span><\/p>\n

                      }<\/span><\/p>\n

                      catch(Exception e){}<\/span><\/p>\n

                      }<\/span><\/p>\n

                      static void insert()<\/span><\/p>\n

                      {<\/span><\/p>\n

                      try<\/span><\/p>\n

                      {<\/span><\/p>\n

                      int row1;<\/span><\/p>\n

                      Statement insst;<\/span><\/p>\n

                      Connection c2;<\/span><\/p>\n

                      c2 = DriverManager.getConnection(“jdbc:odbc:jnf2”);<\/span><\/p>\n

                      insst=c2.createStatement();<\/span><\/p>\n

                      System.out.println(“9,jnf,vbj,30”);<\/span><\/p>\n

                      row1=insst.executeUpdate(“insert into Student” + ” values (9,’jnf’,’vbj’,100)”);<\/span><\/p>\n

                      c2.commit();<\/span><\/p>\n

                      System.out.println(“No of rows inserted = ” + row1);<\/span><\/p>\n

                      }<\/span><\/p>\n

                      catch(SQLException e){System.out.println(“error” + e);}<\/span><\/p>\n

                      }<\/span><\/p>\n

                      static void modify()<\/span><\/p>\n

                      {<\/span><\/p>\n

                      try<\/span><\/p>\n

                      {<\/span><\/p>\n

                      System.out.println(“6,jnf,vbj,change 30 to 80”);<\/span><\/p>\n

                      int row2;<\/span><\/p>\n

                      Statement modst;<\/span><\/p>\n

                      Connection c3;<\/span><\/p>\n

                      c3 = DriverManager.getConnection(“jdbc:odbc:jnf2”); modst=c3.createStatement();<\/span><\/p>\n

                      row2=modst.executeUpdate(“update student set mark=90 where regno=6”);<\/span><\/p>\n

                      c3.commit();<\/span><\/p>\n

                      System.out.println(“No of rows update d = ” + row2);<\/span><\/p>\n

                      }<\/span><\/p>\n

                      catch(SQLException e){System.out.println(“error” + e);}<\/span><\/p>\n

                      }<\/span><\/p>\n

                      static void delete()<\/span><\/p>\n

                      {<\/span><\/p>\n

                      try<\/span><\/p>\n

                      {<\/span><\/p>\n

                      System.out.println(“6,jnf,vbj,90”);<\/span><\/p>\n

                      int row3;<\/span><\/p>\n

                      Statement delst;<\/span><\/p>\n

                      Connection c4;<\/span><\/p>\n

                      c4 = DriverManager.getConnection(“jdbc:odbc:jnf2”);<\/span><\/p>\n

                      delst=c4.createStatement();<\/span><\/p>\n

                      row3=delst.executeUpdate(“delete from student where regno=6”);<\/span><\/p>\n

                      if(row3>0)<\/span><\/p>\n

                      {<\/span><\/p>\n

                      c4.commit();<\/span><\/p>\n

                      System.out.println(“No of rows update d = ” + row3);<\/span><\/p>\n

                      }<\/span><\/p>\n

                      else<\/span><\/p>\n

                      { System.out.println(“record not found”); }<\/span><\/p>\n

                      }<\/span><\/p>\n

                      catch(SQLException e){System.out.println(“error” + e);}<\/span><\/p>\n

                      }<\/span><\/p>\n

                      static void clrscr()<\/span><\/p>\n

                      {<\/span><\/p>\n

                      for(int k=1;k<25;k++)<\/span><\/p>\n

                      System.out.println();<\/span><\/p>\n

                      }<\/span><\/p>\n

                      }<\/span><\/p>\n

                      The output of this code is shown in the below Figure 27.10 and Figure 27.11.<\/span><\/p>\n<\/div>\n

                      \u00a0 Results<\/strong><\/p>\n

                      \"\"<\/p>\n

                      \"\"<\/p>\n

                      \n

                      \u00a0 \u00a0 Transaction Management<\/strong><\/p>\n

                       <\/p>\n

                      Transactions are not explicitly opened and closed. Instead, the connection has a state called AutoCommit mode. If AutoCommit is true, then every statement is automatically committed. The default case is true.<\/p>\n

                       <\/p>\n

                      setAutoCommit<\/strong><\/p>\n

                       <\/p>\n

                      Connection.setAutoCommit(boolean)<\/p>\n

                       <\/p>\n

                      If AutoCommit is false, then every statement is added to an ongoing transaction. It must explicitly commit or rollback the transaction using Connection.commit() and Connection.rollback().<\/p>\n

                       <\/p>\n

                      Statements<\/strong><\/p>\n

                       <\/p>\n

                      There are three types of statements called,<\/p>\n

                        \n
                      • Statement<\/li>\n
                      • PreparedStatement<\/li>\n
                      • CallableStatement<\/li>\n<\/ul>\n

                        The Statement object is used for executing a static SQL statement and obtaining the results produced by it as we have seen earlier.<\/p>\n

                         <\/p>\n

                        Prepared Statements<\/strong><\/p>\n

                         <\/p>\n

                        Prepared Statements are used for queries that are executed many times. They are parsed (compiled) by the DBMS only once. The column values can be set after compilation. Instead of values, \u2018?\u2019 is used and it can be substituted later with actual values.<\/p>\n

                         <\/p>\n

                        Querying with PreparedStatement<\/strong><\/p>\n

                         <\/p>\n

                        Example<\/strong><\/p>\n

                         <\/p>\n

                        String queryStr =<\/p>\n

                        “SELECT * FROM employee ” +<\/p>\n

                        “WHERE id = ? and salary > ?”;<\/p>\n

                        PreparedStatement pstmt = con.pre pareStatement(queryStr);<\/p>\n

                        pstmt.setString(1, \u201cE2001″);<\/span><\/p>\n

                        pstmt.setInt(2, 48000);<\/span><\/p>\n

                        ResultSet rs = pstmt.executeQuery();<\/span><\/p>\n<\/div>\n

                        \u00a0 In this example code, the SELECT statement is given with ‘?’ for the two attributes ‘id’ and ‘salary’. PreparedStatement object is created by passing this query string. Then we use methods like setString() and setInt() to pass values for id and salary. Then the query is executed using executeQuery() method.<\/p>\n

                         <\/p>\n

                        Updating with PreparedStatement<\/strong><\/p>\n

                         <\/p>\n

                        Example<\/strong><\/p>\n

                         <\/p>\n

                        String deleteStr =<\/p>\n

                        \u201cDELET E FROM employee ” +<\/p>\n

                        “WHERE id = ? and salary > ?”;<\/p>\n

                        PreparedStatement pstmt = con.pre pareStatement(deleteStr); pstmt.setString(1, \u201cE2001″);<\/p>\n

                        pstmt.setDouble(2, 48000);<\/p>\n

                        int delnum = pstmt.executeUpdate();<\/p>\n

                        Statements vs. PreparedStatements<\/strong><\/p>\n

                         <\/p>\n

                        Example<\/strong><\/p>\n

                         <\/p>\n

                        The difference in syntax is given with an example in the below Table 27.2.<\/p>\n

                        Table 27.2 Difference between Statement and PreparedStatement<\/strong><\/p>\n

                         <\/p>\n

                        \"\"<\/p>\n

                         <\/p>\n

                        \n

                        \u00a0 \u00a0 CallableStatement<\/strong><\/p>\n

                         <\/p>\n

                        A CallableStatement is used to access the database stored procedures. The CallableStatement interface can also accept runtime input parameters.<\/p>\n

                         <\/p>\n

                        A CallableStatement object is created using the Connection.prepareCall() method.<\/p>\n

                         <\/p>\n

                        Example<\/strong><\/p>\n

                         <\/p>\n

                        CallableStatement cstmt = null;<\/p>\n

                        try {<\/p>\n

                        String SQL = “{call getEmpName (?, ?)}”;<\/p>\n

                        cstmt = conn.prepareCall(SQL);<\/p>\n

                        . . .<\/p>\n

                        }<\/p>\n

                        catch (SQLException e)\u00a0 { . . . }<\/p>\n

                        finally { . . . }<\/p>\n

                         <\/p>\n

                        ResultSet Meta-Data<\/strong><\/p>\n

                         <\/p>\n

                        A ResultSetMetaData is an object that can be used to get information about the properties of the columns in a ResultSet object.<\/p>\n

                         <\/p>\n

                        Example<\/strong><\/p>\n

                         <\/p>\n

                        ResultSetMetaData rsmd = rs.getMetaData();<\/p>\n

                        int numcols = rsmd.getColumnCount();<\/p>\n

                        for (int i = 1 ; i <= numcols; i++)<\/p>\n

                        {<\/p>\n

                        System.out.print(rsmd.getColumnLabel(i)+” “);<\/p>\n

                        }<\/p>\n

                         <\/p>\n

                        The method getMetaData() is used with the ResultSet object which returns a ResultSetMetaData object. With the ResultSetMetaData object, use the method getColumnCount() which returns the number of\u00a0columns in every record. Then we can use a for loop that executes for the number of columns to display the column label using the method getColumnLabel().<\/span><\/p>\n

                         <\/p>\n

                        Summary<\/strong><\/p>\n<\/div>\n

                        \u00a0 \u00a0 This module explains about how to work with SQL. It has explored the working of JDBC with simple programming illustrations. This section also discusses about the transaction management in JDBC and its basic operations that can be performed.<\/p>\n

                         <\/p>\n

                        Web Links<\/strong><\/p>\n

                          \n
                        • https:\/\/pawangkp.files.wordpress.com\/2009\/10\/deawsj-6_ppt<\/strong>_1b.ppt<\/strong><\/em><\/li>\n
                        • https:\/\/www.tutorialspoint.com\/jdbc\/jdbc-statements.htm<\/em><\/li>\n<\/ul>\n

                           <\/p>\n

                           <\/p>\n

                           <\/p>\n","protected":false},"author":4,"menu_order":25,"template":"","meta":{"pb_show_title":"on","pb_short_title":"","pb_subtitle":"","pb_authors":["dr-m-vijayalakshmi"],"pb_section_license":""},"chapter-type":[],"contributor":[58],"license":[],"class_list":["post-238","chapter","type-chapter","status-publish","hentry","contributor-dr-m-vijayalakshmi"],"part":3,"_links":{"self":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/csp12\/wp-json\/pressbooks\/v2\/chapters\/238","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/csp12\/wp-json\/pressbooks\/v2\/chapters"}],"about":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/csp12\/wp-json\/wp\/v2\/types\/chapter"}],"author":[{"embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/csp12\/wp-json\/wp\/v2\/users\/4"}],"version-history":[{"count":7,"href":"https:\/\/ebooks.inflibnet.ac.in\/csp12\/wp-json\/pressbooks\/v2\/chapters\/238\/revisions"}],"predecessor-version":[{"id":584,"href":"https:\/\/ebooks.inflibnet.ac.in\/csp12\/wp-json\/pressbooks\/v2\/chapters\/238\/revisions\/584"}],"part":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/csp12\/wp-json\/pressbooks\/v2\/parts\/3"}],"metadata":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/csp12\/wp-json\/pressbooks\/v2\/chapters\/238\/metadata\/"}],"wp:attachment":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/csp12\/wp-json\/wp\/v2\/media?parent=238"}],"wp:term":[{"taxonomy":"chapter-type","embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/csp12\/wp-json\/pressbooks\/v2\/chapter-type?post=238"},{"taxonomy":"contributor","embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/csp12\/wp-json\/wp\/v2\/contributor?post=238"},{"taxonomy":"license","embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/csp12\/wp-json\/wp\/v2\/license?post=238"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}