封装数据库工具类
方式一：可重用性方案
/**
* 数据库工具类 连接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);
}
}
}
