1.什么是JDBC?
Java DataBase Connectivity Java 數(shù)據(jù)庫(kù)連接
其實(shí)就是官方（sun公司）定義的一套操作所有關(guān)系型數(shù)據(jù)庫(kù)的規(guī)則，即接口蓝牲。各個(gè)數(shù)據(jù)庫(kù)廠商去實(shí)現(xiàn)這套接口，提供數(shù)據(jù)庫(kù)驅(qū)動(dòng)jar包澡绩。我們可以使用這套接口（JDBC）編程锤灿，真正去執(zhí)行的代碼式驅(qū)動(dòng)jar包的實(shí)現(xiàn)類(lèi)第股。
2.步驟：
a.導(dǎo)入驅(qū)動(dòng)jar包,需要Add As Library
b.注冊(cè)驅(qū)動(dòng)對(duì)象
mysql注冊(cè)驅(qū)動(dòng)：mysql5之后的jar包可以省略注冊(cè)驅(qū)動(dòng)的步驟
Class.forName("com.mysql.cj.jdbc.Driver");
c.定義數(shù)據(jù)庫(kù)連接對(duì)象 Connection
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/dbtest","root","bbobbo24");
d.定義sql語(yǔ)句
String sqlString = "delete from tbtest where id = 8;";
e.獲取執(zhí)行sql語(yǔ)句的對(duì)象 Statement
Statement statement = connection.createStatement();
f.執(zhí)行sql,接收返回結(jié)果
int count = statement.executeUpdate(sqlString);
g.處理結(jié)果
if (count > 0) {
System.out.println("操作成功");
} else {
System.out.println("操作失敗");
}
h.釋放資源
if (connection != null) {
try {
connection.close();
}catch (SQLException e) {
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
}
}
3.數(shù)據(jù)庫(kù)連接對(duì)象 Connection
1.獲取執(zhí)行sql的對(duì)象Statement
a. createStatement
b. prepareStatement(String sqlString) 可以防止SQL注入丁稀，效率更高
String sqlString = "select * from tbtest where worker=? and score > ?;";
preparedStatement = connection.prepareStatement(sqlString);
preparedStatement.setInt(1,2);
preparedStatement.setDouble(2,80);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
System.out.println(resultSet.getInt("id") + " " + resultSet.getString("name"));
}
2.管理事務(wù)：
- 開(kāi)啟事務(wù)：setAutoCommit(false);
- 提交事務(wù)：commit();
- 回滾事務(wù)：rollback();
判空&&異常
try {
// 開(kāi)啟事務(wù)
connection.setAutoCommit(false);
statement = connection.createStatement();
statement.executeUpdate("update tbtest set score = score + 5 where score > 80;");
//如果這兒發(fā)生了一個(gè)錯(cuò)誤
statement.executeUpdate("update tbtest set score = score - 5 where score < 80;");
//提交事務(wù)
connection.commit();
System.out.println("成績(jī)更新成功");
} catch (Exception exception) {
if (connection != null) {
try {
//回滾事務(wù)
connection.rollback();
System.out.println("回滾事務(wù)");
} catch (Exception exception1) {
}
}
System.out.println(exception.getMessage());
}
4.Statement 執(zhí)行sql的對(duì)象
-
execute可以執(zhí)行任意sql -
int executeUpdate執(zhí)行DML(insert吼拥、update、delete)二驰、DDL(create扔罪、alter秉沼、drop) -
ResultSet executeQuery執(zhí)行DQL(select)
5.結(jié)果集對(duì)象 ResultSet
- next(); 游標(biāo)向下移動(dòng)一行桶雀，當(dāng)查詢(xún)結(jié)果是個(gè)表時(shí)游標(biāo)在表第一行，判斷當(dāng)前行是否是最后一行
- getXxx(參數(shù)):獲取數(shù)據(jù)
a. 可以傳值列的編號(hào)從1開(kāi)始getString(2)
b. 傳值列的名稱(chēng)推薦getString("name")
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
System.out.println(resultSet.getInt("id") + " " + resultSet.getString("name"));
}
6數(shù)據(jù)庫(kù)連接池
- 節(jié)約資源
- 用戶(hù)訪問(wèn)高效
1.使用：DataSource
a. 獲取連接dataSource.getConnection()
b. 歸還連接connection.close();//如果連接對(duì)象Connection是從連接池獲取那么close將不是關(guān)閉連接唬复，而是歸還矗积。
- Druid：數(shù)據(jù)庫(kù)連接池實(shí)現(xiàn)技術(shù)，由阿里巴巴提供
a. 導(dǎo)入jar包 地址
b. 定義配置文件properties
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/dbtest
username=root
password=xxxxxx
#初始化連接數(shù)量
initialSize=5
#最大連接數(shù)
maxActive=20
#最大等待時(shí)間
maxWait=3000
c. 加載配置文件
d. 獲取數(shù)據(jù)庫(kù)連接池對(duì)象 DruidDataSourceFactory
e. 獲取連接
Properties properties = new Properties();
InputStream inputStream = ConneFour.class.getClassLoader().getResourceAsStream("druid.properties");
properties.load(inputStream);
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
Connection connection = dataSource.getConnection();
System.out.println(connection);
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("select * from tbtest;");
while (resultSet.next()) {
System.out.println(resultSet.getInt(1) + " " + resultSet.getString(2));
}
7.Spring JDBC
Spring 框架對(duì) JDBC 的封裝敞咧，提供了一個(gè) JdbcTemplate對(duì)象簡(jiǎn)化JDBC開(kāi)發(fā)
a. 導(dǎo)入jar包 地址
- spring-beans
- spring-core
- spring-jdbc
- spring-tx
- com.springsource.org.apache.commons.logging-1.1.1
b.創(chuàng)建JdbcTemplate棘捣，依賴(lài)于數(shù)據(jù)源DataSource
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
c.執(zhí)行CRUD
- update() 執(zhí)行DML語(yǔ)句
- queryForMap() 查詢(xún)結(jié)果，將結(jié)果封裝成map集合 這個(gè)方法查詢(xún)的結(jié)果集長(zhǎng)度只能為1休建，列名作為key,值為value,將這條記錄封裝成map集合
- queryForList() 查詢(xún)結(jié)果乍恐，將結(jié)果封裝成list集合 將其中每一條記錄封裝成map
- query() 查詢(xún)結(jié)果，將結(jié)果封裝為JavaBean對(duì)象
- queryForObject() 查詢(xún)結(jié)果测砂，將結(jié)果封裝成對(duì)象
//int infos = jdbcTemplate.update("update tbtest set score=100 where id=1;");
//Map infos = jdbcTemplate.queryForMap("select * from tbtest where id=1;");
//List infos = jdbcTemplate.queryForList("select * from tbtest;");
List<TbInfoBean> infos = jdbcTemplate.query("select * from tbtest;",new BeanPropertyRowMapper<TbInfoBean>(TbInfoBean.class));
