Pages

Thursday, September 22, 2011

How to connect your JSP page with mysql database to create, insert and update tables

I am using Netbeans IDE 6.8 so the tutorial also based on that software

here we go

Requirements:

Download Connector/J 

Must have Mysql database installed

Step 1:


Extract Connector/J  and copy "mysql-connector-java-5.1.17-bin.jar" file and paste it in " apache-tomcat-5.5.33\apache-tomcat-5.5.33\common\lib "

NOTE : if you have more than such files in "apache-tomcat-5.5.33" folder than it will clash resulting connection failure


Step 2:


Paste below code to connect




        <%


                    String connectionURL = "jdbc:mysql://localhost:3306/Database_Name";
                    Connection connection = null;
                    Statement statement = null;
  try {


                        Class.forName("com.mysql.jdbc.Driver").newInstance();
                        connection = DriverManager.getConnection(connectionURL, "root", "pass");
                        statement = connection.createStatement();
                        String QueryString =
                                "create table new_Table (id int not null auto_increment,name " + "varchar(25),city varchar(20), primary key(id));";
                        statement.executeUpdate(QueryString);





                    out.print("Query Has Successfully Executed !");
 }
 catch (Exception ex) 
{
                    out.print("Can not execute query");
}
        %>



Above query will create a table "new_Table" in Database_Name

 Spares Parts


To Create Table Use:



String QueryString =  "create table user_master(id int not null auto_increment,name " + "varchar(25),city varchar(20), primary key(id));";



To Insert Table Use:


String QueryString = "INSERT INTO user_master (name, city) VALUES ( 'Name', 'Chd )";


To Update Table Use:


String QueryString = "UPDATE user_master SET name = 'jolly.exe', city = 'CHD' WHERE id = 2";

AD