Java/Database SQL JDBC/PreparedStatement
Содержание
- 1 Count Records Using PreparedStatement
- 2 Count Records using the Prepared Statement
- 3 DELETE data in a table
- 4 Delete Records Using PreparedStatement
- 5 Deleting Records using the Prepared Statement
- 6 Demo Prepared Statement Set BigDecimal
- 7 Demo Prepared Statement Set Blob
- 8 Demo PreparedStatement Set Boolean
- 9 Demo PreparedStatement Set Byte
- 10 Demo PreparedStatement Set Bytes
- 11 Demo PreparedStatement Set Clob
- 12 Demo PreparedStatement Set Date
- 13 Demo PreparedStatement Set Float And Double
- 14 Demo PreparedStatement Set Integers
- 15 Demo PreparedStatement Set Long
- 16 Demo PreparedStatement Set Null for char/string column
- 17 Demo PreparedStatement Set Null for int value column
- 18 Demo PreparedStatement Set Reference
- 19 Demo PreparedStatement Set Short
- 20 Demo PreparedStatement Set String
- 21 Demo PreparedStatement Set Time
- 22 Demo PreparedStatement Set Timestamp
- 23 Demo PreparedStatement Set URL
- 24 Inserting Records using the Prepared Statement
- 25 Inserting with a prepared statement that uses the various setXXX() methods.
- 26 Insert Records Using PreparedStatement
- 27 Modify data in a table
- 28 Prepared Statement Batch Update
- 29 PreparedStatement Set Array
- 30 PreparedStatement Set Object
- 31 Rows affected when updating data in database table
- 32 SELECT data from a table
- 33 Select Records Using PreparedStatement
- 34 Update Records Using PreparedStatement
- 35 Use PreparedStatement Twice
- 36 Using the Prepared Statement Twice
Count Records Using PreparedStatement
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class CountRecordsUsingPreparedStatement {
public static Connection getConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
String username = "name";
String password = "password";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static void main(String[] args) {
ResultSet rs = null;
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = getConnection();
String query = "select count(*) from tableName";
pstmt = conn.prepareStatement(query);
rs = pstmt.executeQuery();
if (rs.next()) {
int numberOfRows = rs.getInt(1);
System.out.println("numberOfRows= " + numberOfRows);
} else {
System.out.println("error: could not get the record counts");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
rs.close();
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
</source>
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>
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>
Delete Records Using PreparedStatement
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class DeleteRecordsUsingPreparedStatement {
public static Connection getConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
String username = "name";
String password = "password";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static void main(String[] args)throws Exception {
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = getConnection();
String query = "delete from tableName";
pstmt = conn.prepareStatement(query);
pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
pstmt.close();
conn.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>
Demo Prepared Statement Set BigDecimal
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class DemoPreparedStatementSetBigDecimal {
public static Connection getConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
String username = "name";
String password = "password";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static void main(String[] args) throws Exception {
Connection conn = null;
PreparedStatement pstmt = null;
String query = null;
try {
conn = getConnection();
query = "insert into BIG_DECIMAL_TABLE(id, big_decimal) values(?, ?)";
pstmt = conn.prepareStatement(query);
pstmt.setString(1, "001");
pstmt.setBigDecimal(2, new java.math.BigDecimal("123456789"));
// execute query, and return number of rows created
int rowCount = pstmt.executeUpdate();
System.out.println("rowCount=" + rowCount);
} catch (Exception e) {
e.printStackTrace();
} finally {
pstmt.close();
conn.close();
}
}
}
</source>
Demo Prepared Statement Set Blob
<source lang="java">
/* Defining the Table: Oracle and MySql create table MyPictures (
id INT PRIMARY KEY, name VARCHAR(0), photo BLOB
);
- /
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class DemoPreparedStatementSetBlob {
public static Connection getConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/databaseName";
String username = "root";
String password = "root";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static void main(String[] args)throws Exception {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
java.sql.Blob blob = null;
try {
conn = getConnection();
// prepare blob object from an existing binary column
pstmt = conn.prepareStatement("select photo from my_pictures where id = ?");
pstmt.setString(1, "0001");
rs = pstmt.executeQuery();
rs.next();
blob = rs.getBlob(1);
// prepare SQL query for inserting a new row using setBlob()
String query = "insert into blob_table(id, blob_column) values(?, ?)";
// begin transaction
conn.setAutoCommit(false);
pstmt = conn.prepareStatement(query);
pstmt.setString(1, "0002");
pstmt.setBlob(2, blob);
int rowCount = pstmt.executeUpdate();
System.out.println("rowCount=" + rowCount);
// end transaction
conn.rumit();
} finally {
rs.close();
pstmt.close();
conn.close();
}
}
}
</source>
Demo PreparedStatement Set Boolean
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class DemoPreparedStatementSetBoolean {
public static Connection getConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/databaseName";
String username = "root";
String password = "root";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static void main(String[] args) throws Exception {
boolean booleanValue = true;
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = getConnection();
String query = "insert into boolean_table(id, boolean_column) values(?, ?)";
pstmt = conn.prepareStatement(query);
pstmt.setString(1, "0001");
pstmt.setBoolean(2, booleanValue);
int rowCount = pstmt.executeUpdate();
System.out.println("rowCount=" + rowCount);
} finally {
pstmt.close();
conn.close();
}
}
}
</source>
Demo PreparedStatement Set Byte
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class DemoPreparedStatementSetIntegers {
public static Connection getConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/databaseName";
String username = "root";
String password = "root";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static void main(String[] args) throws Exception {
String id = "0001";
byte byteValue = 1;
short shortValue = 1;
int intValue = 12345;
long longValue = 100000000L;
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = getConnection();
String query = "insert into integer_table(id, byte_column, "
+ "short_column, int_column, long_column) values(?, ?, ?, ?, ?)";
// create PrepareStatement object
pstmt = conn.prepareStatement(query);
pstmt.setString(1, id);
pstmt.setByte(2, byteValue);
pstmt.setShort(3, shortValue);
pstmt.setInt(4, intValue);
pstmt.setLong(5, longValue);
// execute query, and return number of rows created
int rowCount = pstmt.executeUpdate();
System.out.println("rowCount=" + rowCount);
} finally {
pstmt.close();
conn.close();
}
}
}
</source>
Demo PreparedStatement Set Bytes
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class DemoPreparedStatementSetBytes {
public static Connection getConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/databaseName";
String username = "root";
String password = "root";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static void main(String[] args) throws Exception {
byte[] shortData = "www.jexp.ru".getBytes();
byte[] longData = "www.jexp.ru".getBytes();
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = getConnection();
String query = "insert into bytes_table (id, short_data, long_data) values(?, ?, ?)";
pstmt = conn.prepareStatement(query);
pstmt.setString(1, "0001");
pstmt.setBytes(2, shortData);
pstmt.setBytes(3, longData);
int rowCount = pstmt.executeUpdate();
System.out.println("rowCount=" + rowCount);
} finally {
pstmt.close();
conn.close();
}
}
}
</source>
Demo PreparedStatement Set Clob
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; public class DemoPreparedStatementSetClob {
public static Connection getConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/databaseName";
String username = "root";
String password = "root";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static void main(String[] args)throws Exception {
String id = "0001";
String newID = "0002";
ResultSet rs = null;
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = getConnection();
// begin transaction
conn.setAutoCommit(false);
String query1 = "select clob_column from clob_table where id = ?";
pstmt = conn.prepareStatement(query1);
pstmt.setString(1, id);
rs = pstmt.executeQuery();
rs.next();
java.sql.Clob clob = (java.sql.Clob) rs.getObject(1);
String query = "insert into clob_table(id, clob_column) values(?, ?)";
pstmt = conn.prepareStatement(query);
pstmt.setString(1, newID);
pstmt.setClob(2, clob);
// execute query, and return number of rows created
int rowCount = pstmt.executeUpdate();
System.out.println("rowCount=" + rowCount);
// end transaction
conn.rumit();
} finally {
rs.close();
pstmt.close();
conn.close();
}
}
}
</source>
Demo PreparedStatement Set Date
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class DemoPreparedStatementSetDate {
public static java.sql.Date getCurrentJavaSqlDate() {
java.util.Date today = new java.util.Date();
return new java.sql.Date(today.getTime());
}
public static Connection getConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/databaseName";
String username = "root";
String password = "root";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static void main(String[] args) throws Exception {
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = getConnection();
String query = "insert into date_table(id, date_column) values(?, ?)";
pstmt = conn.prepareStatement(query);
pstmt.setString(1, "0001");
java.sql.Date date = getCurrentJavaSqlDate();
pstmt.setDate(2, date);
// execute query, and return number of rows created
int rowCount = pstmt.executeUpdate();
System.out.println("rowCount=" + rowCount);
} finally {
pstmt.close();
conn.close();
}
}
}
</source>
Demo PreparedStatement Set Float And Double
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class DemoPreparedStatementSetFloatAndDouble {
public static Connection getConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/databaseName";
String username = "root";
String password = "root";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static void main(String[] args) throws Exception {
String id = "0001";
float floatValue = 0001f;
double doubleValue = 1.0001d;
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = getConnection();
String query = "insert into double_table(id, float_column, double_column) values(?, ?, ?)";
pstmt = conn.prepareStatement(query);
pstmt.setString(1, id);
pstmt.setFloat(2, floatValue);
pstmt.setDouble(3, doubleValue);
// execute query, and return number of rows created
int rowCount = pstmt.executeUpdate();
System.out.println("rowCount=" + rowCount);
} finally {
pstmt.close();
conn.close();
}
}
}
</source>
Demo PreparedStatement Set Integers
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class DemoPreparedStatementSetIntegers {
public static Connection getConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/databaseName";
String username = "root";
String password = "root";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static void main(String[] args) throws Exception {
String id = "0001";
byte byteValue = 1;
short shortValue = 1;
int intValue = 12345;
long longValue = 100000000L;
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = getConnection();
String query = "insert into integer_table(id, byte_column, "
+ "short_column, int_column, long_column) values(?, ?, ?, ?, ?)";
// create PrepareStatement object
pstmt = conn.prepareStatement(query);
pstmt.setString(1, id);
pstmt.setByte(2, byteValue);
pstmt.setShort(3, shortValue);
pstmt.setInt(4, intValue);
pstmt.setLong(5, longValue);
// execute query, and return number of rows created
int rowCount = pstmt.executeUpdate();
System.out.println("rowCount=" + rowCount);
} finally {
pstmt.close();
conn.close();
}
}
}
</source>
Demo PreparedStatement Set Long
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class DemoPreparedStatementSetIntegers {
public static Connection getConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/databaseName";
String username = "root";
String password = "root";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static void main(String[] args) throws Exception {
String id = "0001";
byte byteValue = 1;
short shortValue = 1;
int intValue = 12345;
long longValue = 100000000L;
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = getConnection();
String query = "insert into integer_table(id, byte_column, "
+ "short_column, int_column, long_column) values(?, ?, ?, ?, ?)";
// create PrepareStatement object
pstmt = conn.prepareStatement(query);
pstmt.setString(1, id);
pstmt.setByte(2, byteValue);
pstmt.setShort(3, shortValue);
pstmt.setInt(4, intValue);
pstmt.setLong(5, longValue);
// execute query, and return number of rows created
int rowCount = pstmt.executeUpdate();
System.out.println("rowCount=" + rowCount);
} finally {
pstmt.close();
conn.close();
}
}
}
</source>
Demo PreparedStatement Set Null for char/string column
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class DemoPreparedStatementSetNull {
public static Connection getConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/databaseName";
String username = "root";
String password = "root";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static void main(String[] args) throws Exception {
String id = "0001";
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = getConnection();
String query = "insert into nullable_table(id,string_column, int_column) values(?, ?, ?)";
// create PrepareStatement object
pstmt = conn.prepareStatement(query);
pstmt.setString(1, id);
pstmt.setNull(2, java.sql.Types.VARCHAR);
pstmt.setNull(3, java.sql.Types.INTEGER);
// execute query, and return number of rows created
int rowCount = pstmt.executeUpdate();
System.out.println("rowCount=" + rowCount);
} finally {
pstmt.close();
conn.close();
}
}
}
</source>
Demo PreparedStatement Set Null for int value column
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class DemoPreparedStatementSetNull {
public static Connection getConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/databaseName";
String username = "root";
String password = "root";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static void main(String[] args) throws Exception {
String id = "0001";
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = getConnection();
String query = "insert into nullable_table(id,string_column, int_column) values(?, ?, ?)";
// create PrepareStatement object
pstmt = conn.prepareStatement(query);
pstmt.setString(1, id);
pstmt.setNull(2, java.sql.Types.VARCHAR);
pstmt.setNull(3, java.sql.Types.INTEGER);
// execute query, and return number of rows created
int rowCount = pstmt.executeUpdate();
System.out.println("rowCount=" + rowCount);
} finally {
pstmt.close();
conn.close();
}
}
}
</source>
Demo PreparedStatement Set Reference
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; public class DemoPreparedStatementSetRef {
public static Connection getConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/databaseName";
String username = "root";
String password = "root";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static void main(String[] args) throws Exception {
String deptName = "oldName";
String newDeptName = "newName";
ResultSet rs = null;
Connection conn = null;
PreparedStatement pstmt = null;
PreparedStatement pstmt2 = null;
try {
conn = getConnection();
// prepare query for getting a REF object and PrepareStatement object
String refQuery = "select manager from dept_table where dept_name=?";
pstmt = conn.prepareStatement(refQuery);
pstmt.setString(1, deptName);
rs = pstmt.executeQuery();
java.sql.Ref ref = null;
if (rs.next()) {
ref = rs.getRef(1);
}
if (ref == null) {
System.out.println("error: could not get a reference for manager.");
System.exit(1);
}
String query = "INSERT INTO dept_table(dept_name, manager)values(?, ?)";
pstmt2 = conn.prepareStatement(query);
pstmt2.setString(1, newDeptName);
pstmt2.setRef(2, ref);
// execute query, and return number of rows created
int rowCount = pstmt2.executeUpdate();
System.out.println("rowCount=" + rowCount);
} finally {
pstmt.close();
pstmt2.close();
conn.close();
}
}
}
</source>
Demo PreparedStatement Set Short
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class DemoPreparedStatementSetIntegers {
public static Connection getConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/databaseName";
String username = "root";
String password = "root";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static void main(String[] args) throws Exception {
String id = "0001";
byte byteValue = 1;
short shortValue = 1;
int intValue = 12345;
long longValue = 100000000L;
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = getConnection();
String query = "insert into integer_table(id, byte_column, "
+ "short_column, int_column, long_column) values(?, ?, ?, ?, ?)";
// create PrepareStatement object
pstmt = conn.prepareStatement(query);
pstmt.setString(1, id);
pstmt.setByte(2, byteValue);
pstmt.setShort(3, shortValue);
pstmt.setInt(4, intValue);
pstmt.setLong(5, longValue);
// execute query, and return number of rows created
int rowCount = pstmt.executeUpdate();
System.out.println("rowCount=" + rowCount);
} finally {
pstmt.close();
conn.close();
}
}
}
</source>
Demo PreparedStatement Set String
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class DemoPreparedStatementSetString {
public static Connection getConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/databaseName";
String username = "root";
String password = "root";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static void main(String[] args) throws Exception {
String stringValue = "stringValueToBeInserted";
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = getConnection();
String query = "insert into string_table(string_column) values(?)";
pstmt = conn.prepareStatement(query);
pstmt.setString(1, stringValue);
// execute query, and return number of rows created
int rowCount = pstmt.executeUpdate();
System.out.println("rowCount=" + rowCount);
} finally {
pstmt.close();
conn.close();
}
}
}
</source>
Demo PreparedStatement Set Time
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class DemoPreparedStatementSetTimeAndTimestamp {
public static Connection getConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/databaseName";
String username = "root";
String password = "root";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static java.sql.Timestamp getCurrentJavaSqlTimestamp() {
java.util.Date date = new java.util.Date();
return new java.sql.Timestamp(date.getTime());
}
public static java.sql.Time getCurrentJavaSqlTime() {
java.util.Date date = new java.util.Date();
return new java.sql.Time(date.getTime());
}
public static void main(String[] args) throws Exception {
String id = "0001";
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = getConnection();
String query = "insert into time_table(id,time_column, timestamp_column) values(?, ?, ?)";
pstmt = conn.prepareStatement(query);
pstmt.setString(1, id);
java.sql.Time time = getCurrentJavaSqlTime();
System.out.println("time=" + time);
pstmt.setTime(2, time);
java.sql.Timestamp timestamp = getCurrentJavaSqlTimestamp();
System.out.println("timestamp=" + timestamp);
pstmt.setTimestamp(3, timestamp);
// execute query, and return number of rows created
int rowCount = pstmt.executeUpdate();
System.out.println("rowCount=" + rowCount);
} finally {
pstmt.close();
conn.close();
}
}
}
</source>
Demo PreparedStatement Set Timestamp
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class DemoPreparedStatementSetTimeAndTimestamp {
public static Connection getConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/databaseName";
String username = "root";
String password = "root";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static java.sql.Timestamp getCurrentJavaSqlTimestamp() {
java.util.Date date = new java.util.Date();
return new java.sql.Timestamp(date.getTime());
}
public static java.sql.Time getCurrentJavaSqlTime() {
java.util.Date date = new java.util.Date();
return new java.sql.Time(date.getTime());
}
public static void main(String[] args) throws Exception {
String id = "0001";
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = getConnection();
String query = "insert into time_table(id,time_column, timestamp_column) values(?, ?, ?)";
pstmt = conn.prepareStatement(query);
pstmt.setString(1, id);
java.sql.Time time = getCurrentJavaSqlTime();
System.out.println("time=" + time);
pstmt.setTime(2, time);
java.sql.Timestamp timestamp = getCurrentJavaSqlTimestamp();
System.out.println("timestamp=" + timestamp);
pstmt.setTimestamp(3, timestamp);
// execute query, and return number of rows created
int rowCount = pstmt.executeUpdate();
System.out.println("rowCount=" + rowCount);
} finally {
pstmt.close();
conn.close();
}
}
}
</source>
Demo PreparedStatement Set URL
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class DemoPreparedStatementSetURL {
public static Connection getConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/databaseName";
String username = "root";
String password = "root";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static void main(String[] args) throws Exception {
String id = "0001";
String urlValue = "http://www.jexp.ru";
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = getConnection();
String query = "insert into url_table(id, url) values(?, ?)";
pstmt = conn.prepareStatement(query);
pstmt.setString(1, id);
pstmt.setURL(2, new java.net.URL(urlValue));
// execute query, and return number of rows created
int rowCount = pstmt.executeUpdate();
System.out.println("rowCount=" + rowCount);
} finally {
pstmt.close();
conn.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>
Inserting with a prepared statement that uses the various setXXX() methods.
<source lang="java">
import java.io.File; import java.io.FileInputStream; import java.math.BigDecimal; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.Time; import java.sql.Timestamp; public class Main {
public static void main(String[] argv) throws Exception {
String driverName = "com.jnetdirect.jsql.JSQLDriver";
Class.forName(driverName);
String serverName = "127.0.0.1";
String portNumber = "1433";
String mydatabase = serverName + ":" + portNumber;
String url = "jdbc:JSQLConnect://" + mydatabase;
String username = "username";
String password = "password";
Connection connection = DriverManager.getConnection(url, username, password);
String sql = "INSERT INTO mysql_all_table("
+ "col_boolean,"
+ "col_byte,"
+ "col_short,"
+ "col_int,"
+ "col_long,"
+ "col_float,"
+ "col_double,"
+ "col_bigdecimal,"
+ "col_string,"
+ "col_date,"
+ "col_time,"
+ "col_timestamp,"
+ "col_asciistream,"
+ "col_binarystream,"
+ "col_blob) "
+ "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setBoolean(1, true);
pstmt.setByte(2, (byte)123);
pstmt.setShort(3, (short)123);
pstmt.setInt(4, 123);
pstmt.setLong(5, 123L);
pstmt.setFloat(6, 1.23F);
pstmt.setDouble(7, 1.23D);
pstmt.setBigDecimal(8, new BigDecimal(1.23));
pstmt.setString(9, "a string");
pstmt.setDate(10, new java.sql.Date(System.currentTimeMillis()));
pstmt.setTime(11, new Time(System.currentTimeMillis()));
pstmt.setTimestamp(12, new Timestamp(System.currentTimeMillis()));
File file = new File("infilename1");
FileInputStream is = new FileInputStream(file);
pstmt.setAsciiStream(13, is, (int)file.length());
file = new File("infilename2");
is = new FileInputStream(file);
pstmt.setBinaryStream(14, is, (int)file.length());
file = new File("infilename3");
is = new FileInputStream(file);
pstmt.setBinaryStream(15, is, (int)file.length());
pstmt.executeUpdate();
}
}
</source>
Insert Records Using PreparedStatement
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class InsertRecordsUsingPreparedStatement {
public static Connection getConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
String username = "name";
String password = "password";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static void main(String[] args) throws Exception {
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = getConnection();
String query = "insert into dept(deptnum, deptname, deptloc) values(?, ?, ?)";
pstmt = conn.prepareStatement(query); // create a statement
pstmt.setInt(1, 1); // set input parameter 1
pstmt.setString(2, "deptname"); // set input parameter 2
pstmt.setString(3, "deptLocation"); // set input parameter 3
pstmt.executeUpdate(); // execute insert statement
} catch (Exception e) {
e.printStackTrace();
} finally {
pstmt.close();
conn.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 Batch Update
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; public class MainClass {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement statement = null;
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
String url = "jdbc:mysql://localhost/database";
connection = DriverManager.getConnection(url, "username", "password");
String sql = "UPDATE employees SET email = ? WHERE employee_id = ?";
statement = connection.prepareStatement(sql);
statement.setString(1, "a@a.ru");
statement.setLong(2, 1);
statement.addBatch();
statement.setString(1, "b@b.ru");
statement.setLong(2, 2);
statement.addBatch();
statement.setString(1, "c@c.ru");
statement.setLong(2, 3);
statement.addBatch();
statement.executeBatch();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
} // nothing we can do
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
} // nothing we can do
}
}
}
}
</source>
PreparedStatement Set Array
<source lang="java">
/* JDBC Recipes: A Problem-Solution Approach (Problem-Solution Approach) (Hardcover) by Mahmoud Parsian
- Publisher: Apress (September 15, 2005)
- Language: English
- ISBN: 1590595203
- /
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import oracle.sql.ArrayDescriptor; public class Main {
public static void main(String[] args) throws Exception {
Connection conn = null;
PreparedStatement pstmt = null;
java.sql.Array sqlArray = null;
conn = getOracleConnection();
// For oracle you need an array descriptor specifying
// the type of the array and a connection to the database
// the first parameter must match with the SQL ARRAY type created
ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("CHAR_ARRAY", conn);
// then obtain an Array filled with the content below
String[] content = { "v1", "v2", "v3", "v4" };
sqlArray = new oracle.sql.ARRAY(arrayDescriptor, conn, content);
String query = "insert into CHAR_ARRAY_TABLE(id, array) values(?, ?)";
pstmt = conn.prepareStatement(query);
pstmt.setString(1, "0001");
pstmt.setArray(2, sqlArray);
int rowCount = pstmt.executeUpdate();
System.out.println("rowCount=" + rowCount);
System.out.println("--Demo_PreparedStatement_SetArray end--");
pstmt.close();
conn.close();
}
private static Connection getHSQLConnection() throws Exception {
Class.forName("org.hsqldb.jdbcDriver");
System.out.println("Driver Loaded.");
String url = "jdbc:hsqldb:data/tutorial";
return DriverManager.getConnection(url, "sa", "");
}
public static Connection getMySqlConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/demo2s";
String username = "oost";
String password = "oost";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static Connection getOracleConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
String username = "userName";
String password = "password";
Class.forName(driver); // load Oracle driver
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
}
</source>
PreparedStatement Set Object
<source lang="java">
/* JDBC Recipes: A Problem-Solution Approach (Problem-Solution Approach) (Hardcover) by Mahmoud Parsian
- Publisher: Apress (September 15, 2005)
- Language: English
- ISBN: 1590595203
- /
import java.sql.Clob; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Timestamp; public class Main {
public static void main(String[] args) throws Exception {
ResultSet rs = null;
Connection conn = null;
PreparedStatement pstmt = null;
PreparedStatement pstmt2 = null;
conn = getOracleConnection();
String[] columnNames = { "id", "name", "content", "date_created" };
Object[] inputValues = new Object[columnNames.length];
inputValues[0] = new java.math.BigDecimal(100);
inputValues[1] = new String("String Value");
inputValues[2] = new String("This is my resume.");
inputValues[3] = new Timestamp((new java.util.Date()).getTime());
// prepare blob object from an existing binary column
String insert = "insert into resume (id, name, content, date_created ) values(?, ?, ?, ?)";
pstmt = conn.prepareStatement(insert);
pstmt.setObject(1, inputValues[0]);
pstmt.setObject(2, inputValues[1]);
pstmt.setObject(3, inputValues[2]);
pstmt.setObject(4, inputValues[3]);
pstmt.executeUpdate();
String query = "select id, name, content, date_created from resume where id=?";
pstmt2 = conn.prepareStatement(query);
pstmt2.setObject(1, inputValues[0]);
rs = pstmt2.executeQuery();
Object[] outputValues = new Object[columnNames.length];
if (rs.next()) {
for (int i = 0; i < columnNames.length; i++) {
outputValues[i] = rs.getObject(i + 1);
}
}
System.out.println("id=" + ((java.math.BigDecimal) outputValues[0]).toString());
System.out.println("name=" + ((String) outputValues[1]));
System.out.println("content=" + ((Clob) outputValues[2]));
System.out.println("date_created=" + ((java.sql.Date) outputValues[3]).toString());
rs.close();
pstmt.close();
pstmt2.close();
conn.close();
}
private static Connection getHSQLConnection() throws Exception {
Class.forName("org.hsqldb.jdbcDriver");
System.out.println("Driver Loaded.");
String url = "jdbc:hsqldb:data/tutorial";
return DriverManager.getConnection(url, "sa", "");
}
public static Connection getMySqlConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/demo2s";
String username = "oost";
String password = "oost";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static Connection getOracleConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
String username = "userName";
String password = "password";
Class.forName(driver); // load Oracle driver
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
}
</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 data from a table
<source lang="java">
import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; public class Main {
public static void main(String[] argv) throws Exception {
Connection con = null;
PreparedStatement prepstmt = con
.prepareStatement("select Name, Addr from Employee where Id = ?");
prepstmt.setString(1, "1");
ResultSet rs;
rs = prepstmt.executeQuery();
boolean found = rs.next();
if (found)
System.out.println(rs.getString(1));
prepstmt.close();
}
}
</source>
Select Records Using PreparedStatement
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class SelectRecordsUsingPreparedStatement {
public static Connection getConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
String username = "name";
String password = "password";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static void main(String[] args) {
ResultSet rs = null;
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = getConnection();
String query = "select deptno, deptname, deptloc from dept where deptno > ?";
pstmt = conn.prepareStatement(query); // create a statement
pstmt.setInt(1, 1001); // set input parameter
rs = pstmt.executeQuery();
// extract data from the ResultSet
while (rs.next()) {
int dbDeptNumber = rs.getInt(1);
String dbDeptName = rs.getString(2);
String dbDeptLocation = rs.getString(3);
System.out.println(dbDeptNumber + "\t" + dbDeptName + "\t" + dbDeptLocation);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
rs.close();
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
</source>
Update Records Using PreparedStatement
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class UpdateRecordsUsingPreparedStatement {
public static Connection getConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
String username = "name";
String password = "password";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static void main(String[] args) throws Exception {
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = getConnection();
String query = "update dept set DEPT_LOC = ? where DEPT_NUM = ? ";
pstmt = conn.prepareStatement(query); // create a statement
pstmt.setString(1, "deptLocation"); // set input parameter 1
pstmt.setInt(2, 1001); // set input parameter 2
pstmt.executeUpdate(); // execute update statement
} catch (Exception e) {
e.printStackTrace();
System.exit(1);
} finally {
pstmt.close();
conn.close();
}
}
}
</source>
Use PreparedStatement Twice
<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 = getHSQLConnection();
System.out.println("Got Connection.");
Statement st = conn.createStatement();
st.executeUpdate("create table survey (id int,name varchar);");
st.executeUpdate("create view surveyView as (select * from survey);");
st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
st.executeUpdate("insert into survey (id,name ) values (2,"anotherValue")");
ResultSet rs = null;
PreparedStatement ps = null;
String query = "select id, name from survey where id = ?";
ps = conn.prepareStatement(query);
// specify values for all input parameters
ps.setInt(1, 001); // set the first parameter: id
// now, PreparedStatement object is ready to be executed.
rs = ps.executeQuery();
// iterate the result set object
while (rs.next()) {
int id = rs.getInt(1);
String name = rs.getString(2);
System.out.println("[id=" + id + "][name=" + name + "]");
}
// NOTE: you may use PreparedStatement as many times as you want
// here we use it for another set of parameters:
ps.setInt(1, 002); // set the first parameter: id
// now, PreparedStatement object is ready to be executed.
rs = ps.executeQuery();
// iterate the result set object
while (rs.next()) {
int id = rs.getInt(1);
String name = rs.getString(2);
System.out.println("[id=" + id + "][name=" + name + "]");
}
rs.close();
ps.close();
conn.close();
}
private static Connection getHSQLConnection() throws Exception {
Class.forName("org.hsqldb.jdbcDriver");
System.out.println("Driver Loaded.");
String url = "jdbc:hsqldb:data/tutorial";
return DriverManager.getConnection(url, "sa", "");
}
public static Connection getMySqlConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/demo2s";
String username = "oost";
String password = "oost";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static Connection getOracleConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:caspian";
String username = "mp";
String password = "mp2";
Class.forName(driver); // load Oracle driver
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
}
</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>
