[尚硅谷JDBC核心技术(新版jdbc)]idea完结撒花大总结!看着一个就够了!

15 篇文章 0 订阅
9 篇文章 0 订阅

首先,

要使用到工具类JDBCUtils,完成连接的创建和关闭操作,下面是所有课程讲的各种的操作。getConnection3()内的druid.properties配置文件要放在src下,里面包括一些用户名、驱动等的要求。其中getConnection3()方法使用了目前常用的Druid连接池的技术实现连接,closeResource1()方法其中直接调用了commons-dbutils-1.3.jar包中自带的关闭数据库的方法。

package com.atguigu4.util;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import org.apache.commons.dbutils.DbUtils;

import javax.sql.DataSource;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JDBCUtils {
    //基础的数据库连接操作
    public static Connection getConnection() throws Exception {
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
        Properties pros = new Properties();
        pros.load(is);
        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");
        Class.forName(driverClass);
        Connection conn = DriverManager.getConnection(url, user, password);
        return conn;
    }
    //使用C3P0的数据池的连接操作,数据连接池最好放在外面只要执行一次,而不是每次调用都会执行
    private static ComboPooledDataSource cpds = new ComboPooledDataSource("helloc3p0");
    public static Connection getConnection1() throws Exception{
        Connection conn=cpds.getConnection();
        return conn;
    }
    //使用DPBC的数据池的连接操作,数据连接池最好放在外面只要执行一次,而不是每次调用都会执行
    private static DataSource source;
    static{
        try {
            Properties pros=new Properties();
    /*方式一:
    InputStream is=ClassLoader.getSystemClassLoader().getResourceAsStream("dpbc.properties");*/
            //方式二:
            FileInputStream is=new FileInputStream(new File("src/dpbc.properties"));
            pros.load(is);
            source= BasicDataSourceFactory.createDataSource(pros);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection2() throws Exception{
        Connection conn=source.getConnection();
        return conn;
    }
    //使用Druid的数据池的连接操作,数据连接池最好放在外面只要执行一次,而不是每次调用都会执行
    private  static DataSource source1;
    static{
        try {
            Properties pros=new Properties();
            InputStream is=ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
            pros.load(is);
            DataSource source1= DruidDataSourceFactory.createDataSource(pros);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection3() throws Exception{
        Connection conn=source.getConnection();
        return conn;
    }
    public static void closeResource(Connection conn, PreparedStatement ps) {
        try {
            if (ps!=null){
                ps.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if (conn!=null){
                conn.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    public static void closeResource(Connection conn, PreparedStatement ps, ResultSet rs) {
        try {
            if (ps!=null){
                ps.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if (conn!=null){
                conn.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if (rs!=null){
                rs.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    public static void closeResource1(Connection conn, PreparedStatement ps, ResultSet rs) {
        try {
            DbUtils.close(conn);
            DbUtils.close(ps);
            DbUtils.close(rs);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

其次,

直接用commons-dbutils-1.3.jar包中提供的增删改、查和查询单条结果的的方法,当然也可以自定义ResultSetHandler(这是一个接口)的实现类,实现自己想要的操作。

package com.atguigu5.dbutils;

import com.atguigu2.bean.Customer;
import com.atguigu4.util.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.*;
import org.testng.annotations.Test;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class QueryRunnerTest {
    //增删改操作
    @Test
    public void testInsrt() {
        Connection conn= null;
        try {
            QueryRunner runner=new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql="insert into customers(name,email,birth)values(?,?,?)";
            int insertCount=runner.update(conn,sql,"大大","dada@qq.com","1997-09-08");
            System.out.println("添加了"+insertCount+"条记录");
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,null);
        }
    }
    //单条查询操作:BeanHandler<Customer> handler=new BeanHandler<>(Customer.class);
    @Test
    public void testQuery1() {
        Connection conn= null;
        try {
            QueryRunner runner=new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql="select id,name,email,birth from customers where id=?";
            BeanHandler<Customer> handler=new BeanHandler<>(Customer.class);
            Customer customer=runner.query(conn,sql,handler,23);
            System.out.println(customer);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,null);
        }
    }
    //多条查询操作:BeanListHandler<Customer> handler=new BeanListHandler<>(Customer.class);
    @Test
    public void testQuery2(){
        Connection conn= null;
        try {
            QueryRunner runner=new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql="select id,name,email,birth from customers where id<?";
            BeanListHandler<Customer> handler=new BeanListHandler<>(Customer.class);
            List<Customer> list=runner.query(conn,sql,handler,23);
            list.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,null);
        }
    }
    //单条查询操作:MapHandler handler=new MapHandler();
    @Test
    public void testQuery3() {
        Connection conn= null;
        try {
            QueryRunner runner=new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql="select id,name,email,birth from customers where id=?";
            MapHandler handler=new MapHandler();
            Map<String,Object> map=runner.query(conn,sql,handler,23);
            System.out.println(map);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,null);
        }
    }
    //多条查询操作:MapListHandler handler=new MapListHandler();
    @Test
    public void testQuery4() {
        Connection conn= null;
        try {
            QueryRunner runner=new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql="select id,name,email,birth from customers where id<?";
            MapListHandler handler=new MapListHandler();
            List<Map<String,Object>> list=runner.query(conn,sql,handler,23);
            list.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,null);
        }
    }
    //下面两个方法用于返回查询的记录,比如最大值或者顾客数量
    @Test
    public void testQuery5() {
        Connection conn= null;
        try {
            QueryRunner runner=new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql="select max(birth) from customers ";
            ScalarHandler handler=new ScalarHandler();
            Date maxBirth= (Date) runner.query(conn,sql,handler);
            System.out.println(maxBirth);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,null);
        }
    }
    @Test
    public void testQuery6() {
        Connection conn= null;
        try {
            QueryRunner runner=new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql="select Count(*)from customers ";
            ScalarHandler handler=new ScalarHandler();
            Long count= (Long) runner.query(conn,sql,handler);
            System.out.println(count);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,null);
        }
    }
    //自定义ResultSetHandler(这是一个接口)的实现类
    @Test
    public void testQuery7() {
        Connection conn= null;
        try {
            QueryRunner runner=new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql="select id,name,email,birth from customers where id=?";
            ResultSetHandler<Customer> handler=new ResultSetHandler<Customer>() {
                @Override
                public Customer handle(ResultSet resultSet) throws SQLException {
                    return null;
                }
            };
            Customer customer=runner.query(conn,sql,handler);
            System.out.println(customer);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,null);
        }
    }

}

完结撒花!

  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

小小通信码农

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值