JDBC笔记

JDBC

概念：JDBC(java DataBase connectivity ) java数据库连接，java语言操作数据库。
JDBC本质：其实是官方(sun)公司定义的一套操作所有的关系型数据库，即接口。各个数据库厂商去实现这套接口，提供数据库驱动jar包，我们可以使用这套接口（JDBC）编程，真正执行的代码是驱动jar包中的实现类。

快速入门：

步骤：
1.导入驱动jar包(file – >project Structure – Modules – Dependencies)
2.注册驱动
3.获取数据库连接对象Connection
4.定义sql
5.获取执行sq1语句的对象Statement
6.执行sql,接受返回结果
7.处理结果
8.释放资源

package jdbc;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class JdbcDemo1 {
    public static void main(String[] args) throws Exception {
        //1.导入驱动jar包
        //注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //3.获取数据库连接对象
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/studb","root","root");
        //4.定义sql语句
        String str = "update 用户收入表 set 收入 = 43000 where id = 101";
        //5.获取执行sql的对象，Statement
       Statement stmt =  conn.createStatement();
        //6.执行sql
        int count = stmt.executeUpdate(str);
        //7.处理结果
        System.out.println(count);
        //8.释放资源
        stmt.close();
        conn.close();
    }
}

各个类对象：

1.DriverManger：驱动管理对象

功能：
1).注册驱动：告诉程序该用哪一个数据库驱动jar
链接：https://pan.baidu.com/s/1lYbA-trDpiRt-s6C7MsAuA
提取码：7nbg
static void registerDriver( Driver driver)：注册与给定的驱动程序DriverManager
写代码使用：Class.forName(“com.mysql.jdbc.Driver”);在com.mysql.jdbc.Driver类中存在静态代码块

static{
    try{
        java.sql.DriverManager.registerDriver( new Driver() );
    }catch(SQLException E){
        throw new RuntimenException("Can`t register  driver");
    }
}

*注意：mysql5以后的驱动jar包可以省略注册驱动的步骤。

2).获取数据库连接：
方法：static Connection getConnection( String url, String user , String password );
参数：

	url：指定路径：语法：jdbc:mysql://ip地址(域名) : 端口号/数据库名称；
	如果连接的是本机的mysql服务器，并且mysql服务器默认端口3306，则url可以简写为：jdbc:mysql:///数据库名称
	user：用户名 
	password：密码

2.Connection：数据库连接对象

功能：
1).获取执行sql的对象
Statement CreateStatement()
PreparedStateement prepareStatement( String sql )
2).管理事务
开启事务：setAutoCommit (boolean autoCommit ）：调用该方法设置参数为false，即开启事务。
提交事务：commit()
回滚事务：rollback()

3.Statement：执行sql的对象

方法：

1). boolean execute （String sql );可以执行任意的sql 了解
2). int executeUpdate (String sql ); 执行DML（insert 、update 、delete)语句，DDL（create ，alter，drop)语句。返回值：影响的行数，可以通过这个影响的行数判断DML语句是否执行成功，返回值>0 则执行成功，反之失败
3).  ResultSet executeQuery (String sql ) ,执行DQL(select )语句。

4.ResultSet：结果集对象，封装查询结果

1).boolean next()：游标向下移动一行，判断当前行是否是最后一行末尾(是否有数据)，如果是最后一行返回false，如果不是返回true。
2).getXxx(参数)：获取数据
Xxx：代表数据类型，如：int getInt()，float getFloat();
参数类型：
1.int 代表列的编号，从1开始计算
2.String ：代表列名称。如：getString(“name”)

package jdbc;

import java.sql.*;

public class Jdbc_Resultset {

    //ResultSet 结果集对象，封装查询结果
    public static void main(String[] args) {
        ResultSet_mothd();
    }

