JDBC数据库连接基础

使用Java语言操作关系型数据库的一套API，( Java DataBase Connectivity ) Java 数据库连接

一、JDBC快速入门
基本流程: 编写代码 -->将SQL发送到MySQL服务端 -->接收并执行SQL语句 -->将结果返回到java

public class JDBCDemo {
public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");// 注册驱动
        String url = "jdbc:mysql://127.0.0.1:3306/db1";// 获取连接
        String username = "root";
        String password = "root";
        Connection conn =DriverManager.getConnection(url, username, password);
        String sql = "update account set money = 2000 where id = 1";// 定义sql
        Statement stmt = conn.createStatement();//获取执行sql的对象 Statement
        int count = stmt.executeUpdate(sql);//受影响的行数//执行sql
        System.out.println(count);//处理结果
        stmt.close();//释放资源
        conn.close();
   }
}

二、JDBC API详情
1. DriverManager(驱动管理类)
1.1 注册驱动
registerDriver方法是用于注册驱动的，Driver的源代码是包含它的，之后的驱动包可以省去Class.forName("com.mysql.jdbc.Driver");注册步骤。
1.2 获取数据库连接
url ： 连接路径
语法：jdbc:mysql://ip地址(域名):端口号/数据库名称?参数键值对1&参数键值对2…
示例：jdbc:mysql://127.0.0.1:3306/db1
如果连接的是本机mysql服务器，并且mysql服务默认端口是3306，则url可以简写为：jdbc:mysql:///数据库名称?参数键值对
配置 useSSL=false 参数，禁用安全连接方式，解决警告提示
user ：用户；poassword ：密码
2. Connection(数据库连接对象)
1.1 获取执行对象
Statement createStatement() //普通执行SQL对象
PreparedStatement prepareStatement(sql) //预编译SQL的执行SQL对象：防止SQL注入
1.2 事务管理
setAutoCommit(boolean autoCommit)//开启事务
参与autoCommit 表示是否自动提交事务，true表示自动提交事务，false表示手动提交事务。而开启事务需要将该参数设为为false。
commit()//提交事务；rollback()//回滚事务

 public class JDBCDemo3_Connection {
   public static void main(String[] args) throws Exception {
       //Class.forName("com.mysql.jdbc.Driver");
       //如果连接的是本机mysql并且端口是默认的 3306 可以简化书写
       String url = "jdbc:mysql:///db1? useSSL=false";
       String username = "root";
       String password = "1234";
       Connection conn = DriverManager.getConnection(url, username, password);
String sql1 = "update account set money = 3000 where id = 1";
       String sql2 = "update account set money = 3000 where id = 2";
       //4. 获取执行sql的对象 Statement
       Statement stmt = conn.createStatement();
       try {
           conn.setAutoCommit(false); // 开启事务
           int count1 = stmt.executeUpdate(sql1);//受影响的行数
           System.out.println(count1);
           int i = 3/0;
           int count2 = stmt.executeUpdate(sql2);//受影响的行数
           System.out.println(count2);
           //程序运行到此处，说明没有出现任何问题，则需求提交事务
           conn.commit();
       } catch (Exception e) {
           //程序在出现异常时会执行到这个地方，此时就需要回滚事务
           conn.rollback();
           e.printStackTrace();
       }
       stmt.close();
       conn.close();
    }
}    

3. statement(执行SQL语句)
int executeUpdata(String sql)//执行DDL、DML语句
ResultSet executrQuery(String sql)//执行DQL语句

//执行DML语句
String sql = "update account set money = 3000 where id = 1";
Statement stmt = conn.createStatement();
int count = stmt.executeUpdate(sql);
if(count > 0){
     System.out.println("修改成功~");
}else{
     System.out.println("修改失败~");
} 

//执行DDL语句(以后少用)
String sql = "drop database db2";
Statement stmt = conn.createStatement();
int count = stmt.executeUpdate(sql);//执行完DDL语句，可能是0

4. ResultSet(结果集对象)
ResultSet executeQuery(sql);//执行DQL 语句，返回ResultSet 对象
boolean next()//将光标从当前位置向前移动一行，判断当前行是否有效行
方法返回值说明：true有效行，当前行有数据；false无效行，当前行没有数据
xxx getXxx(参数)//获取数据
xxx : 数据类型；如： int getInt(参数) ；String getString(参数)
参数 : int类型的参数 : 列的编号，从1开始; String类型的参数 : 列的名称

一开始光标指定于第一行前，\红色箭头指向于表头行。调用 next() 方法后，光标就下移到第一行数据，并且方法返回true，此时就可以通过 getInt("id") 获取当前行id字段的值，也可以通过 getString("name") 获取当前行name字段的值。如果想获取下一行的数据，继续调用 next() 方法。

String sql = "select * from account";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
List<Account> list = new ArrayList<>();
while (rs.next()){ //获取数据 getXxx()
        Account account = new Account();
        int id = rs.getInt("id");
        String name = rs.getString("name");
        double money = rs.getDouble("money");
        account.setId(id);
        account.setName(name);
        account.setMoney(money);
        list.add(account);
} 

5.  PreparedStatement(预编译SQL语句并执行：预防SQL注入问题)

获取PreparedStatement对象，将sql语句发送给mysql服务器进行检查，编译（这些步骤很耗时）
执行时就不用再进行这些步骤了，速度更快
如果sql模板一样，则只需要进行一次检查、编译
1.1 代码模拟SQL注入问题

