0. 准备工作
先要导入mysql-connector的jar包,添加依赖关系。然后导入jdbc.properties配置文件
//jdbc 配置文件内容
url=jdbc:mysql:///db1
user=root
password=root
driver=com.mysql.jdbc.Driver
1. JDBCUtils工具类代码实现
package util;
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;
/**
* JDBC 工具类
*/
public class JDBCUtils {
private static String url;
private static String user;
private static String password;
private static String driver;
/**
* 文件读取,只会执行一次,使用静态代码块
*/
static {
//读取文件,获取值
try {
//1.创建Properties集合类
Properties pro = new Properties();
//获取src路径下的文件--->ClassLoader类加载器
ClassLoader classLoader = JDBCUtils.class.getClassLoader();
URL resource = classLoader.getResource("jdbc.properties");;
String path = resource.getPath();
//2.加载文件
pro.load(new FileReader(path));
//3获取数据
url = pro.getProperty("url");
user = pro.getProperty("user");
password = pro.getProperty("password");
driver = pro.getProperty("driver");
//4.注册驱动
Class.forName(driver);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* 获取连接
* @return 连接对象
*/
public static Connection getConnection() throws SQLException {
Connection conn = DriverManager.getConnection(url, user, password);
return conn;
}
/**
* 释放资源
* @param rs
* @param st
* @param conn
*/
public static void close(ResultSet rs, Statement st,Connection conn){
if (rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(st != null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
2. 把数据里面创建的表格当作一个对象
如下面这个表:
创建一个数据库表的类,代码如下:
package com.zk.demo02jdbcutils;
public class UserBean {
private int id;
private String username;
private String password;
public UserBean(int id, String username, String password){
this.id = id;
this.username = username;
this.password = password;
}
@Override
public String toString() {
return "UserBean{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
'}';
}
}
3.测试JDBCUtils工具类
package com.zk.demo02jdbcutils;
import util.JDBCUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class Demo01JDBCUtils {
public static void main(String[] args) {
List<UserBean> list = findAll();
System.out.println("list = " + list);
}
//定义一个方法,查询user表的数据将其封装为对象,然后装载集合,返回。
public static List<UserBean> findAll(){
Connection conn = null;
Statement st = null;
ResultSet rs = null;
List<UserBean> list = null;
try {
conn = JDBCUtils.getConnection();
//定义sql
String sql = "SELECT * FROM user";
//获取执行sql的对象
st = conn.createStatement();
//执行sql
rs = st.executeQuery(sql);
UserBean bean = null;
list = new ArrayList<UserBean>();
while (rs.next()){
int id = rs.getInt("id");
String username = rs.getString("username");
String password = rs.getString("password");
bean = new UserBean(id,username,password);
list.add(bean);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.close(rs,st,conn);
}
return list;
}
}