您好,登錄后才能下訂單哦!
小編給大家分享一下java數(shù)據(jù)庫開發(fā)之JDBC基礎(chǔ)使用方法的示例分析,相信大部分人都還不怎么了解,因此分享這篇文章給大家參考一下,希望大家閱讀完這篇文章后大有收獲,下面讓我們一起去了解一下吧!
JDBC是一種用于執(zhí)行SQL語句的Java API,可以為多種關(guān)系數(shù)據(jù)庫提供統(tǒng)一訪問,它由一組用Java語言編寫的類和接口組成。JDBC提供了一種基準(zhǔn),據(jù)此可以構(gòu)建更高級的工具和接口,使數(shù)據(jù)庫開發(fā)人員能夠編寫數(shù)據(jù)庫應(yīng)用程序
JDBC 數(shù)據(jù)庫訪問規(guī)范
應(yīng)用程序 <-> JDBC <-> MySQL驅(qū)動 <-> MySQL
<-> Oracle驅(qū)動 <-> Oracle
導(dǎo)入jar包
加載驅(qū)動 Class.forName(‘類名')
給出url、username、password
使用DriverManager類得到Connection類
maven導(dǎo)入依賴
<dependencies> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.18</version> </dependency> </dependencies>
連接示例
import java.sql.Connection; import java.sql.DriverManager; class Demo { // MySQL >= 8.0 配置參數(shù) private static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver"; private static final String DB_URL = "jdbc:mysql://localhost:3306/data"; private static final String USER = "root"; private static final String PASSWORD = "123456"; public static void main(String[] args) throws Exception { // 注冊 JDBC 驅(qū)動 Class.forName(JDBC_DRIVER); // 等效于 // com.mysql.cj.jdbc.Driver driver = new com.mysql.cj.jdbc.Driver(); // DriverManager.registerDriver(driver); // 打開鏈接 Connection conn = DriverManager.getConnection(DB_URL, USER, PASSWORD); // 關(guān)閉鏈接 conn.close(); } }
所有的java.sql.Driver實(shí)現(xiàn)類,都提供了static代碼塊,
塊內(nèi)代碼把自己注冊到DriverManager中
jdbc4.0之后 每個(gè)驅(qū)動jar包中,在META-INF/services目錄下提供了一個(gè)java.sql.Driver文件
內(nèi)容就是該接口的實(shí)現(xiàn)類名稱
// 發(fā)送DML, DDL int Statement.executeUpdate(String sql);
代碼示例
// 注冊 JDBC 驅(qū)動 Class.forName(JDBC_DRIVER); // 打開鏈接 Connection conn = DriverManager.getConnection(DB_URL, USER, PASSWORD); Statement statement = conn.createStatement(); // 增刪改 // String sql = "insert into student(sname) values('陸小鳳')"; // String sql = "update student set sname='花無缺' where sid=4"; String sql = "delete from student where sid=4"; int ret = statement.executeUpdate(sql); System.out.println(ret); // 關(guān)閉鏈接 conn.close();
ResultSet executeQuery(String querySql); boolean ResultSet.next(); // 獲取列數(shù)據(jù) ResultSet.getString() ResultSet.getObject() ResultSet.getInt() ResultSet.getDouble()
行光標(biāo)
beforeFirst <- 默認(rèn)光標(biāo)位置
first
last
AfterLast
// 打開鏈接 Connection conn = DriverManager.getConnection(DB_URL, USER, PASSWORD); Statement statement = conn.createStatement(); // 查詢 String sql = "select * from student"; ResultSet ret = statement.executeQuery(sql); while (ret.next()){ // 通過列序號獲取 int uid = ret.getInt(1); // 通過列名稱獲取 String name = ret.getString("sname"); System.out.println(uid + ", " + name); } // 關(guān)閉資源 ret.close(); statement.close(); conn.close();
// 定義 try{ // 實(shí)例化 } finally{ // 關(guān)閉資源 }
JBDC主要的類
DriverManager Connection conn = DriverManager.getConnection(DB_URL, USER, PASSWORD); Statement statement = conn.createStatement(); int executeUpdate(String sql) // 執(zhí)行增、刪、改 ResultSet executeQuery(String sql) // 執(zhí)行查詢 boolean execute(String sql) // 執(zhí)行增、刪、改、查
ResultSet滾動結(jié)果集
一個(gè)二維表格,內(nèi)部維護(hù)了一個(gè)行光標(biāo)(游標(biāo))
next() // 最常用
beforeFirst()
afterLast()
first()
last()
getRow()
absolute()
relative()
isBeforeFirst()
isAfterLast()
isFirst()
isLast()
元數(shù)據(jù)
// 元數(shù)據(jù) ResultSetMetaData ResultSet.getMetaData() // 獲取結(jié)果集列數(shù) int ResultSetMetaData.getColumnCount() // 獲取指定列的列名 String ResultSetMetaData.getColumnName(int colIndex)
確定結(jié)果集特性
1、是否可滾動
2、是否敏感
3、是否可更新
// 不滾動, 不敏感,不可更新 Statement createStatement() // 滾動支持 Statement createStatement(int resultSetType, int resultSetConcurrency) resultSetType: ResultSet.TYPE_FORWARD_ONLY // 不滾動 ResultSet.TYPE_SCROLL_INSENSITIVE // 滾動,不隨數(shù)據(jù)庫變化而變化 ResultSet.TYPE_SCROLL_SENSITIVE // 滾動,不隨數(shù)據(jù)庫變化而變化 resultSetConcurrency // 是否通過修改結(jié)果集二反向影響數(shù)據(jù)庫 ResultSet.CONCUR_READ_ONLY // 結(jié)果集只讀 ResultSet.CONCUR_UPDATABLE // 結(jié)果集可更新
PreparedStatement是Statement子接口
1、防止SQL注入攻擊
2、提高代碼可讀性,可維護(hù)性
3、提高效率
// 打開鏈接 Connection conn = DriverManager.getConnection(DB_URL, USER, PASSWORD); // 使用預(yù)處理查詢, 使用?占位 String sql = "select * from student where sid = ?"; PreparedStatement statement = conn.prepareStatement(sql); // 為參數(shù)賦值 statement.setInt(1, 1); // 獲取數(shù)據(jù) ResultSet ret = statement.executeQuery(); while (ret.next()){ String name = ret.getString("sname"); System.out.println(name); } // 關(guān)閉資源 ret.close(); statement.close(); conn.close();
服務(wù)器工作:
(1)校驗(yàn):sql語句的語法
(2)編譯:為一個(gè)與函數(shù)相似的東西
(3)執(zhí)行:調(diào)用函數(shù)
PreparedStatement
(1)先將sql發(fā)給數(shù)據(jù)庫,數(shù)據(jù)庫先進(jìn)行校驗(yàn)
(2)執(zhí)行的時(shí)候只發(fā)送參數(shù)
prepare myfun from 'select * from student where sid = ?' set @uid=1 execute myfun using @uid
設(shè)置連接參數(shù):
useServerPrepStmts=true
cachePrepStmts=true
DB_URL = "jdbc:mysql://localhost:3306/data?useServerPrepStmts=true&cachePrepStmts=true";
9.JdbcUtils1.0小工具
JdbcUtils.java
package util; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.Properties; public class JdbcUtils { // 配置文件路徑 private static String dbconfig = "dbconfig.properties"; private static Properties prop = null; // 靜態(tài)代碼塊只執(zhí)行一次 static { // 初始化數(shù)據(jù)庫配置參數(shù) try { InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream(dbconfig); prop = new Properties(); prop.load(in); } catch (IOException e) { throw new RuntimeException(e); } // 加載驅(qū)動 try{ Class.forName(prop.getProperty("driver")); } catch (ClassNotFoundException e) { throw new RuntimeException(e); } } public static Connection getConnection() throws SQLException { return DriverManager.getConnection( prop.getProperty("url"), prop.getProperty("username"), prop.getProperty("password") ); } public static void main(String[] args) throws SQLException { Connection conn = getConnection(); System.out.println(conn); } }
dbconfig.properties
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/data
username=root
password=123456
DAO模式
data access object
寫一個(gè)類,把訪問數(shù)據(jù)庫的代碼封裝起來
DAO在數(shù)據(jù)庫與業(yè)務(wù)邏輯(service)之間
實(shí)體域,即操作的對象
DAO模式步驟
(1)提供一個(gè)DAO接口
(2)提供一個(gè)DAO接口的實(shí)現(xiàn)類
(3)在編寫一個(gè)DAO工廠,Service通過工廠來獲取DAO實(shí)現(xiàn)
daoconfig.properties
UserDaoClassName=UserDaoImpl
UserDao.java
public interface UserDao { }
UserDaoImpl.java
public class UserDaoImpl implements UserDao{ }
DaoFactory.java
import java.io.IOException; import java.io.InputStream; import java.util.Properties; public class DaoFactory { // 配置文件路徑 private static String dbconfig = "daoconfig.properties"; private static Properties prop = null; // 靜態(tài)代碼塊只執(zhí)行一次 static { // 初始化數(shù)據(jù)庫配置參數(shù) try { InputStream in = DaoFactory.class.getClassLoader().getResourceAsStream(dbconfig); prop = new Properties(); prop.load(in); } catch (IOException e) { throw new RuntimeException(e); } } /** * 返回一個(gè)UserDao的具體實(shí)現(xiàn)類 */ public static UserDao getUserDao() { String daoClassName = prop.getProperty("UserDaoClassName"); // 通過反射創(chuàng)建實(shí)現(xiàn)類的對象 try { Class Clazz = Class.forName(daoClassName); return (UserDao) Clazz.newInstance(); } catch (Exception e) { throw new RuntimeException(e); } } }
User.java
public class User { private String username; private int age; public String getUsername() { return username; } public int getAge() { return age; } public void setUsername(String username) { this.username = username; } public void setAge(int age) { this.age = age; } @Override public String toString() { return "User{" + "username='" + username + '\'' + ", age=" + age + '}'; } }
daoconfig.properties
UserDaoClassName=UserDaoImpl
UserDao.java
public interface UserDao { public void addUser(User user); public User getUserByUsername(String username); }
UserDaoImpl.java
import util.JdbcUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; /** * create table user( * id int primary key auto_increment, * username varchar(50), * age int * ) */ public class UserDaoImpl implements UserDao { /** * ORM 對象關(guān)系映射 * @param user */ @Override public void addUser(User user) { Connection conn = null; PreparedStatement statement = null; try { // 得到連接 conn = JdbcUtils.getConnection(); String sql = "insert into user(username, age) values(?, ?)"; // 準(zhǔn)備模板 statement = conn.prepareStatement(sql); // 賦值 statement.setString(1, user.getUsername()); statement.setInt(2, user.getAge()); // 執(zhí)行 statement.executeUpdate(); } catch (Exception e) { throw new RuntimeException(e); } finally { try { if (statement != null) { statement.close(); } if (conn != null) { conn.close(); } } catch (SQLException e) { } } } @Override public User getUserByUsername(String username) { Connection conn = null; PreparedStatement statement = null; try { // 得到連接 conn = JdbcUtils.getConnection(); String sql = "select * from user where username = ? limit 1"; // 準(zhǔn)備模板 statement = conn.prepareStatement(sql); // 賦值 statement.setString(1, username); // 執(zhí)行 ResultSet resultSet = statement.executeQuery(); if(resultSet.next()){ User user = new User(); user.setUsername(resultSet.getString("username")); user.setAge(resultSet.getInt("age")); return user; } else{ return null; } } catch (Exception e) { throw new RuntimeException(e); } finally { try { if (statement != null) { statement.close(); } if (conn != null) { conn.close(); } } catch (SQLException e) { } } } }
DaoFactory.java
import java.io.IOException; import java.io.InputStream; import java.util.Properties; public class DaoFactory { // 配置文件路徑 private static String dbconfig = "daoconfig.properties"; private static Properties prop = null; // 靜態(tài)代碼塊只執(zhí)行一次 static { // 初始化數(shù)據(jù)庫配置參數(shù) try { InputStream in = DaoFactory.class.getClassLoader().getResourceAsStream(dbconfig); prop = new Properties(); prop.load(in); } catch (IOException e) { throw new RuntimeException(e); } } /** * 返回一個(gè)UserDao的具體實(shí)現(xiàn)類 */ public static UserDao getUserDao() { String daoClassName = prop.getProperty("UserDaoClassName"); // 通過反射創(chuàng)建實(shí)現(xiàn)類的對象 try { Class Clazz = Class.forName(daoClassName); return (UserDao) Clazz.newInstance(); } catch (Exception e) { throw new RuntimeException(e); } } }
Service.java
// 添加測試 UserDao userDao = DaoFactory.getUserDao(); User user = new User(); user.setUsername("小明"); user.setAge(23); userDao.addUser(user); // 查詢測試 User user1 = userDao.getUserByUsername("小明"); System.out.println(user1);
Data -> java.sql.Data
Time -> java.sql.Time
Timestamp -> java.sql.Timestamp
領(lǐng)域?qū)ο笾兴袑傩圆荒艹霈F(xiàn)java.sql包內(nèi)容
繼承關(guān)系
java.util.Date
-java.sql.Date
父類轉(zhuǎn)子類:util.Data -> sql.Date、Time、Timestamp
java.util.Date UtilDate = new java.util.Date(); long longDate = UtilDate.getTime(); java.sql.Date sqlData = new java.sql.Date(longDate);
子類轉(zhuǎn)父類:sql.Date、Time、Timestamp -> util.Data
java.util.Date UtilDate = new java.sql.Date(System.currentTimeMillis());
可以將文件存入MySQL
my.ini配置
max_allowed_packet=10485760
批處理只針對更新(增,刪,改)
一次向服務(wù)器發(fā)送多條sql語句
開啟批處理參數(shù)
rewriteBatchedStatements=true
dbconfig.properties
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/data?rewriteBatchedStatements=true
username=root
password=123456
import util.JdbcUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; class Demo { public static void main(String[] args) throws SQLException { Connection conn = JdbcUtils.getConnection(); String sql = "insert into user(username)values(?)"; PreparedStatement statement = conn.prepareStatement(sql); for(int i=0; i<10000; i++){ statement.setString(1, "name" + i); statement.addBatch(); // 裝箱 } long start = System.currentTimeMillis(); statement.executeBatch(); // 提交數(shù)據(jù) long end = System.currentTimeMillis(); System.out.println(end - start); // 107 } }
以上是“java數(shù)據(jù)庫開發(fā)之JDBC基礎(chǔ)使用方法的示例分析”這篇文章的所有內(nèi)容,感謝各位的閱讀!相信大家都有了一定的了解,希望分享的內(nèi)容對大家有所幫助,如果還想學(xué)習(xí)更多知識,歡迎關(guān)注億速云行業(yè)資訊頻道!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。