JDBC

1.下载jar包

1. 下载地址：mvnrepository

2. 搜索jar包
   

3. 下载对应的版本

2.导入包

项目的根目录下新建lib目录，将下载好的包复制进去，最后右击lib目录->Add as Library...

3.编写代码

package com.sanduo.lesson01;


import java.sql.*;

// 第一个jdbc程序
public class JdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1. 加载驱动
        //DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        Class.forName("com.mysql.jdbc.Driver"); //反射实现
        //2. 用户信息url
        // useUnicode=true&characterEncoding=utf8&useSSL=true
        String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String password = "root";
        //3. 连接成功，数据库对象 Connection 代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);
        //4. 执行SQL的对象 Statement 执行SQL
        Statement statement = connection.createStatement();
        //5. 执行SQL的对象去执行SQL
        String sql = "select * from `user`";
        ResultSet resultSet = statement.executeQuery(sql);// 回返的结果集
        if(!resultSet.next()){
            System.out.println("暂无数据");
        }
        while (resultSet.next()){
            System.out.println("id="+resultSet.getObject("id"));
            System.out.println("name="+resultSet.getObject("name"));
            System.out.println("age="+resultSet.getObject("age"));
            System.out.println("password="+resultSet.getObject("password"));
        }

        //6. 释放连接
        resultSet.close();
        statement.close();
        connection.close();

    }
}

4.分析代码

DriverManager

//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver"); //反射实现

//Connection  代表数据库
// 数据库设置自动提交
// 事务提交
// 事务回滚
connection.rollback();
connection.commit();
connection.setAutoCommit();

Connection connection = DriverManager.getConnection(url, username, password);

URL

String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true";
// mysql --3306
//协议:mysql://主机地址:端口号/数据库名?参数1&参数2&参数3
// oralce -- 1521
//jdbc:oralce:thin:@主机地址:localhost：sid

Statement 执行SQL的对象 PrepareStatement 执行SQL的对象

 String sql = "select * from `user`";
        statement.executeQuery();// 查询操作返回结果集
        statement.executeUpdate();// 执行任何SQL
        statement.execute();//更新、插入、删除都是这个，返回受影响的行数

ResultSet 查询的结果集：封装了所有的查询结果

获得指定的数据类型

        resultSet.getObject();// 在不知道列类型的时候使用
        // 如果知道列类型就指定使用了
        resultSet.getString();
        resultSet.getInt();
        resultSet.getFloat();
        resultSet.getDouble();
        resultSet.getDate();

遍历，指针

   resultSet.beforeFirst();//移动最前面
        resultSet.afterLast();//移动到最后面
        resultSet.next();//移动到下一个数据
        resultSet.previous();//移动到上一个数据
        resultSet.absolute(row);//移动到指定行

释放资源

  //6. 释放连接
        resultSet.close();
        statement.close();
        connection.close();

5.提取工具类

1.src 目录下新建db.properties文件

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456

2.创建工具类 JdbcUtils，代码如下

package com.sanduo.lesson02.utils;

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

public class JdbcUtils {
    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;

