How to get ID of inserted record in database ?

Some database operations, especially those involving parent-child relationships (one-to-many), you may want to insert a record into the parent table first, then into the child table. But the records in the child table must have a foreign key that refers to the parent table’s primary key.

In such case, you need a technique that retrieves the value of the primary key of the last record inserted (in case, when ID is auto-incremented by the database engine)

For MySQL and Java Derby database

String sql = "YOUR INSERT STATEMENT HERE";

PreparedStatement ps = 

      conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

ps.execute();

ResultSet rs = ps.getGeneratedKeys();

int generatedKey = 0;

if (rs.next()) {

   generatedKey = rs.getInt(1);

}

System.out.println("Inserted record's ID: " + generatedKey);

For Oracle database

PreparedStatement statement = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

statement.execute();

PreparedStatement ps = connection.prepareStatement(

          "select your_table_id_sequence.currval from dual");

ResultSet rs = ps.executeQuery();

if (rs.next()) {

   generatedKey = (int) rs.getLong(1);

}

System.out.println("Inserted record's ID: " + generatedKey);

Note : For Oracle database, you should create a sequence for the table’s primary key.

What are Driver libraries for common databases ?

An appropriate JDBC driver library is required in the application’s classpath in order to work with a database engine via JDBC.

A JDBC driver library consists of Java classes which implement low-level communication with the database engine.

It talks with Java applications via JDBC API and usually bundled as a JAR or ZIP file.

Driver libraries

MySQL (Oracle corp.) : mysql-connector-java-3.0.17-ga-bin.jar

SQL Server (Microsoft) : sqljdbc.jar, sqljdbc4.jar

Oracle : jodbc5.jar, jodbc6.jar, …

PostgreSQL (PostgreSQL Global Development Group) : postgresql-9.1-902.jdbc4.jar

Some drivers come as JAR files (Oracle, PostgreSQL), so you can add the JAR files directly to your application’s classpath.

Some drivers come as ZIP bundles (MySQL, SQL Server), so you have to extract the bundles and copy the appropriate JAR file (as specified above) to your application’s classpath.

Database connection URLs for common databases

When working with a database system via JDBC, the following information is required for making connection to the database :

• Driver class name
  Name of the class that implements java.sql.Driver interface. The JDBC’s driver manager needs to load this class in order to work with the database driver.
  
• Database URL
  A string that contains information about the database to connect to and other configuration properties. This string has its own format and is varied among different databases.

1. MySQL

Driver class name : com.mysql.jdbc.Driver

Format of database URL :  jdbc:mysql:// [host] [,failoverhost...] [:port] / [database]

                       [?propertyName1][=propertyValue1][&propertyName2][=propertyValue2]...

Examples : 

jdbc:mysql://localhost:3306/test

jdbc:mysql://localhost:3306/test?user=root&password=secret

2. SQL Server

Driver class name : com.microsoft.sqlserver.jdbc.SQLServerDriver

Format of database URL: jdbc:sqlserver:// [serverName [\instanceName] 

                                             [:portNumber] ] [;property=value[;property=value]]

Examples :

jdbc:sqlserver://localhost;integratedSecurity=true;

jdbc:sqlserver://localhost\\sqlexpress;integratedSecurity=true

jdbc:sqlserver://localhost\\sqlexpress;user=sa;password=secret

3. Oracle

Driver class name : oracle.jdbc.OracleDriver

Format of database URL : jdbc:oracle:<driver-type>:@<database>

                                             jdbc:oracle:<driver-type>:<user>/<password>@<database>

where driver-type can be thin, oci or kprb.

Examples :

jdbc:oracle:thin:@localhost:1521:testdb

jdbc:oracle:thin:root/secret@localhost:1521:testdb

jdbc:oracle:oci:@hoststring

jdbc:oracle:oci:@localhost:1521:testdb

jdbc:oracle:oci:root/secret@hoststring

jdbc:oracle:oci:root/secret@localhost:1521:testdb

4. PostgreSQL

Driver class name : org.postgresql.Driver

Format of database URL : jdbc:postgresql:<database>

                                             jdbc:postgresql://<host>/<database>

                                             jdbc:postgresql://<host>:<port>/<database>

Examples : 

jdbc:postgresql:testdb

jdbc:postgresql://localhost/testdb

jdbc:postgresql://localhost:5432/testdb

5. JavaDB (Apache Derby)

