SQLyog基本操作（十三）-JDBC

10.1 什么是JDBC？

　　JDBC（Java Data Base Connectivity,java数据库连接）是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成。JDBC提供了一种基准，据此可以构建更高级的工具和接口，使数据库开发人员能够编写数据库应用程序。

10.2 数据库驱动

　　我们安装好数据库之后，我们的应用程序不能直接使用数据库，必须要通过相应的数据库驱动程序，通过驱动程序去和数据库打交道。SUN公司为了简化开发人员的操作（对数据库的统一），提供了一个规范（Java操作数据库的规范），俗称JDBC，这些规范的实现由具体的厂商去做。对与开发人员来说，我么只需要掌握JDBC接口的操作即可。其实也就是利用数据库厂商的JDBC接口实现，即对Connection等接口的实现类的jar文件。

10.3 常用接口

10.3.1 Driver接口

　　Driver接口由数据库厂家提供，作为java开发人员，只需要使用Driver接口就可以了。在编程中要连接数据库，必须先装载特定厂商的数据库驱动程序，不同的数据库有不同的装载方法。如：

• 装载MySql驱动：Class.forName("com.mysql.jdbc.Driver");

• 装载Oracle驱动：Class.forName("oracle.jdbc.driver.OracleDriver");

10.3.2 Connection接口

　　Connection与特定数据库的连接（会话），在连接上下文中执行sql语句并返回结果。

　　DriverManager.getConnection(url, user, password)方法建立在JDBC URL中定义的数据库Connection连接上。

• 连接MySql数据库：Connection conn = DriverManager.getConnection("jdbc:mysql://host:port/database", "user", "password");

• 连接Oracle数据库：Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@host:port:database", "user", "password");

• 连接SqlServer数据库：Connection conn = DriverManager.getConnection("jdbc:microsoft:sqlserver://host:port; DatabaseName=database", "user", "password");

常用方法：

• createStatement()：创建向数据库发送sql的statement对象。

• prepareStatement(sql) ：创建向数据库发送预编译sql的PrepareSatement对象。

• prepareCall(sql)：创建执行存储过程的callableStatement对象。

• setAutoCommit(boolean autoCommit)：设置事务是否自动提交。

• commit() ：在链接上提交事务。

• rollback() ：在此链接上回滚事务。

10.3.3 Statement接口

　　用于执行静态SQL语句并返回它所生成结果的对象。

三种Statement类：

• Statement：由createStatement创建，用于发送简单的SQL语句（不带参数）。

• PreparedStatement ：继承自Statement接口，由preparedStatement创建，用于发送含有一个或多个参数的SQL语句。PreparedStatement对象比Statement对象的效率更高，并且可以防止SQL注入，所以我们一般都使用PreparedStatement。

• CallableStatement：继承自PreparedStatement接口，由方法prepareCall创建，用于调用存储过程。

常用Statement方法：

• execute(String sql)：运行语句，返回是否有结果集

• executeQuery(String sql)：运行select语句，返回ResultSet结果集。

• executeUpdate(String sql)：运行insert/update/delete操作，返回更新的行数。

• addBatch(String sql) ：把多条sql语句放到一个批处理中。

• executeBatch()：向数据库发送一批sql语句执行。

10.3.4 ResultSet接口

ResultSet提供检索不同类型字段的方法，常用的有：

• getString(int index)、getString(String columnName)：获得在数据库里是varchar、char等类型的数据对象。

• getFloat(int index)、getFloat(String columnName)：获得在数据库里是Float类型的数据对象。

• getDate(int index)、getDate(String columnName)：获得在数据库里是Date类型的数据。

• getBoolean(int index)、getBoolean(String columnName)：获得在数据库里是Boolean类型的数据。

• getObject(int index)、getObject(String columnName)：获取在数据库里任意类型的数据。

ResultSet还提供了对结果集进行滚动的方法：

• next()：移动到下一行

• previous()：移动到前一行

• absolute(int row)：移动到指定行

• beforeFirst()：移动resultSet的最前面。

• afterLast() ：移动到resultSet的最后面。

使用后依次关闭对象及连接：ResultSet → Statement → Connection

10.4 使用JDBC的步骤

　　加载JDBC驱动程序 → 建立数据库连接Connection → 创建执行SQL的语句Statement → 处理执行结果ResultSet → 释放资源

10.4.1 注册驱动 (只做一次)

　　方式一：Class.forName(“com.MySQL.jdbc.Driver”); //推荐这种方式，不会对具体的驱动类产生依赖。

　　方式二：DriverManager.registerDriver(com.mysql.jdbc.Driver); //会造成DriverManager中产生两个一样的驱动，并会对具体的驱动类产生依赖。

