小饶学编程之JAVA SE第二部分——05JDBC

迷南。 2022-12-21 06:16 107阅读 0赞

### JDBC ###

*  一、JDBC概述
 *  二、JDBC快速入门
 *   *  2.1 实现步骤
     *  2.2 代码实现说明
     *  2.3 JDBC事务管理
 *  三、扩展
 *   *  3.1 Statement与PrepareStatement的区别
     *  3.2 使用Batch功能
     *  3.3 SQL注入（攻击）
 *  四、dbutils
 *   *  4.1 dbutils的使用
 *  五、连接池
 *   *  5.1 获取DataSource对象
     *  5.2 使用DataSource对象
 *  六、数据库连接信息
 *  七、JDBC案例

# 一、JDBC概述 #

JDBC(Java DataBase Connectivity) java数据库连接。是一套通过java操作数据的公共**规范**。

**作用：** JAVA程序连接访问关系型数据库的。

# 二、JDBC快速入门 #

## 2.1 实现步骤 ##

1.  导入数据库jdbc驱动包
2.  配置数据库连接相关信息
3.  通过Class.forName、DriverManager加载驱动并创建Connection对象连接数据库
4.  通过Connection对象创建PrepareStatement对象发送sql到数据库并执行
5.  通过PrepareStatement设置参数（如果sql没有占位符则跳过）
6.  执行sql，获取结果
7.  关闭连接

\*\*DQL: \*\*

public class JDBCTest {
        
    
        public static void main(String[] args) throws Exception {
        
            //配置数据库连接信息
            //数据库驱动
            String driverClass = "com.mysql.jdbc.Driver";
            //数据库url
            String url = "jdbc:mysql://localhost:3306/eshop?useUnicode=true&characterEncoding=UTF-8";
            //用户名
            String user = "root";
            //密码
            String password = "123456";
    
            //加载数据库驱动
            Class.forName(driverClass);
            //创建数据库连接
            Connection conn = DriverManager.getConnection(url, user, password);
            //发送sql语句到数据库
            PreparedStatement pst = conn.prepareStatement(
            "select * from student");
            //执行sql语句并返回结果
            ResultSet rs = pst.executeQuery();
            //提取执行结果
            while(rs.next()) {
        
                System.out.println("name: " + rs.getString("stu_name") + " password: " + rs.getString(2));
            }
            conn.close();
        }
    }

\*\*DML: \*\*

public class JDBCTest {
        
    
        public static void main(String[] args) throws Exception {
        
            //配置数据库连接信息
            //数据库驱动
            String driverClass = "com.mysql.jdbc.Driver";
            //数据库url
            String url = "jdbc:mysql://localhost:3306/eshop?useUnicode=true&characterEncoding=UTF-8";
            //用户名
            String user = "root";
            //密码
            String password = "123456";
    
            //加载数据库驱动
            Class.forName(driverClass);
            //创建数据库连接
            Connection conn = DriverManager.getConnection(url, user, password);
            //发送sql语句到数据库
            PreparedStatement pst = conn.prepareStatement(
            "delete from student where stu_id = ? ");
            //执行sql语句并返回结果
            int i = pst.executeUpdate();
            //提取执行结果
            if(i>0){
        
    			System.out.println("删除成功");
    		}else{
        
    			System.out.println("删除失败");
        	}
            conn.close();
        }
    }

## 2.2 代码实现说明 ##

`Connection`接口：与特定数据库的连接(会话)。  
`PreparedStatement`接口：表示预编译的 SQL 语句的对象。SQL 语句被预编译并存储在 `PreparedStatement` 对象中。然后可以使用此对象多次高效地执行该语句。

`ResultSet`接口：表示数据库结果集的数据表，通常通过执行查询数据库的语句生成。`ResultSet` 对象具有指向其当前数据行的光标。最初，光标被置于第一行之前。`next` 方法将光标移动到下一行；因为该方法在 `ResultSet` 对象没有下一行时返回 `false`，所以可以在 `while` 循环中使用它来迭代结果集。

