java 二阶段 day22 JDBC

JDBC

建立连接

public class JdbcDemo1 {
    public static void main(String[] args) throws Exception {
        //1,注册驱动，jdk1.8以后也不需要注册，自动会注册。
        Class.forName("com.mysql.jdbc.Driver");//获取类对象，导致类加载，执行静态代码块，注册驱动
        //2.获取连接
        //url:uniform resource location 统一资源定位符
        //jdbc:mysql://协议
        //localhost:主机名或则是ip地址
        //3306:端口号
        //company:数据库名
        //如果Mysql用的是8，Class.forName("com.mysql.cj.Driver"); 还要加上serverTimezone=GMT%2B;服务时区=格林尼治+8;%就相当于加
        //String url="jdbc:mysql://localhost:3306/companydb?useSSL=false&character=utf-8&serverTimezone=GMT%2B8";
        String url="jdbc:mysql://localhost:3306/companydb?useSSL=false&character=utf-8";
        //建立三次握手
        Connection connection = DriverManager.getConnection(url, "root", "root123");
        if(connection!=null){
            System.out.println("连接成功");
        }
        //3.关闭
        connection.close();
    }
}

创建数据库，表

public class JdbcDemo2 {
    public static void main(String[] args) throws Exception{
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/companydb?useSSL=false&characterEncoding=utf-8";
        //获取连接
        Connection conn = DriverManager.getConnection(url, "root", "root123");
        //创建命令
        Statement statement = conn.createStatement();
        //执行命令
        //execute(sql):any SQL statement 执行任何SQL语句，如果第一个结果是ResultSet，返回true,否则返回false
        //executeUpdate(sql) 执行DML(insert delete update) DDL(create table,view..)返回影响的行数，或则是0
        //executeQuery(sql) 执行查询语句，返回结果集
        int count = statement.executeUpdate("create database if not exists qf2002");
        //切换数据库
        int use_qf2002 = statement.executeUpdate("use qf2002");
        //删除表
        statement.executeUpdate("drop table student");
        //创建表
        int i = statement.executeUpdate("create table if not exists student( stu_id int primary key auto_increment," +
                "stu_name varchar(20)," +
                "password varchar(20)," +
                "gender char(1)," +
                "born date ," +
                "address varchar(20))" +
                "auto_increment=1000;");
        System.out.println(count);
        System.out.println(i);
        //关闭
        statement.close();
        conn.close();
    }
}

插入数据，删除，更新

public class JdbcDemo3 {
    public static void main(String[] args) throws Exception{
        //加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/qf2002?useSSL=false&characterEncoding=utf-8";
        Connection conn = DriverManager.getConnection(url, "root", "root123");
        Statement sta = conn.createStatement();
        sta.executeUpdate("delete from student");
        int i = sta.executeUpdate("insert into student(stu_name,password,gender,born,address) values('轩轩','1234','男','1998-02-09','北京')");
        sta.executeUpdate("insert into student(stu_id,stu_name,password,gender,born,address) values(1,'轩轩','1234','男','1998-02-09','北京')");
        sta.executeUpdate("insert into student(stu_id,stu_name,password,gender,born,address) values(7,'轩而','12234','女','1978-02-09','上海')");
        sta.executeUpdate("insert into student(stu_name,password,gender,born,address) values('轩9','12234','女','1988-02-09','上海')");
        sta.executeUpdate("update student set stu_name='小轩',born='1999-03-01' where stu_id=1");
        //添加之后获取主键
        sta.executeUpdate("insert into student(stu_id,stu_name,password,gender,born,address) values(null,'轩','1223s4','女','1938-02-09','上海')",Statement.RETURN_GENERATED_KEYS);
        ResultSet generatedKeys = sta.getGeneratedKeys();
        if(generatedKeys.next()){
            long generatedKey = generatedKeys.getLo###ng(1);
            System.out.println(generatedKey);
        }
        sta.close();
        conn.close();
        System.out.println("执行结果:"+i);
    }
}

查询数据

