JDBC

• 用java操作数据库
• Java DataBase Connectivity
• 官方定义的一套操作所有关系型数据库的规则，即接口。各个数据库厂商去实现这套接口，提供数据库驱动jar包，我们可以使用这套接口JDBC编程，真正执行的代码是驱动jar包中的实现类
• 执行sql语句

//注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//获取数据库的连接对象
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/dbtest3", "root", "123456789");
//定义sql语句
String sql = "update commpany set age = 30 where id = 2";
//获取执行sql对象
Statement statement = connection.createStatement();
//执行sql
int count = statement.executeUpdate(sql);
System.out.println(count);
//释放资源
statement.close();
connection.close();

• 正确的释放资源

public static void main(String[] args){
    Statement statement = null;
    Connection connection = null;
    ResultSet resultSet = null;
    try {
        //注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //获取数据库的连接对象
        connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/dbtest3", "root", "123456789");
        //定义sql语句
        String sql = "select * from commpany;";
        //获取执行sql对象
        resultSet = statement.executeQuery(sql);
        .....

    } 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();
            }
        }

    }
}

各类对象

DrivaerManager

• 驱动管理，注册驱动，获取数据库连接
• 现在的版本可以省略注册驱动的步骤
• 静态代码块，注册驱动时，直接运行

static {
    try {
        DriverManager.registerDriver(new Driver());
    } catch (SQLException var1) {
        throw new RuntimeException("Can't register driver!");
    }
}

• getConnection的第一个参数执行数据库格式jdbc:mysql://ip地址（域名）:端口号/数据库
• 如果连接的是本机的msq服务器，并且mysql的端口为3306，则可以简写为jdbc:mysql:///数据库

Connection

• 数据库连接
• 获取执行sql的对象
• 事务

//开启事务
connection.setAutoCommit(false);
//提交事务
connection.commit();
//回滚
connection.rollback();

Statement

• 执行sql

//执行任意的sql
boolean res = statement.execute(sql);
//执行sql 执行DML增删改语句 (insert update delete) DDL语句(create alter drop)
//返回影响的行数
int count = statement.executeUpdate(sql);
//执行DQL查询语句，返回结果集
ResultSet resultSet = statement.executeQuery(sql);

ResultSet

• 结果集
• 获取

//游标向下移动一行
resultSet.next();
//获取数据 XXX代表数据类型，参数为列的编号/列的名称
resultSet.getXXX();

• 基本使用

//执行sql
resultSet = statement.executeQuery(sql);
resultSet.next();
int anInt = resultSet.getInt(1);
String name = resultSet.getString("name");
int age = resultSet.getInt("age");
String dep_name = resultSet.getString("dep_name");
String dep_location = resultSet.getString("dep_location");
System.out.println(anInt + " - " + name + " - " + age + " - " + dep_name + " - " + dep_location);

• 循环取除数据

while (resultSet.next()) {
    int anInt = resultSet.getInt(1);
    String name = resultSet.getString("name");
    int age = resultSet.getInt("age");
    String dep_name = resultSet.getString("dep_name");
    String dep_location = resultSet.getString("dep_location");
    System.out.println(anInt + " - " + name + " - " + age + " - " + dep_name + " - " + dep_location);
}

PreparedStatement

• 预编译sql，参数使用?作为占位符
• 防止sql注入，效率更高
• sql注入问题，在拼接sql时有一些sql的关键字参与字符串拼接，造成安全性问题

String sql = "select * from user where username = ? and password = ?;";
preparedStatement = connection.prepareStatement(sql);
//sql赋值
preparedStatement.setString(1, username);
preparedStatement.setString(2, password);
//执行
resultSet = preparedStatement.executeQuery();

事务

