JDBC的原理及使用--自学笔记

朴灿烈づ我的快乐病毒、 2021-12-15 22:49 196阅读 0赞

**1. jdbc是什么**  
*jdbc（Java DataBase Connectivity）是JAVA数据库链接。*  
**2. jdbc怎么用**

*  导入jar包  
    ![在这里插入图片描述][20190703131225345.png]
 *  加载驱动

//加载驱动类
    			Class.forName("com.mysql.jdbc.Driver");

*  建立链接

String url = "jdbc:mysql://localhost:3306/company";
    			String user = "root";
    			String password = "123456";
    			Connection con = DriverManager.getConnection(url, user, password);

*  执行sql语句，获取结果集

//建立语句对象
    			String sql = "SELECT * FROM t_dept";
    			PreparedStatement preparedStatement = con.prepareStatement(sql);
    			ResultSet rs = preparedStatement.executeQuery(sql);

执行sql语句有两种形式
    (1)  Statement
    		安全性差，会有sql注入的情况发生
    (2)  PreparedStatement
    		安全性强，避免发生sql注入

*  从结果集中取结果

String deptno = null;
    			String dname = null;
    			String loc = null;
    			String des = null;
    			
    			while (rs.next()) { 
    				deptno = rs.getString("deptno"); 
    				dname = rs.getString("dname");
    				loc = rs.getString("loc");
    				des = rs.getString("des");
    				System.out.println(deptno+"\t"+dname+"\t"+loc+"\t"+des);
    			}

**事务处理**
    		jabc默认自动提交事务，如果我们需要进行事务处理，可以在sql执行语句前加入语句
    		
    
    ```
    con.setAutoCommit(false);//手动提交事务
    ```
    		sql语句执行后进行事务提交语句
    		
    
    ```
    con.commit();
    ```

**3. jdbc工具类之dbcp**

*不常用，在此不做介绍*

**4. jdbc工具之c3p0**  
方便使用的工具类，自动生成连接池

*  导包  
    ![在这里插入图片描述][20190703133111117.png]
 *  使用

package cn.wf.mysql;
    
    import java.beans.PropertyVetoException;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.ArrayList;
    
    import com.mchange.v2.c3p0.ComboPooledDataSource;
    
    
    /** * JAVA通过C3P0连接MySQL数据库 * @author WF * */
    
    public class JDBCMySQL_c3p0 { 
    
    	public static void main(String[] args) { 
    		//声明
    		Connection con = null;
    		//结果集
    		ResultSet rs = null;
    		String driver = "com.mysql.jdbc.Driver";
    		String url = "jdbc:mysql://localhost:3306/world";
    		String user = "root";
    		String password = "";
    		
    		ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
    		try { 
    				comboPooledDataSource.setDriverClass(driver);
    				comboPooledDataSource.setJdbcUrl(url);
    				comboPooledDataSource.setUser(user);
    				comboPooledDataSource.setPassword(password);
    				con = comboPooledDataSource.getConnection();
    				//建立语句对象
    				String sql = "SELECT * FROM city";
    				PreparedStatement preparedStatement = con.prepareStatement(sql);
    				rs = preparedStatement.executeQuery(sql);
    				
    				String Name = null;
    				String CountryCode = null;
    				String District = null;
    				String Population = null;
    				
    				while (rs.next()) { 
    					Name = rs.getString("Name"); 
    					CountryCode = rs.getString("CountryCode");
    					District = rs.getString("District");
    					Population = rs.getString("Population");
    					System.out.println(Name+"\t"+CountryCode+"\t"+District+"\t"+Population);
    				}
    			} catch (PropertyVetoException e) { 
    				e.printStackTrace();
    			} catch (SQLException e) { 
    				e.printStackTrace();
    			}
    			
    
    				
    
    	}
    
    }

[20190703131225345.png]: /images/20211215/d12b25c2bb3c431bb93decd01fe89c36.png
[20190703133111117.png]: /images/20211215/e7225fbfa1c546a9aa239c04976f0d84.png