JDBC20210804

jdbc 操作

package com.q;

import com.Tqinglan.enity.User;
import org.junit.Test;

import java.sql.*;
import java.util.Date;

public class JDBCtest01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1、注册驱动
        String url = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false";
        Class.forName("com.mysql.jdbc.Driver");
        //2、创建数据库连接
        //
        Connection conn =  DriverManager.getConnection(url,"root","123456");
        System.out.println(conn);
        //3、编写sql获取操作数据库对象
        String sql = "select * from user";
        PreparedStatement ps = conn.prepareStatement(sql);
        //执行对应的操作，并且将返回的数据返回结果集
        ResultSet re = ps.executeQuery();
        //操作结果集
        while(re.next()){
            int id = re.getInt(1);
            String uName = re.getString(2);
            String pwd = re.getString(3);
            String email = re.getString(4);
            Date birthday = re.getDate(5);
            System.out.println(id+uName+pwd+email+birthday);
        }
        //6、关闭资源
        re.close();
        ps.close();
        conn.close();
    }
    @Test
    public void TestInsert() throws ClassNotFoundException, SQLException {
        String url = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false";
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection(url,"root","123456");
        String sql = "INSERT INTO `user` VALUES (0002, '美丽', '123456', '2082585862@qq.com', '2021-08-09', 4);";
        PreparedStatement ps = conn.prepareStatement(sql);
        int count = ps.executeUpdate(sql);
        if(count!=0){
            System.out.println("插入成功");
        }
        ps.close();
        conn.close();

    }
    @Test
    public void TestDelete() throws ClassNotFoundException, SQLException {
        String url = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false";
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection(url,"root","123456");
        String sql="delete from user where id=2";
        PreparedStatement ps = conn.prepareStatement(sql);
        int count = ps.executeUpdate();
        if(count!=0){
            System.out.println("shanchu成功");
        }
        ps.close();
        conn.close();
    }
    @Test
    public void Testupdate() throws ClassNotFoundException, SQLException {
        String url = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false";
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection(url,"root","123456");
        String sql = "update user set userName='qian' where id = 3";
        PreparedStatement ps = conn.prepareStatement(sql);
        int count = ps.executeUpdate();
        if(count!=0){
            System.out.println("修改成功");
        }
        ps.close();
        conn.close();
    }
    @Test
    public void TestfindById() throws ClassNotFoundException, SQLException {
        String url = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false";
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection(url,"root","123456");
        String sql = "select * from user where id =1";
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet count = ps.executeQuery();
        if(count.next()) {
            User user = new User();
            user.setId(count.getInt(1));
            user.setUserName(count.getString(2));
            user.setPwd(count.getString(3));
            user.setEmail(count.getString(4));
            user.setBirthday(count.getDate(5));
            user.setRole(count.getInt(6));
            System.out.println(user);
        }
        ps.close();
        conn.close();
    }
}

实体类

package com.Tqinglan.enity;

import java.util.Date;
import java.util.Objects;

public class User {
    private int id;
    private String userName;
    private String pwd;
    private String email;
    private Date birthday;
    private int role;

    public User(int id, String userName, String pwd, String email, Date birthday, int role) {
        this.id = id;
        this.userName = userName;
        this.pwd = pwd;
        this.email = email;
        this.birthday = birthday;
        this.role = role;
    }

    public User() {
    }

    public int getId() {
        return id;
    }

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

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getPwd() {
        return pwd;
    }

    public void setPwd(String pwd) {
        this.pwd = pwd;
    }

    public String getEmail() {
        return email;
    }

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

    public Date getBirthday() {
        return birthday;
    }

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

    public int getRole() {
        return role;
    }

    public void setRole(int role) {
        this.role = role;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", userName='" + userName + '\'' +
                ", ped='" + pwd + '\'' +
                ", email='" + email + '\'' +
                ", birthday=" + birthday +
                ", role=" + role +
                '}';
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (!(o instanceof User)) return false;
        User user = (User) o;
        return getId() == user.getId() && getRole() == user.getRole() && Objects.equals(getUserName(), user.getUserName()) && Objects.equals(getPwd(), user.getPwd()) && Objects.equals(getEmail(), user.getEmail()) && Objects.equals(getBirthday(), user.getBirthday());
    }

    @Override
    public int hashCode() {
        return Objects.hash(getId(), getUserName(), getPwd(), getEmail(), getBirthday(), getRole());
    }
}

数据库文件链接

https://www.cnblogs.com/cloudCity/p/user1.html