PreparedStatement preparedStatement = null;
Connection connection = null;
ResultSet resultSet = null;
try {
    //获取数据库的连接对象
    connection = JDBCUtility.getConnection();
    //开启事务
    connection.setAutoCommit(false);
    //定义sql语句
    String sql = "update account set balance = balance + ? where id = ?";
    //获取执行sql对象
    preparedStatement = connection.prepareStatement(sql);
    //设置sql 减500
    preparedStatement.setDouble(1,+500);
    preparedStatement.setInt(2,1);
    //执行sql
    preparedStatement.executeUpdate();
    //手动异常
    int i = 3 / 0;
    //设置sql 加500
    preparedStatement.setDouble(1,-500);
    preparedStatement.setInt(2,2);
    //执行sql
    preparedStatement.executeUpdate();
    //提交事务
    connection.commit();
    //要抓大异常
}  catch (Exception throwables) {
    throwables.printStackTrace();
    //回滚
    try {
        if (connection != null) {
            connection.rollback();
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}  finally {
    JDBCUtility.close(resultSet, preparedStatement, connection);
}

数据库连接池

• 一个存放数据库连接的容器
• 当系统初始化后，容器被创建，容器申请一些连接对象，当用户访问数据库时，从容器中获取连接对象，用户访问完之后，将连接对象归还给容器
• 节约资源，用户访问高效

C3P0

• 导入jar包，设置配置文件，名字必须为*.properties(手动导入)/c3p0-config.xml(默认加载)

//创建数据库连接池
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
//使用指定名称配置 xml文件中的另一段
//ComboPooledDataSource otherc3p0 = new ComboPooledDataSource("otherc3p0");
//获取一个连接
Connection connection = comboPooledDataSource.getConnection();
//归还
connection.close();
System.out.println(connection);

Druid

• 阿里
• 导入jar包，设置配置文件

Properties properties = new Properties();
InputStream resourceAsStream = DemoDruid.class.getClassLoader().getResourceAsStream("druid.properties");
properties.load(resourceAsStream);
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);

for (int i = 0; i < 11; i++) {
    Connection connection = dataSource.getConnection();
    System.out.println(connection);
    if (i == 5) {
        //归还
        connection.close();
    }
}

Spring-JDBC

• 基本使用

//创建JDBCTemplate
JdbcTemplate jdbcTemplate = new JdbcTemplate(DemoJDBCUtility.getDataSource());
String sql = "update account set balance = 400 where id = ?";
//自动释放资源，以参数列表的形式传递sql值
int update = jdbcTemplate.update(sql, 1);
System.out.println(update);

• 添加

String sql = "insert into commpany values (null, ?, ?, ?, ?)";
//自动释放资源，以参数列表的形式传递sql值
int update = jdbcTemplate.update(sql, "dawsda", 40, "市场部", "hangzhou");

• 删除

String sql = "Delete from commpany where id = ?";
//自动释放资源，以参数列表的形式传递sql值
int update = jdbcTemplate.update(sql, 10);

• 查询一条记录，以map形式

String sql = "select * from commpany where id = ?";
//查询的结果集长度只能为1
Map<String, Object> stringObjectMap = jdbcTemplate.queryForMap(sql, 1);
stringObjectMap.forEach((s, o) -> System.out.println(s + " - " + o));

• 查询多条记录，list形式

sql = "select * from commpany";
List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
maps.forEach(map -> map.forEach((s, o) -> System.out.println(s + " - " + o)));

• 查询多条记录，封装成对象

List<Employee> employees = jdbcTemplate.query(sql, new RowMapper<Employee>() {
    @Override
    public Employee mapRow(ResultSet resultSet, int i) throws SQLException {
        int anInt = resultSet.getInt("id");
        String name = resultSet.getString("name");
        int age = resultSet.getInt("age");
        String dep_name = resultSet.getString("dep_name");
        String dep_location = resultSet.getString("dep_location");
        return new Employee(name, anInt, age, dep_name, dep_location);
    }
});
for (Employee employee : employees) {
    System.out.println(employee);
}

• 利用temp中的实现类，快速封装对象 new BeanPropertyRowMapper<类型>(类型.class)

List<Employee> employees = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Employee>(Employee.class));
System.out.println(employees);

• 查询数量，返回任意类型的数据

String sql = "select count('id') from commpany";
Object o = jdbcTemplate.queryForObject(sql, Long.class);
System.out.println(o);