1.PreparedStatement對象 PreparedStatement對象繼承Statement對象,它比Statement對象更強大,使用起來更簡單 1. Statement對象編譯SQL語句時,如果SQL語句有變數,就需要使用分隔符來隔開,如果變數非常多,就會使SQL變得非常複雜。 Pr ...
1.PreparedStatement對象
PreparedStatement對象繼承Statement對象,它比Statement對象更強大,使用起來更簡單
- Statement對象編譯SQL語句時,如果SQL語句有變數,就需要使用分隔符來隔開,如果變數非常多,就會使SQL變得非常複雜。PreparedStatement可以使用占位符,簡化sql的編寫
- Statement會頻繁編譯SQL。PreparedStatement可對SQL進行預編譯,提高效率,預編譯的SQL存儲在PreparedStatement對象中
- PreparedStatement防止SQL註入。【Statement通過分隔符'++',編寫永等式,可以不需要密碼就進入資料庫】
//模擬查詢id為2的信息
String id = "2";
Connection connection = UtilsDemo.getConnection();
String sql = "SELECT * FROM users WHERE id = ?";
PreparedStatement preparedStatement = connection.preparedStatement(sql);
//第一個參數表示第幾個占位符【也就是?號】,第二個參數表示值是多少
preparedStatement.setString(1,id);
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
System.out.println(resultSet.getString("name"));
}
//釋放資源
UtilsDemo.release(connection, preparedStatement, resultSet);
2.批處理
當需要向資料庫發送一批SQL語句執行時,應避免向資料庫一條條發送執行,採用批處理以提升執行效率
批處理有兩種方式:
- Statement
- PreparedStatement
通過executeBath()方法批量處理執行SQL語句,返回一個int[]數組,該數組代表各句SQL的返回值
以下代碼是以Statement方式實現批處理
/*
* Statement執行批處理
*
* 優點:
* 可以向資料庫發送不同的SQL語句
* 缺點:
* SQL沒有預編譯
* 僅參數不同的SQL,需要重覆寫多條SQL
* */
Connection connection = UtilsDemo.getConnection();
Statement statement = connection.createStatement();
String sql1 = "UPDATE users SET name='zhongfucheng' WHERE id='3'";
String sql2 = "INSERT INTO users (id, name, password, email, birthday)" +
" VALUES('5','nihao','123','[email protected]','1995-12-1')";
//將sql添加到批處理
statement.addBatch(sql1);
statement.addBatch(sql2);
//執行批處理
statement.executeBatch();
//清空批處理的sql
statement.clearBatch();
UtilsDemo.release(connection, statement, null);
以下方式以PreparedStatement方式實現批處理
/*
* PreparedStatement批處理
* 優點:
* SQL語句預編譯了
* 對於同一種類型的SQL語句,不用編寫很多條
* 缺點:
* 不能發送不同類型的SQL語句
*
* */
Connection connection = UtilsDemo.getConnection();
String sql = "INSERT INTO test(id,name) VALUES (?,?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
for (int i = 1; i <= 205; i++) {
preparedStatement.setInt(1, i);
preparedStatement.setString(2, (i + "zhongfucheng"));
//添加到批處理中
preparedStatement.addBatch();
if (i %2 ==100) {
//執行批處理
preparedStatement.executeBatch();
//清空批處理【如果數據量太大,所有數據存入批處理,記憶體肯定溢出】
preparedStatement.clearBatch();
}
}
//不是所有的%2==100,剩下的再執行一次批處理
preparedStatement.executeBatch();
//再清空
preparedStatement.clearBatch();
UtilsDemo.release(connection, preparedStatement, null);
3.處理大文本和二進位數據
clob和blob
- clob用於存儲大文本
- blob用於存儲二進位數據
MYSQL
MySQL存儲大文本是用Test【代替clob】,Test又分為4類
- TINYTEXT
- TEXT
- MEDIUMTEXT
- LONGTEXT
同理blob也有這4類
下麵用JDBC連接MySQL資料庫去操作大文本數據和二進位數據
/*
*用JDBC操作MySQL資料庫去操作大文本數據
*
*setCharacterStream(int parameterIndex,java.io.Reader reader,long length)
*第二個參數接收的是一個流對象,因為大文本不應該用String來接收,String太大會導致記憶體溢出
*第三個參數接收的是文件的大小
*
* */
public class Demo5 {
@Test
public void add() {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
String sql = "INSERT INTO test2 (bigTest) VALUES(?) ";
preparedStatement = connection.prepareStatement(sql);
//獲取到文件的路徑
String path = Demo5.class.getClassLoader().getResource("BigTest").getPath();
File file = new File(path);
FileReader fileReader = new FileReader(file);
//第三個參數,由於測試的Mysql版本過低,所以只能用int類型的。高版本的不需要進行強轉
preparedStatement.setCharacterStream(1, fileReader, (int) file.length());
if (preparedStatement.executeUpdate() > 0) {
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
} catch (FileNotFoundException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection, preparedStatement, null);
}
}
/*
* 讀取大文本數據,通過ResultSet中的getCharacterStream()獲取流對象數據
*
* */
@Test
public void read() {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
String sql = "SELECT * FROM test2";
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
Reader reader = resultSet.getCharacterStream("bigTest");
FileWriter fileWriter = new FileWriter("d:\\abc.txt");
char[] chars = new char[1024];
int len = 0;
while ((len = reader.read(chars)) != -1) {
fileWriter.write(chars, 0, len);
fileWriter.flush();
}
fileWriter.close();
reader.close();
}
} catch (SQLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection, preparedStatement, resultSet);
}
}
/*
* 使用JDBC連接MYsql資料庫操作二進位數據
* 如果我們要用資料庫存儲一個大視頻的時候,資料庫是存儲不到的。
* 需要設置max_allowed_packet,一般我們不使用資料庫去存儲一個視頻
* */
public class Demo6 {
@Test
public void add() {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
String sql = "INSERT INTO test3 (blobtest) VALUES(?)";
preparedStatement = connection.prepareStatement(sql);
//獲取文件的路徑和文件對象
String path = Demo6.class.getClassLoader().getResource("1.wmv").getPath();
File file = new File(path);
//調用方法
preparedStatement.setBinaryStream(1, new FileInputStream(path), (int)file.length());
if (preparedStatement.executeUpdate() > 0) {
System.out.println("添加成功");
}
} catch (SQLException e) {
e.printStackTrace();
} catch (FileNotFoundException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection, preparedStatement, null);
}
}
@Test
public void read() {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
String sql = "SELECT * FROM test3";
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
//如果讀取到數據,就把數據寫到磁碟下
if (resultSet.next()) {
InputStream inputStream = resultSet.getBinaryStream("blobtest");
FileOutputStream fileOutputStream = new FileOutputStream("d:\\aa.jpg");
int len = 0;
byte[] bytes = new byte[1024];
while ((len = inputStream.read(bytes)) > 0) {
fileOutputStream.write(bytes, 0, len);
}
fileOutputStream.close();
inputStream.close();
}
} catch (SQLException e) {
e.printStackTrace();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection, preparedStatement, null);
}
}
Oracle
下麵用JDBC連接Oracle資料庫去操作大文本數據和二進位數據
//使用JDBC連接Oracle資料庫操作二進位數據
/*
* 對於Oracle資料庫和Mysql資料庫是有所不同的。
* 1.Oracle定義了BLOB欄位,但是這個欄位不是真正地存儲二進位數據
* 2.向這個欄位存一個BLOB指針,獲取到Oracle的BLOB對象,把二進位數據放到這個指針裡面,指針指向BLOB欄位
* 3.需要事務支持
*
* */
public class Demo7 {
@Test
public void add() {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = UtilsDemo.getConnection();
//開啟事務
connection.setAutoCommit(false);
//插入一個BLOB指針
String sql = "insert into test4(id,image) values(?,empty_blob())";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, 1);
preparedStatement.executeUpdate();
//把BLOB指針查詢出來,得到BLOB對象
String sql2 = "select image from test4 where id= ? for update";
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.setInt(1, 1);
resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
//得到Blob對象--當成是Oracle的Blob,不是JDBC的,所以要強轉[導的是oracle.sql.BLOB包]
BLOB blob = (BLOB) resultSet.getBlob("image");
//寫入二進位數據
OutputStream outputStream = blob.getBinaryOutputStream();
//獲取到讀取文件讀入流
InputStream inputStream = Demo7.class.getClassLoader().getResourceAsStream("01.jpg");
int len=0;
byte[] bytes = new byte[1024];
while ((len = inputStream.read(bytes)) > 0) {
outputStream.write(bytes, 0, len);
}
outputStream.close();
inputStream.close();
connection.setAutoCommit(true);
}
} catch (SQLException e) {
e.printStackTrace();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
UtilsDemo.release(connection, preparedStatement, null);
}
}
@Test
public void find() {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = UtilsDemo.getConnection();
String sql = "SELECT * FROM test4 WHERE id=1";
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
//獲取到BLOB對象
BLOB blob = (BLOB) resultSet.getBlob("image");
//將數據讀取到磁碟上
InputStream inputStream = blob.getBinaryStream();
FileOutputStream fileOutputStream = new FileOutputStream("d:\\zhongfucheng.jpg");
int len=0;
byte[] bytes = new byte[1024];
while ((len = inputStream.read(bytes)) > 0) {
fileOutputStream.write(bytes, 0, len);
}
inputStream.close();
fileOutputStream.close();
}
} catch (SQLException e) {
e.printStackTrace();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
UtilsDemo.release(connection, preparedStatement, null);
}
}
}
對於JDBC連接Oracle資料庫操作CLOB數據,我就不再重覆了,操作跟BLOB幾乎相同
4.獲取資料庫的自動主鍵列
為什麼要獲取資料庫的自動主鍵列數據?
應用場景:
有一張老師表,一張學生表。現在來了一個新的老師,學生要跟著新老師上課。
我首先要知道老師的id編號是多少,學生才能知道跟著哪個老師學習【學生外鍵參照老師主鍵】。
@Test
public void test() {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
String sql = "INSERT INTO test(name) VALUES(?)";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, "ouzicheng");
if (preparedStatement.executeUpdate() > 0) {
//獲取到自動主鍵列的值
resultSet = preparedStatement.getGeneratedKeys();
if (resultSet.next()) {
int id = resultSet.getInt(1);
System.out.println(id);
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection, preparedStatement, null);
}
5.調用資料庫的存儲過程
調用存儲過程的語法:
{call <procedure-name>[(<arg1>,<arg2>, ...)]}
調用函數的語法:
{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
如果是Output類型的,那麼在JDBC調用的時候是要註冊的。如下代碼所示:
/*
jdbc調用存儲過程
delimiter $$
CREATE PROCEDURE demoSp(IN inputParam VARCHAR(255), INOUT inOutParam varchar(255))
BEGIN
SELECT CONCAT('zyxw---', inputParam) into inOutParam;
END $$
delimiter ;
*/
//我們在JDBC調用存儲過程,就像在調用方法一樣
public class Demo9 {
public static void main(String[] args) {
Connection connection = null;
CallableStatement callableStatement = null;
try {
connection = JdbcUtils.getConnection();
callableStatement = connection.prepareCall("{call demoSp(?,?)}");
callableStatement.setString(1, "nihaoa");
//註冊第2個參數,類型是VARCHAR
callableStatement.registerOutParameter(2, Types.VARCHAR);
callableStatement.execute();
//獲取傳出參數[獲取存儲過程里的值]
String result = callableStatement.getString(2);
System.out.println(result);
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
connection.close();
callableStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
參考資料:
----------------------------------------------------------------------------------過程
#修改mysql語句的結果符為//
mysql > delimiter //
#定義一個過程,獲取users表總記錄數,將10設置到變數count中
create procedure simpleproc(out count int)
begin
select count(id) into count from users;
end
//
#修改mysql語句的結果符為;
mysql > delimiter ;
#調用過程,將結果覆給變數a,@是定義變數的符號
call simpleproc(@a);
#顯示變數a的值
select @a;
//以下是Java調用Mysql的過程
String sql = "{call simpleproc(?)}";
Connection conn = JdbcUtil.getConnection();
CallableStatement cstmt = conn.prepareCall(sql);
cstmt.registerOutParameter(1,Types.INTEGER);
cstmt.execute();
Integer count = cstmt.getInt(1);
System.out.println("共有" + count + "人");
----------------------------------------------------------------------------------函數
#修改mysql語句的結果符為//
mysql > delimiter //
#定義一個函數,完成字元串拼接
create function hello( s char(20) ) returns char(50)
return concat('hello,',s,'!');
//
#修改mysql語句的結果符為;
mysql > delimiter ;
#調用函數
select hello('world');
//以下是Java調用Mysql的函數
String sql = "{? = call hello(?)}";
Connection conn = JdbcUtil.getConnection();
CallableStatement cstmt = conn.prepareCall(sql);
cstmt.registerOutParameter(1,Types.VARCHAR);
cstmt.setString(2,"zhaojun");
cstmt.execute();
String value = cstmt.getString(1);
System.out.println(value);
JdbcUtil.close(cstmt);
JdbcUtil.close(conn);
如果文章有錯的地方歡迎指正,大家互相交流。習慣在微信看技術文章的同學,可以關註微信公眾號:Java3y