Java Tutorial/Database/Preparedstatement

Содержание

1 Count Records using the Prepared Statement
2 Create a PreparedStatement object with two parameter markers
3 Create a Table Using PreparedStatement
4 DELETE data in a table
5 Deleting Records using the Prepared Statement
6 Inserting Records using the Prepared Statement
7 Modify data in a table
8 Prepared Statement Set Big Decimal
9 Prepared Statement With Batch Update
10 Rows affected when updating data in database table
11 Select Records Using Prepared Statement
12 Set byte, short and long data types by using the Prepared Statement
13 Set Date by using the Prepared Statement
14 Set NULL
15 Set string,ingeger,double and float example by using the Prepared Statement
16 Set the Number of Rows to Prefetch Using PreparedStatement
17 Set Time by using the Prepared Statement
18 Set Timestamp by using the Prepared Statement
19 Use PreparedStatement.setAsciiStream()
20 Use PreparedStatement.setBigDecimal()
21 Use PreparedStatement.setBinaryStream()
22 Use PreparedStatement.setBoolean()
23 Use PreparedStatement.setBytes()
24 Use PreparedStatement.setCharacterStream()
25 Use PreparedStatement.setURL()
26 Use PreparedStatement"s setByte(), setShort(), setInt(), and setLong()
27 Using the Prepared Statement Twice
28 Working with the Preparedstatement

Count Records using the Prepared Statement

   <source lang="java">

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();
 }

}</source>

Create a PreparedStatement object with two parameter markers

   <source lang="java">

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", "");
 }

}</source>

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

   <source lang="java">

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", "");
 }

}</source>

DELETE data in a table

   <source lang="java">

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();
 }

}</source>

Deleting Records using the Prepared Statement

   <source lang="java">

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();
 }

}</source>

Inserting Records using the Prepared Statement

   <source lang="java">

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();
 }

}</source>

Modify data in a table

   <source lang="java">

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();
 }

}</source>

Prepared Statement Set Big Decimal

   <source lang="java">

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();
 }

}</source>

Prepared Statement With Batch Update

   <source lang="java">

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();
 }

}</source>

Rows affected when updating data in database table

   <source lang="java">

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();
 }

}</source>

Select Records Using Prepared Statement

   <source lang="java">

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();
 }

}</source>

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

   <source lang="java">

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)");
 }

}</source>

Set Date by using the Prepared Statement

   <source lang="java">

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();
 }

}</source>

Set NULL

   <source lang="java">

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", "");
 }

}</source>

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

   <source lang="java">

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)");
 }

}</source>

Set the Number of Rows to Prefetch Using PreparedStatement

   <source lang="java">

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", "");
 }

}</source>

Set Time by using the Prepared Statement

   <source lang="java">

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)");
 }

}</source>

Set Timestamp by using the Prepared Statement

   <source lang="java">

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)");
 }

}</source>

Use PreparedStatement.setAsciiStream()

   <source lang="java">

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", "");
 }

}</source>

Use PreparedStatement.setBigDecimal()

   <source lang="java">

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", "");
 }

}</source>

1.00000

Use PreparedStatement.setBinaryStream()

   <source lang="java">

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", "");
 }

}</source>

Use PreparedStatement.setBoolean()

   <source lang="java">

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", "");
 }

}</source>

1
0

Use PreparedStatement.setBytes()

   <source lang="java">

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", "");
 }

}</source>

Use PreparedStatement.setCharacterStream()

   <source lang="java">

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", "");
 }

}</source>

Use PreparedStatement.setURL()

   <source lang="java">

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", "");
 }

}</source>

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

   <source lang="java">

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", "");
 }

}</source>

Using the Prepared Statement Twice

   <source lang="java">

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);
   }
 }

}</source>

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.

   <source lang="java">

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", "");
 }

}</source>

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

Java Tutorial/Database/Preparedstatement

Содержание

Count Records using the Prepared Statement

Create a PreparedStatement object with two parameter markers

Create a Table Using PreparedStatement

DELETE data in a table

Deleting Records using the Prepared Statement

Inserting Records using the Prepared Statement

Modify data in a table

Prepared Statement Set Big Decimal

Prepared Statement With Batch Update

Rows affected when updating data in database table

Select Records Using Prepared Statement

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

Set Date by using the Prepared Statement

Set NULL

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

Set the Number of Rows to Prefetch Using PreparedStatement

Set Time by using the Prepared Statement

Set Timestamp by using the Prepared Statement

Use PreparedStatement.setAsciiStream()

Use PreparedStatement.setBigDecimal()

Use PreparedStatement.setBinaryStream()

Use PreparedStatement.setBoolean()

Use PreparedStatement.setBytes()

Use PreparedStatement.setCharacterStream()

Use PreparedStatement.setURL()

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

Using the Prepared Statement Twice

Working with the Preparedstatement

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Навигация

Инструменты