public class JdbcDemo4 {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/qf2002?useSSL=false&characterEncoding=utf-8";
        Connection conn = DriverManager.getConnection(url, "root", "root123");
        Statement stat = conn.createStatement();
        ResultSet resultSet = stat.executeQuery("select stu_id,stu_name,password,gender,born,address from student");
        while (resultSet.next()){
            int stu_id = resultSet.getInt("stu_id");
            String stu_name = resultSet.getString("stu_name");
            String password = resultSet.getString("password");
            String gender = resultSet.getString("gender");
            Date born = resultSet.getDate("born");
            String address = resultSet.getString("address");
            System.out.println(stu_id+"---"+stu_name+"---"+password+"---"+gender+"---"+born+"---"+address);
        }
        resultSet.close();
        stat.close();
        conn.close();
    }
}

数据库中的date---->java.sql.date
time------>java.sql.time
datetime--------->java.sql.timestamp
java.sql.date、java.sql.time、java.sql.timestamp都继承了java.util.date

resultSet

其实也可以来回读，只是规定从前往后读取。

编号是从1开始的

常见的异常

PreparedStatement

public class JdbcDemo6 {
    public static void main(String[] args) throws Exception{
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/qf2002?useSSL=false&characterEncoding=utf-8";
        Connection conn = DriverManager.getConnection(url, "root", "root123");
        //增加数据
        PreparedStatement pstat = conn.prepareStatement("insert into student(stu_id,stu_name,password,gender,born,address) values (?,?,?,?,?,?)");
        pstat.setInt(1,3);
        pstat.setString(2, "轩轩");
        pstat.setString(3, "11111");
        pstat.setString(4, "男");
        //插入实际的三种方式
        //1.
        //pstat.setDate(5, new java.sql.Date(System.currentTimeMillis()));
        //2.
        //pstat.setObject(5, new java.util.Date());
        //3.
        pstat.setString(5, "2020-02-09");
        pstat.setString(6, "南京");
        pstat.executeUpdate();
        pstat.close();
        conn.close();
//      删除
        PreparedStatement pstat1 = conn.prepareStatement("delete from student where stu_id >=?");
        pstat1.setInt(1, 10);
        pstat1.executeUpdate();
        pstat1.close();
//      修改数据
        PreparedStatement pstat2 = conn.prepareStatement("update student set password=?,gender=? where stu_id=?");
        pstat2.setString(1, "22222");
        pstat2.setString(2, "女");
        pstat2.setInt(3, 4);
        pstat2.executeUpdate();
        pstat2.close();
//       查询数据
        PreparedStatement pstat3 = conn.prepareStatement("select * from student");
        ResultSet res = pstat3.executeQuery();
        while(res.next()){
            int stu_id = res.getInt("stu_id");
            String stu_name = res.getString("stu_name");
            String password = res.getString("password");
            String gender = res.getString("gender");
            Date born = res.getDate("born");
            String address = res.getString("address");
            System.out.println(stu_id+"--"+stu_name+"--"+password+"--"+gender+"--"+born+"--"+address);
        }
        res.close();
        pstat3.close();
        conn.close();
    }
}

封装

/**
 * 2020/8/20
 * 16:31
 * 注册驱动
 * 获取连接
 * 增删查
 * 释放资源
 */