## 2.3 JDBC事务管理 ##

事务是数据库中非常重要的一个特性，事务属于原子性操作，是数据库执行过程中的一个逻辑单位，**要么全部成功要么全部失败**。

Connection接口中的事务操作

*  `pulic void commit()` 事务提交
 *  `pulic void rollback()` 事务回滚
 *  `pulic void setAutoCommit(boolean autoCommit)` 执行是否自动提交事务，默认自动提交事务

public class JDBCTest {
        
    
        public static void main(String[] args) throws SQLException {
        
            //配置数据库连接信息
            //数据库驱动
            String driverClass = "com.mysql.jdbc.Driver";
            //数据库url
           String url = "jdbc:mysql://localhost:3306/eshop?useUnicode=true&characterEncoding=UTF-8";
            //用户名
            String user = "root";
            //密码
            String password = "123456";
    
            Connection conn = null;
    
            try {
        
                //加载数据库驱动
                Class.forName(driverClass);
                //创建数据库连接
                conn = DriverManager.getConnection(url, user, password);
                //设置事务为手动提交
                conn.setAutoCommit(false);
                //发送sql语句到数据库
                // 添加一条数据
                PreparedStatement pst1 = conn.prepareStatement("insert into student(stu_name, stu_password, stu_age) values(?,?,?)");
                pst.setString(1, "seven");
                pst.setString(2, "123");
                pst.setInt(2, 20);
                //执行sql语句并返回结果
                int count1 = pst.executeUpdate();
                // 删除一条数据
                 PreparedStatement pst2 = conn.prepareStatement("delete from student where stu_name = ? ");
                pst.setString(1, "zhangsan");            
                //执行sql语句并返回结果
                int count2 = pst.executeUpdate();
                
                //事务提交：事务内的添加操作跟删除操作要么全部成功，要么全部失败。
                conn.commit();
                //受影响条数
                System.out.println("count: " + count);
                conn.close();
            } catch (Exception e) {
        
                e.printStackTrace();
                conn.rollback();
            }
    
        }
    }

# 三、扩展 #

## 3.1 Statement与PrepareStatement的区别 ##

1.  PreparedStatement是预编译的,对于多次执行的SQL可以大大提高效率。而Statement不行
2.  PreparedStatement可以通过占位符的方式解决SQL注入的问题，而Statement不行
3.  2者SQL的传入位置不同

//	Statement code :
       Statement statement = conn.createStatement();
       String sqlString = " delete from student where stu_name ='seven' " ;
       statement.executeUpdate(sqlString);
    //PreparedStatement code:
       PreparedStatement updateSales = conn.prepareStatement("delete from student where stu_name ='seven' "); 
    updateSales.executeUpdate();

## 3.2 使用Batch功能 ##

当执行一行的SQL，不同的参数（比如插入N条数据）的时候可以使用PreparedStatement的Batch功能。这样不管操作多少条数据都只需要2次网络往返(1次是预存储statement，1次是执行batch命令)。

**注意：**

*  事务的设置，不能使自动提交，要批量添加后才提交。
 *  在mysql 下使用批量执行的时候要在，url 后面添加手动设置支持批量添加
    
    rewriteBatchedStatements=true