10.4.2 建立连接

 Connection conn = DriverManager.getConnection(url, user, password); 

　　URL用于标识数据库的位置，通过URL地址告诉JDBC程序连接哪个数据库，URL的写法为：

　　其他参数如：useUnicode=true&characterEncoding=utf8

10.4.3 创建执行SQL语句的statement

 //Statement
 String id = "5";
 String sql = "delete from table where id=" +  id;
 Statement st = conn.createStatement();
 st.executeQuery(sql);
 //存在sql注入的危险
 //如果用户传入的id为“5 or 1=1”，那么将删除表中的所有记录
 ​
 //PreparedStatement 有效的防止sql注入(SQL语句在程序运行前已经进行了预编译,
 //当运行时动态地把参数传给PreprareStatement时，即使参数里有敏感字符如 or '1=1'
 //也会把数据库作为一个参数一个字段的属性值来处理而不会作为一个SQL指令)
 String sql = “insert into user (name,pwd) values(?,?)”;
 PreparedStatement ps = conn.preparedStatement(sql);
 ps.setString(1, “col_value”);//占位符顺序从1开始
 ps.setString(2, “123456”); //也可以使用setObject
 ps.executeQuery();

10.4.4 处理执行结果(ResultSet)

 ResultSet rs = ps.executeQuery();  
 While(rs.next()){  
      rs.getString(“col_name”);  
      rs.getInt(1);  
      //…
 }  

10.4.5 释放资源

 //数据库连接（Connection）非常耗资源，尽量晚创建，尽量早的释放 
 //都要加try catch 以防前面关闭出错，后面的就不执行了 
 try {
      if (rs != null) {
          rs.close();
      }
 } catch (SQLException e) {
      e.printStackTrace();
 } finally {
      try {
          if (st != null) {
              st.close();
          }
      } catch (SQLException e) {
          e.printStackTrace();
      } finally {
          try {
              if (conn != null) {
                  conn.close();
              }
          } catch (SQLException e) {
              e.printStackTrace();
          }
      }
 }

10.5 编写第一个JDBC程序

10.5.1 准备工作

　　利用Java开发数据库时需要java.sql、javax.sql（Java自带），此外还需要导入一个数据库驱动包：mysql-connector-java-5.1.47.jar.

　　所有版本下载地址：https://mvnrepository.com/artifact/mysql/mysql-connector-java

　　5.1.47版本下载地址：https://mvnrepository.com/artifact/mysql/mysql-connector-java/5.1.47

10.5.2 在SQLyog上面创建测试数据库、数据表

测试代码：

 -- 创建测试数据库 jdbcStudy
 CREATE DATABASE `jdbcStudy` CHARACTER SET utf8 COLLATE utf8_general_ci;
 -- 使用jdbcStudy数据库
 USE `jdbcStudy`;
 -- 创建users表
 CREATE TABLE `users`(
   `id` INT PRIMARY KEY,
   `name` VARCHAR(40),
   `password` VARCHAR(40),
   `email` VARCHAR(60),
   `birthday` DATE
 );
 -- 插入数据
 INSERT INTO `users`(`id`,`name`,`password`,`email`,`birthday`)VALUES
 (1,'zhangsan','123456','zs@sina.com','1980-12-04'),
 (2,'lisi','123456','lisi@sina.com','1981-12-04'),
 (3,'wangwu','123456','wangwu@sina.com','1979-12-04')

SELECT * FROM `user`;显示结果：

10.5.3 在IDEA上创建一个普通项目（一路next），命名为JDBC

　　新建一个lib路径，将上面下载好的mysql-connector-java-5.1.47.jar复制到该路径下，然后在lib文件夹上右键Add as Library...将该jar包添加到External Libraries中。

生成结果如下：

　　接着在src文件下新建Package：com.kuang.lesson01，里面新建Class：JdbcDemo01，接下来在该java文件中编写代码：

