Java Tutorial/Database/Preparedstatement

Count Records using the Prepared Statement

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class Main {
  public static void main(String[] argv) throws Exception {
    int records = 0;
    Class.forName("com.mysql.jdbc.Driver");
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial",
        "root", "root");
    String sql = "SELECT COUNT(*) FROM mytable" ;
    PreparedStatement prest = con.prepareStatement(sql);
    ResultSet rs = prest.executeQuery();
    while (rs.next()) {
      records = rs.getInt(1);
    }
    System.out.println("Number of records: " + records);
    con.close();
  }
}

Create a PreparedStatement object with two parameter markers

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    Statement st = conn
        .createStatement();
    st.executeUpdate("create table survey (id int, name VARCHAR(30) );");
    
    String INSERT_RECORD = "insert into survey(id, name) values(?,?)";
    
    PreparedStatement pstmt = conn.prepareStatement(INSERT_RECORD);
    pstmt.setString(1, "1");
    pstmt.setString(2, "name1");
    pstmt.executeUpdate();
    ResultSet rs = st.executeQuery("SELECT * FROM survey");
    outputResultSet(rs);
    
    pstmt.setString(1, "2");
    pstmt.setString(2, "name2");
    pstmt.executeUpdate();
    rs = st.executeQuery("SELECT * FROM survey");
    outputResultSet(rs);
    
    rs.close();
    st.close();
    conn.close();
  }
  private static void outputResultSet(ResultSet rs) throws Exception {
    ResultSetMetaData rsMetaData = rs.getMetaData();
    int numberOfColumns = rsMetaData.getColumnCount();
    for (int i = 1; i < numberOfColumns + 1; i++) {
      String columnName = rsMetaData.getColumnName(i);
      System.out.print(columnName + "   ");
    }
    System.out.println();
    System.out.println("----------------------");
    while (rs.next()) {
      for (int i = 1; i < numberOfColumns + 1; i++) {
        System.out.print(rs.getString(i) + "   ");
      }
      System.out.println();
    }
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

ID   NAME   
----------------------
1   name1   
ID   NAME   
----------------------
1   name1   
2   name2

When your SQL query does not have any parameters, you should use a Statement instead of a PreparedStatement object, unless you are going to use it many times.

Create a Table Using PreparedStatement

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLWarning;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    conn.setAutoCommit(false);
    Statement st = conn.createStatement();
    PreparedStatement pstmt = conn.prepareStatement("create table survey (id int, name VARCHAR(30) );");
    pstmt.executeUpdate();
    ResultSet rs = st.executeQuery("SELECT * FROM survey");
    outputResultSet(rs);
    rs.close();
    st.close();
    conn.close();
  }
  private static void outputResultSet(ResultSet rs) throws Exception {
    ResultSetMetaData rsMetaData = rs.getMetaData();
    int numberOfColumns = rsMetaData.getColumnCount();
    for (int i = 1; i < numberOfColumns + 1; i++) {
      String columnName = rsMetaData.getColumnName(i);
      System.out.print(columnName + "   ");
    }
    System.out.println();
    System.out.println("----------------------");
    while (rs.next()) {
      for (int i = 1; i < numberOfColumns + 1; i++) {
        System.out.print(rs.getString(i) + "   ");
      }
      System.out.println();
    }
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

DELETE data in a table

import java.sql.Connection;
import java.sql.PreparedStatement;
public class Main {
  public static void main(String[] argv) throws Exception {
    Connection con = null;
    PreparedStatement prepstmt;
    prepstmt = con.prepareStatement("DELETE FROM tCust " + " WHERE custId = ?");
    prepstmt.setString(1, "1");
    prepstmt.executeUpdate();
    prepstmt.close();
    con.close();
  }
}

Deleting Records using the Prepared Statement

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class Main {
  public static void main(String[] argv) throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial",
        "root", "root");
    String sql = "DELETE FROM product where year_made = ?";
    PreparedStatement prest = con.prepareStatement(sql);
    prest.setInt(1, 2008);
    int del = prest.executeUpdate();
    System.out.println("Number of deleted records: " + del);
    con.close();
  }
}

Inserting Records using the Prepared Statement

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class Main {
  public static void main(String[] argv) throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial", "root",
        "root");
    String sql = "INSERT product VALUES(?,?)";
    PreparedStatement prest = con.prepareStatement(sql);
    prest.setString(1, "asdf");
    prest.setInt(2, 2009);
    int count = prest.executeUpdate();
    System.out.println(count + "row(s) affected");
    con.close();
  }
}

