JDBC

发表于 2019-03-13 更新于 2020-12-01 阅读次数：本文字数： 20k 阅读时长 ≈ 18 分钟

基本使用步骤

在官网下载对应的jar包并导入项目
加载驱动

Class.forName(“com.mysql.jdbc.Driver);
创建一个链接

Connection conn = DriverManager.getConnection(“url”,”user”,”password”);
执行完所有语句后关闭连接

conn.close();

package hellojdbc;

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

/**
 * 1. 导入jar包
 * 2. 注册驱动
 * 3. 创建数据库连接
 */
public class JDBCConnDemo {
    public static void main(String[] args) {
        Connection conn = null;
        try {
            //注册驱动
            Class.forName("com.mysql.jdbc.Driver");

            /**
             * 不这样写的原因：
             * 1. 会导致注册两次
             * 2. 会时程序依赖于MySQL数据包
             */
            //DriverManager.registerDriver(new Driver());//第二种注册方式

            //使用DriverManager获取一个数据库连接
            conn = DriverManager.getConnection(
                    //指定数据库类型，所在URL，所用数据库，用户名及密码
                    "jdbc:mysql://127.0.0.1:3306/blog?useSSL=false"
                    ,"root","xxxxxx");
            //进行增删查改操作
            System.out.println("连接成功");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        //释放资源
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println("关闭成功");
    }
}

执行SQL语句

查询语句

package hellojdbc;

import java.sql.*;

public class JDBCDemo2 {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(
                    "jdbc:mysql://127.0.0.1:3306/blog?useSSL=false",
                    "root",
                    "xxx"
            );
            //使用connection创建一个Statement对象（执行SQL语句的工具接口）
            statement = connection.createStatement();

