last insterted record does not reflect in select statement in mysql using JDBC

Question

so here is my problem.. i am trying to add country name in country table having a two fields country id and country name. i am taking a country name through jsp page entered by user and list all the country names available in a table but the problem is whatever the name inserted by user does not display in listing page....

here is code snippets

for inserting record

public boolean insertCountry(CountryBean bean)
{

String country=bean.getCountryname();

boolean flag=false;

int result;



try
{

    conn=connection.createConnection();


    stmt=conn.createStatement();
    String sqlInsert="insert into country(Country_Name) values('"+country+"')";

    result=stmt.executeUpdate(sqlInsert);






    if(result>0)
        flag=true;
    else
        flag=false;



}




catch(Exception e)
{
    e.printStackTrace();
}

return flag;

}

for displaying records

public List ListCountry() throws SQLException {

    List<CountryBean> list=new ArrayList<CountryBean>();
    CountryBean bean;

    Connection conn=null;
    Statement stmt=null;
    ResultSet rs=null;



    try
    {

        conn=connection.createConnection();

        stmt=conn.createStatement();

        String sqlselect="select * from country order by country_name";
        rs=stmt.executeQuery(sqlselect);

        if(rs!=null)
        {
            while(rs.next())
            {
                bean=new CountryBean();
                bean.setCountryname(rs.getString("country_name"));
                System.out.println(rs.getString("country_name"));

                list.add(bean);

            }


        }
    }

        catch(Exception e)
        {
            e.printStackTrace();
        }
    finally
    {
        if(rs!=null){
            rs.close();
        }

        if(conn!=null){
            conn.close();
        }
        if(stmt!=null){
            stmt.close();
        }

    }

    return list;

}

}

listing jsp page

<table border="2">
    <tr>
        <th>Country Name</th>

    </tr>
    <%
        for (int i = 0; i < list.size(); i++) {
                CountryBean bean = list.get(i);
    %>
            <tr>
    <td><%=bean.getCountryname()%></td>
             <%
                }
                }
     %>

public static Connection createConnection() { try { Class.forName("com.mysql.jdbc.Driver");

             conn=DriverManager.getConnection("jdbc:mysql://localhost:3309/ecom_db","root","root");

    }
    catch(Exception e)
    {
        e.printStackTrace();


    }
    return conn;
    }                                                                 Name of the country is successfully entered in to the database nothing wrong with the queries but the problem is it does not appear in to the list in table tag.list.size method returns only previously inserted countries not the new one.

I don't see a transaction commit, is your connection set to autocommit? — Taylor, Mar 14 '13 at 15:05
Don't mess up HTML with Java code. Please avoid this hateful [scriptlet](http://stackoverflow.com/q/3177733/1037210) which is **highly** discouraged and always use [PreparedStatements](http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html) to mitigate SQL injection attacks. — Lion, Mar 14 '13 at 15:09
OP posted the connection statement in my answer's comment section. Since no parameters in the connection string, it is set to auto-commit mode. — karmanaut, Mar 14 '13 at 15:09

score 1 · Answer 1 · edited May 23 '17 at 12:11

Your displaying record function looks terribly messed up. Probably not posted correctly. I assume you have created the connection properly by supplying the name and password for DB.

Class.forName("com.mysql.jdbc.Driver").newInstance();
        Connection conn = DriverManager
                .getConnection(
                        "jdbc:mysql://localhost:3306/Twitter?jdbcCompliantTruncation=false&amp;useServerPrepStmts=false&amp;rewriteBatchedStatements=true",
                        "root", "xxxx");

Your insert query looks wrong to me. Has a semicolon missing.

  String sqlInsert="insert into country(Country_Name) values('"+country+"');";
  String sqlselect="select * from country order by country_name;";

Post any exceptions that are being thrown.

Edit : Display record function corrected by OP. My misconception that ; is required. See this for more clarification.

public static  Connection createConnection()
    {
        try
        {
            Class.forName("com.mysql.jdbc.Driver");

    conn=DriverManager.getConnection("jdbc:mysql://localhost:33/ecom_db","root","root");

        }
        catch(Exception e)
        {
            e.printStackTrace();

        }
        return conn;
        }

public static Connection createConnection() { try { Class.forName("com.mysql.jdbc.Driver"); conn=DriverManager.getConnection("jdbc:mysql://localhost:3309/ecom_db","root","root"); } catch(Exception e) { e.printStackTrace(); } return conn; } Name of the country is successfully entered in to the database nothing wrong with the queries but the problem is it does not appear in to the list in table tag.list.size method returns only previously inserted countries not the new one. — user2169941, Mar 14 '13 at 13:39
Does running the same retrieval query in database work fine for you? — karmanaut, Mar 14 '13 at 14:58
yes if i write the same query in database it executes fine with the newly added country but it does not work in coding don't know why? — user2169941, Mar 14 '13 at 15:19

last insterted record does not reflect in select statement in mysql using JDBC

1 Answers1