In this tutorial, we will learn about how to create a table in the database, and how to create records in these tables through JSP. In this tutorial, you will learn-
Create Table Create Records JSP Operations: Insert, Update, Delete, Select
Create Table
In MYSQL database, we can create a table in the database with any MYSQL client. Here we are using PHPMyadminclient, and there we have an option “new” to create a new table using below screenshot.
In this, we have to provide table name as guru_test, and we will create two fields’emp_id and emp_name. Emp_idis havingdatatype as int Emp_nameis havingdatatype as varchar
Another option is by using command prompt and changes to MYSQL directory: C:> C:>cd Program Files\MY SQL\bin C:>Program Files\MySql\bin> We can login to database as follows: C:\Program Files\MYSQL\bin>mysql –u gururoot –p Enter Password: ** Mysql> Create table guru_testin the database named as GuruTestas the following on MYSQL prompt: First the records are inserted using INSERT query and then we can use SELECTquery to check whether the table is created or not.
Create Records
After creating a table we need to create records into the guru_test table using insert query, which is shown below: The records entered here are:
1 and guru emp1 2 and guru emp2
JSP Operations: Insert, Update, Delete, Select
Using JSP, we can do multiple operations into the database. We can insert the records, and also, we can delete the records which are not required. If any record needs to be edited, then we can do using an update. The Selectoperation will help to fetch the records which are required. Select The Select operation is used to select the records from the table. Example: In this example, we are going to learn about the select operation of fetching records from guru_test table which was created in the above section. Explanation of the code: Code Line 1: Here we are importing io, uti and SQL libraries of java. Code Line 3: Here we are importing core library of JSTL and giving its prefix as gurucore which will help to get output. Code Line 4: Here we are importing SQL library of jstl and giving its prefix as gurusql which will help to do the SQL operations. Code Line 15-17: Here using gurusql, we are connecting data source by naming variable as “guru” and driver as a JDBC driver. Also adding username and password with “gururoot” and “guru”. Code Line 19-21: Here we are using SQL query of the select query. Code Line 31-32: We are printing the output for emp id and emp name, which are fetched from the results of the query and using foreach loop we print the output. When you execute the above code, we will get the output as below; Output: Here both the records will be fetched from the database 1 guru emp1 2 guru emp2 Insert Insert operator is used to insert the records into the database. Example: In this example, we are going to learn about inserting the records in the table guru_test Explanation of the code: Code Line 19-20: Here we are inserting records into the table guru_test of GuruTestdatabase.The records inserted are: empID – 3 and empname – emp emp3.These records will be inserted in the table When you execute the code, the records are inserted into the table as guru_test ,with value 3 and emp emp3. Note: Here we are not showing the output as we are just inserting the record in the table. We can get the record using select query as ‘select * from guru_test’. If the record was inserted then, we would get the value as 3 and emp3.If the record is not inserted then, 3 will not be seen in records in the table. Delete This is delete operation where we delete the records from the table guru_test. Example: Here we will delete query to delete the record from the table guru_test. The record which has to be deleted has to be set in variable “guruid”, and the corresponding record is deleted from the database. Explanation of the code: Code Line 18: We are setting a variable guruid whose value is 3, which has to be deleted from the database. This is always a primary key of the table. In this case, the primary key is the emp_id. Code Line 19-22: Here we are using a delete query which is setting a parameter in the where clause.Here parameter is guruid which is set in code line 18. The corresponding record is deleted. Output: When you execute the above code, the record with emp_id as 3 is deleted. Note: In this example, we cannot show the output as we are deleting the record from the table.To check whether that record is deleted, we need to use select query “select * from guru_test”. In that case, if we get 3 as emp id then delete query has failed else the record has been deleted successfully. Update The update is used to edit the records in the table. Example: Explanation of the code: Code Line 18: Here we are setting a variable guruid as 2. This is the ID where we want to update the record. Code Line 19-22: Here we are using an update query to update the record in the table guru_test of the record, which is set in point 18.Here emp guru2 is replaced by emp guru99 Output: When you execute the above code the record withemp_id 2 is changed to 99. So, now the output will show emp”guru99″ instead of emp “guru2”. Summary: In this tutorial, we learnt about connecting JSP to database and database access. Also, we learned about various operations perform on the table in the database like create, delete, update, etc.
