JDBC简单使用

最新推荐文章于 2022-10-25 20:21:33 发布

Junzizhiai

最新推荐文章于 2022-10-25 20:21:33 发布

阅读量129

点赞数

分类专栏： Java学习

本文链接：https://blog.csdn.net/Junzizhiai/article/details/104453452

版权

Java学习专栏收录该内容

36 篇文章 0 订阅

订阅专栏

使用JDBC

创建测试数据库

CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;

USE jdbcStudy;

CREATE TABLE `users`(
  `id`INT PRIMARY KEY,
  `name` VARCHAR (40),
  `password` VARCHAR (40),
  `email` VARCHAR(60),
  `birthday` DATE
)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `users`(`id`,`name`,`password`,`email`,`birthday`) 
VALUE (1,'zhangsan','123456','za@sina.com','1980-12-04'),
(2,'lisi','123456','li@sina.com','1981-12-04'),
(3,'wangwu','123456','wang@sina.com','1979-12-04');

第一个JDBC程序

import java.sql.*;
public class JdbcFirstDemo {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //固定写法，加载驱动

        //2.用户信息和url
        //useUnicode=ture&characterEncoding=utf8&useSSL=ture
        String url = "jdbc:mysql://192.168.31.112:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username ="root";
        String password ="123456";
        //3.连接成功，数据库对象
        Connection connection = DriverManager.getConnection(url, username,password);
        //4.执行SQL的对象
        Statement statement = connection.createStatement();

        //5.执行SQL的对象去执行SQL，可能存在结果，查看返回结果
        String sql = "SELECT * FROM users;";

        ResultSet resultSet = statement.executeQuery(sql);
        //返回结果集，结果集中粉装了我们全部查询出来的结果
        while ( resultSet.next()){
            System.out.println("id=" + resultSet.getObject("id"));
            System.out.println("name=" + resultSet.getObject("name"));
            System.out.println("pwd=" + resultSet.getObject("password"));
            System.out.println("email=" + resultSet.getObject("email"));
            System.out.println("birth=" + resultSet.getObject("birthday"));
        }
        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();

    }
}

步骤总结：

1.加载驱动

2.连接数据库

3.获取执行Sql 的

4.获得返回结果集

5.关闭资源

DriverManager

 //1.加载驱动
 //DriverManager.registerDriver(new com.mysql.jdbc.Driver() );
Class.forName("com.mysql.jdbc.Driver");
//固定写法，加载驱动
//
 //3.连接成功，数据库对象
        Connection connection = DriverManager.getConnection(url, username,password);
        connection.rollback();
        connection.commit();
        connection.getAutoCommit();

url

//2.用户信息和url
//useUnicode=ture&characterEncoding=utf8&useSSL=ture
String url = "jdbc:mysql://192.168.31.112:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=true";

//mysql 3306
//jdbc:mysql
//oralce --1521
//jdbc:orcal:thin：@localhost：1521：sid

statement执行SQL对的对象。prepareStatement执行SQL的对象

statement.executeQuery(); //查询返回操作
statement.execute();//执行任务SQL
statement.executeUpdate();  //更新插入删除都用他

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

获得指定的数据类型

resultSet.getObject();
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();       
resultSet.beforeFirst(); //移动到最前面
resultSet.afterLast(); //移动到最后面

绝对定位

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

代码实现（提取工具类）

//文件名字：db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://192.168.31.112:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456


import java.io.IOException;
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 (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException 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 e) {
                e.printStackTrace();
            }
        }
        if (st != null) {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
}

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();
            st = conn.createStatement();
            String sql = "INSERT INTO `users` (`id` ,`name`,`password`,`email`,`birthday`)" +
                    "VALUES(6,'liuliu','123456','liu@sina.com','1977-12-04');";
            int i = st.executeUpdate(sql);
