实习日志【二】

概述

今天的任务主要为通过jdbc实现数据库的增删改查

1. 主要步骤

1. 获取驱动
2. 创建连接
3. 编写sql
4. 获取存放sql的对象
5. 去执行sql语句 并得到结果
6. 关闭连接

2. 主要代码

TestUser

package com.h;

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

public class TestUser2 {
    Connection conn = null;
    PreparedStatement pstm = null;
    ResultSet rs = null;
    static User user = new User();

    // 增加
    public void addUser(User user){
        try {
            // 1. 获取连接
            conn = DBUtl.get_Conn();
            // 2. 获取存放sql语句的对象
            pstm = conn.prepareStatement("insert into tb_user(username,password) values(?,?)");
            // 3. 填坑
            pstm.setString(1,user.getUsername());
            pstm.setString(2,user.getPassword());
            // 4. 执行sql并得到数据
            int i = pstm.executeUpdate();
            // 5. 处理结果
            if(i>0){
                System.out.println("增加成功");
            }else {
                System.out.println("增加失败");
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            // 6. 关闭连接
            try {
                DBUtl.get_Close(null,pstm,conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    // 删除
    public void deleteUser(User user) {
        try {
            conn = DBUtl.get_Conn();
            pstm = conn.prepareStatement("DELETE FROM tb_user WHERE username=?;");
            pstm.setString(1,user.getUsername());
            int i = pstm.executeUpdate();
            if(i>0){
                System.out.println("删除成功");
            }else {
                System.out.println("删除失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                DBUtl.get_Close(null,pstm,conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    // 修改
    public void editUser(User user) {
        try {
            conn = DBUtl.get_Conn();
            pstm = conn.prepareStatement("update tb_user set password=? where username=?;");
            pstm.setString(1,user.getPassword());
            pstm.setString(2,user.getUsername());
            int i = pstm.executeUpdate();
            if(i>0){
                System.out.println("修改成功");
            }else {
                System.out.println("修改失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                DBUtl.get_Close(null,pstm,conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    // 查询
    public void searchUser(User user) {
        try {
            conn = DBUtl.get_Conn();
            pstm = conn.prepareStatement("select * from tb_user where username=?");
            pstm.setString(1,user.getUsername());
            rs = pstm.executeQuery();
            // 6. 遍历结果
            while(rs.next()){
                System.out.println("用户ID："+ rs.getInt(1));
                System.out.println("用户名："+ rs.getString(2));
                System.out.println("用户密码："+ rs.getString(3));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            // 6. 关闭连接
            try {
                DBUtl.get_Close(rs,pstm,conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void input (int i) {
        Scanner in =new Scanner(System.in);
        System.out.println("用户名：");
        user.setUsername(in.next());
        if(i==3)
            System.out.println("新密码：");
        else
            System.out.println("密码：");
        user.setPassword(in.next());
    }

    public static void main(String[] args) {
        TestUser2 t2 = new TestUser2();
        Scanner in =new Scanner(System.in);
        boolean breakout = false;

        while(true){
            System.out.println("请选择操作：\n 1. 新增\n 2. 删除\n 3. 修改密码\n 4. 查询\n 0. 退出");
            int a=in.nextInt();
            switch (a) {
                case 1:
                    System.out.println("输入新增数据：");
                    t2.input(1);
                    t2.addUser(user);
                    break;
                case 2:
                    System.out.println("输入删除数据：");
                    t2.input(2);
                    t2.deleteUser(user);
                    break;
                case 3:
                    System.out.println("输入修改数据：");
                    t2.input(3);
                    t2.editUser(user);
                    break;
                case 4:
                    System.out.println("输入查询数据：");
                    t2.input(4);
                    t2.searchUser(user);
                    break;
                case 0:
                    breakout = true;
                    break;
            }
            if (breakout) {
                break;
            }
        }
        System.exit(0);
    }
}

##DBUtl

package com.h;

import java.sql.*;

public class DBUtl {
    private static String driver = "com.mysql.jdbc.Driver";
    private static String url = "jdbc:mysql://localhost:3306/java7?useSSL=true&characterEncoding=utf-8";
    private static String user = "root";
    private static String password = "123";

    // 1. 获取驱动
    static {
        try {
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 2. 创建连接
    public static Connection get_Conn() throws SQLException {
        Connection conn = DriverManager.getConnection(url, user, password);
        System.out.println("数据库连接成功" + conn);
        return conn;
    }

    // 3. 关闭连接
    public static void get_Close(ResultSet rs, PreparedStatement pstm, Connection conn) throws SQLException {
        if(rs != null){
            rs.close();
        }
        if(pstm != null){
            pstm.close();
        }
        if(conn != null){
            conn.close();
        }
    }

    // 白盒测试-单元测试
    public static void main(String[] args) {
        try {
            get_Conn();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

##User

package com.h;
// 实体类 - 映射数据库字段
public class User {

    // 1. 对属性的封装

    private int id; // 用户ID
    private String username; // 用户名
    private String password; // 用户密码

    // 2. get and set

    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 getPassword() {
        return password;
    }

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

    // 3. 构造方法

    public User(int id, String username, String password) {
        this.id = id;
        this.username = username;
        this.password = password;
    }

    public User(String username, String password) {
        this.username = username;
        this.password = password;
    }

    public User() {
    }

    // 4. toString 方法重写

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

3. 结果