    public static  void  ResultSet_mothd(){
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            //1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取连接数据库对象
            connection = DriverManager.getConnection("jdbc:mysql:///studb","root","root");
            //3.获取执行对象
            statement = connection.createStatement();
            //4.定义sql
            String sql  = "select * from 用户收入表";
            //5.执行sql
            resultSet = statement.executeQuery(sql);
            //6.处理结果
            //6.1 让游标向下移动一行
            //循环判断游标是否是最后一行末尾,游标移动到下一行有数据返回true，没有数据返回false
            while (resultSet.next()){
                int id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                float gong = resultSet.getFloat(3);
                int bumen = resultSet.getInt(4);
                String tiem = resultSet.getString(5);
                System.out.println("ID号："+id+"\t姓名："+name+"\t收入："+gong+"\t部门编号："+bumen+"\t入职时间："+tiem);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        //释放资源
        finally {
            if(resultSet != null){
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(statement != null){
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(connection != null){
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }

}

jdbc实现登录操作：

package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class jdbcDemo2_DML {
    public static void main(String[] args) {
        //执行添加一条记录方法
        //InsertData();
        //更新字段
        //Update();
        //删除字段
        DeleteDate();
    }

    //添加一条记录
    public static void InsertData(){
        Connection conn = null;
        Statement statement = null;
        try {
            //1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取数据库连接对象Connection
            conn = DriverManager.getConnection("jdbc:mysql:///studb","root","root");
            //3.定义sql
          String sql= "insert into 用户收入表 values (106,'alis',5821.00,1,'2019-09-18')";
            //4.获取执行sql的对象
            statement = conn.createStatement();
            //5.执行sql
            int index = statement.executeUpdate(sql);
            //6.处理结果
            System.out.println(index);
            if(index>0){
                System.out.println("添加成功");
            }else{
                System.out.println("添加失败");
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        //7.释放资源
        finally {
            //先关闭 Statement ,在关闭Connection
            //避免空指针异常  statement.close()
            if(statement != null){
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            //避免空指针异常  conn.close()
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

        }
    }

    //修改表中的记录
    public static void Update(){
        Connection connection = null;
        Statement statement = null;
        try {
            //1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取数据库连接对象Connection
            connection =  DriverManager.getConnection("jdbc:mysql:///studb","root","root");
            //3.定义sql语句
            String sql = "update 用户收入表 set name =  '小小' where id = 106";
            //4.获取执行sql对象 Statement
            statement = connection.createStatement();
            //5.执行sql
            int index = statement.executeUpdate(sql);
            //6.执行结果
            System.out.println(index);
            if(index > 0){
                System.out.println("添加成功");
            }else{
                System.out.println("添加失败");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        //7.释放资源
        finally {
            if (statement != null){
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

            if(connection != null){
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }

    //删除一条记录
    public static void DeleteDate(){
        Connection conn = null  ;
        Statement statement = null ;
        try {
            //1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取连接数据库对象
            conn = DriverManager.getConnection("jdbc:mysql:///studb","root","root");
            //3.定义sql语句
            String sql = "delete from 用户收入表 where id = 106";
            //4.获取执行sql语句对象
            statement = conn.createStatement();
            //5.执行sql
            int index = statement.executeUpdate(sql);
            //6.执行结果
            if(index > 0){
                System.out.println("删除成功");
            }else {
                System.out.println("删除失败");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        //7.释放资源
        finally {
            if(statement != null){
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }
}

出现的问题：
1.SQL注入问题：在拼接sql时，有一些sql的特殊关键字参与字符串拼接，会造成安全性问题；
如：

String sql = "select * from students where username ='"+ name+"' and  password= '" + password+"'";

在控制栏随便输入用户名和密码：a’ or ‘a’ = 'a 将会显示登录成功，执行
SELECT * FROM students where username = ‘aa’ and password = ‘a’ or ‘a’ = ‘a’
时，将会打印表中所有的记录，
2.解决注入问题：使用PreparedSatement对象来解决

5.PreparedStatement：执行sql的对象

PreparedStatement是预编译的SQL:表示使用 ？作为占位符。

使用步骤：
1.导入驱动jar包(file -- >project Structure -- Modules -- Dependencies)
2.注册驱动
3.获取数据库连接对象Connection
4.定义sql
sql中的参数使用?作为占位符：select * from user where name = ? and password = ？；
5.获取执行sq1语句的对象PreparedStatement ,获取PreparedStatement对像的方法：Connection.prepareStatement(String sql)
PreparedStatement statement = connection.prepareStatement(sql);

6.给占位符？赋值
方法：setXxx(参数1,参数2)：参数1：是占位符？的位置编号，从1开始，参数2是占位符？的值
7.执行sql
方法：不需要传递sql参数
int executeUpdate ()
ResultSet executeQuery ()
8.释放资源

PreparedStatement可以完成增删改查的所有操作，可以防止SQL注入，效率更高。

package PreparedStatement;

import JDBCUtils.JDBC_Utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class PrepardeStatement_test {
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement pre = null;
        try {
            //注册驱动和获取sql连接对象：使用工具类中的静态方法：getConn()
            con = JDBC_Utils.getConn();
            //定义sql语句
            String sql = "update 用户收入表 set 收入 =  5566 where name = ?";
            //获取sql执行对象PreparedStatement
            pre = con.prepareStatement(sql);

            pre.setObject(1,"赵丽颖");
            //执行sql
            int index = pre.executeUpdate();
            if(index > 0){
                System.out.println("查询成功");
            }else {
                System.out.println("查询失败");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            //使用工具类中的静态方法close
            JDBC_Utils.close(pre,con);
        }

    }
}

JDBC控制事务

1.事务：一个包含多个步骤的业务操作，如果这个业务操作被事务管理，则这多个步骤要么同时成功要么同时失败
2.操作：a.开启事务 b.提交事务 c.回滚事务
3.使用Connection对象来管理事务
开启事务：setAutoCommit( boolean autonCommit)；调用该方法设置参数为false，即开启事务，在执行sql前开启事务
提交事务：commit()，当所有sql都执行完提交事务
回滚事务：rollback()，在catch中回滚事务

package ShiWu;

import JDBCUtils.JDBC_Utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Transfer {
    public static void main(String[] args) {
    new Transfer().setDate();
    }

    public void setDate(){
        Connection connection = null;
        PreparedStatement pre1 = null;
        PreparedStatement pre2 = null;
        try {
            connection = JDBC_Utils.getConn();
            //开启事务
            connection.setAutoCommit(false);
        //转账
        String sql1 = "update 转账表 set balance = balance - ? where name = ?";
        //收账
        String sql2 = "update 转账表 set balance = balance + ? where name = ?";

           //获取执行sql对象
            pre1 = connection.prepareStatement(sql1);
            pre2 = connection.prepareStatement(sql2);
            //设置参数
            pre1.setFloat(1,200);
            pre1.setString(2,"小甜");
            pre2.setFloat(1,200);
            pre2.setString(2,"花梨");
            //执行sql
            pre1.executeUpdate();
            //手动制造异常
//            int i = 9/0;
            pre2.executeUpdate();
            //提交事务
            connection.commit();
        } catch (Exception throwables) {
            //回滚事务
            try {
                if (connection != null)
                connection.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            throwables.printStackTrace();
        }
        finally {
            JDBC_Utils.close(pre1,connection);
            JDBC_Utils.close(pre2,null);
        }

    }
}

数据库连接池

1.概念：
其实就是一个容器（集合），存放数据库连接的容器。
当系统初始化好后，容器被创建，容器中会申请一些连接对象，当用户来访问数据库时，从容器中获取连接对象归还给容器。

2.好处：
1.节约资源 2.用户访问高效

3.实现：
1.标准接口：DateSource javax.sql包下的
方法：
1.获取连接：getConnection()
2.归还连接：如果连接对象Connection是从连接池中获取的，那么调用 Connection.close()方法，则不会再关闭连接了，而是归还连接。
3.一般我们不去实现它，有数据库厂商来实现
3.1.C3P0:数据库连接池技术
3.2.Druid:数据库连接池实现技术，由阿里巴巴提供。

C3P0：数据库连接池技术

使用步骤：
1.导入jar包(两个) c3p0-0.9.5.2.jar ，mchange-commons-java-0.2.12.jar和驱动jar
链接：https://pan.baidu.com/s/1uz2NrcjhTWBEnKfF-NfQbQ
提取码：wo82
2.定义配置文件
1.名称：c3p0.properties 或者 c3p0-config.xml
2.路径：直接将文件放在src目录下

3.创建核心对象，数据库连接池对象：ComboPooledDataSource
4.获取连接：getConnection

//1.获取DataSource对象，连接数据库连接池，使用默认配置
    //DataSource ds =  new ComboPooledDataSource();
//使用自定义配置
    DataSource ds = new ComboPooledDataSource("otherc3p0");

Druid：数据库连接池技术，由阿里巴巴提供

步骤：
1.导入jar包，druid-1.0.9.jar，和驱动jar包
链接：https://pan.baidu.com/s/1g7nf66QzVrFWGGtU15pQbw
提取码：ikqb
2.定义配置文件：是properties形式的，可以命名为任意名称，可以放在任意路径下
3.加载配置文件：Properties
4.获取数据库连接池对象：通过工厂来获取 DruidDataSourceFactory
5.获取连接：getConnection
//1.导入jar包
//2.定义配置文件
//3.加载配置文件

Properties p = new Properties();
InputStream is = DruidDemo1.class.getClassLoader().getResourceAsStream("druid.properties");
p.load(is);

//4.获取连接池对象
DataSource ds = DruidDataSourceFactory.createDataSource(p);
//5.获取连接
Connection conn = ds.getConnection();

工具类

package datasource;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;


//工具类
public class JDBCUtils {
    private  static   String url;
    private  static   String user;
    private  static   String password;
    private  static   String driverClassName;
    private  static   DataSource ds ;
    static {
        try {
            Properties pro = new Properties();
            InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties");
            pro.load(is);
            ds = DruidDataSourceFactory.createDataSource(pro);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //释放资源
    public static void close(Connection connection, Statement statement , ResultSet resultSet){
        if(connection != null){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(statement != null){
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
    public static void close(Connection connection,Statement statement){
        close(connection,statement,null);
    }

    //从连接池里取一个连接对象
    public static Connection getConnection() throws SQLException {
            return  ds.getConnection();
    }

}

Spring ——JDBC Template

Spring 框架对JDBC的简单封装，提供了一个JDBCTemplate对象简化JDBC的开发

步骤：
1.导入jar包链接：https://pan.baidu.com/s/18XwwzRrXMv2jXFO_HMfqaQ
提取码：qfja

2.创建JdbcTemplate对象，依赖于数据源DataSource
JdbcTemplate template = new JdbcTemplate(DataSource ds)

3.调用JdbcTemplate中的方法来完成CRUD的操作
execute方法：可以用于执行任何SQL语句，一般用于执行DDL语句；
update方法及batchUpdate方法：update方法用于执行新增、修改、删除等语句；batchUpdate方法用于执行批处理相关语句；
query方法及queryForXXX方法：用于执行查询相关语句；

方法操作：
使用到的数据库：

//创建一个JdbcTemplate对象，对象中的参数请参考上面编写的工具类
JdbcTemplate tem = new JdbcTemplate(JDBCUtils.getDataSource());

1.update():执行DML语句，增删改语句

//修改一条记录
@Test
public void testUpdate(){
    String sql = "update 学生信息表 set school_id = ? where name = ?";
    int index = tem.update(sql,2000,"李泽");
    System.out.println(index);
}

//增加记录
@Test
public void testInsert(){
    String sql = "insert into 学生信息表 values (?,?,?,?,?,?,?)";
    int index = tem.update(sql,"3006","赵丽颖","女","计算机科学系","201","软件技术","123456789");
    System.out.println(index);
}

//删除记录 
@Test
public void testDelete(){
    String sql = "delete from 学生信息表 where name = ?";
    int index = tem.update(sql,"赵丽颖");
    System.out.println(index);
}

2.queryForMap()：查询结果将结果集封装为map集合，将列名作为key，将值为value将这条记录封装为一个Map集合，这个方法查询的结果集长度只能为1.

@Test
    public void testSelect(){
    String sql = "select * from 学生信息表 where school_id" +
            "xlain = ?";
    Map<String,Object> map = tem.queryForMap(sql,"3003");
    System.out.println(map);
}

3.queryForList()：查询结果将结果集封装为list集合，将每一条记录封装为一个Map集合，再将Map集合装载在List集合中。

@Test
public void testSelectAll(){
    String sql = "select * from 学生信息表";
    List<Map<String,Object>> list = tem.queryForList(sql);
    for(Map l:list){
        System.out.println(l);
    }
}

4.query()：查询结果，将结果封装为JavaBean对象，query的参数：RowMapper，一般我们会使用BeanPropertyRowMapper实现类。可以完成数据到JavaBean的自动封装。

  @Test
    public void testDiy(){
        String sql = "select * from 学生信息表";
       List<Student> list =  tem.query(sql, new RowMapper<Student>() {
            //每调用一次就会封装一个Student对象返回
            @Override
            public Student mapRow(ResultSet resultSet, int i) throws SQLException {
//                Student student = new Student();
                String school_id = resultSet.getString(1);
                String name = resultSet.getString(2);
                String sex = resultSet.getString(3);
                String xibie = resultSet.getString(4);
                String major_id = resultSet.getString(5);
                String major_name = resultSet.getString(6);
                String id = resultSet.getString(7);
                Student student = new Student(school_id,name,sex,xibie,major_id,major_name,id);
                return student;
            }
        });
        for (Student s:list) {
            System.out.println(s);
        }
    }

    /*
    使用已经实现好的BeanPropertyRowMapper类，要求：数据库中的列名必须和定义的Student对应的属性名一致否则将返回null；
    查询所有记录，将其封装为一个自定义对象的list集合
     */
    @Test
    public void testDiy_2(){
        String sql = "select * from 学生信息表";
        List<Student> list =  tem.query(sql,new BeanPropertyRowMapper<Student>(Student.class));
        for (Student s:list) {
            System.out.println(s);
        }
    }

5.queryForObject()：查询结果，将结果封装为对象，一般用于聚合函数的查询

@Test
public  void testNumbers(){
    String sql = "select count(school_id) from 学生信息表";
    int count = tem.queryForObject(sql,Integer.class);
    System.out.println(count);
}