10.5.4 导入数据库驱动

 package com.kuang.lesson01;
 ​
 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
 ​
 /**
  * 我的第一个JDBC程序
  */
 public class JdbcDemo01 {
     public static void main(String[] args) throws ClassNotFoundException, SQLException {
         //1.加载驱动
         Class.forName("com.mysql.jdbc.Driver");//固定写法，加载驱动
 ​
         //2.添加url和用户信息（与SQLyog建立新连接登录时输入的“我的SQL主机地址”、“用户名”、“密码”相一致）
         //jdbc:mysql://主机地址　　端口　　数据库名　　设置编码方式　　建立安全连接
         String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
         String username = "root";//用户名
         String password = "123456";//密码
 ​
         //3.根据url和用户信息进行连接，连接成功后获取数据库对象      Connection代表数据库
         Connection connection = DriverManager.getConnection(url,username,password);
 ​
         //4.执行SQL对象    Statement表示执行的sql对象
         Statement statement = connection.createStatement();
 ​
         //5.执行SQL的对象去执行SQL，可能存在结果，查看返回结果
         String sql = "SELECT * FROM `users`";//对应SQL中的查询语句，语句正确性已验证
         //返回的结果集中封装了我们全部查询出来的结果
         ResultSet resultSet = statement.executeQuery(sql);
         //查看结果：由于返回的结果是Set，需要先判断是否含有下一元素，再进行读取操作
         while (resultSet.next()){
             //根据对应的名称获得对应的结果
             System.out.println("id:"+resultSet.getObject("id"));
             System.out.println("name:"+resultSet.getObject("name"));
             System.out.println("password:"+resultSet.getObject("password"));
             System.out.println("email:"+resultSet.getObject("email"));
             System.out.println("birthday:"+resultSet.getObject("birthday"));
             System.out.println("===================================================");
         }
 ​
         //6.使用完毕后，断开连接，释放资源
         resultSet.close();
         statement.close();
         connection.close();
     }
 }
 ​

输出结果：

 id:1
 name:zhangsan
 password:123456
 email:zs@sina.com
 birthday:1980-12-04
 ===================================================
 id:2
 name:lisi
 password:123456
 email:lisi@sina.com
 birthday:1981-12-04
 ===================================================
 id:3
 name:wangwu
 password:123456
 email:wangwu@sina.com
 birthday:1979-12-04
 ===================================================

输出上述结果，代表连接成功。

10.5.5 步骤总结

1、加载驱动

 //DriverManager.registerDriver(new com.mysql.jdbc.Driver());//里面包含了注册
 Class.forName("com.mysql.jdbc.Driver");//固定写法，使用这个可以直接加载驱动

2、添加url和用户信息

//jdbc:mysql://主机地址:端口/数据库名?参数1&参数2&参数3        设置字符集编码格式        建立安全连接
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
//MySQL默认端口号3306：jdbc:mysql://主机地址:端口号/数据库名?参数1&参数&参数3
//Oracle默认端口号1521：jdbc:oracle:thin:@localhost:1521:sid
String username = "用户名";//用户名
String password = "密码";//密码

附：SQLyog中的url和用户信息

3、根据url和用户信息进行连接，连接成功后获取数据库对象

 Connection connection = DriverManager.getConnection(url,username,password);
 //Connection代表数据库，可设置自动提交、事务提交、事务回滚
 //connection.setAutoCommit();
 //connection.commit();
 //connection.rollback();

4、获得执行sql的对象

 //Statement表示执行的sql对象
 Statement statement = connection.createStatement();
 //connection.createStatement();
 //connection.prepareStatement()

5、执行SQL的对象去执行SQL，可能存在结果，查看返回结果

 String sql = "SELECT * FROM `users`";//对应SQL中的查询语句，语句正确性已验证
 //ResultSet:查询的结果集，封装了所有的查询结果
 ResultSet resultSet = statement.executeQuery(sql);
 //statement.executeQuery();//查询操作，返回ResultSet
 //statement.executeUpdate();//更新、插入、删除，都是用这个，返回一个受影响的行数
 //statement.execute();//执行任何SQL
 //statement.executeBatch();//批处理执行
 ​
 //查看结果：由于返回的结果是Set，需要先判断是否含有下一元素，再进行读取操作
 while (resultSet.next()){
　　　　resultSet.beforeFirst();//移动到最前面
　　　　resultSet.afterLast();//移动到最后面
　　　　resultSet.next();//移动到下一个数据
　　　　resultSet.previous();//移动到前一行
　　　　resultSet.absolute(row);//移动到指定行
 ​
　　　　//resultSet.getObject();//在不知道列类型的情况下使用
　　　　//如果知道列类型就使用指定的类型
　　　　//resultSet.getString();
　　　　//resultSet.getInt();
　　　　//resultSet.getDouble();
　　　　//resultSet.getDate();
　　　　//根据对应的名称获得对应的结果
　　　　System.out.println("id:"+resultSet.getObject("id"));
　　　　System.out.println("name:"+resultSet.getObject("name"));
　　　　System.out.println("password:"+resultSet.getObject("password"));
　　　　System.out.println("email:"+resultSet.getObject("email"));
　　　　System.out.println("birthday:"+resultSet.getObject("birthday"));
　　　　System.out.println("===================================================");
 }

6、使用完毕后，断开连接，释放资源(必须关闭，占用不关闭占用很多资源)

 resultSet.close();
 statement.close();
 connection.close();

参考链接：JDBC详解 - ErBing - 博客园 https://www.cnblogs.com/erbing/p/5805727.html