//            st.executeQuery()  查询执行
            if (i > 0){
                System.out.println("插入成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

sql注入的问题

sql存在漏洞，导致被攻击。

SQL 会被拼接，导致攻击。

prepareStatement对象

本质是繁殖SQL注入的本质，把传递进来的参数当作字符

引号会被转义掉。
PreparedStatement对象可以防止sql注入。效率更好！

import com.duan.demo02.utils.JdbcUtils;

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

public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            //1.连接
            conn = JdbcUtils.getConnection();
            //区别
            //使用？占位符
            String sql = "insert into users(id,`name`,`password`,`email`,`birthday`)values(?,?,?,?,?)";
            //2.获取prepareStatement对象
            pstm = conn.prepareStatement(sql);//预编译sql，先写sql，然后不执行
            //3.手动给参数赋值
            pstm.setInt(1, 4);//id
            pstm.setString(2, "leiwei");
            pstm.setString(3, "123456");
            pstm.setString(4, "123456@qq.com");
            // 注意点：sql.Date   数据库
            //        util.Date  Java  new Date().getTime() 获得时间戳
            pstm.setDate(5, new java.sql.Date(new java.util.Date().getTime()));

            //4.执行：
            int i = pstm.executeUpdate();

            if (i > 0) {
                System.out.println("插入成功！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //5.释放
            JdbcUtils.release(conn, pstm, null);
        }

    }
}

结果：
插入成功！

2.删除：

import com.duan.demo02.utils.JdbcUtils;

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

public class TestDelete {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            //1.连接
            conn = JdbcUtils.getConnection();
            //区别
            //使用？占位符
            String sql = "delete from users where id = ? ";
            //2.获取prepareStatement对象
            pstm = conn.prepareStatement(sql);//预编译sql，先写sql，然后不执行
            //3.手动给参数赋值
            pstm.setInt(1,4);
            //4.执行：
            int i = pstm.executeUpdate();

            if (i > 0) {
                System.out.println("删除成功！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //5.释放
            JdbcUtils.release(conn, pstm, null);
        }

    }
}

结果:
删除成功！

3.更新：

import com.duan.demo02.utils.JdbcUtils;

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

public class TestUpdate {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            //1.连接
            conn = JdbcUtils.getConnection();
            //区别
            //使用？占位符
            String sql = "update users set `name`=?  where id=?;";
            //2.获取prepareStatement对象
            pstm = conn.prepareStatement(sql);//预编译sql，先写sql，然后不执行
            //3.手动给参数赋值
            pstm.setString(1,"断浮");
            pstm.setInt(2,1);
            //4.执行：
            int i = pstm.executeUpdate();

            if (i > 0) {
                System.out.println("更新成功！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //5.释放
            JdbcUtils.release(conn, pstm, null);
        }

    }
}

结果：
更新成功！

4.查询：

import com.duan.demo02.utils.JdbcUtils;

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

public class TestSelect {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();//获取连接

            String sql = " select * from users where id = ? ";//编写sql

            pstm = conn.prepareStatement(sql);//预编译

            pstm.setInt(1, 1);//传递参数

            rs = pstm.executeQuery();//执行
            if (rs.next()) {
                System.out.println(rs.getString("name"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn, pstm, rs);
        }
    }
}

防止sql注入

package com.duan.demo03;

import com.duan.demo02.utils.JdbcUtils;

import java.sql.*;

public class 防止SQL注入 {
    public static void main(String[] args) {
        //login("lisi","123456");
        login("'' or 1=1 ", "123456");
    }

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

        try {
            conn = JdbcUtils.getConnection();
            //PreparedStatement，防止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.getString("name"));
                System.out.println(rs.getString("password"));
                System.out.println("================================");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, rs);
        }
    }
}

Junzizhiai

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
JDBC简单使用

使用JDBC创建测试数据库CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;USE jdbcStudy;CREATE TABLE `users`( `id`INT PRIMARY KEY, `name` VARCHAR (40), `password` VARCHAR (40), `...
复制链接

扫一扫