java操作数据库之jdbc(封装版 )

一.导入mysql包

mysql-connector-java-5.0.8-bin.jar

二.封装DButils
DButils类(关闭流和取得连接)

package util;
import java.sql.*;

public class DBUtil {
private static String driver = "com.mysql.jdbc.Driver";
private static String url ="jdbc:mysql://localhost:3306/javauseSSL=true&characterEncoding=utf-8";
private static String user="root";
private static String password="123456";

static {
    try {
        Class.forName(driver);
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }
}

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


// 关闭连接
public static void get_CloseConn(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 bean;

/**
* user - 实体层 - 同来映射数据库
*/
public class User {

// 对属性的封装
private int id;
private String username;
private String password;

// 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;
}

// 构造方法
public User() {
}

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

// toString的方法重写

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

四.Dao层(接口)
IUserDao类

package dao;

import bean.User;

import java.util.List;

/**
* dao - 数据库交互层 - 可以直接访问数据库
*/

public interface IUserDao {
// 全查
List<User> findAll();
 }

五.DaoImpl层(dao实现类)

package dao.impl;

import bean.User;
import dao.IUserDao;
import util.DBUtil;

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

public class UserDaoImpl implements IUserDao {
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
User user = null;

@Override
public List<User> findAll() {
    List<User> userList = new ArrayList<>();

    try {
        // 1. 创建连接
        conn = DBUtil.get_Conn();
        // 2. 获取存放sql语句的对象
        pstm = conn.prepareStatement("select * from tb_user");
        // 3. 执行并返回结果
        rs = pstm.executeQuery();
        // 4. 遍历结果集
        while (rs.next()){
            user = new User();
            user.setId(rs.getInt("id"));
            user.setUsername(rs.getString("username"));
            user.setPassword(rs.getString("password"));
            userList.add(user);
        }
    } catch (Exception e) {
        e.printStackTrace();
    }finally {
        try {
            DBUtil.get_CloseConn(rs, pstm, conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    return userList;
}

//测试运行
public static void main(String[] args) {
    UserDaoImpl us = new UserDaoImpl();
    us.findAll();
}
}