Modify data in a table

import java.sql.Connection;
import java.sql.PreparedStatement;
public class Main {
  public static void main(String[] argv) throws Exception {
    Connection con = null;
    PreparedStatement prepstmt;
    prepstmt = con.prepareStatement("UPDATE employee SET Name = ? "
        + " WHERE Id = ?");
    prepstmt.setString(1, "Smith");
    prepstmt.setString(2, "1");
    prepstmt.executeUpdate();
    prepstmt.close();
    con.close();
  }
}

Prepared Statement Set Big Decimal

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class Main {
  public static void main(String[] argv) throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial",
        "root", "root");
    String sql = "INSERT bigdecimal VALUES(?,?)";
    PreparedStatement prest = con.prepareStatement(sql);
    prest.setString(1, "D");
    BigDecimal b = new BigDecimal("111111111111111111111111111111111");
    prest.setBigDecimal(2, b);
    prest.executeUpdate();
  }
}

Prepared Statement With Batch Update

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class Main {
  public static void main(String[] argv) throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial",
        "root", "root");
    con.setAutoCommit(false);
    String sql = "INSERT product VALUES(?,?)";
    PreparedStatement prest = con.prepareStatement(sql);
    prest.setString(1, "A");
    prest.setInt(2, 2002);
    prest.addBatch();
    prest.setString(1, "B");
    prest.setInt(2, 1998);
    prest.addBatch();
    prest.setString(1, "C");
    prest.setInt(2, 1980);
    prest.addBatch();
    prest.setString(1, "D");
    prest.setInt(2, 1975);
    prest.addBatch();
    int count[] = prest.executeBatch();
    con.rumit();
    con.close();
  }
}

Rows affected when updating data in database table

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class Main {
  public static void main(String[] args) throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/book", "root", "");
    PreparedStatement ps = connection.prepareStatement("UPDATE books SET title = ? WHERE id = ?");
    ps.setString(1, "Java");
    ps.setInt(2, 1);
    int rows = ps.executeUpdate();
    System.out.printf("%d row(s) updated!", rows);
    connection.close();
  }
}

Select Records Using Prepared Statement

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class Main {
  public static void main(String[] argv) throws Exception {
    int count = 0;
    Class.forName("com.mysql.jdbc.Driver");
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial",
        "root", "root");
    String sql = "SELECT title,year_made FROM product WHERE year_made >= ? AND year_made <= ?";
    PreparedStatement prest = con.prepareStatement(sql);
    prest.setInt(1, 2000);
    prest.setInt(2, 2009);
    ResultSet rs = prest.executeQuery();
    while (rs.next()) {
      String mov_name = rs.getString(1);
      int mov_year = rs.getInt(2);
      count++;
      System.out.println(mov_name + "\t" + "- " + mov_year);
    }
    System.out.println("Number of records: " + count);
    prest.close();
    con.close();
  }
}

Set byte, short and long data types by using the Prepared Statement

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class Main {
  public static void main(String[] argv) throws Exception {
    Connection con = null;
    Class.forName("com.mysql.jdbc.Driver");
    con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial", "root", "root");
    String sql = "INSERT datatypes VALUES(?,?,?)";
    PreparedStatement prest = con.prepareStatement(sql);
    prest.setByte(1, (byte) 5);
    prest.setShort(2, (short) 65);
    prest.setLong(3, (long) 254);
    int row = prest.executeUpdate();
    System.out.println(row + " row(s) affected)");
  }
}

Set Date by using the Prepared Statement

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class Main {
  public static void main(String[] argv) throws Exception {
    Date date = new Date(0);
    Class.forName("com.mysql.jdbc.Driver");
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial",
        "root", "root");
    PreparedStatement prest = con.prepareStatement("INSERT Records VALUES(?,?,?)");
    prest.setInt(1, 1);
    prest.setString(2, "R");
    prest.setDate(3, date.valueOf("1998-1-17"));
    int row = prest.executeUpdate();
  }
}

