JDBC从入门到精通

于 2021-08-03 10:45:40 发布
阅读量112 收藏
点赞数
分类专栏： Java 文章标签： java intellij-idea maven mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/siriling/article/details/119344133
版权

一、JDBC

简介

Java数据库连接，（Java Database Connectivity，简称JDBC）是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法。

我们通常说的JDBC是面向关系型数据库的。

基本环境搭建
Maven

此为父项目

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <!--父工程-->
    <groupId>org.example</groupId>
    <artifactId>Student_Management_System</artifactId>
    <packaging>pom</packaging>
    <version>1.0-SNAPSHOT</version>

    <!--导入依赖-->
    <dependencies>
        <!--mysql驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.22</version>
        </dependency>
        <!--junit-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
    </dependencies>

    <!--在build中配置resource，来防止我们资源导出失败的问题-->
    <build>
        <resources>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>false</filtering>
            </resource>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>false</filtering>
            </resource>
        </resources>
    </build>
</project>

二、JDBC基础

连接数据库步骤

1.得到数据源

static {
    try {
        Properties info = new Properties();
        info.load(new FileInputStream("E:\\IDEA\\workspace\\Student_Management_System\\Student_Management_System-01\\src\\main\\resources\\jdbc.properties"));
        username = info.getProperty("username");
        password = info.getProperty("password");
        url = info.getProperty("url");
        driver = info.getProperty("driverClassName");

        //注册驱动
        Class.forName(driver);

    } catch (Exception e) {
        e.printStackTrace();
    }
}

2.得到连接

