封装数据库工具类

方式一：可重用性方案

/**
 * 数据库工具类   连接companydb数据库
 * 1.提供连接
 * 2.资源关闭
 * 可重用性方案
 */
import java.sql.*;

public class DButils {
   
//    只执行一次
    static {
   
        try {
   
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
   
            e.printStackTrace();
        }
    }
    //获得连接
    public static Connection getConnection() {
   
        Connection connection = null;
        try {
   
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/companydb?useUnicode=true&characterEncoding=UTF8", "root", "root");
        } catch (Exception e) {
   
            e.printStackTrace();
        }
        return connection;
    }

    //释放资源
    public static void closeAll(Connection connection, Statement statement, ResultSet resultSet){
   
        try {
   
            if (resultSet != null) {
   
                resultSet.close();
            }
            if (statement != null) {
   
                statement.close();
            }
            if (connection != null) {
   
                connection.close();
            }
        } catch (SQLException e) {
   
            e.printStackTrace();
        }
    }
}

方式二：跨平台方案

• src下创建db.proerties文件

driver = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/companydb?useUnicode=true&characterEncoding=utf8
username = root
password = root

/**
 * 数据库工具类
 * 1.获取连接 connection
 * 2.释放资源
 * 可跨平台方案
 */
import java.io.*;
import java.sql.*;
import java.util.Properties;
public class DBUtils{
   
    //配置文件集合    永远不变
    private static final Properties properties = new Properties();
    static {
   
        try {
   
            //使用类自带的流
            //首次使用工具类，触发类加载
            InputStream is = DBUtils.class.getResourceAsStream("/db.properties");
            //通过流将配置信息的内容分割成键值对
            //将is流中的配置文件信息，加载到集合中
            properties.load(is);
            Class.forName(properties.getProperty("driver"));
        } catch (ClassNotFoundException e) {
   
            e.printStackTrace();
        } catch (IOException e) {
   
            e.printStackTrace();
        }
    }
    //获得连接
    public static Connection getConnection(){
   
        Connection connection = null;
        try {
   
            connection = DriverManager.getConnection(properties.getProperty("url"),properties.getProperty("username"),properties.getProperty("password"));
        } catch (SQLException e) {
   
            e.printStackTrace();
        }
        return connection;
    }
    //释放资源
    public static void closeAll(Connection connection, Statement statement, ResultSet resultSet) {
   
        try {
   
            if (resultSet != null) {
   
                resultSet.close();
            }
            if (statement != null) {
   
                statement.close();
            }
            if (connection != null) {
   
                connection.close();
            }
        } catch (SQLException e) {
   
            e.printStackTrace();
        }
    }
}

ORM封装

ORM（Object Relational Mapping）从数据库查询到的结果集(ResultSet)在进行遍历时，逐行遍历，取出的都是零散的数据。需要将零散的数据进行封装整理；

ORM 实体类(entity)：零散数据的载体

• 一行数据中，多个零散的数据进行整理
• 通过entity的规则对表中的数据进行对象的封装
• 表名=类名；列名=属性名；提供各个属性的get、set方法
• 提供无参构造方法、看情况添加有参构造

创建表并添加元素

CREATE TABLE `user`(
    id INT PRIMARY KEY,#主键
    username VARCHAR(20) NOT NULL,
    `password` VARCHAR(20) NOT NULL,#非空
    sex CHAR(2),
    email VARCHAR(50) NOT NULL,
    address VARCHAR(20) NOT NULL
)CHARSET = utf8;
INSERT INTO `user` (id,username,`password`,sex,email,address)
VALUES (1001,'kaka','123','男','123456789@qq.com','陕西省西安市');

/**
 * ORM查询
 */
import java.sql.*;
public class OrmSelect {
   
    public static void main(String[] args) {
   
        Connection connection = DBUtils.getConnection();
        String sql = "select id,username,PASSWORD,sex,email,address from `user`;";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
   
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            //拿到每一行数据
            while (resultSet.next()) {
   
                //拿到每一列的数据
                User user = new User();
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String password = resultSet.getString("password");
                String sex = resultSet.getString("sex");
                String email = resultSet.getString("email");
                String address = resultSet.getString("address");
                //将零散的数据，封装在User对象里
                user.setId(id);
                user.setUsername(username);
                user.setPassword(password);
                user.setSex(sex);
                user.setEmail(email);
                user.setAddress(address);
                System.out.println(user);
            }
        } catch (SQLException e) {
   
            e.printStackTrace();
        } finally {
   
            DBUtils.closeAll(connection, preparedStatement, resultSet);
        }
    }
}