Set NULL

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    Statement st = conn
        .createStatement();
    st.executeUpdate("create table survey (id int,myDate DATE);");
    String INSERT_RECORD = "insert into survey(id, myDate) values(?, ?)";
    
    PreparedStatement pstmt = conn.prepareStatement(INSERT_RECORD);
    pstmt.setString(1, "1");
    pstmt.setNull(2, java.sql.Types.DATE);
    
    pstmt.executeUpdate();
    
    ResultSet rs = st.executeQuery("SELECT * FROM survey");
    outputResultSet(rs);
    rs.close();
    st.close();
    conn.close();
  }
  private static void outputResultSet(ResultSet rs) throws Exception {
    ResultSetMetaData rsMetaData = rs.getMetaData();
    int numberOfColumns = rsMetaData.getColumnCount();
    for (int i = 1; i < numberOfColumns + 1; i++) {
      String columnName = rsMetaData.getColumnName(i);
      System.out.print(columnName + "   ");
    }
    System.out.println();
    System.out.println("----------------------");
    while (rs.next()) {
      for (int i = 1; i < numberOfColumns + 1; i++) {
        System.out.print(rs.getString(i) + "   ");
      }
      System.out.println();
    }
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

Set string,ingeger,double and float example by using the Prepared Statement

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class Main {
  public static void main(String[] argv) throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial",
        "root", "root");
    String sql = "INSERT myTable VALUES(?,?,?,?)";
    PreparedStatement prest = con.prepareStatement(sql);
    prest.setString(1, "A");
    prest.setInt(2, 5);
    prest.setDouble(3, 2.0);
    prest.setFloat(4, 4.2f);
    int row = prest.executeUpdate();
    System.out.println(row + " row(s) affected)");
  }
}

Set the Number of Rows to Prefetch Using PreparedStatement

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLWarning;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    conn.setAutoCommit(false);
    Statement st = conn.createStatement();
    st.executeUpdate("create table survey (id int, name VARCHAR(30) );");
    String INSERT_RECORD = "select * from survey where id < ?";
    PreparedStatement pstmt = conn.prepareStatement(INSERT_RECORD);
    pstmt.setInt(1, 1);
    
    pstmt.setFetchSize(200);    
    
    ResultSet rs = pstmt.executeQuery();
    outputResultSet(rs);
    rs.close();
    st.close();
    conn.close();
  }
  private static void outputResultSet(ResultSet rs) throws Exception {
    ResultSetMetaData rsMetaData = rs.getMetaData();
    int numberOfColumns = rsMetaData.getColumnCount();
    for (int i = 1; i < numberOfColumns + 1; i++) {
      String columnName = rsMetaData.getColumnName(i);
      System.out.print(columnName + "   ");
    }
    System.out.println();
    System.out.println("----------------------");
    while (rs.next()) {
      for (int i = 1; i < numberOfColumns + 1; i++) {
        System.out.print(rs.getString(i) + "   ");
      }
      System.out.println();
    }
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

Set Time by using the Prepared Statement

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Time;
public class Main {
  public static void main(String[] argv) throws Exception {
    Time time = new Time(0);
    Class.forName("com.mysql.jdbc.Driver");
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial",
        "root", "root");
    String sql = "INSERT child VALUES(?,?)";
    PreparedStatement prest = con.prepareStatement(sql);
    prest.setString(1, "vinod");
    prest.setTime(2, time.valueOf("1:60:60"));
    int row = prest.executeUpdate();
    System.out.println(row + " row(s) affectec)");
  }
}

Set Timestamp by using the Prepared Statement

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
public class Main {
  public static void main(String[] argv) throws Exception {
    Timestamp tstamp = new Timestamp(0);
    Class.forName("com.mysql.jdbc.Driver");
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial",
        "root", "root");
    String sql = "INSERT myTable VALUES(?,?)";
    PreparedStatement prest = con.prepareStatement(sql);
    prest.setString(1, "x");
    prest.setTimestamp(2, tstamp.valueOf("2009-02-24 12:51:42.11"));
    int row = prest.executeUpdate();
    System.out.println(row + " row(s) affected)");
  }
}

