您好,登錄后才能下訂單哦!
JDBC簡介
JDBC全稱為:Java Data Base Connectivity (java數(shù)據(jù)庫連接),可以為多種數(shù)據(jù)庫提供填統(tǒng)一的訪問。JDBC是sun開發(fā)的一套數(shù)據(jù)庫訪問編程接口,是一種SQL級的API。它是由java語言編寫完成,所以具有很好的跨平臺特性,使用JDBC編寫的數(shù)據(jù)庫應(yīng)用程序可以在任何支持java的平臺上運行,而不必在不同的平臺上編寫不同的應(yīng)用程序。
JDBC編程步驟
(1)加載驅(qū)動程序:
下載驅(qū)動包 : http://dev.mysql.com/downloads/connector/j/
解壓,得到 jar文件。將該文件復制到Java工程目錄Java Resources/Libraries/ 下,→ buildpath 。
(2)獲得數(shù)據(jù)庫連接
(3)創(chuàng)建Statement對象:
(4)向數(shù)據(jù)庫發(fā)送SQL命令
(5)處理數(shù)據(jù)庫的返回結(jié)果(ResultSet類)
package com.baidu.emp.jdbcTest; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; import com.mysql.jdbc.Driver; /** * 開始使用jdbc連接數(shù)據(jù)庫 * @author Admin * */ public class Test001 { public static void main(String[] args) throws Exception { /** * 加載驅(qū)動 */ // 方法一: /* * import java.sql.DriverManager; import com.mysql.jdbc.Driver; */ // Driver driver = new Driver(); // DriverManager.registerDriver(driver); // 方法二:(推薦使用) Class.forName("com.mysql.jdbc.Driver"); /** * 創(chuàng)建鏈接 */ String url = "jdbc:mysql://localhost:3306/testjdbc"; String user = "root"; String password = "root"; Connection connection = DriverManager.getConnection(url, user, password); // 創(chuàng)建statement對象 Statement statement = connection.createStatement(); /** * 執(zhí)行SQL,獲取結(jié)果集 */ String sql = "select * from test01"; ResultSet result = statement.executeQuery(sql); // 遍歷結(jié)果集 while (result.next()) { String name = result.getString("name"); int id = result.getInt("id"); System.out.println(name + "\t" + id); } /** * 關(guān)閉鏈接,釋放資源 */ result.close(); statement.close(); connection.close(); } }
防止SQL注入改用prepareStatement
package com.boya.emp.jdbcTest; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; /** * SQL注入,使用prepareStatement對象進行預編譯 * @author Admin * */ public class Test002 { public static void main(String[] args) throws Exception { /** * 加載驅(qū)動 */ Class.forName("com.mysql.jdbc.Driver"); /** * 創(chuàng)建鏈接 */ String url = "jdbc:mysql://localhost:3306/testjdbc"; String user = "root"; String password = "root"; Connection connection = DriverManager.getConnection(url, user, password); // 寫SQL String sql = "select * from test01 where id = ?"; //創(chuàng)建statement對象,預編譯 PreparedStatement statement = connection.prepareStatement(sql); //設(shè)置參數(shù) statement.setInt(1, 2); /** * 執(zhí)行SQL,獲取結(jié)果集 */ ResultSet result = statement.executeQuery(); // 遍歷結(jié)果集 while (result.next()) { String name = result.getString("name"); int id = result.getInt("id"); System.out.println(name + "\t" + id); } /** * 關(guān)閉鏈接,釋放資源 */ result.close(); statement.close(); connection.close(); } }
進行代碼優(yōu)化,設(shè)置配置文件,工具類,實現(xiàn)增刪該查
增加配置文件方便修改數(shù)據(jù)庫,用戶登錄。。。
jdbc.properties(配置文件名)
driverName=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/testjdbc userName=root password=root
注意寫配置文件時中間不可以有空格,引號之類的
工具類:增強了代碼的復用性
package com.baidu.emp.utils; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Properties; import org.junit.Test; public class JdbcUtils { static String driverClassName; static String url; static String user; static String password; static { // 創(chuàng)建配置文件對象 Properties properties = new Properties(); // 加載配置文件輸入流 InputStream inputStream = JdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties"); // 重新加載配置文件 try { properties.load(inputStream); // 獲取配置文件的值 driverClassName = properties.getProperty("driverName"); url = properties.getProperty("url"); user = properties.getProperty("userName"); password = properties.getProperty("password"); Class.forName(driverClassName); } catch (Exception e) { // 拋出異常 throw new RuntimeException(e); } } /** * 獲取連接 */ @Test public void testName() throws Exception { System.out.println(driverClassName); } public static Connection getConnection() { Connection connection = null; try { connection = DriverManager.getConnection(url, user, password); } catch (SQLException e) { // 拋出異常 throw new RuntimeException(e); } return connection; } /** * 關(guān)閉鏈接,釋放資源 */ public static void close(Connection connection, PreparedStatement statement, ResultSet resultSet) { try { if (resultSet != null) { resultSet.close(); } resultSet = null; // 垃圾及時清除 //注意,不要弄成死循環(huán) close(connection, statement); } catch (SQLException e) { throw new RuntimeException(e); } } /** * 增刪改釋放資源 */ public static void close(Connection connection, PreparedStatement statement) { try { if (connection != null) { connection.close(); } connection = null; if (statement != null) { statement.close(); } statement = null; } catch (SQLException e) { throw new RuntimeException(e); } } }
測試增刪改查:
package com.baidu.emp.jdbcTest; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import org.junit.After; import org.junit.Before; import org.junit.Test; import com.baidu.emp.utils.JdbcUtils; /** * 使用jdbcUtils連接數(shù)據(jù)庫進行增刪改查 * * @author Admin * */ public class Test003 { // 初始化值 Connection connection = null; PreparedStatement statement = null; ResultSet result = null; @Before public void start() throws Exception { // 創(chuàng)建鏈接 connection = JdbcUtils.getConnection(); System.out.println("創(chuàng)建鏈接"); } @After public void end() throws Exception { // 關(guān)閉鏈接 JdbcUtils.close(connection, statement, result); System.out.println("關(guān)閉鏈接"); } /** *插入數(shù)據(jù) * @throws Exception */ @Test public void add() throws Exception { String sql = "insert into test01 values(null,?)"; statement = connection.prepareStatement(sql); statement.setString(1, "李四"); int result = statement.executeUpdate(); if (result!=0) { System.out.println("添加成功"); } } /** * 刪除數(shù)據(jù) * @throws Exception */ @Test public void del() throws Exception { String sql = "delete from test01 where id =?"; statement = connection.prepareStatement(sql); statement.setInt(1,3); int result = statement.executeUpdate(); if (result!=0) { System.out.println("刪除成功"); } } /** * 修改數(shù)據(jù) * @throws Exception */ @Test public void change() throws Exception { String sql = "update test01 set name = ? where id = ?"; statement = connection.prepareStatement(sql); statement.setString(1, "張飛"); statement.setInt(2, 2); int result = statement.executeUpdate(); if (result!=0) { System.out.println("修改成功"); } } /** * 查詢?nèi)繑?shù)據(jù) * @throws Exception */ @Test public void findAll() throws Exception { String sql = "select id , name from test01"; statement = connection.prepareStatement(sql); result = statement.executeQuery(); if (result.next()) { System.out.println("查詢成功"); } } /** * 條件查詢數(shù)據(jù) * @throws Exception */ @Test public void findOne() throws Exception { String sql = "select id , name from test01 where id = ?"; statement = connection.prepareStatement(sql); statement.setInt(1, 2); result = statement.executeQuery(); if (result.next()) { System.out.println("查詢成功"); } } }
以上就是相關(guān)知識以及相關(guān)代碼,感謝大家對億速云的支持。
免責聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。