JDBC(一)

JDBC

• Java DataBase connectivity: Java数据库连接
• 学习JDBC主要学习的是如何通过Java代码执行SQL语句
• JDBC是Sun公司提供的一套专门用于Java语言和数据库软件进行连接的API(Application Programma Interface应用程序编程接口),Sun公司为了避免Java程序员每一种数据库软件都学习一套全新的方法, Sun公司通过JDBC接口定义好了方法名,让各个数据库的厂商根据此方法名写各自的实现类(驱动), 这样的话Java程序员只需要学会JDBC接口中方法的调用即可访问任何数据库软件, 甚至换数据库时代码都不需要改变. 这样的话大大提高了开发效率.
  

如何使用JDBC连接数据库软件

• 1.创建Maven工程jdbc01
• 2.在pom文件中添加引入MySQL驱动(jar包)的依赖
  

<dependencies>
    
    <dependency>
        <groupId>mysqlgroupId>
        <artifactId>mysql-connector-javaartifactId>
        <version>8.0.15version>
    dependency>
dependencies>
1
2
3
4
5
6
7
8

• 3.添加测试代码

public class Demo01 {
    public static void main(String[] args) throws SQLException {
        //1.获取数据库连接;
        Connection conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/empdb?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false","root","123456");
        System.out.println("连接对象:"+conn);
        //2.创建执行SQL语句的对象
        Statement s = conn.createStatement();
        //3.执行SQL语句
        s.execute("create table jdbct1(name varchar(20),age int)");
        //4.关闭资源
        conn.close();
        System.out.println("执行完成!");
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14

Statement执行SQL语句的对象

• execute(“sql”);可以执行任意的SQL语句,推荐执行数据库相关和表相关的SQL
• int row=execteUpdate("sql);此方法执行增删改查相关的SQL语句,row代表生效的行数
• ResultSet rs=executeQuery(“sql”);此方法专门用于执行查询相关的SQL语句,ResultSet是结果集对象,里面装着查询回来的所有数据

数据库连接池DBCP(DataBaseConnectionPool)

• 作用:是将连接重用,避免了频繁开关连接导致的资源浪费,从而提高执行效率
  
• 如何使用数据库连接池?
• • 在pom.xml里面添加数据库连接池的依赖

 
    <dependency>
        <groupId>com.alibabagroupId>
        <artifactId>druidartifactId>
        <version>1.1.21version>
    dependency>
1
2
3
4
5
6

• • 相关代码

//创建数据库连接池
DruidDataSource dds = new DruidDataSource();
//设置连接数据库的信息
dds.setUrl("jdbc:mysql://localhost:3306/empdb?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false");
dds.setUsername("root");
dds.setPassword("***");
//设置初始连接数量
dds.setInitialSize(3);
//设置最大连接数量
dds.setMaxActive(5);
//获取连接对象  异常抛出
Connection conn = dds.getConnection();
System.out.println("连接:"+conn);
1
2
3
4
5
6
7
8
9
10
11
12
13

练习

1.删除表

public class Demo02 {
    public static void main(String[] args) throws SQLException {
        //1.获取数据库连接;
        Connection conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/empdb?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false","root","123456");
        System.out.println("连接对象:"+conn);
        //2.创建执行SQL语句的对象
        Statement s = conn.createStatement();
        //3.执行SQL语句
        s.execute("drop table jdbct1");
        //4.关闭资源
        conn.close();
        System.out.println("执行完成!");
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2.插入 修改 和查询表

public class Demo03 {
    public static void main(String[] args) throws SQLException {
        Connection conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/empdb?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false","root","123456");
        Statement s = conn.createStatement();
//        s.executeUpdate("insert into emp(name) values('Tom')");
        //执行修改
//        s.executeUpdate("update emp set name='jerry' where name='Tom' ");
//        s.executeUpdate("delete from emp where name ='jerry'");
        //执行查询数据的SQL
        ResultSet rs = s.executeQuery("select * from emp");
        //遍历结果集对象 rs.next() 询问是否有下一条数据,有返回true否则返回false
        while (rs.next()){
            String name = rs.getString("name");
            double sal = rs.getDouble("sal");
            System.out.println(name+":"+sal);
        }
        conn.close();
        System.out.println("执行完成");
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

3.把连接数据库写成工具类,加入线程池并实现查询

public class DBUtils {
    private static DruidDataSource dds;
    static {
        dds=new DruidDataSource();
        dds.setUrl("jdbc:mysql://localhost:3306/empdb?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false");
        dds.setUsername("root");
        dds.setPassword("***");
        dds.setInitialSize(3);
        dds.setMaxActive(5);
    }
    public static Connection getConn() throws SQLException {
        Connection conn = dds.getConnection();
        return conn;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

public class Demo04 {
    public static void main(String[] args) {
        //获取连接对象
        try (Connection conn = DBUtils.getConn()){
            //创建执行SQl语句对象
            Statement s = conn.createStatement();
            ResultSet rs = s.executeQuery("select name  from  emp");
            while (rs.next()){
                String name = rs.getString("name");
                System.out.println(name);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

4.运用连接池连接数据库

 
       <dependency>
           <groupId>com.alibabagroupId>
           <artifactId>druidartifactId>
           <version>1.1.21version>
       dependency>
1
2
3
4
5
6

public class Demo05 {
    public static void main(String[] args) throws SQLException {
        DruidDataSource dds=new DruidDataSource();
        dds.setUrl("jdbc:mysql://localhost:3306/empdb?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false");
        dds.setUsername("root");
        dds.setPassword("***");
        dds.setInitialSize(3);
        dds.setMaxActive(5);
        Connection conn = dds.getConnection();
        System.out.println("连接:"+conn);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12

5.用户注册,注册时判断用户是否存在

• 首先创建一张表

create table user(id int primary key auto_increment,
username varchar(50),password varchar(50),nick varchar(50));
1
2

public class Demo06 {
    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名");
        String username=sc.nextLine();
        System.out.println("请输入密码");
        String password=sc.nextLine();
        System.out.println("请输入昵称");
        String nick=sc.nextLine();
        try (Connection conn = DBUtils.getConn()){
            Statement s = conn.createStatement();
            //查询user表中是否存在username
            ResultSet rs = s.executeQuery("select id from user where username='" + username + "'");
            if(rs.next()){
                System.out.println("用户名已存在");
                return;
            }
            String sql="insert into user values(null,'"+username+"','"+password+"','"+nick+"')";
            s.executeUpdate(sql);
            System.out.println("注册成功!");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

6.寻找SQL注入漏洞

public class Demo07{
    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名");
        String username = sc.nextLine();
        System.out.println("请输入密码");
        String password = sc.nextLine();
        try (Connection conn = DBUtils.getConn()){
            Statement s = conn.createStatement();
            String sql="select count(*) from user where username='"+username+"' and password='"+password+"'";
            System.out.println(sql);
            ResultSet rs = s.executeQuery(sql);
            //让游标往下移动 指向返回的数据
            rs.next();
            //取出查询到的数量  1代表查询到的数据位置
            int count = rs.getInt(1);
            if (count>0){
                System.out.println("登录成功!");
            }else{
                System.out.println("用户名或密码错误!");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

7.防止SQL注入,采用预编译形式

public class Demo07 {
    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名");
        String username = sc.nextLine();
        System.out.println("请输入密码");
        String password = sc.nextLine();
        try (Connection conn = DBUtils.getConn()){
//            Statement s = conn.createStatement();
//            String sql="select count(*) from user where username='"+username+"' and password='"+password+"'";
//            System.out.println(sql);
//            ResultSet rs = s.executeQuery(sql);

            通过PreparedStatement对象解决SQL注入的问题   Admin    admin
            String sql="select count(*) from user where username=? and password=?";
            PreparedStatement ps = conn.prepareStatement(sql);
            //把?替换成用户名和密码 1和2 代表?的位置
            ps.setString(1,username);
            ps.setString(2,password);
            //执行SQL语句
            ResultSet rs = ps.executeQuery();
            //让游标向下移动 指向返回的数据
            rs.next();
            //取出查询到的数量 1代表查询到的数据位置
            int count = rs.getInt(1);
            if(count>0){
                System.out.println("登录成功");
            }else{
                System.out.println("用户名或密码错误!");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

8.登录功能复写(防注入)

public class Demo08 {
    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名");
        String username = sc.nextLine();
        System.out.println("请输入密码");
        String password = sc.nextLine();
        try(Connection conn=DBUtils.getConn()){
            String sql="select password from user where username=?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1,username);
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                if(rs.getString("password").equals(password)){
                    System.out.println("登录成功!");
                }else{
                    System.out.println("密码错误");
                }
            }else {
                System.out.println("用户名不存在!");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

9.注册功能复写(防注入)

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

public class Demo09 {
    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名");
        String username = sc.nextLine();
        System.out.println("请输入密码");
        String password = sc.nextLine();
        System.out.println("请输入昵称");
        String nick = sc.nextLine();
        //得到数据库连接
        try (Connection conn = DBUtils.getConn()){

            String sql="select id from user where username=?";
            //创建执行SQL语句的对象
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1,username);
            //查询user表中是否已经存在了username
            ResultSet rs = ps.executeQuery();
            //判断是否查询到了数据
            if (rs.next()){
                System.out.println("用户名已存在!");
                return;
            }
            //往数据库中添加数据
            String insert_sql="insert into user values (null,?,?,?)";
            ps=conn.prepareStatement(insert_sql);
            ps.setString(1,username);
            ps.setString(2,password);
            ps.setString(3,nick);
            ps.executeUpdate();
            System.out.println("注册成功!");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42