Use PreparedStatement.setAsciiStream()

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    Statement stmt = conn.createStatement();

    stmt.executeUpdate("create table survey (id int, name BINARY );");
    String sql = "INSERT INTO survey (name) VALUES(?)";
    PreparedStatement pstmt = conn.prepareStatement(sql);
    // prepare text stream
    File file = new File("yourFileName.txt");
    int fileLength = (int) file.length();
    InputStream stream = (InputStream) new FileInputStream(file);
    pstmt.setString(1, "001");
    pstmt.setAsciiStream(2, stream, fileLength);
    // insert the data
    pstmt.executeUpdate();
    
    ResultSet rs = stmt.executeQuery("SELECT * FROM survey");
    while (rs.next()) {
        System.out.print(new String(rs.getBytes(2)));
    }
    
    
    rs.close();
    stmt.close();
    conn.close();
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

Use PreparedStatement.setBigDecimal()

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    Statement stmt = conn.createStatement();

    stmt.executeUpdate("create table survey (id DECIMAL, name BINARY );");
    String sql = "INSERT INTO survey (id) VALUES(?)";
    PreparedStatement pstmt = conn.prepareStatement(sql);
    pstmt.setBigDecimal(1, new BigDecimal("1.00000"));
    // insert the data
    pstmt.executeUpdate();
    
    ResultSet rs = stmt.executeQuery("SELECT * FROM survey");
    while (rs.next()) {
        System.out.print(rs.getString(1));
    }
    
    
    rs.close();
    stmt.close();
    conn.close();
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

1.00000

Use PreparedStatement.setBinaryStream()

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    Statement stmt = conn.createStatement();

    stmt.executeUpdate("create table survey (id int, name BINARY );");
    String sql = "INSERT INTO survey (name) VALUES(?)";
    PreparedStatement pstmt = conn.prepareStatement(sql);
    // prepare small binary stream
    File smallFile = new File("yourFileName.txt");
    int smallFileLength = (int) smallFile.length();
    InputStream smallStream = (InputStream) new FileInputStream(smallFile);
    pstmt.setBinaryStream(2, smallStream, smallFileLength);
    
    // insert the data
    pstmt.executeUpdate();
    
    ResultSet rs = stmt.executeQuery("SELECT * FROM survey");
    while (rs.next()) {
        System.out.print(rs.getString(1));
    }
    
    
    rs.close();
    stmt.close();
    conn.close();
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

Use PreparedStatement.setBoolean()

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    Statement stmt = conn.createStatement();

    stmt.executeUpdate("create table survey (id int, register int );");
    String sql = "INSERT INTO survey (register) VALUES(?)";
    PreparedStatement pstmt = conn.prepareStatement(sql);
    pstmt.setBoolean(1, true);
    pstmt.executeUpdate();
    pstmt.setBoolean(1, false);
    pstmt.executeUpdate();
    
    ResultSet rs = stmt.executeQuery("SELECT * FROM survey");
    while (rs.next()) {
        System.out.println(rs.getString(2));
    }
    
    
    rs.close();
    stmt.close();
    conn.close();
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

1
0

Use PreparedStatement.setBytes()

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    Statement stmt = conn.createStatement();
    stmt.executeUpdate("create table survey (id int, name BINARY);");
    String sql = "INSERT INTO survey (name) VALUES(?)";
    PreparedStatement pstmt = conn.prepareStatement(sql);
    pstmt.setBytes(1, "asdfasdf".getBytes());
    pstmt.executeUpdate();
    ResultSet rs = stmt.executeQuery("SELECT * FROM survey");
    while (rs.next()) {
      System.out.println(rs.getBytes(2));
    }
    rs.close();
    stmt.close();
    conn.close();
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

Use PreparedStatement.setCharacterStream()

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    Statement stmt = conn.createStatement();
    stmt.executeUpdate("create table survey (id int, name BINARY);");
    String sql = "INSERT INTO survey (name) VALUES(?)";
    PreparedStatement pstmt = conn.prepareStatement(sql);
    
    File file = new File("yourFileName.txt");
    long fileLength = file.length();
    Reader fileReader = (Reader) new BufferedReader(new FileReader(file));
    pstmt.setCharacterStream(1, fileReader, (int)fileLength);
    int rowCount = pstmt.executeUpdate();
    ResultSet rs = stmt.executeQuery("SELECT * FROM survey");
    while (rs.next()) {
      System.out.println(rs.getBytes(2));
    }
    rs.close();
    stmt.close();
    conn.close();
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

Use PreparedStatement.setURL()