            /**
             * statement执行SQL语句的方法：
             * 1. execute()可以执行任何SQL语句，返回boolean值
             *    执行后第一个结果是ResultSet，否则返回true
             * 2. executeQuery()执行select()语句，返回查询到的结果集
             * 3. executeUpdate()执行DML(insert,update,delete)语句，返回影响的记录条数
             */
            resultSet = statement.executeQuery("select * from categories");
            //获取结果
            while (resultSet.next()){
                //根据第几列获取值
                int id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                //根据字段名获取值
                int pid = resultSet.getInt("pid");
                String path = resultSet.getString("path");
                System.out.println(id+" "+name+" "+pid+" "+path);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if(resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(statement!=null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(connection!=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

DML语句

package hellojdbc;

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

public class JDBCDemo3 {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(
                    "jdbc:mysql://127.0.0.1:3306/blog?useSSL=false",
                    "root",
                    "xxxxxx"
            );
            statement = connection.createStatement();
            //返回执行受影响的条数，执行失败返回0
            int i = statement.executeUpdate(
                    "update categories set path='改一下试试' where id=1"
            );
            if(i>0){
                System.out.println("更新成功");
            }else{
                System.out.println("更新失败");
            }

            int j = statement.executeUpdate(
                    "insert into categories(name,pid,path) values ('插入语句',2,'要用单引号');"
            );
            if(j>0){
                System.out.println("插入成功");
            }else{
                System.out.println("插入失败");
            }

            int k = statement.executeUpdate(
                    "delete from categories where id = 2;"
            );
            if(k>0){
                System.out.println("删除成功");
            }else{
                System.out.println("删除失败");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if(statement != null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(connection != null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

使用PreparedStatement防止SQL注入

package hellojdbc;

import java.sql.*;

public class JDBCDemo4 {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(
                    "jdbc:mysql://127.0.0.1/blog?useSSL=false",
                    "root",
                    "xxx"
            );
            //使用？占位符，执行时会替换成实际数据，预编译
            preparedStatement = connection.prepareStatement("select * from students where score != ?");
            //给preparedStatement设置参数,第一个参数是索引从1开始，第二个是具体值
            preparedStatement.setInt(1,0);
            //执行
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                System.out.println(resultSet.getString(1)+"\t"
                    +resultSet.getInt(2)+"\t"
                    +resultSet.getString(3)+"\t"
                    +resultSet.getInt(4));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if(connection!=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(preparedStatement!=null){
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

批量插入

package hellojdbc;

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

public class JDBCDemo5 {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        Random r = new Random();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(
                    "jdbc:mysql://127.0.0.1/blog?useSSL=false",
                    "root",
                    "xxx");
            statement = connection.createStatement();//每次都要写语句，无法避免SQL注入
            //设置事务不自动提交
            connection.setAutoCommit(false);
            for (int i = 0; i < 10000; i++) {
                //添加到缓存区
                statement.addBatch("insert into students(stuName,age,course,score) " +
                        "values ('测试" + i + "'," + r.nextInt(30) + ",'语数英'," + r.nextInt(100) + ")");
                if (i % 50 == 0) {
                    //执行批处理
                    int[] ints = statement.executeBatch();
                    System.out.println(ints.length);
                    statement.clearBatch();//清理缓存区
                }
            }
            //手动提交事务
            statement.executeBatch();
            connection.commit();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            try {
                connection.rollback();//出错回滚
                connection.commit();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            if(statement!= null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

可以使用prepareStatement避免SQL注入

prepareStatement ps = connection.prepareStatement("insert into students(name,age) values (?,?)");
for (int i=0;i<100;i++){
    ps.setString(1,"xxx");
    ps.setInt(2,1);
    //放入批处理
    ps.addBatch();

    int[] rs = ps.executeBatch();
    ps.clearBatch();
}

读取和写入二进制文件

package hellojdbc;

import java.io.*;
import java.sql.*;

public class JDBCDemo6 {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement1 = null;
        PreparedStatement preparedStatement2 = null;
        PreparedStatement preparedStatement3 = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(
                    "jdbc:mysql://127.0.0.1/blog?useSSL=false",
                    "root",
                    "zm19980225"
            );
            String sql1 = "drop table if exists xmlTables";
            String sql2 = "create table xmlTables(" +
                    "id int primary key auto_increment," +
                    "data text)";
            preparedStatement1 = connection.prepareStatement(sql1);
            //执行sql1
            preparedStatement1.execute();
            //执行sql2
            preparedStatement1.execute(sql2);

            //添加数据
            preparedStatement2 = connection.prepareStatement("insert into xmlTables(data) values (?)");
            File file = new File("src/hellojdbc/emp.xml");
            InputStream in = new FileInputStream(file);
            //此处有三种方法：
            //preparedStatement2.setAsciiStream(1,in,file.length());
            //preparedStatement2.setCharacterStream(1,new InputStreamReader(in),file.length());
            preparedStatement2.setBinaryStream(1,in,file.length());
            int i = preparedStatement2.executeUpdate();
            if(i>0){
                System.out.println("添加成功");
            }
            //读取数据
            preparedStatement3 = connection.prepareStatement("select * from xmlTables where id=?");
            preparedStatement3.setInt(1,1);
            ResultSet resultSet = preparedStatement3.executeQuery();
            if(resultSet.next()){
                int id = resultSet.getInt("id");
                InputStream data = resultSet.getBinaryStream("data");
                System.out.println("id:"+id);
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                byte[] bytes = new byte[1024];
                int curr = -1;
                while((curr = data.read(bytes))!=-1){
                    baos.write(bytes,0,curr);
                }
                System.out.println("data:"+baos.toString());
                baos.close();
                data.close();
            }
            in.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                preparedStatement3.close();
                preparedStatement2.close();
                preparedStatement1.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

设置隔离级别和保存点

//设置隔离即便
connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

//保存点，回滚的时候指定只回滚到指定位置
SavaPoint savepoint = connection.setSavepoint("save1");//上面的语句不会回滚
connection.rollback(savepoint);

数据库连接池

创建一个连接池，每次需要的的时候从连接池获取连接，减少每次用户都要从服务器获取连接，效率低
使用DataSource接口

package hellojdbc;

import hellojdbc.com.xm.util.DbUtiles;

import javax.sql.DataSource;
import java.io.*;
import java.sql.*;
import java.util.*;
import java.util.logging.Logger;

public class MyJDBCPool implements DataSource {
    //实现线程同步控制
    private static List<Connection> list = Collections.synchronizedList(new LinkedList<>());
    static {
        try {
            Properties properties = new Properties();
            InputStream is = DbUtiles.class.getClassLoader().getResourceAsStream("database.properties");
            properties.load(is);
            is.close();
            String driver = properties.getProperty("driver");
            String url = properties.getProperty("url");
            String usename = properties.getProperty("user");
            String password = properties.getProperty("password");
            Class.forName(driver);
            for(int i=0;i<5;i++){
                Connection connection = DriverManager.getConnection(url,usename,password);
                list.add(connection);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public Connection getConnection() throws SQLException {
        if(list.size()>0){
            Connection connection = list.remove(0);
            System.out.println("还剩"+list.size()+"个");
            return connection;
        }
        return null;
    }

    /**
     * 用完释放放回
     * @param connection
     */
    public void release(Connection connection){
        list.add(connection);
        System.out.println("放回成功");
    }
    @Override
    public Connection getConnection(String username, String password) throws SQLException {
        return null;
    }

    @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 ConnectionBuilder createConnectionBuilder() throws SQLException {
        return null;
    }

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

    @Override
    public ShardingKeyBuilder createShardingKeyBuilder() 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;
    }
}

public class PoolTest {
    public static void main(String[] args) {
        MyJDBCPool pool = new MyJDBCPool();
        for(int i=0;i<5;i++){
            try {
                Connection connection = pool.getConnection();
                System.out.println("使用："+connection.hashCode());
                pool.release(connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

DBCP连接池

需要下载三个包：

commons-dbcp.jar
commons-pool.jar
commons-logging.jar

配置文件

driver = com.mysql.jdbc.Driver
url = jdbc:mysql://127.0.0.1:3306/blog?useSSL=false
username = root
password = xxx

#初始化连接
initialSize = 5

#最大连接数量
maxActive = 10

#最大空闲数量
maxIdle = 6

#最小空闲数量
minIdle = 2

#超时等待时间，以毫秒为单位
maxWaitMillis = 60000

package hellojdbc;

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;

public class DBCPTest {
    public static void main(String[] args) {
        try {
            BasicDataSourceFactory factory = new BasicDataSourceFactory();
            Properties properties = new Properties();
            InputStream is = DBCPTest.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            properties.load(is);
            BasicDataSource pool = factory.createDataSource(properties);

            for (int i = 0; i < 50; i++) {
                Connection conn = pool.getConnection();
                System.out.println(conn);
                conn.close();
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

C3P0连接池

需要下载c3p0-0.9.5.2.bin，导入两个包

c3p0.jar
mchange-commons-java.jar

配置文件

<c3p0-config>
    <!-- 默认配置，如果没有指定则使用这个配置 -->
    <default-config>
        <!-- 基本配置 -->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/blog?useSSL=false</property>
        <property name="user">root</property>
        <property name="password">zm19980225</property>
        <!--扩展配置 -->
        <!-- 连接超过30秒报错 -->
        <property name="checkoutTimeout">30000</property>
        <!-- 30秒检查空闲连接 -->
        <property name="idleConnectionTestPeriod">30</property>
        <!-- 初始化池大小 -->
        <property name="initialPoolSize">2</property>
        <!-- 最大空闲时间 -->
        <property name="maxIdleTime">30</property>
        <!-- 最多有多少个连接 -->
        <property name="maxPoolSize">10</property>
        <!-- 最少几个连接 -->
        <property name="minPoolSize">2</property>
        <!-- 每次最多可以执行多少个批处理语句 -->
        <property name="maxStatements">50</property>
    </default-config>
    <!-- 命名的配置 -->
    <named-config name="xiaoming">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/blog?useSSL=false</property>
        <property name="user">root</property>
        <property name="password">zm19980225</property>
        <!-- 如果池中数据连接不够时一次增长多少个 -->
        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">100</property>
        <property name="minPoolSize">50</property>
        <property name="maxPoolSize">1000</property>
        <property name="maxStatements">0</property>
        <property name="maxStatementsPerConnection">5</property>
    </named-config>
</c3p0-config>

package hellojdbc;

import com.mchange.v2.c3p0.ComboPooledDataSource;

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

public class C3P0Test {
    public static void main(String[] args) {
        //创建连接，指定所用配置，文件命名使用“c3p0-config.xml"或者"c3p0-config.properties",默认路径在rc目录下
        ComboPooledDataSource pool = new ComboPooledDataSource("xiaoming");
        //使用
        for (int i = 0; i < 30; i++) {
            try {
                Connection conn = pool.getConnection();
                System.out.println(conn.getClass());
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

阿里Druid连接池

官方文档

url = jdbc:mysql://127.0.0.1:3306/blog?useSSL=false
username = root
password = zm19980225
driverClassName = com.mysql.jdbc.Driver
# 初始化连接池大小
initialSize = 10
# 最大连接池数
maxActive = 20
# 最小链接池数
minIdle = 5
# 获取连接时最大等待时间，单位毫秒
maxWait = 30000

package hellojdbc;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

public class DruidTest {
    public static void main(String[] args) {
        //创建连接池
        DruidDataSource dataSource = null;
        try {
            Properties properties = new Properties();
            InputStream is = DruidTest.class.getClassLoader().getResourceAsStream("druid.properties");
            properties.load(is);
            is.close();
            //druid数据源工厂
            dataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
        for (int i = 0; i < 30; i++) {
            try {
                Connection conn = dataSource.getConnection();
                System.out.println(conn.getClass().getName());
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Apache封装的DbUtiles用法

package DBUtilsTest;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import hellojdbc.DruidTest;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

public class DruidUtiles {
    private static DruidDataSource dataSource;
    static {
        try {
            //1.加载集合
            Properties properties = new Properties();
            InputStream is = DruidTest.class.getClassLoader().getResourceAsStream("druid.properties");
            properties.load(is);
            is.close();
            dataSource = (DruidDataSource)DruidDataSourceFactory.createDataSource(properties);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static DataSource getDataSource(){
        return dataSource;
    }
}

package DBUtilsTest;

import com.xm.beans.Student;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class DBUtilesDemo {
    public static void main(String[] args) {
        //创建QueryRunner
        QueryRunner queryRunner = new QueryRunner(DruidUtiles.getDataSource());

        try {
            //查询一条语句变成一个数组
            Object[] objects = queryRunner.query("select * from students where id=1",new ArrayHandler());
            for (Object o : objects) {
                System.out.println(o);
            }

            //查询多条数据
            List<Object[]> objects1 = queryRunner.query("select * from students", new ArrayListHandler());
            for (Object[] o : objects1) {
                System.out.println(o);
            }

            //查询数据的某一列
            List<String> stuName = queryRunner.query("select * from students", new ColumnListHandler<String>("stuName"));
            for (String s : stuName) {
                System.out.println(s);
            }

            //查询一条数据，把输出放入Map集合，key存属性，value存数据
            Map<String,Object> map = queryRunner.query("select * from students where id=1",new MapHandler());
            for (Map.Entry<String,Object> entry:map.entrySet()) {
                System.out.println(entry.getKey()+"=>"+entry.getValue());
            }

            //查询数据个数
            Long total = queryRunner.query("select count(*) from students",new ScalarHandler<Long>());
            System.out.println(total);

            //查询一条记录，把它封装成对象
            Student student = queryRunner.query("select * from students where id=1",new BeanHandler<Student>(Student.class));
            System.out.println(student.toString());

            //查询全部数据并封装成对象放入集合
            List<Student> students = queryRunner.query("select * from students",new BeanListHandler<Student>(Student.class));
            for (Student student1 : students) {
                System.out.println(student1.toString());
            }

            //执行删除语句
            int result  = queryRunner.update("delete from students where id=?",10);
            System.out.println(result);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}