Java PreparedStatement

Java code for establishing a Database Connection with MySQL Database

    package bbsProject;
    import java.sql.Connection;
    import java.sql.DriverManager;
    public class bbsdemo {
      
      public static void main(String args[]) 
      {
        try
          {
              Connection con;
              Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
              con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","");
              System.out.println("Connection successfully opened");
              con.close();
          }
          catch(Exception e)
          {
            System.out.println(e.getMessage());
          }	
        }
      } 

Output

Java code for Inserting a record into the student table in MySQL Database (WITHOUT PREPAREDSTATEMENT)

Schema for the Student table inside the test Database in MySQL is given below. We will insert a record in this table

      create table students( id int auto_increment primary key,
      name varchar(10),password varchar(10),country varchar(10),mark int(10));
    

Java Code for inserting a record in the Students table is given below

      package bbsProject;
      import java.sql.Connection;
      import java.sql.DriverManager;
      import java.sql.ResultSet;
      import java.sql.SQLException;
      import java.sql.Statement;
      import java.util.Scanner;
      public class bbsdemo {
        
        public static void main(String args[]) throws InstantiationException, IllegalAccessException, ClassNotFoundException 
        {
          try
            {
              Connection con=connect();
              //establish connection with the database 
              System.out.println("Enter the student details");
              Scanner input = new Scanner(System.in);
              String name = input.nextLine();
              String password = input.nextLine();
              String country = input.nextLine();
              int mark = input.nextInt();
              Statement stmt = con.createStatement();
              stmt.executeUpdate("insert into students(name,password,country,mark) values('"+name+"','"+password+"','"+country+"','"+mark+"');");
              System.out.println("Record inserted successfully");
              stmt.close();
              con.close();
              input.close();
              
            }
            catch(SQLException E)
            {
              System.out.println(E.getMessage());
            }
             
           }
           
           public static Connection connect() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException
           {
              Connection con=null;
              Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
              con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","");
              System.out.println("Connection Established Successfully");
              return con;
              
           }
        } 

Output

Java code for updating a record in the student table. Here the password is updated (WITHOUT PREPAREDSTATEMENT)

        package bbsProject;
        import java.sql.Connection;
        import java.sql.DriverManager;
        import java.sql.SQLException;
        import java.sql.Statement;
        import java.util.Scanner;
        public class bbsdemo {
          
          public static void main(String args[]) throws InstantiationException, IllegalAccessException, ClassNotFoundException 
          {
            try
            {
              Connection con=null;
              Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
              con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","");
              System.out.println("Connection Established Successfully");
              Statement stmt = con.createStatement();
              stmt.executeUpdate("update students set password='testnow' where name='satish';");
              System.out.println("Record updated successfully");
              stmt.close();
              con.close();
              
            }
            catch(SQLException E)
            {
              System.out.println(E.getMessage());
            }
             
           }
        } 

Output

Java code for deleting a record from the student table in MySQL Database (WITHOUT PREPAREDSTATEMENT)

package bbsProject;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class bbsdemo {
	
  public static void main(String args[]) throws InstantiationException, IllegalAccessException, ClassNotFoundException 
  {
	  try
		{
			Connection con=null;
			Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
			con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","");
			System.out.println("Connection Established Successfully");
			Statement stmt = con.createStatement();
			stmt.executeUpdate("delete from students where name='satish';");
			System.out.println("Record Deleted successfully");
			stmt.close();
			con.close();
			
		}
		catch(SQLException E)
		{
			System.out.println(E.getMessage());
		}
		 
	 }
}   

Output

Java code for selecting all records from the student table in MySQL Database (WITHOUT PREPAREDSTATEMENT)

package bbsProject;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class bbsdemo {
	
  public static void main(String args[]) throws InstantiationException, IllegalAccessException, ClassNotFoundException 
  {
	  try
		{
			Connection con=null;
			Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
			con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","");
			System.out.println("Connection Established Successfully");
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("Select * from students");
			int recordcount=0;
			while(rs.next())
			{
				System.out.println(rs.getInt(1)+rs.getString(2)+rs.getString(3)+rs.getString(4)+rs.getInt(5));
				recordcount++;
			}
			if(recordcount==0)
			{
				System.out.println("Sorry No records in the table");
			}
			stmt.close();
			rs.close();
			con.close();
			
		}
		catch(SQLException E)
		{
			System.out.println(E.getMessage());
		}
		 
	 }
}   

Output

Java code for inserting a record in MySQL Database (Using PREPAREDSTATEMENT)

                package bbsProject;
                import java.sql.Connection;
                import java.sql.DriverManager;
                import java.sql.PreparedStatement;
                import java.sql.ResultSet;
                import java.sql.SQLException;
                import java.sql.Statement;
                import java.util.Scanner;
                public class bbsdemo {
                  
                  public static void main(String args[]) throws InstantiationException, IllegalAccessException, ClassNotFoundException 
                  {
                    try
                    {
                      Connection con=connect();
                      System.out.println("Enter the student details");
                      Scanner input = new Scanner(System.in);
                      String name = input.nextLine();
                      String password = input.nextLine();
                      String country = input.nextLine();
                      int mark = input.nextInt();
                      String sql = "insert into students(name,password,country,mark) values (?,?,?,?);";
                      PreparedStatement stmt = con.prepareStatement(sql);
                      stmt.setString(1, name);
                      stmt.setString(2, password);
                      stmt.setString(3, country);
                      stmt.setInt(4, mark);
                      stmt.executeUpdate();
                      System.out.println("Record Inserted Successfully");
                      stmt.close();
                      con.close();
                      
                    }
                    catch(SQLException E)
                    {
                      System.out.println(E.getMessage());
                    }
                     
                   }
                   
                   public static Connection connect() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException
                   {
                      Connection con=null;
                      Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
                      con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","");
                      System.out.println("Connection Established Successfully");
                      return con;
                      
                   }
                }
                 

Output