public class DbUtils {
    private static String url;
    private static String user;
    private static String password;
    static{
        //注册驱动
        InputStream in = DbUtils.class.getClassLoader().getResourceAsStream("qf/test/test01/dbmessage.properties");
        Properties properties = new Properties();
        try {
            properties.load(in);
            url = properties.getProperty("url");
            user = properties.getProperty("user");
            password = properties.getProperty("password");
        } catch (IOException e) {
            System.out.println("读取失败...");
        }finally {
            try {
                in.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            System.out.println("驱动注册失败...");
        }
    }
    //得到连接
    public static Connection getConnect(){
        try {
            Connection conn = DriverManager.getConnection(url, user, password);
            return conn;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    //增删改
    public static void update(String sql,Object...objects){
        Connection conn = getConnect();
        PreparedStatement pstat = null;
        try {
            pstat = conn.prepareStatement(sql);
            for(int i=0;i<objects.length;i++){
                pstat.setObject(i+1, objects[i]);
            }
            pstat.executeUpdate();
            System.out.println("执行成功...");
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            shutAll(conn,pstat,null);
        }
    }
    //释放资源
    public static void shutAll(Connection conn, Statement stat, ResultSet res){
        try {
            if(res!=null){
                res.close();
            }
            if(stat!=null){
                stat.close();
            }
            if(conn!=null){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

ORM

DAO

Service

手动封装查询操作

//第一种查询工具方法(把重复的保留，不重复的用参数在带回来)（享元模式）
public static <T> List<T> executeQuery(RowMapper<T> rowMapper,String sql,Object... prams){
    Connection conn = null;
    PreparedStatement pstat = null;
    ResultSet res = null;
    try{
        conn = getConnection();
        pstat = conn.prepareStatement(sql);
        if(prams!=null){
            for (int i = 0; i < prams.length; i++) {
                pstat.setObject(i+1, prams[i]);
            }
        }
        res = pstat.executeQuery();
        List<T> lists = new ArrayList<>();
        while(res.next()){
            //得到对象
           T t = rowMapper.getRow(res);
           if(t!=null){
               lists.add(t);
           }
        }
        return lists;
    }catch (SQLException e){
        throw new BookException("查询失败...");
    }finally {
        closeAll(conn,pstat,res);
    }
}

RowMapper的接口

public interface RowMapper<T> {
    T getRow(ResultSet res);
}

Rowmapper的实现

public class RowMapperImpl implements RowMapper<Book> {
    @Override
    public Book getRow(ResultSet res) {
        try {
            Integer id =res.getInt("id");
            String title = res.getString("title");
            String author = res.getString("author");
            Timestamp publicDate = res.getTimestamp("publicDate");
            String publisher = res.getString("publisher");
            String isbn = res.getString("isbn");
            BigDecimal price = res.getBigDecimal("price");
            Integer cid = res.getInt("cid");
            Book book = new Book(id,title,author,publicDate,publisher,isbn,price,cid);
            return book;
        }catch (SQLException e){
            throw new BookException("读取数据库失败...");
        }
    }
}

第二种方式，利用反射

//第二种查询工具的方法（反射）参数：类对象，sql语句，数据数组
public static <T> List<T> executeQuery(Class<T> tClass,String sql,Object... prams){
    Connection conn = null;
    PreparedStatement pstat = null;
    ResultSet res = null;
    try {
        conn = getConnection();
        pstat = conn.prepareStatement(sql);
        if(prams != null){
            for (int i = 0; i < prams.length; i++) {
                pstat.setObject(i+1,prams[i]);
            }
        }
        res = pstat.executeQuery();
        List<T> lists = new ArrayList<>();
        while(res.next()){
            //获得结果集的列名(列名和实体类中的属性名必须一模一样)
            ResultSetMetaData columns = res.getMetaData();
            //实例化对象
            T t = tClass.newInstance();
            for (int i = 0; i < columns.getColumnCount(); i++) {
                //得到列名
                String columnName = columns.getColumnLabel(i + 1);
                //反射
                PropertyDescriptor propertyDescriptor = new PropertyDescriptor(columnName,tClass);
                //得到写方法，也就是set方法
                Method writeMethod = propertyDescriptor.getWriteMethod();
                writeMethod.invoke(t,res.getObject(columnName));
            }
            if(t != null){
                lists.add(t);
            }
        }
        return lists;
    }catch (Exception e){
        throw new BookException("查询失败...");
    }finally {
        closeAll(conn,pstat,res);
    }
}

事务

三层架构

数据库连接池

所有的连接池必须实现DataSource的接口。
老的项目使用的连接池DBCP，C3P0。

自己实现数据库连接池

// 自己创建连接池,连接池必须实现DataSource接口,实现的方法很多，只需实现getConnection()方法就可以
public class DbPool implements DataSource {
    private static String driver;
    private static String url;
    private static String user;
    private static String password;
    private static ConcurrentLinkedDeque<Connection> dbPools = new ConcurrentLinkedDeque<>();
    //创建数据库连接池
    static{
        Properties properties = new Properties();
        InputStream source = DbUtils.class.getClassLoader().getResourceAsStream("db.properties");
        try {
            properties.load(source);
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            user = properties.getProperty("user");
            password = properties.getProperty("password");
            Class.forName(driver);
            //创建大小为5的数据库连接池
            for (int i = 0; i <5 ; i++) {
                Connection conn = DriverManager.getConnection(url, user, password);
                dbPools.offer(conn);
            }
            System.out.println("创建数据库连接池成功...");
        } catch (Exception e) {
            throw new RuntimeException("创建数据库连接池失败....");
        }
    }

    //从连接池中获得连接
    @Override
    public Connection getConnection() throws SQLException {
        //需要加锁
        synchronized (dbPools){
            //只有里面还有连接，才能取出
            if(dbPools.size()>0){
                return dbPools.poll();
            }
        }
        return null;
    }
    //用完需要归还
    public void release(Connection conn){
        dbPools.offer(conn);
        System.out.println("已经归还");
    }

    @Override
    public Connection getConnection(String username, String password) throws SQLException {
        return null;
    }

    @Override
    public <T> T unwrap(Class<T> iface) throws SQLException {
        return null;
    }

    @Override
    public boolean isWrapperFor(Class<?> iface) throws SQLException {
        return false;
    }

    @Override
    public PrintWriter getLogWriter() throws SQLException {
        return null;
    }

    @Override
    public void setLogWriter(PrintWriter out) throws SQLException {

    }

    @Override
    public void setLoginTimeout(int seconds) throws SQLException {

    }

    @Override
    public int getLoginTimeout() throws SQLException {
        return 0;
    }

    @Override
    public Logger getParentLogger() throws SQLFeatureNotSupportedException {
        return null;
    }
}

Druid(阿里的，使用的很频繁)

需要导入druid.jar包

//第一种创建数据库连接池对象
DruidDataSource druidDataSource = new DruidDataSource();
//配置参数
druidDataSource.setDriverClassName("com.mysql.jdbc.Driver");
druidDataSource.setUrl("jdbc:mysql://localhost:3306/bookshop?useSSL=false&character=utf8");
druidDataSource.setUsername("root");
druidDataSource.setPassword("root123");
//其他参数
//连接池的初始大小
druidDataSource.setInitialSize(10);
//连接池的最大大小
druidDataSource.setMaxActive(50);
//连接池的最小空闲
druidDataSource.setMinIdle(5);
//最大等待时间
druidDataSource.setMaxWait(10000);

最常用的方式
配置文件信息：

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/bookshop?useSSL=false&characterEncoding=utf8
username=root
password=root123

initialSize=10
maxActive=50
minIdle=5
maxWait=10000

//第二种创建连接池的方式
Properties properties = new Properties();
InputStream resource = DruidPoolTest.class.getClassLoader().getResourceAsStream("commonsDb.properties");
properties.load(resource);
resource.close();
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);

//第三种创建方式
Properties properties = new Properties();
InputStream resource = DruidPoolTest.class.getClassLoader().getResourceAsStream("commonsDb.properties");
properties.load(resource);
resource.close();
DruidDataSource druidDataSource = new DruidDataSource();
druidDataSource.configFromPropety(properties);
DataSource dataSource = druidDataSource;

Commons DbUtils（操作数据库的工具类）

需要导入druid.jar,commons-dbutils.jar,commons-logging.jar包

使用工具类，实体类必须要有无参构造。因为要利用反射+内省，必然会用到无参构造。
ColumnListHandler:默认返回List<String>,返回一列的值。针对多行一列。
ScalarHandler:默认返回Long,返回一行一列。
BeanListHander:返回List<T>,返回多行多列
BeanHandler:返回T，返回一行多列。

//创建对象
QueryRunner que = new QueryRunner(BankUtils.getDataSource());
//增删改
que.update(conn,sql,money,bankNum);
//查所有
String sql = "select bankNum,money,bankName,address from bank";
que.query(sql,new BeanListHandler<>(Bank.class));
//查一行多列
String sql = "select bankNum,money,bankName,address from bank where bankNum = ?;";
que.query(sql,new BeanHandler<>(Bank.class),bankNum);
//查一行一列
String sql = "select count(*) from bank";
return que.query(sql,new ScalarHandler<>());
//查一列多行
String sql = "select bankNum from bank ";
return que.query(sql,new ColumnListHandler<>());