import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    Statement st = conn
        .createStatement();
    st.executeUpdate("create table survey (id int,myURL CHAR);");
    String INSERT_RECORD = "insert into survey(id, myURL) values(?, ?)";
    
    PreparedStatement pstmt = conn.prepareStatement(INSERT_RECORD);
    pstmt.setString(1, "1");
    pstmt.setURL(2, new URL("http://www.jexp.ru"));
    
    pstmt.executeUpdate();
    
    ResultSet rs = st.executeQuery("SELECT * FROM survey");
    outputResultSet(rs);
    rs.close();
    st.close();
    conn.close();
  }
  private static void outputResultSet(ResultSet rs) throws Exception {
    ResultSetMetaData rsMetaData = rs.getMetaData();
    int numberOfColumns = rsMetaData.getColumnCount();
    for (int i = 1; i < numberOfColumns + 1; i++) {
      String columnName = rsMetaData.getColumnName(i);
      System.out.print(columnName + "   ");
    }
    System.out.println();
    System.out.println("----------------------");
    while (rs.next()) {
      for (int i = 1; i < numberOfColumns + 1; i++) {
        System.out.print(rs.getString(i) + "   ");
      }
      System.out.println();
    }
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

Use PreparedStatement"s setByte(), setShort(), setInt(), and setLong()

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    Statement stmt = conn.createStatement();

    stmt.executeUpdate("create table survey (id int, id2 tinyint, id3 smallint, id4 bigint, id5 real);");
    String sql = "INSERT INTO survey (id2,id3,id4,id5) VALUES(?,?,?,?)";
    PreparedStatement pstmt = conn.prepareStatement(sql);
    byte b = 1;
    short s = 2;
    pstmt.setByte(1, b);
    pstmt.setShort(2, s);
    pstmt.setInt(3, 3);
    pstmt.setLong(4, 4L);
    pstmt.executeUpdate();
    ResultSet rs = stmt.executeQuery("SELECT * FROM survey");
    while (rs.next()) {
        System.out.println(rs.getString(2));
    }
    
    
    rs.close();
    stmt.close();
    conn.close();
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

Using the Prepared Statement Twice

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class Main {
  public static void main(String[] argv) throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial",
        "root", "root");
    String sql = "SELECT * FROM product WHERE year_made = ?";
    PreparedStatement prest = con.prepareStatement(sql);
    prest.setInt(1, 2002);
    ResultSet rs1 = prest.executeQuery();
    while (rs1.next()) {
      String mov_name = rs1.getString(1);
      int mad_year = rs1.getInt(2);
      System.out.println(mov_name + "\t- " + mad_year);
    }
    prest.setInt(1, 2003);
    ResultSet rs2 = prest.executeQuery();
    while (rs2.next()) {
      String mov_name = rs2.getString(1);
      int mad_year = rs2.getInt(2);
      System.out.println(mov_name + "\t- " + mad_year);
    }
  }
}

Working with the Preparedstatement

When your SQL query is parameterized, you should use a PreparedStatement object. A PreparedStatement object enables you to pass input parameters to the SQL statement before sending it to the database server for execution.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    Statement st = conn
        .createStatement();
    st.executeUpdate("create table survey (id int, myDate TIMESTAMP );");
    
    String INSERT_RECORD = "insert into survey(id) values(?)";
    
    PreparedStatement pstmt = conn.prepareStatement(INSERT_RECORD);
    pstmt.setString(1, "1");
    pstmt.executeUpdate();
    ResultSet rs = st.executeQuery("SELECT * FROM survey");
    outputResultSet(rs);
    
    pstmt.setString(1, "2");
    pstmt.executeUpdate();
    rs = st.executeQuery("SELECT * FROM survey");
    outputResultSet(rs);
    
    rs.close();
    st.close();
    conn.close();
  }
  private static void outputResultSet(ResultSet rs) throws Exception {
    ResultSetMetaData rsMetaData = rs.getMetaData();
    int numberOfColumns = rsMetaData.getColumnCount();
    for (int i = 1; i < numberOfColumns + 1; i++) {
      String columnName = rsMetaData.getColumnName(i);
      System.out.print(columnName + "   ");
    }
    System.out.println();
    System.out.println("----------------------");
    while (rs.next()) {
      for (int i = 1; i < numberOfColumns + 1; i++) {
        System.out.print(rs.getString(i) + "   ");
      }
      System.out.println();
    }
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

ID   MYDATE   
----------------------
1   null   
ID   MYDATE   
----------------------
1   null   
2   null