1.PreparedStatement對(duì)象
PreparedStatement對(duì)象繼承Statement對(duì)象，它比Statement對(duì)象更強(qiáng)大在塔，使用起來(lái)更簡(jiǎn)單
- Statement對(duì)象編譯SQL語(yǔ)句時(shí)幻件，如果SQL語(yǔ)句有變量，就需要使用分隔符來(lái)隔開(kāi)蛔溃，如果變量非常多绰沥，就會(huì)使SQL變得非常復(fù)雜。PreparedStatement可以使用占位符贺待，簡(jiǎn)化sql的編寫
- Statement會(huì)頻繁編譯SQL徽曲。PreparedStatement可對(duì)SQL進(jìn)行預(yù)編譯，提高效率狠持，預(yù)編譯的SQL存儲(chǔ)在PreparedStatement對(duì)象中
- PreparedStatement防止SQL注入疟位。【Statement通過(guò)分隔符'++',編寫永等式喘垂，可以不需要密碼就進(jìn)入數(shù)據(jù)庫(kù)】
//模擬查詢id為2的信息
String id = "2";
Connection connection = UtilsDemo.getConnection();
String sql = "SELECT * FROM users WHERE id = ?";
PreparedStatement preparedStatement = connection.preparedStatement(sql);
//第一個(gè)參數(shù)表示第幾個(gè)占位符【也就是?號(hào)】甜刻，第二個(gè)參數(shù)表示值是多少
preparedStatement.setString(1,id);
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
System.out.println(resultSet.getString("name"));
}
//釋放資源
UtilsDemo.release(connection, preparedStatement, resultSet);
2.批處理
當(dāng)需要向數(shù)據(jù)庫(kù)發(fā)送一批SQL語(yǔ)句執(zhí)行時(shí)，應(yīng)避免向數(shù)據(jù)庫(kù)一條條發(fā)送執(zhí)行正勒，采用批處理以提升執(zhí)行效率
批處理有兩種方式：
- Statement
- PreparedStatement
通過(guò)executeBath()方法批量處理執(zhí)行SQL語(yǔ)句得院，返回一個(gè)int[]數(shù)組，該數(shù)組代表各句SQL的返回值
以下代碼是以Statement方式實(shí)現(xiàn)批處理
/*
* Statement執(zhí)行批處理
*
* 優(yōu)點(diǎn)：
* 可以向數(shù)據(jù)庫(kù)發(fā)送不同的SQL語(yǔ)句
* 缺點(diǎn)：
* SQL沒(méi)有預(yù)編譯
* 僅參數(shù)不同的SQL章贞，需要重復(fù)寫多條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','ss@qq.com','1995-12-1')";
//將sql添加到批處理
statement.addBatch(sql1);
statement.addBatch(sql2);
//執(zhí)行批處理
statement.executeBatch();
//清空批處理的sql
statement.clearBatch();
UtilsDemo.release(connection, statement, null);
以下方式以PreparedStatement方式實(shí)現(xiàn)批處理
/*
* PreparedStatement批處理
* 優(yōu)點(diǎn)：
* SQL語(yǔ)句預(yù)編譯了
* 對(duì)于同一種類型的SQL語(yǔ)句祥绞，不用編寫很多條
* 缺點(diǎn)：
* 不能發(fā)送不同類型的SQL語(yǔ)句
*
* */
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) {
//執(zhí)行批處理
preparedStatement.executeBatch();
//清空批處理【如果數(shù)據(jù)量太大，所有數(shù)據(jù)存入批處理，內(nèi)存肯定溢出】
preparedStatement.clearBatch();
}
}
//不是所有的%2==100蜕径，剩下的再執(zhí)行一次批處理
preparedStatement.executeBatch();
//再清空
preparedStatement.clearBatch();
UtilsDemo.release(connection, preparedStatement, null);
3.處理大文本和二進(jìn)制數(shù)據(jù)
clob和blob
- clob用于存儲(chǔ)大文本
- blob用于存儲(chǔ)二進(jìn)制數(shù)據(jù)
MYSQL
MySQL存儲(chǔ)大文本是用Test【代替clob】两踏，Test又分為4類
- TINYTEXT
- TEXT
- MEDIUMTEXT
- LONGTEXT
同理blob也有這4類
下面用JDBC連接MySQL數(shù)據(jù)庫(kù)去操作大文本數(shù)據(jù)和二進(jìn)制數(shù)據(jù)
/*
*用JDBC操作MySQL數(shù)據(jù)庫(kù)去操作大文本數(shù)據(jù)
*
*setCharacterStream(int parameterIndex,java.io.Reader reader,long length)
*第二個(gè)參數(shù)接收的是一個(gè)流對(duì)象，因?yàn)榇笪谋静粦?yīng)該用String來(lái)接收兜喻，String太大會(huì)導(dǎo)致內(nèi)存溢出
*第三個(gè)參數(shù)接收的是文件的大小
*
* */
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);
//第三個(gè)參數(shù)梦染，由于測(cè)試的Mysql版本過(guò)低，所以只能用int類型的朴皆。高版本的不需要進(jìn)行強(qiáng)轉(zhuǎn)
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);
}
}
/*
* 讀取大文本數(shù)據(jù)帕识，通過(guò)ResultSet中的getCharacterStream()獲取流對(duì)象數(shù)據(jù)
*
* */
@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數(shù)據(jù)庫(kù)操作二進(jìn)制數(shù)據(jù)
* 如果我們要用數(shù)據(jù)庫(kù)存儲(chǔ)一個(gè)大視頻的時(shí)候，數(shù)據(jù)庫(kù)是存儲(chǔ)不到的遂铡。
* 需要設(shè)置max_allowed_packet肮疗，一般我們不使用數(shù)據(jù)庫(kù)去存儲(chǔ)一個(gè)視頻
* */
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);
//獲取文件的路徑和文件對(duì)象
String path = Demo6.class.getClassLoader().getResource("1.wmv").getPath();
File file = new File(path);
//調(diào)用方法
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();
//如果讀取到數(shù)據(jù)，就把數(shù)據(jù)寫到磁盤下
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數(shù)據(jù)庫(kù)去操作大文本數(shù)據(jù)和二進(jìn)制數(shù)據(jù)
//使用JDBC連接Oracle數(shù)據(jù)庫(kù)操作二進(jìn)制數(shù)據(jù)
/*
* 對(duì)于Oracle數(shù)據(jù)庫(kù)和Mysql數(shù)據(jù)庫(kù)是有所不同的扒接。
* 1.Oracle定義了BLOB字段伪货，但是這個(gè)字段不是真正地存儲(chǔ)二進(jìn)制數(shù)據(jù)
* 2.向這個(gè)字段存一個(gè)BLOB指針，獲取到Oracle的BLOB對(duì)象,把二進(jìn)制數(shù)據(jù)放到這個(gè)指針里面,指針指向BLOB字段
* 3.需要事務(wù)支持
*
* */
public class Demo7 {
@Test
public void add() {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = UtilsDemo.getConnection();
//開(kāi)啟事務(wù)
connection.setAutoCommit(false);
//插入一個(gè)BLOB指針
String sql = "insert into test4(id,image) values(?,empty_blob())";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, 1);
preparedStatement.executeUpdate();
//把BLOB指針查詢出來(lái),得到BLOB對(duì)象
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對(duì)象--當(dāng)成是Oracle的Blob,不是JDBC的,所以要強(qiáng)轉(zhuǎn)[導(dǎo)的是oracle.sql.BLOB包]
BLOB blob = (BLOB) resultSet.getBlob("image");
//寫入二進(jìn)制數(shù)據(jù)
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對(duì)象
BLOB blob = (BLOB) resultSet.getBlob("image");
//將數(shù)據(jù)讀取到磁盤上
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);
}
}
}
對(duì)于JDBC連接Oracle數(shù)據(jù)庫(kù)操作CLOB數(shù)據(jù),我就不再重復(fù)了,操作跟BLOB幾乎相同
4.獲取數(shù)據(jù)庫(kù)的自動(dòng)主鍵列
為什么要獲取數(shù)據(jù)庫(kù)的自動(dòng)主鍵列數(shù)據(jù)?
應(yīng)用場(chǎng)景:
有一張老師表钾怔，一張學(xué)生表超歌。現(xiàn)在來(lái)了一個(gè)新的老師，學(xué)生要跟著新老師上課蒂教。
我首先要知道老師的id編號(hào)是多少，學(xué)生才能知道跟著哪個(gè)老師學(xué)習(xí)【學(xué)生外鍵參照老師主鍵】脆荷。
@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) {
//獲取到自動(dòng)主鍵列的值
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.調(diào)用數(shù)據(jù)庫(kù)的存儲(chǔ)過(guò)程
調(diào)用存儲(chǔ)過(guò)程的語(yǔ)法：
{call <procedure-name>[(<arg1>,<arg2>, ...)]}
調(diào)用函數(shù)的語(yǔ)法：
{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
如果是Output類型的凝垛，那么在JDBC調(diào)用的時(shí)候是要注冊(cè)的。如下代碼所示：
/*
jdbc調(diào)用存儲(chǔ)過(guò)程
delimiter $$
CREATE PROCEDURE demoSp(IN inputParam VARCHAR(255), INOUT inOutParam varchar(255))
BEGIN
SELECT CONCAT('zyxw---', inputParam) into inOutParam;
END $$
delimiter ;
*/
//我們?cè)贘DBC調(diào)用存儲(chǔ)過(guò)程,就像在調(diào)用方法一樣
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");
//注冊(cè)第2個(gè)參數(shù),類型是VARCHAR
callableStatement.registerOutParameter(2, Types.VARCHAR);
callableStatement.execute();
//獲取傳出參數(shù)[獲取存儲(chǔ)過(guò)程里的值]
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();
}
}
}
參考資料：
----------------------------------------------------------------------------------過(guò)程
#修改mysql語(yǔ)句的結(jié)果符為//
mysql > delimiter //
#定義一個(gè)過(guò)程蜓谋，獲取users表總記錄數(shù)梦皮，將10設(shè)置到變量count中
create procedure simpleproc(out count int)
begin
select count(id) into count from users;
end
//
#修改mysql語(yǔ)句的結(jié)果符為;
mysql > delimiter ;
#調(diào)用過(guò)程，將結(jié)果覆給變量a桃焕，@是定義變量的符號(hào)
call simpleproc(@a);
#顯示變量a的值
select @a;
//以下是Java調(diào)用Mysql的過(guò)程
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 + "人");
----------------------------------------------------------------------------------函數(shù)
#修改mysql語(yǔ)句的結(jié)果符為//
mysql > delimiter //
#定義一個(gè)函數(shù)剑肯，完成字符串拼接
create function hello( s char(20) ) returns char(50)
return concat('hello，',s,'!');
//
#修改mysql語(yǔ)句的結(jié)果符為;
mysql > delimiter ;
#調(diào)用函數(shù)
select hello('world');
//以下是Java調(diào)用Mysql的函數(shù)
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);
如果文章有錯(cuò)的地方歡迎指正观堂，大家互相交流让网。習(xí)慣在微信看技術(shù)文章的同學(xué)，可以關(guān)注微信公眾號(hào):Java3y