//	PreparedStatement executeBatch  code :
    //1，设置手动提交事务
    conn.setAutoCommit(false);
    PreparedStatement ps = conn.prepareStatement("insert into user_blb(u_name,u_pwd) values(?,?)";
       // 2，循环设置参数，并添加到batch中
    	for (n = 0; n < 100; n++) {
        
            ps.setString(1,"seven"+i);
            ps.setString(2,"abc"+i);
            ps.addBatch();
    }
    // 3,执行批处理功能                                             
    int [] = ps.executeBatch();
    // 4，提交事务
    con.commit();
    // 5，清空batch
    pstm.clearBatch();

## 3.3 SQL注入（攻击） ##

在定义SQL的时候把用户输入的内容作为参数跟SQL拼接的话，当用户输入不合理的数据值的时候有可能导致SQL执行异常。  
例如：  
正常的登录SQL ： select \* from student where stu\_name = ‘seven’ and stu\_pwd = ‘123’ 其中seven跟123为用户从界面传入的登录账号，如果用户输入字段带’号则会异常。

如果用户输入 1’ or 1='1 时候则拼接的SQL为 select \* from student where stu\_name = ‘1’ or 1=‘1’ and stu\_pwd = ‘123’  
此时用户只知道用户名不知道密码则也可以登录成功。

再例如如果用户输入 abc’ or 1=‘1’ – （最后要有个空格）则拼接的SQL为  
select \* from student where stu\_name = ‘abc’ or 1=‘1’ – ’ and stu\_pwd = ‘123’  
则用户此时不知道用户名跟密码情况都可以登录成功。

# 四、dbutils #

dbutils是Apache公司开发的一个封装了JDBC的框架。是一种用于执行SQL语句的JAVA API，可以为多种关系数据库提供统一访问。  
下载地址 http://commons.apache.org/proper/commons-dbutils/download\_dbutils.cgi

## 4.1 dbutils的使用 ##

`新增（删除、修改类似）`

// 先获取连接
    Connection conn = DBHelper.getConn();
    // 创建QueryRunner对象
    QueryRunner qr = new QueryRunner();
    // 定义SQL
    String sql = "insert into  student(stu_name,stu_pwd) values(?,?)" ;
    // 执行SQL,获取结果
    int i = qr.update(conn, sql
                      .............................................,"古天乐","123");

`查询`

// 先获取连接
    Connection conn = DBHelper.getConn();
    // 创建QueryRunner对象
    QueryRunner qr = new QueryRunner();
    // 定义SQL
    String sql = "select * from student " ;
    // 执行查询，BeanListHandler表示把结果封装到一个Student对象的List集合中
    // Bean中的属性必须跟数据库中的字段名一样才可以映射上
    List<Student> list =  qr.query(conn, sql,new BeanListHandler<Student>(Student.class));
    DBHelper.closeConn(conn);

重点在BeanListHandler，它是`ResultSetHandler<T>`接口的实现类，每种实现类表示将结果封装成不同的对象。

*  **1 BeanListHandler将查询结果的每一行封装到Bean对象，然后再存入List集合**

String sql = "select * from student " ;
    List<Student> list =  qr.query(conn, sql,new BeanListHandler<Student>(Student.class));

*  **2 BeanHandler将查询结果的第一行数据，封装到Bean对象**

String sql = "select * from student where stu_id = ? and stu_name =? " ;
    Student bean =  qr.query(conn, sql,new BeanHandler<Student>(Student.class),101,"zhangsan");
      // 说明：如果SQL中有参数的占位符则在后面添加占位符对应的参数，个数、类型跟SQL中的？号要对应。
      // 也可以把所有参数值封装到数组中一次性传入，如：Object param = {101,"zhangsan"};然后将param在后面传入。

*  **3 ColumnListHandler 将查询结果的指定列的数据封装到List集合中**

String sql = "select stu_name from student " ;
    List<String> list = qr.query(conn, sql,new ColumnListHandler<String>());

*  **4 ScalarHandler 封装结果为单行单列的结果，类似count、avg、max、min、sum…函数**

String sql = "select count(*) from student " ;
    Object object =  qr.query(conn, sql,new ScalarHandler<Object>());

*  **5 ArrayHandler：将查询结果的第一行数据，保存到Object数组中**

String sql = "select * from student " ;
    Object [] object =  qr.query(conn, sql,new ArrayHandler());

*  **6 ArrayListHandler 将查询的结果，每一行先封装到Object数组中，然后将数据存入List集合**

String sql = "select * from student " ;
    List<Object []> list =  qr.query(conn, sql,new ArrayListHandler());

*  **7 MapHandler 将查询结果的第一行数据封装到map结合（key=列名，value=列值）**

String sql = "select * from student " ;
    Map<String,Object> map =  qr.query(conn, sql,new MapHandler());

*  **8 MapListHandler将查询结果的每一行封装到map集合（key=列名，value=列值），再将map集合存入List集合**

String sql = "select * from student " ;
    List<Map<String,Object>> list =  qr.query(conn, sql,new MapListHandler());

\*\*在上述封装中需要Bean中的属性必须跟数据库中的字段名一样才可以映射上。但是数据库字段一般带下划线，如stu\_name,而java中属性一般使用小驼峰命名，2者不一致。则需要在定义Handler的时候传入一个BasicRowProcessor对象。DButils已经默认创建了GenerousBeanProcessor可以直接下划线字段到小驼峰的转换。如： \*\*

String sql = " select * from student";
    BasicRowProcessor brp = new BasicRowProcessor(new GenerousBeanProcessor());
    List<Student> list = runner.query(conn,sql, new BeanListHandler<Student>(Student.class,brp));

如果数据库字段跟bean中的属性命名不仅仅是下划线的区别的话，则需要使用其它的Processor，如：

// 要先把数据库字段跟属性字段全部映射关系存入到Map,在定义到BeanProcessor中
    	Map<String,String> map = new HashMap<>(); 
    	map.put("stu_id","stuId");
    	map.put("stu number","stuNumber");//数据库中字段是空格，属性是小驼峰
    	map.put("stu_name","stuName");
    	map.put("stu_age","stuAge");
    	map.put("stu_sex","stuSex");
    	map.put("stu_brithday","stuBrithday");		
    
    	BasicRowProcessor brp = new BasicRowProcessor(new BeanProcessor(map));
    	List<Student1> list = runner.query(conn,sql, new BeanListHandler<Student>(Student.class,brp));

# 五、连接池 #

## 5.1 获取DataSource对象 ##

*  1 方式1

DruidDataSource ds = new DruidDataSource();
        ds.setUrl(URL);
        ds.setUsername(USER);
        ds.setPassword(PWD);
        ds.setDriverClassName(DRIVER);

*  2 方式2

Map<String,String> map = new HashMap<String,String>();
        map.put("driverClassName", DRIVER);
        map.put("url", URL);
        map.put("username", USER);
        map.put("password", PWD);
    
        DataSource ds = DruidDataSourceFactory.createDataSource(map);

*  3 方式3

driverClassName=com.mysql.jdbc.Driver
    	url=jdbc:mysql://127.0.0.1:3306/test1?useUnicode=true&characterEncoding=UTF-8
    	username=root
        password=root

Properties p = new Properties();
       InputStream is = new FileInputStream(new File("src/datasource.properties"));
       p.load(is);
       DataSource ds = DruidDataSourceFactory.createDataSource(p);

## 5.2 使用DataSource对象 ##

QueryRunner qr = new QueryRunner(ds);
    	String sql = " select * from student " ;
    	List<Student> list = qr.query(sql, new BeanListHandler<Student>(Student.class));

QueryRunner qr = new QueryRunner( );
    	String sql = " select * from student " ;
    	List<Student> list = qr.query(ds.getConnection(),sql, new  	  BeanListHandler<Student>(Student.class));

如果不需要DataSource对象，而需要原始的Connection对象的话也可以通过DataSource的getConnection()方法获取连接对象。

# 六、数据库连接信息 #

//sqlserver
    private static final String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; 
    private static final String URL = "jdbc:sqlserver://127.0.0.1:1433;database=apsfcDB";
    private static final String USER = "sa";
    private static final String PWD = "123456";
    
    
    // oracle
    private static final String DRIVER = "oracle.jdbc.driver.OracleDriver"; 
    private static final String URL = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
    private static final String USER = "scott";
    private static final String PWD = "tiger";
    
    
    //mysql
    private static final String DRIVER = "com.mysql.jdbc.Driver"; 
    private static final String URL = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8";
    private static final String USER = "root";
    private static final String PWD = "admin";

# 七、JDBC案例 #

[06JDBC案例][06JDBC]

[06JDBC]: https://blog.csdn.net/weixin_46460843/article/details/109716874