Driver class name : org.apache.derby.jdbc.EmbeddedDriver

Format of database URL : jdbc:derby: [subsubprotocol:] [databaseName][;attribute=value] *

where subsubprotocol can take one of the following values : directory (default), memory, classpath, and jar.

Examples : 

jdbc:derby:testdb

jdbc:derby:centraldb/sales

jdbc:derby:sample;create=true

jdbc:derby:memory:testdb

jdbc:derby:classpath:testdb

jdbc:derby:directory:testdb

jdbc:derby:jar(C:/testdb.jar)/market/asia

Note : From JDBC 4.0, loading the driver class explicitly is no longer needed, so you don’t need to care about the driver class name.

How to download images from database in JSP ?

1) Create a webpage "imageDownload.jsp"  to display and download the image from database. All images will show as hyperlink image.

2) Another  "image.jsp" is used to retrieve image.

Step 1. To create a "imageupload" table in Database

CREATE TABLE 'imageupload' (

  'id' bigint(20) NOT NULL auto_increment,

  'imagefile' blob NOT NULL,

  PRIMARY KEY ('id')

)

Step 2. To create a web page  "image.jsp" 

<%@ page import="java.sql.*,java.io.*,java.util.*" %>

<%

String url = "jdbc:mysql://localhost:3306/userdetails";

if(request.getParameter("imgid")!=null && request.getParameter("imgid")!="") {

  int id =  Integer.parseInt(request.getParameter("imgid"));

  String filename = "image"+id+".jpg";

  Connection con = null;

  try{     

    Class.forName("com.mysql.jdbc.Driver").newInstance();

    con = DriverManager.getConnection(url, "root", "root");     

    Statement st1 = con.createStatement();

    String strQuery = "select imagefile from imageupload where id="+id;

   

    ResultSet rs1 = st1.executeQuery(strQuery);

    String imgLen="";

    if(rs1.next()) {

      imgLen = rs1.getString(1);

     } 

   

    rs1 = st1.executeQuery(strQuery);

    if(rs1.next()) {

      int len = imgLen.length();

      byte [] rb = new byte[len];

      InputStream readImg = rs1.getBinaryStream(1);

      int index = readImg.read(rb, 0, len); 

      st1.close();

      response.reset();

      response.setContentType("image/jpg");

      response.setHeader("Content-disposition",

                           "attachment; filename=" +filename);

      response.getOutputStream().write(rb, 0, len);

      response.getOutputStream().flush();       

    }

  }

  catch (Exception e) {

    e.printStackTrace();

  }

}

%>

Step 3. To create a "imageDownload.jsp"

<%@ page import="java.sql.*,java.io.*,java.util.*" %>

<HTML>

 <HEAD>  <TITLE>Download Images</TITLE>  </HEAD>

 <BODY>

   <br><br>

  <table align="center" border=0 width="200px">

   <tr>

    <td colspan=2 align="center"><b>Download Images</b></td>

  </tr>

  <tr><td colspan=2>&nbsp;</td></tr>

  <%

  String url = "jdbc:mysql://localhost:3306/userdetails";

  Connection conn = null;

  try {     

    Class.forName("com.mysql.jdbc.Driver").newInstance();

    conn = DriverManager.getConnection(url, "root", "root");

    Statement stmt = conn.createStatement();

    String strQuery = "select id from imageupload";

   

    ResultSet rs = stmt.executeQuery(strQuery);

    int sno = 0;

    while(rs.next())

    {

      sno++;

%>

      <tr style="background-color:#efefef;" 

        mce_style="background-color:#efefef;" 

        mce_style="background-color:#efefef;">

      <td><b><%=sno%></b></td>

       <td align="center">

           <a href="image.jsp?imgid=<%=rs.getInt(1)%>" 

              mce_href="image.jsp?imgid=<%=rs.getInt(1)%>" 

              mce_href="image.jsp?imgid=<%=rs.getInt(1)%>">

           <img src="image.jsp?imgid=<%=rs.getInt(1)%>" 

              mce_src="image.jsp?imgid=<%=rs.getInt(1)%>" 

              mce_src="image.jsp?imgid=<%=rs.getInt(1)%>" 

              width="50" height="50">

         </a>

       </td>

      </tr>

<%

    }

    rs.close();

    con.close();

    stmt.close();

  }

  catch(Exception e)  {

    e.getMessage();

  }

%>

 </table>

</BODY>

</HTML>