@Test
public void testLogin() throws  Exception {
    //获取连接：如果连接的是本机mysql并且端口是默认的 3306 可以简化书写
    String url = "jdbc:mysql:///db1?useSSL=false";
    String username = "root";
    String password = "1234";
    Connection conn = DriverManager.getConnection(url, username, password);
    String name = "sjdljfld";
    String pwd = "' or '1' = '1";
    String sql = "select * from tb_user where username = '"+name+"' and password = '"+pwd+"'";
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery(sql);
    if(rs.next()){
        System.out.println("登录成功~");
    }else{
        System.out.println("登录失败~");
    }
    rs.close();
    stmt.close();
    conn.close();
}

 分析：代码是将用户名和密码拼接到sql语句中的
select * from tb_user where username = 'sjdljfld' and password = ''or '1' = '1'
//or后面的 '1' = '1'是始终满足的
1.2 preparedStatement概述
1.1.1 获取 PreparedStatement 对象
// SQL语句中的参数值，使用？占位符替代
String sql = "select * from user where username = ? and password = ?";
// 通过Connection对象获取，并传入对应的sql语句
PreparedStatement pstmt = conn.prepareStatement(sql);
1.1.2 设置参数值
PreparedStatement对象：setXxx(参数1，参数2)：给 ? 赋值
Xxx：数据类型 ； 如 setInt (参数1，参数2)
参数1： ？的位置编号，从1 开始；参数2： ？的值
1.1.3 执行SQL语句
executeUpdate();  执行DDL语句和DML语句
executeQuery();  执行DQL语句
注意：调用这两个方法时不需要传递SQL语句，因为获取SQL语句执行对象时已经对SQL语句进行预编译了。

@Test
public void testPreparedStatement() throws  Exception {
    //获取连接：如果连接的是本机mysql并且端口是默认的 3306 可以简化书写
   // useServerPrepStmts=true 参数开启预编译功能
    String url = "jdbc:mysql:///db1?useSSL=false&useServerPrepStmts=true";
    String username = "root";
    String password = "1234";
    Connection conn = DriverManager.getConnection(url, username, password);  
    String name = "zhangsan";
    String pwd = "' or '1' = '1";
    String sql = "select * from tb_user where username = ? and password = ?";
    PreparedStatement pstmt = conn.prepareStatement(sql);
    pstmt.setString(1,name);
    pstmt.setString(2,pwd);
    ResultSet rs = pstmt.executeQuery();
    if(rs.next()){
        System.out.println("登录成功~");
    }else{
        System.out.println("登录失败~");
    }
    rs.close();
    pstmt.close();
    conn.close();
}

select * from where username = 'sjdljfld' and password = '\'or \'1\' = \'1'//将特殊字符进行了转义( ' )

三、数据库连接池
数据库连接池是个容器，负责分配、管理数据库连接(Connection)
它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个；
释放空闲时间超过最大空闲时间的数据库连接来避免，没有释放会引起的数据库连接遗漏
(1)资源重用；(2)提升系统响应速度；(3)避免数据库连接遗漏
在一开始就创建好了一些连接（Connection）对象存储起来。
Connection getConnection()//数据库连接池实现
使用更多的是Druid连接池(需要导入jar包 druid-1.1.12.jar，和properties文件)

//编写配置文件
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///db1?useSSL=false&useServerPrepStmts=true
username=root
password=1234
# 初始化连接数量
initialSize=5
# 最大连接数
maxActive=10
# 最大等待时间
maxWait=3000 
 

//使用代码

Properties prop = new Properties();//加载配置文件
prop.load(new FileInputStream("jdbc-demo/src/druid.properties"));
//获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);

//获取数据库连接 Connection
Connection connection = dataSource.getConnection();
System.out.println(connection); //获取到了连接后就可以继续做其他操作了
//后面接conn.prepareStatement(sql);

四、JDBC练习
1. 查询所有

String sql = "select * from tb_brand;";
PreparedStatement pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
Brand brand = null;
List<Brand> brands = new ArrayList<>();
while (rs.next()){
        int id = rs.getInt("id");
        String brandName = rs.getString("brand_name");
        String companyName = rs.getString("company_name");
        int ordered = rs.getInt("ordered");
        String description = rs.getString("description");
        int status = rs.getInt("status");
        //封装Brand对象
        brand = new Brand();
        brand.setId(id);
        brand.setBrandName(brandName);
        brand.setCompanyName(companyName);
        brand.setOrdered(ordered);
        brand.setDescription(description);
        brand.setStatus(status);
        brands.add(brand);
}

 2. 添加数据

String sql = "insert into tb_brand(brand_name, company_name, ordered, description, status) values(?,?,?,?,?);";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1,brandName);
pstmt.setString(2,companyName);
pstmt.setInt(3,ordered);
pstmt.setString(4,description);
pstmt.setInt(5,status);

int count = pstmt.executeUpdate(); 
System.out.println(count > 0);

3. 修改数据 

String sql = " update tb_brand\n" +
        "         set brand_name  = ?,\n" +
        "         company_name= ?,\n" +
        "         ordered     = ?,\n" +
        "         description = ?,\n" +
        "         status      = ?\n" +
        "     where id = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1,brandName);//前面给出了值定义
pstmt.setString(2,companyName);
pstmt.setInt(3,ordered);
pstmt.setString(4,description);
pstmt.setInt(5,status);
pstmt.setInt(6,id);
int count = pstmt.executeUpdate(); // 影响的行数
System.out.println(count > 0);

4. 删除数据 

String sql = " delete from tb_brand where id = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,id);
int count = pstmt.executeUpdate(); 
System.out.println(count > 0);