Wednesday, 18 April 2012

How to get MySQL AUTO_INCREMENT field in JSP?


Database Table:
CREATE TABLE STUDENT
(
                SID INT AUTO_INCREMENT,
                SNAME VARCHAR (100),
                SAGE INT,
                CONSTRAINT student_id_pk PRIMARY KEY (SID)
); 

JSP:
Procedure 1 :

<%--
    Document   : studentReg
    Created on : Apr 14, 2012, 7:23:48 PM
   Author     : pkjana
--%>
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
        <%!           
Connection conn = null;
                PreparedStatement ps = null;
                ResultSet rs = null;
        %>
        <%
            try {
                DriverManager.registerDriver(new com.mysql.jdbc.Driver());
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/otbdc", "userid", "password");

                String sql = "INSERT INTO STUDENT (SID, SNAME, SAGE) VALUES(?,?,?)";

                ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setInt(1, 0);
                ps.setString(2, "Brahma");
                ps.setInt(3, 10);
                int returnLastInsertId = ps.executeUpdate();
                if (returnLastInsertId != -1) {
                    rs = ps.getGeneratedKeys();
                    rs.next();
                    out.println("STUDENT ID: " + rs.getInt(1));
                }
            } catch (Exception e) {
                out.println("Error:" +e);
            } finally {               
                try {
                    rs.close();
                    ps.close();
                    conn.close();
                } catch (SQLException e) {
                    out.println("Error:" + e);
                }
            }
        %>
    </body>
</html>

Procedure 2 :( By Table Column Name)

<%--
    Document   : studentReg
    Created on : Apr 14, 2012, 7:23:48 PM
   Author     : pkjana
--%>
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
        <%!           
            Connection conn = null;
            PreparedStatement ps = null;
            ResultSet rs = null;
        %>
        <%
            try {
                DriverManager.registerDriver(new com.mysql.jdbc.Driver());
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/otbdc", "userid", "password");
                String sql = "INSERT INTO STUDENT (SID, SNAME, SAGE) VALUES (?,?,?)";

                String rv[] = {"SID"};
                ps = conn.prepareStatement(sql, rv);
                ps.setInt(1, 0);
                ps.setString(2, "Krishna");
                ps.setInt(3, 12);

                int returnLastInsertId = ps.executeUpdate();
                if (returnLastInsertId != -1) {
                    rs = ps.getGeneratedKeys();
                    rs.next();
                    out.println("STUDENT ID: " + rs.getInt(1));
                }
            } catch (Exception e) {
                out.println("Error:" +e);
            } finally {               
                try {
                    rs.close();
                    ps.close();
                    conn.close();
                } catch (SQLException e) {
                    out.println("Error:" + e);
                }
            }
        %>
    </body>
</html>

Procedure 3 :( By Table Column Index)

<%--
    Document   : studentReg
    Created on : Apr 14, 2012, 7:23:48 PM
   Author     : pkjana
--%>
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
        <%!           
            Connection conn = null;
            PreparedStatement ps = null;
            ResultSet rs = null;
        %>
        <%
            try {
                DriverManager.registerDriver(new com.mysql.jdbc.Driver());
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/otbdc", "userid", "password");
                String sql = "INSERT INTO STUDENT (SID, SNAME, SAGE) VALUES (?,?,?)";

                int rtv[]={1};
 ps = conn.prepareStatement(sql, rtv);
                ps.setInt(1, 0);
                ps.setString(2, "Siba");
                ps.setInt(3, 20);

                int returnLastInsertId = ps.executeUpdate();
                if (returnLastInsertId != -1) {
                    rs = ps.getGeneratedKeys();
                    rs.next();
                    out.println("STUDENT ID: " + rs.getInt(1));
                }
            } catch (Exception e) {
                out.println("Error:" +e);
            } finally {               
                try {
                    rs.close();
                    ps.close();
                    conn.close();
                } catch (SQLException e) {
                    out.println("Error:" + e);
                }
            }
        %>
    </body>
</html>