JDBC02

最新推荐文章于 2022-04-20 15:14:16 发布

Duplicity.

最新推荐文章于 2022-04-20 15:14:16 发布

阅读量103

点赞数

文章标签： JDBC

本文链接：https://blog.csdn.net/qq_44341488/article/details/87457971

版权

创建工具类

import java.sql.*;

/**
 * @Date 2019/2/16 - 15:43
 */
public class DBUtils {
    private static String url = "jdbc:mysql://localhost:3306/mydb1";
    private static String user = "root";
    private static String password = "123456";
    private static String driverClass = "com.mysql.jdbc.Driver";

    static {
        //注册驱动
        try {
            Class.forName(driverClass);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, user, password);
    }

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

JDBC操作 - 插入数据

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

/**
 * @Date 2019/2/16 - 16:16
 */
public class Test02 {
    public static void main(String[] args) throws SQLException {
        //1.获取连接
        Connection con = DBUtils.getConnection();

        //创建PreparedStatement对象
        String sql = "insert into user (id,name,password,email,birthday) value (?,?,?,?,?)";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setInt(1, 4);
        ps.setString(2, "zhaoliu");
        ps.setString(3, "123456");
        ps.setString(4, "zhaoliu@qq.com");
        ps.setDate(5, new Date(System.currentTimeMillis()));


        //执行插入
        int r = ps.executeUpdate();//返回受影响的行数


        //关闭资源
        DBUtils.close(con,ps,null);
    }
}

执行插入数据后，修改数据前
在这里插入图片描述

JDBC操作 - 修改数据

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

/**
 * @Date 2019/2/16 - 17:02
 */
public class Test03 {
    public static void main(String[] args) throws SQLException {
        //需求：修改zhangsan的密码,邮箱
        //1.获取连接
        Connection con = DBUtils.getConnection();
        //创建PreparedStatement对象
        String sql = "update user set password=?,email=? where name=?";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1,"654321");
        ps.setString(2,"zsvip@qq.com");
        ps.setString(3,"zhangsan");

        //执行修改操作
        ps.executeUpdate();


        //关闭资源
        DBUtils.close(con,ps,null);
    }
}

执行修改数据后，删除数据前
在这里插入图片描述

JDBC操作 - 删除数据

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

/**
 * @Date 2019/2/16 - 17:14
 */
public class Test04 {
    public static void main(String[] args) throws SQLException {
        //需求：删除name为zhaoliu的数据
        //1.获取连接
        Connection con = DBUtils.getConnection();
        //创建PreparedStatement对象
        String sql = "delete from user where name=?";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1, "zhaoliu");

        //执行删除操作
        ps.executeUpdate();

        //关闭资源
        DBUtils.close(con, ps, null);
    }
}

删除数据后
在这里插入图片描述

JDBC操作 - 查询数据

import java.util.Date;

/**
 * @Date 2019/2/16 - 17:29
 */
public class User {
    private int id;
    private String name;
    private String password;
    private String email;
    private Date birthday;

    public int getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public String getPassword() {
        return password;
    }

    public String getEmail() {
        return email;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setId(int id) {
        this.id = id;
    }

    public void setName(String name) {
        this.name = name;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    @Override
    public String toString() {
        return "User{" +
                "id:" + id +
                ", name:'" + name + '\'' +
                ", password:'" + password + '\'' +
                ", email:'" + email + '\'' +
                ", birthday:" + birthday +
                '}';
    }
}

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * @Date 2019/2/16 - 17:33
 */
public class Test05 {
    public static void main(String[] args) throws SQLException {
        Connection con = DBUtils.getConnection();
        Statement sm = con.createStatement();
        ResultSet rs = sm.executeQuery("select * from user;");
        List<User> list = new ArrayList<>();
        while (rs.next()) {
            User user = new User();
            user.setId(rs.getInt("id"));
            user.setName(rs.getString("name"));
            user.setPassword(rs.getString("password"));
            user.setEmail(rs.getString("email"));
            user.setBirthday(rs.getDate("birthday"));
            list.add(user);
        }
        for (User user : list) {
            System.out.println(user);
        }
        DBUtils.close(con, sm, rs);
    }
}

查询结果：
在这里插入图片描述

模拟登录

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


/**
 * @Date 2019/2/16 - 20:07
 */
public class UserServer {

    public User findUser(String name, String password) {
        User user = null;
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = DBUtils.getConnection();
            String sql = "select * from user where name = ? and password=?;";
            ps = con.prepareStatement(sql);
            ps.setString(1, name);
            ps.setString(2, password);
            rs = ps.executeQuery();
            while (rs.next()){
                user = new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
                user.setPassword(rs.getString("password"));
                user.setEmail(rs.getString("email"));
                user.setBirthday(rs.getDate("birthday"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(con, ps, rs);
        }
        return user;
    }
}

import java.util.Scanner;

/**
 * @Date 2019/2/16 - 20:10
 */
public class Test06 {
    public static void main(String[] args) {
        System.out.println("======登录系统=====");
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名:");
        String name = sc.nextLine();
        System.out.println("请输入密码:");
        String password = sc.nextLine();
        User user = new UserServer().findUser(name, password);
        if (user != null) {
            System.out.println("登陆成功");
        } else {
            System.out.println("登陆失败");
        }
    }
}

SQL注入

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

/**
 * @Date 2019/2/16 - 21:17
 */
public class UserServer01 {

    public User findUser(String name, String password) {
        User user = null;
        Connection con = null;
        Statement sm = null;
        ResultSet rs = null;
        try {
            con = DBUtils.getConnection();
            sm = con.createStatement();
            String sql = "select * from user where name = '" + name + "' and password='" + password + "';";
            rs = sm.executeQuery(sql);
            while (rs.next()) {
                user = new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
                user.setPassword(rs.getString("password"));
                user.setEmail(rs.getString("email"));
                user.setBirthday(rs.getDate("birthday"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(con, sm, rs);
        }
        return user;
    }
}

import java.util.Scanner;

/**
 * @Date 2019/2/16 - 20:10
 */
public class Test06 {
    public static void main(String[] args) {
        System.out.println("======登录系统=====");
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名:");
        String name = sc.nextLine();
        System.out.println("请输入密码:");
        String password = sc.nextLine();
        User user = new UserServer01().findUser(name, password);
        if (user != null) {
            System.out.println("登陆成功");
        } else {
            System.out.println("登陆失败");
        }
    }
}

结果显示：
在这里插入图片描述

Duplicity.

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
JDBC02

创建工具类import java.sql.*;/** * @Date 2019/2/16 - 15:43 */public class DBUtils { private static String url = "jdbc:mysql://localhost:3306/mydb1"; private static String user = "root"; pr...
复制链接

扫一扫