public static Connection getConnection() {
    Connection conn = null;
    try {
        conn = DriverManager.getConnection(url,username,password);
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return conn;
}

3.发送SQL语句

PreparedStatement preparedStatement = null;
String sql="select * from students_member";
preparedStatement = connection.prepareStatement(sql);

4.得到结果集

ResultSet rs = preparedStatement.executeQuery();

5.显示结果

System.out.println(" \n\n   学生信息表");
System.out.println("   ----------------------------------");
System.out.println("       学号       密码    姓名  年龄 性别");
System.out.println("   ----------------------------------");
while(rs.next()){
    int id = rs.getInt("s_id");
    String password = rs.getString("s_password");
    String name = rs.getString("s_name");
    int age = rs.getInt("s_age");
    String sex = rs.getString("s_sex");
    System.out.println("   "+id+"    "+password+"   "+name+"   "+age+"   "+sex);
}
System.out.println("   ----------------------------------");

6.关闭连接

public static void closeConnection(ResultSet rs, Statement st,Connection conn) {
    try {
        if (rs != null){
            rs.close();
        }
        if (st != null){
            st.close();
        }
        if (conn != null){
            conn.close();
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

三、JDBC查询、事务、批处理

resources

jdbc.properties

driverClassName=com.mysql.cj.jdbc.Driver
username=root
password=123456
url=jdbc:mysql://localhost:3306/Student_Management_System?serverTimezone=GMT%2B8&rewriteBatchedStatements=true
建立JDBC工具类

JDBCUtils

package com.ling.utils;

import java.io.FileInputStream;
import java.sql.*;
import java.util.Properties;

public class JDBCUtils {
    static String username;
    static String password;
    static String url;
    static String driver;

    //静态代码块
    static {
        try {
            Properties info = new Properties();
            info.load(new FileInputStream("E:\\IDEA\\workspace\\Student_Management_System\\Student_Management_System-01\\src\\main\\resources\\jdbc.properties"));
            username = info.getProperty("username");
            password = info.getProperty("password");
            url = info.getProperty("url");
            driver = info.getProperty("driverClassName");

            //注册驱动
            Class.forName(driver);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //得到连接的静态方法
    public static Connection getConnection() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url,username,password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    //关闭连接的静态方法
    public static void closeConnection(ResultSet rs, Statement st,Connection conn) {
        try {
            if (rs != null){
                rs.close();
            }
            if (st != null){
                st.close();
            }
            if (conn != null){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
Test
测试查询
import com.ling.utils.JDBCUtils;
import org.junit.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class testJDBCUtils {
    @Test
    public void getStudent() {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement preparedStatement = null;
        //创建发送sql语句车
        String sql="select * from students_member";
        try {
            preparedStatement = connection.prepareStatement(sql);
            ResultSet rs = preparedStatement.executeQuery();

            //文本方式显示学生信息
            System.out.println(" \n\n   学生信息表");
            System.out.println("   ----------------------------------");
            System.out.println("       学号       密码    姓名  年龄 性别");
            System.out.println("   ----------------------------------");

            while(rs.next()){
                int id = rs.getInt("s_id");
                String password = rs.getString("s_password");
                String name = rs.getString("s_name");
                int age = rs.getInt("s_age");
                String sex = rs.getString("s_sex");
                System.out.println("   "+id+"    "+password+"   "+name+"   "+age+"   "+sex);
            }
            System.out.println("   ----------------------------------");

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeConnection(null,preparedStatement,connection);
        }
    }
}
测试事务
import com.ling.utils.JDBCUtils;
import org.junit.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class testJDBCUtils {
    //测试事务
    @Test
    public void updateStudent(){
        Connection connection = JDBCUtils.getConnection();

        //关闭自动提交事事务
        try {
            connection.setAutoCommit(false);
        } catch (SQLException e) {
            System.out.printf("关闭自动提交事务失败");
        }

        PreparedStatement preparedStatement=null;

        try {
            //人为制造错误（测试一下事务）
//            int i=1/0;

            String sql="update student_member set s_age=? where s_name=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,22);
            preparedStatement.setString(2,"小刚");
            preparedStatement.executeUpdate();

            //提交事务
            connection.commit();
            System.out.println("学生信息更新成功");
        } catch (Exception e) {
            System.out.println("更新学生信息发生异常");
            try {
                connection.rollback(); //回滚
            } catch (SQLException e1) {
                System.out.println("回滚异常");
            }
        } finally {
            JDBCUtils.closeConnection(null,preparedStatement,connection);
        }

    }
}
测试批处理

批量添加

import com.ling.utils.JDBCUtils;
import org.junit.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class testJDBCUtils {
    
    //批处理（批量添加）
    @Test
    public void addStudent() {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement preparedStatement = null;

        try {
            String sql = "insert into student_member values (?,?,?,?,?,?,?,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            System.out.println("开始批处理添加5000条数据");
            long start=System.currentTimeMillis(); //记下开始时间
            for (int i = 1; i <= 5000; i++) {
                preparedStatement.setInt(1, 2019000000+i);
                preparedStatement.setString(2, "0000");
                preparedStatement.setString(3, "机器人");
                preparedStatement.setInt(4, 20);
                preparedStatement.setString(5, "男");
                preparedStatement.setInt(6, 190);
                preparedStatement.setString(7, "机器人学院");
                preparedStatement.setString(8, "12345678912");
                preparedStatement.setString(9, "地球");
                //加入preparedStatement
                preparedStatement.addBatch();
                if (i % 1000 == 0) {
                    preparedStatement.executeBatch(); //批量处理
                    preparedStatement.clearBatch(); //处理完后的清理
                }
            }
            long end=System.currentTimeMillis(); //记下结束时间
            System.out.println("所有添加已完成，耗时"+(end-start));
        } catch (Exception e) {
            System.out.println("添加学生信息发生异常");
        } finally {
            JDBCUtils.closeConnection(null, preparedStatement, connection);
        }
    }
}

批量删除

import com.ling.utils.JDBCUtils;
import org.junit.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class testJDBCUtils {
    
    //批处理（批量删除）
    @Test
    public void deleteStudent(){
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement preparedStatement = null;

        try {
            String sql = "delete from student_member where s_name=?";
            preparedStatement = connection.prepareStatement(sql);
            System.out.println("开始批处理删除5000条数据");
            long start=System.currentTimeMillis(); //记下开始时间
            for (int i = 1; i <= 5000; i++) {
                preparedStatement.setString(1, "机器人");
                //加入preparedStatement
                preparedStatement.addBatch();
                if (i % 1000 == 0) {
                    preparedStatement.executeBatch(); //批量处理
                    preparedStatement.clearBatch(); //处理完后的清理
                }
            }
            long end=System.currentTimeMillis(); //记下结束时间
            System.out.println("所有删除已完成，耗时"+(end-start));
        } catch (Exception e) {
            System.out.println("删除学生信息发生异常");
            e.printStackTrace();
        } finally {
            JDBCUtils.closeConnection(null, preparedStatement, connection);
        }
    }
}

四、数据库连接池

  • c3p0
  • druid（德鲁伊）

当多个连接存在时，会照成连接非常慢，连接不上，这时需要连接池解决

@Test
public void manyConnection() {
    try {
        for (int i = 0; i < 5000 ; i++) {
            Connection connection = JDBCUtils.getConnection();
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}
@Test
public void manyConnectionClose() {

    try {
        System.out.println("开始处理5000次连接");
        long start=System.currentTimeMillis(); //记下开始时间
        for (int i = 0; i < 5000 ; i++) {
            Connection connection = JDBCUtils.getConnection();
            //                System.out.println("do something...");
            connection.close();
        }
        long end=System.currentTimeMillis(); //记下结束时间
        System.out.println("5000次连接已全部完成，耗时"+(end-start));
    } catch (Exception e) {
        e.printStackTrace();
    }
}
环境配置
Maven
<dependencies>
    <!-- https://mvnrepository.com/artifact/com.mchange/c3p0 -->
    <dependency>
        <groupId>com.mchange</groupId>
        <artifactId>c3p0</artifactId>
        <version>0.9.5.4</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.2.4</version>
    </dependency>
</dependencies>
resources

jdbc.properties

driverClassName=com.mysql.cj.jdbc.Driver
username=root
password=123456
url=jdbc:mysql://localhost:3306/Student_Management_System?serverTimezone=GMT%2B8
#initial connection Size
initialSize=10
#min connection Size
minIdle=5
#max connection Size
maxActive=50
#max wait time(5000  mil seconds)
maxWait=5000

JDBCUtils

package com.ling.utils;

import java.io.FileInputStream;
import java.sql.*;
import java.util.Properties;

public class JDBCUtils {
    static String username;
    static String password;
    static String url;
    static String driver;

    static {
        try {
            Properties info = new Properties();
            info.load(new FileInputStream("E:\\IDEA\\workspace\\Student_Management_System\\Student_Management_System-01\\src\\main\\resources\\jdbc.properties"));
            username = info.getProperty("username");
            password = info.getProperty("password");
            url = info.getProperty("url");
            driver = info.getProperty("driverClassName");

            //注册驱动
            Class.forName(driver);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //得到连接的静态方法
    public static Connection getConnection() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url,username,password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    //关闭连接的静态方法
    public static void closeConnection(ResultSet rs, Statement st,Connection conn) {
        try {
            if (rs != null){
                rs.close();
            }
            if (st != null){
                st.close();
            }
            if (conn != null){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
测试c3p0
c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
  <default-config name="myc3p0">
    <property name="user">root</property>
    <property name="password">123456</property>
    <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost:3306/Student_Management_System?serverTimezone=GMT%2B8</property>

    <!--初始的连接数-->
    <property name="initialPoolSize">10</property>
    <!---->
    <property name="maxIdleTime">30</property>
    <!--每次增长的连接数-->
    <property name="acquireIncrement">5</property>
    <!--最大连接数-->
    <property name="maxPoolSize">100</property>
    <!--最小连接数-->
    <property name="minPoolSize">10</property>
    <!--可连接的最多命令对象数-->
    <property name="maxStatements">5</property>
    <!--每个对象可连接的最多的命令对象数-->
    <property name="maxStatementsPerConnection">2</property>
  </default-config>
</c3p0-config>
Test

配置文件放在src目录下，不然找不到

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.junit.Test;
import java.sql.Connection;
import java.sql.SQLException;

public class testDatabasePool {
    @Test
    public void testC3P0() {
        ComboPooledDataSource cpds = new ComboPooledDataSource("src\\main\\resources\\myc3p0");
        try {
            System.out.println("开始处理5000次连接（使用数据源c3p0）");
            long start=System.currentTimeMillis(); //记下开始时间
            for (int i = 0; i < 5000 ; i++) {
                Connection connection = cpds.getConnection();
//                System.out.println("do something...");
                connection.close();
            }
            long end=System.currentTimeMillis(); //记下结束时间
            System.out.println("5000次连接已全部完成，耗时"+(end-start));
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
测试druid
Test
import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Test;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.util.Properties;

public class testDatabasePool {
    @Test
    public void testDruid() {
        Properties properties = new Properties();
        try {
            properties.load(new FileInputStream("src\\main\\resources\\jdbc.properties"));
            DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
            System.out.println("开始处理5000次连接（使用数据源druid）");
            long start=System.currentTimeMillis(); //记下开始时间
            for (int i = 0; i < 5000 ; i++) {
                Connection connection = dataSource.getConnection();
//                System.out.println("do something...");
                connection.close();
            }
            long end=System.currentTimeMillis(); //记下结束时间
            System.out.println("5000次连接已全部完成，耗时"+(end-start));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

五、构建druid工具类

环境配置
Maven
<dependencies>
    <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.2.4</version>
    </dependency>
</dependencies>
JDBCUtils
package conm.ling.utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JDBCUtils {
    static DataSource dataSource;

    static {
        try {
            Properties properties = new Properties();
            properties.load(new FileInputStream("src\\main\\resources\\jdbc.properties"));
            //得到一个druid（德鲁伊）的数据源
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获得连接的静态方法
    public static Connection getConnection() {
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    //关闭连接的静态方法
    public static void closeConnection(ResultSet rs, Statement st, Connection conn) {
        try {
            if (rs != null){
                rs.close();
            }
            if (st != null){
                st.close();
            }
            if (conn != null){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
Test

测试类

import conm.ling.utils.JDBCUtils;
import org.junit.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class testJDBCUtils {
    @Test
    public void testDruid() {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        String sql="select * from student_member";
        try {
            preparedStatement = connection.prepareStatement(sql);
            rs = preparedStatement.executeQuery();

            //文本方式显示学生信息
            System.out.println(" \n\n   学生信息表");
            System.out.println("   ----------------------------------");
            System.out.println("       学号       密码    姓名  年龄 性别");
            System.out.println("   ----------------------------------");

            while(rs.next()){
                int id = rs.getInt("s_id");
                String password = rs.getString("s_password");
                String name = rs.getString("s_name");
                int age = rs.getInt("s_age");
                String sex = rs.getString("s_sex");
                System.out.println("   "+id+"    "+password+"   "+name+"   "+age+"   "+sex);
            }
            System.out.println("   ----------------------------------");

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeConnection(rs,preparedStatement,connection);
        }
    }
}

六、dbutils工具

环境配置
Maven
<build>
    <plugins>
        <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-compiler-plugin</artifactId>
            <configuration>
                <source>1.8</source>
                <target>1.8</target>
            </configuration>
        </plugin>
    </plugins>
</build>

<dependencies>
    <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.2.4</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/commons-dbutils/commons-dbutils -->
    <dependency>
        <groupId>commons-dbutils</groupId>
        <artifactId>commons-dbutils</artifactId>
        <version>1.7</version>
    </dependency>
</dependencies>
Student

实体类

package com.ling.bean;

public class Student {
    private int s_id;
    private String s_password;
    private String s_name;
    private String s_age;
    private String s_sex;
    private String s_SClass;
    private String s_department;
    private String s_telephone;
    private String s_address;

    public Student() {
    }

    public Student(int s_id, String s_password, String s_name, String s_age, String s_sex, String s_SClass, String s_department, String s_telephone, String s_address) {
        this.s_id = s_id;
        this.s_password = s_password;
        this.s_name = s_name;
        this.s_age = s_age;
        this.s_sex = s_sex;
        this.s_SClass = s_SClass;
        this.s_department = s_department;
        this.s_telephone = s_telephone;
        this.s_address = s_address;
    }

    public int getS_id() {
        return s_id;
    }

    public void setS_id(int s_id) {
        this.s_id = s_id;
    }

    public String getS_password() {
        return s_password;
    }

    public void setS_password(String s_password) {
        this.s_password = s_password;
    }

    public String getS_name() {
        return s_name;
    }

    public void setS_name(String s_name) {
        this.s_name = s_name;
    }

    public String getS_age() {
        return s_age;
    }

    public void setS_age(String s_age) {
        this.s_age = s_age;
    }

    public String getS_sex() {
        return s_sex;
    }

    public void setS_sex(String s_sex) {
        this.s_sex = s_sex;
    }

    public String getS_SClass() {
        return s_SClass;
    }

    public void setS_SClass(String s_SClass) {
        this.s_SClass = s_SClass;
    }

    public String getS_department() {
        return s_department;
    }

    public void setS_department(String s_department) {
        this.s_department = s_department;
    }

    public String getS_telephone() {
        return s_telephone;
    }

    public void setS_telephone(String s_telephone) {
        this.s_telephone = s_telephone;
    }

    public String getS_address() {
        return s_address;
    }

    public void setS_address(String s_address) {
        this.s_address = s_address;
    }

    @Override
    public String toString() {
        return "Student{" +
                "s_id=" + s_id +
                ", s_password='" + s_password + '\'' +
                ", s_name='" + s_name + '\'' +
                ", s_age='" + s_age + '\'' +
                ", s_sex='" + s_sex + '\'' +
                ", s_SClass='" + s_SClass + '\'' +
                ", s_department='" + s_department + '\'' +
                ", s_telephone='" + s_telephone + '\'' +
                ", s_address='" + s_address + '\'' +
                '}';
    }
}
JDBCUtils
package com.ling.utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;

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

public class JDBCUtils {
    static DataSource dataSource;

    static {
        try {
            Properties properties = new Properties();
            properties.load(new FileInputStream("src\\main\\resources\\jdbc.properties"));
            //得到一个druid（德鲁伊）的数据源
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection() {
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    //关闭连接的静态方法
    public static void closeConnection(ResultSet rs, Statement st, Connection conn) {
        try {
            if (rs != null){
                rs.close();
            }
            if (st != null){
                st.close();
            }
            if (conn != null){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
dbUtils底层原理
//connection连接code
//SQL语句code
PreparedStatement preparedStatement = connection.preparedStatement(sql)
ResultSet resultSet = preparedStatement.executeQuery();
List<Boys> list = new ArrayList();
while(resultSet.next()) {
	int id = resultSet.getInt("id");
	String name = resultSet.getString("name");
	Student student = new Student(id,name);
	list.add(student);
}
System.out.print(list);
Test
查询学生（返回多条记录）

query(connection, sql, new BeanListHandler<>(xxx.class),1)

参数解析

  • connection：连接connection
  • sql：SQL语句
  • 用BeanListHandler返回一个List集合（在方法中会使用反射生成Student对象，然后调用该对象的setXXX方法，因此必须提供无参构造和setXXX方法）
  • 1：可变形参（给SQL语句里的问号赋值的参数）
import com.ling.bean.Student;
import com.ling.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;
import java.sql.Connection;
import java.util.List;


public class testDbUtils {
    @Test
    public void testQuery() throws Exception {
        Connection connection = JDBCUtils.getConnection();
        //执行crud
        QueryRunner qr = new QueryRunner();
        String sql="select * from student_member";
        List<Student> list = qr.query(connection, sql, new BeanListHandler<>(Student.class));
        for (Student student:list) {
            System.out.println(student);
        }
        //关闭连接
        JDBCUtils.closeConnection(null,null,connection);
    }
}
添加学生（update，insert，delete）
import com.ling.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.junit.Test;
import java.sql.Connection;

public class testDbUtils {
    @Test
    public void testUpdate() throws Exception {
        Connection connection = JDBCUtils.getConnection();
        QueryRunner qr = new QueryRunner();
        String sql="insert into student_member values(?,?,?,?,?,?,?,?,?)";
        int num=qr.update(connection,sql,2019250110,"0000","机器人",20,"男",190,"机器人学院","12345678912","地球");
        System.out.println(num>0 ? "ok" : "fail");
        JDBCUtils.closeConnection(null,null,connection);
    }
}
修改学生的数据
import com.ling.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.junit.Test;
import java.sql.Connection;

public class testDbUtils {
    @Test
    public void testUpdate2() throws Exception {
        Connection connection = JDBCUtils.getConnection();
        QueryRunner qr = new QueryRunner();
        String sql="update student_member set s_password=? where s_id=?";
        int num=qr.update(connection,sql,"000",2019250110);
        System.out.println(num>0 ? "ok" : "fail");
        JDBCUtils.closeConnection(null,null,connection);
    }
}
使用DBUtils返回单行记录(即返回一个对象)

BeanHandler<>返回一个对象（在方法中会使用反射生成Student对象，然后调用该对象的setXXX方法，因此必须提供无参构造和setXXX方法）

import com.ling.bean.Student;
import com.ling.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.junit.Test;
import java.sql.Connection;

public class testDbUtils {
    @Test
    public void testQuerySingle() throws Exception {
        Connection connection = JDBCUtils.getConnection();
        QueryRunner qr = new QueryRunner();
        String sql="select * from student_member where s_id=?";
        Student student = qr.query(connection, sql, new BeanHandler<>(Student.class),2019250101);
        System.out.println(student);
        JDBCUtils.closeConnection(null,null,connection);
    }
}
使用DBUtils返回单行单列的一个集合（Object）

ScalarHandler()一个集合（Object），如果是多行多列，默认返回第一行第一列

import com.ling.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;
import java.sql.Connection;

public class testDbUtils {
    @Test
    public void testScalar() throws Exception {
        Connection connection = JDBCUtils.getConnection();
        QueryRunner qr = new QueryRunner();
        String sql="select * from student_member where s_id=?";
        Object query = qr.query(connection, sql, new ScalarHandler(),2019250101);
        System.out.println(query);
        JDBCUtils.closeConnection(null,null,connection);
    }
}

七、BasicDao构建

环境配置

Maven

导入druid和dbutils

<!--Java8-->
<properties>
    <maven.compiler.target>1.8</maven.compiler.target>
    <maven.compiler.source>1.8</maven.compiler.source>
</properties>
<dependencies>
    <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.2.4</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/commons-dbutils/commons-dbutils -->
    <dependency>
        <groupId>commons-dbutils</groupId>
        <artifactId>commons-dbutils</artifactId>
        <version>1.7</version>
    </dependency>
</dependencies>
Student
package com.ling.bean;

public class Student {
    private int s_id;
    private String s_password;
    private String s_name;
    private String s_age;
    private String s_sex;
    private String s_SClass;
    private String s_department;
    private String s_telephone;
    private String s_address;

    public Student() {
    }

    public Student(int s_id, String s_password, String s_name, String s_age, String s_sex, String s_SClass, String s_department, String s_telephone, String s_address) {
        this.s_id = s_id;
        this.s_password = s_password;
        this.s_name = s_name;
        this.s_age = s_age;
        this.s_sex = s_sex;
        this.s_SClass = s_SClass;
        this.s_department = s_department;
        this.s_telephone = s_telephone;
        this.s_address = s_address;
    }

    public int getS_id() {
        return s_id;
    }

    public void setS_id(int s_id) {
        this.s_id = s_id;
    }

    public String getS_password() {
        return s_password;
    }

    public void setS_password(String s_password) {
        this.s_password = s_password;
    }

    public String getS_name() {
        return s_name;
    }

    public void setS_name(String s_name) {
        this.s_name = s_name;
    }

    public String getS_age() {
        return s_age;
    }

    public void setS_age(String s_age) {
        this.s_age = s_age;
    }

    public String getS_sex() {
        return s_sex;
    }

    public void setS_sex(String s_sex) {
        this.s_sex = s_sex;
    }

    public String getS_SClass() {
        return s_SClass;
    }

    public void setS_SClass(String s_SClass) {
        this.s_SClass = s_SClass;
    }

    public String getS_department() {
        return s_department;
    }

    public void setS_department(String s_department) {
        this.s_department = s_department;
    }

    public String getS_telephone() {
        return s_telephone;
    }

    public void setS_telephone(String s_telephone) {
        this.s_telephone = s_telephone;
    }

    public String getS_address() {
        return s_address;
    }

    public void setS_address(String s_address) {
        this.s_address = s_address;
    }

    @Override
    public String toString() {
        return "Student{" +
                "s_id=" + s_id +
                ", s_password='" + s_password + '\'' +
                ", s_name='" + s_name + '\'' +
                ", s_age='" + s_age + '\'' +
                ", s_sex='" + s_sex + '\'' +
                ", s_SClass='" + s_SClass + '\'' +
                ", s_department='" + s_department + '\'' +
                ", s_telephone='" + s_telephone + '\'' +
                ", s_address='" + s_address + '\'' +
                '}';
    }
}
JDBCUtils
package com.ling.utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JDBCUtils {
    static DataSource dataSource;

    static {
        try {
            Properties properties = new Properties();
            properties.load(new FileInputStream("src\\main\\resources\\jdbc.properties"));
            //得到一个druid（德鲁伊）的数据源
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection() {
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    //关闭连接的静态方法
    public static void closeConnection(ResultSet rs, Statement st, Connection conn) {
        try {
            if (rs != null){
                rs.close();
            }
            if (st != null){
                st.close();
            }
            if (conn != null){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

StudentDao

package com.ling.dao;

public interface StudentDao {
    public void ok();
}

StudentDaoImpl

package com.ling.dao.impl;

import com.ling.bean.Student;
import com.ling.dao.StudentDao;

public class StudentDaoImpl extends BasicDao<Student> implements StudentDao {
    @Override
    public void ok() {
        System.out.println("ok");
    }
}

BasicDao

package com.ling.dao.impl;
import com.ling.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class BasicDao<T> {
    QueryRunner qr = new QueryRunner();

    //返回多条记录（即返回多个对象）
    public List<T> queryMul(String sql,Class<T> clazz,Object... parameters) {
        Connection connection = JDBCUtils.getConnection();

        try {
            return qr.query(connection, sql, new BeanListHandler<T>(clazz),parameters);
        } catch (SQLException e) {
            //调用方法的时候可以catch可以不catch
            throw new RuntimeException(e);
        }finally {
            //关闭连接
            JDBCUtils.closeConnection(null,null,connection);
        }
    }
    //测试添加学生（update，insert，delete）
    public int cud(String sql,Object... parameters) {
        Connection connection = JDBCUtils.getConnection();
        try {
            return qr.update(connection,sql,parameters);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtils.closeConnection(null,null,connection);
        }
    }
    //返回一条记录（即返回一个对象）
    public T querySingle(String sql,Class<T> clazz,Object... parameters) {
        Connection connection = JDBCUtils.getConnection();
        try {
            return qr.query(connection, sql, new BeanHandler<>(clazz),parameters);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtils.closeConnection(null,null,connection);
        }
    }
    //使用DBUtils返回单行单列的一个集合（Object）
    public Object queryScalar(String sql,Object... parameters) {
        Connection connection = JDBCUtils.getConnection();
        try {
            return qr.query(connection, sql, new ScalarHandler(),parameters);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtils.closeConnection(null,null,connection);
        }
    }
}
配置分解
返回多条记录

List：泛型

String sql：SQL语句

Class clazz：实体类

Object… parameters：参数（多个参数）

public List<T> queryMul(String sql,Class<T> clazz,Object... parameters) {
    Connection connection = JDBCUtils.getConnection();

    try {
        return qr.query(connection, sql, new BeanListHandler<T>(clazz),parameters);
    } catch (SQLException e) {
        //调用方法的时候可以catch可以不catch
        throw new RuntimeException(e);
    }finally {
        //关闭连接
        JDBCUtils.closeConnection(null,null,connection);
    }
}
修改、添加、删除

update，insert，delete

public int cud(String sql,Object... parameters) {
    Connection connection = JDBCUtils.getConnection();
    try {
        return qr.update(connection,sql,parameters);
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }finally {
        JDBCUtils.closeConnection(null,null,connection);
    }
}
返回一条记录

即返回一个对象

public T querySingle(String sql,Class<T> clazz,Object... parameters) {
    Connection connection = JDBCUtils.getConnection();
    try {
        return qr.query(connection, sql, new BeanHandler<>(clazz),parameters);
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }finally {
        JDBCUtils.closeConnection(null,null,connection);
    }
}
返回单行单列的一个集合（Object）
public Object queryScalar(String sql,Object... parameters) {
    Connection connection = JDBCUtils.getConnection();
    try {
        return qr.query(connection, sql, new ScalarHandler(),parameters);
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }finally {
        JDBCUtils.closeConnection(null,null,connection);
    }
}

代码资源

本文章的代码资源：点击这里

siriling
关注
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

打赏作者

siriling

作者编码，总结不已，希望各位多

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

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

打赏作者

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

抵扣说明：

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

余额充值