    static {
        try {
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");
            // 驱动只用加载一次
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    // 释放连接资源
    public static void release(Connection conn, Statement st, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if (st != null) {
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

}

新增数据

package com.sanduo.lesson02;

import com.sanduo.lesson02.utils.JdbcUtils;

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

public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            //获取数据库连接
            conn = JdbcUtils.getConnection();
            // 获得sql的执行对象
            st = conn.createStatement();
            String sql = "INSERT INTO `users` (id,`name`,`password`,`desc`,`email`) VALUES(40,'XXXX','123456','DDDDD','1052543176@QQ.COM')";
            int i = st.executeUpdate(sql);
            if(i>0){
                System.out.println("插入成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

更新数据

package com.sanduo.lesson02;

import com.sanduo.lesson02.utils.JdbcUtils;

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

public class TestUpdate {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            //获取数据库连接
            conn = JdbcUtils.getConnection();
            // 获得sql的执行对象
            st = conn.createStatement();
            String sql = "update `users` set name='sanduo' WHERE `id`= 40";
            int i = st.executeUpdate(sql);
            if(i>0){
                System.out.println("修改成功");
            }else{
                System.out.println("修改失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

删除数据

package com.sanduo.lesson02;

import com.sanduo.lesson02.utils.JdbcUtils;

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

public class TestDelete {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            //获取数据库连接
            conn = JdbcUtils.getConnection();
            // 获得sql的执行对象
            st = conn.createStatement();
            String sql = "DELETE FROM `users` WHERE `id`=1";
            int i = st.executeUpdate(sql);
            if(i>0){
                System.out.println("删除成功");
            }else{
                System.out.println("删除失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

查询数据

package com.sanduo.lesson02;

import com.sanduo.lesson02.utils.JdbcUtils;

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

public class TestQuery {
    public static void main(String[] args) {
        Connection conn = null;
        Statement  st   = null;
        ResultSet  rs   = null;

        // 新建连接
        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();

            // sql
            String sql = "select * from users where id = 40";
            rs = st.executeQuery(sql);//查询完毕会返回结果集
            while (rs.next()){
                System.out.println(rs.getObject("id"));
                System.out.println(rs.getObject("name"));
                System.out.println(rs.getObject("desc"));
                System.out.println(rs.getObject("email"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

6.SQL注入

sql存在漏洞，容易被攻击导致数据泄露

7.PrepareStatement对象

PrepareStatement 可以防止SQL注入，效率更好！

新增数据

package com.sanduo.lesson03;

import com.sanduo.lesson02.utils.JdbcUtils;

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

public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JdbcUtils.getConnection();
            // 区别
            // 使用问号占位符，代替参数
            String sql = "INSERT INTO `users` (id,`name`,`password`,`desc`,`email`) VALUES(?,?,?,?,?)";
            st = conn.prepareStatement(sql);//预编译sql,先写sql，然后不执行
            // 手动给参数赋值
            st.setInt(1,42);
            st.setString(2,"hahah");
            st.setString(3,"123456");
            st.setString(4,"10525431761@qq.com");
            st.setString(5,"10525431761@qq.com");

            // 执行
            int i = st.executeUpdate();
            if(i>0){
                System.out.println("插入成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

更新数据

package com.sanduo.lesson03;

import com.sanduo.lesson02.utils.JdbcUtils;

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

public class TestUpdate {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JdbcUtils.getConnection();
            // 区别
            // 使用问号占位符，代替参数
            String sql = "update `users` set name=? WHERE `id`= ?";
            st = conn.prepareStatement(sql);//预编译sql,先写sql，然后不执行
            // 手动给参数赋值
            st.setString(1,"hahah");
            st.setInt(2,42);


            // 执行
            int i = st.executeUpdate();
            if(i>0){
                System.out.println("更新成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

删除数据

package com.sanduo.lesson03;

import com.sanduo.lesson02.utils.JdbcUtils;

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

public class TestDelete {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JdbcUtils.getConnection();
            // 区别
            // 使用问号占位符，代替参数
            String sql = "DELETE FROM `users` WHERE `id`=?";
            st = conn.prepareStatement(sql);//预编译sql,先写sql，然后不执行
            // 手动给参数赋值
            st.setInt(1,40);


            // 执行
            int i = st.executeUpdate();
            if(i>0){
                System.out.println("删除成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

查询数据

package com.sanduo.lesson03;

import com.sanduo.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestQuery {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();
            // 区别
            // 使用问号占位符，代替参数
            String sql = "select * from users where id = ?";
            st = conn.prepareStatement(sql);//预编译sql,先写sql，然后不执行
            // 手动给参数赋值
            st.setInt(1, 42);
            // 执行
            rs = st.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getObject("id"));
                System.out.println(rs.getObject("name"));
                System.out.println(rs.getObject("desc"));
                System.out.println(rs.getObject("email"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, rs);
        }
    }
}

SQL注入

package com.sanduo.lesson03;

import com.sanduo.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class SQL注入 {

    public static void main(String[] args) {
        //login("1111","123456");
        login("'' or 1=1", "123456");
    }

    // 登录业务
    public static void login(String username, String password) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        System.out.println(username);
        System.out.println(password);
        // 新建连接
        try {
            conn = JdbcUtils.getConnection();
            // PrepareStatement 防止SQL注入的本质,把传递进来的参数当作字符
            // 假设其中存在转译字符，就直接忽略，' 会被直接忽略
            String sql = "select * from users where  `name`=? and password=?";
            st = conn.prepareStatement(sql);
            st.setString(1, username);
            st.setString(2, password);

            rs = st.executeQuery();//查询完毕会返回结果集
            while (rs.next()) {
                System.out.println(rs.getObject("id"));
                System.out.println(rs.getObject("name"));
                System.out.println(rs.getObject("desc"));
                System.out.println(rs.getObject("email"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, rs);
        }
    }
}

9.数据池

编写连接池，实现一个接口DataSource

开源数据源实现 拿来即用

DBCP
C3P0
Druid:阿里巴巴

使用了这些数据库连接池之后，我们在项目开发中就不需要编写连接数据库的代码了

DBCP
commons-dbcp-1.4.jar
commons-pool-1.6.jar

C3P0
c3p0-0.9.2.1.jar
mchange-commons-java-0.2.3.4.jar

10.DBCP

1.导入包

commons-dbcp-1.4.jar
commons-pool-1.6.jar

2.配置文件

在src目录下创建 dbcpconfig.properties文件
配置文件如下：

#连接配置 这里的名字，是DBCP数据源中定义好的
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=root

#初始化连接
initialSize=10

#最大连接数
maxActive=50

#最大空闲连接
maxIdle=20

#最小空闲连接
minIdle=5

#超时等待以毫秒为单位计算 6000毫秒/1000 = 60秒
maxWait=60000

#JDBC驱动建立连接时附带的连接属性的格式必须为这样：[属性名=property;]
#注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8

#指定由连接池所创建的连接的自动提交（auto-commit）状态：
defaultAutoCommit=true

#driver default 指定由连接池所创建的连续只读（read-only）状态
#如果没有设置该值，则“setReadOnly”方法将不被调用。（某些驱动不支持只读模式，如：Informix）
defaultReadOnly=

#driver default 指定由连接池所创建的连接事务级别(TransactionIsolation)。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED,REPEATABLE_READ
defaultTransactionIsolation=READ_UNCOMMITTED

3.编写工具类

编写 JdbcUtils_DBCP.java类文件，代码如下：

package com.sanduo.lesson5.utils;

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

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

public class JdbcUtils_DBCP {
    private static DataSource dataSource = null;


    static {
        try {
            InputStream in = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            Properties properties = new Properties();
            properties.load(in);

            //创建数据源 工厂模式->创建
            dataSource = BasicDataSourceFactory.createDataSource(properties);


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

    // 获取连接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();//从数据源中获取连接
    }

    // 释放连接资源
    public static void release(Connection conn, Statement st, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if (st != null) {
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

}

4.测试代码

package com.sanduo.lesson5.utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestQuery {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;


        try {
            conn = JdbcUtils_DBCP.getConnection();
            // 区别
            // 使用问号占位符，代替参数
            String sql = "select * from `user` where id = ?";
            st = conn.prepareStatement(sql);//预编译sql,先写sql，然后不执行
            // 手动给参数赋值
            st.setInt(1, 1);
            // 执行
            rs = st.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getObject("id"));
                System.out.println(rs.getObject("name"));

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils_DBCP.release(conn,st,rs);
        }
    }
}

11.C3P0

1.导入包

c3p0-0.9.5.5.jar
mchange-commons-java-0.2.19.jar

2.编辑配置文件

cdp0-config.xml代码如下：

<?xml version="1.0" encoding="UTF-8"?>

<c3p0-config>
    <!--
     C3P0的缺省（默认配置）,如果在代码中 ComboPooledDataSource ds = new ComboPooledDataSource(); 这样就表示获取默认数数据源
     -->
    <!-- 默认配置，如果没有指定使用则使用这个配置 -->
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property>
        <property name="user">root</property>
        <property name="password">123</property>
        <property name="acquireIncrement">50</property>
        <property name="initialPoolSize">100</property>
        <property name="minPoolSize">50</property>
        <property name="maxPoolSize">1000</property>
        <!-- intergalactoApp adopts a different approach to configuring statement caching -->
        <property name="maxStatements">0</property>
        <property name="maxStatementsPerConnection">5</property>
        <!-- he's important, but there's only one of him -->
        <user-overrides user="master-of-the-universe">
            <property name="acquireIncrement">1</property>
            <property name="initialPoolSize">1</property>
            <property name="minPoolSize">1</property>
            <property name="maxPoolSize">5</property>
            <property name="maxStatementsPerConnection">50</property>
        </user-overrides>
    </default-config>

    <!--
   C3P0的命名配置,如果在代码中 ComboPooledDataSource ds = new ComboPooledDataSource("MySQL"); 这样就表示使用的是name为MySQL的数据源
   -->
    <!-- 命名的配置 -->
    <named-config name="mysql">
        <!-- 连接数据库的4项基本参数 -->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/test?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=true</property>
        <property name="user">root</property>
        <property name="password">root</property>
        <!-- 如果池中数据连接不够时一次增长多少个 -->
        <property name="acquireIncrement">5</property>
        <!-- 初始化连接数 -->
        <property name="initialPoolSize">10</property>
        <!-- 最小连接数 -->
        <property name="minPoolSize">10</property>
        <!-- 最大连接数 -->
        <property name="maxPoolSize">40</property>
        <!-- JDBC的标准参数，用以控制数据源内加载的PrepareStatements数量 -->
        <property name="maxStatements">200</property>
        <!-- 连接池内单个连接所拥有的最大缓存statements数 -->
        <property name="maxStatementsPerConnection">5</property>
    </named-config>

</c3p0-config>

3.编写C3P0工具类

JdbcUtils_C3P0.java 类

package com.sanduo.lesson5.utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;

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

public class JdbcUtils_C3P0 {
    private static DataSource dataSource = null;
    static {
        try {
            //创建数据源 工厂模式->创建
            dataSource = new ComboPooledDataSource("mysql");//配置文件写法
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 获取连接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();//从数据源中获取连接
    }

    // 释放连接资源
    public static void release(Connection conn, Statement st, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if (st != null) {
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

}

4.测试代码

编写TestQueryC3P0代码测试

package com.sanduo.lesson5.utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestQueryC3P0 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;


        try {
            conn = JdbcUtils_C3P0.getConnection();
            // 区别
            // 使用问号占位符，代替参数
            String sql = "select * from `user` where id = ?";
            st = conn.prepareStatement(sql);//预编译sql,先写sql，然后不执行
            // 手动给参数赋值
            st.setInt(1, 1);
            // 执行
            rs = st.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getObject("id"));
                System.out.println(rs.getObject("name"));

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils_C3P0.release(conn,st,rs);
        }
    }
}